Merge pull request #4100 from koying/fixmysql

Mysql fixes (fixes #14883)

2 files changed, 11 insertions, 7 deletions

diff --git a/xbmc/dbwrappers/mysqldataset.cpp b/xbmc/dbwrappers/mysqldataset.cpp
index 436f564878..523e621511 100644
--- a/xbmc/dbwrappers/mysqldataset.cpp
+++ b/xbmc/dbwrappers/mysqldataset.cpp
@@ -155,7 +155,7 @@ int MysqlDatabase::connect(bool create_new) {
         char sqlcmd[512];
         int ret;
 
-        sprintf(sqlcmd, "CREATE DATABASE `%s`", db.c_str());
+        sprintf(sqlcmd, "CREATE DATABASE `%s` CHARACTER SET utf8 COLLATE utf8_general_ci", db.c_str());
         if ( (ret=query_with_reconnect(sqlcmd)) != MYSQL_OK )
         {
           throw DbErrors("Can't create new database: '%s' (%d)", db.c_str(), ret);
@@ -250,7 +250,7 @@ int MysqlDatabase::copy(const char *backup_name) {
     }
 
     // create the new database
-    sprintf(sql, "CREATE DATABASE `%s`", backup_name);
+    sprintf(sql, "CREATE DATABASE `%s` CHARACTER SET utf8 COLLATE utf8_general_ci", backup_name);
     if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
     {
       mysql_free_result(res);
@@ -1375,7 +1375,8 @@ int MysqlDataset::exec(const string &sql) {
   }
 
   // force the charset and collation to UTF-8
-  if ( ci_find(qry, "CREATE TABLE") != string::npos )
+  if ( ci_find(qry, "CREATE TABLE") != string::npos 
+    || ci_find(qry, "CREATE TEMPORARY TABLE") != string::npos )
   {
     qry += " CHARACTER SET utf8 COLLATE utf8_general_ci";
   }
diff --git a/xbmc/music/MusicDatabase.cpp b/xbmc/music/MusicDatabase.cpp
index e144554b06..0f14c1ad41 100644
--- a/xbmc/music/MusicDatabase.cpp
+++ b/xbmc/music/MusicDatabase.cpp
@@ -2498,10 +2498,13 @@ bool CMusicDatabase::CleanupArtists()
     // (nested queries by Bobbin007)
     // must be executed AFTER the song, album and their artist link tables are cleaned.
     // don't delete the "Various Artists" string
-    CStdString strSQL = "delete from artist where idArtist not in (select idArtist from song_artist)";
-    strSQL += " and idArtist not in (select idArtist from album_artist)";
-    CStdString strSQL2;
-    m_pDS->exec(strSQL.c_str());
+
+    // Create temp table to avoid 1442 trigger hell on mysql
+    m_pDS->exec("CREATE TEMPORARY TABLE tmp_delartists (idArtist integer)");
+    m_pDS->exec("INSERT INTO tmp_delartists select idArtist from song_artist");
+    m_pDS->exec("INSERT INTO tmp_delartists select idArtist from album_artist");
+    m_pDS->exec("delete from artist where idArtist not in (select idArtist from tmp_delartists)");
+    m_pDS->exec("DROP TABLE tmp_delartists");
     return true;
   }
   catch (...)