Joomla! Issue Tracker | Joomla! CMS #19707 - Repair the update of database schema changes on postgreSQL

Fixed in Code Base
10 Aug 2018
Medium
Build: staging
# 19707
Diff
csthomas:db_fix_postgresql_button

Pending

User tests: Successful: Unsuccessful:

csthomas
16 Feb 2018

Pull Request for Issue # #14331

Summary of Changes

This PR is based on #17351, #18483 and is a replacement for #18483.

After PR, joomla recognises correctly:

SET NOT NULL;
SET DEFAULT '';
DROP DEFAULT; - does not work in #18483
DROP NOT NULL;
TYPE character varying(127) - new stuff - allow to resize column
... DROP DEFAULT, ... SET DEFAULT 0; - only test the last action after comma

Testing Instructions

[UPDATED]

Install joomla staging and go to backend to extension -> database.
Now you see an issue:

This issue should not happen because there is a fresh installation.

The column has a definition at

joomla-cms/installation/sql/postgresql/joomla.sql

Line 1868 in 73ca292

"data" text NOT NULL,

This means that the update query from

joomla-cms/administrator/components/com_admin/sql/updates/postgresql/3.1.0.sql

Line 3 in c8b2121

ALTER TABLE "#__updates" ALTER COLUMN "data" SET DEFAULT '';

is wrong and should be removed as I did in my PR.

After we click on the database fix, everything should be OK, but the query from:

joomla-cms/administrator/components/com_admin/sql/updates/postgresql/3.7.0-2017-03-03.sql

Line 3 in 73ca292

ALTER TABLE "#__updates" ALTER COLUMN "data" DROP DEFAULT;

is not visible by joomla (bug) and the query can not fix our database.

Now our database is different than the fresh from installation but joomla does not see any problems.

Apply this PR by com_patchtester
Go to extension -> database.
Now you see
After you click on the Fix button, the column data from #__updates will be repaired and will be equal to definition from

joomla-cms/installation/sql/postgresql/joomla.sql

Line 1868 in 73ca292

"data" text NOT NULL,
The next tests are to check if each query starts with ALTER TABLE ALTER COLUMN is visible by joomla. I prepared 7 queries:

ALTER TABLE "#__newsfeeds" ALTER COLUMN "modified_by" DROP NOT NULL;
ALTER TABLE "#__newsfeeds" ALTER COLUMN "modified_by" DROP DEFAULT;
ALTER TABLE "#__newsfeeds" ALTER COLUMN "modified_by" TYPE bigint USING "modified_by"::bigint;

ALTER TABLE "#__contact_details" ALTER COLUMN "con_position" SET DEFAULT '';
ALTER TABLE "#__contact_details" ALTER COLUMN "mobile" DROP DEFAULT,
                                 ALTER COLUMN "mobile" SET DEFAULT 'new value';
ALTER TABLE "#__contact_details" ALTER COLUMN "address" SET NOT NULL;
ALTER TABLE "#__contact_details" ALTER COLUMN "alias" TYPE character varying(10) USING substr("alias", 1, 10);

After you put it at the bottom of

joomla-cms/administrator/components/com_admin/sql/updates/postgresql/3.8.4-2018-01-16.sql

Lines 1 to 2 in 5bf0e5d

    
           DROP INDEX "#__user_keys_series_2"; 
        
           DROP INDEX "#__user_keys_series_3";

and refresh extension -> database page you should see a 7 issues:

This means that every query is recognised by joomla.

Then click on the Fix button.
Now everything is OK.

To revert all above changes, replace above code by:

ALTER TABLE "#__newsfeeds" ALTER COLUMN "modified_by" SET NOT NULL;
ALTER TABLE "#__newsfeeds" ALTER COLUMN "modified_by" SET DEFAULT 0;
ALTER TABLE "#__newsfeeds" ALTER COLUMN "modified_by" TYPE integer USING "modified_by"::integer;

ALTER TABLE "#__contact_details" ALTER COLUMN "con_position" DROP DEFAULT;
ALTER TABLE "#__contact_details" ALTER COLUMN "mobile" SET DEFAULT '';
ALTER TABLE "#__contact_details" ALTER COLUMN "address" DROP NOT NULL;
ALTER TABLE "#__contact_details" ALTER COLUMN "alias" TYPE character varying(255);

Then refresh extension -> database page we will see 7 issues:

this means that all queries have been recognised.
Now click on the Fix button. All s OK.

Remove all custom changes from administrator/components/com_admin/sql/updates/postgresql/3.8.4-2018-01-16.sql and refresh extension -> database. All is OK.

Expected result

All changes/issues are visible and can be fixed.

Actual result

Joomla does not recognise a few above queries or recognises them incorrectly.

csthomas - open - 16 Feb 2018

csthomas - change - 16 Feb 2018

Status

New

⇒

Pending

joomla-cms-bot - change - 16 Feb 2018

Category

⇒

Postgresql SQL Administration com_admin Libraries

alikon - comment - 17 Feb 2018

i remember that DROP DEFAULT; was fixed on #18483, but i've no problem on closing that in favour of this one as your are adding new features, and with the hope this one have more luck than my old one ?

trying to truncate a VARCHAR from 255 to 10 doesn't work
getting the ERROR: value too long for type character varying(10)

ALTER TABLE "#__contact_details" ALTER COLUMN "alias" TYPE character varying(10);

you should issue/manage something like this instead

ALTER TABLE "#__contact_details" ALTER COLUMN "alias" TYPE varchar(10)
USING substr("alias", 1, 10)

csthomas - change - 19 Feb 2018

Labels

Added: ?

csthomas - change - 19 Feb 2018

The description was changed

csthomas - edited - 19 Feb 2018

csthomas - comment - 19 Feb 2018

Thanks,

ALTER TABLE "#__contact_details" ALTER COLUMN "alias" TYPE varchar(10) USING substr("alias", 1, 10)

I fixed it and now you can use function with parameters after word USING

alikon - comment - 21 Feb 2018

after the first fix, still get database issues

csthomas - comment - 21 Feb 2018

after the first fix, still get database issues

I have not explained that before.
This is an expected behaviour (for above columns) because of earlier sql queries.

This was a reason I had to comment line in administrator/components/com_admin/sql/updates/postgresql/3.1.0.sql

because it is conflicted with query from
administrator/components/com_admin/sql/updates/postgresql/3.7.0-2017-03-03.sql

Example:
The first update file contains "... column X SET DEFAULT ''"
The second update file redefines column X and change default value to NULL.
This two changes has a conflict and joomla show first or second issue.

The result is, the problem won't be fixed because the database table won't match to the query from the first file or to the query from the second file.

In my example, which you tested, I redefined a few columns and then joomla show changed column that does not match to queries from previous update files.

If this is a problem I can find a better columns (which does not exists in update files before).

alikon - comment - 21 Feb 2018

i was ?
?

alikon - test_item - 21 Feb 2018 - Tested successfully

alikon - comment - 21 Feb 2018

I have tested this item ✅ successfully on 7001e67

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/19707.}

brianteeman - comment - 23 Feb 2018

I finally got pgsql installed on my windows machine

Not sure if my test is successful or not as I still have a few issues to fix after applying the patch

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/19707.}

csthomas - comment - 23 Feb 2018

Nice,

after you applied the PR, joomla starts to show you a database problems (old problems that have never been recognized before).

Before you start to use my above test examples you can click on fix button and all issues should disappear.
Today or tomorrow I will try to add easier text instructions.

csthomas - change - 26 Feb 2018

The description was changed

csthomas - edited - 26 Feb 2018

csthomas - comment - 26 Feb 2018

I updated the test instructions.

csthomas - comment - 1 Mar 2018

As PR #17351 for the mysql version has been merged it would be good to take a look at this version for postgreSQL.

csthomas - comment - 17 Apr 2018

Can we merge this after only one success test?

2df1ea4 17 May 2018

Repair the update of database schema changes on postgreSQL

8f5cc76 17 May 2018