Answered Installing 2.3.1 Database Errors

I'm trying to install 2.3.1 onto php 5.5 with MySQL 5.1.62.

FYI - the Webserver is separate to the MySQL Server, I have already created a database on the MySQL server and I am using an account with dba rights (note, the account doesn't have the rights to actually create databases, it's locked down to this specific database).


When I enter the Database Host, Login, Password, Name & Table Prefix, click the Test database server connection and view collations. and I get this:

Connecting to database server: Success!
- Checking MySQL server version: OK! Running: 5.1.63-log
- Checking MySQL client version: OK! Running: 5.0.11-20120503-$Id:40933630edef551dfaca71298a83fad8d03d62d4$

Although I notice Connection character set and Collation are both emtpy.


Then when I click Create or test selection of your database., I get this:

Checking database: MODX could not create your database. Please manually create your database and then try again.


If I click on Test database server connection and view collations again, I get this:

Connecting to database server:
Could not connect to the database server. Check the connection properties and try again.

[2014-07-27 07:24:25] (ERROR in xPDOConnection::connect @ /hsphere/local/home/crystaldev/crystaldisplaydev.com/core/xpdo/xpdo.class.php : 3043) SQLSTATE[HY000] [2019] Unknown character set

[2014-07-27 07:24:25] (ERROR in xPDOConnection::connect @ /hsphere/local/home/crystaldev/crystaldisplaydev.com/core/xpdo/xpdo.class.php : 3043) SQLSTATE[HY000] [2019] Unknown character set


The MySQL server Collation and Characterset is:

Server connection collation: Utf8_General_ci
Server charset: UTF-8 Unicode (utf8)


I have previously installed an earlier version of ModX on this server and database server.

Any suggestions?

<crickets>
<tumbleweed>

If it couldn't connect to the database server, that means that one of the connection items is incorrect - the database server, the username or the password.

Quote from: sottwell at Jul 28, 2014, 06:38 PM

If it couldn't connect to the database server, that means that one of the connection items is incorrect - the database server, the username or the password.

So, are you saying this part:

When I enter the Database Host, Login, Password, Name & Table Prefix, click the Test database server connection and view collations. and I get this:

Connecting to database server: Success!
- Checking MySQL server version: OK! Running: 5.1.63-log
- Checking MySQL client version: OK! Running: 5.0.11-20120503-$Id:40933630edef551dfaca71298a83fad8d03d62d4$

Should I report that as a bug?

A couple things stand out here.

1) When it connects to the database server the first time, it's probably just pinging it.
2) When it connects for the purpose of installing, it's trying to create the database tables within the schema. I also do installs using MS SQL Server and MODX installation cannot create a database, so I have to do that manually ahead of time or else I get that "cannot create database" error.

To get around this, do the following:
1) log into MySQL using your credentials you use to connect with MODX
2) try to create a table
3) make sure it creates successfully in the schema you've specified with MODX

My guess is that your database don't have rights in MySQL to actually create tables or you don't have rights to create tables in that schema.

Hope this helps.

Regards,
Tom

Quote from: stalemate at Jul 28, 2014, 07:53 PM

A couple things stand out here.

1) When it connects to the database server the first time, it's probably just pinging it.

Do you know if a ping can retrieve the the version of MySQL server installed?

Quote from: stalemate at Jul 28, 2014, 07:53 PM

2) When it connects for the purpose of installing, it's trying to create the database tables within the schema. I also do installs using MS SQL Server and MODX installation cannot create a database, so I have to do that manually ahead of time or else I get that "cannot create database" error.

I know the user account specified doesn't have rights to create databases on the server, it does however have DBA rights on the pre-created database itself.

Quote from: stalemate at Jul 28, 2014, 07:53 PM

To get around this, do the following:
1) log into MySQL using your credentials you use to connect with MODX
2) try to create a table
3) make sure it creates successfully in the schema you've specified with MODX

I have logged into mypPhpAdmin and can confirm I am able to perform all DBA tasks on the database itself, but I am not able to create or delete databases. This is part of the security within the implementation of the hosting environment.

Quote from: stalemate at Jul 28, 2014, 07:53 PM

My guess is that your database don't have rights in MySQL to actually create tables or you don't have rights to create tables in that schema.

Hope this helps.

Regards,
Tom

I suspect the problem is security also - but it would be specific to the current release of ModX - as I've installed previous releases onto this database server without these problems.

1) You should be able to get the version just by logging in
2) to create a table to test, go to the schema you'll be using, go to the SQL window and then type something like this:



My guess is that your account wouldn't allow that.

btw, which Web Server are you using to connect?

Your DB user can be limited just that database, but make sure the user has *full rights* to that db. If not, MODX isn't going to work there.

http://bobsguides.com/modx-friendly-hosts.html

Quote from: stalemate at Jul 28, 2014, 08:35 PM

1) You should be able to get the version just by logging in
2) to create a table to test, go to the schema you'll be using, go to the SQL window and then type something like this:

CREATE TABLE TEST (
TESTFIELD INTEGER)

My guess is that your account wouldn't allow that.

Output from running your script:

Your SQL query has been executed successfully (Query took 0.0105 sec)
CREATETABLE TEST(
TESTFIELD INTEGER
)

Quote from: stalemate at Jul 28, 2014, 08:35 PM

btw, which Web Server are you using to connect?

I'm not sure what you mean - do you mean the OS of the server?

Output:

GRANT USAGE ON *.* TO 'CRYSTA1_dba'@'%' IDENTIFIED BY PASSWORD 'sssssh!'
GRANT ALL PRIVILEGES ON `CRYSTA1\_modx`.* TO 'CRYSTA1_dba'@'%' WITH GRANT OPTION

I think there's a bug in the 2.3.1 installer.