Migrating data between sqlite and mySQL

[toxicandy]

I have an old sqlite database that I am no longer going to be using but I want to transfer all of my data to my new mySQL database. I have everyone imported and it all looks good but the problem is when I order my one of my columns which contains float values things go weird. Any data that is newly uploaded regardless of the size of the float gets placed first when I order by that column in ascending order. For instance, if I ALREADY had these floats: 100.2, 101.2, 150.5 before in the sqlite database and then after moving them to mySQL I append to that list something like 100.5 when I order them using SELECT * FROM TABLE ORDER BY float ASC instead of getting 100.2 100.5 101.2 150.5 I get 100.5 100.2 101.2 150.5 even if I then appended something like 300.5 it would just insert the 300.5 in between 100.5 and 100.2. Any ideas why? The collation is same between the two but it just won't order them correctly, any thoughts as to why?

[diafol]

float is a reserved keyword, so try:

SELECT * FROM `TABLE` ORDER BY `float` ASC

But I'm thinking that this isn't the name of your field?

[toxicandy]

No the name of the field is actually runTime I was just using float to represent the basic structure we are using for the select query.

[cereal]

Hi, I understand the data is float but I'm not sure about the column type, is this: decimal, float or double?

[toxicandy]

Column is a float in both sqlite and mySQL

[diafol]

Supply an SQL dump of the data and either paste here or provide link to said dump.

[toxicandy]

Here is a complete dump:

http://pastebin.com/xF3c2k4g

[diafol]

Strange. Works for me. I created your table, imported a slice of the data and inserted a record. I ran your select query, but it all looks ok to me:

[cereal]

Same for me, I ran:

SELECT * FROM `playertimes` WHERE `runtime` BETWEEN 100 AND 200 ORDER BY `runtime` ASC LIMIT 20

And returns in perfect order.

[toxicandy]

That is why I thought this was weird. IF instead of importing the table from sqlite I just start fresh with no records the query works, as soon as I move the data over the queries break. Same data but it stops ordering them correctly. The queries return correct values when quering the sqlite database but when I transfer the data to mySQL and run the queries onto mySQL, it looks like it works, they come out ordered UNTIL I insert any new record then that record regardless of the runTime gets shown first and then the correct list after.

[diafol]

How are you exporting this to MySQL?
Can you show the code?

[toxicandy]

I can provide you with a .sq3 from the sqlite3 database, most of the time I am going from .sq3 to .csv and importing that, I have tried various methods/programs.

edit methods tried:

.sq3 to .sql using https://dbconvert.com/sqlite/mysql/
.sq3 to xml to mysql
.sq3 to json to mysql

all were doing using navicat premium

[diafol]

It would be interesting to see the original converted .sql file, if you still have it. You may have guessed but am grasping at straws here. :)

[toxicandy]

https://dl.dropboxusercontent.com/u/3036539/kztimer-sqlite.sq3

Here is the origional from sqlite3. It's really weird how/why this is all happening.

[cereal]

Very strange, I think it could be the exporter tool but, once imported, the numbers should be managed as floats no matter. What I see is that the default definition of float in the MySQL table will set only 6 digits and round the last, but this should not make a big difference as you stated.

Have you tried to export data directly from sqlite client?

.mode csv
.output /path/to/output.csv
select * from playertimes;
.quit

Then loop output.csv through fgetcsv() or use this script:

<?php

# MySQL credentials
require 'config.php';

/**
 * Set placeholders for batch insert
 * 
 * @param  integer $number
 * @return  string
 */
function set_placeholders($number)
{
    $v = [];

    for($i = 0; $i < $number; $i++)
        $v[] = '(?, ?, ?, ?, ?, ?, ?)';

    return implode(',', $v);
}

# sqlite database, define path
$db1 = new PDO("sqlite:./kztimer-sqlite.sq3");
$q1  = $db1->prepare("SELECT * FROM playertimes");
$q1->execute();
$rows = $q1->fetchAll(PDO::FETCH_ASSOC);

# mysql database
$db2 = new PDO("mysql:host={$db_host};dbname={$db_name}", $db_user, $db_pass);

# batch insert: 200 rows per time
$chunks = array_chunk($rows, 200);
$i = 1;

foreach($chunks as $chunk)
{
    $setp = set_placeholders(count($chunk));
    $q2   = $db2->prepare("INSERT INTO playertimes (steamid, mapname, name, teleports, runtime, runtimepro, teleports_pro) VALUES {$setp}");

    $values = [];

    foreach($chunk as $row)
        $values = array_merge($values, array_values($row));

    $q2->execute($values);

    print 'Rows '. $q2->rowCount() . PHP_EOL;
    print 'Chunk '. $i . PHP_EOL;
    print PHP_EOL;

    $i++;
}

Just set the path to the sqlite database and the credentials to the MySQL database and it should import the table without problems.

However, I'm not sure this would make a difference.