We have just deployed our first 2019 server running MySQL 8. We can create and delete users for MySQL 8 without any issues, but attempting to change the password of a MySQL user results in an exception. Is there any debug level logging available in SollidCP that might help identify the problem?
We have enabled full logging in MySQL 8 and can see the following activity:
C:Program FilesMySQLMySQL Server 8.0binmysqld.exe, Version: 8.0.16 (MySQL Community Server - GPL). started with:
TCP Port: 3306, Named Pipe: (null)
Time Id Command Argument
2019-08-30T20:30:19.240388Z 7 Connect
2019-08-30T20:30:19.240691Z 7 Query SELECT @@skip_networking, @@skip_name_resolve, @@have_ssl='YES', @@ssl_key, @@ssl_ca, @@ssl_capath, @@ssl_cert, @@ssl_cipher, @@ssl_crl, @@ssl_crlpath, @@tls_version
2019-08-30T20:30:19.246195Z 7 Quit
2019-08-30T20:31:02.083284Z 8 Connect root@WIN-8I54K2BF6BA on mysql using SSL/TLS
2019-08-30T20:31:02.118934Z 8 Query SHOW VARIABLES
2019-08-30T20:31:02.180316Z 8 Query SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP())
2019-08-30T20:31:02.189125Z 8 Query SHOW COLLATION
2019-08-30T20:31:02.207286Z 8 Query SET character_set_results=NULL
2019-08-30T20:31:02.225635Z 8 Init DB mysql
2019-08-30T20:31:02.230620Z 8 Query SELECT Db FROM db WHERE LOWER(User)='tphoneuser' AND Host='%' AND Select_priv = 'Y' AND Insert_priv = 'Y' AND Update_priv = 'Y' AND Delete_priv = 'Y' AND Index_priv = 'Y' AND Alter_priv = 'Y' AND Create_priv = 'Y' AND Drop_priv = 'Y' AND Create_tmp_table_priv = 'Y' AND Lock_tables_priv = 'Y'
2019-08-30T20:31:15.565943Z 8 Init DB mysql
2019-08-30T20:31:15.566567Z 8 Query SELECT Db FROM db WHERE LOWER(User)='tphoneuser' AND Host='%' AND Select_priv = 'Y' AND Insert_priv = 'Y' AND Update_priv = 'Y' AND Delete_priv = 'Y' AND Index_priv = 'Y' AND Alter_priv = 'Y' AND Create_priv = 'Y' AND Drop_priv = 'Y' AND Create_tmp_table_priv = 'Y' AND Lock_tables_priv = 'Y'
2019-08-30T20:31:15.568280Z 8 Init DB mysql
2019-08-30T20:31:15.568640Z 8 Query REVOKE ALL PRIVILEGES ON tphonedb.* FROM 'tphoneuser'@'%'
2019-08-30T20:31:15.750899Z 8 Init DB mysql
2019-08-30T20:31:15.751212Z 8 Query GRANT ALL PRIVILEGES ON tphonedb.* TO 'tphoneuser'@'%'
2019-08-30T20:31:15.758539Z 8 Init DB mysql
Solid CP then displays the following error:
Page URL: https://cp.XXXXXXX.com/Default.aspx?pid=SpaceMySql8&mid=168&ctl=edit_item&ItemID=8791&SpaceID=550
Logged User: XXXXXXXXX
Work on Behalf: XXXXXXXbetting_test
Hosting Space: 550
Stack Trace: System.Exception: Exception of type 'System.Exception' was thrown.
The MySQL user password is not changed.
Any suggestions (polite ones please!) would be much appreciated.
Thanks in advance!
Mark...
Hello,
You are best to check the Windows Eventlog of the server running the MySQL server. You can find the errors in Applications -> SolidCP.
It is also possible to modify the SolidCP Server web.config to increase the logging from 2 to 3 or 4.
Regards,
Trevor
Thank you Trevor, I've checked the event logs and this is what I'm seeing. It looks like SolidCP is generating invalid SQL for the password update but I'm struggling to configure MySQL8 to log all queries in order to prove that. I changed the SolidCP logging level from 2 to 4 on the MySQL server but that didn't give any extra information in either the event log, or the audit log - am I missing something obvious?
[9/5/2019 4:29:49 PM] ERROR: 'MySQL Server 8.0' UpdateUser
MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('Testing10!!')' at line 1
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at SolidCP.Providers.Database.MySqlServer.ExecuteNonQuery(String commandText, String connectionString)
at SolidCP.Providers.Database.MySqlServer.ChangeUserPassword(String username, String password)
at SolidCP.Providers.Database.MySqlServer.UpdateUser(SqlUser user, String[] allDatabases)
at SolidCP.Server.DatabaseServer.UpdateUser(SqlUser user, String[] databases)
Hello,
Can you please try download this file: http://installer.solidcp.com/Files/1.4.4/SQLProvider-Fix.zip
The DLL files should override the files in C:SolidCPServer asp.net v4.5bin on the server running the SQL server.
Regards,
Trevor
Hi Trevor,
Thanks for looking into this, I've upgraded the server to 1.4.4 and replaced the two DLL files you've provided, unfortunately I still get the same exception when changing a password. I can still create new MySQL users so it appears that SolidCP is still communicating with MySQL correctly, it just won't perform a password change. Can I provide you with any further information?
Thanks,
Mark...
