## Overview This is a procedure for migrating Pleasanter data to a different type of DB using [CodeDefiner](https://pleasanter.org/ja/manual/faq-codedefiner-about). As shown in the figure below, when building a new Pleasanter server, it is possible to migrate all DB data from another Pleasanter server that is already running and build a Pleasanter in the same state. Since the ID value will be the same as the source DB, Pleasanter data cannot be registered in the destination DB in advance. (If data is registered in the destination DB, the data migration process will fail with a unique constraint violation error.)  *If you are migrating data between the same type of DB, please perform the backup and restore procedure instead of this procedure. ・SQL Server: See [Migration Procedure from Pleasanter's Database (SQL Server) to Different Environments](migrate-to-other-environment-pleasanter-net5) ・PostgreSQL: See [FAQ: PostgreSQL database backup and restore procedure](faq-postgresql-backup-restore) ・MySQL: See [FAQ: MySQL database backup and restore procedure](faq-mysql-backup-restore) ## Limitations 1. There are restrictions on the types of databases that can be migrated, as shown in the table below. |Migration source|Migration destination|Usability of this procedure|Notes| |:--|:--|:--|:--| |SQL Server|PostgreSQL|Yes| - | |SQL Server|MySQL|Yes| Migration is not possible with versions of Pleasanter before 1.4.12.0 | |PostgreSQL|SQL Server|No| - | |PostgreSQL|MySQL|No| - | |MySQL|SQL Server|No| - | |MySQL|PostgreSQL|No| - | 2. Errors may occur during data migration due to differences in specifications for each DB, such as the maximum number of characters that can be stored. Before implementing this procedure in a production environment, perform a rehearsal and check for the presence of error data and recovery procedures before performing the work in the production environment. 3. Large-capacity data such as attachments in the hundreds of megabytes may cause errors during migration processing due to the program exceeding its tolerance range. 4. Large-capacity string data in the hundreds of millions of characters may cause errors during migration processing due to the program exceeding its tolerance range. ## Prerequisites 1. As you proceed through this procedure, write the source DB information in "[Migration.json](/en/manual/Migration-json)" and the destination DB information in "[Rds.json](/en/manual/rds-json)" for the DB connection information to be written in the parameter file. The same settings will be used in "[Rds.json](/en/manual/rds-json)" even after starting Pleasanter. The source DB information written in "[Migration.json](/en/manual/Migration-json)" will not be used except for the migration process of this procedure, so for security reasons, update "SourceConnectionString" to null after the migration is complete. ## Advance preparations ### 1. Check the source DB 1. Upgrade the source Pleasanter to the same version as the destination Pleasanter as necessary. In this procedure, the table definitions of the source and destination DBs must match, so adjust them so that they are the same version. <br/> *Please refrain from putting the source Pleasanter version first, as it will not be possible to migrate data due to insufficient definitions. 2. The source DB connection information will be written in "[Migration.json](/en/manual/Migration-json)" in the following procedure. Please check the contents of "[Rds.json](/en/manual/rds-json)" and "[Service.json](/en/manual/service-json)" of the source DB as necessary. 3. If the source DB is on an external server from the destination Pleasanter, you need to set the source DB to allow external connections. Please set it as necessary. ### 2. Pleasanter setup (first half) Perform the steps in the setup manual halfway through. ### 2-a. When installing on Windows ・[Install Pleasanter on Windows Using the Installer](getting-started-installer-pleasanter-windows) ・[Install Pleasanter on Windows](getting-started-pleasanter-windows) If you are setting up Pleasanter for the first time using any of the above procedures, perform the following setup procedure (halfway through). <details> <summary> (Click here to open/close the details) </summary> Perform the steps in the checklist below in order from the beginning of each manual. **〈Setup Procedure Checklist〉** □ Preparation □ (If using the installer) Install the installer □ Set up Pleasanter *Do not perform "Run CodeDefiner", "Set up IIS", or "Check Pleasanter's operation". </details> ### 2-b. When installing on Linux using the installer ・ ・ ・ ・ If you are setting up Pleasanter for the first time using any of the above procedures, follow the setup procedure below (partway through). <details> <summary> (Click here to open/close details) </summary> Perform the steps in the checklist below in order from the top of each manual. **〈Setup Procedure Checklist〉** □ .NET setup □ Database setup □ Installing the installer □ "Run the installer" in Pleasanter setup *Do not perform any steps beyond "Check that Pleasanter starts". </details> ### 2-c. Installing on Linux without using the installer ・[Install Pleasanter on Ubuntu](getting-started-pleasanter-ubuntu) ・[プリザンターを AlmaLinux にインストールする](getting-started-pleasanter-almalinux) ・[Install Pleasanter on Red Hat Enterprise Linux 8](getting-started-pleasanter-rhel-8) ・[Install Pleasanter on Red Hat Enterprise Linux 9](getting-started-pleasanter-rhel) If you are setting up Pleasanter for the first time using any of the above procedures, follow the setup procedure below (part way through). <details> <summary> (Click here to open/close details) </summary> Perform the steps in the checklist below in order from the beginning of each manual. **〈Setup Procedure Checklist〉** □ .NET setup □ Database setup □ Prizenter setup "Prepare the application" and "Configure the database" *Do not perform any steps after "Run CodeDefiner". </details> ### 2-d. When using Docker ・ ・ If you are setting up Pleasanter for the first time using any of the above procedures, follow the setup procedure below (partway through). <details> <summary> (Click here to open/close details) </summary> Perform the steps in the checklist below in order from the beginning of each manual. **〈Setup Procedure Checklist〉** □ File placement *Do not perform any steps after "Building the container image". </details> ## DB migration flow 1. Change "[Migration.json](/en/manual/Migration-json)" 2. Run CodeDefiner in migrate mode 3. Change "[Migration.json](/en/manual/Migration-json)" 4. Set up Pleasanter (second half) 5. (If migration is canceled due to an error) Check the error log ## 1. Change "[Migration.json](/en/manual/Migration-json)" Enter information about the source DB. Check the "[Migration.json](/en/manual/Migration-json)" manual and enter the content according to the version. **When starting Presenter with Docker** After modifying the parameter file including "[Migration.json](/en/manual/Migration-json)", run the following command to build the container image. ``` docker compose build ``` ## 2. Run CodeDefiner in migrate mode Check whether the arguments /l and /z are set according to the results of the flowchart below. **〈Flowchart〉** 1. Question: Have you run the Pleasanter installer? (*) If so, run "2-a. Command without arguments /l and /z". 2. Question: No installer and version ver.1.4.6 or later? If so, run "2-b. Command with arguments /l and /z". 3. Otherwise, run "2-a. Command without arguments /l and /z". *If you are using Docker, assume "No installer". ### 2-a. Commands without arguments /l and /z <details> <summary> (Click here to open/close details) </summary> #### For Windows Execute the following command in the command prompt. ``` cd {path to Implem.CodeDefiner} dotnet Implem.CodeDefiner.dll migrate ``` #### For Linux Execute the following command in the terminal. ``` cd {path to Implem.CodeDefiner} sudo -u {Linux user name} /usr/local/bin/dotnet Implem.CodeDefiner.dll migrate ``` #### For Docker Execute docker compose run. ``` docker compose run --rm codedefiner mingate ``` </details> ### 2-b. Commands that specify the arguments /l and /z <details> <summary> (Click here to open/close the details) </summary> #### About the arguments /l and /z The following command is an example of setting up a English environment. |Argument|e.g.|Description| |:--|:--|:--| |/l|en|Rewrites the value of DefaultLanguage in Service.json (*1)| |/z|UTC|Rewrites the value of TimeZoneDefault in Service.json (*1) | *1: For the language to specify with /l and the time zone to specify with /z, please refer to the following manual page. [FAQ: I want to know the parameter settings for languages ​​and time zones supported by Pleasanter](https://pleasanter.org/manual/faq-supported-language) #### For Windows Execute the following command in the command prompt. ``` cd {path to Implem.CodeDefiner} dotnet Implem.CodeDefiner.dll migrate /l "en" /z "UTC" ``` #### For Linux Execute the following command in the terminal. ``` cd {path to Implem.CodeDefiner} sudo -u {Linux user name} /usr/local/bin/dotnet Implem.CodeDefiner.dll migrate /l "en" /z "UTC" ``` #### For Docker Execute docker compose run. ``` docker compose run --rm codedefiner mingate /l "en" /z "UTC" ``` </details> ### Start message The following message is displayed at the start. Press the "y" key and then the "Enter" key to proceed. ``` text Type "y" (yes) if the license is correct, otherwise type "n" (no). | ``` ### Migration process flow The process is performed in two steps. 1. Process to create an Implem.Pleasanter database and an empty table with no data in the migration destination DB 2. Process to migrate data from the migration source DB to the migration destination *Note: Depending on the amount and size of data, it may take some time for the migration process in 2 above to finish. ### When the migration is complete, the following will be displayed at the end of the message on the screen. ``` text <SUCCESS> Starter.MigrateDatabase: The migration is complete. <SUCCESS> Starter.Main: All of the processes have been completed ``` After the message is displayed, please carry out the following steps. **・3. Change "[Migration.json](/en/manual/Migration-json)"** **・4. Set up Pleasanter (second half)** ### If an error occurs during migration, the following will be displayed at the end of the message on the screen. ・If version is 1.4.13.0 or later and "AbortWhenException" = true in "[Migration.json](/en/manual/Migration-json)" ``` text <ERROR> System error message ~ End of system error message Abort. Press any key to close. ``` ・If version is 1.4.13.0 or later and "AbortWhenException" = false in "[Migration.json](/en/manual/Migration-json)" ``` text <ERROR> Starter.MigrateDatabase: The migration process was completed, but some data encountered errors. See {file path}. <ERROR> Starter.Main: There were {number of <ERROR>} errors. Please check the log file ({file path}). ``` ・If version is 1.4.12.0 or earlier ``` text <ERROR> Starter.Main: There were {number of <ERROR>} errors. Please check the log file ({file path}). ``` After the message is displayed, please follow the steps below. **・3. Change "[Migration.json](/en/manual/Migration-json)"** **・5. (If migration is stopped due to an error) Check the error log** *Please do not perform "4. Setting up Pleasanter (second half)" until error recovery is complete, as this is a procedure to start Pleasanter in the destination database. ## 3. Change "[Migration.json](/en/manual/Migration-json)" The "SourceConnectionString" in "[Migration.json](/en/manual/Migration-json)" contains the connection information for the source DB. "[Migration.json](/en/manual/Migration-json)" is not used except for the command execution in "2. Run CodeDefiner in migrate mode" above, so change it to null immediately after execution. ``` "SourceConnectionString": null, ``` ## 4. Set up Pleasanter (second half) Preparation: Follow the remaining setup steps from "2. Set up Pleasanter (first half)" to the end. ### 4-a. When installing on Windows <details> <summary> (Click here to open/close details) </summary> Follow the steps in the following checklist until the end of each manual. **〈Setup Procedure Checklist〉** □ IIS setup □ Check that Pleasanter is running *Do not execute "Run CodeDefiner". </details> ### 4-b. Installing on Linux using the installer <details> <summary> (Click here to open/close the details) </summary> Perform the steps in the following checklist in order until the end of each manual. **〈Setup Procedure Checklist〉** □ Pleasanter setup: "Check that Pleasanter is running", "Create a script for the Pleasanter service", "Register as a service and start the service" □ Reverse proxy (nginx) setup □ Check that Pleasanter is working </details> ### 4-c. Installing on Linux without using the installer <details> <summary> (Click here to open/close the details) </summary> Perform the steps in the following checklist in order until the end of each manual. **〈Setup Procedure Checklist〉** □ Pleasanter setup: "Check Pleasanter startup", "Create script for Pleasanter service", "Register as service and start service" □ Reverse proxy (nginx) setup □ Pleasanter operation check *Do not execute "Run CodeDefiner". </details> ### 4-d. When using Docker <details> <summary> (Click here to open/close details) </summary> Perform the steps in the following checklist in order until the end of each manual. **〈Setup Procedure Checklist〉** □ Pleasanter startup *Do not execute "Run CodeDefiner". </details> ## 5. (If migration is aborted due to an error) Check the error log After "2. Run CodeDefiner in migrate mode", two types of log files will be created: the CodeDefiner standard log and the error data list log. ・Log file storage location: {Implem.CodeDefiner path}/logs 1. CodeDefiner standard log file: "Implem.CodeDefiner_{year/month/date}_{hour/minute/second}.log" 2. Error data list log file: "Implem.CodeDefiner_{year/month/date}_{hour/minute/second}._Migratelog" (created for version 1.4.13.0 and later) ### CodeDefiner standard log file The same log as the message displayed when CodeDefiner is executed is recorded. For versions earlier than 1.4.12.0, please determine the table where the error occurred from the contents of the CodeDefiner standard log file. ### Error data list log file Created for version 1.4.13.0 or later. Records table-level or data-level error information. **Table-level error** Cause: Mainly connection errors. Occurs due to external causes or when the amount of data in the source DB is too large to be stored in the program. ・Example ``` text [ERROR] Table:"Binaries" System.Data.SqlClient.SqlException caught. ``` ・Table: Name of the table causing the error ・Only outputs information about the table where an error was detected during processing. ・After the error occurred, the migration of data in the table may have been skipped. ＊Be sure to compare the tables ( **including _history and _deleted** ) before and after migration to check the situation. **Data-level error** Cause: An error during SQL processing for INSERT of the relevant data. Occurs when a unique constraint is violated due to a work error or when the data size is too large. ·example ``` [ERROR] Table:"Binaries" Data:"BinaryId"=999, "TenantId"=999, "ReferenceId"=999, "Guid"=ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ, "Ver"=1, "BinaryType"=Attachments, "Title"=Attachment.zip, "Body"=, "Bin"=System.Byte[], "Thumbnail"=, "Icon"=, "FileName"=Attachment.zip, "Extension"=.zip, "Size"=999, "ContentType"=application/x-zip-compressed, "BinarySettings"=, "Comments"=, "Creator"=999, "Updator"=999, "CreatedTime"=2025/01/01 12:00:00.000, "UpdatedTime"=2025/01/01 12:00:00.000 ``` ・Table: The name of the table causing the error ・Data: The data causing the error ・Only the information on the data where an error was detected in the INSERT SQL process is output. ＊Be sure to compare the tables ( **including _history and _deleted** ) before and after the migration to check the situation. **Recovery Procedure** 1. Identify the table where the error was detected from the log contents 2. Compare the DB contents before and after the migration of the relevant table ( **including _history and _deleted** ) 3. Check the data that was not migrated 4. Implement recovery work. For details, please refer to the FAQ below. ## FAQ ### Q1. Is it possible to write combinations of DBs that are not subject to migration in "[Migration.json](/en/manual/Migration-json)" and "[Rds.json](/en/manual/rds-json)"? Since version 1.4.13.0, parameters are checked, so if you write anything other than what is allowed, CodeDefiner will start with an error and you will not be able to migrate the database. Migration.json ・Only "SQLServer" can be specified for Dbms. ・Only "Local" can be specified for Provider. Rds.json ・Only "PostgreSQL" or "MySQL" can be specified for Dbms. ・Only "Local" can be specified for Provider. For versions before 1.4.12.0, only combinations from SQL Server to PostgreSQL are assumed. If you write information for a non-target DB in the connection information of each parameter file and execute it, it will cause an error that the system does not expect, so please refrain from doing so. ### Q2. I detected an error that seems to be a DB connection error. Is there a problem with the settings? The following causes are possible. 1. An error in "[Rds.json](/en/manual/rds-json)" related to the migration DB 2. An error in the migration DB settings (external reference settings, firewall, etc.) 3. An error in "[Migration.json](/en/manual/Migration-json)" related to the migration source DB 4. An error in the migration source DB settings (external reference settings, firewall, etc.) (1.) and (2.) above are detected immediately after the CodeDefiner process starts. (3.) and (4.) above are detected at the start of the migration process after the Implem.Pleasanter service creation process ends, if there is no problem with the migration destination DB settings. Please use the above criteria to determine whether the error occurred in the connection to the DB before or after migration, and review the settings. ### Q3. The process to create the Implem.Pleasanter service in the migration destination DB was executed normally, but the data migration process ended with a connection error at the beginning. When correcting the connection information of the source DB and performing the migration process again, do I need to delete the Implem.Pleasanter created in the destination DB? No need to delete it. Please re-run the same command as "2. Run CodeDefiner in migrate mode". The empty Implem.Pleasanter service created last time will be used as is for migration. When the confirmation message "Type "y" (yes) if the license is correct, otherwise type "n" (no)" is displayed, press "y" and "Enter" in the same way. ### Q4. How do I repair data that failed to migrate? (When correcting the cause data in the source DB and redoing the entire migration process) This is effective when there are many error data. 1. Compare the DB contents before and after migration (including _history and _deleted) and check the data that was not migrated. 2. Check the contents of each column in the pre-migration DB for the data that was not migrated. 3. Compare the messages output to the CodeDefiner standard log and the column definition of the migration destination DB with the contents of 2. above to determine the cause of the error in the previous INSERT process. 4. In the pre-migration DB, correct all the parts that caused the data that caused the migration error. 　*There are two ways to do this: use a database client to execute the UPDATE statement, or start Pleasanter and correct it. In many cases, data in text format can be corrected with the UPDATE statement without any problems, but it is preferable to correct JSON format and binary files on Pleasanter. 5. Connect to the destination DB using a database client and delete the Implem.Pleasanter service with the DROP DATABASE statement. 6. Re-perform the migration procedure in this manual. ### Q5. Please tell me how to repair data that failed to migrate. (When registering data that failed to migrate one by one while maintaining the post-migration DB) This is effective when the number of error data is small. 1. Compare the contents of the DB before and after the migration (including _history and _deleted) and check the data that was not migrated. 2. Check the contents of each column in the DB before the migration for the data that was not migrated. 3. Compare the messages output to the CodeDefiner standard log and the column definition of the migration destination DB with the contents of 2. above to determine the cause of the error in the previous INSERT process. 4. Write the INSERT statement. When doing so, correct the value of the error cause confirmed in 3. above. 5. Connect to the migration destination DB using a database client and execute the INSERT statement written in 4. above. 6. Execute 2. to 5. for all data that needs to be migrated. ### Q6. The following error message was displayed during the migration process to MySQL. Can the value of "max_allowed_packet" be changed? **"MySqlConnector.MySqlException (0x80004005): Error submitting 100MB packet; ensure 'max_allowed_packet' is greater than 100MB."** (*The 100MB part varies depending on the environment.) The error may be resolved by increasing the value of "max_allowed_packet" on the MySQL server. ・Reference: [MySQL :: MySQL 8.4 Reference Manual :: B.3.2.8 Packet Too Large](https://dev.mysql.com/doc/refman/8.4/en/packet-too-large.html) The location where the MySQL configuration file containing "max_allowed_packet" is deployed varies depending on the environment. After correcting the MySQL configuration file, restart the MySQL service. ### Q7. There seems to be something not displayed in the log error data list. Is there a problem with the log file? Due to the specifications of the log file output, data exceeding 1024 characters and data in JSON format will not be output to the log file. Please check the exact contents of the source data using database client software or database client commands. ## Supported Versions |Supported versions|Body| |:--|:--| | - |Initial release of function to migrate Pleasanter's DB from SQL Server to PostgreSQL| |1.4.13.0 and later|Added function to migrate Pleasanter's DB from SQL Server to MySQL| ## Related Column <div id="ManualList"><ul><li><a href="/en/manual/migrate-sql-server-to-postgresql">Migration Procedure from Pleasanter's DB from SQL Server to PostgreSQL</a><span>08.13.2024 up</span></li></ul></article> <ul><li><a href="/en/manual/Migration-json">Set Parameter: Migration.json</a><span>10.04.2024 up</span></li> <li><a href="/en/manual/rds-json">Set Parameter: Rds.json</a><span>10.24.2024 up</span></li> <li><a href="/en/manual/service-json">Set Parameter: Service.json</a><span>10.10.2024 up</span></li></ul></article></div><input id="SearchTextHidden" type="hidden" value="" />