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

How to solve the failure of creating a table in MySQL? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

Today, a friend asked me a question about creating tables in MySQL. The phenomenon of the problem was that it failed to create tables. According to his feedback, the problem was rather strange.

CREATE TABLE XXX

.. More than 260 fields are omitted here

`xxxxIsAllowIn`varchar (4) COLLATE utf8_bin DEFAULT NULL COMMENT'xx access (Yes, No)'

`xxxxIsAllowIn` varchar (30) COLLATE utf8_bin DEFAULT NULL COMMENT 'Finance-admittance'

PRIMARY KEY (`SERIALNO`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='? "3 è s?

Yes, you read it correctly, and there are garbled codes. According to the feedback from friends, it can be created successfully in the production environment, but the test environment fails.

The error message is:

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

I copied the text locally and wanted to reproduce it. As a result, the direct execution of the garbled code failed. In this case, my colleague helped me to filter the questions and delete the comments in the following way.

Cat a.sql | sed's Universe Compact.

So it saves a lot of trouble, so I will continue to analyze this problem. Generally speaking, this error seems to be that the data in a single row exceeds the limit, because there is a limit of 65535 per row in MySQL, which must be the reason.

However, the feedback from friends did not exceed this limit. According to the character types in it, it was found that it did not reach 65535.

So this problem becomes more subtle. Let's talk about several solutions.

Solution 1:

Modify the storage engine to set to myisam

...

KEY `idx_ customerName` (`CUSTOMERNAME`)

) ENGINE=myisam DEFAULT CHARSET=utf8 COLLATE=utf8_bin

"c.sql" 276L, 16070C written

Mysql > source c.sql

Query OK, 0 rows affected (0.07 sec)

MyISAM has three row storage formats: fixed/dynamic/compressed,InnoDB adds the format of Barracuda on this basis.

The default parameter settings in 5.7 are as follows:

Mysql > show variables like'% format'

+-+ +

| | Variable_name | Value |

+-+ +

| | binlog_format | ROW |

| | date_format |% Y-%m-%d |

| | datetime_format |% Y-%m-%d% H:%i:%s |

| | default_week_format | 0 | |

| | innodb_default_row_format | dynamic |

| | innodb_file_format | Barracuda |

| | time_format |% H:%i:%s |

+-+ +

7 rows in set (0.00 sec)

So the difference now is between MyISAM and InnoDB.

The format of the shared tablespace is Antelope, which is the default in 5. 5.

Solution 2

I did some tests on this question. The settings of character set and row_format are compared.

) ENGINE=innodb row_format=dynamic DEFAULT CHARSET=utf8 COLLATE=utf8_bin

"c.sql" 276L, 16090C written

Mysql > source c.sql

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB p

) ENGINE=innodb row_format=compact DEFAULT CHARSET=utf8 COLLATE=utf8_bin

"c.sql" 276L, 16090C written

Mysql > source c.sql

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

) ENGINE=innodb DEFAULT CHARSET=latin1

"c.sql" 276L, 16056C written

Mysql > source c.sql

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

A preliminary conclusion is that innodb_strict_mode is set to off. The default is 5.7. of course, starting from the MySQL5.5 version, you can enable InnoDB strict check mode. If you use the page data compression feature, it is recommended to enable this feature. When creating a table, changing a table, and creating an index, if there is an error in the writing, there will be no warning, but will directly throw the error, which can directly stifle the problem in the cradle.

Of course, the phenomenon of the problem in this is really quite complicated.

Solution 3:

Starting with the design of the table structure, split the logic of the table as much as possible, and divide it into multiple tables. Try not to have too many fields in a table. The number of databases and tables is as small as possible; there are generally no more than 50 databases, and the number of data tables under each database is generally no more than 500 (including partition tables). It is obvious that the design of this table is to start to expand vertically according to the needs of the business. in fact, you can split a logical table, and the logical data is easy to expand continuously, rather than constantly expanding at the field level.

This is the answer to how to solve the problem of the failure to create a table in MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to solve, you can follow the industry information channel for more related knowledge.

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.