Shulou(Shulou.com)05/31 Report--

This article mainly explains "the reason why MySQL can't create a table". Friends who are interested may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "the reason why MySQL can't create a table".

Today, I helped my colleagues deal with a very interesting problem before I got off work. Although I knew the direction of the problem and the general reason, I still didn't think of how to reproduce the problem at that time. I went home in the evening, cleaned up, turned on the computer, reverse reasoning, verification, testing, and reappearance, so there was a complete interpretation of the problem.

Problem background

The description of the problem sounds simple: an error was thrown while deploying a data change. I leaned over with curiosity and saw the error.

ERROR 1005 (HY000): Can't create table 'xxx.QRTZ_JOB_DETAILS' (errno: 150) what is the create table statement like? is there anything special about it? There is nothing special about this statement, and it doesn't use any new version of the features and syntax.

DROP TABLE IF EXISTS `QRTZ_JOB_ DETAILS`

CREATE TABLE `QRTZ_JOB_ DETAILS` (

`NAME` varchar (SCHED_) NOT NULL

`NAME` varchar (200) NOT NULL

`JOB_ GROUP` varchar (200) NOT NULL

`Ddescription` varchar (250) DEFAULT NULL

`NAME` varchar (JOB_CLASS_) NOT NULL

`IS_ DURABLE` varchar (1) NOT NULL

`IS_ NONCONCURRENT` varchar (1) NOT NULL

`IS_UPDATE_ Data` varchar (1) NOT NULL

`REQUESTS_ recovery` varchar (1) NOT NULL

`JOB_ data` blob

PRIMARY KEY (`SCHED_ NAME`, `JOB_ NAME`, `JOB_ GROUP`)

ENGINE=InnoDB DEFAULT CHARSET=utf8 's current problem is to create more than 10 tables, of which only 2 failed. It sounds awkward to create this problem alone.

The intuition about this problem is that the bug or parameter setting is beyond the limit, but it's just a guess. You must be rigorous in dealing with the problem and check it out with curiosity, or this is a bottomless pit, which will only bring you more secrets and know why it doesn't know why.

Preliminary analysis of the problem

Such a situation made me very interested in this issue, and I decided to try and see if I could find a persuasive evidence. Looking at this create statement, the mind is like a sieve for all kinds of exclusion, too many table fields, too many primary key fields, table attribute formatting, lob field influence, database field number overflow and so on, possible syntax restrictions and so on.

I started to do the following test, which gave no room for all the above guesses, because I just created a field, but it didn't work.

CREATE TABLE `QRTZ_JOB_ DETAILS` (`SCHED_ NAME` varchar) NOT NULL)

ERROR 1005 (HY000): some students in Can't create table 'test.QRTZ_JOB_DETAILS' (errno: 150) may be wondering if it is caused by case sensitivity.

Show variables like'% case%'

+-+ +

| | Variable_name | Value |

+-+ +

| | lower_case_file_system | OFF |

| | lower_case_table_names | 0 | |

Case-sensitive settings are turned on in this environment, but this is not enough to make the problem unsolvable.

Is there any related grammatical gray area involved? I added an S. after the table name.

> create table QRTZ_JOB_DETAILSS (id int)

Query OK, 0 rows affected (0.13 sec) this means that the limitations of this table have nothing to do with syntax traps, but creating this table is so tangled.

> create table QRTZ_JOB_DETAILS (id int)

ERROR 1005 (HY000): Can't create table 'seal.QRTZ_JOB_DETAILS' (errno: 150) and a temporary solution is to create a lowercase table. There is no problem with the creation process, but there is no way for developers to move forward, because their application side is a third-party Quarz scheduling project, and they identify it in uppercase format.

Some students may say that it may be caused by foreign keys. I checked the deployment script and found that there was not even a shadow of REFERENCE in it. There was no foreign key in the deployment script at all.

Some students may say that there is a problem to see what the log says. Mysql provides very little information on this point. There is only one line of errors reported in error log, and there is no other more specific information.

At the same time, I was also a little hesitant. I checked the impact of the database version and conducted a comparative test in versions 5.1 and 5.5, but no problem was found, but the problem still exists.

Further communicate with the developer.

With doubt, I further communicated with my developer classmates. The script they quoted was a third-party open source project, Quarz, in which the script was generated using navicat, and this change was deployed in their test environment, which was version 5.1, while the online environment was 5.5. the script provided by the third party involved many tables, and I got a script and deployed it in my own test environment. I can't believe there's no mistake.

Later, the developer further confirmed that all the tables with the word QRTZ in the database were deleted (provided there was a backup), because this was a batch of changes, either available or fallback. After deleting these tables, they tried to create the table that had just failed again, and this time it was a success. And in this process, I did not do any special operation, the development students finally said helplessly, whether it has something to do with character ah, if colleagues hear, it is not allowed to vomit blood.

There are clues to find a breakthrough in the problem

In terms of technical problems, many may indeed be caused by bug, but we can't classify all the seemingly strange problems as bug, and from the point of view of many of the problems I deal with, although many of them can be classified as bug, many of the root causes of the problems are still caused by some very basic mistakes.

How to analyze this problem? mysql's query log records the process of all operations, which brings me great convenience, so I can see a basic situation in the process of each step. It is clear what attempts have been made at that time and what changes have been made before. Of course, this log gives me some clear information, but I haven't found the cause of the problem yet.

Before cleaning up the table structure, I subconsciously made a basic backup of information, which is the case of cleaning up previous tables.

> show tables like 'QRTZ%'

+-+

| | Tables_in_seal (QRTZ%) |

+-+

| | QRTZ_BLOB_TRIGGERS |

| | QRTZ_CALENDARS |

| | QRTZ_CRON_TRIGGERS |

| | QRTZ_FIRED_TRIGGERS |

| | QRTZ_JOB_LISTENERS |

| | QRTZ_LOCKS |

| | QRTZ_PAUSED_TRIGGER_GRPS |

| | QRTZ_SCHEDULER_STATE |

| | QRTZ_SIMPLE_TRIGGERS |

| | QRTZ_SIMPROP_TRIGGERS |

| | QRTZ_TRIGGER_LISTENERS |

+-I opened the deployed script and began to look carefully that there was no foreign key information in the script, but I felt that the direction of the problem was clear, just hidden, or missed in the previous analysis.

When I saw the unintentional check message in the log, I couldn't help but see that the table that failed to create was QRTZ_JOB_DETAILS, and the only table with a similar name was QRTZ_JOB_LISTENERS. This table structure definition information is very clear.

> show create table QRTZ_JOB_LISTENERS\ G

* * 1. Row *

Table: QRTZ_JOB_LISTENERS

Create Table: CREATE TABLE `LISTENERS` (

`NAME` varchar (200) NOT NULL

`JOB_ GROUP` varchar (200) NOT NULL

`LISTENER` varchar (JOB_) NOT NULL

PRIMARY KEY (`JOB_ NAME`, `JOB_ GROUP`, `JOB_ LISTENER`)

KEY `JOB_ NAME` (`JOB_ NAME`, `JOB_ GROUP`)

CONSTRAINT `QRTZ_JOB_LISTENERS_ibfk_ 1` FOREIGN KEY (`JOB_ NAME`, `JOB_ GROUP`) REFERENCES `QRTZ_JOB_ DETAILS` (`JOB_ NAME`, `JOB_GROUP

) ENGINE=InnoDB DEFAULT CHARSET=utf8

There is a foreign key in 1 row in set (0.00 sec) QRTZ_JOB_LISTENERS, which points to QRTZ_JOB_DETAILS, but in fact there is no foreign key information in the script. There is only one possibility that QRTZ_JOB_LISTENERS is not in this script and is likely to be created outside of this deployment. This is particularly important and is a breakthrough in this issue.

How to verify the previous status? I looked at the backup strategy of this environment. I was surprised that there would be a backup every day. I simply filtered it, and the cause of the problem began to become clear.

# grep "CREATE TABLE\ `QRTZ_" * 33-7*.sql | sort | uniq

CREATE TABLE `QRTZ_BLOB_ TRIGGERS` (

CREATE TABLE `QRTZ_ CALENDARS` (

CREATE TABLE `QRTZ_CRON_ TRIGGERS` (

CREATE TABLE `QRTZ_FIRED_ TRIGGERS` (

CREATE TABLE `QRTZ_JOB_ DETAILS` (

CREATE TABLE `LISTENERS` (CREATE TABLE

CREATE TABLE `QRTZ_ LOCKS` (

CREATE TABLE `QRTZ_PAUSED_TRIGGER_ GRPS` (

CREATE TABLE `QRTZ_SCHEDULER_ state` (

CREATE TABLE `QRTZ_SIMPLE_ TRIGGERS` (

CREATE TABLE `QRTZ_SIMPROP_ TRIGGERS` (

CREATE TABLE `LISTENERS` (CREATE TABLE

CREATE TABLE `QRTZ_ TRIGGERS` (

And in this way, the problem is a little more complicated than we thought. Tables QRTZ_JOB_DETAILS and QRTZ_JOB_LISTENERS existed before, but this deployment change, the developer only submitted the QRTZ_JOB_DETAILS change.

Simulated recurrence problem

With the above analysis, the cause of the problem is very clear, because the table QRTZ_JOB_DETAILS existed before, and it is the foreign key association table of QRTZ_JOB_LISTENERS. This time, only QRTZ_JOB_DETAILS is changed. Delete first, and then the process will fail because of foreign key dependencies.

Here we have to mention the magic assist of the navicat tool, because normally delete a table, if there is a foreign key reference is definitely not deleted, there will be the following error.

> DROP TABLE IF EXISTS `QRTZ_JOB_ DETAILS`

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails but navicat does some work by automatically generating some auxiliary script content with the following statement before the script is executed, so that the table can be deleted.

> SET FOREIGN_KEY_CHECKS=0

Query OK, 0 rows affected (0.00 sec)

> DROP TABLE IF EXISTS `QRTZ_JOB_ DETAILS`

Query OK, 0 rows affected (0. 00 sec) so that the problem can be easily repeated.

> CREATE TABLE `DETAILS` (`SCHED_ NAME` varchar) NOT NULL

ERROR 1005 (HY000): Can't create table 'test.QRTZ_JOB_DETAILS' (errno: 150)

Add, use this command to see the meaning of 150 error

# perror 150

MySQL error code 150: Foreign key constraint is incorrectly formed

At this point, I believe you have a deeper understanding of "the reason why MySQL cannot create a table". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.