Date:Posted By:Category:mariadb, perlTag:

In my first post on this blog I try to explain how to run an efficient wordpress setup in terms of performance by going light-weight. Along the way I converted the wordpress database to the Maria database engine. I also explain how to change your tables from InnoDB to Maria.

After having it switched off, InnoDB leaves traces behind in the information_schema. A quick list shows me these tables

MariaDB [(NONE)]> SELECT TABLE_NAME,ENGINE,TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_NAME LIKE '%INNO%';
+--------------------------------+--------+-------------+
| TABLE_NAME                     | ENGINE | TABLE_TYPE  |
+--------------------------------+--------+-------------+
| INNODB_BUFFER_POOL_PAGES       | MEMORY | SYSTEM VIEW |
| INNODB_CMP                     | MEMORY | SYSTEM VIEW |
| INNODB_RSEG                    | MEMORY | SYSTEM VIEW |
| INNODB_BUFFER_POOL_PAGES_INDEX | MEMORY | SYSTEM VIEW |
| INNODB_TRX                     | MEMORY | SYSTEM VIEW |
| INNODB_SYS_TABLES              | MEMORY | SYSTEM VIEW |
| INNODB_LOCK_WAITS              | MEMORY | SYSTEM VIEW |
| INNODB_CMPMEM_RESET            | MEMORY | SYSTEM VIEW |
| INNODB_LOCKS                   | MEMORY | SYSTEM VIEW |
| INNODB_CMPMEM                  | MEMORY | SYSTEM VIEW |
| INNODB_TABLE_STATS             | MEMORY | SYSTEM VIEW |
| INNODB_SYS_INDEXES             | MEMORY | SYSTEM VIEW |
| INNODB_CMP_RESET               | MEMORY | SYSTEM VIEW |
| INNODB_BUFFER_POOL_PAGES_BLOB  | MEMORY | SYSTEM VIEW |
| INNODB_INDEX_STATS             | MEMORY | SYSTEM VIEW |
+--------------------------------+--------+-------------+
15 ROWS IN SET (0.00 sec)

Even if they aren’t in the INNODB format, they are lingering around and now with the InnoDB feature turned off, you cannot access some of them anymore, this made my simple backup perl script fail. See what happens when I try to SELECT from 2 of those.

MariaDB [(NONE)]> SELECT * FROM information_schema.INNODB_LOCK_WAITS;
Empty SET, 1 warning (0.00 sec)

MariaDB [(NONE)]> SHOW warnings;
+---------+------+------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                    |
+---------+------+------------------------------------------------------------------------------------------------------------+
| Warning | 1012 | InnoDB: SELECTing FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS but the InnoDB storage engine IS NOT installed |
+---------+------+------------------------------------------------------------------------------------------------------------+
1 ROW IN SET (0.00 sec)
MariaDB [(NONE)]> SELECT * FROM information_schema.INNODB_INDEX_STATS;
ERROR 2013 (HY000): Lost connection TO MySQL server during query

Ok, 2 different outcomes here, the first is just a warning, scripts/php will treat that as such and with good coding you have that trapped. The second one is a problem. That’s where the backup script failed, arguably this could of course be trapped in the code. The point is to solve the backup issue because this table is where it stopped working, in fact, it’s one of the tables as there are more like that.

fixing the backup issue in mysql_backup.pl

I hardcoded a quick fix into the backup script to skip tables with a INNODB pattern. Probably not the best fix ever but now the backup works again. This perl script is great for small/medium dumps. I use it all the time for those quick mysql databases that run small apps. I think it’s fairly safe to skip those tables from the backup since I don’t use INNODB anymore. Unless some application has a table that has INNODB in the name it should not have impact. The script ends nicely skipping those unwanted tables.