MSSQL fix sql syntax in administrator/components/com_admin/sql/updates/sqlazure by csthomas · Pull Request #14102 · joomla/joomla-cms

csthomas · 2017-02-16T10:08:18Z

Summary of Changes

Update sql files for mssql database has invalid syntax.
This PR fix files from 3.5.0 and one older to correct syntax.

Testing Instructions

Install Joomla 3.5.0 on sqlsrv (mssql) database in folder joomla
Download and extract Joomla from https://github.com/csthomas/joomla-cms/archive/msfixupdate.zip to folder joomla37
Move joomla to joomla_old
Move joomla37 to joomla
Copy joomla_old/configuration.php to joomla/configuration.php
Download postupdate.php file from https://gist.github.com/mbabker/d7bfb4e1e2fbc6b7815a733607f89281#file-postupdate-php and save it in joomla/administrator/ folder.
[optional] Add a few changes as on below diff file to debugging if you like:

diff --git a/libraries/joomla/database/driver/sqlsrv.php b/libraries/joomla/database/driver/sqlsrv.php
index a0b4285cfb..d2a2097a3d 100644
--- a/libraries/joomla/database/driver/sqlsrv.php
+++ b/libraries/joomla/database/driver/sqlsrv.php
@@ -630,7 +630,7 @@ class JDatabaseDriverSqlsrv extends JDatabaseDriver
                {
                        $array = array();
                }
-
+echo "$query\n\n";
                // Execute the query. Error suppression is used here to prevent warnings/notices that the connection has been lost.
                $this->cursor = @sqlsrv_query($this->connection, $query, array(), $array);
 
@@ -685,7 +685,7 @@ class JDatabaseDriverSqlsrv extends JDatabaseDriver
                        {
                                // Get the error number and message from before we tried to reconnect.
                                $this->errorNum = $errorNum;
-                               $this->errorMsg = $errorMsg;
+                               $this->errorMsg = $errorMsg;echo "\n\n $errorMsg\n\n";
 
                                // Throw the normal query exception.
                                JLog::add(JText::sprintf('JLIB_DATABASE_QUERY_FAILED', $this->errorNum, $this->errorMsg), JLog::ERROR, 'database-error');

run php -f administrator/postupdate.php
From time to time if you apply diff file to see debug info you may see:

DELETE 
FROM [j37_session]
WHERE [time] < N'1487235471'

Error displaying the error page: Application Instantiation Error: Failed to start the session because headers have already been sent by ".../libraries/joomla/database/driver/sqlsrv.php" at line 633.

Then try again run php file, after a few times it won't failed:)

At the end of logs you should see:

Update to 3.7.0-beta3 completed successfully.

or if this PR is still not perfect then (TEST FAILED):

Failed to finalize the upgrade, please check the logs for additional details.

Alternative way to test for git users:

git clone ...
git checkout -f 3.5.0
rm configuration.php # if exists
# Install Joomla 3.5.0 on sqlsrv
git checkout -f staging
curl https://patch-diff.githubusercontent.com/raw/joomla/joomla-cms/pull/14102.diff | git apply
# Apply above diff file
php -f administrator/postupdate.php
...

I have tested only from Joomla 3.5.0 because I have not php 5.6 or less.

Expected result

Upgrade from Joomla 3.5.0 to 3.7 will go without errors.

Actual result

Upgrade fails.

Documentation Changes Required

None

alikon · 2017-02-18T07:53:15Z

i've followed first test info
upgraded from 3.6.5 to Joomla! 3.7.0-beta3 dev [ Amani ] 8-February-2017 14:11 GMT
on Microsoft SQL Server 12.00.2000
successfully

but after visiting home page got a 404 Component not found.
and on backend

alikon · 2017-02-18T08:28:48Z

I have tested this item ✅ successfully on 2ec04fa

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

cause previous mentioned isssues are present on a fresh install of current staging too

csthomas · 2017-02-18T09:48:18Z

Backend error become from $query->group() which I fixed at #13262.

The 404 error is unrelated, but I do not see it on linux.
I get only error message from joomla generated by some plugin (default enabled).
I have to turn on windows on virtual machine:)

csthomas · 2017-02-18T20:25:13Z

The 404 error also appears on staging joomla installation on mssql. So it is not related.

waader · 2017-02-18T21:05:13Z

I tried it with Joomla 3.4.8 and php 5.3.29. postupdate.php gives some warnings on each invocation. After logging into backend I get
Error loading component: com_fields, Component not found.
and Manage > Database gives this list, that is not fixable:
Database schema version (None) does not match CMS version (3.7.0-2017-02-02).
Database update version (3.7.0-beta2) does not match CMS version (3.7.0-beta3).
Table N'epik2_fields' does not exist. (From file 3.7.0-2016-08-29.sql.)
Table N'epik2_fields_categories' does not exist. (From file 3.7.0-2016-08-29.sql.)
Table N'epik2_fields_groups' does not exist. (From file 3.7.0-2016-08-29.sql.)
Table N'epik2_fields_values' does not exist. (From file 3.7.0-2016-08-29.sql.)

csthomas · 2017-02-18T21:24:08Z

@waader I have fixed one another file from 3.5.0. your upgrade stop on this file, there is a reason of above problems.

csthomas · 2017-02-18T23:49:54Z

I have merged branch 'staging' into 'msfixupdate' in order to add #14133 changes.

csthomas · 2017-02-19T09:41:17Z

Now you can upgrade from joomla >= 3.4.0 but there is still a few bugs in updates files IIRC 3.03, 3.2.x and 3.4.0

waader · 2017-02-19T09:46:58Z

Upgrade from Joomla 3.4.8 works now.

csthomas · 2017-02-23T07:57:19Z

I have to rebase this patch, there is not changes. Now should be easier to test by Patch Tester.

waader · 2017-02-24T19:23:23Z

I have tested this item ✅ successfully on d441d0d

Could it be that mssql didn´t work for joomla3.0.2 at all. I tried to install this version but couldn´t reach the second tab (database). As upgrade from 3.4.8 works I mark this as successfull.

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

csthomas · 2017-02-25T15:13:33Z

When I choose no sample then it works better but you should not use Database -> Fix button on that PR.
After you click on Fix button then probably not all queries will be performed.

I do it in different way on windows. In general it is described at the Test Instruction above.
But for wamp installation my last command looks like:

C:\wamp\www\joomla370>..\..\bin\php\php5.6.25\php.exe -c ..\..\bin\php\php5.6.25
\phpForApache.ini -f administrator\postupdate.php
Update to 3.7.0-beta3 completed successfully.

csthomas · 2017-03-02T22:58:09Z

Can someone with privilege can merge this PR. This is for mssql only with last success test.
There are only sql updates files. Last php file is already merged.

It is blocking me from creating a next PR.

ghost · 2017-03-03T08:30:24Z

pinging @infograf768

infograf768 · 2017-03-03T08:33:07Z

RTC, as it is unlikely that we will get more MSSQL testers.

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

ghost · 2017-03-03T08:35:06Z

thanks @infograf768

joomla-cms-bot added the PR-staging label Feb 16, 2017

csthomas changed the title ~~MSSQL update files - fix query syntax~~ MSSQL fix sql syntax in administrator/components/com_admin/sql/updates/sqlazure Feb 16, 2017

zero-24 added the Release Blocker label Feb 17, 2017

zero-24 added this to the Joomla 3.7.0 milestone Feb 17, 2017

csthomas mentioned this pull request Feb 17, 2017

MSSQL Replace datetime to datetime2(0) type for all joomla columns. #14123

Merged

csthomas mentioned this pull request Feb 18, 2017

Mssql: Load class before call sqlsrv_fetch_object on windows platform #14137

Merged

MSSQL update files - fix query syntax

d441d0d

csthomas force-pushed the msfixupdate branch from a913866 to d441d0d Compare February 23, 2017 07:55

This was referenced Feb 23, 2017

Advanced MSSQL query workaround for limit, offset and for sql with aggregate functions #13262

Merged

Normalize finder tables across 3 db systems and add default values #13517

Closed

joomla-cms-bot removed this from the Joomla 3.7.0 milestone Mar 3, 2017

joomla-cms-bot added the RTC This Pull Request is Ready To Commit label Mar 3, 2017

zero-24 added this to the Joomla 3.7.0 milestone Mar 3, 2017

rdeutz merged commit 9ea3444 into joomla:staging Mar 3, 2017

joomla-cms-bot removed the RTC This Pull Request is Ready To Commit label Mar 3, 2017

csthomas deleted the msfixupdate branch March 3, 2017 15:07

zero-24 removed the Release Blocker label Mar 3, 2017

jjaracz pushed a commit to jjaracz/joomla-cms that referenced this pull request Mar 9, 2017

MSSQL update files - fix query syntax (joomla#14102)

92aae96

Uh oh!

Conversation

csthomas commented Feb 16, 2017 • edited by joomla-cms-bot Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Summary of Changes

Testing Instructions

Expected result

Actual result

Documentation Changes Required

Uh oh!

alikon commented Feb 18, 2017 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

alikon commented Feb 18, 2017 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

csthomas commented Feb 18, 2017

Uh oh!

csthomas commented Feb 18, 2017

Uh oh!

waader commented Feb 18, 2017

Uh oh!

csthomas commented Feb 18, 2017

Uh oh!

csthomas commented Feb 18, 2017

Uh oh!

csthomas commented Feb 19, 2017

Uh oh!

waader commented Feb 19, 2017

Uh oh!

csthomas commented Feb 23, 2017 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

waader commented Feb 24, 2017

Uh oh!

csthomas commented Feb 25, 2017 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

csthomas commented Mar 2, 2017

Uh oh!

ghost commented Mar 3, 2017

Uh oh!

infograf768 commented Mar 3, 2017

Uh oh!

ghost commented Mar 3, 2017

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

7 participants

csthomas commented Feb 16, 2017 •

edited by joomla-cms-bot

Loading

alikon commented Feb 18, 2017 •

edited

Loading

alikon commented Feb 18, 2017 •

edited

Loading

csthomas commented Feb 23, 2017 •

edited

Loading

csthomas commented Feb 25, 2017 •

edited

Loading