roundcube & horde database tables - InnoDB to MyISAM table conversion ?

[eva2000]

Curious if anyone knows if there's a script on WHM server to convert roundcube and horde database tables from InnoDB back to MyISAM ? Seems alot of clients I work with who have InnoDB storage engine enabled when WHM/Cpanel was installed had some of the roundcube and horde tables created using InnoDB instead of MyISAM.

Now to converting these tables from InnoDB back to MyISAM isn't as simple as ALTER TABLE tablename ENGINE=MyISAM as some of the roundcube InnoDB tables have Foriegn keys added. So you end up with error message below if you try to do normal ALTER TABLE table name method.

mysql> alter table roundcube.cache ENGINE=MyISAM;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

I wrote a script to do all the leg work in converting such InnoDB tables back to MyISAM by temp disabling foreign key checks, dropping the foreign keys on all related InnoDB tables, then doing the InnoDB to MyISAM conversion and then re-enabling foreign key checks. But then thought I'd ask, if WHM/Cpanel already has a process or script to do this conversion for InnoDB to MyISAM for particular roundcube database tables which when in InnoDB table format contain Foreign keys.

thanks

 

[cPanelTristan]

Is the only table that poses an issue roundcube.cache or are there others?

 

[eva2000]

i dumped the roundcube db schema only and egrep on CREATE TABLE and CONSTRAINT and these are following tables within roundcube db that have issues when converting from InnoDB to MyISAM

6 tables in roundcube contain foreign keys when using InnoDB storage engine

CREATE TABLE `cache` (
  CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE

CREATE TABLE `contactgroupmembers` (
  CONSTRAINT `contactgroup_id_fk_contactgroups` FOREIGN KEY (`contactgroup_id`) REFERENCES `contactgroups` (`contactgroup_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `contact_id_fk_contacts` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`contact_id`) ON DELETE CASCADE ON UPDATE CASCADE

CREATE TABLE `contactgroups` (
  CONSTRAINT `user_id_fk_contactgroups` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE

CREATE TABLE `contacts` (
  CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE

CREATE TABLE `identities` (
  CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE

CREATE TABLE `messages` (
  CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE

 

[eva2000]

Roundcube InnoDB

[email protected] [~]# ./mysqlmymonlite.sh showcreate
What is your mysql database name ? 
roundcube


Do you want to display all roundcube tables' schema (how table was created) ? [y/n]
y

Do you want save output to text file ? Answering no will output only to screen. [y/n]
n
*************************** 1. row ***************************
       Table: cache
Create Table: CREATE TABLE `cache` (
  `cache_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cache_key` varchar(128) CHARACTER SET ascii NOT NULL,
  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `data` longtext NOT NULL,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`cache_id`),
  KEY `created_index` (`created`),
  KEY `user_cache_index` (`user_id`,`cache_key`),
  CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*************************** 1. row ***************************
       Table: contactgroupmembers
Create Table: CREATE TABLE `contactgroupmembers` (
  `contactgroup_id` int(10) unsigned NOT NULL,
  `contact_id` int(10) unsigned NOT NULL DEFAULT '0',
  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  PRIMARY KEY (`contactgroup_id`,`contact_id`),
  KEY `contact_id_fk_contacts` (`contact_id`),
  CONSTRAINT `contactgroup_id_fk_contactgroups` FOREIGN KEY (`contactgroup_id`) REFERENCES `contactgroups` (`contactgroup_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `contact_id_fk_contacts` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`contact_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
*************************** 1. row ***************************
       Table: contactgroups
Create Table: CREATE TABLE `contactgroups` (
  `contactgroup_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `del` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`contactgroup_id`),
  KEY `contactgroups_user_index` (`user_id`,`del`),
  CONSTRAINT `user_id_fk_contactgroups` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*************************** 1. row ***************************
       Table: contacts
Create Table: CREATE TABLE `contacts` (
  `contact_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `del` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL DEFAULT '',
  `email` varchar(255) NOT NULL,
  `firstname` varchar(128) NOT NULL DEFAULT '',
  `surname` varchar(128) NOT NULL DEFAULT '',
  `vcard` text,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`contact_id`),
  KEY `user_contacts_index` (`user_id`,`email`),
  CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*************************** 1. row ***************************
       Table: cp_schema_version
Create Table: CREATE TABLE `cp_schema_version` (
  `version_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `version` varchar(128) NOT NULL,
  `applied` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  PRIMARY KEY (`version_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
*************************** 1. row ***************************
       Table: identities
Create Table: CREATE TABLE `identities` (
  `identity_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `del` tinyint(1) NOT NULL DEFAULT '0',
  `standard` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL,
  `organization` varchar(128) NOT NULL DEFAULT '',
  `email` varchar(128) NOT NULL,
  `reply-to` varchar(128) NOT NULL DEFAULT '',
  `bcc` varchar(128) NOT NULL DEFAULT '',
  `signature` text,
  `html_signature` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`identity_id`),
  KEY `user_identities_index` (`user_id`,`del`),
  CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
*************************** 1. row ***************************
       Table: messages
Create Table: CREATE TABLE `messages` (
  `message_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `del` tinyint(1) NOT NULL DEFAULT '0',
  `cache_key` varchar(128) CHARACTER SET ascii NOT NULL,
  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `idx` int(11) unsigned NOT NULL DEFAULT '0',
  `uid` int(11) unsigned NOT NULL DEFAULT '0',
  `subject` varchar(255) NOT NULL,
  `from` varchar(255) NOT NULL,
  `to` varchar(255) NOT NULL,
  `cc` varchar(255) NOT NULL,
  `date` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `size` int(11) unsigned NOT NULL DEFAULT '0',
  `headers` text NOT NULL,
  `structure` text,
  PRIMARY KEY (`message_id`),
  UNIQUE KEY `uniqueness` (`user_id`,`cache_key`,`uid`),
  KEY `created_index` (`created`),
  KEY `index_index` (`user_id`,`cache_key`,`idx`),
  CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*************************** 1. row ***************************
       Table: session
Create Table: CREATE TABLE `session` (
  `sess_id` varchar(40) NOT NULL,
  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `ip` varchar(40) NOT NULL,
  `vars` mediumtext NOT NULL,
  PRIMARY KEY (`sess_id`),
  KEY `changed_index` (`changed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(128) NOT NULL,
  `mail_host` varchar(128) NOT NULL,
  `alias` varchar(128) NOT NULL,
  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `last_login` datetime DEFAULT NULL,
  `language` varchar(5) DEFAULT NULL,
  `preferences` text,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`,`mail_host`),
  KEY `alias_index` (`alias`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
[email protected] [~]# ./mysqlmymonlite.sh vbshowtables
What is your mysql database name ? 
roundcube


Do you want to continue with display all roundcube tables info ? [y/n]
y

Do you want save output to text file ? Answering no will output only to screen. [y/n]
n

----------------------------
MySQL datadir disk usage
----------------------------
/var/lib/mysql/ uses 1153752 kilobytes of disk space

------------------------------
roundcube Index Size = 0.19 MB
------------------------------
------------------------------
roundcube Data Size = 0.13 MB
------------------------------

----------------------
roundcube per Table Size
----------------------
+-------------------------------+----------------+----------------+-----------+------------+--------+
| Table Name                    | Number of Rows | Storage Engine | Data Size | Index Size | Total  |
+-------------------------------+----------------+----------------+-----------+------------+--------+
| roundcube.cache               | 0 Rows         | InnoDB         | 0.02MB    | 0.03MB     | 0.05MB |
| roundcube.contactgroupmembers | 0 Rows         | InnoDB         | 0.02MB    | 0.02MB     | 0.03MB |
| roundcube.contactgroups       | 0 Rows         | InnoDB         | 0.02MB    | 0.02MB     | 0.03MB |
| roundcube.contacts            | 0 Rows         | InnoDB         | 0.02MB    | 0.02MB     | 0.03MB |
| roundcube.cp_schema_version   | 1 Rows         | MyISAM         | 0.00MB    | 0.00MB     | 0.00MB |
| roundcube.identities          | 2 Rows         | InnoDB         | 0.02MB    | 0.02MB     | 0.03MB |
| roundcube.messages            | 0 Rows         | InnoDB         | 0.02MB    | 0.05MB     | 0.06MB |
| roundcube.session             | 3 Rows         | InnoDB         | 0.02MB    | 0.02MB     | 0.03MB |
| roundcube.users               | 2 Rows         | InnoDB         | 0.02MB    | 0.03MB     | 0.05MB |
+-------------------------------+----------------+----------------+-----------+------------+--------+

 

[eva2000]

now after converting back to MyISAM via my custom script

Roundcube MyISAM

[email protected] [~]# ./mysqlmymonlite.sh showcreate
What is your mysql database name ? 
roundcube


Do you want to display all roundcube tables' schema (how table was created) ? [y/n]
y

Do you want save output to text file ? Answering no will output only to screen. [y/n]
n
*************************** 1. row ***************************
       Table: cache
Create Table: CREATE TABLE `cache` (
  `cache_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cache_key` varchar(128) CHARACTER SET ascii NOT NULL,
  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `data` longtext NOT NULL,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`cache_id`),
  KEY `created_index` (`created`),
  KEY `user_cache_index` (`user_id`,`cache_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
*************************** 1. row ***************************
       Table: contactgroupmembers
Create Table: CREATE TABLE `contactgroupmembers` (
  `contactgroup_id` int(10) unsigned NOT NULL,
  `contact_id` int(10) unsigned NOT NULL DEFAULT '0',
  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  PRIMARY KEY (`contactgroup_id`,`contact_id`),
  KEY `contact_id_fk_contacts` (`contact_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
*************************** 1. row ***************************
       Table: contactgroups
Create Table: CREATE TABLE `contactgroups` (
  `contactgroup_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `del` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`contactgroup_id`),
  KEY `contactgroups_user_index` (`user_id`,`del`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
*************************** 1. row ***************************
       Table: contacts
Create Table: CREATE TABLE `contacts` (
  `contact_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `del` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL DEFAULT '',
  `email` varchar(255) NOT NULL,
  `firstname` varchar(128) NOT NULL DEFAULT '',
  `surname` varchar(128) NOT NULL DEFAULT '',
  `vcard` text,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`contact_id`),
  KEY `user_contacts_index` (`user_id`,`email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
*************************** 1. row ***************************
       Table: cp_schema_version
Create Table: CREATE TABLE `cp_schema_version` (
  `version_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `version` varchar(128) NOT NULL,
  `applied` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  PRIMARY KEY (`version_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
*************************** 1. row ***************************
       Table: identities
Create Table: CREATE TABLE `identities` (
  `identity_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `del` tinyint(1) NOT NULL DEFAULT '0',
  `standard` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL,
  `organization` varchar(128) NOT NULL DEFAULT '',
  `email` varchar(128) NOT NULL,
  `reply-to` varchar(128) NOT NULL DEFAULT '',
  `bcc` varchar(128) NOT NULL DEFAULT '',
  `signature` text,
  `html_signature` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`identity_id`),
  KEY `user_identities_index` (`user_id`,`del`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
*************************** 1. row ***************************
       Table: messages
Create Table: CREATE TABLE `messages` (
  `message_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `del` tinyint(1) NOT NULL DEFAULT '0',
  `cache_key` varchar(128) CHARACTER SET ascii NOT NULL,
  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `idx` int(11) unsigned NOT NULL DEFAULT '0',
  `uid` int(11) unsigned NOT NULL DEFAULT '0',
  `subject` varchar(255) NOT NULL,
  `from` varchar(255) NOT NULL,
  `to` varchar(255) NOT NULL,
  `cc` varchar(255) NOT NULL,
  `date` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `size` int(11) unsigned NOT NULL DEFAULT '0',
  `headers` text NOT NULL,
  `structure` text,
  PRIMARY KEY (`message_id`),
  UNIQUE KEY `uniqueness` (`user_id`,`cache_key`,`uid`),
  KEY `created_index` (`created`),
  KEY `index_index` (`user_id`,`cache_key`,`idx`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
*************************** 1. row ***************************
       Table: session
Create Table: CREATE TABLE `session` (
  `sess_id` varchar(40) NOT NULL,
  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `ip` varchar(40) NOT NULL,
  `vars` mediumtext NOT NULL,
  PRIMARY KEY (`sess_id`),
  KEY `changed_index` (`changed`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(128) NOT NULL,
  `mail_host` varchar(128) NOT NULL,
  `alias` varchar(128) NOT NULL,
  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `last_login` datetime DEFAULT NULL,
  `language` varchar(5) DEFAULT NULL,
  `preferences` text,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`,`mail_host`),
  KEY `alias_index` (`alias`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
[email protected] [~]# ./mysqlmymonlite.sh vbshowtables
What is your mysql database name ? 
roundcube


Do you want to continue with display all roundcube tables info ? [y/n]
y

Do you want save output to text file ? Answering no will output only to screen. [y/n]
n

----------------------------
MySQL datadir disk usage
----------------------------
/var/lib/mysql/ uses 1153868 kilobytes of disk space

------------------------------
roundcube Index Size = 0.03 MB
------------------------------
------------------------------
roundcube Data Size = 0.00 MB
------------------------------

----------------------
roundcube per Table Size
----------------------
+-------------------------------+----------------+----------------+-----------+------------+--------+
| Table Name                    | Number of Rows | Storage Engine | Data Size | Index Size | Total  |
+-------------------------------+----------------+----------------+-----------+------------+--------+
| roundcube.cache               | 0 Rows         | MyISAM         | 0.00MB    | 0.00MB     | 0.00MB |
| roundcube.contactgroupmembers | 0 Rows         | MyISAM         | 0.00MB    | 0.00MB     | 0.00MB |
| roundcube.contactgroups       | 0 Rows         | MyISAM         | 0.00MB    | 0.00MB     | 0.00MB |
| roundcube.contacts            | 0 Rows         | MyISAM         | 0.00MB    | 0.00MB     | 0.00MB |
| roundcube.cp_schema_version   | 1 Rows         | MyISAM         | 0.00MB    | 0.00MB     | 0.00MB |
| roundcube.identities          | 2 Rows         | MyISAM         | 0.00MB    | 0.00MB     | 0.00MB |
| roundcube.messages            | 0 Rows         | MyISAM         | 0.00MB    | 0.00MB     | 0.00MB |
| roundcube.session             | 3 Rows         | MyISAM         | 0.00MB    | 0.00MB     | 0.00MB |
| roundcube.users               | 2 Rows         | MyISAM         | 0.00MB    | 0.01MB     | 0.01MB |
+-------------------------------+----------------+----------------+-----------+------------+--------+
[email protected] [~]#

 

[eva2000]

hmmm just read 11.30 change log and notes cPanel & WHM 11.30 Release Notes and it says

Roundcube requires InnoDB support. If InnoDB is disabled, Roundcube will not update.

was this a requirement pre-11.30 ?

just ran /usr/local/cpanel/bin/update-roundcube and seems to update fine with innodb storage engine disabled in my.cnf

/usr/local/cpanel/bin/update-roundcube
Roundcube is up to date. Execute '/usr/local/cpanel/bin/update-roundcube --force' to force an update.

Or does that statement mean roundcube by default uses innnodb tables for some of it's tables and needs innodb storage engine enabled. But if you convert those roundcube innodb tables to myisam and disable innodb storage engine, then roundcube updates will work still ?