Steps to follow Before doing MySQLUpgrade. ========================================== Pre-requisite: 1. Before doing activity stop the mysql service and please take backup of mysql binaries and my.ini file to safe location then start the mysql service again. 2. Stop the SapphireIMS Service. Steps to upgrade MySQL 5.7/8.0.20/8.0.26/8.0.30/8.0.33 to MySQL 8.3.0 ========================================== Case:1 :SapphireIMS Server and MySQL Server both on same machine -------------------------------------------------------- 1. Unzip the MySQLUpgrade_8.3.0.zip file 2. Open MySQLInstaller.bat do following changes if required: * set upgradeMySQLUsrName(Provide username used to connect MySQL server). * set upgradeMySQLPwd(Provide password used to connect MySQL server). * set upgradeMySQLPort (Provide port used to connect MySQL server). * set upgradeMySQLServer (Provide ipaddress of machine where MySQL is installed). * set olderMySQLConnectorJar (Provide previously installed connector jar file name). 3. Open a new command prompt as "Run as administrator", then navigate to MySQLUpgrade.bat contains folder and then execute the MySQLUpgrade.bat file. E.g.: \MySQLUpgrade.bat Once script got executed please close the command prompt if it is open. 4. Open config.bat () and check 'imsMySQLPath' in this mysql server 5.7/8.0 getting replaced with mysql server 8.3. If not replaced kindly replace as mysql server 8.3. Also check the 'imsMySQLDriver'; in this MySQL ODBC Connector 8.3.0 should be present if not then replace 'imsMySQLDriver' MySQL ODBC Connector any version with 8.3.0. 5. Open my.ini do following changes i. Search for STRICT_TRANS_TABLES and delete this, after deleting row looks like sql-mode="NO_ENGINE_SUBSTITUTION" Please ignore if STRICT_TRANS_TABLES is not available in my.ini ii. Search for variable "secure-file-priv" and delete the available path in this variable. After delete it looks like secure-file-priv="" Please ignore if value is already empty. iii. Search for disable-log-bin variable , this varibale must be present in my.ini. Please add at the bottom of my.ini if this variable is not avaible. iv. Add the value "local-infile=1",the value must be added at the bottom of the my.ini. Please ignore if the variable is already Added. v. Please comment these two variables of present query_cache_size = 0 and query_cache_type = 0 vi. Add below changes in mysql and client section if value is not present else update utf8 with utf8mb4. [client] default-character-set=utf8mb4 [mysql] default-character-set = utf8mb4 # created and no character set is defined character-set-server=utf8mb4 vii. Search for variable 'open_files_limit' in my.ini. If the variable is present then change the value to '50000'. If its not present add the below line in [mysqld] section open_files_limit=50000 viii. Search for variable 'innodb_open_files' in my.ini. If the variable is present then change the value to '20000'. If its not present add the below line in [mysqld] section innodb_open_files=20000 ix. Search for variable 'authentication_policy' in my.ini. If the variable is present then change the value to 'caching_sha2_password'. authentication_policy=caching_sha2_password x. Remove the below variables from my.ini file if its present innodb_log_file_size read_buffer_size sync_master_info read_rnd_buffer_size innodb_redo_log_capacity xi. Add the following variables bottom of the my.ini file read_buffer_size=8192 read_rnd_buffer_size=1 sync_source_info =10000 (Add if not available) innodb_redo_log_capacity=100663296 6. Open command prompt as Admin change directory to mysql bin location i). eg: cd C:\Program Files (x86)\SapphireIMS\MySQL Server 8.0\bin C:\Program Files (x86)\SapphireIMS\MySQL Server 8.0\bin>mysqld --defaults-file="my.ini file location"--upgrade=AUTO Eg: mysqld --defaults-file="C:\Program Files (x86)\SapphireIMS\MySQL Server 8.0\my.ini" --upgrade=AUTO (It will take few minutes to complete upgrade, if it got stopped please check error log ) ii). To confirm upgrade is done or not – Go to C:\Program Files (x86)\SapphireIMS\MySQL Server 8.0\Data and check log file (logfile_name.err) 7. Log file should display - C:\Program Files (x86)\SapphireIMS\MySQL Server 8.0\bin\mysqld.exe: ready for connections. Version: '8.3.0' socket: '' port: 3306 MySQL Community Server - GPL. 8. Open Query browser or Workbench and check that all the databases and tables Once got confirmation upgrade successful then close the command prompt. 9. Open my.ini and change datadir and basedir from Mysql 5.7/8.0 to MySQL 8.3 and save the file 10. Open a new command prompt as "Run as administrator", then navigate to postUpgrade.bat contains folder and then execute the postUpgrade.bat file. E.g.: \postUpgrade.bat It will rename the Mysql 5.7/8.0 folder to 8.3 and it will start the service. 11. Kindly refer the "utf8mb4 conversion.docx" document to convert utf8mb3 to utf8mb4.(Important step -- Please don't ignore as utf8mb4 has better performance.) 12. Kindly refer "Caching_sha2_password authentication plugin" document to change authentication plugin from mysql_native_password to caching_sha2_password(Important step -- Please don't ignore as mysql_native_password authentication has be deprecated from MySQL 8.0.34). Case:2 SapphireIMS Server and MySQL Server (Windows) on different machines -------------------------------------------------------------------------- 1) Execute MySQLUpgrade.bat on MySQL Server installed machine. Note: First execute the MySQLupgrade.bat in MySQL Server installed machine, Post execute in SapphireIMS installed machine. No need to run Mysqld --upgrade=AUTO command (Step 6)in application server. 2) Once upgrade is completed kindly edit my.ini file by following the above (step 5) in MySQL Server installed machine. 3) Open my.ini and change datadir and basedir from Mysql 5.7/8.0 to MySQL 8.3 and save the file in MySQL Server installed machine. 4) Execute PostUpgrade.bat on MySQL Server installed machine. 5) Kindly refer the "utf8mb4 conversion.docx" document to convert utf8mb3 to utf8mb4.(Important step -- Please don't ignore as utf8mb4 has better performance.) 6) Kindly refer "Caching_sha2_password authentication plugin" document to change authentication plugin from mysql_native_password to caching_sha2_password(Important step -- Please don't ignore as mysql_native_password authentication has be deprecated from MySQL 8.0.34). 7) Execute MySQLUpgrade.bat on Sapphire installed machine. 8) Execute PostUpgrade.bat on Sapphire installed machine. Steps to create MySQL service: ---------------------------------- After running postupgrade.bat file, if SapphireMySQL service not created then follow the below steps: 1) Go to the mysql installed path and verify the folder is renamed to MySQL Server 8.3. If not renamed kindly rename. 2) Open the registry and go to the HKEY_LOCAL_MACHINE\SOFTWARE\MySQL AB\MySQL Server 8.3 location. Check the following variable 2.1) Datalocation 2.2) Location 2.3) Version the value should contain mysql8.3 installation path and version. Refer the snap 'MySQLRegistery.png'. 3) Open Command Prompt with admin privilege and navigate to mysql installation path bin folder(Ex: C:\Program Files (x86)\MySQL\MySQL Server 8.3\bin) and execute the following commands mysqld.exe --install --defaults-file="\my.ini" Note: Make sure 'serviceName' and 'My.ini folderpath' value should be replaced as per the installation.