{"id":439,"date":"2014-01-06T15:17:14","date_gmt":"2014-01-06T20:17:14","guid":{"rendered":"http:\/\/devolve.net\/blog\/?p=439"},"modified":"2015-05-21T10:09:21","modified_gmt":"2015-05-21T14:09:21","slug":"mysql-engines-constraints-keys","status":"publish","type":"post","link":"https:\/\/www.devolve.local\/mysql-engines-constraints-keys\/","title":{"rendered":"MySQL engines, constraints & keys"},"content":{"rendered":"

I wanted to see how I could improve the performance of a MySQL database with mixed table engines by converting all the MyISAM tables to InnoDB, as well as make the huge DB responsive while backing up by using mysqldump<\/code> with the --single-transaction<\/code> option. I used the following PHP script (I know, spare me):<\/p>\n

< ?php\r\n\/\/mysql_connect('127.0.0.1', 'root', 'fubar');\r\n$rsrc = mysql_connect(':\/usr\/local\/tmp\/mysql.sock', 'root', 'fubar');\r\n$dbname = 'jstest';\r\n$query = \"select table_name from INFORMATION_SCHEMA.TABLES where table_schema = '$dbname' and engine = 'MyISAM'\";\r\necho $query . \"\\n\";\r\n$rs = mysql_query($query, $rsrc);\r\nwhile ($row = mysql_fetch_array($rs)) {\r\n  $tbl = $row[0];\r\n  \/\/echo $tbl . \"\\n\";\r\n  $sql = \"ALTER TABLE $dbname.$tbl ENGINE='InnoDB'\";\r\n  echo $sql . \"\\n\";\r\n  $rs2 = mysql_query($sql, $rsrc);\r\n  $rnum = mysql_affected_rows();\r\n  echo $rnum . \" rows affected\\n\";\r\n}<\/pre>\n
After looking at the table status following the script run, there was one table which was still set to use MyISAM. In the mysql shell, I tried manually altering the one table to use InnoDB, and then needed a bunch of additional commands to smooth out the DDL problems before MySQL was happy.<\/p>\n
mysql> alter table page_info_story_activity engine='InnoDB';\r\nERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key\r\nmysql> desc page_info_story_activity;\r\n+---------------+-----------+------+-----+-------------------+-----------------------------+\r\n| Field         | Type      | Null | Key | Default           | Extra                       |\r\n+---------------+-----------+------+-----+-------------------+-----------------------------+\r\n| aid           | int(11)   | NO   | PRI | NULL              | auto_increment              |\r\n| sid           | int(11)   | NO   | PRI | NULL              |                             |\r\n| pid           | int(11)   | NO   | PRI | NULL              |                             |\r\n| status        | char(2)   | NO   |     | NULL              |                             |\r\n| date_assigned | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |\r\n+---------------+-----------+------+-----+-------------------+-----------------------------+\r\n5 rows in set (0.01 sec)\r\n\r\nmysql> show indexes from page_info_story_activity\\G\r\n*************************** 1. row ***************************\r\n        Table: page_info_story_activity\r\n   Non_unique: 0\r\n     Key_name: said\r\n Seq_in_index: 1\r\n  Column_name: sid\r\n    Collation: A\r\n  Cardinality: NULL\r\n     Sub_part: NULL\r\n       Packed: NULL\r\n         Null:\r\n   Index_type: BTREE\r\n      Comment:\r\nIndex_comment:\r\n*************************** 2. row ***************************\r\n        Table: page_info_story_activity\r\n   Non_unique: 0\r\n     Key_name: said\r\n Seq_in_index: 2\r\n  Column_name: pid\r\n    Collation: A\r\n  Cardinality: NULL\r\n     Sub_part: NULL\r\n       Packed: NULL\r\n         Null:\r\n   Index_type: BTREE\r\n      Comment:\r\nIndex_comment:\r\n*************************** 3. row ***************************\r\n        Table: page_info_story_activity\r\n   Non_unique: 0\r\n     Key_name: said\r\n Seq_in_index: 3\r\n  Column_name: aid\r\n    Collation: A\r\n  Cardinality: 94573\r\n     Sub_part: NULL\r\n       Packed: NULL\r\n         Null:\r\n   Index_type: BTREE\r\n      Comment:\r\nIndex_comment:\r\n3 rows in set (0.00 sec)\r\n\r\n-- the key \"said\" is an aggregate key formed from the \r\n-- first 3 columns: sid,pid,aid\r\n-- but sid cannot be auto_increment unless it is also primary_key\r\n\r\nmysql> alter table page_info_story_activity add primary key (aid);\r\nERROR 1062 (23000): Duplicate entry '376612' for key 'PRIMARY'\r\n\r\n-- there are dupes in aid, so I cannot set it as primary key, \r\n-- b\/c primary key is unique by definition.\r\n\r\n-- this will remove the auto_increment constraint:\r\nmysql> alter table page_info_story_activity change aid aid int(11) not null;\r\nQuery OK, 94573 rows affected (0.20 sec)\r\nRecords: 94573  Duplicates: 0  Warnings: 0\r\n\r\n-- you have to use all the options from the original DDL without the option\r\n-- that you no longer want. Kind of error prone.\r\n-- now we can add a non-primary key:\r\nmysql> alter table page_info_story_activity add key aid_key (aid);\r\nQuery OK, 0 rows affected (0.38 sec)\r\nRecords: 0  Duplicates: 0  Warnings: 0\r\n\r\n-- then add back the auto_increment:\r\nmysql> alter table page_info_story_activity change aid aid int(11) not null auto_increment;\r\nQuery OK, 94573 rows affected (1.09 sec)\r\nRecords: 94573  Duplicates: 0  Warnings: 0\r\n\r\n-- finally change to innodb:\r\nmysql> alter table page_info_story_activity engine='InnoDB';\r\nQuery OK, 94573 rows affected (0.97 sec)\r\nRecords: 94573  Duplicates: 0  Warnings: 0<\/pre>\n
Of course, I edited out a whole mess of trial and error here. The issue was that MySQL wants any column you mark as auto_increment to have its own key, it doesn’t need to be primary; but it can’t be an aggregate which is what it had originally. My solution was to simply add a non-primary key to that column, while keeping the aggregate key. Using a primary key was out of the question since there are duplicate values in the aid<\/code> column.<\/p>\n
EDIT:
\nTurns out, all I really needed to do was add the individual index, then alter the table engine. Oh well, it was educational. :-)<\/p>\n","protected":false},"excerpt":{"rendered":"
I wanted to see how I could improve the performance of a MySQL database with mixed table engines by converting all the MyISAM tables to InnoDB, as well as make the huge DB responsive while backing up by using mysqldump with the –single-transaction option. I used the following PHP script (I know, spare me): < […]\n<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[6],"tags":[34,41,36,35,26],"_links":{"self":[{"href":"https:\/\/www.devolve.local\/wp-json\/wp\/v2\/posts\/439"}],"collection":[{"href":"https:\/\/www.devolve.local\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devolve.local\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devolve.local\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devolve.local\/wp-json\/wp\/v2\/comments?post=439"}],"version-history":[{"count":3,"href":"https:\/\/www.devolve.local\/wp-json\/wp\/v2\/posts\/439\/revisions"}],"predecessor-version":[{"id":442,"href":"https:\/\/www.devolve.local\/wp-json\/wp\/v2\/posts\/439\/revisions\/442"}],"wp:attachment":[{"href":"https:\/\/www.devolve.local\/wp-json\/wp\/v2\/media?parent=439"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devolve.local\/wp-json\/wp\/v2\/categories?post=439"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devolve.local\/wp-json\/wp\/v2\/tags?post=439"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}