[#14321] - Fix install languages and discover extension for mssql and small fixes for other databases
- Fixed in Code Base
- 19 Mar 2017
- Medium
- Build: staging
- # 14321
- Diff
- csthomas:sqlupdate_strict
User tests: Successful: Unsuccessful:
Pull Request for Issue #9457 and related to #11763 which is incomplete.
Summary of Changes
Mysql strict mode - install languages fix.
Postgresql - reinstall languages fix, prevent double installation in db.
Mssql - install and reinstall and discover extension fix.
Additional changes:
- column xreference across all databases has default value ''
- asset_id on mysql has to be unsigned.
Mysql column type TEXT can not have default value ''. It is ignored.
Source: https://dev.mysql.com/doc/refman/5.7/en/blob.html
BLOB and TEXT columns cannot have DEFAULT values.
So from others db I also removed default value for:
- data (#__updates)
- custom_data (#__extensions)
- system_data (#__extensions)
Testing Instructions
[MYSQL - strict mode]
- Install Joomla on mysql and enable strict mode, for ex change one line:
diff --git a/libraries/joomla/database/driver/mysqli.php b/libraries/joomla/database/driver/mysqli.php
index 37c9ae0f44..9c9e10206d 100644
--- a/libraries/joomla/database/driver/mysqli.php
+++ b/libraries/joomla/database/driver/mysqli.php
@@ -184,7 +184,8 @@ class JDatabaseDriverMysqli extends JDatabaseDriver
}
// Set sql_mode to non_strict mode
- mysqli_query($this->connection, "SET @@SESSION.sql_mode = '';");
+ mysqli_query($this->connection, "SET @@SESSION.sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';");- Try to install languages, it should failed
- apply patch
- Now should be OK.
[POSTGRESQL]
- Install joomla on postgresql.
- Install some language pack, reinstall it. before patch language will be installed twice.
- After patch it is installed only once
[SQLSRV]
- Install joomla on sqlsrv
- Try to install some language - it fails
- Try to discover some extension - it fails (you should have one not installed, but extracted in correct place)
- Apply patch
- Install language
- Try to discover extension
Expected result
All above things works
Documentation Changes Required
None
| Status | New | ⇒ | Pending |
joomla-cms-bot
- | Category | ⇒ | SQL Administration com_admin Postgresql MS SQL com_installer Installation Libraries |
| Labels |
Added:
?
|
||
Forgot to say:
My db version is 5.5.25
Which version of joomla do you use?
I suppose you use some older version where such errors has not been fixed yet.
Besides that, this errors are unrelated. This PR only fix stuff for install language queries/database.
I use staging clean install
Hmm, I have mysql 5.7 and I do not see any errors.
Please enable debug log in global configuration.
Change setting in System debug plugin:
Log Categories: database-error
(Logging Tab)
Log Almost Everything: Yes
Delete file if exist: administrator/logs/everything.php
Then try to generate above errors.
Next check above files. You should see some log errors.
Forgot to say that my database name is "strictmode"
#
#<?php die('Forbidden.'); ?>
#Date: 2017-03-04 10:46:15 UTC
#Software: Joomla Platform 13.1.0 Stable [ Curiosity ] 24-Apr-2013 00:00 GMT
#Fields: datetime priority clientip category message
2017-03-04T10:46:15+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.title' isn't in GROUP BY
2017-03-04T10:46:15+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.title' isn't in GROUP BY
2017-03-04T10:46:15+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.title' isn't in GROUP BY
2017-03-04T10:46:15+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.title' isn't in GROUP BY
2017-03-04T10:46:15+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.asset_id' isn't in GROUP BY
2017-03-04T10:46:15+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.asset_id' isn't in GROUP BY
2017-03-04T10:46:16+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.title' isn't in GROUP BY
2017-03-04T10:46:16+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.title' isn't in GROUP BY
2017-03-04T10:46:16+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.title' isn't in GROUP BY
2017-03-04T10:46:16+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.title' isn't in GROUP BY
2017-03-04T10:46:16+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.asset_id' isn't in GROUP BY
2017-03-04T10:46:16+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.asset_id' isn't in GROUP BY
2017-03-04T10:46:16+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.title' isn't in GROUP BY
2017-03-04T10:46:16+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.title' isn't in GROUP BY
2017-03-04T10:46:16+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.asset_id' isn't in GROUP BY
2017-03-04T10:46:16+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.title' isn't in GROUP BY
2017-03-04T10:46:16+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.title' isn't in GROUP BY
2017-03-04T10:46:16+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.asset_id' isn't in GROUP BY
2017-03-04T10:48:10+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.title' isn't in GROUP BY
2017-03-04T10:48:10+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.title' isn't in GROUP BY
2017-03-04T10:48:10+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.asset_id' isn't in GROUP BY
2017-03-04T10:48:10+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.title' isn't in GROUP BY
2017-03-04T10:48:10+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.title' isn't in GROUP BY
2017-03-04T10:48:10+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.asset_id' isn't in GROUP BY
2017-03-04T10:48:11+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.title' isn't in GROUP BY
2017-03-04T10:48:11+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.title' isn't in GROUP BY
2017-03-04T10:48:11+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.asset_id' isn't in GROUP BY
2017-03-04T10:48:12+00:00 INFO ::1 updater Loading information from update site #1 with name "Joomla! Core" and URL https://update.joomla.org/core/list.xml took 0.20 seconds
2017-03-04T10:48:12+00:00 INFO ::1 updater Loading information from update site #2 with name "Accredited Joomla! Translations" and URL https://update.joomla.org/language/translationlist_3.xml took 0.14 seconds
2017-03-04T10:48:13+00:00 INFO ::1 updater Loading information from update site #3 with name "Joomla! Update Component Update Site" and URL https://update.joomla.org/core/extensions/com_joomlaupdate.xml took 0.13 seconds
2017-03-04T10:48:14+00:00 INFO ::1 updater Loading information from update site #1 with name "Joomla! Core" and URL https://update.joomla.org/core/list.xml took 0.17 seconds
2017-03-04T10:48:50+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.name' isn't in GROUP BY
2017-03-04T10:48:50+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.name' isn't in GROUP BY
2017-03-04T10:48:50+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.name' isn't in GROUP BY
2017-03-04T10:48:50+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.name' isn't in GROUP BY
2017-03-04T10:48:50+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.asset_id' isn't in GROUP BY
2017-03-04T10:48:50+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.asset_id' isn't in GROUP BY
2017-03-04T10:48:51+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.name' isn't in GROUP BY
2017-03-04T10:48:51+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.name' isn't in GROUP BY
2017-03-04T10:48:51+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.asset_id' isn't in GROUP BY
2017-03-04T10:48:51+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.name' isn't in GROUP BY
2017-03-04T10:48:51+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.name' isn't in GROUP BY
2017-03-04T10:48:51+00:00 ERROR ::1 database-error Database query failed (error # 1055): 'strictmode.a.asset_id' isn't in GROUP BY
Changing the database name has no effect.
I think there are important differences between msql 5.5 and 5.7
Saw this on the net:
in MySQL 5.7 sql mode is set by default to ONLY_FULL_GROUP_BY
That was it!
If I take off ONLY_FULL_GROUP_BY from the code above, I do not have any more errors.
I.e. using
mysqli_query($this->connection, "SET @@SESSION.sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';");
Shall I therefore consider this PR as OK?
Yes. This PR is OK, your errors is unrelated.
Which sample data do you use on instalation?
None. I have set up the site as multilingual.
I have tested this item
OK here on mysql.
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/14321.
Works with postgresql, but couldn´t test with mssql as installation could not be finalised. After choosing the Install Sample Data and pressing the Install-Button the program returns to the Overview tab.
If you does not have latest version then samples data is broken. Try to install without samples.
Please edit 691 line in libraries/joomla/database/driver/sqlsrv.php
$this->errorMsg = $errorMsg;JFactory::getApplication()->enqueueMessage($this->errorMsg . $query);
and in installation/model/database.php line 1144
replace (in method setDatabaseCharset)
{
to
{return false;
Then I get: Incorrect syntax near the keyword 'SET' ALTER DATABASE [joomla37] CHARACTER SET 'utf8'
I know that error but it not stop installation. Normally it is hidden and installation go forward.
Did you change the second file?
The method looks like this:
public function setDatabaseCharset($db, $name)
{
// Run the create database query.
$db->setQuery($db->getAlterDbCharacterSet($name));
try
{
$db->execute();
}
catch (RuntimeException $e)
{
return false;
}
return false;
}
Yes and it generates error which not stop installation. I want to, you do not run this query. Type return false; on the top of method.
Ok, now I have
public function setDatabaseCharset($db, $name)
{
return false;
}
and the installation goes back to Tab Overview with no message.
If you not try with this zip then may be this help:
https://github.com/csthomas/joomla-cms/archive/sqlupdate_strict.zip
I do not know what going wrong.
Please check your php error_log. Or ajax call results on installation, ex firebug, tab net or connections.
IIRC some days I had some php warning or notice which was displayed. It broke ajax results and installation stop.
may be turn off display errors may temporary help.
PHP Warning: simplexml_load_file() [function.simplexml-load-file]: I/O warning : failed to load external entity "file:///C:/Program%20Files%20(x86)/Ampps/www/joomla37/plugins/fields/gallery/gallery.xml" in C:\Program Files (x86)\Ampps\www\joomla37\libraries\cms\installer\installer.php on line 1997, referer: http://localhost/joomla37/installation/index.php?view=site
When I delete the respective line in joomla.sql installation works. So next I tried to install another language, but I get this error message:
Fatal error: Using $this when not in object context in C:\Program Files (x86)\Ampps\www\joomla37\administrator\components\com_installer\models\languages.php on line 151
This is php 5.3 only. We can not use $this in closure. Before closure create a $ins = $this; and replace $this to $ins inner closure. Ow also add use ($ins)
$ins = $this;
// Sort the array by value of subarray
usort(
$languages,
function($a, $b) use ($ins)
{
$ordering = $ins->getState('list.ordering');
if (strtolower($ins->getState('list.direction')) === 'asc')
{
return StringHelper::strcmp($a->$ordering, $b->$ordering);
}
else
{
return StringHelper::strcmp($b->$ordering, $a->$ordering);
}
}
);
That works!
OK, I'm adding that changes to PR.
The gallery plugin should also be removed from the mysql and postgres installation files.
But the are still included in your patch.
Yes because you used Patch tester which replace all joomla.sql file instead add only changes.
If you download https://github.com/csthomas/joomla-cms/archive/sqlupdate_strict.zip then everything should be ok (this is a version of joomla where gallery has not been deleted yet)
I have rebased PR. Now it will work with Patch Tester too without mentioned problem.
I have tested this item
I did a retest with all 3 databases and using php 5.3 and 5.6.
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/14321.
@infograf768 can you please test again so we have 2 successfully tests?
I have tested this item
ok on mysqli
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/14321.
RTC as there are 2 successfully Tests?
wilsonge
- | Status | Pending | ⇒ | Fixed in Code Base |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2017-03-19 10:56:06 |
| Closed_By | ⇒ | wilsonge |
Tested on mysqli, using the
JDatabaseDriverMysqlimodification posted above.Before patch, when installing Joomla, I can't install languages.
I get
Field 'data' doesn't have a default valueAfter patch, I indeed can install languages but I get in CPanel 2 errors
I can install languages but I get in CPanel
and, if I go to administrator/index.php?option=com_content
I get an error:
Error also for any manager