If you use an unsigned MySQL int to count seconds you can count up to roughly 136 years before the field is full 
On another note I also got my Google Wave invite today, so if anyone wants to connect just add chrisjohndean@googlewave.com to your list!
Not much for me to say here that isn’t covered in the following tutorial: http://www.howtoforge.com/mysql_master_master_replication
Aside from the comments I made there, which I hope they include, if not I’ll update this post with those comments in due course
I’m looking to setup some replication between two servers and server B has none of the databases server A has, so obviously I have to populate B with A’s data.
Easy you might think, simply start the slave and issue some like:
mysql> load data from master;
But actually, in my case, this fails because of the following bug when loading master data and the fact that views exist on server A
As it says here http://bugs.mysql.com/bug.php?id=20596
[30 Aug 2006 20:36] Trudy Pelzer
Since the current implementation of LOAD DATA FROM MASTER and LOAD TABLE FROM MASTER is very limited, these statements are deprecated in versions 4.1, 5.0 and 5.1 of MySQL. We will introduce a more advanced technique (online backup) in a future (>5.1) version, that will have the added advantage of working with more storage engines.
Due to the deprecated status of the statements, this bug will not be fixed. For 5.1 and earlier, the recommended alternative solution to using LOAD DATA|TABLE FROM MASTER is mysqldump on the master piped (or output copied) to the mysql client on the slave. This also has the advantage of working for all storage engines.
A deprecation warning will be added for these statements in 4.1, 5.0 and 5.1 shortly.
Now there’s a number of ways around this e.g. exporting the views table, dropping them, doing load data from master then re-importing them or just copying the mysql data dir’s from A to B, or using mysql dump as suggested above, but this shouldn’t be necessary!
This is a bug that’s been known about by MySQL for almost 3 years, should be a fairly simple thing to fix really and hasn’t been. I don’t remember seeing any deprecation warning in the latest version of MySQL and have wasted more time as a result.
Hopefully now SUN seem to be taking a proper hold of MySQL some of these more minor but pain in the ass issues will start to be fixed!!!
So, I needed to upgrade MySQL on our development boxes today and I was met by a little surprise from the RPM program…
Basically it won’t do an upgrade as the vendor has changed from being MySQL AB to Sun Microsystems, and as a result I have to do a complete uninstall and re-install manually…
Ho hum, I know it’s a small issue and for the best, but it’s still a pain in the ass when something silly such as vendor name change wastes time in what would otherwise be a quick and simple upgrade.
So anyway as I’m going through it the following might be useful to you if you have to do the same any time soon.
First download all the current MySQL packages you need:
$hell> mkdir mysql-5.1.34
$hell> cd mysql-5.1.34
Then stop all running MySQL Process:
$hell> /etc/init.d/myst stop
Then find all the MySQL packages you need to remove by running:
$hell> rpm -qa | grep -i '^mysql-'
Then uninstall each e.g.:
$hell> rpm -e MySQL-client-community-5.1.29-0.rhel4
Then re-install all the new ones you just downloaded e.g.:
$hell> rpm -i MySQL-shared-community-5.1.34-0.rhel4.i386.rpm
Then run the MySQL upgrade program to do the final checks and upgrade the MySQL system database if necessary:
$hell> /usr/bin/mysql_upgrade -uroot -p
And that’s it, all should work nicely again
Remember though, you shouldn’t upgrade between major versions that aren’t in sequence. i.e. Don’t upgrade from MySQL from 4.0 to 5.1 as the additions to the software made in 4.1, 5.0, etc. can be lost by skipping these intermediate upgrades.
I can across a few other funky things in MySQL 5.1 today that I thought it might be worth telling you about in case you ever come across them too.
This time I was partitioning a number of large tables in and initially started to get the same weird errors as I did before when stupid queries were running away with themselves due to lack of temp space.
When you partition a table, MySQL seems to build a partitioned copy of it on the file system before swapping to that table for general use – which seems like a fair way to go, but if you don’t have enough temp space for the new table to be built in you get issues similar to those I discussed here, and you can get round them in the same way.
In my case, when doing this kind of maintenance I now add an ‘overflow’ path to the tmpdir variable which is basically a dir on part of the local filesystem with a large chunk of free space on it, but that isn’t on the same partition as the MySQL tables themselves.
This allows these operations to spill over when they need to without causing a lot of hassle – be warned though, it’s not generally a good idea to use a fileshare on a NAS for this procedure! I don’t know how well it’d work with a SAN as I don’t have one to play with, but doing it on a NAS will be REALLY slow in most cases and may cause other issues as a result.
But anyway that’s not the main thing I wanted to talk about.
What I wanted to talk about here is all your databases suddenly apparently vanishing altogether from MySQL after you’ve implemented some a partitioning scheme. I guess the same would apply if you suddenly added a load of new databases/database tables to your MySQL setup too.
At the same time as vanishing databases I’ve also seen errors where MySQL reports that it cannot open the directory on the file system that a particular database resides in, and other similar filesystem related error messages.
What causes this to happen? Well it appears to be the a combination of the max number of connections, the max files mysql can open and the table cache, which dictates the number of files MySQL can have open at any one time.
Basically what seems to happen is that when table cache gets full, MySQL essentially dies not being able to open any more database files, and therefore cannot access any more information either. BUT because the main operation on the server is not interrupted it seems that the MySQL process doesn’t die - it just continues to run but without access to any information, as if none of it ever existed!
This tends to happen after partitioning has been done because a partitioning scheme can result in a large number of new database data files being produced – after all, that’s all it’s doing, breaking one massive single myISAM file down into more manageable chunks.
Also another reason it tends to happen after partitioning is because partitioning came in in MySQL 5.1 and in MySQL 5.1 the name of the table cache server variable (at least) changed from table_cache to table_open_cache. So, if your my.cnf or my.ini still references table_cache and your running 5.1.3 upwards you’ll not actually be setting this value any more and as a result the server will revert to its default value – namely 64 – a tiny amount.
I have seen recommendations that you should set this value to around to 2048 for a lot of systems (which seems a bit arbitrary really), but the way to determine what kind of numbers you should be using here comes from analysing the results of the opened_files server status variable traded off against the max connections, and whatever the table cache is currently set to - see the resources links below for more info on this – you can access this status variable and a few other useful related parameters by running:
SHOW STATUS LIKE '%open%';
Issues can also ensue when the number of files opened by the server exceeds/hits the limit of the total number of files the operating system allows any one user to open, and if this actually appears to be the case you need to check the docs for your OS in order to up this limit.
To get an idea of the maximum number of tables mysql might look to open at any one time you can count the number of files in the mysql data dir - this dir might also contain other junk like bin logs, error logs, etc. but for a rough upper limit this will do it:
ls -1R | wc -l
If your data dir is polluted with logs then you can filter this kind of a count through grep and add up the results:
ls -1R | grep *.MYI | wc -l
ls -1R | grep *.MYD | wc -l
To find out the number of files mysql has open currently you can also do this:
$shell> lsof | grep mysql | wc –l
You can find out the number of max open files your OS supports by running this:
$shell> cat /proc/sys/fs/file-max
Some resources on these issues:
http://dev.mysql.com/doc/refman/5.0/en/table-cache.html
http://forums.mysql.com/read.php?35,168304,168304
http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_open-files-limit
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_table_open_cache
http://dev.mysql.com/doc/refman/5.0/en/not-enough-file-handles.html
http://www.linuxquestions.org/questions/linux-general-1/number-of-files-in-directory-274630/
http://www.techiesabode.com/article/read_article_w.php?article_id=2
...and aside from anything said in the above ad, they run everything on Green Energy which I think's great!
Also Find Me On