Immigrate data from SQLite3 to MySQL on Linux

This is a classical problem of how to immigrate data from SQLite3 to MySQL. I tried lots of scripts found online and the following one is the best suitable to me.

The code is from https://github.com/athlite/sqlite3-to-mysql/blob/master/sqlite3-to-mysql  and the author should take the whole credit.

#/usr/bin/env sh
sed \
-e '/PRAGMA.*;/ d' \
-e '/BEGIN TRANSACTION.*/ d' \
-e '/COMMIT;/ d' \
-e '/.*sqlite_sequence.*;/d' \
-e 's/"/`/g' \
-e 's/CREATE TABLE \(`\w\+`\)/DROP TABLE IF EXISTS \1;\nCREATE TABLE \1/' \
-e 's/\(CREATE TABLE.*\)\(PRIMARY KEY\) \(AUTOINCREMENT\)\(.*\)\();\)/\1AUTO_INCREMENT\4, PRIMARY KEY(id)\5/' \
-e "s/'t'/1/g" \
-e "s/'f'/0/g" \
$1

 Here are the full process of immigrating SQLite3 to MySQL in Linux (Ubuntu for me).
1) Make sure that you have installed sqlite3 which is a management software for sqlite database. Otherwise,
    $  sudo apt-get install sqlite3
2) Save the above code in a file like sqlite32mysql.sh and assign execute privilege.
    $  chmod +x sqlite32mysql.sh
3) If you don't have a database, then create one
     mysql -u xx -p xx -h 127.0.0.1 "CREATE DATABASE sample IF NOT EXIST"
4) If you'd like only import the data to old tables,
      $  sqlite3 your_sqlite3_db.db .dump|grep "^INSERT"| ./sqlite32mysql.sh >dump.sql
   Otherwise create new tables,
     $   sqlite3 your_sqlite3_db.db .dump| ./sqlite32mysql.sh >dump.sql
5) If there are still some records cannot be imported correctly, do it separately using the following command   to grab them out.
     $ sed -n "starter_num, end_line_num p" dump.sql > manual.sql
6) Load the dump.sql using mysql.
    $ mysql -u xx -p xx -h 127.0.0.1 sample <dump.sql

BINGO!

Remove duplicated rows

Problem: 

Assume you have a table with structure as follows.

    Comment [id, app_id, reviewer_id,review_id ....] where id is primary key.

And you have to remove duplicated records with the same (app_id, and review_id).

Here are some solutions to fulfill your requirement.

Solution 1: [For small size of table]

 1) Find out which records are duplicated.

    SELECT id FROM (SELECT COUNT(*) AS num, id, app_id, review_id FROM Comment GROUP BY app_id,review_id) AS result WHERE result.num>2;

 NOTE: the above sql statement will list one of duplicated id;

2) Discard duplicated records

   DELETE FROM Comment WHERE id in (SELECT id FROM (SELECT COUNT(*) AS num, id, app_id, review_id FROM Comment GROUP BY app_id,review_id) AS result WHERE result.num>2)

Solution 2: [Once for all]

    ALTER IGNORE TABLE Comment ADD UNIQUE INDEX idx_name (app_id,review_id);

This solution works for me so that I did not examine its limitation in some extreme cases. 

The credit of this solution goes to http://stackoverflow.com/a/3312066 . 

EPFImporter with error [WARNING]: Incorrect string value: '\xE6\xB0\x91\xE8\xA8\xB4...' for column 'title' at row 15

When using EPFImporter provided by Apple .Inc to import EPF data into table, it reported the following errors.

[WARNING]: Incorrect string value: '\xE6\xB0\x91\xE8\xA8\xB4...' for column 'title' at row 15

The reason is that the encoding of the column title is utf8 (assuming it is utf8) which cannot store characters larger than 3 bytes. For mysql, utf8 is designed as 3 bytes. After MySQL 5.5, they add another type utf8mb4 to deal with such problem  which is 4-byte encoding.

The solution is to make sure your table is look like this:

CREATE TABLE `epf_application_detail_tmp` (
`export_date` BIGINT(20) NULL DEFAULT NULL,
`application_id` INT(11) NOT NULL DEFAULT '0',
`language_code` VARCHAR(20) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
`title` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`description` LONGTEXT NULL COLLATE 'utf8mb4_unicode_ci',
`release_notes` LONGTEXT NULL COLLATE 'utf8mb4_unicode_ci',
`company_url` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`support_url` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`screenshot_url_1` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`screenshot_url_2` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`screenshot_url_3` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`screenshot_url_4` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`screenshot_width_height_1` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`screenshot_width_height_2` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`screenshot_width_height_3` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`screenshot_width_height_4` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`ipad_screenshot_url_1` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`ipad_screenshot_url_2` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`ipad_screenshot_url_3` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`ipad_screenshot_url_4` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`ipad_screenshot_width_height_1` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`ipad_screenshot_width_height_2` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`ipad_screenshot_width_height_3` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`ipad_screenshot_width_height_4` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
PRIMARY KEY (`application_id`, `language_code`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB;

For convenience, modify the default setting of mysql.

add the following to my.cnf

[mysqld]

collation-server=utf8mb4_unicode_ci
character-set-server=utf8mb4

Then, restart mysql and then create database e.g., epf to store EPF data. If you create epf already, make sure to change its default setting.

How to: Move the MySQL data directory in *buntu

1. Open the terminal
2. Stop MySQL with the command "sudo /etc/init.d/mysql stop".
3. Copy the existing data directory (by default located in /var/lib/mysql) using the command "sudo cp -R -p /var/lib/mysql /path/to/new/datadir". All you need are the data files, so delete the others with the command "sudo rm /path/to/new/datadir". (You will get a message about not being able to delete some directories, but that's what you want.)
4. Edit the MySQL configuration file with the command "gksu gedit /etc/mysql/my.cnf". Find the entry for "datadir", and change the path (which should be "/var/lib/mysql") to the new data directory.
5. NOW FOR THE PART THE OTHER TUTORIALS DON'T MENTION... From 7.10 (Gutsy Gibbon) forward, Ubuntu uses some security software called AppArmor that specifies the areas of your filesystem applications are allowed to access. Unless you modify the AppArmor profile for MySQL, you'll never be able to restart MySQL with the new datadir location.
   • In the terminal, enter the command "sudo gedit /etc/apparmor.d/usr.sbin.mysqld".
   • Copy the lines beginning with "/var/lib/mysql", comment out the originals with hash marks ("#"), and paste the lines below the originals.
   • Now change "/var/lib/mysql" in the two new lines with "/path/to/new/datadir". Save and close the file.
   • Reload the AppArmor profiles with the command "sudo /etc/init.d/apparmor reload".
6. Restart MySQL with the command "sudo /etc/init.d/mysql restart". With any luck, MySQL will start with no errors, and your data will be stored in the new location!

Credit goes to http://ubuntuforums.org/showthread.php?t=897354

C.5.2.4. Client does not support authentication protocol

Error:

shell> mysql
Client does not support authentication protocol requested
by server; consider upgrading MySQL client

To solve this problem, you should use one of the following approaches:



Upgrade all client programs to use a 4.1.1 or newer client library.


When connecting to the server with a pre-4.1 client program, use an account that still has a pre-4.1-style password.


Reset the password to pre-4.1 style for each user that needs to use a pre-4.1 client program. This can be done using the SET PASSWORD statement and the OLD_PASSWORD() function:
mysql> SET PASSWORD FOR
    -> 'some_user'@'some_host' = OLD_PASSWORD('newpwd');


Alternatively, use UPDATE and FLUSH PRIVILEGES:
mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd')
    -> WHERE Host = 'some_host' AND User = 'some_user';
mysql> FLUSH PRIVILEGES;


Substitute the password you want to use for “newpwd” in the preceding examples. MySQL cannot tell you what the original password was, so you'll need to pick a new one.


Tell the server to use the older password hashing algorithm:



Start mysqld with the old_passwords system variable set to 1.


Assign an old-format password to each account that has had its password updated to the longer 4.1 format. You can identify these accounts with the following query:
mysql> SELECT Host, User, Password FROM mysql.user
    -> WHERE LENGTH(Password) > 16;


For each account record displayed by the query, use the Host and User values and assign a password using theOLD_PASSWORD() function and either SET PASSWORD or UPDATE, as described earlier.








Note

In older versions of PHP, the mysql extension does not support the authentication protocol in MySQL 4.1.1 and higher. This is true regardless of the PHP version being used. If you wish to use the mysql extension with MySQL 4.1 or newer, you may need to follow one of the options discussed above for configuring MySQL to work with old clients. The mysqli extension (stands for "MySQL, Improved"; added in PHP 5) is compatible with the improved password hashing employed in MySQL 4.1 and higher, and no special configuration of MySQL need be done to use this MySQL client library. For more information about the mysqli extension, see http://php.net/mysqli.


It may also be possible to compile the older mysql extension against the new MySQL client library. This is beyond the scope of this Manual; consult the PHP documentation for more information. You also be able to obtain assistance with these issues in our MySQL with PHP forum.
From: http://dev.mysql.com/doc/refman/5.0/en/old-client.html

MYSQL collection

(1) Insert rows from select result set

13.2.5.1. INSERT ... SELECT Syntax

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

eg. Tie_all have the same structure as Tie2. Tie_all:(id,reviewer1,reviewer2,tie_strength)

insert into Tie_all (reviewer1,reviewer2,tie_strength) (select reviewer1,reviewer2,tie_strength from Tie2);

Mysql 5.0 Function and Operator Reference

Too long to paste here... please refer to the following link
http://dev.mysql.com/doc/refman/5.0/en/func-op-summary-ref.html