Author: admin

Oracle 从11小时到25秒–还有优化空间吗?

2016-07-25 老虎刘 老虎刘谈SQL优化     2015年5月的一天,客户发来邮件请求帮助:一个SQL执行需要11个小时,执行计划中使用nested loops好像效率差了些,能不能用hint让优化器使用hash join,用use_hash 的 hint好像没生效? 下面是客户邮件原文截取: SQL代码如下: SELECT DISTINCT N.ID_NBDL_TCIMS_PCFC_ORDER, M.POLICY_NO, N.POLICY_NO FROM (SELECT /*+ USE_HASH(R,C) */ C.ID_BSE_TCIMS_PCFC_ORDER, C.PRODUCT_CODE, D.INSURANT_CERTIFICATE_NUMBER, C.DATE_BEGIN, C.POLICY_NO FROM N_SM_PCFC_ORDER_MERGE_02_TMP R, BSE_TCIMS_PCFC_ORDER_BM_TMP  C, BSE_TCIMS_PCFC_INS_BM_TMP    D WHERE R.TCIMS_LIST_ID IS NULL AND R.RN =

再谈SQL profile : 到底能不能固定执行计划?

2016-07-24 老虎刘 老虎刘谈SQL优化     在我发布了使用coe_load_sql_profile.sql脚本可以解决生产系统 执行计划突然变差的文章后,第二天刚好又看到一篇文章:《执行计划:Oracle的Profile特性与SQL执行计划的稳定性》,文章专门对sql profile做了深入的介绍,对了解SQL profile的原理很有帮助。因为撰写人和转发者都是业内大拿,粉丝众多,看了那篇文章的人应该非常多,因此有必要对那篇文章的一个小瑕疵加以纠正,即“SQL profile不能固定执行计划”这个结论是不准确的,只是在某种测试条件下的一种结果。 下图是截取文章的结论部分:     在文章作者使用的Test case中,应该确实能得到这样的结果,但是结论却不够完整,会给读者一个误导:SQL prfofile不能固定执行计划。   上述结论是使用oracle的sql tuning advisor工具生成的sql profile(只写入一个opt_estimate的有一个hint),作为一个优化工具,对这样一个SQL不做完全固定也是可以理解的。这个也提醒了我们的DBA,用sql tuning advisor做的sql profile不一定能固定执行计划。 我想补充说明的是:如果在sql profile写入了完整的outline data,是可以固定执行计划的,比如将下面一个SQL完整的Hint的内容写入profile: /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE(‘11.2.0.3’) DB_VERSION(‘11.2.0.3’) ALL_ROWS OUTLINE_LEAF(@”SEL$1″) FULL(@”SEL$1″ “T1″@”SEL$1″) INDEX(@”SEL$1” “T2″@”SEL$1” (“T2″.”OBJECT_ID”)) LEADING(@”SEL$1″ “T1″@”SEL$1” “T2″@”SEL$1″)

修改隐含参数造成SQL性能下降案例之二

2016-07-23 老虎刘谈SQL优化 客户发来这样一条执行时间 1.3小时的SQL 想做优化: SQL代码: INSERT INTO HWDW.TW_CI_SIM_RES ( …… ) SELECT    …… FROM HWODS.TO_CI_I91822  S —–大表 ,( SELECT PHONE_NO,TD_PHONE_NO FROM  HWDW.TW_CI_PHONE_TD_INFO—–小表 WHERE STATIS_DATE = :B1 )T WHERE S.PHONE_NO = T.TD_PHONE_NO(+); 通过SQL Monitor显示的数据发现执行计划不正常:在Estimate-rows估值正常的情况下,hash join的大表做了左表,这种情况一般是相关参数做了改动。 果然,检查发现,_right_outer_hash_enable 隐含参数被改成了false 。如果将参数改回默认的TRUE,预计SQL性能会提升10倍左右。 说明:

关于parallel(并行)的几个基本常识

2016-08-08 老虎刘 老虎刘谈SQL优化     关于并行的使用,我原来所在研发部门(Real-World Performance)的同事陈焕生已经写了3篇非常棒的“深入理解”系列,可以说并行的精华部分都已经涵盖。   然而,对于大部分SQL开发者和DBA来说,并行的一些最基本的使用方法还没有完全掌握,我着重介绍一下并行使用的常见问题及注意事项。 1、什么时候使用并行? 常见的场景有: a) 普通SQL最常见的情况就是大表的全表扫描,还有就是大的索引的快速全扫描(注意,index fast full scan可以使用并行,index full scan 不能使用并行)。 需要纠正一个误区:SQL执行慢就可以通过使用并行或是增加并行来提高速度。     正解:并行能否发挥作用要看SQL的具体执行计划,比如标量子查询或是DB link,增大并行带来的性能提升是微乎其微的!     多大的表算大表? 至少要百万级以上记录的表吧。如果几亿甚至十几亿记录数的表全表扫描不使用并行,SQL的执行时间会相当长,特别是表在SQL执行的过程中如果还有其他session的DML操作的时候。 OLTP系统的正常事务一般不会使用大表全扫描的执行计划,如果有一些统计分析的业务,建议在系统资源相对空闲的时候开启并行。 b) 用create table As Select创建一张大表,如 create table test parallel 16 as select …. from t1,t2 where …..;

What Should I Monitor, and How Should I Do It?

Monitoring tools offer two core types of functionality: alerts based on aliveness checks and comparing metrics to thresholds, and displaying time-series charts of status counters. Nagios + Graphite are the prototypical time-series tools that do these things.

But these tools don’t answer the crucial questions about what we should monitor. What kinds of aliveness/health checks should we build into Nagios? Which metrics should we monitor with thresholds to raise alarms, and what should the thresholds be? What graphs should we build of status counters, which graphs should we examine and what do they mean?

We need guiding principles to help answer these questions. This webinar briefly introduces the principles that motivate and inform what we do at VividCortex, then explains which types of health checks and charts are valuable and what conclusions should be drawn from them. The webinar is focused mostly on MySQL database monitoring, but will be relevant beyond MySQL as well. Some of the questions we answer are:

What status counters from MySQL are central and core, and which are peripheral?
What is the meaning of MySQL status metrics?
Which subsystems inside MySQL are the most common causes of problems in production?
What is the unit of work-getting-done in MySQL, and how can you measure it?
Which open-source tools do a good job at monitoring in the way we recommend at VividCortex?
Which new and/or popular open-source tools should you evaluate when choosing a solution?

You will leave this webinar with a solid understanding of the types of monitoring you should be doing, the low-hanging fruit, and tools for doing it. This is not just a sales pitch for VividCortex. Register below, and we will send you a link to the recording and a copy of the slide deck.

Pic Cred

tce

加入dbDao.com Oracle技术学习QQ群:171092051   如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复! 诗檀软件专业数据库修复团队 服务热线 : 400-690-3643 备用电话: 13764045638 邮箱:service@parnassusdata.com   tewtwe 关注dbDao.com的新浪微博 扫码关注dbDao.com 微信公众号:

MySQL 5.6.26 Overview and Highlights

MySQL 5.6.26 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here.
For this release, there are 3 “Functionality Added or Changed” items, 1 “Security Fix”, and 36 other bug fixes.
Out of those other 36 bugs, 13 are InnoDB, 1 Partitioning, 3 Replication, and 19 misc. (including 3 potentially crashing bug fixes, and 1 performance-related fix) Here are the ones of note:

Functionality Added/Changed: Replication: When using a multi-threaded slave, each worker thread has its own queue of transactions to process. In previous MySQL versions, STOP SLAVE waited for all workers to process their entire queue. This logic has been changed so that STOP SLAVE first finds the newest transaction that was committed by any worker thread. Then, it waits for all workers to complete transactions older than that. Newer transactions are not processed. The new logic allows STOP SLAVE to complete faster in case some worker queues contain multiple transactions. (Bug #75525)
Functionality Added/Changed: Previously, the max_digest_length system variable controlled the maximum digest length for all server functions that computed statement digests. However, whereas the Performance Schema may need to maintain many digest values, other server functions such as MySQL Enterprise Firewall need only one digest per session. Increasing the max_digest_length value has little impact on total memory requirements for those functions, but can increase Performance Schema memory requirements significantly. To enable configuring digest length separately for the Performance Schema, its digest length is now controlled by the new performance_schema_max_digest_length system variable.
Functionality Added/Changed: Previously, changes to the validate_password plugin dictionary file (named by the validate_password_dictionary_file system variable) while the server was running required a restart for the server to recognize the changes. Now validate_password_dictionary_file can be set at runtime and assigning a value causes the named file to be read without a restart. In addition, two new status variables are available. validate_password_dictionary_file_last_parsed indicates when the dictionary file was last read, and validate_password_dictionary_file_words_count indicates how many words it contains. (Bug #66697)
Security-related: Due to the LogJam issue (https://weakdh.org/), OpenSSL has changed the Diffie-Hellman key length parameters for openssl-1.0.1n and up. OpenSSL has provided a detailed explanation at http://openssl.org/news/secadv_20150611.txt. To adopt this change in MySQL, the key length used in vio/viosslfactories.c for creating Diffie-Hellman keys has been increased from 512 to 2,048 bits. (Bug #77275)
InnoDB: Importing a tablespace with a full-text index resulted in an assertion when attempting to rebuild the index.
InnoDB: Opening a foreign key-referenced table with foreign_key_checks enabled resulted in an error when the table or database name contained special characters.
InnoDB: The page_zip_verify_checksum function returned false for a valid compressed page.
InnoDB: A failure to load a change buffer bitmap page during a concurrent delete tablespace operation caused a server exit.
InnoDB: After dropping a full-text search index, the hidden FTS_DOC_ID and FTS_DOC_ID_INDEX columns prevented online DDL operations. (Bug #76012)
InnoDB: An index record was not found on rollback due to inconsistencies in the purge_node_t structure. (Bug #70214)
Partitioning: In certain cases, ALTER TABLE … REBUILD PARTITION was not handled correctly when executed on a locked table.
Replication: If flushing the cache to the binary log failed, for example due to a disk problem, the error was not detected by the binary log group commit logic. This could cause inconsistencies between the master and the slave. The fix uses the binlog_error_action variable to decide how to handle this situation. If binlog_error_action=ABORT_SERVER, then the server aborts after informing the client with an ER_BINLOGGING_IMPOSSIBLE error. If binlog_error_action=IGNORE_ERROR, then the error is ignored and binary logging is disabled until the server is restarted again. The same is mentioned in the error log file, and the transaction is committed inside the storage engine without being added to the binary log. (Bug #76795)
Replication: When using GTIDs, a multi-threaded slave which had relay_log_recovery=1 and that stopped unexpectedly could encounter a relay-log-recovery cannot be executed when the slave was stopped with an error or killed in MTS mode error upon restart. The fix ensures that the relay log recovery process checks if GTIDs are in use or not. If GTIDs are in use, the multi-threaded slave recovery process uses the GTID protocol to fill any unprocessed transactions. (Bug #73397)
Replication: When two slaves with the same server_uuid were configured to replicate from a single master, the I/O thread of the slaves kept reconnecting and generating new relay log files without new content. In such a situation, the master now generates an error which is sent to the slave. By receiving this error from the master, the slave I/O thread does not try to reconnect, avoiding this problem. (Bug #72581)
Crashing Bug: Incorrect cost calculation for the semi-join Duplicate Weedout strategy could result in a server exit.
Crashing Bug: For large values of max_digest_length, the Performance Schema could encounter an overflow error when computing memory requirements, resulting in a server exit.
Crashing Bug: GROUP BY or ORDER BY on a CHAR(0) NOT NULL column could lead to a server exit.
Performance-related: When choosing join order, the optimizer could incorrectly calculate the cost of a table scan and choose a table scan over a more efficient eq_ref join. (Bug #71584)

Conclusions:
So while there were no major changes, and not too many overall bug fixes, the security fix could be an issue if you run the latest RHEL/CentOS with SSL connections + a DHE SSL cipher specifed with –ssl-cipher=DHE-RSA-… Also, some of those InnoDB bugs are nasty, especially the fulltext bugs, thus if you use InnoDB’s fulltext, I’d recommend planning for an upgrade.
The full 5.6.26 changelogs can be viewed here (which has more details about all of the bugs listed above):
http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-26.html
Hope this helps.

Find Queries By Error Code With VividCortex

VividCortex now lets you search for queries that cause a specific error in your
application. The error code itself will be database-specific, but for example
error 1062 in MySQL is a duplicate key error, and in PostgreSQL error 23503 is a
foreign key violation.

One of our customers requested that we add this feature so they could search for
queries that cause UTF8 issues, which is a great example of when this can be
useful.

To use this feature, click on the Queries navigation link, which brings up a
catalog of every query we have seen execute in your systems. At the top, select
the drop-down menu and filter by errors, then type in the error code you’re
looking for and click Apply:

The result will be a listing of all queries that cause the server to return that
error (even if it’s only an occasional error).

You might be surprised at how many queries cause once-in-a-million errors!
They’re really hard to find in production systems if you don’t have the deep
visibility we provide.

You can click on any of the errors and inspect it, sample by sample, to see
exactly which instances of it cause errors. Sampling is biased towards
capturing errors and warnings,
so you can find and resolve them easily. Samples are color-coded red when they
have errors.

Let us know if you, too, have great ideas for features we can implement to make
your life easier!

MySQL 5.5.45 Overview and Highlights

MySQL 5.5.45 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here:
http://dev.mysql.com/downloads/mysql/5.5.html
This release, similar to the last 5.5 release, is mostly uneventful.
There were 0 “Functionality Added or Changed” items this time, 1 “Security Fix”, and just 9 bugs overall fixed.
Out of the 9 bugs, there were 3 InnoDB bugs, 1 security-related bug, and 1 potential crashing bug. Here are the ones worth noting:

InnoDB: An index record was not found on rollback due to inconsistencies in the purge_node_t structure.
InnoDB: An assertion was raised when InnoDB attempted to dereference a NULL foreign key object.
InnoDB: On Unix-like platforms, os_file_create_simple_no_error_handling_func and os_file_create_func opened files in different modes when innodb_flush_method was set to O_DIRECT. (Bug #76627)
Security-related: Due to the LogJam issue (https://weakdh.org/), OpenSSL has changed the Diffie-Hellman key length parameters for openssl-1.0.1n and up. OpenSSL has provided a detailed explanation at http://openssl.org/news/secadv_20150611.txt. To adopt this change in MySQL, the key length used in vio/viosslfactories.c for creating Diffie-Hellman keys has been increased from 512 to 2,048 bits. (Bug #77275)
Crashing Bug: GROUP BY or ORDER BY on a CHAR(0) NOT NULL column could lead to a server exit.

I don’t think I’d call any of these urgent for all (unless you run the latest RHEL/CentOS with SSL connections + a DHE SSL cipher specifed with –ssl-cipher=DHE-RSA-…), but if running 5.5, especially if not a very recent 5.5, you should consider upgrading.
For reference, the full 5.5.45 changelog can be viewed here:
http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-45.html
Hope this helps.

MariaDB: InnoDB foreign key constraint errors

Introduction
A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. The purpose of the foreign key is to identify a particular row of the referenced table. Therefore, it is required that the foreign key is equal to the candidate key in some row of the primary table, or else have no value (the NULL value). This is called a referential integrity constraint between the two tables. Because violations of these constraints can be the source of many database problems, most database management systems provide mechanisms to ensure that every non-null foreign key corresponds to a row of the referenced table. Consider following simple example:create table parent (
id int not null primary key,
name char(80)
) engine=innodb;

create table child (
id int not null,
name char(80),
parent_id int,
foreign key(parent_id) references parent(id)
) engine=innodb;As far as I know, the following storage engines for MariaDB and/or MySQL support foreign keys:

InnoDB (both innodb_plugin and XtraDB)
PBXT (https://mariadb.com/kb/en/mariadb/about-pbxt/)
SolidDB for MySQL (http://sourceforge.net/projects/soliddb/)
ScaleDB (https://mariadb.com/kb/en/mariadb/scaledb/ and http://scaledb.com/pdfs/TechnicalOverview.pdf)
MySQL Cluster NDB 7.3 or later (https://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-ndb-innodb-engines.html)

MariaDB foreign key syntax is documented at https://mariadb.com/kb/en/mariadb/foreign-keys/ (and MySQL at http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html). While most of the syntax is parsed and checked when the CREATE TABLE or ALTER TABLE clause is parsed, there are still several error cases that can happen inside InnoDB. Yes, InnoDB has its own internal foreign key constraint parser (in dict0dict.c function dict_create_foreign_constraints_low()).
However, the error messages shown in CREATE or ALTER TABLE, and SHOW WARNINGS in versions of MariaDB prior to 5.5.45 and 10.0.21 are not very informative or clear. There are additional error messages if you issue SHOW ENGINE INNODB STATUS, which help, but were not an ideal solution. In this blog I’ll present a few of the most frequent error cases using MariaDB 5.5.44 and how these error messages are improved in MariaDB 5.5.45 and 10.0.21. I will use the default InnoDB (i.e. XtraDB) but innodb_plugin works very similarly.
Constraint name not unique
Foreign name constraint names must be unique in a database. However, the error message is unclear and leaves a lot unclear:————–
CREATE TABLE t1 (
id int(11) NOT NULL PRIMARY KEY,
a int(11) NOT NULL,
b int(11) NOT NULL,
c int not null,
CONSTRAINT test FOREIGN KEY (b) REFERENCES t1 (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
————–
Query OK, 0 rows affected (0.45 sec)

————–
CREATE TABLE t2 (
id int(11) NOT NULL PRIMARY KEY,
a int(11) NOT NULL,
b int(11) NOT NULL,
c int not null,
CONSTRAINT mytest FOREIGN KEY (c) REFERENCES t1(id),
CONSTRAINT test FOREIGN KEY (b) REFERENCES t2 (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
————–

ERROR 1005 (HY000): Can’t create table `test`.`t2` (errno: 121 “Duplicate key on write or update”)
————–
show warnings
————–

+———+——+——————————————————————————–+
| Level | Code | Message |
+———+——+——————————————————————————–+
| Error | 1005 | Can’t create table `test`.`t2` (errno: 121 “Duplicate key on write or update”) |
| Warning | 1022 | Can’t write; duplicate key in table ‘t2’ |
+———+——+——————————————————————————–+
2 rows in set (0.00 sec)These messages are not very helpful because there are two foreign key constraints. Looking into SHOW ENGINE INNODB STATUS we get a better message:show engine innodb status
————–
————————
LATEST FOREIGN KEY ERROR
————————
2015-07-30 12:37:48 7f44a1111700 Error in foreign key constraint creation for table `test`.`t2`.
A foreign key constraint of name `test`.`test`
already exists. (Note that internally InnoDB adds ‘databasename’
in front of the user-defined constraint name.)
Note that InnoDB’s FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:CREATE TABLE t1 (
id int(11) NOT NULL PRIMARY KEY,
a int(11) NOT NULL,
b int(11) NOT NULL,
c int not null,
CONSTRAINT test FOREIGN KEY (b) REFERENCES t1 (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
————–

Query OK, 0 rows affected (0.14 sec)

————–
CREATE TABLE t2 (
id int(11) NOT NULL PRIMARY KEY,
a int(11) NOT NULL,
b int(11) NOT NULL,
c int not null,
CONSTRAINT mytest FOREIGN KEY (c) REFERENCES t1(id),
CONSTRAINT test FOREIGN KEY (b) REFERENCES t2 (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
————–

ERROR 1005 (HY000): Can’t create table ‘test.t2’ (errno: 121)
————–
show warnings
————–

+———+——+————————————————————————————————————————————————————————————————————————————————————————————————————-+
| Level | Code | Message |
+———+——+————————————————————————————————————————————————————————————————————————————————————————————————————-+
| Warning | 121 | Create or Alter table `test`.`t2` with foreign key constraint failed. Foreign key constraint `test/test` already exists on data dictionary. Foreign key constraint names need to be unique in database. Error in foreign key definition: CONSTRAINT `test` FOREIGN KEY (`b`) REFERENCES `test`.`t2` (`id`). |
| Error | 1005 | Can’t create table ‘test.t2’ (errno: 121) |
+———+——+————————————————————————————————————————————————————————————————————————————————————————————————————-+
2 rows in set (0.00 sec)
No index
There should be an index for columns in a referenced table that contains referenced columns as the first columns.create table t1(a int, b int, key(b)) engine=innodb
————–
Query OK, 0 rows affected (0.46 sec)

————–
create table t2(a int, b int, constraint b foreign key (b) references t1(b), constraint a foreign key a (a) references t1(a)) engine=innodb
————–

ERROR 1005 (HY000): Can’t create table `test`.`t2` (errno: 150 “Foreign key constraint is incorrectly formed”)
————–
show warnings
————–

+———+——+—————————————————————————————————————————————————————–+
| Level | Code | Message |
+———+——+—————————————————————————————————————————————————————–+
| Warning | 150 | Create table ‘test/t2’ with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
|
| Error | 1005 | Can’t create table `test`.`t2` (errno: 150 “Foreign key constraint is incorrectly formed”) |
| Warning | 1215 | Cannot add foreign key constraint |
+———+——+—————————————————————————————————————————————————————–+
3 rows in set (0.00 sec)Fine but again we have no idea which foreign key it was. As before, there is a better message in the SHOW ENGINE INNODB STATUS output:LATEST FOREIGN KEY ERROR
————————
2015-07-30 13:44:31 7f30e1520700 Error in foreign key constraint of table test/t2:
foreign key a (a) references t1(a)) engine=innodb:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:create table t1(a int, b int, key(b)) engine=innodb
————–

Query OK, 0 rows affected (0.16 sec)

————–
create table t2(a int, b int, constraint b foreign key (b) references t1(b), constraint a foreign key a (a) references t1(a)) engine=innodb
————–

ERROR 1005 (HY000): Can’t create table ‘test.t2’ (errno: 150)
————–
show warnings
————–

+———+——+—————————————————————————————————————————————————————————————————————————————-+
| Level | Code | Message |
+———+——+—————————————————————————————————————————————————————————————————————————————-+
| Warning | 150 | Create table ‘`test`.`t2`’ with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns. Error close to foreign key a (a) references t1(a)) engine=innodb. |
| Error | 1005 | Can’t create table ‘test.t2’ (errno: 150) |
+———+——+—————————————————————————————————————————————————————————————————————————————-+
2 rows in set (0.00 sec)
Referenced table not found
A table that is referenced on foreign key constraint should exist in InnoDB data dictionary. If not:create table t1 (f1 integer primary key) engine=innodb
————–

Query OK, 0 rows affected (0.47 sec)

————–
alter table t1 add constraint c1 foreign key (f1) references t11(f1)
————–

ERROR 1005 (HY000): Can’t create table `test`.`#sql-2612_2` (errno: 150 “Foreign key constraint is incorrectly formed”)
————–
show warnings
————–

+———+——+—————————————————————————————————–+
| Level | Code | Message |
+———+——+—————————————————————————————————–+
| Error | 1005 | Can’t create table `test`.`#sql-2612_2` (errno: 150 “Foreign key constraint is incorrectly formed”) |
| Warning | 1215 | Cannot add foreign key constraint |
+———+——+—————————————————————————————————–+
show engine innodb status
————–
LATEST FOREIGN KEY ERROR
————————
2015-07-30 13:44:34 7f30e1520700 Error in foreign key constraint of table test/#sql-2612_2:
foreign key (f1) references t11(f1):
Cannot resolve table name close to:
(f1)Both messages are first referring to an internal table name and the foreign key error message is referring to an incorrect name. In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:create table t1 (f1 integer primary key) engine=innodb
————–

Query OK, 0 rows affected (0.11 sec)

————–
alter table t1 add constraint c1 foreign key (f1) references t11(f1)
————–

ERROR 1005 (HY000): Can’t create table ‘test.#sql-2b40_2’ (errno: 150)
————–
show warnings
————–

+———+——+—————————————————————————————————————————————————————————-+
| Level | Code | Message |
+———+——+—————————————————————————————————————————————————————————-+
| Warning | 150 | Alter table `test`.`t1` with foreign key constraint failed. Referenced table `test`.`t11` not found in the data dictionary close to foreign key (f1) references t11(f1). |
| Error | 1005 | Can’t create table ‘test.#sql-2b40_2’ (errno: 150) |
+———+——+—————————————————————————————————————————————————————————-+
2 rows in set (0.00 sec)

————–
show engine innodb status
————–
150730 13:50:36 Error in foreign key constraint of table `test`.`t1`:
Alter table `test`.`t1` with foreign key constraint failed. Referenced table `test`.`t11` not found in the data dictionary close to foreign key (f1) references t11(f1).
Temporal tables
Temporal tables can’t have foreign key constraints because temporal tables are not stored to the InnoDB data dictionary.create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb
————–

ERROR 1005 (HY000): Can’t create table `test`.`t2` (errno: 150 “Foreign key constraint is incorrectly formed”)
————–
show warnings
————–

+———+——+——————————————————————————————–+
| Level | Code | Message |
+———+——+——————————————————————————————–+
| Error | 1005 | Can’t create table `test`.`t2` (errno: 150 “Foreign key constraint is incorrectly formed”) |
| Warning | 1215 | Cannot add foreign key constraint |
+———+——+——————————————————————————————–+
2 rows in set (0.00 sec)

————–
show engine innodb status
————–
LATEST FOREIGN KEY ERROR
————————
2015-07-30 13:44:35 7f30e1520700 Error in foreign key constraint of table tmp/#sql2612_2_1:
foreign key(a) references t1(a)) engine=innodb:
Cannot resolve table name close to:
(a)) engine=innodb

————–
alter table t1 add foreign key(b) references t1(a)
————–

ERROR 1005 (HY000): Can’t create table `test`.`#sql-2612_2` (errno: 150 “Foreign key constraint is incorrectly formed”)
————–
show warnings
————–

+———+——+—————————————————————————————————–+
| Level | Code | Message |
+———+——+—————————————————————————————————–+
| Error | 1005 | Can’t create table `test`.`#sql-2612_2` (errno: 150 “Foreign key constraint is incorrectly formed”) |
| Warning | 1215 | Cannot add foreign key constraint |
+———+——+—————————————————————————————————–+
2 rows in set (0.00 sec)These error messages do not really help the user, because the actual reason for the error is not printed and the foreign key error references an internal table name. In MariaDB 5.5.45 and 10.0.21 this is clearly improved:create temporary table t1(a int not null primary key, b int, key(b)) engine=innodb
————–

Query OK, 0 rows affected (0.04 sec)

————–
create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb
————–

ERROR 1005 (HY000): Can’t create table ‘test.t2’ (errno: 150)
————–
show warnings
————–

+———+——+————————————————————————————————————————————————————————————-+
| Level | Code | Message |
+———+——+————————————————————————————————————————————————————————————-+
| Warning | 150 | Create table `tmp`.`t2`Ï with foreign key constraint failed. Referenced table `tmp`.`t1` not found in the data dictionary close to foreign key(a) references t1(a)) engine=innodb. |
| Error | 1005 | Can’t create table ‘test.t2’ (errno: 150) |
+———+——+————————————————————————————————————————————————————————————-+
2 rows in set (0.00 sec)

————–
alter table t1 add foreign key(b) references t1(a)
————–

ERROR 1005 (HY000): Can’t create table ‘test.#sql-2b40_2’ (errno: 150)
————–
show warnings
————–

+———+——+———————————————————————————————————————————————————————+
| Level | Code | Message |
+———+——+———————————————————————————————————————————————————————+
| Warning | 150 | Alter table `tmp`.`t1`Ï with foreign key constraint failed. Referenced table `tmp`.`t1` not found in the data dictionary close to foreign key(b) references t1(a). |
| Error | 1005 | Can’t create table ‘test.#sql-2b40_2’ (errno: 150) |
+———+——+———————————————————————————————————————————————————————+
2 rows in set (0.00 sec)
Column count does not match
There should be exactly the same number of columns in both the foreign key column list and the referenced column list. However, this currently raises the following error:create table t1(a int not null primary key, b int, key(b)) engine=innodb
————–

Query OK, 0 rows affected (0.17 sec)

————–
alter table t1 add foreign key(a,b) references t1(a)
————–

ERROR 1005 (HY000): Can’t create table ‘test.#sql-4856_1’ (errno: 150)
————–
show warnings
————–

+——-+——+—————————————————-+
| Level | Code | Message |
+——-+——+—————————————————-+
| Error | 1005 | Can’t create table ‘test.#sql-4856_1’ (errno: 150) |
+——-+——+—————————————————-+
1 row in set (0.00 sec)

—————–+
show engine innodb status;
—————–+
LATEST FOREIGN KEY ERROR
————————
150730 15:15:57 Error in foreign key constraint of table test/#sql-4856_1:
foreign key(a,b) references t1(a):
Syntax error close to: 2015-07-30 13:44:35 7f30e1520700 Error in foreign key constraint of table tmp/#sql2612_2_2: foreign key(b) references t1(a): Cannot resolve table name close to: (a)The error message is not clear and the foreign key error refers to an internal table name. In MariaDB 5.5.45 and 10.0.21 there is additional information:create table t1(a int not null primary key, b int, key(b)) engine=innodb
————–

Query OK, 0 rows affected (0.14 sec)

————–
alter table t1 add foreign key(a,b) references t1(a)
————–

ERROR 1005 (HY000): Can’t create table ‘test.#sql-2b40_2’ (errno: 150)
————–
show warnings
————–

+———+——+—————————————————————————————————————————————————————————————————————–+
| Level | Code | Message |
+———+——+—————————————————————————————————————————————————————————————————————–+
| Warning | 150 | Alter table `test`.`t1` with foreign key constraint failed. Foreign key constraint parse error in foreign key(a,b) references t1(a) close to ). Too few referenced columns, you have 1 when you should have 2. |
| Error | 1005 | Can’t create table ‘test.#sql-2b40_2’ (errno: 150) |
+———+——+—————————————————————————————————————————————————————————————————————–+
2 rows in set (0.00 sec)
Incorrect cascading
A user may define a foreign key constraint with ON UPDATE SET NULL or ON DELETE SET NULL. However, this requires that the referenced columns are not defined as NOT NULL. Currently, the error message on this situation is:create table t1 (f1 integer not null primary key) engine=innodb
————–

Query OK, 0 rows affected (0.40 sec)

————–
alter table t1 add constraint c1 foreign key (f1) references t1(f1) on update set null
————–

ERROR 1005 (HY000): Can’t create table `test`.`#sql-2612_2` (errno: 150 “Foreign key constraint is incorrectly formed”)
————–
show warnings
————–

+———+——+—————————————————————————————————–+
| Level | Code | Message |
+———+——+—————————————————————————————————–+
| Error | 1005 | Can’t create table `test`.`#sql-2612_2` (errno: 150 “Foreign key constraint is incorrectly formed”) |
| Warning | 1215 | Cannot add foreign key constraint |
+———+——+———————————————————————————————

——–+
show engine innodb status;
——–+
LATEST FOREIGN KEY ERROR
————————
2015-07-30 13:44:37 7f30e1520700 Error in foreign key constraint of table test/#sql-2612_2:
foreign key (f1) references t1(f1) on update set null:
You have defined a SET NULL condition though some of the
columns are defined as NOT NULL.Both error messages are not very useful, because the first does not really tell how the foreign key constraint is incorrectly formed and later does not say which column has the problem. This is improved in MariaDB 5.5.45 and 10.0.21:create table t1 (f1 integer not null primary key) engine=innodb
————–

Query OK, 0 rows affected (0.10 sec)

————–
alter table t1 add constraint c1 foreign key (f1) references t1(f1) on update set null
————–

ERROR 1005 (HY000): Can’t create table ‘test.#sql-2b40_2’ (errno: 150)
————–
show warnings
————–

+———+——+———————————————————————————————————————————————————————————————————————————+
| Level | Code | Message |
+———+——+———————————————————————————————————————————————————————————————————————————+
| Warning | 150 | Alter table `test`.`t1` with foreign key constraint failed. You have defined a SET NULL condition but column f1 is defined as NOT NULL in foreign key (f1) references t1(f1) on update set null close to on update set null. |
| Error | 1005 | Can’t create table ‘test.#sql-2b40_2’ (errno: 150) |
+———+——+———————————————————————————————————————————————————————————————————————————+
2 rows in set (0.00 sec)
Incorrect types
Column types for foreign key columns and referenced columns should match and use the same character set. If they do not, you currently get:create table t1 (id int not null primary key, f1 int, f2 int, key(f1)) engine=innodb
————–

Query OK, 0 rows affected (0.47 sec)

————–
create table t2(a char(20), key(a), foreign key(a) references t1(f1)) engine=innodb
————–

ERROR 1005 (HY000): Can’t create table `test`.`t2` (errno: 150 “Foreign key constraint is incorrectly formed”)
————–
show warnings
————–

+———+——+—————————————————————————————————————————————————————–+
| Level | Code | Message |
+———+——+—————————————————————————————————————————————————————–+
| Warning | 150 | Create table ‘test/t2’ with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
|
| Error | 1005 | Can’t create table `test`.`t2` (errno: 150 “Foreign key constraint is incorrectly formed”) |
| Warning | 1215 | Cannot add foreign key constraint |
+———+——+—————————————————————————————————————————————————————–+
3 rows in set (0.00 sec)

——–+
show engine innodb status;
——–+
LATEST FOREIGN KEY ERROR
————————
2015-07-30 13:44:39 7f30e1520700 Error in foreign key constraint of table test/t2:
foreign key(a) references t1(f1)) engine=innodb:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.But do we have an index for the referenced column f1 in the table t2? So if there are multiple columns in both the foreign key column list and the referenced column list, where do we look for the error? In MariaDB 5.5.45 and 10.0.21 this is improved by:create table t1 (id int not null primary key, f1 int, f2 int, key(f1)) engine=innodb
————–

Query OK, 0 rows affected (0.15 sec)

————–
create table t2(a char(20), key(a), foreign key(a) references t1(f1)) engine=innodb
————–

ERROR 1005 (HY000): Can’t create table ‘test.t2’ (errno: 150)
————–
show warnings
————–

+———+——+—————————————————————————————————————————————————————————————————-+
| Level | Code | Message |
+———+——+—————————————————————————————————————————————————————————————————-+
| Warning | 150 | Create table `test`.`t2` with foreign key constraint failed. Field type or character set for column a does not mach referenced column f1 close to foreign key(a) references t1(f1)) engine=innodb |
| Error | 1005 | Can’t create table ‘test.t2’ (errno: 150) |
+———+——+—————————————————————————————————————————————————————————————————-+
2 rows in set (0.00 sec)
Conclusions
There are several different ways to incorrectly define a foreign key constraint. In many cases when using earlier versions of MariaDB (and MySQL), the error messages produced by these cases were not very clear or helpful. In MariaDB 5.5.45 and 10.0.21 there are clearly improved error messages to help out the user. Naturally, there is always room for further improvements, so feedback is more than welcome!
References

https://mariadb.atlassian.net/browse/MDEV-6697
https://mariadb.atlassian.net/browse/MDEV-8524

 

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569