Tales of an IT Nobody

devbox:~$ iptables -A OUTPUT -j DROP

MySQL’s max_connect_errors … 6 years later… August 2, 2013

Having recently been bitten by the awful default value (10) for max_connect_errors on a production server – I’m having a very hard time coming to terms with who the heck thought this would be a good way to do it.

This type of “feature” allows you to effecitvely DOS yourself quickly with just one misconfigured script – or Debian’s stupid debian-sys-maint account not replicating.

I’ve been thinking about how I could avoid this scenario in the future – upping the limit was a no brainer. But another item of curiosity: How do I know what hosts are on the list?

 

Have a look at this, 6 years later: http://bugs.mysql.com/bug.php?id=24906

 

So up until 5.6 (still brand new in the DBMS world) – there was no practical way to find out who was on this list.

The default needs to be changed, and this feature should be able to be turned off…

 

No Comments on MySQL’s max_connect_errors … 6 years later…
Categories: linux mysql rant servers

MySQL command line – zebra stripe admin tool February 7, 2013

I came up with a cool usage for the zebra stripe admin tool.  In MySQL you can set a custom pager for your MySQL CLI output; so one can simply set it to the zebra stripe tool and get the benefit of alternated rows for better visual clarity.

Something like ‘PAGER /path/to/zebra’ should yield you results like the image below.

Zebra stripe tool used as a MySQL pager

Zebra stripe tool used as a MySQL pager

 

You can always adjust the script to skip more lines before highlighting; you can also modify it if you’re savvy to the color codes to just set the font color instead of the entire background (which may be preferable but not a ‘global’ solution so the script doesn’t do it).

1 Comment on MySQL command line – zebra stripe admin tool
Categories: linux mysql servers tools

pt-online-schema-change and partitions – a word of caution October 30, 2012

Just a quick word of wisdom to those seeking to cleverly change the partitioning on a live table using a tool like pt-online-schema-change from the Percona Toolkit.

You will lose data if you don’t account for ALL of your data’s ranges upfront. (E.g: MAXVALUE oriented partition). The reason being is how online schema changing tools work; they create a ‘shadow copy’ of the table, set up a trigger and re-fill the table contents in chunks.

 

If you try to manually apply partitions to a table that has data from years ranging from 2005-2012 (And up), say something like:

ALTER TABLE _foo PARTITION BY RANGE(YEAR(dt)) ( PARTITION p0 VALUES LESS THAN (2012) )

And it has data from 2012 or above, MySQL will give you this safety error: (errno 1526): Table has no partition for value 2012 – this is a safeguard!

 

Now, if you use a hot schema tool, the safeguard isn’t in place; the copying process will happily rebuild the data with the new partitions and completely discard everything not covered by the partition range!

Meaning, this command will successfully finish, and discard all data from 2012+:

 

Just a heads up for those wanting to do something similar, you’ll definitely want to ALWAYS have something like this too fall back on as your final partition:

No Comments on pt-online-schema-change and partitions – a word of caution
Categories: databases mysql servers

MySQL COALESCE(), UNION behavior on zerofilled columns June 28, 2012

I’ve just filed a (potential) bug report, depending on your views for MySQL’s COALESCE() behavior:

http://bugs.mysql.com/bug.php?id=65763

If you have a zerofill column and perform the COALESCE() function on it, the leading zeros are truncated.

As I mention in the bug report, this may not matter to most – but it does change the output one would expect and is worthy of notice or a mention in the official documentation in my book.

Test scenario 1:

INSERT INTO coalesceTest (paddedNumber) VALUES (5), (10), (200), (3000);

SELECT COALESCE(paddedNumber), paddedNumber from coalesceTest;

Results:

Test scenario 2:

Results:

 

No Comments on MySQL COALESCE(), UNION behavior on zerofilled columns
Categories: mysql servers

On MySQL: The latest, far-reaching password circumvention June 11, 2012

By now, everyone has, or will be hearing about this issue.

While it’s an extremely simple hack and covers (dare I say the majority) of MySQL installation version. Let’s not forget to finish reading the entire disclosure:

From the disclosure:

But practically it’s better than it looks – many MySQL/MariaDB builds are not affected by this bug.

Whether a particular build of MySQL or MariaDB is vulnerable, depends on
how and where it was built. A prerequisite is a memcmp() that can return
an arbitrary integer (outside of -128..127 range). To my knowledge gcc builtin memcmp is safe, BSD libc memcmp is safe. Linux glibc sse-optimized memcmp is not safe, but gcc usually uses the inlined builtin version.

As far as I know, official vendor MySQL and MariaDB binaries are not
vulnerable.

Regardless, it’s a stupid simple test to see if you’re vulnerable or not so fire one up!

I just tested 5 gcc compiled hosts (mostly pre-5.5.23) and none of them were vulnerable. But regardless, maybe it’s time to re-compile ;)

No Comments on On MySQL: The latest, far-reaching password circumvention
Categories: mysql security servers

MySQL 5.6 – InnoDB (innodb_file_per_table) and recovery May 1, 2012

All I can say is rejoice!.

There’s a lot of fluff out there that beat around the bush or contain a regurgitated process for recovery using the 5.6 LAB edition of MySQL.

So instead, here’s the info straight from the horses mouth: http://blogs.innodb.com/wp/2012/04/innodb-transportable-tablespaces/  . This will make a huge difference in the world of MySQL and InnoDB users.

No Comments on MySQL 5.6 – InnoDB (innodb_file_per_table) and recovery
Categories: mysql servers

MySQL – multibyte cheat sheet for conditionals April 5, 2012

This is basically a short cheat sheet to launch some ideas for comparisons/matching with multibyte characters.

Connection collation of utf8_unicode_ci and charset of utf8 – Table + data and server:

Check out the behavior of the following queries:

Other resources:

No Comments on MySQL – multibyte cheat sheet for conditionals

Is there a hacking campaign against open source? September 26, 2011

Linux.com, kernel.org, mysql(twice this year), wordpress and php have all reported breaches of some sort this year. Is there some sort of campaign against these ‘high profile’ open source projects? It’s starting to feel like it, to me.

The more hands you get in the pot, the more nervous you should get as an administrator. System issues stem from more than password change frequency and difficulty – stale keys and giving access to folks that shouldn’t have access happens.

I also feel isolation or ‘separation of concerns’ is a tactic that is pushed aside in the name of maxing out a system, more often than not this stop gap would save a lot of trouble. Apache’s ability to mitigate concern from last year’s breaches is a good example of isolation, they had a fairly sophisticated break in and the repercussions weren’t as vocal as the ones from this year.

There doesn’t seem to be sufficient coverage of this MySQL hack right now – how sure are we this isn’t a sample set from a compromised browser as opposed to the site?

I hope there will be continued disclosure so everyone can learn something extra to safeguarding themselves.

While it doesn’t feel right to ream MySQL (at all, or at this point of the news) I have some initial thoughts I just can’t shake:

  1. If MySQL was ‘hacked’: Infiltrated earlier this year; you made no extra measures on a wider scope? really?
  2. Why the hell is your web/any cluster accessible without a VPN? It sounds like they’re selling shell access directly to the host/s..

 C’est la vie

No Comments on Is there a hacking campaign against open source?
Categories: mysql security servers

MySQL – max_allowed_packet – what is going on? August 4, 2011

So there’s enough noise in the MySQL community about what’s covered well here (https://www.facebook.com/note.php?note_id=10150236650815933)

Unfortuantely the bug is private for the time being; in my conversation with others, the general premise seems to be what good does max_allowed_packet really do? 

First off, I’d like to point out what seems to be what I hope is heading for deprecation – otherwise it just feels a bit sloppy; the default max_allowed_packet for the MySQL client is 1GB. (AKA: Maximum).

As the FB  post recognizes, there’s some ambiguity to how this setting is even enforced in the first place, especially when considering a master->slave configuration (Why does replication even have to follow that rule? Maybe replication clients can have a hard-coded packet to the maximum to get over this?)

I’d propose one of the two:

1. Enforce max_allowed_packet at the server – negotiate a loose communication with the client, where the client will obtain the server’s value and take it for it’s own.

2. Better yet, allow it to be set on a per user basis, following #1.

No Comments on MySQL – max_allowed_packet – what is going on?
Categories: mysql servers

mk-heartbeat – sample conf file July 26, 2011

There’s not a whole lot (if any) documentation on what the file for the –config parameter for Maatkit‘s mk-heartbeat should look like.

If you quickly pilfer through the source code, what it’s looking for becomes pretty straight forward (It’d be nice to have this in the documentation, however).

The config files are newline delimited and resemble a my.cnf file using ‘param=value’ notation.

E.g.:

Sample config

So this would be launched using something like:

In my book, this is essential behavior to avoid having your password hang out on the output from ps

No Comments on mk-heartbeat – sample conf file
Categories: mysql

Nay say for ext2/ext3, seemingly ext4 for MySQL servers July 19, 2011

 Basically I felt compelled to make a note regarding what filesystem to evaluate when you are performing a MySQL install. There seems to be a lot of reasons NOT to use the ext filesystems, and instead use XFS..

This is a straight out quote from a MySQL at Facebook blog entry:

ext-2 and ext-3 lock a per-inode mutex for the duration of a write. This means that ext-2 and ext-3 do not allow concurrent writes to a file and that can prevent you from getting the write throughput you expect when you stripe a file over several disks with RAID. XFS does not do this which is one of the reasons many people prefer XFS for InnoDB.

More on IO concurrency from  MySQL big name Domas Mituzas

  • O_DIRECT serializes writes to a file on ext2, ext3, jfs, so I got at most 200-250w/s.
  • xfs allows parallel (and out-of-order, if that matters) DIO, so I got 1500-2700w/s (depending on file size – seek time changes.. :) of random I/O without write-behind caching. There are few outstanding bugs that lock this down back to 250w/s

 A patch for ext4 was created, but it doesn’t appear that it made it in; it seems to yield minimal benefit.

And some other performance and risk observations involved with the most wildly used ext3.

If you’re looking to install or upgrade a MySQL server, it may very well be worth the time investment to research the depths of what filesystem you select, since it has just as much to do with the database performance as the MySQL configuration itself! 

No Comments on Nay say for ext2/ext3, seemingly ext4 for MySQL servers
Categories: databases mysql servers

Worthy of distribution: Reset root MySQL password July 18, 2011

Oh snap! Need to reset your mysql root/admin (or any?) MySQL password? Well, you’ll need root and control over MySQLd to some extent, but this is worthy of a rainy-day bookmark indeed: http://mysqlpreacher.com/wordpress/2011/03/recovering-a-mysql-root-password-three-solutions/

No Comments on Worthy of distribution: Reset root MySQL password

MySQL 5.5.12 – init script warning May 25, 2011

I’ve just reported a bug regarding the init script that comes in MySQL 5.5’s source distribution .

Basically, if you call the ‘start’ clause of the script twice it will hose the service by allowing multiple instances to run trying to utilize the same resources (pid file, socket and tcp port) – naturally this renders the service that -was- working fine to screech to a halt, mysqladmin shutdown won’t work.. The only way to fix this is to do something like this to get things to normal:

My solution to avoid this for the time being is to put this in the beginning of the ‘start’ case clause in the ‘mysql.server’ script that we’re copying to /etc/init.d:

I chose exit 0; because technically, it’s still a successful command.

No Comments on MySQL 5.5.12 – init script warning
Categories: linux mysql servers