Search code examples
mysqlmariadbinnodbmyisamrsyslog

MariaDB - online move/archive tables

We have a script that "rotates"/archive the Syslog tables in MySQL. This script:

  • at Linux level, renames the "MyISAM" tables files then compress them then
  • inside MySQL, rename these tables

The 2 steps are "online". No MySQLd restart is required.

Now I built a new Syslog database in MariaDB (Debian Stretch). The tables are using InnoDB and not MyISAM. This script fails at the 2nd execution to rename the table inside MySQL after moving the file:

ERROR 1050 (42S01): Table 'SystemEvents_1' already exists

A reference of the table is kept somewhere (tablespace internal system table?) which prevents from doing that.

My question: would it work if I migrate my tables to the ARIA engine with transactional=0?

Thanks, Vince

Solution

  • I think it is no longer possible. I converted my tables to MyISAM (and even Aria with transactional=0) and had the same error message. I think the best is to use mysqldump to export the tables instead of directly renaming the filesystem files. Less convenient but mysqldump will always work regardless the choosen engine.