Page 1 of 1
Specified key was too long; during db upgrade script
Posted:
Wed Dec 14, 2016 5:56 pmby rockgeneral
As you can see from my signature we are using an old version of vicidial. I have an issue with the filter phone group functionality that I recently asked for help on, on this forum. Matt correctly pointed out that the version I was using was very old. I am going to upgrade to the latest version however I was testing the upgrade procedure on another vicidial server I had. While running the upgrade_2.10.sql script I received 2 warnings on two different create table statements.
Warning (Code 1071): Specified key was too long; max key length is 1000 bytes
The statements were:
CREATE TABLE www_phrases (
phrase_id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
phrase_text VARCHAR(10000) default '',
php_filename VARCHAR(255) NOT NULL,
php_directory VARCHAR(255) default '',
source VARCHAR(20) default '',
index (phrase_text)
) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Edit:
The resulting key was shortened to: KEY `phrase_text` (`phrase_text`(333))
CREATE TABLE vicidial_language_phrases (
phrase_id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
language_id VARCHAR(100) NOT NULL,
english_text VARCHAR(10000) default '',
translated_text TEXT,
source VARCHAR(20) default '',
modify_date TIMESTAMP,
index (language_id),
index (english_text)
) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Edit:
The resulting key was shortened to: KEY `english_text` (`english_text`(333))
MySQL version:
mysql Ver 15.1 Distrib 5.5.33-MariaDB, for Linux (x86_64) using readline 5.1
Is there a setting that I can apply to change the max key length for MyISAM tables? Or is there some other fix for this? Your help is greatly appreciated.
Thank you,
rockgeneral
Single Server
VERSION: 2.4b0.5 BUILD: 110506-1612 (Installed via ViciBox, I don't recall which version)
Asterisk 1.4.21.2-vici
Re: Specified key was too long; during db upgrade script
Posted:
Wed Dec 14, 2016 10:02 pmby mflorell
I can't say I've ever run into that before, even when upgrading older systems. In my experience, mysql will automatically set the index to the maximum allowable first 333 characters in the field to be used in the index. You can also manually set this KEY_BLOCK_SIZE when defining indexes.
Re: Specified key was too long; during db upgrade script
Posted:
Thu Dec 15, 2016 9:43 amby rockgeneral
Matt,
To clarify, I checked the table schema after running the script and MySQL did indeed create the index with the max allowable key size for the datatype of 333. So is the intended key size for these fields actually 333 (instead of the full field size) and I can leave them this way? Or is there some other action I should take?
Thanks again for your help!
rockgeneral
Single Server
VERSION: 2.4b0.5 BUILD: 110506-1612 (Installed via ViciBox, I don't recall which version)
Asterisk 1.4.21.2-vici
Re: Specified key was too long; during db upgrade script
Posted:
Thu Dec 15, 2016 1:49 pmby mflorell
Yes, that will work, leaving it like that.
Re: Specified key was too long; during db upgrade script
Posted:
Thu Dec 15, 2016 2:05 pmby rockgeneral
Thanks Matt!
Re: Specified key was too long; during db upgrade script
Posted:
Wed Mar 27, 2019 8:35 amby cavagnaro
Hi,
Installing from scratch and having same issue, how exactly did you fix this?
- Code: Select all
ERROR 1071 (42000) at line 3348: Specified key was too long; max key length is 1000 bytes
This what I get. Already tried to create the DB with charset UTF8 and set global as INNODB but not working so far...
Any idea?
Re: Specified key was too long; during db upgrade script
Posted:
Wed Mar 27, 2019 6:14 pmby mflorell
You should not use InnoDB storage engines for VICIdial.
Re: Specified key was too long; during db upgrade script
Posted:
Wed Mar 27, 2019 7:18 pmby cavagnaro
Yes, I know, I created the table as "create database asterisk", and inmediatly did the script execution.
I can see it does creates some tables. For example:
| vicidial_voicemail |
| vicidial_webservers |
| vicidial_xfer_log |
| vicidial_xfer_presets |
| vicidial_xfer_stats |
| vtiger_rank_data |
| vtiger_rank_parameters |
| vtiger_vicidial_roles |
| web_client_sessions
But when it arrives to CREATE TABLE www_phrases it fails.
If I do a "show table status" all tables created are shown as MyISAM.
Re: Specified key was too long; during db upgrade script
Posted:
Wed Mar 27, 2019 7:43 pmby cavagnaro
This is what "show engines" output
- Code: Select all
MariaDB [DialerDB]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | gzip-compresses tables for a low storage footprint | NO | NO | NO |
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| FEDERATED | YES | Allows to access tables on other MariaDB servers, supports transactions and more | YES | NO | YES |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
Re: Specified key was too long; during db upgrade script
Posted:
Wed Mar 27, 2019 7:43 pmby cavagnaro
show table status;
- Code: Select all
| vicidial_xfer_stats | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | 2019-03-28 00:40:42 | 2019-03-28 00:40:42 | NULL | utf8mb4_general_ci | NULL | | |
| vtiger_rank_data | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | 2019-03-28 00:40:42 | 2019-03-28 00:40:42 | NULL | utf8mb4_general_ci | NULL | | |
| vtiger_rank_parameters | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | 2019-03-28 00:40:42 | 2019-03-28 00:40:42 | NULL | utf8mb4_general_ci | NULL | | |
| vtiger_vicidial_roles | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | 2019-03-28 00:40:42 | 2019-03-28 00:40:42 | NULL | utf8mb4_general_ci | NULL | | |
| web_client_sessions | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | 2019-03-28 00:40:42 | 2019-03-28 00:40:42 | NULL | utf8mb4_general_ci | NULL | | |
Re: Specified key was too long; during db upgrade script
Posted:
Wed Mar 27, 2019 8:06 pmby cavagnaro
I changed the default engine to MyISAM, created the DB again and same issue.
- Code: Select all
[mysqld]
default-storage-engine=MyISAM
Re: Specified key was too long; during db upgrade script
Posted:
Wed Mar 27, 2019 8:07 pmby cavagnaro
- Code: Select all
MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | DEFAULT | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | gzip-compresses tables for a low storage footprint | NO | NO | NO |
| InnoDB | YES | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| FEDERATED | YES | Allows to access tables on other MariaDB servers, supports transactions and more | YES | NO | YES |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
Re: Specified key was too long; during db upgrade script
Posted:
Thu Mar 28, 2019 5:11 amby mflorell
What version of MariaDB are you using?
Re: Specified key was too long; during db upgrade script
Posted:
Fri Mar 29, 2019 4:37 pmby cavagnaro
mysql Ver 15.1 Distrib 10.0.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
I found that if doing:
- Code: Select all
mysql -uroot -p DialerDB < MySQL_AST_CREATE_tables.sql
Fails, but I connect to MySQL via mysql -p and then in the CLI i run the SQL statements, it works...
So no clue what the issue is but made it work like that
Re: Specified key was too long; during db upgrade script
Posted:
Fri Mar 29, 2019 5:20 pmby williamconley
cavagnaro wrote:mysql Ver 15.1 Distrib 10.0.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
I found that if doing:
- Code: Select all
mysql -uroot -p DialerDB < MySQL_AST_CREATE_tables.sql
Fails, but ...
what message does it show when it fails?
Re: Specified key was too long; during db upgrade script
Posted:
Mon Apr 01, 2019 11:17 amby cavagnaro
Hi
Mentioned above:
- Code: Select all
ERROR 1071 (42000) at line 3348: Specified key was too long; max key length is 1000 bytes
Re: Specified key was too long; during db upgrade script
Posted:
Tue Apr 02, 2019 10:24 amby williamconley
cavagnaro wrote:mysql Ver 15.1 Distrib 10.0.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
I found that if doing:
- Code: Select all
mysql -uroot -p DialerDB < MySQL_AST_CREATE_tables.sql
Fails, but I connect to MySQL via mysql -p and then in the CLI i run the SQL statements, it works...
So no clue what the issue is but made it work like that
cavagnaro wrote:Hi
Mentioned above:
- Code: Select all
ERROR 1071 (42000) at line 3348: Specified key was too long; max key length is 1000 bytes
"the SQL statements" that run one way but not another ... so what is on line 3348? And you say it runs if you do so manually but NOT if you run it in the .sql file? I suppose the sql file may start up with different defaults.
mflorell wrote:I can't say I've ever run into that before, even when upgrading older systems. In my experience, mysql will automatically set the index to the maximum allowable first 333 characters in the field to be used in the index. You can also manually set this KEY_BLOCK_SIZE when defining indexes.
Re: Specified key was too long; during db upgrade script
Posted:
Tue Apr 02, 2019 3:00 pmby cavagnaro
in that line is the declaration of a field just with value(10000) (www_phrases).
Yeah, I also guess has something to do with encoding or similar.
This may help anyone who faces similar problem in the future as a workaround at least