[MySQL Integration] - Issues in Configuration Tool

[MySQL Integration] - Issues in Configuration Tool

  
Hi Guys,

I have successfully installed MySQL on my machine and using configuration tool, I choose MySQL as my platform database and input credentials.  When I clicked Create/Updata Database button, it prompted me an error.

Here's the screenshot.



I need for assistance in connecting to MySQL successfully.


Cheers,

Oliver
Hi Oliver,

Sorry that you have hit this issue. Can I ask you what is the result of testing the connection for that user ?

Also did you if you checked the updated instalation checklist. There are specific instruction for the MySQL setup that need ot be followed. Namely you need to run the scripts that create the platform database and session database and it's respective users and permissions.

Let me know if this helps. Anything else feel free to ask.

Best Regards
David Nunes

Hi David,

I am with Oliver, and we've encountered another error with regards to the Configuration Tool.



Kindly advise action to be done.

Thanks,

Julius
Hi David,

We have successfully connected to the MySQL Database already.  But clicking the Apply and Exit button prompted us an error.  Here's the screenshot.




We ask for your assistance in order for successfull installation.


Thanks,

Oliver
Hi Oliver,

There seems to be a configuration missing. 
You need these configuration set on the MySQL database for the platform to operate correctly:

MySQL Database:
  • Ensure you use MySQL 5.6.5+
  • Make sure sql mode is set to "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
  • Make sure default-storage-engine is set to INNODB
  • Make sure lower_case_table_names is set to 1
  • Make sure max_allowed_packet is set to 300M
  • Make sure log_bin_trust_function_creators is set to 1 
In this particular case you are hitting on the max_allowed_packet limit. If you run MySQL on windows the confugration should look something like this:

Windows:



Linux:



Let me know if this helps.
Best Regards
David Nunes



Hi David,

I have set all of what you have instructed.  But I can't see the log_bin_trust_function_creators.  Can I ask where I an find this.?

Thanks,

Oliver
Hi Oliver,

I really need to improve these instructions, theyu are not easy to follow ...

That one needs to be done on the file system  my.cnf (linux), my.ini (windows).

Just add log_bin_trust_function_creators = 1
 

You can get the location of your my.ini from MySQL workbench:


Let me know if this helps.

Best Regars
David Nunes


Hi Guys,

Have you managed to complete the instalation ?
Can I help you in any way ?

Thanks
David Nunes
Hi David,

I've set all the configuration on your instructions and I have added log_bin_trust_function_creators = 1 already in the my.ini file but still same error prompted us.

Do you see any possible error in configuration?


Best Regards,

Oliver

Hi Oliver,

Sent you a PM so we can troubleshoot the issue I was pretty sure the settings should have fixed the prolem.
Hi,

after overcoming the max_allow_packaged problem I hit this:

Service Center Installer v8.9.5.25
 
Start installing Service Center v8.9.5.25...
This operation may take a few minutes to complete...
 
Publishing Extension(s)...
Publishing Extension 'OMLProcessor'...
Publishing Extension 'IntegrationStudio'...
Reading ServiceCenter.oml oml file information...
Adding ServiceCenter eSpace to database...
Message 1
  [Error] MySql.Data.MySqlClient.MySqlException (0x80004005): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
   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.ExecuteScalar()
   at OutSystems.HubEdition.Extensibility.Data.ExecutionService.BaseExecutionService.ExecuteScalar(IDbCommand cmd)
   at OutSystems.HubEdition.RuntimePlatform.Db.Command.ExecuteScalar(String description, Boolean isApplication, Boolean skipLog, Boolean applyTransformationsToParameters)
   at OutSystems.HubEdition.RuntimePlatform.DBRuntimePlatformMySQL.ExecuteNonQueryGetIdentity(Command cmd, String idColumnName)
   at OutSystems.HubEdition.ServerCommon.DBCommon.CreateEspaceVersion(Transaction trans, Int32 versionNum, Byte[] oml, Int32 userId, Int32 espaceId, String serviceStudioVersion, String lastUpgradeVersion, String name, String description, String hash, DateTime lastModified, Boolean hasHTTPS, Boolean hasIntegratedAuthentication, Boolean hasSMS, Boolean isUserProvider, String userProviderKey, String userProviderName, String directUpgradeFromHash, String generalHash, JQueryVersion jqueryVersion)
   at #3Xe.#2Ye.#IYe(Transaction trans, String eSpaceName, Int32 eSpaceId, String filename, String SSVersion, String LastUpgradeVersion, Boolean hasHTTPS, Boolean hasIntegratedAuthentication, Boolean hasSMS, String versionHash, String generalHash)
   at #3Xe.#2Ye.#OYe(String filename, Int32& eSpaceVersion, Int32& eSpaceId, String& eSpaceName, String& tenantName, ObjectKey& eSpaceSSKey)
   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.ExecuteScalar()
   at OutSystems.HubEdition.Extensibility.Data.ExecutionService.BaseExecutionService.ExecuteScalar(IDbCommand cmd)
   at OutSystems.HubEdition.RuntimePlatform.Db.Command.ExecuteScalar(String description, Boolean isApplication, Boolean skipLog, Boolean applyTransformationsToParameters)
   at OutSystems.HubEdition.RuntimePlatform.DBRuntimePlatformMySQL.ExecuteNonQueryGetIdentity(Command cmd, String idColumnName)
   at OutSystems.HubEdition.ServerCommon.DBCommon.CreateEspaceVersion(Transaction trans, Int32 versionNum, Byte[] oml, Int32 userId, Int32 espaceId, String serviceStudioVersion, String lastUpgradeVersion, String name, String description, String hash, DateTime lastModified, Boolean hasHTTPS, Boolean hasIntegratedAuthentication, Boolean hasSMS, Boolean isUserProvider, String userProviderKey, String userProviderName, String directUpgradeFromHash, String generalHash, JQueryVersion jqueryVersion)
   at #3Xe.#2Ye.#IYe(Transaction trans, String eSpaceName, Int32 eSpaceId, String filename, String SSVersion, String LastUpgradeVersion, Boolean hasHTTPS, Boolean hasIntegratedAuthentication, Boolean hasSMS, String versionHash, String generalHash)
   at #3Xe.#2Ye.#OYe(String filename, Int32& eSpaceVersion, Int32& eSpaceId, String& eSpaceName, String& tenantName, ObjectKey& eSpaceSSKey)
 
Hi,

same problem with the RC version:

Service Center Installer v9.0.0.0
 
Start installing Service Center v9.0.0.0...
This operation may take a few minutes to complete...
 
Publishing Extension(s)...
Publishing Extension 'OMLProcessor'...
Publishing Extension 'IntegrationStudio'...
Reading ServiceCenter.oml oml file information...
Adding ServiceCenter eSpace to database...
Message 1
  [Error] MySql.Data.MySqlClient.MySqlException (0x80004005): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
   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.ExecuteScalar()
   at OutSystems.HubEdition.Extensibility.Data.ExecutionService.BaseExecutionService.ExecuteScalar(IDbCommand cmd)
   at OutSystems.HubEdition.RuntimePlatform.Db.Command.ExecuteScalar(String description, Boolean isApplication, Boolean skipLog, Boolean applyTransformationsToParameters)
   at OutSystems.HubEdition.RuntimePlatform.DBRuntimePlatformMySQL.ExecuteNonQueryGetIdentity(Command cmd, String idColumnName)
   at OutSystems.HubEdition.ServerCommon.Database.DBCommon.CreateEspaceVersion(Transaction trans, Int32 versionNum, Byte[] oml, Int32 userId, Int32 espaceId, String serviceStudioVersion, String lastUpgradeVersion, String name, String description, String hash, DateTime lastModified, Boolean hasHTTPS, Boolean hasIntegratedAuthentication, Boolean hasSMS, Boolean isUserProvider, String userProviderKey, String userProviderName, String directUpgradeFromHash, String generalHash, JQueryVersion jqueryVersion)
   at #6Xe.#5Ye.#LYe(Transaction trans, String eSpaceName, Int32 eSpaceId, String filename, String SSVersion, String LastUpgradeVersion, Boolean hasHTTPS, Boolean hasIntegratedAuthentication, Boolean hasSMS, String versionHash, String generalHash)
   at #6Xe.#5Ye.#RYe(String filename, Int32& eSpaceVersion, Int32& eSpaceId, String& eSpaceName, String& tenantName, ObjectKey& eSpaceSSKey)
   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.ExecuteScalar()
   at OutSystems.HubEdition.Extensibility.Data.ExecutionService.BaseExecutionService.ExecuteScalar(IDbCommand cmd)
   at OutSystems.HubEdition.RuntimePlatform.Db.Command.ExecuteScalar(String description, Boolean isApplication, Boolean skipLog, Boolean applyTransformationsToParameters)
   at OutSystems.HubEdition.RuntimePlatform.DBRuntimePlatformMySQL.ExecuteNonQueryGetIdentity(Command cmd, String idColumnName)
   at OutSystems.HubEdition.ServerCommon.Database.DBCommon.CreateEspaceVersion(Transaction trans, Int32 versionNum, Byte[] oml, Int32 userId, Int32 espaceId, String serviceStudioVersion, String lastUpgradeVersion, String name, String description, String hash, DateTime lastModified, Boolean hasHTTPS, Boolean hasIntegratedAuthentication, Boolean hasSMS, Boolean isUserProvider, String userProviderKey, String userProviderName, String directUpgradeFromHash, String generalHash, JQueryVersion jqueryVersion)
   at #6Xe.#5Ye.#LYe(Transaction trans, String eSpaceName, Int32 eSpaceId, String filename, String SSVersion, String LastUpgradeVersion, Boolean hasHTTPS, Boolean hasIntegratedAuthentication, Boolean hasSMS, String versionHash, String generalHash)
   at #6Xe.#5Ye.#RYe(String filename, Int32& eSpaceVersion, Int32& eSpaceId, String& eSpaceName, String& tenantName, ObjectKey& eSpaceSSKey)
 
Hi Antonio,

Have you changed the setting 
innodb_log_file_size = 480M

If so and you are still hitting the problem would you mind sending me your my.ini file usually located here on windows : C:\ProgramData\MySQL\MySQL Server 5.6\my.ini

There might be a setting that is breaking the platform instalation.
Hi David,

I have replaced my.ini with the configuration you've sent.

I encountered another error.  Here it is.




Thanks,

Oliver
Hi David,

I'm sorry the innodb_log_file_size = 480M setting must have been overwrited by the workbench when I saved the max_allow_packed because I changed innodb_log_file_size  in the my.ini file .... my mess.

Nevertheless you need to improve the instalation check list when installing MySQL, especially for those comming from other Databases like MSSQL. I'll mention this on the weekly follow up.
Hi António,

Yes you are absolutly right. We are working on improving the checklist to avoid these kinds of problems. The innodb_log_file_size  in particular caught us off guard because it's a breking changes introduced on mysql 5.6.20. On previous versions changing this setting wasn't needed to run the platform.
Hi Oliver,

The privileges cache seams to be inconsistent. Running FLUSH PRIVILEGES on mysql workbench connected to the database should get you rid of the problem.

We never it this issue on regression testing. Oliver besides repllacing the file and running the configuration tool have you changed any user privilege ? did any aditional change? I would like to trace the issue and replicate it from my side.
Hi David,


Here's the privileges I've set for each user(OSADMIN, OSRUNTIME, OSLOG, OSSTATE).


I've set all privileges to outsystems database for each user.



On the last error I've sent, I execute GRANT ALL ON outsystems.OSSYS_DEVELOPER_DBONNECTION TO 'OSRUNTIME'@'localhost';
FLUSH PRIVILEGES;
and it worked but it prompted same error with other table to be granted.  Everytime I run the script on MySQL workbench and click Apply and Exit button in configuration tool, it always prompt me same error with the different table names to be granted. Do you see any missing configuration that might cause this problem? or Do I have to continue running the script for each table it prompts to be granted?


Thanks in Advance!

Best Regards,

Oliver
Hi David,


Here's the sample of the same error but this time with other table name.




As you can notice, the last message of the error tells us what table to be granted and this table name changes eveytime I run the sript(GRANT) on MySQL workbench.

Thanks,

Oliver
Hi Oliver,

I was finally able to replicate the issue. We will need to make a fix to the product.
The good news is that there is a easy workaround. 
If you are ok with changing the host limitations of the user from localhost to % you should be able to proceed without any problem.
They should look something like this:




Also once you finish the instalation it would be great if you moved to 9.0.0.0 available here.

Let me know if this fixed it.

Hi David,


We have successfully installed the MySQL as a platform database.  The last instruction fixed it up.

A lot of thanks for the support..! We may now close this issue...


Cheers,

Oliver
Hi David,


Though the database platform changed successfully to MySQL, the license is no longer valid.  How can get rid of the problem and continue using the OutSystems Beta?

Thanks in Advance!


Regards,

Oliver
Hi Oliver,

Can you put an image of the license page on the forum with the error. If necessary we can reissue the beta license.
Hi David,


Here's the licensing page with the error.



Regards,


Oliver