|
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]> SELECT @@version;
|
+-----------------+
|
| @@version |
|
+-----------------+
|
| 10.0.12-MariaDB |
|
+-----------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> CREATE TABLE parent (
|
-> id INT NOT NULL,
|
-> subid INT NOT NULL,
|
-> PRIMARY KEY (id),
|
-> KEY(subid)
|
-> ) ENGINE=InnoDB;
|
Query OK, 0 rows affected (0.27 sec)
|
|
MariaDB [test]> CREATE TABLE child (
|
-> id INT,
|
-> parent_id INT,
|
-> parent_subid INT,
|
-> FOREIGN KEY (parent_id)
|
-> REFERENCES parent(id)
|
-> ON DELETE CASCADE,
|
-> FOREIGN KEY (parent_subid)
|
-> REFERENCES parent(subid)
|
-> ON DELETE CASCADE
|
-> ) ENGINE=InnoDB;
|
Query OK, 0 rows affected (0.02 sec)
|
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)
|
|
|
There is for sure a problem with foreign keys in 10.0.12
After i ran your code i could see the foreign keys at first check.
At the second check i get:
mysql> SHOW CREATE TABLE child;
|
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| child | CREATE TABLE `child` (
|
`id` int(11) unsigned NOT NULL,
|
`parent_id` int(11) DEFAULT NULL,
|
`parent_subid` int(11) DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
KEY `parent_id` (`parent_id`),
|
KEY `parent_subid` (`parent_subid`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
|
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set
|
Now i can see the keys, but no foreign keys at all without any other modify on tables....
If i try to delete an index it tells me that there is a foreign key depending on it and cannot be changed.
If i restart maria db server everything works fine for a while(i can see all foreign keys and keys)... but after some minutes i get the same issues again.
Everything is really random. I'm trying to get some time to investigate it more.
This is my development server(windows). I think i saw the same issues on the debian server i use for public testing but i'm not really sure.
So the only fix i made after i migrated to 10.0.12 was to dump all the databases in sql files, remove data folder, then i started mariadb to recreate data folder, i reset root password and then imported all the sql files into the server.
I dont know if there is something that maria db 10.0.12 doesn't like in my config files... i used the same in 10.0.10 and didn't had problems at all.
I can try to downgrade to 10.0.10 to see if there are still problems...
|
|
And i think that when issues start to appear, storage is suffering(doesn't store all the keys i create).
I created 2 issues but now i think they are pretty the same issue. They are linked somehow.
|
|
This is shown after i restart the server 
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| child | CREATE TABLE `child` (
|
`id` int(11) unsigned NOT NULL,
|
`parent_id` int(11) DEFAULT NULL,
|
`parent_subid` int(11) DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
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=utf8 |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set
|
|
mysql>
|
|
|
Please check if your error log contains any errors.
It sounds to me like a hardware/environmental problem, even more so considering your other bug report MDEV-6426 with OS error 6 (No such device or address).
If you are saying it started happening after upgrade, it's possible that due to some changes InnoDB became more sensitive to some kinds of errors.
I will assign it to Jan, the InnoDB expert, so he could take a look and say what might cause this.
|
|
Here are all my logs.
I have removed the others at that time to see fresh info.
after-upgrade-before-data-folder-remove.err :
This is exactly after upgrade, when maria db didn't stayed too much online, it crashed at the first FTS query.
So i had to remove the data folder and reimport sql on fresh data folder, and then after-upgrade-after-data-folder-remove.err was in action.
|
|
Those OS failures are on fact that 5.5 and 10.x uses different default log file size. Always, provide a correct log file size on my.cnf. Still investigating the actual foreign key issues...
|
|
Can't repeat :
Created tables:
jan@jan-GE70-0NC-0ND ~/mysql/upgrade $ mysql -u root -S mysql.sock2
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 1
|
Server version: 5.5.39-MariaDB Source distribution
|
|
Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
MariaDB [(none)]> use test;
|
Database changed
|
MariaDB [test]> source create.sql
|
Query OK, 0 rows affected (0.44 sec)
|
|
|
MariaDB [test]> show create table parent;
|
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| parent | CREATE TABLE `parent` (
|
`id` int(11) NOT NULL,
|
`subid` int(11) NOT NULL,
|
PRIMARY KEY (`id`),
|
KEY `subid` (`subid`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
|
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> show create table child;
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| child | 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=utf8 |
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
Shutdown:
jan@jan-GE70-0NC-0ND ~/mysql/upgrade $ mysqladmin -u root shutdown -S mysql.sock2
|
Started mariadb 10.x server and run mysql_upgrade:
jan@jan-GE70-0NC-0ND ~/mysql/upgrade $ /usr/local/mysql/bin/mysql_upgrade --datadir=/home/jan/mysql/upgrade --user=jan --basedir=/usr/local/mysql -S /home/jan/mysql/upgrade/mysql.sock2 -u root
|
/usr/local/mysql/bin/mysql_upgrade: the '--datadir' option is always ignored
|
/usr/local/mysql/bin/mysql_upgrade: the '--basedir' option is always ignored
|
Looking for 'mysql' as: /usr/local/mysql/bin/mysql
|
Looking for 'mysqlcheck' as: /usr/local/mysql/bin/mysqlcheck
|
Phase 1/3: Fixing table and database names
|
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' '--socket=/home/jan/mysql/upgrade/mysql.sock2'
|
Processing databases
|
information_schema
|
mysql
|
performance_schema
|
test
|
Phase 2/3: Checking and upgrading tables
|
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' '--socket=/home/jan/mysql/upgrade/mysql.sock2'
|
Processing databases
|
information_schema
|
mysql
|
mysql.columns_priv OK
|
mysql.db OK
|
mysql.event OK
|
mysql.func OK
|
mysql.help_category OK
|
mysql.help_keyword OK
|
mysql.help_relation OK
|
mysql.help_topic OK
|
mysql.host OK
|
mysql.ndb_binlog_index OK
|
mysql.plugin OK
|
mysql.proc OK
|
mysql.procs_priv OK
|
mysql.proxies_priv OK
|
mysql.servers OK
|
mysql.tables_priv OK
|
mysql.time_zone OK
|
mysql.time_zone_leap_second OK
|
mysql.time_zone_name OK
|
mysql.time_zone_transition OK
|
mysql.time_zone_transition_type OK
|
mysql.user OK
|
performance_schema
|
test
|
test.child OK
|
test.parent OK
|
Phase 3/3: Running 'mysql_fix_privilege_tables'...
|
OK
|
Check tables again:
jan@jan-GE70-0NC-0ND ~/mysql/upgrade $ mysql -u root -S mysql.sock2
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 12
|
Server version: 10.0.13-MariaDB Source distribution
|
|
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
MariaDB [(none)]> use test;
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
Database changed
|
MariaDB [test]> show create table parent;
|
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| parent | CREATE TABLE `parent` (
|
`id` int(11) NOT NULL,
|
`subid` int(11) NOT NULL,
|
PRIMARY KEY (`id`),
|
KEY `subid` (`subid`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
|
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> show create table child;
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| child | 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=utf8 |
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
Can you provide step-by-step what did you do and outputs from the necessary commands (tables that fail etc).
Thanks.
|
|
I'm trying to get a a step by step.
The idea is that last time i started from scratch and only imported my databases from scripts.
This solved FullText issues, but i still had problems after a while with all innodb tables. Here is a general problem. After a while it seems that innodb doesn't show relations or indexes. I restart mariadb and it works for a while(some times 1 hour or 4 hours, sometimes only 5 minutes. Now i'm designing some tables for one of my applications and I created a lot of indexes and relations, might be also a heavy use of indexes and FK creation.
By the way, i also bring sometimes the laptop on sleep mode, but i had issues also without turning it to sleep. For example in those logs you can see the startup of mariadb when i got the problems, because i never shutdown my laptop. I only hibernate or sleep it.
Today it happened 3 times.
First time was heavy use of Index/FK creation, second time was the redo of Elena Stepanova scripts, third time were some application queries(selects/updates/nothing related to Index/FK creation).
My memory usage is mostly at 4-5GB/RAM. Partition has 30GB of free space. Also the system partition has 3GB of space.
I think it didn't happened on my debian wheezy server(i upgraded to 10.0.12 there immediately after the update arrived) because i never restarted the service there after upgrade(just checked).
Might be only a windows issue.
BTW i also use navicat as a GUI, but it only generates standard and correct queries(i check them all the time before pressing save because i need to queue all the queries on SVN revisions for automatic update on all servers).
|
|
So only relations and indexes disappear when problems apear. Everything else seems to work perfectly. I can select data, i can insert/update, but i can't modify/remove indexes or FKs.
I also think that the problem can be related to FullText Indexes, because i started to use it heavy on all tables where i need search, and i upgraded to 10.0.12 only because on 10.0.10 i couldn't use FT like i used it on MyISAM, but after i realized that the standard on FT(couldn't combine multiple indexes) is a bit different on InnoDB than how it was on ISAM.
And the problem here might be because there are a lot of indexed words for FTS.
I'm not sure but maybe because i indexed some columns with a lot of text(HTML texts).
Can be an overload of FTS feature that breaks all indexes after a while ?
|
|
Now i have other issue )
I have this table
CREATE TABLE `wb_reseller` (
|
`Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
`Id_Image` int(11) unsigned DEFAULT NULL,
|
`Id_User` int(11) unsigned DEFAULT NULL,
|
`Id_UserModified` int(11) unsigned DEFAULT NULL,
|
`Name` varchar(100) NOT NULL,
|
`Alias_Url` varchar(100) NOT NULL,
|
`Domain` varchar(100) DEFAULT NULL,
|
`DateAdded` datetime DEFAULT NULL,
|
`DateModified` datetime DEFAULT NULL,
|
`Disabled` tinyint(3) unsigned NOT NULL DEFAULT '1',
|
PRIMARY KEY (`Id`),
|
UNIQUE KEY `Alias_Url` (`Alias_Url`),
|
KEY `Id_Image` (`Id_Image`),
|
KEY `Id_User` (`Id_User`),
|
KEY `Id_UserModified` (`Id_UserModified`),
|
CONSTRAINT `wb_reseller_ibfk_1` FOREIGN KEY (`Id_Image`) REFERENCES `titan_upload_image` (`Id`) ON DELETE SET NULL ON UPDATE CASCADE,
|
CONSTRAINT `wb_reseller_ibfk_2` FOREIGN KEY (`Id_User`) REFERENCES `titan_user` (`Id`) ON DELETE SET NULL ON UPDATE CASCADE,
|
CONSTRAINT `wb_reseller_ibfk_3` FOREIGN KEY (`Id_UserModified`) REFERENCES `titan_user` (`Id`) ON DELETE SET NULL ON UPDATE CASCADE
|
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
|
|
And i'm wanting to create a fulltext search index:
ALTER TABLE `wb_reseller`
|
ADD FULLTEXT INDEX (`Name`) ;
|
and i get error:
1050 - Table 'wct/#sql-ib342' already exists
|
|
For this new problem I would need also definitions of tables titan_upload_image and titan_user and if there is foreign keys also those tables. Without foreign keys, I can't repeat the problem.
|
|
And sometimes mariadb hangs on table creation with indexes or FKs, also on "alter tables" that affects indexes. The process remains open but i can't connect to it from any client, until i restart it by "force closing" the process from task manager, and start it from services. On services manager i can't stop it when hang happens. Windows service manager tells me that the service didn't responded.
And all this started to happen only from 10.0.12.
I use mariadb 10 here from last beta version, and i upgraded it constantly to the newest version every time when update happened. I upgraded it every time manually(by replacing files). The only version i skipped was 10.0.11.
I never had problems like these before. No hangs, no Indexes issues, actually i never had to restart it.
|
|
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...
|
after-upgrade-after-data-folder-remove.err