Transfer to new Server / Upgrade fails - sql error in migration [resolved]

We've hit a roadblock in upgrading from an old 1.5 install to a new server with 2.3 (just before the version released Aug 27th).

We've tried the process twice, with a clean install on the new server and both times we get to the "upgrading" process fails with this message:

Omeka encountered an error when upgrading your installation.

SQL error in migration: Mysqli statement execute error : Unknown column 'record_name' in 'omeka_search_texts'

{see end of this post for the 16 line error message that follows}

We think we've followed the directions correctly:
- The new server has a fresh, empty copy of the new version, with one superuser and no data records added.
- On the old machine we deactivated plugins
- From the old machine we exported the database using mysqldump
- We also copied the Archive folder to the new installation
- We then imported the mysql database to the new installation

Once we've done all this, Omeka then tries to upgrade the installation, at which point it fails.

On the new install we can see from phpmyadmin that the data has been transferred over, along with the users from the old install. We're not sure where to go next on this and are looking for help.
Thanks!

Here's the detailed error message:

Omeka encountered an error when upgrading your installation.

SQL error in migration: Mysqli statement execute error : Unknown column 'record_name' in 'omeka_search_texts'

exception 'Zend_Db_Statement_Mysqli_Exception' with message 'Mysqli statement execute error : Unknown column 'record_name' in 'omeka_search_texts'' in /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Zend/Db/Statement/Mysqli.php:214
Stack trace:
#0 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Zend/Db/Statement.php(303): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#2 [internal function]: Zend_Db_Adapter_Abstract->query('ALTER TABLE `o...')
#3 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Omeka/Db.php(79): call_user_func_array(Array, Array)
#4 /Library/Server/Web/Data/Sites/Default/dm/application/migrations/20120813000000_changeToRecordType.php(22): Omeka_Db->__call('query', Array)
#5 /Library/Server/Web/Data/Sites/Default/dm/application/migrations/20120813000000_changeToRecordType.php(22): Omeka_Db->query('ALTER TABLE `o...')
#6 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Omeka/Db/Migration/Manager.php(231): changeToRecordType->up()
#7 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Omeka/Db/Migration/Manager.php(111): Omeka_Db_Migration_Manager->_migrateUp(Object(DateTime))
#8 /Library/Server/Web/Data/Sites/Default/dm/application/controllers/UpgradeController.php(41): Omeka_Db_Migration_Manager->migrate()
#9 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Zend/Controller/Action.php(516): UpgradeController->migrateAction()
#10 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Zend/Controller/Dispatcher/Standard.php(308): Zend_Controller_Action->dispatch('migrateAction')
#11 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#12 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Zend/Application/Bootstrap/Bootstrap.php(105): Zend_Controller_Front->dispatch()
#13 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Zend/Application.php(382): Zend_Application_Bootstrap_Bootstrap->run()
#14 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Omeka/Application.php(79): Zend_Application->run()
#15 /Library/Server/Web/Data/Sites/Default/dm/admin/index.php(28): Omeka_Application->run()
#16 {main}

What does the omeka_search_texts table look like (what are the columns) in your dump of the old database?

John, thanks for looking into this with us.

I'm not quite sure what you're asking for, but on the new install where upgrading gives an error I can use phpmyadmin to see what was imported.

Here are the omeka_search_texts details. Is there a way to attach a pdf or more readable file?

[line for omeka_search_texts from phpmyadmin]
omeka_search_texts Browse Browse Structure Structure Search Search Insert Insert Empty Empty Drop Drop 0 MyISAM utf8_unicode_ci 1 KiB -

[structure of the table for omeka_search_texts]
# Name Type Collation Attributes Null Default Extra Action
1 idPrimary int(10) UNSIGNED No None AUTO_INCREMENT Change Change Drop Drop
Primary Primary
Unique Unique
Index Index
Spatial Spatial
Fulltext Fulltext
Distinct values Distinct values
2 record_type varchar(30) utf8_unicode_ci No None Change Change Drop Drop
Primary Primary
Unique Unique
Index Index
Spatial Spatial
Fulltext Fulltext
Distinct values Distinct values
3 record_id int(10) UNSIGNED No None Change Change Drop Drop
Primary Primary
Unique Unique
Index Index
Spatial Spatial
Fulltext Fulltext
Distinct values Distinct values
4 public tinyint(1) No None Change Change Drop Drop
Primary Primary
Unique Unique
Index Index
Spatial Spatial
Fulltext Fulltext
Distinct values Distinct values
5 title mediumtext utf8_unicode_ci Yes NULL Change Change Drop Drop
Primary Primary
Unique Unique
Index Index
Spatial Spatial
Fulltext Fulltext
Distinct values Distinct values
6 text longtext utf8_unicode_ci No None Change Change Drop Drop
Primary Primary
Unique Unique
Index Index
Spatial Spatial
Fulltext Fulltext
Distinct values Distinct values

[attempt to past from the 'print view' of the omeka_search_texts table]

Column Type Null Default Links to Comments
id (Primary) int(10) No
record_type varchar(30) No
record_id int(10) No
public tinyint(1) No
title mediumtext Yes NULL
text longtext No
Indexes

Keyname Type Unique Packed Column Cardinality Collation Null Comment
PRIMARY BTREE Yes No id 0 A No
record_name BTREE Yes No record_type A No
record_id 0 A No
text FULLTEXT No No text No

Space usage:
Data 0 B
Index 1,024 B
Total 1,024 B
Row Statistics:
Format dynamic
Rows 0
Next autoindex 1
Creation Sep 02, 2015 at 01:26 PM
Last update Sep 02, 2015 at 01:26 PM

What I was asking for was what, if anything, was contained in the MySQL dump you made of the old database, your starting point for the upgrade, about the search_texts table. This table doesn't exist in Omeka 1.5, so if the dump is just a clean dump of a 1.5 install's database, you shouldn't see this problem.

One thing I'll note: when you get a migration error like this, you should usually start over with the original dumped version of the database, not try to re-run the upgrade on the database that already failed once. If that's what you did here, that could explain why you see this error: you had some other error the first time you tried to upgrade, and this one is just the result of trying to work from a half-upgraded database.

In other words, if you delete the "new" copy of the database and re-import your dumped copy of the old one, do you get the same error when you try to upgrade? I would expect that you would get a different error which is the real problem.

John - I'm coming back to this after a week of teaching.
Consulting with our IT office and using your advice we started the server transfer/upgrade again. We cleared out all the files from the previous install and started over. Sadly Omeka It is still failing on the 'upgrade' database phase.

Here's the sequence we used:

First we deactivated plugins.

Then we saved the mysql db from the old 1.5.1 install as a zipped sql dump file. We also saved a copy of archive folder.

On the new server we installed the latest Omeka, created a DB & User and edited the db.ini file to match.

We changed the name of the archive folder to files and the files folder inside that to original and then moved to replace the files folder in the new 2.x install.

At this point localhost/dm {dm is the name of our install} brought up the initial register superuser screen. This worked.

In phpmyadmin we then imported the sql dump file.

localhost/admin now asks to upgrade the database

This fails with the same error as before (I'll post it at the end of the message).

We are not sure what to do next.
The working 1.5.1 install is on a machine that is backed up, but upgrading it worries us since if we end up with the issue we have now then we will not have any working installation.

We've thought about installing 1.5.1 on our new server and trying to import the db and then upgrading that, but the new server also has a newer version of PHP installed. We could uninstall that (?) and try getting the same version of PHP as the old server is running, but that seems like a way to introduce even more variables.

Do you have any other suggestions? We're at a bit of a loss here. We do wonder if we are doing something in the wrong order in the new install/copy files/import sql db sequence.

Any help would be most appreciated. As you may have surmised, like many smaller universities what began with a smooth setup and several years of trouble-free operation now has to be managed/upgraded by faculty with more expertise in History than CS...

(possibly helpful info)
config of the old install:
Omeka 1.5.1
PHP 5.3.8
MySQL Server: 5.5.25

confing of the new install
Omeka 2.3.1
PHP 5.5.27
MySQL Server: 5.6.26

database upgrade error:

Omeka encountered an error when upgrading your installation.

SQL error in migration: Mysqli statement execute error : Unknown column 'record_name' in 'omeka_search_texts'

exception 'Zend_Db_Statement_Mysqli_Exception' with message 'Mysqli statement execute error : Unknown column 'record_name' in 'omeka_search_texts'' in /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Zend/Db/Statement/Mysqli.php:214
Stack trace:
#0 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Zend/Db/Statement.php(303): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#2 [internal function]: Zend_Db_Adapter_Abstract->query('ALTER TABLE `o...')
#3 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Omeka/Db.php(79): call_user_func_array(Array, Array)
#4 /Library/Server/Web/Data/Sites/Default/dm/application/migrations/20120813000000_changeToRecordType.php(22): Omeka_Db->__call('query', Array)
#5 /Library/Server/Web/Data/Sites/Default/dm/application/migrations/20120813000000_changeToRecordType.php(22): Omeka_Db->query('ALTER TABLE `o...')
#6 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Omeka/Db/Migration/Manager.php(231): changeToRecordType->up()
#7 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Omeka/Db/Migration/Manager.php(111): Omeka_Db_Migration_Manager->_migrateUp(Object(DateTime))
#8 /Library/Server/Web/Data/Sites/Default/dm/application/controllers/UpgradeController.php(41): Omeka_Db_Migration_Manager->migrate()
#9 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Zend/Controller/Action.php(516): UpgradeController->migrateAction()
#10 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Zend/Controller/Dispatcher/Standard.php(308): Zend_Controller_Action->dispatch('migrateAction')
#11 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#12 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Zend/Application/Bootstrap/Bootstrap.php(105): Zend_Controller_Front->dispatch()
#13 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Zend/Application.php(382): Zend_Application_Bootstrap_Bootstrap->run()
#14 /Library/Server/Web/Data/Sites/Default/dm/application/libraries/Omeka/Application.php(79): Zend_Application->run()
#15 /Library/Server/Web/Data/Sites/Default/dm/admin/index.php(28): Omeka_Application->run()
#16 {main}

Follow-up / More Details:
Maybe this will be helpful...

We've now also attempted to install 1.5.1 on our new server. We managed to get the Archive folder transferred and the DB dump imported. The site actually comes up! But we cannot login to the Admin panel.

Every page has the following error at the bottom:
Fatal error: Uncaught exception 'Zend_Db_Statement_Mysqli_Exception' with message 'Mysqli statement execute error : Data too long for column 'id' at row 1' in /Library/Server/Web/Data/Sites/Default/om15/application/libraries/Zend/Db/Statement/Mysqli.php:214 Stack trace: #0 /Library/Server/Web/Data/Sites/Default/om15/application/libraries/Zend/Db/Statement.php(300): Zend_Db_Statement_Mysqli->_execute(Array) #1 /Library/Server/Web/Data/Sites/Default/om15/application/libraries/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array) #2 /Library/Server/Web/Data/Sites/Default/om15/application/libraries/Zend/Db/Adapter/Abstract.php(575): Zend_Db_Adapter_Abstract->query('INSERT INTO `om...', Array) #3 /Library/Server/Web/Data/Sites/Default/om15/application/libraries/Zend/Db/Table/Abstract.php(1075): Zend_Db_Adapter_Abstract->insert('omeka_sessions', Array) #4 /Library/Server/Web/Data/Sites/Default/om15/application/libraries/Zend/Session/SaveHandler/DbTable.php(356): Zend_Db_Table_Abstract->insert(Array) #5 [internal in /Library/Server/Web/Data/Sites/Default/om15/application/libraries/Zend/Db/Statement/Mysqli.php on line 214

As an experiment, we also get this error if we install 1.5.2 but don't import anything from the old install.

SOLVED! After much trial and error and several attempts we have succeeded in transferring our site from an ancient iMac to a new mac Mini under the latest MacOSX operating system.

Please watch for a separate post with a summary of our transfer and install directions...

For details on how this was resolved please see: http://omeka.org/forums/topic/upgrade-151-to-231-on-os-x-yosemite-lessons-learned#post-110430