Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
5.5.41, 5.5.46, 5.5(EOL), 10.0(EOL), 10.1(EOL)
-
Tested on Windows 2012, CentOS6.7
Description
When creating a table with a constraint which references a table without explicitly naming the database the statement fails with the following error.
ERROR 1046 (3D000) at line ##@ No database selected.
|
The test statement used is as follows:
CREATE TABLE `kg_test1`.`person` |
(
|
`Id` INT(11) NOT NULL AUTO_INCREMENT, |
`Name` VARCHAR(50) NOT NULL, |
PRIMARY KEY (`Id`), |
CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`) |
) ENGINE=INNODB DEFAULT CHARSET=utf8; |
I'm using a local client to connect and run the test statement on MariaDB 5.5.41 - 46, on both Windows 2012 & CentOS 6.7....which all fail with the same error as above.
You can work around this by either adding a database name to the reference, (kg_test1.group) or adding database context (i.e. use <db-name>) when logging in via the client, but it does not have to be the same database the table is being created in.
This is a change in behaviour to MariaDB verisons prior to 5.5.41.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Affects Version/s | 5.5 [ 15800 ] | |
| Affects Version/s | 10.0 [ 16000 ] | |
| Affects Version/s | 10.1 [ 16100 ] | |
| Affects Version/s | 5.5.42 [ 18102 ] | |
| Affects Version/s | 5.5.43 [ 18601 ] | |
| Affects Version/s | 5.5.44 [ 19100 ] | |
| Affects Version/s | 5.5.45 [ 19405 ] | |
| Assignee | Jan Lindström [ jplindst ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Labels | upstream |
| Description |
When creating a table with a constraint which references a table without explicitly naming the database the statement fails with the following error. ERROR 1046 (3D000) at line ##@ No database selected. The test statement used is as follows: CREATE TABLE `kg_test1`.`person` ( `Id` INT(11) NOT NULL AUTO_INCREMENT, `Name` VARCHAR(50) NOT NULL, PRIMARY KEY (`Id`), CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; I'm using a local client to connect and run the test statement on MariaDB 5.5.41 - 46, on both Windows 2012 & CentOS 6.7....which all fail with the same error as above. You can work around this by either adding a database name to the reference, (kg_test1.group) or adding database context (i.e. use <db-name>) when logging in via the client, but it does not have to be the same database the table is being created in. This is a change in behaviour to MariaDB verisons prior to 5.5.41. |
When creating a table with a constraint which references a table without explicitly naming the database the statement fails with the following error.
{noformat} ERROR 1046 (3D000) at line ##@ No database selected. {noformat} The test statement used is as follows: {code:sql} CREATE TABLE `kg_test1`.`person` ( `Id` INT(11) NOT NULL AUTO_INCREMENT, `Name` VARCHAR(50) NOT NULL, PRIMARY KEY (`Id`), CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; {code} I'm using a local client to connect and run the test statement on MariaDB 5.5.41 - 46, on both Windows 2012 & CentOS 6.7....which all fail with the same error as above. You can work around this by either adding a database name to the reference, (kg_test1.group) or adding database context (i.e. use <db-name>) when logging in via the client, but it does not have to be the same database the table is being created in. This is a change in behaviour to MariaDB verisons prior to 5.5.41. |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Assignee | Jan Lindström [ jplindst ] | Sergei Golubchik [ serg ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
commit 8bd6088800240a4e74ee58116f58d559cc4fc9f2
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Thu Apr 7 10:47:46 2016 +0300
MDEV-9142 :Adding Constraint with no database reference
results in ERROR 1046 (3D000) at line 13: No database selected.
Use database from create table to foreign key database if
nothing else is given.
| Fix Version/s | 5.5 [ 15800 ] | |
| Fix Version/s | 10.0 [ 16000 ] | |
| Fix Version/s | 10.1 [ 16100 ] |
| Assignee | Sergei Golubchik [ serg ] | Jan Lindström [ jplindst ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
commit 19e3597e0c718a4cfdfe8789c7b4b11a4e0ba0c6
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Thu Apr 7 10:47:46 2016 +0300
MDEV-9142 :Adding Constraint with no database reference
results in ERROR 1046 (3D000) at line 13: No database selected.
Use database from create table to foreign key database if
nothing else is given.
| Component/s | Data Definition - Alter Table [ 10114 ] | |
| Fix Version/s | 5.5.50 [ 22014 ] | |
| Fix Version/s | 10.0.25 [ 21701 ] | |
| Fix Version/s | 5.5 [ 15800 ] | |
| Fix Version/s | 10.0 [ 16000 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 72618 ] | MariaDB v4 [ 149834 ] |
The change was introduced by this commit:
commit 0b28d7e048fa097280be54f9baffd202f7626bddAuthor: Praveenkumar Hulakund <praveenkumar.hulakund@oracle.com>Date: Wed Sep 10 10:50:17 2014 +0530Bug#18790730 - CROSS-DATABASE FOREIGN KEY WITHOUT PERMISSIONSCHECK.Analysis:----------Issue here is, while creating or altering the InnoDB table,if the foreign key defined on the table references a parenttable on which the user has no access privileges then thetable is created without reporting any error.Currently the privilege level REFERENCES_ACL is unusedand is not used for access evaluation while creating thetable with a foreign key constraint or adding the foreignkey constraint to a table. But when no privileges are grantedto user then also access evaluation on parent table is ignored.Fix:---------For DMLs, irrelevant of the fact, support does not want anychanges to avoid permission checks on every operation.So, as a fix, added a function "check_fk_parent_table_access"to check whether any of the SELECT_ACL, INSERT_ACL, UDPATE_ACL,DELETE_ACL or REFERENCE_ACL privileges are granted for userat table level. If none of them is granted then error is reported.This function is called during the table creation and alteroperation.However, I can't say from the above whether the described side-effect is expected or not. Assigning to jplindst for an expert opinion.
Here is the complete test case:
--source include/have_innodb.incDROP DATABASE test;CREATE DATABASE kg_test1;CREATE TABLE `kg_test1`.`group` (Id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `kg_test1`.`person` (`Id` INT(11) NOT NULL AUTO_INCREMENT,`Name` VARCHAR(50) NOT NULL,PRIMARY KEY (`Id`),CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`)) ENGINE=INNODB DEFAULT CHARSET=utf8;SHOW WARNINGS;DROP DATABASE kg_test1;CREATE DATABASE test;