maintainDB.sh restore error - incorrect key file for table 'test_info'

I'm currently running 6.0.1 on my production server and want to test out 6.5 before we migrate to the new release.  I took a backup using the maintainDB.sh script.  On another system, I installed 6.0.1 and ran maintainDB.sh restore but it eventually fails.  Here's the output of the script:

uxoslnxbld3(bsmith): ./maintainDB.sh restore -T /scratch/bsmith/database.mysql.properties.dist -F /usr1/TeamCity_Backup_20110815_115439.zip
TeamCity maintenance tool. Copyright 2010 JetBrains s.r.o. All Rights Reserved
Command line arguments: restore -T /scratch/bsmith/database.mysql.properties.dist -F /usr1/TeamCity_Backup_20110815_115439.zip
Using TeamCity data directory: /usr1/TeamCityData
Restoring from backup file: /usr1/TeamCity_Backup_20110815_115439.zip
Backup created by TeamCity version:
Version: 6.0.1
Build number: 15816
Data format version: 418
Using explicitly specified target database settings: /scratch/bsmith/database.mysql.properties.dist
Using target database
database type: MYSQL
database driver: <not specified>
connection: jdbc:mysql://localhost:3306/teamcity
login as: teamcity
Connected to the Target DB successfully.
Starting restore
Opening zip file: /usr1/TeamCity_Backup_20110815_115439.zip
Using character set UTF-8 for database import.
Restoring Configuration
Restoring Database
Creating tables
Importing data...
importing table: action_history
importing table: agent
importing table: agent_build_type_access
importing table: agent_sources_version
importing table: agent_type
importing table: agent_type_info
importing table: agent_type_param
importing table: agent_type_runner
importing table: agent_type_vcs
importing table: archived_projects
importing table: audit_additional_object
importing table: backup_builds
importing table: backup_info
importing table: build_artifact_dependency
importing table: build_checkout_rules
importing table: build_data_storage
importing table: build_dependency
importing table: build_labels
importing table: build_queue
importing table: build_revisions
importing table: build_runtime_info
importing table: build_set_tmp
importing table: build_state
importing table: build_type_vcs_change
importing table: canceled_info
importing table: cloud_image_without_agent
importing table: cloud_started_instance
importing table: comments
importing table: compiler_output
importing table: db_version
importing table: default_build_parameters
importing table: deleted_build_types
importing table: downloaded_artifacts
importing table: duplicate_diff
importing table: duplicate_fragments
importing table: duplicate_results
importing table: duplicate_stats
importing table: failed_tests_output
importing table: final_artifact_dependency
importing table: fragments
importing table: history
importing table: ignored_tests
importing table: inspection_data
importing table: inspection_diff
importing table: inspection_fixes
importing table: inspection_info
importing table: inspection_results
importing table: inspection_stats
importing table: light_history
importing table: meta_file_line
importing table: paused_build_types
importing table: personal_build_relative_path
importing table: personal_vcs_changes
importing table: personal_vcs_history
importing table: project_files
importing table: remember_me
importing table: remote_run_build_types
importing table: responsibilities
importing table: run_configuration_policy
importing table: running
importing table: server
importing table: server_property
importing table: single_row
importing table: stats
importing table: stats_publisher_state
importing table: tc_build_tags
importing table: test_failure_rate
importing table: test_info
importing table: test_info_trunk
importing table: test_names
importing table: user_blocks
importing table: user_build_parameters
importing table: user_notification_events
importing table: user_projects_order
importing table: user_projects_visibility
importing table: user_property
importing table: user_roles
importing table: user_watch_type
importing table: usergroup_notification_events
importing table: usergroup_roles
importing table: usergroup_subgroups
importing table: usergroup_users
importing table: usergroup_watch_type
importing table: usergroups
importing table: users
importing table: vcs_changes
importing table: vcs_history
importing table: vcs_root_versions
importing table: vcs_settings_hash
importing table: working_server
Creating indices...
index agent_name_ui
index agent_host_address
index agent_authorization_token
index agent_agent_type_id
index running_state_id
index build_state_mod_id
index build_state_build_type_id
index build_state_build_id
index build_artif_dep_state_id
index final_artif_dep_state_id
index agent_build_type_access_ui
index agent_bt_access_agent_id
index agent_bt_access_bt_id
index vcs_history_root
index vcs_history_user
index build_type_vcs_change_ui
index vcs_changes_index
index vcs_personal_changes_index
index test_name_id_idx
Finishing
SQL error when doing: Executing a SQL statement
SQL query: create index test_name_id_idx on test_info (test_name_id)
SQL exception: Incorrect key file for table 'test_info'; try to repair it
jetbrains.buildServer.serverSide.db.UnexpectedDBException: SQL error when doing: Executing a SQL statement
SQL query: create index test_name_id_idx on test_info (test_name_id)
SQL exception: Incorrect key file for table 'test_info'; try to repair it
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2512)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:781)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:624)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at jetbrains.buildServer.serverSide.db.DBFunctions.executeSimpleInternal(DBFunctions.java:570)
at jetbrains.buildServer.serverSide.db.DBFunctions.executeSimpleInternal(DBFunctions.java:553)
at jetbrains.buildServer.serverSide.db.DBFunctions.executeDdls(DBFunctions.java:518)
at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor.createIndices(RestoreProcessor.java:757)
at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor.access$200(RestoreProcessor.java:38)
at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor$3.run(RestoreProcessor.java:742)
at jetbrains.buildServer.serverSide.db.DBFacade.withDB(DBFacade.java:301)
at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor.createIndices(RestoreProcessor.java:739)
at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor.restoreDatabase(RestoreProcessor.java:433)
at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor.runProcess(RestoreProcessor.java:115)
at jetbrains.buildServer.serverSide.maintenance.AbstractMaintenanceProcess.run(AbstractMaintenanceProcess.java:152)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: Incorrect key file for table 'test_info'; try to repair it
... 24 more
Restoring finished with errors
Critical error has occurred during command execution.

The error seems reproducible.  I will try it again with a newer backup.   I also haven't tried to do a direct 6.0.1 -> 6.5.3 migration as I  think I have encountered errors with that approach.

9 comments
Comment actions Permalink

Well, I tried a couple more times and the error is reproducible.  I also tried going from 6.0.1 to 6.5.3 and got a little further before failing with the below error.  I'm using MySQL 5.5.15.
/usr1/mysql/bin/mysql  Ver 14.14 Distrib 5.5.15, for linux2.6 (i686) using readline 5.1

This is not good. It does not seem that we have a working backup strategy for TeamCity.


Creating indices...
    index agent_name_ui
    index agent_host_address
    index agent_authorization_token
    index agent_agent_type_id
    index running_state_id
    index build_state_mod_id
    index build_state_build_type_id
    index build_state_build_id
    index build_artif_dep_state_id
    index final_artif_dep_state_id
    index agent_build_type_access_ui
    index agent_bt_access_agent_id
    index agent_bt_access_bt_id
    index vcs_history_root
    index vcs_history_user
    index build_type_vcs_change_ui
    index vcs_changes_index
    index vcs_personal_changes_index
    index test_name_id_idx
SQL query executed during 279 seconds and then failed: create index test_name_id_idx on test_info (test_name_id)
java.sql.SQLException: Incorrect key file for table 'test_info'; try to repair it
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2512)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:781)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:624)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at jetbrains.buildServer.serverSide.db.DBFunctions.executeSimpleInternal(DBFunctions.java:555)
    at jetbrains.buildServer.serverSide.db.DBFunctions.executeSimpleInternal(DBFunctions.java:539)
    at jetbrains.buildServer.serverSide.db.DBFunctions.executeParsedDdls(DBFunctions.java:504)
    at jetbrains.buildServer.serverSide.db.DBFunctions.executeDdls(DBFunctions.java:484)
    at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor.createIndices(RestoreProcessor.java:752)
    at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor.access$200(RestoreProcessor.java:38)
    at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor$3.run(RestoreProcessor.java:737)
    at jetbrains.buildServer.serverSide.db.DBFacade.withDB(DBFacade.java:299)
    at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor.createIndices(RestoreProcessor.java:734)
    at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor.restoreDatabase(RestoreProcessor.java:433)
    at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor.runProcess(RestoreProcessor.java:115)
    at jetbrains.buildServer.serverSide.maintenance.AbstractMaintenanceProcess.run(AbstractMaintenanceProcess.java:152)
    at java.lang.Thread.run(Thread.java:619)
Finishing
Unexpected exception SQLException: SQL error when doing: Executing a SQL statement
SQL query: create index test_name_id_idx on test_info (test_name_id)
SQL exception: Incorrect key file for table 'test_info'; try to repair it
jetbrains.buildServer.serverSide.db.UnexpectedDBException: Unexpected exception SQLException: SQL error when doing: Executing a SQL statement
SQL query: create index test_name_id_idx on test_info (test_name_id)
SQL exception: Incorrect key file for table 'test_info'; try to repair it
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2512)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:781)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:624)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at jetbrains.buildServer.serverSide.db.DBFunctions.executeSimpleInternal(DBFunctions.java:555)
    at jetbrains.buildServer.serverSide.db.DBFunctions.executeSimpleInternal(DBFunctions.java:539)
    at jetbrains.buildServer.serverSide.db.DBFunctions.executeParsedDdls(DBFunctions.java:504)
    at jetbrains.buildServer.serverSide.db.DBFunctions.executeDdls(DBFunctions.java:484)
    at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor.createIndices(RestoreProcessor.java:752)
    at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor.access$200(RestoreProcessor.java:38)
    at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor$3.run(RestoreProcessor.java:737)
    at jetbrains.buildServer.serverSide.db.DBFacade.withDB(DBFacade.java:299)
    at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor.createIndices(RestoreProcessor.java:734)
    at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor.restoreDatabase(RestoreProcessor.java:433)
    at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor.runProcess(RestoreProcessor.java:115)
    at jetbrains.buildServer.serverSide.maintenance.AbstractMaintenanceProcess.run(AbstractMaintenanceProcess.java:152)
    at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: Incorrect key file for table 'test_info'; try to repair it
    ... 25 more
Restoring finished with errors
Critical error has occurred during command execution.
uxoslnxbld3(bsmith):

0
Comment actions Permalink

Thinking about it some more, could this problem be caused by going from a 64-bit database to 32-bit database? I just noticed that the production database is 64-bits (solaris x64) and the prototype database is 32-bits (Linux x86).  I'm going to find a Linux x64 machine and try it there.

0
Comment actions Permalink

Using a 64-bit database, with 64-bit java on 64-bit Linux, it makes no difference. I get the same error.

So please advise on the next step.

0
Comment actions Permalink

Hello Bill,

Sorry for the delay with replying.

So far this looks like an issue with MySQL that gets reproduces by TeamCIty backup restoration.
At least I've found mentioning 1 and 2.

I'd try:
- ensuring you have enough space on the drive and in temp directory
- if you are using MyISAM, try InnoDB (it is recommended to use InnoDB anyway)

0
Comment actions Permalink

Thanks for the response, Yegor.

It appears that we're using InnoDB.  With MySQL 5.5, it's the default engine.  Our production server is using MySQL 5.1.x so the default engine is MyISAM.

If I look at the prototype server, I noticed that MySQL files are quite large:

drwx------ 6 mysql mysql       4096 Aug 18 14:33 .
drwxrwxrwx 9 mysql mysql       4096 Aug 18 14:42 ..
-rw-rw---- 1 mysql mysql 4146069504 Aug 18 17:04 ibdata1
-rw-rw---- 1 mysql mysql    5242880 Aug 18 17:04 ib_logfile0
-rw-rw---- 1 mysql mysql    5242880 Aug 18 17:04 ib_logfile1

Notice that ibdata1 is close to 4GB.
If I look at our production server, the files are much smaller:

total 41019
drwxrwxr-x   5 mysql    mysql         11 Jan 24  2011 .
drwxrwxrwx   6 root     root           9 Feb  1  2011 ..
-rw-rw----   1 mysql    mysql    5242880 Jan 22  2011 ib_logfile0
-rw-rw----   1 mysql    mysql    5242880 Dec 17  2010 ib_logfile1
-rw-rw----   1 mysql    mysql    10485760 Jan 22  2011 ibdata1
drwx------   2 mysql    root          71 Dec 17  2010 mysql
drwx------   2 mysql    mysql        273 Jan 22  2011 teamcity



It seems that we do have plenty of disk space for the database partitions as well as tmp space.

I'm going to try switching to MyISAM and see if it works better.

0
Comment actions Permalink

When I made the MYISAM storage engine be the default, I was able to successfull restore the database.  So I guess this is a MySQL INNODB bug that needs to be reported to them?

0
Comment actions Permalink

Bill,

May be the error is caused by some 4Gb size limit? Theoretically can be imposed by filesystem of MySQL itself.

We have seem performance issues for MyISAM databases, so InnoDB seems a bit more preferred. You can probably start with MyISAM and do the convertion to InnoDB later.

BTW, you can consider using file-per-table mode in MySQL which works a bit better performance-wise from our experience.

0
Comment actions Permalink

I did do the file-per-table mode with INNODB but it didn't help.  It still fails.

The test_info table is quite large (almost 4GB).

uxoslnx64bld1(bsmith): (root) ls -al test_in*
-rw-rw---- 1 mysql mysql       8722 Aug 25 18:33 test_info.frm
-rw-rw---- 1 mysql mysql 4060086272 Aug 25 20:54 test_info.ibd
-rw-rw---- 1 mysql mysql       8722 Aug 25 18:33 test_info_trunk.frm
-rw-rw---- 1 mysql mysql      98304 Aug 25 18:33 test_info_trunk.ibd

There are no file system limits that I'm aware of. I was able to create an 8GB tar ball without any issues.  According to http://dev.mysql.com/doc/refman/4.1/en/full-table.html, MySQL supports table sizes up to 4TB on Linux.

So I guess this is a bug in MySQL and I should file a bug report with them.  Is this correct? I just wanted to confirm that there's nothing else that JetBrains can do for this issue.

0
Comment actions Permalink

Ok, I was able to successfully run the maintainDB.sh script to restore the database using InnoDB.  The key was ensuring enough space in /tmp as you suggested earlier.  I had 2+ GB space in /tmp but it seems that it wasn't enough.  I saw this web page: http://www.mysqlperformancetuning.com/a-fix-for-incorrect-key-file-for-table-mysql which made me think about the tmp issue that you suggested.  So I reconfigured MySQL using --tmpdir=<large directory> to point it at a large partition containing 30+ GB of space and this time, it ran successfully.

Thanks for the help.

0

Please sign in to leave a comment.