4 Troubleshooting

This chapter provides information about optimizing the command line options and avoiding issues connecting SQL Developer to the MySQL database server. It includes information about:

4.1 Defining the User Account

When you migrate to Oracle, SQL Developer attempts to connect to the MySQL database as MySQL user associated with the current database connection. (SQL Developer does not use the MySQL root user.) SQL Developer can capture only those MySQL database objects that the specified MySQL user has privileges to access.

Therefore, when you create the database connection that you intend to use for MySQL migrations, be sure to specify a MySQL user that has sufficient privileges to access all objects and data that you plan to migrate to Oracle.

4.2 Dumping MySQL Data

If you are having difficulty migrating from MySQL to Oracle, you can check the SQL Developer discussion forum on the Oracle Technology Network.

If you have a support contract, the SQL Developer support team may ask you to provide a dump of the MySQL database. This helps the team track the problem and provide a swift solution. By using the mysqldump method to create a copy of the MySQL database you generate text files that are portable to other systems, even those with different hardware architecture. The SQL Developer support team can regenerate the output into another database.

The following table provides an explanation of the code used to dump the MySQL data and to regenerate the database from the mysqldump output text file:

Command Description
mysqldump Allows you to extract the schema and data in a MySQL database to a file.
mysql Loads MySQL so you can carry out the command.
-u user name MySQL user name.
-p password Password for the specified user.
--opt Optimizes table dumping speed and writes a dump file for reloading speed. For a list of definitions of the options enabled by --opt, see "Optimizing Command Line Options".
database_name Name of the database containing the information you want to dump to an output text file.
> Symbol used for re-directing the input in UNIX and NT.
file_name.sql File name containing the MySQL database information.

To dump the MySQL data, use the following command:

% mysqldump -u user name -ppassword --opt database_name > file_name.sql

To regenerate the database from the mysqldump output text file into a database, use the following command:

% mysql -u user name -ppassword database_name < file_name.sql

4.3 Optimizing Command Line Options

You automatically switch on options within the mysqldump command line by using --opt. The following table lists the commands encompassed by the -opt command:

Command Description
--add-drop-table Adds a DROP TABLE IF EXISTS statement before each CREATE TABLE statement.
--all Includes all of the MySQL specific create options.
--extended-insert Writes multiple row INSERT statements.
--quick Dumps tables directly to the standard output without buffering the query. If you suspend mysqldump while using this option, you may interfere with other clients because it could cause the server to stop responding.
--lock-tables Locks all tables as read only.