3.2 Beta 1 Install with MySQL warns Silverstripe cannot alter tables

[silverstripe]

3.2 Beta 1 Install with MySQL warns Silverstripe cannot alter tables

[dhensby]

Can I get a bit more info about your environment, please?

What OS are you using and how have you installed this software.

I've just tested on CentOS 7 x64, with default packages for PHP (5.4.16) and MariaDB (5.5.41) and I didn't get any problems, though I'm using root DB user

[dhensby]

PS: Even when I do it with a new user it works:

CREATE USER 'dan'@'localhost' IDENTIFIED BY '***';
CREATE DATABASE `ss_temp`;
GRANT ALL PRIVILEGES ON `ss_temp`.* TO 'dan'@'localhost';

[rogyw]

Host: HostGator Linux, Apache PHP 5.4.38
mysqli Client API library version 5.5.42-37.1 Client API header version 5.5.38-35.2
pdo_mysql Client API version 5.5.42-37.1

1. Created a new subdomain
2. Uploaded the SilverStripe-cms-v3.2.0-beta1.tar.gz and decompress.
3. visit subdomain and on install.php page - selected MySQL 5.0+ (using MySQLi) with new blank database and new user.
4. click check/re-check requirements - results in Warning Can I update tables.
5. checked with MySQL 5.0+ (using PDO) with same warning result.
6. Switched to PHP5.5.22 in CPanel - with same warning result

[micmania1]

The easiest way to debug this is to try and connect to the MySQL server from your host with your user and attempt to update a table in the database.

Do this completely outside of SilverStripe and if you can't update the table then SilverStripe is working correctly. Otherwise, there may be an issue with the installer and further investigation will be needed.

[dhensby]

@micmania1, @turtleline is saying that the dev/build works despite the warning. So it appears that he does have perms.

Actually, @turtleline, maybe you just have create perm. Can you add a db field to page and ew build after and see if that gets added?

Thanks

[rogyw]

@micmania1 Thanks. Did a separate php test of database connection and confirmed Alter table is working ok.

@dhensby Thanks. After some debugging I suspect the warning may display when the database name includes an underscore character. Some hosts (like Hostgator) prefix username_ before the db name selected by user.
checkDatabasePermissionGrant() is returning 0.
Example debugging output is:

$database= site_sstest1
$dbPattern= (("site_sstest1"\.\*)|("%"\.\*)|(\*\.\*))
$expression= /GRANT[ ,\w]+((ALL PRIVILEGES)|(ALTER(?! ((VIEW)|(ROUTINE)))))[ ,\w]+ON (("site_sstest1"\.\*)|("%"\.\*)|(\*\.\*))/i
$grant= GRANT ALL PRIVILEGES ON "site\_sstest1".* TO 'site_sstest2'@'localhost'

Please can you check if the warning displays when you add an underscore to the database name?

[rogyw]

print_r($grants) =

Array
(
    [0] => GRANT USAGE ON *.* TO 'site_sstest2'@'localhost' IDENTIFIED BY PASSWORD '************'
    [1] => GRANT ALL PRIVILEGES ON "site\_sstest1".* TO 'site_sstest2'@'localhost'
)

Have had a chance to confirm adding underscore to database name on local Windows XAMPP displays the same warning. Removing the underscore in database name results in "All Requirements Pass"

XAMPP = xampp-win32-5.5.24-0-VC11-installer.exe Includes: Apache 2.4.12, MySQL 5.6.24, PHP 5.5.24

[rogyw]

related reference: http://dev.mysql.com/doc/refman/5.5/en/grant.html

The “” and “%” wildcards are permitted when specifying database names in GRANT statements that grant privileges at the global or database levels. This means, for example, that if you want to use a “” character as part of a database name, you should specify it as “_” in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern; for example, GRANT ... ON foo\_bar.* TO ....

[dhensby]

Nice investigating. You reckon you can submit a PR to fix it? :)

[dhensby]

bump @turtleline ?

[feingestalten]

Bug with underscores warning still exist in 3.2.1 installer.
Confirmed on Ubuntu 14.04, PHP 5.5.9, MySQL

[fabiograz]

Hi,
Bug still exists in version 3.3.1. PHP version 5.6 mysql 5.6. In many hosting service underscores are part of database name.
Thanks

[tractorcow]

The problem is in the regexp for detecting permissions... someone needs to update it to work with @rogyw 's permissions as he posted at #96 (comment)

[rogyw]

@dhensby Sorry for lack of reply.

@fabiograz Is your MySQL version > 5.6.12?

Since reporting this I have replaced my local systems hard drive, updated O/S and software. I have just tried to recreate the issue and have not managed to see it with SS 3.3.1 from zip; XAMPP PHP 5.5.28 MySQL 5.6.12.

I'm reluctant to submit a PR without first finding out about the implementation in MariaDB - Didn't see the same warning in its docs

https://mariadb.com/kb/en/mariadb/grant/ seems to specify priv_level as db_name.* in syntax. Can't see mention of escaping or allowing % and _

[fabiograz]

My Mysql version is: 5.6.30

[markoliverbrawn]

Just to get me running: Adding:

$database = str_replace('_','\_', $database);

Above:

// Escape all valid database patterns (permission must exist on all tables)

Sorted it for me.

[tractorcow]

Fixed with silverstripe/silverstripe-framework#5855

[markoliverbrawn]

Nice one.