Specified key was too long; during db upgrade script

[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

[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.

[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

[mflorell]

Yes, that will work, leaving it like that.

[rockgeneral]

Thanks Matt!

[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?

[mflorell]

You should not use InnoDB storage engines for VICIdial.

[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.

[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         |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+

[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 |                |         |


[cavagnaro]

I changed the default engine to MyISAM, created the DB again and same issue.

Code: Select all

[mysqld]
default-storage-engine=MyISAM


[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         |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+


[mflorell]

What version of MariaDB are you using?

[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

[williamconley]

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?

[cavagnaro]

Hi
Mentioned above:

Code: Select all

ERROR 1071 (42000) at line 3348: Specified key was too long; max key length is 1000 bytes


[williamconley]

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

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.

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.

[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