Archive for the ‘MySQL’ Category

How to Convert MySQL from MyISAM to InnoDB Using a Script

Thursday, September 10th, 2009

Okay.  So I need to convert MySQL database using the default MyISAM engine to the InnoDB engine.  The command is simple:

ALTER TABLE table_name ENGINE = InnoDB;

However, this command needs to be done for EVERY table.  Yes, it’s a pain in the rear…  so here’s a way to do it by scripting.

Scripting a MySQL InnoDB Engine Conversion

0) Backup your database. You should probably be doing this already.  Now’s a good time to make sure that your backups ran.

1) Create the script. You’ll need the correct permissions to query the database. Here’s the command.  Be sure to change <DATABASE_NAME> as it fits.

Linux$ mysql -p -e "show tables in <DATABASE_NAME>;" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql

2) Run the script.

Linux $ mysql --database=<DATABASE_NAME> -p < alter_table.sql

3) Verify it by running this command in mysql:

mysql> show table status;

Discussion (If You’re Interested)

The script is simply a bunch of ALTER TABLE commands for each of your tables in the database of question.

The mysql -p -e “show tables in <DATABASE_NAME>” command gets a list of the tables.  This list has a header that looks like “Tables_in_DATABASE”.  The tail command drops that first header line.  Now you have a clean list of tables.  The xargs -i echo command creates the ALTER TABLE command, inserting the table name where the squiggly braces {} are placed.   It’s now stored in alter_table.sql.

Step two simply runs the script that we stored in alter_table.  Both mysql commands will prompt for a password, based on the -p flag.

Tags: , ,
Posted in MySQL | No Comments »

Bugzilla Upgrade Error: DBD::mysql::db do failed: Incorrect table definition; there can be only one auto column and it must be defined as a key

Saturday, January 17th, 2009

While upgrading Bugzilla, I also came across this error when I ran the checksetup.pl script.

Removing index 'groups_name_idx' from the groups table...

DBD::mysql::db do failed: Incorrect table definition; there can be only one auto column and it must be defined as a key [for Statement "ALTER TABLE groups DROP PRIMARY KEY"] at Bugzilla/Install/DB.pm line 1394

Bugzilla::Install::DB::_convert_groups_system_from_groupset() called at Bugzilla/Install/DB.pm line 262

Bugzilla::Install::DB::update_table_definitions('HASH(0x8bf49c8)') called at ./checksetup.pl line 195

What does this error mean?

This error means that the script was unable to DROP the PRIMARY KEY on the groups TABLE, because that field has auto_increment turned on.

How do I fix this?

Turn off auto_increment in the groups table.

1) Look at the groups table

Linux# mysql
mysql> use bugs;
mysql> desc groups;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| bit | bigint(20) | NO | | 0 | |
| name | varchar(255) | NO | | | |
| description | text | NO | | | |
| isbuggroup | tinyint(4) | NO | | 0 | |
| userregexp | tinytext | NO | | | |
| isactive | tinyint(4) | NO | | 1 | |
| id | mediumint(9) | NO | PRI | NULL | auto_increment |
+-------------+--------------+------+-----+---------+----------------+

2) Modify the ID field of the groups table.

mysql> alter table groups modify id mediumint(9) not null;

3) Verify the changes in the groups table

mysql> desc groups;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| bit | bigint(20) | NO | | 0 | |
| name | varchar(255) | NO | | | |
| description | text | NO | | | |
| isbuggroup | tinyint(4) | NO | | 0 | |
| userregexp | tinytext | NO | | | |
| isactive | tinyint(4) | NO | | 1 | |
| id | mediumint(9) | NO | PRI | | |
+-------------+--------------+------+-----+---------+-------+

4) Re-run the checksetup.pl command

Tags: , , , ,
Posted in Bugzilla, Database, MySQL | 1 Comment »

Bugzilla Upgrade Error: DBD::mysql::db do failed: Duplicate entry ‘1-1-0-0′ for key 1 [for Statement “INSERT INTO user_group_map

Saturday, January 17th, 2009

When upgrading Bugzilla from 2.16 to 3.20, I kept getting errors like these when I ran checksetup.pl.

DBD::mysql::db do failed: Duplicate entry '1-1-0-0' for key 1 [for Statement "INSERT INTO user_group_map (user_id, group_id, isbless, grant_type) VALUES (?, ?, ?, ?)"] at Bugzilla/Install/DB.pm line 1602

Bugzilla::Install::DB::_convert_groups_system_from_groupset() called at Bugzilla/Install/DB.pm line 262

Bugzilla::Install::DB::update_table_definitions('HASH(0xa2ee9a8)') called at ./checksetup.pl line 195

What does this error mean?

This means that a MySQL database INSERT command failed, because there exists a duplicate entry in that table.

How do I fix this?

When the upgrade’s trying INSERT a column into a table and it finds an existing duplicate, we can make one of two assumptions:

A) The original entry is correct
OR
B) The new entry is correct

If you choose (A), you should be using the INSERT IGNORE command.  This command keeps the original entry.  Otherwise, if you choose (B), you should use the REPLACE command.  This command, well… replaces the original entry with the new entry.  I, personally, chose the REPLACE command.

Umm… What Exactly do I Do?

Change the code from “INSERT” to “REPLACE”.  Based on the error above, that would be line 1602 of Bugzilla/Install/DB.pm.

I had to do this multiple times.  I ran checksetup.pl, edited the relevant code, and repeated until the upgrade completed without error.

Tags: , , , ,
Posted in Bugzilla, MySQL | 1 Comment »