Specified key was too long; during db upgrade script

All installation and configuration problems and questions

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

Specified key was too long; during db upgrade script

Postby rockgeneral » Wed Dec 14, 2016 5:56 pm

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
System Info:
ViciBox_v9.x86_64-9.0.2.iso | Version: 2.14b0.5 SVN: 3551 | DB Schema Version: 1650 | Asterisk 13.29.2-vici | Single Server
rockgeneral
 
Posts: 93
Joined: Thu Mar 04, 2010 9:28 pm

Re: Specified key was too long; during db upgrade script

Postby mflorell » Wed Dec 14, 2016 10:02 pm

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.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Specified key was too long; during db upgrade script

Postby rockgeneral » Thu Dec 15, 2016 9:43 am

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
System Info:
ViciBox_v9.x86_64-9.0.2.iso | Version: 2.14b0.5 SVN: 3551 | DB Schema Version: 1650 | Asterisk 13.29.2-vici | Single Server
rockgeneral
 
Posts: 93
Joined: Thu Mar 04, 2010 9:28 pm

Re: Specified key was too long; during db upgrade script

Postby mflorell » Thu Dec 15, 2016 1:49 pm

Yes, that will work, leaving it like that.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Specified key was too long; during db upgrade script

Postby rockgeneral » Thu Dec 15, 2016 2:05 pm

Thanks Matt!
System Info:
ViciBox_v9.x86_64-9.0.2.iso | Version: 2.14b0.5 SVN: 3551 | DB Schema Version: 1650 | Asterisk 13.29.2-vici | Single Server
rockgeneral
 
Posts: 93
Joined: Thu Mar 04, 2010 9:28 pm

Re: Specified key was too long; during db upgrade script

Postby cavagnaro » Wed Mar 27, 2019 8:35 am

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?
cavagnaro
 
Posts: 38
Joined: Thu Feb 28, 2019 1:39 pm

Re: Specified key was too long; during db upgrade script

Postby mflorell » Wed Mar 27, 2019 6:14 pm

You should not use InnoDB storage engines for VICIdial.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Specified key was too long; during db upgrade script

Postby cavagnaro » Wed Mar 27, 2019 7:18 pm

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.
cavagnaro
 
Posts: 38
Joined: Thu Feb 28, 2019 1:39 pm

Re: Specified key was too long; during db upgrade script

Postby cavagnaro » Wed Mar 27, 2019 7:43 pm

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         |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
cavagnaro
 
Posts: 38
Joined: Thu Feb 28, 2019 1:39 pm

Re: Specified key was too long; during db upgrade script

Postby cavagnaro » Wed Mar 27, 2019 7:43 pm

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 |                |         |
cavagnaro
 
Posts: 38
Joined: Thu Feb 28, 2019 1:39 pm

Re: Specified key was too long; during db upgrade script

Postby cavagnaro » Wed Mar 27, 2019 8:06 pm

I changed the default engine to MyISAM, created the DB again and same issue.
Code: Select all
[mysqld]
default-storage-engine=MyISAM
cavagnaro
 
Posts: 38
Joined: Thu Feb 28, 2019 1:39 pm

Re: Specified key was too long; during db upgrade script

Postby cavagnaro » Wed Mar 27, 2019 8:07 pm

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         |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
cavagnaro
 
Posts: 38
Joined: Thu Feb 28, 2019 1:39 pm

Re: Specified key was too long; during db upgrade script

Postby mflorell » Thu Mar 28, 2019 5:11 am

What version of MariaDB are you using?
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Specified key was too long; during db upgrade script

Postby cavagnaro » Fri Mar 29, 2019 4:37 pm

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
 
Posts: 38
Joined: Thu Feb 28, 2019 1:39 pm

Re: Specified key was too long; during db upgrade script

Postby williamconley » Fri Mar 29, 2019 5:20 pm

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?
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: Specified key was too long; during db upgrade script

Postby cavagnaro » Mon Apr 01, 2019 11:17 am

Hi
Mentioned above:
Code: Select all
ERROR 1071 (42000) at line 3348: Specified key was too long; max key length is 1000 bytes
cavagnaro
 
Posts: 38
Joined: Thu Feb 28, 2019 1:39 pm

Re: Specified key was too long; during db upgrade script

Postby williamconley » Tue Apr 02, 2019 10:24 am

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.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: Specified key was too long; during db upgrade script

Postby cavagnaro » Tue Apr 02, 2019 3:00 pm

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
cavagnaro
 
Posts: 38
Joined: Thu Feb 28, 2019 1:39 pm


Return to Support

Who is online

Users browsing this forum: asterisknoob, Google [Bot] and 100 guests