Mailtrain-org / mailtrain

Self hosted newsletter app
GNU General Public License v3.0
5.53k stars 692 forks source link

Specified key was too long; max key length is 767 bytes #1350

Closed DavidSamir closed 8 months ago

DavidSamir commented 1 year ago

I've installed Mailtrain v2 on CentOS 7 followed the documentation steps,

the installation was a success but the project is not running properly.

when i check the project status systemctl status mailtrain

May 25 15:45:54 centos-mailtrain node[1686]: ERR! at Promise._settlePromise0 (/opt/mailtrain/server/node_modules/bluebird/js/release/promise.js:649:10) { May 25 15:45:54 centos-mailtrain node[1686]: ERR! code: 'ER_TOO_LONG_KEY', May 25 15:45:54 centos-mailtrain node[1686]: ERR! errno: 1071, May 25 15:45:54 centos-mailtrain node[1686]: ERR! sqlMessage: 'Specified key was too long; max key length is 767 bytes', May 25 15:45:54 centos-mailtrain node[1686]: ERR! sqlState: '42000', May 25 15:45:54 centos-mailtrain node[1686]: ERR! index: 4, May 25 15:45:54 centos-mailtrain systemd[1]: Unit mailtrain.service entered failed state. May 25 15:45:54 centos-mailtrain node[1686]: ERR! sql: 'ALTER TABLE blacklist CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE campaign_links CONVERT TO CHARACTER SET utf8mb4 COLLATE u...BLE campaign_mes May 25 15:45:54 centos-mailtrain node[1686]: ERR! } May 25 15:45:54 centos-mailtrain systemd[1]: mailtrain.service failed.

this is the full SQL statement that fails to run ALTER TABLE blacklist CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE campaign_links CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE campaign_lists CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE campaign_messages CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE campaigns CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE channel_lists CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE channels CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE confirmations CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE custom_fields CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE custom_forms CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE custom_forms_data CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE file_cache CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE files_campaign_attachment CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE files_campaign_file CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE files_mosaico_template_block CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE files_mosaico_template_file CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE files_template_file CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE generated_role_names CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE import_failed CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE import_runs CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE imports CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE knex_migrations CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE knex_migrations_lock CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE links CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE lists CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE mosaico_templates CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE namespaces CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE permissions_campaign CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE permissions_channel CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE permissions_custom_form CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE permissions_list CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE permissions_mosaico_template CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE permissions_namespace CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE permissions_report CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE permissions_report_template CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE permissions_send_configuration CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE permissions_template CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE queued CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE report_templates CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE reports CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE rss CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE segments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE send_configurations CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE settings CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE shares_campaign CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE shares_channel CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE shares_custom_form CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE shares_list CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE shares_mosaico_template CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE shares_namespace CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE shares_report CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE shares_report_template CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE shares_send_configuration CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE shares_template CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE template_dep_campaigns CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE templates CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE test_messages CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE trigger_messages CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE triggers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE tzoffset CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

I've already tried to go through the database to fix the issue manually but its not just for one table, this is list of the tables I came across that have the same issue --> "Specified key was too long; max key length is 767 bytes"

campaigns channels confirmations custom_fields links lists rss settings

I've tried more than OS and diff VPS providers, just to make sure that its something in the app

talheim-it commented 8 months ago

We are going to start with the development and testing of mailtrain v3 in the next weeks.

You are welcome to help us with the testing as soon as the first release candidate is available.