Database error after upgrade to 3.0

  • Resolved acidzero

    (@acidzero)


    4 years, 4 months ago

    Hello. After the upgrade the plugin asked me to update the table. I did that and nothing happened. No progress bar, nothing. I waited for 10 minutes.
    On my page it appeared:
    Erro no banco de dados do WordPress: [Table ‘saindo1_novosdm2.wp_gdrts_items_basic’ doesn’t exist]
    SELECT * FROM wp_gdrts_items_basic WHERE item_id = 8

    And then, in the Toolbox of the plugin, those information appeared:

    Database tables
    Erro no banco de dados do WordPress: [Specified key was too long; max key length is 1000 bytes]
    CREATE TABLE wp_gdrts_items_basic ( id bigint(20) unsigned NOT NULL auto_increment, item_id bigint(20) unsigned NOT NULL default ‘0’, method varchar(128) NOT NULL default ”, series varchar(128) NOT NULL default ”, latest datetime NOT NULL default ‘0000-00-00 00:00:00’ COMMENT ‘gmt’, rating int(11) NOT NULL default ‘0’ COMMENT ‘normalized value’, votes int(11) NOT NULL default ‘0’, sum int(10) unsigned NOT NULL default ‘0’, max tinyint(3) unsigned NOT NULL default ‘0’, PRIMARY KEY (id), UNIQUE KEY item_method_series (item_id,method,series), KEY item_id (item_id), KEY method (method), KEY series (series), KEY latest (latest), KEY rating (rating) ) default CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci

    Database Upgrade Notices
    Created table wp_gdrts_items_basic
    Database Tables Check
    Erro no banco de dados do WordPress: [Table ‘saindo1_novosdm2.wp_gdrts_items_basic’ doesn’t exist]
    SHOW FULL COLUMNS FROM wp_gdrts_items_basic

    [OK] – wp_gdrts_itemmeta
    [OK] – wp_gdrts_items
    [ERROR] – wp_gdrts_items_basic: Table missing.
    [OK] – wp_gdrts_logmeta
    [OK] – wp_gdrts_logs
    [OK] – wp_gdrts_cache

    ——-

    Strangely enough, I checked on my old database (before the upgrade) and there’s no “wp_gdrts_items_basic” either.

    The page I need help with: [log in to see the link]

Viewing 6 replies - 1 through 6 (of 6 total)
  • Plugin Author Milan Petrovic

    (@gdragon)

    4 years, 4 months ago

    The error says that the key is too long, and there are no long keys in that table. This table ‘wp_gdrts_items_basic’ is a new one, and without it, plugin can’t work. I am not sure what can cause that, but, it is possible that you have some configuration issue with the MySQL server, or you still run some old MySQL variant that has MyISAM engine as default. Anyway, here is the SQL for adding this table, try to run it directly on your server, or consult your hosting why it can’t be added, so they can check the MySQL configuration:

    CREATE TABLE wp_gdrts_items_basic (
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
item_id BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
method VARCHAR(64) NOT NULL DEFAULT '',
series VARCHAR(128) NOT NULL DEFAULT '',
latest DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'gmt',
rating INT(11) NOT NULL DEFAULT '0',
votes INT(11) NOT NULL DEFAULT '0',
sum INT(10) UNSIGNED NOT NULL DEFAULT '0',
max TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (id),
UNIQUE INDEX item_method_series (item_id, method, series),
INDEX item_id (item_id),
INDEX method (method),
INDEX series (series),
INDEX latest (latest),
INDEX rating (rating));
    Thread Starter acidzero

    (@acidzero)

    4 years, 4 months ago

    Thank your for the answer. I’ll try to find someone to create it to me.

    Here are my settings in phpmyadmin, to see if you can find why it happened:

    Database server
    Server: Localhost via UNIX socket
    Server type: Percona Server
    Server connection: SSL is not being used Documentation
    Server version: 5.6.41-84.1 – Percona Server (GPL), Release 84.1, Revision b308619
    Protocol version: 10
    Server charset: UTF-8 Unicode (utf8)

    Web server
    cpsrvd 11.78.0.49
    Database client version: libmysql – 5.1.73
    PHP extension: mysqliDocumentation curlDocumentation mbstringDocumentation
    PHP version: 7.2.7

    phpMyAdmin
    Version information: 4.8.3

    Thread Starter acidzero

    (@acidzero)

    4 years, 4 months ago

    I created the table. Then I tried again. No progress, so I went to recheck and saw this:

    Error in database WordPress: [Specified key was too long; max key length is 1000 bytes]
    ALTER TABLE wp_gdrts_items_basic CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

    it looks like I’ve found the problem: the charset utf8mb4 with varchar 128. It’s just too long for my table. I changed varchar to 64 and it accepted utf8mb4, but then I go to the plugin and it changes back to varchar(128) utf8_unicode_ci.

    • This reply was modified 4 years, 4 months ago by acidzero.
    • This reply was modified 4 years, 4 months ago by acidzero.
    Plugin Author Milan Petrovic

    (@gdragon)

    4 years, 4 months ago

    Thanks for the information. I will release a minor update very soon with the changed field lengths. It seems that in some cases, the key length goes over the limit.

    Milan

    Plugin Author Milan Petrovic

    (@gdragon)

    4 years, 4 months ago

    3.0.1 is released with updated column lengths.

    Thread Starter acidzero

    (@acidzero)

    4 years, 4 months ago

    Now it worked like a charm! Glad to have helped! Thank you!

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘Database error after upgrade to 3.0’ is closed to new replies.