MySQL database codepage problems

[thanos]

Hello to all.
I tried to convert a SQLite database to MySQL.
Everyghing was correct except the text in BLOB fields.
Instead of $EPWNYMIA|ΓΡΙΒΑΣ ΒΑΣΙΛΕΙΟΣ» in MySQL the value stored is $EPWNYMIA|ΓΡΙΒΑΣ ΒΑΣΙΛΕΙΟΣ»
I also tried to convert the text with CONVERT('" + String$ + "' USING utf8mb4) with no results,
Please mention that in case of change the field type to VARCHAR the migration was correct.

The environment is:
PureBasic 6.02 x86
MySQL 8.0.30 (laragon)
The database was created with CHARSET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'
After I opened the database I run the following queries in order to display the Greek characters correctly.

Code: Select all

DatabaseQuery(#MYSQL_DB, "SET NAMES 'utf8'")
FinishDatabaseQuery(#MYSQL_DB)
DatabaseQuery(#MYSQL_DB, "SET CHARACTER SET 'utf8'")
FinishDatabaseQuery(#MYSQL_DB)

Thanks in advance.

Thanos

[Fred]

BLOB are binary field, it shouldn't handle any conversion. Is it only the display which is wrong or the stored value ?

[tored]

With MySQL you both have charset on the table, but also on the connections it self. This is also something that can be configured in your MySQL my.cnf.

E.g. in PHP when you set the charset on the connection with PDO it is the equivalent of doing SET NAMES

[thanos]

BLOB are binary field, it shouldn't handle any conversion. Is it only the display which is wrong or the stored value ?

The stored value also

[Fred]

Please post some code reproducing the issue, or we can't really help

[thanos]

Please post some code reproducing the issue, or we can't really help

Code: Select all

EnableExplicit
UseSQLiteDatabase()
UseMySQLDatabase()


#SQLITE_DB = 10
#MYSQL_DB  = 11


Structure dbColumn
  ColumnIndex.i
  ColumnName.s
  ColumnType.i
  ColumnSize.i
EndStructure



Macro FixApostrophe(Text)
  ReplaceString(ReplaceString(Text, "'", "''"), "\", "\\")
EndMacro


Procedure CheckDatabaseUpdate(Database, Query$)
  Protected Result = DatabaseUpdate(Database, Query$)
  If Result = 0
    Debug DatabaseError()
    Debug Query$
  EndIf
  
  ProcedureReturn Result
EndProcedure


Procedure MapDatabaseFields(hDB, Map dbFields.dbColumn())
  Protected TotalCols
  Protected CurrField
  Protected sFldName.s
  
  TotalCols = (DatabaseColumns(hDB) - 1)
  For CurrField = 0 To TotalCols
    sFldName = UCase(DatabaseColumnName(hDB, CurrField))
    AddMapElement(dbFields(), sFldName)
    dbFields()\ColumnIndex = CurrField
    dbFields()\ColumnName  = sFldName
    dbFields()\ColumnType  = DatabaseColumnType(hDB, CurrField)
    dbFields()\ColumnSize  = DatabaseColumnSize(hDB, CurrField)
  Next
EndProcedure


Procedure.s FixNumbers(Text.s, FieldNo, Map dbFields.dbColumn())
  Protected Result$ = Text
  
  ForEach dbFields()
    With dbFields()
      If (\ColumnIndex = FieldNo)
        Select \ColumnType
          Case #PB_Database_Long, #PB_Database_Float, #PB_Database_Double, #PB_Database_Quad
            If (Len(Text) = 0)
              Result$ = "0"
            EndIf
          Default
            Result$ = Text
        EndSelect
        Break 
      EndIf
    EndWith
  Next
  
  ProcedureReturn Result$
EndProcedure




Procedure MigrateDBFromSQLite2MySQL(SQLiteDB$, MySQLDB$, MySQLUser$, MySQLPwd$, List Tables.s())
  Protected Query$
  Protected Table$
  Protected PreFix$
  Protected Record$
  Protected hFile
  Protected i
  Protected SQLFile$
  Protected Command$
  Protected String$
  Protected IsBLOB = #False
  Protected Result = #False
  ;~ Protected NewList Tables.s()
  Protected NewList Commands.s()
  Protected NewMap dbFields.dbColumn()
  
  Debug ElapsedMilliseconds()
  If (OpenDatabase(#MYSQL_DB,  MySQLDB$, MySQLUser$, MySQLPwd$, #PB_Database_MySQL))
    If (OpenDatabase(#SQLITE_DB,  SQLiteDB$, "", "", #PB_Database_SQLite))
      DatabaseQuery(#MYSQL_DB, "SET NAMES 'utf8'")
      FinishDatabaseQuery(#MYSQL_DB)
      DatabaseQuery(#MYSQL_DB, "SET CHARACTER SET 'utf8'")
      FinishDatabaseQuery(#MYSQL_DB)
      
      If (ListSize(Tables()) = 0)
        If DatabaseQuery(#MYSQL_DB, "SHOW TABLES;")
          
          ;~ Get all tables from database
          While NextDatabaseRow(#MYSQL_DB)
            Table$ = GetDatabaseString(#MYSQL_DB, 0)
            
            AddElement(Tables())
            Tables() = Table$
            
            Debug Table$
          Wend
          FinishDatabaseQuery(#MYSQL_DB)
        EndIf
      EndIf
      
      SQLFile$ = GetPathPart(SQLiteDB$) + "_SQLite2MySQL_" + FormatDate("%yyyy-%mm-%dd-%hh-%ii-%ss", Date()) + ".sql"
      hFile    = CreateFile(#PB_Any, SQLFile$)
      
      ;~ Get all fields and attributes from each table
      ForEach Tables()
        
        ;~ Delete any previous record
        AddElement(Commands())
        Commands() = "DELETE FROM " + Tables() + ";"         
        Debug Tables()
        PreFix$ = "INSERT INTO `" + Tables() + "` ("
        If (DatabaseQuery(#SQLITE_DB, "SELECT * FROM " + Tables() + " LIMIT 1;"))
          
          ;~ Map the table definition
          ClearMap(dbFields())
          MapDatabaseFields(#SQLITE_DB, dbFields())
          
          For i = 0 To (DatabaseColumns(#SQLITE_DB) - 1)
            PreFix$ + "`" + DatabaseColumnName(#SQLITE_DB, i) + "`, "
          Next
          PreFix$ = Mid(PreFix$, 1, Len(PreFix$)-2) + ") VALUES("
          FinishDatabaseQuery(#SQLITE_DB)
          
          ;~ Get all records
          If (DatabaseQuery(#SQLITE_DB, "SELECT * FROM " + Tables() + ";"))
            While NextDatabaseRow(#SQLITE_DB)
              Record$ = ""
              For i = 0 To (DatabaseColumns(#SQLITE_DB) - 1)
                String$ = FixApostrophe(FixNumbers(GetDatabaseString(#SQLITE_DB, i), i, dbFields.dbColumn()))
                Record$ + "'" + String$ + "', "
              Next
              Record$ = Mid(Record$, 1, Len(Record$)-2) + ");"
              
              ;~ Add to commands
              AddElement(Commands())
              Commands() = PreFix$ + Record$
              
              ;~ Debug Commands()
              WriteStringN(hFile, PreFix$ + Record$)
            Wend
            FinishDatabaseQuery(#SQLITE_DB)
            
          EndIf
          
        EndIf
        
      Next
      CloseFile(hFile)
      Debug ElapsedMilliseconds()
      Debug ListSize(Commands())
      
      ;~ Write Data to database
      CheckDatabaseUpdate(#MYSQL_DB, "BEGIN;")
      ;hFile = ReadFile(#PB_Any, SQLFile$)
      ;~ If (hFile)
      ;~   While Eof(hFile) = 0
      ;~    Command$ = ReadString(hFile)
      ;~    CheckDatabaseUpdate(#MYSQL_DB, ReFixEnter(Command$))
      ;~  Wend
      ;~  CloseFile(hFile)
      ;~ EndIf
      ForEach Commands()
        CheckDatabaseUpdate(#MYSQL_DB, Commands())
      Next
      CheckDatabaseUpdate(#MYSQL_DB, "COMMIT;")
      
      CloseDatabase(#SQLITE_DB)
      CloseDatabase(#MYSQL_DB)
      
    EndIf
  EndIf
EndProcedure

Define NewList Tables.s()
AddElement(Tables()): Tables() = "Invoices"
 MigrateDBFromSQLite2MySQL("thanos.db", "host=localhost port=3306 dbname='test'", "root", "", Tables())

[thanos]

Please post some code reproducing the issue, or we can't really help

Fred , do you need the sqlite database?

[tored]

This works for me

MySQL 8.0.32 with defaults, using SET NAMES utf8 or not does not matter for me, probably because defaults.

test.txt

Code: Select all

$EPWNYMIA|ΓΡΙΒΑΣ ΒΑΣΙΛΕΙΟΣ»

test.pb

Code: Select all

EnableExplicit

UseSQLiteDatabase()
UseMySQLDatabase()

#DB_FILE = "test.db"
#DATA_FILE = "test.txt"
#MYSQL_USER = "user"
#MYSQL_PASS = "pass"
#MYSQL_DB = "test"

#INSERT_INTO = "INSERT INTO test (data) VALUES (?)"  
#SELECT_QUERY = "SELECT data FROM test ORDER BY id DESC LIMIT 1"

Define file, sqlite, init = #False, *buf, len, mysql, string.s

If Bool(FileSize(#DB_FILE) >= 0)
;   DeleteFile(#DB_FILE)
EndIf  

If Not Bool(FileSize(#DB_FILE) >= 0)
  file = CreateFile(#PB_Any, #DB_FILE)
  If Not file
    Debug "Failed creating dbs file"
    End 1
  EndIf
  CloseFile(file)
  init = #True  
EndIf

file = ReadFile(#PB_Any, #DATA_FILE, #PB_File_SharedRead|#PB_UTF8)
If Not file
  Debug "Failed opening data file"
  End 1
EndIf
len = Lof(file)
*buf = AllocateMemory(len)
If Not *buf
  Debug "Failed allocating memory"
  Gosub free
  End 1
EndIf
ReadData(file, *buf, len)
CloseFile(file)

Debug "data    " + PeekS(*buf, len, #PB_UTF8)

sqlite = OpenDatabase(#PB_Any, #DB_FILE , #Null$, #Null$, #PB_Database_SQLite)
If Not sqlite
  Debug "Failed opening sqlite " + DatabaseError()
  End 1
EndIf

If init
  If Not DatabaseUpdate(sqlite, "CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, data BLOB NOT NULL)")
    Debug "Failed creating table " + DatabaseError()
    Gosub free
    End 1
  EndIf 
  
  SetDatabaseBlob(sqlite, 0, *buf, len)
  If Not DatabaseUpdate(sqlite, #INSERT_INTO)
    Debug "Failed inserting test data " + DatabaseError()
    Gosub free
    End 1
  EndIf
EndIf  

If Not DatabaseQuery(sqlite, #SELECT_QUERY)
  Debug "Failed selecting data " + DatabaseError()
  Gosub free
  End 1
EndIf  

NextDatabaseRow(sqlite)
string =  GetDatabaseString(sqlite, 0)
Debug "sqlite  " + string


mysql = OpenDatabase(#PB_Any, "host=localhost port=3306 dbname=" + #MYSQL_DB, #MYSQL_USER, #MYSQL_PASS, #PB_Database_MySQL)
If Not mysql
  Debug "Failed connecting to mysql"
  Gosub free
  End 1
EndIf
If Not DatabaseUpdate(mysql, "SET NAMES 'utf8'")
  Debug "Failed setting names " + DatabaseError()
  Gosub free
  End 1
EndIf  


If Not DatabaseUpdate(mysql, "CREATE TABLE IF NOT EXISTS test (id INT PRIMARY KEY AUTO_INCREMENT, data BLOB)")
  Debug "Failed creating table " + DatabaseError()
  Gosub free
  End 1
EndIf  

SetDatabaseString(mysql, 0, string)
If Not DatabaseUpdate(mysql, #INSERT_INTO)
  Debug "Failed inserting table " + DatabaseError()
  Gosub free
  End 1
EndIf  

If Not DatabaseQuery(mysql, #SELECT_QUERY)
  Debug "Failed selecting data " + DatabaseError()
  Gosub free
  End 1
EndIf  

NextDatabaseRow(mysql)
string = GetDatabaseString(mysql, 0)
Debug "mysql " + string

Gosub free
End 0

free:
If *buf
  FreeMemory(*buf)
EndIf
If sqlite
  CloseDatabase(sqlite)
EndIf  
If mysql
  CloseDatabase(mysql)
EndIf  
Return

[thanos]

This works for me

MySQL 8.0.32 with defaults, using SET NAMES utf8 or not does not matter for me, probably because defaults.

test.txt

Code: Select all

$EPWNYMIA|ΓΡΙΒΑΣ ΒΑΣΙΛΕΙΟΣ»

test.pb

Code: Select all

EnableExplicit

UseSQLiteDatabase()
UseMySQLDatabase()

#DB_FILE = "test.db"
#DATA_FILE = "test.txt"
#MYSQL_USER = "user"
#MYSQL_PASS = "pass"
#MYSQL_DB = "test"

#INSERT_INTO = "INSERT INTO test (data) VALUES (?)"  
#SELECT_QUERY = "SELECT data FROM test ORDER BY id DESC LIMIT 1"

Define file, sqlite, init = #False, *buf, len, mysql, string.s

If Bool(FileSize(#DB_FILE) >= 0)
;   DeleteFile(#DB_FILE)
EndIf  

If Not Bool(FileSize(#DB_FILE) >= 0)
  file = CreateFile(#PB_Any, #DB_FILE)
  If Not file
    Debug "Failed creating dbs file"
    End 1
  EndIf
  CloseFile(file)
  init = #True  
EndIf

file = ReadFile(#PB_Any, #DATA_FILE, #PB_File_SharedRead|#PB_UTF8)
If Not file
  Debug "Failed opening data file"
  End 1
EndIf
len = Lof(file)
*buf = AllocateMemory(len)
If Not *buf
  Debug "Failed allocating memory"
  Gosub free
  End 1
EndIf
ReadData(file, *buf, len)
CloseFile(file)

Debug "data    " + PeekS(*buf, len, #PB_UTF8)

sqlite = OpenDatabase(#PB_Any, #DB_FILE , #Null$, #Null$, #PB_Database_SQLite)
If Not sqlite
  Debug "Failed opening sqlite " + DatabaseError()
  End 1
EndIf

If init
  If Not DatabaseUpdate(sqlite, "CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, data BLOB NOT NULL)")
    Debug "Failed creating table " + DatabaseError()
    Gosub free
    End 1
  EndIf 
  
  SetDatabaseBlob(sqlite, 0, *buf, len)
  If Not DatabaseUpdate(sqlite, #INSERT_INTO)
    Debug "Failed inserting test data " + DatabaseError()
    Gosub free
    End 1
  EndIf
EndIf  

If Not DatabaseQuery(sqlite, #SELECT_QUERY)
  Debug "Failed selecting data " + DatabaseError()
  Gosub free
  End 1
EndIf  

NextDatabaseRow(sqlite)
string =  GetDatabaseString(sqlite, 0)
Debug "sqlite  " + string


mysql = OpenDatabase(#PB_Any, "host=localhost port=3306 dbname=" + #MYSQL_DB, #MYSQL_USER, #MYSQL_PASS, #PB_Database_MySQL)
If Not mysql
  Debug "Failed connecting to mysql"
  Gosub free
  End 1
EndIf
If Not DatabaseUpdate(mysql, "SET NAMES 'utf8'")
  Debug "Failed setting names " + DatabaseError()
  Gosub free
  End 1
EndIf  


If Not DatabaseUpdate(mysql, "CREATE TABLE IF NOT EXISTS test (id INT PRIMARY KEY AUTO_INCREMENT, data BLOB)")
  Debug "Failed creating table " + DatabaseError()
  Gosub free
  End 1
EndIf  

SetDatabaseString(mysql, 0, string)
If Not DatabaseUpdate(mysql, #INSERT_INTO)
  Debug "Failed inserting table " + DatabaseError()
  Gosub free
  End 1
EndIf  

If Not DatabaseQuery(mysql, #SELECT_QUERY)
  Debug "Failed selecting data " + DatabaseError()
  Gosub free
  End 1
EndIf  

NextDatabaseRow(mysql)
string = GetDatabaseString(mysql, 0)
Debug "mysql " + string

Gosub free
End 0

free:
If *buf
  FreeMemory(*buf)
EndIf
If sqlite
  CloseDatabase(sqlite)
EndIf  
If mysql
  CloseDatabase(mysql)
EndIf  
Return

The most blob fields includes large texts and I passed them as strings.
I did not see the need to convert them to binary.
I will check your solution.
Thank you very much.