Error while activating plugin wp-statistics

[0xFEEDC0DE64]

WP Version: 6.5 PG4WP Version: latest master

---------------------
[1712768022.327] Error running :

                    CREATE TABLE wp_statistics_useronline (
                        ID bigint(20) NOT NULL AUTO_INCREMENT,
                        ip varchar(60) NOT NULL,
                        created int(11),
                        timestamp int(10) NOT NULL,
                        date datetime NOT NULL,
                        referred text CHARACTER SET utf8 NOT NULL,
                        agent varchar(255) NOT NULL,
                        platform varchar(255),
                        version varchar(255),
                        location varchar(10),
                        `user_id` BIGINT(48) NOT NULL,
                        `page_id` BIGINT(48) NOT NULL,
                        `type` VARCHAR(100) NOT NULL,
                        PRIMARY KEY  (ID)
                    ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci
---- converted to ----
CREATE TABLE IF NOT EXISTS wp_statistics_useronline (
                     "ID" bigserial,
                        ip varchar(60) NOT NULL,
                        created int,
                        "timestamp" int NOT NULL,
                        "date" timestamp NOT NULL,
                        referred text NOT NULL,
                        agent varchar(255) NOT NULL,
                        platform varchar(255),
                        version varchar(255),
                        location varchar(10),
                        user_id BIGINT(48) NOT NULL,
                        page_id BIGINT(48) NOT NULL,
                        type VARCHAR(100) NOT NULL,
                        PRIMARY KEY  ( "ID" )
                    );
----> FEHLER:  Syntaxfehler bei »(«
LINE 12:       user_id BIGINT(48) NOT NULL,
                             ^
---------------------

[0xFEEDC0DE64]

i already found a fix for me, i made the bigint detection not upper/lower case dependent

[0xFEEDC0DE64]

the plugin still gets some errors:

---------------------
[1712768331.6169] Error running :
SELECT SUBSTRING_INDEX(REPLACE( REPLACE( referred, 'http://', '') , 'https://' , '') , '/', 1 ) as `domain`, count(referred) as `number` FROM wp_statistics_visitor WHERE `referred` REGEXP "^(https?://|www\.)[\.A-Za-z0-9\-]+\.[a-zA-Z]{2,4}" AND referred <> '' AND LENGTH(referred) >=12 AND `last_counter` BETWEEN '2024-03-11' AND '2024-04-10'  AND `referred` NOT LIKE 'http://example.com%'  AND `referred` NOT LIKE 'http://www.example.com%'  AND `referred` NOT LIKE 'https://example.com%'  AND `referred` NOT LIKE 'https://www.example.com%'  AND `referred` NOT LIKE 'ftp://example.com%'  AND `referred` NOT LIKE 'ftp://www.example.com%'  GROUP BY domain  ORDER BY `number` DESC LIMIT 10
---- converted to ----
SELECT SUBSTRING_INDEX(REPLACE( REPLACE( referred, 'http://', '') , 'https://' , '') , '/', 1 ) as domain, count(referred) as number, domain  ORDER BY number FROM wp_statistics_visitor WHERE referred REGEXP "^(https?://|www\.)[\.A-Za-z0-9\-]+\.[a-zA-Z]{2,4}" AND referred <> '' AND LENGTH(referred) >=12 AND last_counter BETWEEN '2024-03-11' AND '2024-04-10'  AND referred NOT ILIKE 'http://example.com%'  AND referred NOT ILIKE 'http://www.example.com%'  AND referred NOT ILIKE 'https://example.com%'  AND referred NOT ILIKE 'https://www.example.com%'  AND referred NOT ILIKE 'ftp://example.com%'  AND referred NOT ILIKE 'ftp://www.example.com%' GROUP BY SUBSTRING_INDEX(REPLACE( REPLACE( referred, 'http://', '') , 'https://' , '') , '/', 1 ) as domain, domain  ORDER BY number
----> FEHLER:  Syntaxfehler bei »FROM«
LINE 1: ...ount(referred) as number, domain  ORDER BY number FROM wp_st...
                                                             ^
---------------------

[0xFEEDC0DE64]

I tried to format it a bit more readable to find out what is going wrong. the conversion makes absolutely no sense to me:

original mysql:

SELECT
    SUBSTRING_INDEX(REPLACE(REPLACE(referred, 'http://', ''), 'https://', ''), '/', 1) as `domain`,
    count(referred) as `number`
FROM
    wp_statistics_visitor
WHERE
    `referred` REGEXP "^(https?://|www\.)[\.A-Za-z0-9\-]+\.[a-zA-Z]{2,4}" AND
    referred <> '' AND
    LENGTH(referred) >=12 AND
    `last_counter` BETWEEN '2024-03-11' AND '2024-04-10' AND
    `referred` NOT LIKE 'http://example.com%' AND
    `referred` NOT LIKE 'http://www.example.com%' AND
    `referred` NOT LIKE 'https://example.com%' AND
    `referred` NOT LIKE 'https://www.example.com%' AND
    `referred` NOT LIKE 'ftp://example.com%' AND
    `referred` NOT LIKE 'ftp://www.example.com%' 
GROUP BY
    domain
ORDER BY
    `number` DESC
LIMIT
    10;

now the postgres converted sql:

SELECT
    SUBSTRING_INDEX(REPLACE(REPLACE(referred, 'http://', ''), 'https://', ''), '/', 1) as domain,
    count(referred) as number,
    domain
ORDER BY
    number
FROM
    wp_statistics_visitor
WHERE
    referred REGEXP "^(https?://|www\.)[\.A-Za-z0-9\-]+\.[a-zA-Z]{2,4}" AND
    referred <> '' AND
    LENGTH(referred) >=12 AND
    last_counter BETWEEN '2024-03-11' AND '2024-04-10' AND
    referred NOT ILIKE 'http://example.com%' AND
    referred NOT ILIKE 'http://www.example.com%' AND
    referred NOT ILIKE 'https://example.com%' AND
    referred NOT ILIKE 'https://www.example.com%' AND
    referred NOT ILIKE 'ftp://example.com%' AND
    referred NOT ILIKE 'ftp://www.example.com%'
GROUP BY
    SUBSTRING_INDEX(REPLACE(REPLACE(referred, 'http://', ''), 'https://', ''), '/', 1) as domain, domain
ORDER BY
    number

first of all, why does it select "domain" twice? the original sql query only had 2 colums

then why is the FROM after ORDER BY?

then why does the GROUP BY not use "domain" but copy&paste the whole expression again?

and then lastly, where did the DESC go in ORDER BY and where did the LIMIT go?

[mattbucci]

That's a heck of a query, but looks like an issue with the group by rewriting in the select rewriter.

We'll need to add this to the test suite

[mattbucci]

There's a good explanation of why this is happening here.

https://github.com/PostgreSQL-For-Wordpress/postgresql-for-wordpress/blob/c7e3fbd50bb5363c84b39a611f65b9c699c7f63f/pg4wp/rewriters/SelectSQLRewriter.php#L251

This method probably needs to be updated as it doesn't detect this case properly.

Additionally where DESC LIMIT 10;

Went is a mystery for the moment

[0xFEEDC0DE64]

another failed query from the same plugin:

---------------------
[1712770425.047] Error running :
INSERT INTO `wp_statistics_visit` (last_visit, last_counter, visit) VALUES ( '2024-04-10 19:33:45', '2024-04-10', 1) ON DUPLICATE KEY UPDATE visit = visit + 1
---- converted to ----
INSERT INTO "wp_statistics_visit" (last_visit, last_counter, visit) VALUES ( '2024-04-10 19:33:45', '2024-04-10', 1) ON CONFLICT (visit) DO UPDATE SET visit = EXCLUDED.visit RETURNING *
----> FEHLER:  es gibt keinen Unique-Constraint oder Exclusion-Constraint, der auf die ON-CONFLICT-Angabe passt
---------------------

I try to translate my german error message: ERROR: there is no unique constraint or exclusion constraint, which matches the ON CONFLICT definition

I will check if the table creation was missing a unique or so....

[mattbucci]

While yes it would be expected that "visit" needs to be a key which supports conflicts, in your example the plugin incorrectly rewrote the duplicate value. It's missing the + 1

[mattbucci]

This plugin is tricky, I started capturing example failing tests here: https://github.com/PostgreSQL-For-Wordpress/postgresql-for-wordpress/pull/136