[#9759] - Fix inconsisteny between new install and update for NOT NULL of urls columns of redirect_links table with MySQL or Microsoft databases
- Fixed in Code Base
- 8 May 2016
- Medium
- Build: staging
- # 9759
- Diff
- richard67:correct-pr9269-redirect-links-1
- Success continuous-integration/travis-ci/pr The Travis CI build passed Details
- Success JTracker/HumanTestResults Human Test Results: 2 Successful 0 Failed. Details
User tests: Successful: Unsuccessful:
Pull Request for new issue.
Only relevant for MySQL or Microsoft SQL Server or SQL Azure databases, i.e. not relevant for PostgreSQL databases.
Summary of Changes
With PR #9269 for enlargement of URL colums in the "redirect_links" table, a "NOT NULL" constraint for the "new_url" column was accidently added in schema update scripts "3.5.2-2016-04-06.sql" for mysql and sqlazure, which does not exist in the create table statement for new installation.
This PR corrects these inconsistencies between update and new install by new schema update scripts for updating from a 3.5.0 or 3.5.1.
In addition, the old schema updates "3.5.2-2016-04-06.sql" are corrected, too, so pre-3.5.0 versions will not be updated in the wrong way.
@shur Thanks for reporting this issue to me.
Testing Instructions
Pre-requisites, important!!!
- This PR cannot be tested on a newly installed current staging, because there the database schema is of course consistent, so the issue does not appear.
- Due to the limited capabilities of the database schema manager aka "database fix", you cannot use patchtester and then do a database fix to apply this PR.
- For the same reason, you cannot use the "copy or unzip the files on the server and then run the database fix" method for updating to latest staging + this PR, you have to use the custom URL and Joomla! Update Component.
So as a starting point, use a pre-3.5.0, e.g. a 3.4.8 for this test. No need to test several older versions, a 3.4.8 is sufficient.
Finally, please mention in the comment which database type(s) you have tested when setting your test result.
Test Steps
Step 1: On a Joomla! pre-3.5.0, e.g. a 3.4.8, check e.g. with phpMyAdmin (or in case of Microsoft stuff, the Microsoft tool) that in your database, column "new_url" of the redirect_links table does not have a "NOT NULL" constraint, i.e. NULL values are allowed, and columns "old_url" and "referer" have a "NOT NULL" constraint, i.e. NULL values are not allowed.
Result: "NOT NULL" constraints are as described.
Step 2: Update to 3.5.0 or 3.5.1 (does not matter which one).
Step 3: Check e.g. e.g. with phpMyAdmin (MS tool) the "NOT NULL" constrains of columns "old_url", "new_url" and "referer" of the redirect_links table in your database.
Result: There is now a "NOT NULL" constraint for column "new_url". The other 2 columns have not changed.
Step 4: Check with a text editor or on command line (e.g. on Linux: less command) the "NOT NULL" constrains of the "new_url" column in the SQL statement where the table is created in file "installation/sql//joomla.sql", where is your database server type mysql, postgresql or sqlazure (= sqlsrv).
Result: According to the joomla.sql the "NOT NULL" constraint shouldn't have changed compared to Step 1.
Step 5: Update to current staging + this PR = a patched 3.6.0-dev, using following custom URL: http://test5.richard-fath.de/list_test7.xml.
Step 6: Check e.g.with phpMyAdmin (MS tool) the "NOT NULL" constrains of columns "old_url", "new_url" and "referer" of the redirect_links table in your database.
Result: The "NOT NULL" constrains of columns "old_url", "new_url" and "referer" of the redirect_links table are as described in Step 1.
Step 7: Check with a text editor or on command line (e.g. on Linux: less command) the "NOT NULL" constrains of these columns in the SQL statement where the table is created in file "installation/sql//joomla.sql"
Result: Also according to the joomla.sql the "NOT NULL" constraints shouldn't have changed compared to Step 1.
Votes
| Status | New | ⇒ | Pending |
| Labels |
Added:
?
|
||
| Category | ⇒ | MS SQL Postgresql Updating |
| Category | MS SQL Postgresql Updating | ⇒ | MS SQL SQL Updating |
@brianteeman Thanks for correcting the category.
Could you check on postgresql if there are some other inconsistencies for the redirect_links table between a 3.5.0 or 3.5.1 updated from a pre-3.5.0 and the installation/sql/postgresql/joomla.sql, and if so, report them here so I can add the correction to this PR here, too?
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9759.
Not going to have a chance to set that up for a few days
@brianteeman OK, no problem. Just let me know if you later in a few days find some other inconsistencies caused by my PR #9269 for postgresql.
An alternative PR to this one here could be to add this questionable not null constraint to the joomla.sql scripts for new installation, because it does not do any harm (com_redirectI inserts empty string and not null when creating records via UI or automatically) and for the referrer column which can be empty, too, there is also a not null constraint, but then there still might be inconsistency because those who did a new install of 3.5.0 or 3.5.1 and so did not have this constraint and later then updated with the "copy files and use db fix" method to a 3.5.2 or later where this PR is included, then again the change of the old 3.5.0-2016-03-01.sql schema update where the constraint was added will not be applied because of limited functionality of the schema manager.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9759.
| Title |
|
||||||
| Category | MS SQL Updating SQL | ⇒ | MS SQL Postgresql SQL Updating |
| Title |
|
||||||
Adapted title and description to latest changes (e.g. for 3.5.2 becoming 3.6.0 and for handling PostgreSQL, too).
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9759.
I have updated this PR to latest staging and also updated the patched 3.6.0-dev update container behind the custom URL testing.
@brianteeman @wilsonge @shur Do you think you can test this PR?
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9759.
@waader Because you helped me by testing my other PR, #9847 (for which I still hope @wilsonge will find time to test, too): Do you think you can help testing this one here, too?
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9759.
@brianteeman @wilsonge In principle it could be sufficient to test this PR also by code review by a maintainer, if the maintainer trusts me a bit.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9759.
P.S.: Testers please mention in the comment which database type(s) you have tested when setting your test result.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9759.
Apache-2.4-x64 + PHP-5.6-x64 + MySQL-5.6-x64
See what I get:
Joomla 3.4.8 fresh install:
Joomla 3.4.8 updated to 3.5.1:
this Joomla 3.5.1 updated to 3.6.0 (from your link - step 5):
Joomla 3.6.0 fresh install:
So, if we compare the results, we see the consistency of the default values for the field new_url, as for freshly installed joomla and for updated joomla. Despite the fact that only tested for MySQL, I look at the code and I think everything will be okay for the rest of sql.
I have tested this item 
successfully on 7c535bc
see my previous comment with test screenshots
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9759.
After having with Potsgresql (I have one meanwhile), I change the description for this database kind. See "Note for postgresql" at the end of the "Summary of Changes".
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9759.
Hmm, I just see there are SQL syntax errors for PostgreSQL, so I will come soon with a new commit. But this will be for PostgreSQL only and so not invalidate previous tests for other database kinds, so testers of those can just set their test result again after the upcioming commit.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9759.
This PR has received new commits.
CC: @shur
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9759.
@shur No need to test again. Just check the latest commit to see that it only contains changes for PostgreSQL, so your test with MySQL is still valid.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9759.
| Title |
|
||||||
| Title |
|
||||||
After testing with PostgreSQL and reading PostgreSQL manuals I am sure now that all was OK for this DB type, so I changed this PR back to handle MySQL and Microsoft DBs only.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9759.
P.S.: What I had done with this PR for PostgreSQL before the latest commit has even caused SQL syntax errors, so I changed it back with the latest commit.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9759.
I have tested this item successfully on 724dabc
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9759.
I have tested this item successfully on 724dabc
Tested with all 3 db´s. Thanks Richard!
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9759.
| Status | Pending | ⇒ | Ready to Commit |
RTC based on testing.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9759.
| Labels |
Added:
?
|
||
| Milestone |
Added: |
||
Thank you @richard67 and testers!
| Status | Ready to Commit | ⇒ | Fixed in Code Base |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2016-05-08 14:06:55 |
| Closed_By | ⇒ | Kubik-Rubik |
| Labels |
Removed:
?
|
||
Added testing instructions with link to custom URL for update test.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9759.