A hashtag prefixing the default value leads to the default value being stripped

[schultz-it-solutions]

I am not sure where the actual issue sits, so please bear with me, if this is the wrong place to report it... (it very well might be my own misunderstanding of some basics)

Steps to reproduce the issue

clean install of Joomla CMS 3.4.8
update of Joomla CMS 3.5.0-beta3
update database to correct all "database schema errors".
attempt to install (thirdparty) component jDBexport (which I am responsible for).

Expected result

successful installation of the component

Actual result

During installation I get an SQL error

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use ...
The SQL query shows like the following in this message (I am omitting irelevant parts of it):

CREATE TABLE IF NOT EXISTS #__jdbexport_worksheets
( id int(11) NOT NULL AUTO_INCREMENT,
title varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
......
colheading_text_align_vert varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'top',
colheading_text_col varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ' colheading_bg_col varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ' data_text_font varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Arial',
...
ordering int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

.....
The interesting part comes in the colheading_text_col and colheading_bg_col columns (and a few other columns as well):
The default values (in the SQL install-script) for these columns are "hex color codes" like #000000 or #eeeeee.

** colheading_text_col varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '#eeeeee',
**

however the JOOMLA installation process seems to strip these default values (and the corresponding close-quote and comma) away from the SQL statement, resulting in an SQL error of course!

System information (as much as possible)

Additional comments

[brianteeman]

Just to confirm - this component installs perfectly in 3.4.8?

[schultz-it-solutions]

sorry, yes of course!

[brianteeman]

Just wanted to double check

[schultz-it-solutions]

Sure!
However, I changed the install SQL to support "utf8mb4" for this installation. I will verify that the previous "utf8" supporting installation indeed will install on Joomla 3.5.0-beta3 -

[schultz-it-solutions]

I do indeed get the very same error when trying to create the table without specific collation and charsets (as the component installation did successfully in the last 3,5 years for a few hundred users worldwide).
So from my point of view, this seems to be linked to 3.5.0 somehow...
The 3.5.0-beta3 testsite itself is running without issues (as far as I can tell)

[schultz-it-solutions]

submitting the respective SQL "create table" statement from the install-script directly into PHPMYADMIN on the same server, the table is created successfully, including the named default values!

[andrepereiradasilva]

could the problem be related to this change? #9137

just guessing, but since you use # ...

[schultz-it-solutions]

Indeed this sounds like either a very unlikely coincicdence, or actually more probable "the cause of the issue" (although I did not - yet - inspect the actual patch and what it does)...

[yvesh]

Can confirm this issue.. This is going to break a lot of extensions which are storing for example color values in the database. You can test it with the attached sample extension.

sampleerror.zip

[zero-24]

@wilsonge This is another 3.5 blocker i guess :(

[andrepereiradasilva]

yes but it doesn't seem related to utf-8 mb4

[zero-24]

Yes the Problem is this PR: #9137

Maybe we just revert or fix the regex

[wilsonge]

@marcodings can you have a look please?

[wilsonge]

Hopefully Marco can have a look and fix it - if not we will revert. I've added a 3.5 blocker label to the issue so we know it needs to be fixed before release!

[andrepereiradasilva]

can a mantainer change the title of this?

[wilsonge]

Done :)

[schultz-it-solutions]

Thank you wilsonge, sorry for the wrong title...
I can test a patch as soon as it is available...

By the way, it is not only 'default values' that are relevant, also 'comments' containing a hashtag (# is used as 'number' or 'No.' representation in english), or maybe even simple UPDATE or INSERT statements, where the value contains a hashtag...

[wilsonge]

OK I've tested a patch at work which I've applied directly to the CMS - can you test with the latest staging please?

[schultz-it-solutions]

I can positively confirm that the installation of my component (jDBexport) is successful now (after I updated the "driver.php" . I will have to do a full test (as described in "steps to reproduce") yet, but it looks like the issue is solved.
Thank you.

[wilsonge]

Awesome :) Thanks for the test :)

[wilsonge]

We've just added a tad more to improve the robustness slightly (thanks @marcodings ) - I can't imagine there being anymore problems - but if you have any time to check would be appreciated!

[schultz-it-solutions]

yes, will do - however I seem to have difficulties to make a clean install with the "download-from-staging" version:

Before I can open the second INSTALLATION page, I get:
ReferenceError: Install is not defined

This certainly does not have to do anything with the issue at hand, but I need first find out what is going wrong here... sorry

[schultz-it-solutions]

I have successfully tested this latest patch (though I am having the above mentioned issues with installing from "cms-staging" ) :
Steps performed:

1. download joomla-3.5.0.beta3.zip
2. exchange "libraries/joomla/database/driver.php" with that from "cms-staging"
3. clean-install Joomla
4. clean-install jDBexport : SUCCESSFUL
5. initiated a "INSERT INTO" for the respective database table: SUCCESSFUL
6. initiated a "UPDATE TABLE" for the respective database table record: SUCCESSFUL
7. executed the component's "create excel workbook" command, which itself does use these tables and it's color-fields: SUCCESSFUL (everything as expected)

[yvesh]

Patch is working.. Thanks @marcodings @wilsonge