Hi,
first of all I will explain the reason for going Innodb. My forum database is hosted at Amazon RDS, therefore I have a limited access/configuration options. I use mysqldump to make a scheduled copy of vbulletin database. Complete dump of the database weights about 1.8GB. That and the fact that MyISAM tables are being locked while doing backup causes obvious problems... I can't use Percona xtrabackup (or any other alternative) to make a backup of hybrid database (mix of MyISAM and Innodb dables) because I don't have access to physical database files. That leaves mysqldump as my only option.
Mysqldump provides --single-transaction for Innodb tables, but you can only benefit from it if all tables uses Innodb engine. If database is hybrid, table locking will occur as --lock-tables and --single-transaction options are mutually exclusive.
There are plenty of information about converting vbulletin 4 to innodb (and I have red most of it), but I could not find any discussion about complete conversion to innodb. While experimenting myself I've already stumbled upon a few issues:
1) Converting following tables gave me "ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes"
alter table blog_text engine=InnoDB;
alter table searchcore_text engine=InnoDB;
alter table searchgroup_text engine=InnoDB;
I suppose that dropping index'es before conversion will solve this problem.
2) Converting language table gave me another error: ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs
alter table language engine=InnoDB;
Any suggestions?
Another thing that worries me is third party products and their tables. This might be a not an appropriate forum to ask questions about specific plugins, but if anyone has some experience comments are welcome.
I'm using DragonByte Tech: Advanced Post Thanks / Like (Pro) and vB Enterprise Translator.
Converting one of translator tables (alter table vbenterprisetranslator_cache_medium_af engine=InnoDB gave this error: ERROR 1071 (42000) at line 174: Specified key was too long; max key length is 767 bytes
Upon inspection of the table I found that varchar(1000) type is used.
Apart from these are there any other issues I should be aware of?
first of all I will explain the reason for going Innodb. My forum database is hosted at Amazon RDS, therefore I have a limited access/configuration options. I use mysqldump to make a scheduled copy of vbulletin database. Complete dump of the database weights about 1.8GB. That and the fact that MyISAM tables are being locked while doing backup causes obvious problems... I can't use Percona xtrabackup (or any other alternative) to make a backup of hybrid database (mix of MyISAM and Innodb dables) because I don't have access to physical database files. That leaves mysqldump as my only option.
Mysqldump provides --single-transaction for Innodb tables, but you can only benefit from it if all tables uses Innodb engine. If database is hybrid, table locking will occur as --lock-tables and --single-transaction options are mutually exclusive.
There are plenty of information about converting vbulletin 4 to innodb (and I have red most of it), but I could not find any discussion about complete conversion to innodb. While experimenting myself I've already stumbled upon a few issues:
1) Converting following tables gave me "ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes"
alter table blog_text engine=InnoDB;
alter table searchcore_text engine=InnoDB;
alter table searchgroup_text engine=InnoDB;
I suppose that dropping index'es before conversion will solve this problem.
2) Converting language table gave me another error: ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs
alter table language engine=InnoDB;
Any suggestions?
Another thing that worries me is third party products and their tables. This might be a not an appropriate forum to ask questions about specific plugins, but if anyone has some experience comments are welcome.
I'm using DragonByte Tech: Advanced Post Thanks / Like (Pro) and vB Enterprise Translator.
Converting one of translator tables (alter table vbenterprisetranslator_cache_medium_af engine=InnoDB gave this error: ERROR 1071 (42000) at line 174: Specified key was too long; max key length is 767 bytes
Upon inspection of the table I found that varchar(1000) type is used.
Apart from these are there any other issues I should be aware of?
Comment