UNSIGNED integers issue, AUTO_INCREMENT issue, int(1) issue, KEY issue

[hahnn]

Hello,

I tried to install this wonderful plugin to make a test instance of WordPress 6.4.1 (Apache HTTPD/PHP 8.1) to work against a PostgreSQL V14 database.

When trying to install several plugins, I've seen several problems regarding the SQl commands made on the database, there may be one or several issues, still, with one or several WordPress PostgreSQl SQL tables that cannot be created.

1. several SQL tables cannot be created because they contain columns with integers having the UNSIGNED keyword: PostgreSQL doesn't support UNSIGNED integers so this keyword should be simply erased when creating the SQL table
2. several SQL tables couldn't be created because they contained an int(1) column: int(1) should be probably converted to a smallint PostgreSQL type (I presume this int(1) is maybe to store a boolean value, but not sure)
3. a lot of cases of impossibility to create PostgreSQL tables because of the presence of the AUTO_INCREMENT keyword (so in upper case). I don't really know how the conversion work in the plugin because I've seen something in the code about auto_increment (but in lower case). So a suggestion might be to convert this auto increment as a smallserial/serial/bigserial whatever the case is, so using a PHP str_ireplace function instead of the str_replace one for example.
4. several cases of SQL tables that cannot be created because they contain KEY key_name () directives: this doesn't exist in PostgreSQL. The meaning of such KEY directives in MYSQL when creating tables is the creation of a synonym for an index on the columns specified: then a way to probably solve this kind of issue might be to simply erase those KEY directives from the CREATE TABLE statement, and probably create a standard index on the columns if it's not already existing.
5. and last, I've seen an error on a specific case of UPDATE SQL command that includes an ORDER BY clause: PostgreSQL doesn't support the ORDER BY clause in UPDATE SQL requests. In such case, there are solutions like rewriting those kind of UPDATE requests using two requests: one doing a SELECT ... ORDER BY ... FOR UPDATE, then after, the UPDATE.

The 4 first points are probably fast to implement/fix, the last one might be more complicated.

For my 2 cents ;)

KR. Nicolas

[mattbucci]

Can you give some examples of failing plugins which can be used as a test? Alternatively maybe you could give some test cases we could add to the automated test suite?

https://github.com/PostgreSQL-For-Wordpress/postgresql-for-wordpress/blob/v3/tests/rewriteTest.php

Thanks for contributing.

[hahnn]

To illustrate what I've written above, here are some examples taken from my PostgreSQL audit logs:

First example showing the UNSIGNED, AUTO_INCREMENT and int(1) issues all in the same table:

2023-11-26 00:53:21.355 CET [pid=2301495]: [sid=656288ef.231e37/tid=0] client=,user=,db=,app=[inconnu] ERREUR: erreur de syntaxe sur ou près de « UNSIGNED » au caractère 53 2023-11-26 00:53:21.355 CET [pid=2301495]: [sid=656288ef.231e37/tid=0] client=,user=,db=,app=[inconnu] INSTRUCTION : CREATE TABLE wp_itsec_lockouts ( lockout_id bigint UNSIGNED NOT NULL AUTO_INCREMENT, lockout_type varchar(25) NOT NULL, lockout_start timestamp NOT NULL, lockout_start_gmt timestamp NOT NULL, lockout_expire timestamp NOT NULL, lockout_expire_gmt timestamp NOT NULL, lockout_host varchar(40), lockout_user bigint UNSIGNED, lockout_username varchar(60), lockout_active int(1) NOT NULL DEFAULT 1, lockout_context TEXT, PRIMARY KEY (lockout_id) ) ; CREATE INDEX wp_itsec_lockouts_lockout_expire_gmt ON wp_itsec_lockouts (lockout_expire_gmt); CREATE INDEX wp_itsec_lockouts_lockout_host ON wp_itsec_lockouts (lockout_host); CREATE INDEX wp_itsec_lockouts_lockout_user ON wp_itsec_lockouts (lockout_user); CREATE INDEX wp_itsec_lockouts_lockout_username ON wp_itsec_lockouts (lockout_username); CREATE INDEX wp_itsec_lockouts_lockout_active ON wp_itsec_lockouts (lockout_active); 2023-11-26 00:53:21.362 CET [pid=2301495]: [sid=656288ef.231e37/tid=0] client=,user=,db=,app=[inconnu] ERREUR: erreur de syntaxe sur ou près de « UNSIGNED » au caractère 46 2023-11-26 00:53:21.362 CET [pid=2301495]: [sid=656288ef.231e37/tid=0] client=,user=,db=,app=[inconnu] INSTRUCTION : CREATE TABLE wp_itsec_temp ( temp_id bigint UNSIGNED NOT NULL AUTO_INCREMENT, temp_type varchar(25) NOT NULL, temp_date timestamp NOT NULL, temp_date_gmt timestamp NOT NULL, temp_host varchar(40), temp_user bigint UNSIGNED, temp_username varchar(60), PRIMARY KEY (temp_id) ) ; CREATE INDEX wp_itsec_temp_temp_date_gmt ON wp_itsec_temp (temp_date_gmt); CREATE INDEX wp_itsec_temp_temp_host ON wp_itsec_temp (temp_host); CREATE INDEX wp_itsec_temp_temp_user ON wp_itsec_temp (temp_user); CREATE INDEX wp_itsec_temp_temp_username ON wp_itsec_temp (temp_username);

Second example showing the KEY issues:

2023-11-26 00:53:21.431 CET [pid=2301495]: [sid=656288ef.231e37/tid=0] client=,user=,db=,app=[inconnu] ERREUR: erreur de syntaxe sur ou près de « AUTO_INCREMENT » au caractère 62 2023-11-26 00:53:21.431 CET [pid=2301495]: [sid=656288ef.231e37/tid=0] client=,user=,db=,app=[inconnu] INSTRUCTION : CREATE TABLE wp_itsec_dashboard_lockouts ( id int NOT NULL AUTO_INCREMENT, ip varchar(40), time timestamp NOT NULL, count int NOT NULL, PRIMARY KEY (id), UNIQUE KEY ip__time (ip, time) ) ; 2023-11-26 00:53:21.438 CET [pid=2301495]: [sid=656288ef.231e37/tid=0] client=,user=,db=,app=[inconnu] ERREUR: le type « resolution » n'existe pas au caractère 378 2023-11-26 00:53:21.438 CET [pid=2301495]: [sid=656288ef.231e37/tid=0] client=,user=,db=,app=[inconnu] INSTRUCTION : CREATE TABLE wp_itsec_vulnerabilities ( id varchar(128) NOT NULL, software_type varchar(20) NOT NULL, software_slug varchar(255) NOT NULL, first_seen timestamp NOT NULL, last_seen timestamp NOT NULL, resolved_at timestamp default NULL, resolved_by bigint NOT NULL default 0, resolution varchar(20) NOT NULL default '', details text NOT NULL, PRIMARY KEY (id), KEY resolution (resolution), KEY software_type (software_type), KEY last_seen (last_seen) ) ;

Third example with the UPDATE ... ORDER BY:

2023-11-26 01:12:25.130 CET [pid=2302887]: [sid=65628d67.2323a7/tid=0] client=,user=,db=,app=[inconnu] ERREUR: erreur de syntaxe sur ou près de « ORDER » au caractère 273 2023-11-26 01:12:25.130 CET [pid=2302887]: [sid=65628d67.2323a7/tid=0] client=,user=,db=,app=[inconnu] INSTRUCTION : UPDATE wp_posts SET post_password = '3d4d35d34d6e52ea9534', post_modified_gmt = '2023-11-26 00:12:25', post_modified = '2023-11-26 01:12:25' WHERE post_type = 'scheduled-action' AND post_status = 'pending' AND post_password = '' AND post_date_gmt <= '2023-11-26 00:12:25' ORDER BY menu_order ASC, post_date_gmt ASC, ID ASC

Last example with the WordPress SQL table wp_e_events that cannot be created:

2023-11-26 01:11:21.338 CET [pid=2302817]: [sid=65628d28.232361/tid=0] client=,user=,db=,app=[inconnu] ERREUR: erreur de syntaxe sur ou près de « auto_increment » au caractère 42 2023-11-26 01:11:21.338 CET [pid=2302817]: [sid=65628d28.232361/tid=0] client=,user=,db=,app=[inconnu] INSTRUCTION : CREATE TABLE wp_e_events ( id bigint auto_increment primary key, event_data text null, created_at timestamp not null ) ;;

[hahnn]

About the UNSIGNED issue, I think a specific conversion should be put in place regarding the used type:

• for SMALLINT UNSIGNED -> convert to INTEGER
• for INT UNSIGNED -> convert to BIGINT
• for BIGINT UNSIGNED -> keep it as BIGINT

[hahnn]

And about examples of failing plugins:

Relevanssi Solid Security Basique Yoast SEO Social Rocket Complianz - GDPR/CCPA Cookie Consent Broken Link Checker

[mattbucci]

bigint UNSIGNED AUTO_INCREMENT should probably be bigserial https://www.postgresql.org/docs/current/datatype-numeric.html

[hahnn]

bigint UNSIGNED AUTO_INCREMENT should probably be bigserial

That's right :)

[hahnn]

Ahhh... I forgot to speak about another issue:

In my Wordpress installation, my wp-config.php was configured like that for the database:

`// Réglages PGSQL - Votre hébergeur doit vous fournir ces informations. // /* Nom de la base de données de WordPress. / define('DB_NAME', 'mydatabasename');

/* Utilisateur de la base de données PGSQL. / define('DB_USER', 'myusername'); `

And here the issue is that when connecting to the PostgreSQL database, it wanted to connect on a database called myusername using the user myusername, instead of trying to connect to the database called mydatabasename with the user myusername.

So to get things working, I had to rename my database from mydatabasename to myusername.