MYSQL restore DB

Ali Jabak 0 Reputation points
2024-06-27T08:29:01.1166667+00:00

while restoring my DB using MYSQL workbench it creates my_row_number on the tables with AI PK, I need to stop the auto creation since causing issues in my API. If I restore the DB locally works fine only while restoring it on azure

Azure Database for MySQL
Azure Database for MySQL
An Azure managed MySQL database service for app development and deployment.
757 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Kenneth Phillips 0 Reputation points
    2024-06-28T06:20:28.97+00:00

    Hello,

    To stop the auto-creation of my_row_number when restoring your database on Azure using MySQL Workbench, you can follow these steps:

    Check Dump File: Ensure that your dump file does not include any commands or triggers that create my_row_number. Sometimes, these can be added during the export process.

    Modify Restore Settings: When restoring the database, check the settings in MySQL Workbench. Ensure that there are no additional options selected that might be causing the creation of my_row_number.

    Use Command Line: Consider using the MySQL command line for restoring your database. This can give you more control over the process. The basic command is: 

    mysql -u username -p database_name < dumpfile.sql

    Azure Configuration: Verify your Azure MySQL configuration. Ensure that there are no specific settings or policies that might be interfering with the restore process.

    Consult Documentation: Check the MySQL Workbench and Azure documentation for any known issues or additional settings that might be relevant.

    By following these steps, you should be able to prevent the auto-creation of my_row_number and ensure your API functions correctly. hpinstantink com

    Hope it work for you.
    Best Regard,
    Kenneth Phillips

    0 comments
  2. GeethaThatipatri-MSFT 29,007 Reputation points Microsoft Employee
    2024-07-02T11:59:01.7366667+00:00

    @Ali Jabak From what I understand that the auto-creation of my_row_number during the MYSQL database restore is causing issues with your API.

    This seems to be a specific behavior of MYSQL Workbench when interacting with server.

     To prevent the auto-creation of my_row_number, you can try the following steps:

    ·     Before initiating the restore process, ensure that the AUTO_INCREMENT attribute is not set for the Auto Increment PK columns in the dump file.

    • Check the MYSQL Workbench preferences and settings for any options that might influence this behavior and adjust them accordingly.   MySQL :: MySQL Workbench Manual :: 3.2.2 SQL Editor Preferences
    • Consider using MYSQL command-line tools for the restore operation, as it might offer more control over the process.

    I hope this information helps.

    Regards

    Geetha

    0 comments