Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Cannot Reproduce
-
Affects Version/s: 10.0.12
-
Fix Version/s: 10.0.12
-
Component/s: None
-
Labels:None
-
Environment:Windows 8.1 update
Description
Before I updated to mariadb 10, when i created multiple foreign keys server created normal indexes for every foreign key relation i created.
Now it automatically create index only for last relation i declare.
Before i used maria db 5.5.31
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
is a big chain 
i can put those too
CREATE TABLE `titan_upload_file` (
`Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`Id_Object` int(11) unsigned DEFAULT NULL,
`Id_StorageEngine` int(11) unsigned DEFAULT NULL,
`Id_User` int(11) unsigned DEFAULT NULL,
`Id_UserModified` int(11) unsigned DEFAULT NULL,
`FileType` varchar(20) NOT NULL DEFAULT '',
`FileTable` varchar(100) DEFAULT NULL,
`FileColumn` varchar(100) DEFAULT NULL,
`DeleteType` enum('nullmark','zeromark','deleterow') NOT NULL DEFAULT 'nullmark',
`PrefixFolder` varchar(50) NOT NULL DEFAULT '',
`FileName` varchar(255) NOT NULL,
`FileSize` int(11) NOT NULL,
`CopyrightInfo` varchar(50) DEFAULT NULL,
`CopyrightLink` varchar(100) DEFAULT NULL,
`DateAdded` datetime DEFAULT NULL,
`DateModified` datetime DEFAULT NULL,
`Is_Deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
`Disabled` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`Id`),
KEY `Id_User` (`Id_User`),
KEY `Id_UserModified` (`Id_UserModified`),
KEY `Id_StorageEngine` (`Id_StorageEngine`),
CONSTRAINT `titan_upload_file_ibfk_1` FOREIGN KEY (`Id_User`) REFERENCES `titan_user` (`Id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `titan_upload_file_ibfk_2` FOREIGN KEY (`Id_UserModified`) REFERENCES `titan_user` (`Id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `titan_upload_file_ibfk_3` FOREIGN KEY (`Id_StorageEngine`) REFERENCES `titan_config_storage_engine` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8;
CREATE TABLE `titan_user` (
`Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`Id_Image` int(11) unsigned DEFAULT NULL,
`Id_UserGroup` int(11) unsigned DEFAULT NULL,
`Email` varchar(255) NOT NULL,
`UserName` varchar(255) NOT NULL,
`Password` varchar(255) NOT NULL,
`Salt` varchar(255) NOT NULL,
`FirstName` varchar(100) NOT NULL DEFAULT '',
`MiddleName` varchar(20) DEFAULT NULL,
`LastName` varchar(100) NOT NULL DEFAULT '',
`FullName` varchar(200) NOT NULL DEFAULT '',
`NickName` varchar(50) DEFAULT NULL,
`FullNameType` enum('last_name_first','first_name_first') NOT NULL DEFAULT 'first_name_first',
`ActivationHash` varchar(255) NOT NULL DEFAULT '',
`RegistrationDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Activated` tinyint(1) unsigned NOT NULL DEFAULT '0',
`Blocked` tinyint(1) unsigned NOT NULL DEFAULT '0',
`Deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
`DeletionDate` datetime DEFAULT NULL,
`LoginType` enum('legacy','federated') NOT NULL DEFAULT 'legacy',
`FederatedLoginId` text NOT NULL,
`Cache_Permissions` text,
`Cache_UserOptions` text,
`LastLogin` datetime DEFAULT NULL,
`LastAction` datetime DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `UserName` (`UserName`),
UNIQUE KEY `Email` (`Email`),
KEY `Id_Image` (`Id_Image`),
KEY `titan_user_ibfk_2` (`Id_UserGroup`),
FULLTEXT KEY `Email_2` (`Email`,`UserName`,`FirstName`,`MiddleName`,`LastName`,`FullName`,`NickName`),
CONSTRAINT `titan_user_ibfk_1` FOREIGN KEY (`Id_Image`) REFERENCES `titan_upload_image` (`Id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `titan_user_ibfk_2` FOREIGN KEY (`Id_UserGroup`) REFERENCES `titan_user_group` (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
Actually i think you need all the database 
And the others:
CREATE TABLE `titan_user_group` ( `Id` int(11) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(100) DEFAULT NULL, `Alias_Url` varchar(100) DEFAULT NULL, `DrawColor` varchar(6) DEFAULT NULL, `FAS` tinyint(1) unsigned NOT NULL DEFAULT '0', `RRU` tinyint(1) unsigned NOT NULL DEFAULT '0', `BBB` tinyint(1) unsigned NOT NULL DEFAULT '0', `Is_CoreGroup` tinyint(1) unsigned NOT NULL DEFAULT '0', `Is_StockGroup` tinyint(1) unsigned NOT NULL DEFAULT '0', `Is_Default` tinyint(1) unsigned NOT NULL DEFAULT '0', `Cache_Permissions` text, `Disabled` tinyint(1) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`Id`), UNIQUE KEY `Alias_Url` (`Alias_Url`), FULLTEXT KEY `Name` (`Name`) ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;
CREATE TABLE `titan_config_storage_engine` (
`Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`Id_User` int(11) unsigned DEFAULT NULL,
`Id_UserModified` int(11) unsigned DEFAULT NULL,
`Name` varchar(50) DEFAULT NULL,
`StorageClass` enum('legacy','ftp','amazons3') NOT NULL,
`Auth_User` varchar(50) DEFAULT NULL,
`Auth_Key` varchar(50) DEFAULT NULL,
`Auth_Key2` varchar(50) DEFAULT NULL,
`Hostname_Url` varchar(100) DEFAULT NULL,
`Hostname_Port` smallint(6) unsigned DEFAULT NULL,
`Path` varchar(100) DEFAULT NULL,
`BaseUrl` varchar(100) DEFAULT NULL,
`Is_CustomOriginal` tinyint(1) unsigned NOT NULL DEFAULT '0',
`CustomOriginalAddFileName` tinyint(1) unsigned NOT NULL DEFAULT '0',
`CustomOriginalPath` varchar(100) DEFAULT NULL,
`CustomOriginalBaseUrl` varchar(100) DEFAULT NULL,
`Is_CustomThumbs` tinyint(1) unsigned NOT NULL DEFAULT '0',
`CustomThumbsAddFileName` tinyint(1) unsigned NOT NULL DEFAULT '0',
`CustomThumbsPath` varchar(100) DEFAULT NULL,
`CustomThumbsBaseUrl` varchar(100) DEFAULT NULL,
`Is_CustomResize` tinyint(1) unsigned NOT NULL DEFAULT '0',
`CustomResizeAddFileName` tinyint(1) unsigned NOT NULL DEFAULT '0',
`CustomResizePath` varchar(100) DEFAULT NULL,
`CustomResizeBaseUrl` varchar(100) DEFAULT NULL,
`Cache_TotalSpace` bigint(50) DEFAULT NULL,
`Cache_FreeSpace` bigint(50) DEFAULT NULL,
`Cache_FillPercent` float(5,2) DEFAULT NULL,
`Is_Default` tinyint(1) unsigned NOT NULL DEFAULT '0',
`DateAdded` datetime DEFAULT NULL,
`DateModified` datetime DEFAULT NULL,
`Disabled` tinyint(1) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`Id`),
KEY `titan_config_storage_engine_ibfk_2` (`Id_UserModified`),
KEY `titan_config_storage_engine_ibfk_1` (`Id_User`),
FULLTEXT KEY `Name` (`Name`),
CONSTRAINT `titan_config_storage_engine_ibfk_1` FOREIGN KEY (`Id_User`) REFERENCES `titan_user` (`Id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `titan_config_storage_engine_ibfk_2` FOREIGN KEY (`Id_UserModified`) REFERENCES `titan_user` (`Id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
i think there aren't any other chains for these ones.
Hi,
About:
1050 - Table 'wct/#sql-ib342' already exists
I think you have made some error earlier. Easiest way to resolve this is to shutdown the server, and remove the files wct/#sql-ib342.*, while
you do this check other temporal tables and remove them also to avoid further problems.
You missed above titan_upload_image table, but I do not think the exact structure is relevant here. I could not repeat your problems.
R: Jan
If you see randomly while server is up and running that table definition changes, could you send me a example where table is fine (show create table) and then later same output when it is not correct (show create table) and in this situation result from check table extended. And finally,
please provide full, unedited error log when the issue has happened. At the moment, I can't repeat any of the problems mentioned. I do not think this is OS issue, not sure how safe is to put your machine to sleep mode without server shutdown, do you see problem after this all all the time ?
Whole, problem could be related on HW problem, and my suspect would be memory cards, if after shutdown+restore all tables are again correct.
Hello, sorry for the late answer.
This happened in all innodb tables... When the issue started to happen, happened all the time until the restart of the server on all databases.
I made yesterday a downgrade to 10.0.10 for testing and i didn't saw the issue anymore....
I stay on 10.0.10 another 2-3 days for testing, to see if this happens again.
On 10.0.12 the issue started from the first run. There is something for sure, but is very hard to highlight the location of the problem..
What is strange is that i have a debian 7 server with 10.0.12 and i didn't saw the issue there yet.. And i'm working on a website builder which is big data intensive, and i store a lot of cache, huge texts, and there everything seems to work normal.
I started to use indexes only to speed up the joins, search for items and order via ids.
This week I have a lot of work, but immediate after i'm upgrading again to see if the issue still happens and maybe i can isolate it somehow...
Hi Adrian,
Please provide a complete example of the described problem, something similar to the test case below – as you can see, multiple indexes are created for me, both on CREATE TABLE and on ALTER TABLE:
MariaDB [test]> SHOW CREATE TABLE child \G *************************** 1. row *************************** Table: child Create Table: CREATE TABLE `child` ( `id` int(11) DEFAULT NULL, `parent_id` int(11) DEFAULT NULL, `parent_subid` int(11) DEFAULT NULL, KEY `parent_id` (`parent_id`), KEY `parent_subid` (`parent_subid`), CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE, CONSTRAINT `child_ibfk_2` FOREIGN KEY (`parent_subid`) REFERENCES `parent` (`subid`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)MariaDB [test]> CREATE TABLE child2 ( -> id INT, -> parent_id INT, -> parent_subid INT -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec)MariaDB [test]> ALTER TABLE child2 -> ADD FOREIGN KEY (parent_id) REFERENCES parent(id), -> ADD FOREIGN KEY (parent_subid) REFERENCES parent(subid) -> ; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [test]> SHOW CREATE TABLE child2 \G *************************** 1. row *************************** Table: child2 Create Table: CREATE TABLE `child2` ( `id` int(11) DEFAULT NULL, `parent_id` int(11) DEFAULT NULL, `parent_subid` int(11) DEFAULT NULL, KEY `parent_id` (`parent_id`), KEY `parent_subid` (`parent_subid`), CONSTRAINT `child2_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`), CONSTRAINT `child2_ibfk_2` FOREIGN KEY (`parent_subid`) REFERENCES `parent` (`subid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)