We are hosting a few applications on our web hosting server which depend on PostgreSQL and MySQL databases. Unfortunately, some conditions caused that we need to upgrade the server from SLES9 installation to newer SLES10.
Let's compare the SLES10 distro and its predecessor according to databases they contain. The SLES10 contains MySQL server in version 5.0.x while the SLES9 is based on version.4.0.x. By the way, the MySQL 5.0.x was one of the requirements that lead us to the upgrade decision. Further, the SLES10 contains PostgreSQL server in version 8.1.x while the SLES9 contains version 7.4.x.
So, I needed to move data and related metadata from the old installation to the new one. Of course, I decided to test the scenario with data from backup. What problems did I have to solve? At first, the schema of administration database mysql which stores user privileges and so on was changed. There were added some new fields in version 5, e.g. in the table user were added fields like , Show_view_priv and so on, which aren't available in previous versions. So, simple backup command like the following one couldn't be working:
Let's compare the SLES10 distro and its predecessor according to databases they contain. The SLES10 contains MySQL server in version 5.0.x while the SLES9 is based on version.4.0.x. By the way, the MySQL 5.0.x was one of the requirements that lead us to the upgrade decision. Further, the SLES10 contains PostgreSQL server in version 8.1.x while the SLES9 contains version 7.4.x.
So, I needed to move data and related metadata from the old installation to the new one. Of course, I decided to test the scenario with data from backup. What problems did I have to solve? At first, the schema of administration database mysql which stores user privileges and so on was changed. There were added some new fields in version 5, e.g. in the table user were added fields like , Show_view_priv and so on, which aren't available in previous versions. So, simple backup command like the following one couldn't be working:
mysqldump --all-databases | gzip > /backup/mysql.gz
The reason why the script wasn't working is that it is using not complete INSERT statements like this:
INSERT INTO user VALUES ('localhost', 'test', 'test', ...);
Such a statement is trying to insert 31 values but the new schema requires the user table to have 37! So the statement will finish with the error:
To bypass such behaviour I had to fix the backup command to use complete INSERT:
#1136 - Column count doesn't match value count at row 1
To bypass such behaviour I had to fix the backup command to use complete INSERT:
mysqldump -c --all-databases | gzip > /backup/mysql.gz
After the change, there was added only -c parameter, the mysql dump containts complete INSERT statements:
INSERT INTO user (Host, User, ...) VALUES ('localhost', 'test', ...);
Such a statement is accepted by MySQL 5.0.x as well because there are explicitly defined which fields to insert. The remaining fields will stay empty or they will be assigned default values.
I think it's quite useful to remember on possible backward incompatibility between versions and to check if related tools, e.g. for database backup, provide a way how to deal with it.
The pretty much similar thing I had to solve with PostgreSQL. But it was worse because there was a problem with user data and the syntax of adequate COPY/INSERT statements. I will write about it next time.
I think it's quite useful to remember on possible backward incompatibility between versions and to check if related tools, e.g. for database backup, provide a way how to deal with it.
The pretty much similar thing I had to solve with PostgreSQL. But it was worse because there was a problem with user data and the syntax of adequate COPY/INSERT statements. I will write about it next time.
No comments:
Post a Comment