Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.40, 5.5.41
-
None
-
Ubuntu 14.04
Description
When I'm trying to create such tables server is killed. Logs in attachment
Checked on two computers.
CREATE TABLE orders_products
(
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
orders_id INT UNSIGNED NOT NULL,
products_id TINYINT NOT NULL,
quantity SMALLINT UNSIGNED DEFAULT 0,
base_price DECIMAL(8,2) DEFAULT 0.00 NOT NULL,
amount DECIMAL(8,2) DEFAULT 0.00 NOT NULL,
FOREIGN KEY (orders_id) REFERENCES orders (id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (products_id) REFERENCES products (id) ON DELETE RESTRICT ON UPDATE CASCADE
);
CREATE TABLE dashboard
(
id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
created DATETIME NOT NULL,
createdby MEDIUMINT UNSIGNED NOT NULL,
modified DATETIME NOT NULL,
modifiedby MEDIUMINT UNSIGNED NOT NULL,
profiles_id INT UNSIGNED,
profiles_bookings_id INT UNSIGNED,
status TINYINT DEFAULT 0,
FOREIGN KEY (createdby) REFERENCES users (id) ON UPDATE CASCADE,
FOREIGN KEY (modifiedby) REFERENCES users (id) ON UPDATE CASCADE,
FOREIGN KEY (profiles_id) REFERENCES profiles (id) ON UPDATE CASCADE,
FOREIGN KEY (profiles_bookings_id) REFERENCES profiles_bookings (id) ON UPDATE CASCADE
);
I was trying already to drop database, recreate, etc.
Attachments
Activity
Error message is:
[2015-02-03 10:59:18] [HY000][1005] Can't create table 'atest_sexnord5.orders_products' (errno: -1)
After this server is restarted. Table is created but without columns, when I'm deleting table frm file is not deleted.
I've already attached error log, and I'm attaching output of show variables
Got reason!
It was caused by wrong constraint (TINYINT and INT).
Newest version reported another error:
errno: 150 "Foreign key constraint is incorrectly formed"
Today was update of MariaDB in Ubuntu but problem still exists:
mysql Ver 15.1 Distrib 5.5.41-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Could you please explain step by step what you try to do ?
I'm trying to run such query:
CREATE TABLE test6
(
id INT UNSIGNED PRIMARY KEY NOT NULL,
user_group_id TINYINT UNSIGNED,
username VARCHAR(25),
name VARCHAR(45),
surname VARCHAR(45),
password VARCHAR(255),
email VARCHAR(255),
active TINYINT DEFAULT 1 NOT NULL,
created DATETIME,
modified DATETIME,
lang_name VARCHAR(2) DEFAULT 'de' NOT NULL,
phone VARCHAR(15) DEFAULT '1',
limit_normal DECIMAL(4,0),
limit_vip DECIMAL(4,0),
provision_normal DECIMAL(8,4),
provision_vip DECIMAL(8,4),
parent_user INT UNSIGNED,
lastsysystemmessage INT UNSIGNED DEFAULT 0 NOT NULL,
FOREIGN KEY (parent_user) REFERENCES test6 (id)
);
CREATE TABLE test7
(
id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
created DATETIME NOT NULL,
createdby MEDIUMINT UNSIGNED NOT NULL,
modified DATETIME NOT NULL,
modifiedby MEDIUMINT UNSIGNED NOT NULL,
profiles_id INT UNSIGNED,
profiles_bookings_id INT UNSIGNED,
status TINYINT DEFAULT 0,
FOREIGN KEY (createdby) REFERENCES test6 (id) ON UPDATE CASCADE,
FOREIGN KEY (modifiedby) REFERENCES test6 (id) ON UPDATE CASCADE,
FOREIGN KEY (profiles_id) REFERENCES test6 (id) ON UPDATE CASCADE
);
Of course it's wrong, but it causes default installation MariaDB to crash with attached earlier stacktrace on Ubuntu 14.04
I see, but that does not repeat the crash, you get error 150 as you should get.
Maybe I didn't provided everything to reproduce error on clean database, can I send you whole dump (300kb 7zipped) somewhere in private?
Ok, fix found, is your database secred or can I use it on test case ?
revno: 4420
committer: Jan Lindström <jplindst@mariadb.org>
branch nick: 5.5
timestamp: Wed 2015-02-04 14:40:46 +0200
message:
MDEV-7538: Wrong constraint (TINYINT or MEDIUMINT and INT)
causes server crash
Analysis: If wrong data types used on foreign constraint there
was possibility that foreign->id is NULL when incorrect
foreign constraint was removed from the dictionary cache.
Fix: Add guard foreign->id != NULL before trying to lookup
or remove the foreign constraint from dictionary cache.
Tested using user database where problem was repeatable.
Hi,
Please paste the output that you get in the client when you try to execute the statements; paste or attach the output of SHOW VARIABLES statement and your server error log.
Thanks.