[Err] 1071 - Specified key was too long; max key length is 767 bytes

￡神魔★判官ぃ 2023-07-23 11:56 29阅读 0赞

另外一台服务器到期了  
所以新购了一台服务器  
今天安装好了环境  
准备将数据迁移过来

执行导入SQL：`source /data/sql/77dh_20200407_2034.txt`  
![在这里插入图片描述][20200407221510846.png]发现报错如下

> ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

搜索得知需要将如下变量设置为如下值：

*  innodb\_file\_format = Barracuda
 *  innodb\_file\_format\_check = ON
 *  innodb\_file\_format\_max = Barracuda  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5oZW55dWFu_size_16_color_FFFFFF_t_70]  
    查看 `innodb_file_format` 相关变量SQL：  
    `show variables like '%innodb_file_format%';`

更改变量SQL：  
`set global innodb_file_format=Barracuda;`  
`set global innodb_file_format_check=ON;`  
`set global innodb_file_format_max=Barracuda;`

再次执行SQL发现还是报错

> \[Err\] 1071 - Specified key was too long; max key length is 767 bytes

原因是未修改 `/etc/my.cnf`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5oZW55dWFu_size_16_color_FFFFFF_t_70 1]增加这两行配置

`innodb_large_prefix=on`  
`innodb_file_format = BARRACUDA`

重启mysql  
再次执行SQL  
成功执行！

[20200407221510846.png]: /images/20230528/3eb43d42c8c240ea90f13ecb33cae697.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5oZW55dWFu_size_16_color_FFFFFF_t_70]: /images/20230528/eef93d05705a4504aace4712508acff5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5oZW55dWFu_size_16_color_FFFFFF_t_70 1]: /images/20230528/6b6cf83f02e045188f32429119c906e5.png