Mysql problems when copying G2 to "new" server

Hi,

I had perfectly good G2 gallery in Fedora 9, but I wanted
to upgrade the server to Fedora 11, and I did a fresh install
to new disk, putting the old disk to external USB box.

Before I shut down the server with Fedora 9 for the last time,
it was up-to-date (yum update -> nothing to install, and
Gallery 2 admin said all the modules were the latest available).

After F11 install and initial configuration, I copied the
database and data directory along these lines:

# yum install gallery2
# echo that installed G2 into /usr/share/gallery2
# echo but that G2 won't be used at all; then I
# echo edited relevant parts of /etc/httpd/conf.d/
# /etc/init.d/mysqld stop
# fsck.ext3 -f /dev/sdb1
# echo fsck did not find any error or problems
# mkdir /mnt/fedora9
# mount -r /dev/sdb1 /mnt/fedora9
# cd /mnt/fedora9/var/lib/mysql
# tar cpf - Gallery2 | ( cd /var/lib/mysql ; tar xpf - )
# chown -R mysql.mysql /var/lib/mysql/Gallery2
# cd /mnt/fedora9/path/to/G2-data-dir
# tar cpf - G2-data | ( cd /new/path/to/G2-data-dir ; tar xpf - )
# chown -R apache.apache /new/path/to/G2-data-dir/G2-data
# cd /mnt/fedora9/var/www/html
# tar cpf - gallery | ( cd /var/www/html ; tar xpf - )
# chown -R apache.apache /var/www/html
# /etc/init.d/mysqld start
# echo set up root@localhost mysql password (here "secret-password")
# mysql> GRANT ALL ON Gallery2.* TO root@localhost IDENTIFIED BY 'secret-password';

So gallery configuration was also copied from the old server.

BUT, I cannot get mysql work with the Gallery 2 database.

/var/log/mysqld.log has entries like this:

091018 22:40:05 [ERROR] Cannot find or open table Gallery2/g2tab_UserGroupMap from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.

And here is how it loks like in mysql:

# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.37 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Gallery2 |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)

mysql> use Gallery2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show grants;
+---------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '0123456789abcdef' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `Gallery2`.* TO 'root'@'localhost' |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show tables;
+-----------------------------+
| Tables_in_Gallery2 |
+-----------------------------+
| g2tab_AccessMap |
| g2tab_AccessSubscriberMap |
| g2tab_AlbumItem |
| g2tab_AnimationItem |
| g2tab_CacheMap |
| g2tab_ChildEntity |
| g2tab_Comment |
| g2tab_CustomFieldMap |
| g2tab_DataItem |
| g2tab_Derivative |
| g2tab_DerivativeImage |
| g2tab_DerivativePrefsMap |
| g2tab_DescendentCountsMap |
| g2tab_Entity |
| g2tab_EventLogMap |
| g2tab_ExifPropertiesMap |
| g2tab_ExternalIdMap |
| g2tab_FactoryMap |
| g2tab_FailedLoginsMap |
| g2tab_FileSystemEntity |
| g2tab_G1MigrateMap |
| g2tab_Group |
| g2tab_ImageBlockCacheMap |
| g2tab_ImageBlockDisabledMap |
| g2tab_Item |
| g2tab_ItemAttributesMap |
| g2tab_ItemHiddenMap |
| g2tab_LinkItem |
| g2tab_Lock |
| g2tab_MaintenanceMap |
| g2tab_MimeTypeMap |
| g2tab_MovieItem |
| g2tab_PermissionSetMap |
| g2tab_PhotoItem |
| g2tab_PluginMap |
| g2tab_PluginPackageMap |
| g2tab_PluginParameterMap |
| g2tab_RatingCacheMap |
| g2tab_RatingMap |
| g2tab_RecoverPasswordMap |
| g2tab_Schema |
| g2tab_SequenceEventLog |
| g2tab_SequenceId |
| g2tab_SequenceLock |
| g2tab_SessionMap |
| g2tab_ThumbnailImage |
| g2tab_TkOperatnMap |
| g2tab_TkOperatnMimeTypeMap |
| g2tab_TkOperatnParameterMap |
| g2tab_TkPropertyMap |
| g2tab_TkPropertyMimeTypeMap |
| g2tab_UnknownItem |
| g2tab_User |
| g2tab_UserGroupMap |
| g2tab_WatermarkImage |
+-----------------------------+
55 rows in set (0.01 sec)

mysql> describe g2tab_User;
ERROR 1146 (42S02): Table 'Gallery2.g2tab_User' doesn't exist
mysql> select * from g2tab_Schema;
ERROR 1146 (42S02): Table 'Gallery2.g2tab_Schema' doesn't exist
mysql> quit;
Bye
# ll /var/lib/mysql/Gallery2/g2tab_Schema.*
-rw-rw---- 1 mysql mysql 8846 2009-07-01 08:45 /var/lib/mysql/Gallery2/g2tab_Schema.frm
# ll /var/lib/mysql/Gallery2/g2tab_User.*
-rw-rw---- 1 mysql mysql 8870 2009-07-01 08:45 /var/lib/mysql/Gallery2/g2tab_User.frm
# exit

Here is gallery configuration:

# cat /var/www/html/gallery/config.php | grep -v ^"/\*" | grep -v " \*" | grep -v ^$
<?php
@ini_set('display_errors', 0);
if (!isset($gallery) || !method_exists($gallery, 'setConfig')) {
exit;
}
$gallery->setConfig('setup.password', 'hidden-password');
$gallery->setConfig('data.gallery.base', '/new/path/to/G2-data-dir/G2-data/');
$storeConfig['type'] = 'mysqli';
$storeConfig['hostname'] = 'localhost';
$storeConfig['database'] = 'Gallery2';
$storeConfig['username'] = 'root';
$storeConfig['password'] = 'secret-password';
$storeConfig['tablePrefix'] = 'g2tab_';
$storeConfig['columnPrefix'] = 'g2col_';
$storeConfig['usePersistentConnections'] = false;
$gallery->setConfig('storage.config', $storeConfig);
$gallery->setDebug(false);
$gallery->setProfile(false);
$gallery->setConfig('mode.maintenance', false);
$gallery->setConfig('mode.embed.only', false);
$gallery->setConfig('allowSessionAccess', false);
$gallery->setConfig('galleryBaseUrl', '');
$gallery->setConfig('baseUri', 'http://host.domain.com/gallery/main.php');
?>
# exit

What is this? mysql says the tables donät exist, but they are there...

Old disk (F9) was ext3, now f11 has ext4 (except separate /boot partition
which is ext3).

Kernel and many other packages are newer in F11, but I thought everything
would be quite compatible.

What to do? Swapping the old disk back and continutig F9 does not sound
good to me...

-Paavo

Thanks nivekiam, this turned out to be quite easy after all.

I shut down mysqld, replaced new/F11 /var/lib/mysql with old/F9 /var/lib/mysql,
then started mysqld (run OK, but several errors about old content, suggested
running mysql_upgrade, copy paste from /var/log/mysqld.log below) and dumped
Gallery2 database to a sql file: "mysqldump -p Gallery2 > /root/G2-dump.sql".

Then I moved new/F11 /var/lib/mysql in place again, and created Gallery2
database "mysqladmin -u root -p create Gallery2", and then read in all the data
with "mysql -u root -p Gallery2 < /root/G2-dump.sql".

-Paavo

091020 08:49:14 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
091020 8:49:14 [Note] Plugin 'ndbcluster' is disabled.
/usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist
091020 8:49:14 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
091020 8:49:15 InnoDB: Started; log sequence number 1 2843312333
091020 8:49:15 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
091020 8:49:15 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. Created with MySQL 50051, now running 50137. Please use mysql_upgrade to fix this error.
091020 8:49:15 [ERROR] mysql.user has no `Event_priv` column at position 29
091020 8:49:15 [ERROR] Cannot open mysql.event
091020 8:49:15 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
091020 8:49:15 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.37' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution