…Are da bomb!
I can’t believe I have lived without SP’s until MySQL 5.0. I mean, it’s like incredibly easy now to make code more efficient and productive.
Case in point. We have this Radius authentication server. Where Dialup and DSL users on our system get authenticated. The queries to get people online have been getting more and more complex as we have grown. First we added content filtering, then we want to add monthly timeouts for users..but then we have to account for users that have purchased extra hours for this month.. It’s honestly been a nightmare to deal with. Oh and the biggest problem.. let me tell you.. is the fact that users are inconsistent with their login usernames. joeuser@infowest.com could login as joeuser@infowest.com or simply joeuser. So I have to account for that. Not a biggie, but then we have these @netutah.com users that we got a few years back so it adds even more code to the Radius server config file. Then to top it all off we have these ’special dialup’ users that are broadband accounts that need to dialup while on the road..but want to keep their DSL still connected. So, that means that there’s another exception.. ANYWAY, it turned out to be like 6 different super-big queries that the system would run max per user authentication attempt.
Read more
5.0.17 snapshot is working just fine with no crashes or errors.. yet.
I dumped my databases from my 4.1.x server, scp’d them over to the new box, and restored. All looks good. Had I done this originally (tuesday night), I would have saved myself a bit of heartache and not had to deal with the InnoDB problem nor the INSERT DELAYED. But, oh well, at least now it works.
I think I have it figured out. It was a problem with the InnoDB from my 4.1.x installation when I copied it over to my 5.0.15 install. I dumped my InnoDB data files, logs, and recreated them on startup (on the new box), then did a restore from a dump of the RT (ticketing system) database. This seemed to fix the problem.
Weird thing was, before I dumped my InnoDB data files, I tried dumping the database and restoring it from dump. No go. I couldn’t get it to insert the first row in the first table.. So I did the above. Oh and then I tweaked some settings on my InnoDB part of my.cnf. Here’s some of them for future reference. Oh and I was able to recreate my InnoDB files correctly (i had forgot about the auto-extend file so one of my innodb data files before got up to 1102MB before I created a new one.. so now I have 3×1024MBs and a 512MB that can autoextend to 1024MB max.. much cleaner)
innodb_log_files_in_group = 3
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_log_archive = 0
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 32M
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_lock_wait_timeout = 60
Anyway, tonight will be the test. Shall it work? Shall everything work? I sure hope so. I’m still running 5.0.17 (CVS snapshot from last night).. I think I’ll keep it around for the time being.
Stay tuned.. More later..
More issues with MySQL 5.0.15 crashing… not the same as before.. but a different type of crashing.
Time to try the daily snapshot and enable debugging. 
Found out the bug is reproducable on tables that I copied over from 4.1.x days. It only seems to crash on ‘INSERT DELAYED’ queries. A quick look of the Bugs DB on MySQL’s site reveals this bug:
http://bugs.mysql.com/bug.php?id=13707 – Server crash with INSERT DELAYED on MyISAM table
I applied the patch and things work beautifully now on my FreeBSD 6 box and MySQL5.0.15.
Now to apply this patch to my other MySQL 5.0.15 boxes… easier than waiting for 5.0.16 and hoping I dont get a crash.
Last night (or was it this morning?), I had the same issues as the night preceeding. MySQL 5.0.15 kept crashing over and over and over again. However, this only happened when I allowed network connections in. When I kept a ‘deny tcp from any to me 3306′ in the firewall table, mysql worked just happy as can be.
Todays project. Recompile with debug enabled, figure out what in the heck is causing it to crash on network connectivity to the daemon.
At least last night I knew enough not to screw up replication when I moved back to 4.1.15 on the old box.
What a pain to get all of my MySQL 4.1.x installations up to MySQL version 5.0.15. First I have to upgrade all my client libraries, then upgrade any dependencies on the 4.1.x libraries (popper, popproxy, php-mysql, pure-ftp, etc) to reference the new library. Then I get to upgrade the MySQL server installations on my mail servers and other places to be 5.0.15 so they can slave from my ‘main’ master correctly.
Needless to say, yesterday, was a day full of upgrading MySQL. I got all of my slaves and clients done by 11pm. Then I started the move to the new MySQL 5.0.15 box running FreeBSD 6. Unfortunately, after all 30GB were copied (45 sec per gig, not bad), my new 5.0.15 process kept signal 11ing and restarting itself. After all that, my replication was screwed up when I moved back to a 4.1.11 master on the original server. Anyway, it was a pain to get slaves back up. I had to dump the data and copy to the slaves and load manually ‘load data from master’ didnt work right on 5.0.15 slave talking to a 4.1.x master.
However, I think I got my settings on my new ‘master’ correctly set. I missed some kernel stack size variables set in /boot/loader.conf
kern.maxdsiz=2147483648
kern.maxssiz=268435456
I tried setting maxdsiz to 3GB..but my machine crashed on boot. So i’ll keep it at 2gb for now.
Now I’m able to successfully run my InnoDB tests on my new master.. no crashing.. all seems to be good. Here’s hoping that tonight wont bring the same bad luck as last night.
