• Resolved esszach

    (@esszach)


    Hello,

    I am unable to use Redirection due to a couple errors. I believe this started when I updated to WordPress 5.1.1, and updated Redirection to 4.2.3. I was prompted to up upgrade the Redirection database, which upgraded without issue. However, following this, I am unable to add new redirects, receiving the following error when I attempt to do so:

    Unable to add new redirect (41): Incorrect integer value: ” for column ‘regex’ at row 1

    Plugin: 4.2.3
    WordPress: 5.1.1 (multi)
    PHP: 7.2.17
    Browser: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:66.0) Gecko/20100101 Firefox/66.0
    JavaScript: https://www.website.com/subsite/wp-content/plugins/redirection/redirection.js
    REST API: https://www.website.com/subsite/wp-json/

    Error: Unable to add new redirect (41): Incorrect integer value: ” for column ‘regex’ at row 1
    Action: redirection/v1/redirect/ POST
    Params: {“id”:0,”url”:”/source”,”title”:””,”match_data”:{“source”:{“flag_regex”:false,”flag_trailing”:false,”flag_case”:false,”flag_query”:”exact”}},”match_type”:”url”,”action_type”:”url”,”position”:0,”group_id”:1,”action_code”:301,”action_data”:{“url”:”https://www.website.com/target”}}
    Code: 400 Bad Request
    Raw: {“code”:”redirect”,”message”:”Unable to add new redirect”,”data”:{“status”:400,”error_code”:41,”wpdb”:”Incorrect integer value: ” for column ‘regex’ at row 1″}}

    Accompanied by an error in the server log:

    PHP message: WordPress database error Incorrect integer value: ” for column ‘regex’ at row 1 for query INSERT INTO wp_5_redirection_items (regex, match_type, url, match_url, title, group_id, position, action_type, action_code, action_data, status) VALUES (”, ‘url’, ‘/source’, ‘/source’, ”, ‘1’, ‘3’, ‘url’, ‘301’, ‘https://www.website.com/target’, ‘enabled’) made by Red_Item::create” while reading response header from upstream, client: 130.111.28.84, server: http://www.website.com, request: “POST /subsite/wp-json/redirection/v1/redirect/?_wpnonce=3bb5ca08e7 HTTP/1.1”, upstream: “fastcgi://unix:/var/run/php-fpm/php-fpm.sock:”, host: “www.website.com”, referrer: “https://www.website.com/subsite/wp-admin/tools.php?page=redirection.php”

    If I try to uninstall and reinstall the plugin, it will prompt me to complete Redirection setup to finish installation. Trying to “Finish Setup” (setup makes it past initial options & REST API check) produces the following error:

    Database problem

    Failed to perform query “CREATE TABLE IF NOT EXISTS wp_redirection_items ( id int(11) unsigned NOT NULL AUTO_INCREMENT, url mediumtext NOT NULL, match_url varchar(2000) DEFAULT NULL, match_data text, regex int(11) unsigned NOT NULL DEFAULT ‘0’, position int(11) unsigned NOT NULL DEFAULT ‘0’, last_count int(10) unsigned NOT NULL DEFAULT ‘0’, last_access datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’, group_id int(11) NOT NULL DEFAULT ‘0’, status enum(‘enabled’,’disabled’) NOT NULL DEFAULT ‘enabled’, action_type varchar(20) NOT NULL, action_code int(11) unsigned NOT NULL, action_data mediumtext, match_type varchar(20) NOT NULL, title text, PRIMARY KEY (id), KEY url (url(191)), KEY status (status), KEY regex (regex), KEY group_idpos (group_id,position), KEY group (group_id), KEY match_url (match_url(191)) ) DEFAULT CHARACTER SET utf8mb4 COLLATE=utf8_general_ci”

    Message: Failed to perform query “CREATE TABLE IF NOT EXISTS wp_redirection_items (
    id int(11) unsigned NOT NULL AUTO_INCREMENT,
    url mediumtext NOT NULL,
    match_url varchar(2000) DEFAULT NULL,
    match_data text,
    regex int(11) unsigned NOT NULL DEFAULT ‘0’,
    position int(11) unsigned NOT NULL DEFAULT ‘0’,
    last_count int(10) unsigned NOT NULL DEFAULT ‘0’,
    last_access datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
    group_id int(11) NOT NULL DEFAULT ‘0’,
    status enum(‘enabled’,’disabled’) NOT NULL DEFAULT ‘enabled’,
    action_type varchar(20) NOT NULL,
    action_code int(11) unsigned NOT NULL,
    action_data mediumtext,
    match_type varchar(20) NOT NULL,
    title text,
    PRIMARY KEY (id),
    KEY url (url(191)),
    KEY status (status),
    KEY regex (regex),
    KEY group_idpos (group_id,position),
    KEY group (group_id),
    KEY match_url (match_url(191))
    ) DEFAULT CHARACTER SET utf8mb4 COLLATE=utf8_general_ci”
    Installed: –
    Next: 4.1
    Debug:

    COLLATION ‘utf8_general_ci’ is not valid for CHARACTER SET ‘utf8mb4’
    Table “wp_redirection_items” is missing
    Table “wp_redirection_groups” is missing
    Table “wp_redirection_logs” is missing
    Table “wp_redirection_404” is missing
    Stage: create_tables

    If anyone can offer some tips on solving this issue, it would be much appreciated.

    Thanks,
    Zach

Viewing 7 replies - 1 through 7 (of 7 total)
  • Plugin Author John Godley

    (@johnny5)

    The key message is this:

    COLLATION ‘utf8_general_ci’ is not valid for CHARACTER SET ‘utf8mb4’

    Your database settings in wp-config.php are incorrect. You can find more info about this here:

    https://codex.ww.wp.xz.cn/Editing_wp-config.php#Database_character_set

    • This reply was modified 7 years, 1 month ago by John Godley.
    Thread Starter esszach

    (@esszach)

    Hi John, thanks for the reply.

    Based on your reply, it sounds like I will need to convert my DB to the proper charset. Before I do so – I have another site running on a different server where Redirection is working properly. Their wp-config.php files have the same DB_CHARSET and DB_COLLATE settings, however one server is running MariaDB 5.5 and the other is running MariaDB 10.3. Do you know why I am not having this problem on the MariaDB 5.5 server? If Redirection is causing the Character Set problem, wouldn’t I have the issue on both servers?

    Much thanks,
    Zach

    Plugin Author John Godley

    (@johnny5)

    I don’t know why it would behave differently. Redirection is using the values you configure in your wp-config.php and passing them along to the database which is then returning the error. If you change the settings in wp-config.php (even if only temporarily) to something that won’t cause MariaDB to return an error then you should be able to proceed.

    Thread Starter esszach

    (@esszach)

    Hi John,

    Thank you for this information. I found where the DB_COLLATE was being changed and modified the file so that the proper value was being used. This fixed my issue upgrading the Redirection database, but has not fixed my issue adding new redirects. Interestingly, I am able to add redirects if I use the “Regex” option, but not otherwise.

    The error:
    Unable to add new redirect (41): Incorrect integer value: '' for column 'regex' at row 1

    Plugin: 4.2.3
    WordPress: 5.1.1 (multi)
    PHP: 7.2.17
    Browser: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/74.0.3729.131 Safari/537.36
    JavaScript: https://website.com/wp-content/plugins/redirection/redirection.js
    REST API: https://website.com/wp-json/
    
    Error: Unable to add new redirect (41): Incorrect integer value: '' for column 'regex' at row 1
    Action: redirection/v1/redirect/ POST
    Params: {"id":0,"url":"/testit","title":"","match_data":{"source":{"flag_regex":false,"flag_trailing":false,"flag_case":false,"flag_query":"exact"}},"match_type":"url","action_type":"url","position":0,"group_id":5,"action_code":301,"action_data":{"url":"http://www.google.com"}}
    Code: 400 Bad Request
    Raw: {"code":"redirect","message":"Unable to add new redirect","data":{"status":400,"error_code":41,"wpdb":"Incorrect integer value: '' for column 'regex' at row 1"}}

    I have tried completely removing Redirection, dropping all tables, re-installing it and disabling all other plugins, and it still will not work. Any additional assistance would be much appreciated.

    Thanks,
    Zach

    Thread Starter esszach

    (@esszach)

    This seems related –
    If I run the CREATE TABLE command in MySQL for the redirection_items table, the regex ‘regex’ is described by the create statement:

    regexint(11) unsigned NOT NULL DEFAULT 0,

    As this is not set up to allow null values, I’d image this is why I can only add redirects if I include the ‘Regex’ option. Is the create statement I provided correct for the current version of Redirection, or is my DB creating the table improperly?

    Thanks,
    Zach

    • This reply was modified 7 years, 1 month ago by esszach.
    Plugin Author John Godley

    (@johnny5)

    Your database is maybe configured in a different way than usual and it’s rejecting the auto-conversion. I’ve added something to handle this in 4.3, which you can test here:

    https://github.com/johngodley/redirection/releases/tag/4.3-beta-1

    Thread Starter esszach

    (@esszach)

    Thank you very much, John! This seems to have fixed it.

    Much appreciated,
    Zach

Viewing 7 replies - 1 through 7 (of 7 total)

The topic ‘Cannot activate Redirection’ is closed to new replies.