[glom/mysql: 3/21] More MySQL

[Murray Cumming <murrayc src gnome org>]

commit eb87f15e414baf268a5cc884d5b118fac140ac26
Author: Murray Cumming <murrayc murrayc com>
Date:   Thu Jan 3 14:34:59 2013 +0100

    More MySQL

 glom/libglom/connectionpool_backends/mysql.cc      |   21 +++--
 glom/libglom/connectionpool_backends/mysql_self.cc |  102 +++++++++++++++++---
 glom/libglom/connectionpool_backends/mysql_self.h  |    2 +-
 glom/libglom/connectionpool_backends/postgres.cc   |    3 +-
 glom/mode_design/users/dialog_users_list.cc        |    2 +
 5 files changed, 106 insertions(+), 24 deletions(-)
---
diff --git a/glom/libglom/connectionpool_backends/mysql.cc b/glom/libglom/connectionpool_backends/mysql.cc
index 724b824..a92b589 100644
--- a/glom/libglom/connectionpool_backends/mysql.cc
+++ b/glom/libglom/connectionpool_backends/mysql.cc
@@ -37,7 +37,7 @@
 #include <iostream>
 
 // Uncomment to see debug messages
-//#define GLOM_CONNECTION_DEBUG
+#define GLOM_CONNECTION_DEBUG
 
 namespace
 {
@@ -47,8 +47,16 @@ static Glib::ustring create_auth_string(const Glib::ustring& username, const Gli
   if(username.empty() and password.empty())
     return Glib::ustring();
   else
-    return "USERNAME=" + Glom::DbUtils::gda_cnc_string_encode(username) + 
-      ";PASSWORD=" + Glom::DbUtils::gda_cnc_string_encode(password);
+  {
+    Glib::ustring result = "USERNAME=" + Glom::DbUtils::gda_cnc_string_encode(username);
+
+    if(!password.empty()) //There is no password initially, at least with MySQL 5.5:
+    {
+      result +=";PASSWORD=" + Glom::DbUtils::gda_cnc_string_encode(password);
+    }
+
+    return result;
+  }
 }
 
 } //anonymous namespace
@@ -65,15 +73,14 @@ MySQL::MySQL()
 {
 }
 
+//TODO: Avoid copy/paste with Postgres::attempt_connect()
 Glib::RefPtr<Gnome::Gda::Connection> MySQL::attempt_connect(const Glib::ustring& port, const Glib::ustring& database, const Glib::ustring& username, const Glib::ustring& password, bool fake_connection)
 {
-  //We must specify _some_ database even when we just want to create a database.
-  //This _might_ be different on some systems. I hope not. murrayc
-  const Glib::ustring default_database = "template1";
+  const Glib::ustring default_database = "INFORMATION_SCHEMA";
   //const Glib::ustring& actual_database = (!database.empty()) ? database : default_database;;
   const Glib::ustring cnc_string_main = "HOST=" + DbUtils::gda_cnc_string_encode(m_host)
    + ";PORT=" + DbUtils::gda_cnc_string_encode(port);
-  const Glib::ustring cnc_string = cnc_string_main + ";DB_NAME=" + DbUtils::gda_cnc_string_encode(database);
+  const Glib::ustring cnc_string = cnc_string_main +";DB_NAME=" + DbUtils::gda_cnc_string_encode(database);
 
   Glib::RefPtr<Gnome::Gda::Connection> connection;
   Glib::RefPtr<Gnome::Gda::DataModel> data_model;
diff --git a/glom/libglom/connectionpool_backends/mysql_self.cc b/glom/libglom/connectionpool_backends/mysql_self.cc
index 3d910b1..42079fe 100644
--- a/glom/libglom/connectionpool_backends/mysql_self.cc
+++ b/glom/libglom/connectionpool_backends/mysql_self.cc
@@ -180,19 +180,41 @@ Backend::InitErrors MySQLSelfHosted::initialize(const SlotProgress& slot_progres
     + " --datadir=" + Glib::shell_quote(dbdir_data);
     //TODO: + " --random-passwords";
   std::cout << "debug: command_initdb=" << command_initdb << std::endl;
-  bool result = Glom::Spawn::execute_command_line_and_wait(command_initdb, slot_progress);
+  const bool result = Glom::Spawn::execute_command_line_and_wait(command_initdb, slot_progress);
   if(!result)
   {
     std::cerr << "Error while attempting to create self-hosting MySQL database." << std::endl;
   }
   else
   {
+    std::cout << "debug: command_initdb succeeded" << ", this=" << this << std::endl;
+  
     //This is used during the first start:
     m_initial_password_to_set = password;
+    m_initial_username_to_set = initial_username;
 
     //TODO: With MySQL 5.6, use the new --random-passwords option (see above)
     m_temporary_password = "";
     m_temporary_password_active = true;
+
+    //Startup (and shutdown) so we can set the initial username and password.
+    //TODO: This is inefficient, because the caller probably wants to start the server soon anyway,
+    //but that might be in a different instance of this backend,
+    //and we cannot take the risk of leaving the database with a default password.
+    if(startup(slot_progress, false) != STARTUPERROR_NONE)
+    {
+      std::cerr << "Error while attempting to create self-hosting MySQL database, while starting for the first time, to set the initial username and password." << std::endl;
+      return INITERROR_OTHER;
+    }
+    else
+    {
+      if(cleanup(slot_progress) != STARTUPERROR_NONE)
+      {
+        std::cerr << "Error while attempting to create self-hosting MySQL database, while shutting down, after setting the initial username and password." << std::endl;
+        return INITERROR_OTHER;
+      }
+    }
+
     //Get the temporary random password,
     //which will be used when first starting the server.
     /*
@@ -229,8 +251,15 @@ float MySQLSelfHosted::get_mysqlql_utils_version_as_number(const SlotProgress& /
   return 0; //TODO
 }
 
+static Glib::ustring build_query_change_username(const Glib::ustring& old_username, const Glib::ustring& new_username)
+{
+  return "RENAME USER " + DbUtils::escape_sql_id(old_username) + " TO " + DbUtils::escape_sql_id(new_username);
+}
+
 Backend::StartupErrors MySQLSelfHosted::startup(const SlotProgress& slot_progress, bool network_shared)
 {
+  std::cout << G_STRFUNC << std::cout << "  DEBUG0: m_temporary_password_active=" << m_temporary_password_active << std::endl;
+
   m_network_shared = network_shared;
 
   // Don't risk random crashes, although this really shouldn't be called
@@ -299,15 +328,16 @@ Backend::StartupErrors MySQLSelfHosted::startup(const SlotProgress& slot_progres
                                   + " --datadir=" + Glib::shell_quote(dbdir_data)
                                   + " --socket=" + Glib::shell_quote(dbdir_socket)
                                   + " --pid-file=" + Glib::shell_quote(dbdir_pid);
-  std::cout << G_STRFUNC << ": debug: command_mysql_start=" << command_mysql_start << std::endl;
+  //std::cout << G_STRFUNC << ": debug: command_mysql_start=" << command_mysql_start << std::endl;
 
   m_port = available_port; //Needed by get_mysqladmin_command().
   const std::string command_check_mysql_has_started = get_mysqladmin_command(m_temporary_password) //TODO: Get the temporary password in a callback.
     + " ping";
   const std::string second_command_success_text = "mysqld is alive"; //TODO: This is not a stable API. Also, watch out for localisation.
-  std::cout << G_STRFUNC << ": debug: command_check_mysql_has_started=" << command_check_mysql_has_started << std::endl;
+  //std::cout << G_STRFUNC << ": debug: command_check_mysql_has_started=" << command_check_mysql_has_started << std::endl;
 
   const bool result = Glom::Spawn::execute_command_line_and_wait_until_second_command_returns_success(command_mysql_start, command_check_mysql_has_started, slot_progress, second_command_success_text);
+  std::cout << G_STRFUNC << std::cout << "  DEBUG: started" << std::endl;
 
   if(!result)
   {
@@ -319,14 +349,17 @@ Backend::StartupErrors MySQLSelfHosted::startup(const SlotProgress& slot_progres
 
   m_port = available_port; //Remember it for later.
 
-  //If necessary, set the initial password:
+  //If necessary, set the initial root password and rename the root user:
   if(m_temporary_password_active)
   {
-    const std::string command_initdb_initial_password = get_mysqladmin_command(m_temporary_password)
-      + " password" + Glib::shell_quote(m_initial_password_to_set);
-    std::cout << "debug: command_initdb_initial_password=" << command_initdb_initial_password << std::endl;
+    //Set the root password:
+    const std::string command_initdb_set_initial_password = get_mysqladmin_command(m_temporary_password)
+      + " password " + Glib::shell_quote(m_initial_password_to_set);
+    std::cout << "debug: command_initdb_set_initial_password=" << command_initdb_set_initial_password << std::endl;
+
+    const bool result = Glom::Spawn::execute_command_line_and_wait(command_initdb_set_initial_password, slot_progress);
 
-    const bool result = Glom::Spawn::execute_command_line_and_wait(command_initdb_initial_password, slot_progress);
+    std::cout << G_STRFUNC << std::cout << "  DEBUG4" << std::endl;
 
     if(!result)
     {
@@ -337,12 +370,44 @@ Backend::StartupErrors MySQLSelfHosted::startup(const SlotProgress& slot_progres
     m_temporary_password_active = false;
     m_temporary_password.clear();
 
-    //TODO_MySQL: " -U " + initial_username
+  std::cout << G_STRFUNC << std::cout << "  DEBUG5" << std::endl;
+
+
+    //Rename the root user,
+    //so we can connnect as the expected username:
+    //We connect to the INFORMATION_SCHEMA database, because libgda needs us to specify some database.
+    Glib::RefPtr<Gnome::Gda::Connection> gda_connection = connect("INFORMATION_SCHEMA", "root", m_initial_password_to_set);
+    if(!gda_connection)
+    {
+      std::cerr << G_STRFUNC << "Error while attempting to start self-hosting MySQL database, when setting the initial username: connection failed." << std::endl;
+      return STARTUPERROR_FAILED_UNKNOWN_REASON;
+    }
+
+    std::cout << G_STRFUNC << std::cout << "  DEBUG6" << std::endl;
+
+    const std::string query = build_query_change_username("root", m_initial_username_to_set);
+    try
+    {
+      const bool test = gda_connection->statement_execute_non_select(query);
+      if(!test)
+      {
+        std::cerr << G_STRFUNC << "Error while attempting to start self-hosting MySQL database, when setting the initial username: UPDATE failed." << std::endl;
+       return STARTUPERROR_FAILED_UNKNOWN_REASON;
+      }
+    }
+    catch(const Glib::Error& ex)
+    {
+      std::cerr << G_STRFUNC  << "Error while attempting to start self-hosting MySQL database, when setting the initial username: UPDATE failed: " << ex.what() << std::endl;
+      return STARTUPERROR_FAILED_UNKNOWN_REASON;
+    }
   }
 
+  m_initial_username_to_set.clear();
+
   return STARTUPERROR_NONE;
 }
 
+//TODO: Avoid copy/paste with PostgresSelfHosted:
 void MySQLSelfHosted::show_active_connections()
 {
   Glib::RefPtr<Gnome::Gda::SqlBuilder> builder =
@@ -385,11 +450,17 @@ std::string MySQLSelfHosted::get_mysqladmin_command(const Glib::ustring& passwor
 {
   const std::string port_as_text = Glib::Ascii::dtostr(m_port);
 
-  return get_path_to_mysql_executable("mysqladmin")
+  std::string command = get_path_to_mysql_executable("mysqladmin")
     + " --no-defaults"
     + " --port=" + port_as_text
-    + " --user=root"
-    + " --password=" + Glib::shell_quote(password);
+    + " --protocol=tcp" //Otherwise we cannot connect as root. TODO: However, maybe we could use --skip-networking if network sharing is not enabled.
+    + " --user=root";
+
+  //--password='' is not always interpreted the same as specifying no --password.
+  if(!password.empty())
+    command += " --password=" + Glib::shell_quote(password);
+
+  return command;
 }
 
 bool MySQLSelfHosted::cleanup(const SlotProgress& slot_progress)
@@ -473,13 +544,14 @@ Glib::RefPtr<Gnome::Gda::Connection> MySQLSelfHosted::connect(const Glib::ustrin
 
   Glib::RefPtr<Gnome::Gda::Connection> result;
   bool keep_trying = true;
-  guint count_retries = 0;
-  const guint MAX_RETRIES_KNOWN_PASSWORD = 30; /* seconds */
-  const guint MAX_RETRIES_EVER = 60; /* seconds */
+  guint count_retries = 1;
+  const guint MAX_RETRIES_KNOWN_PASSWORD = 20;
+  const guint MAX_RETRIES_EVER = 20;
   while(keep_trying)
   {
     try
     {
+      std::cout << "DEBUG: Calling attempt_connect(): username=" << username << ", password=" << password << std::endl;
       result = attempt_connect(port_as_string(m_port), database, username, password, fake_connection);
     }
     catch(const ExceptionConnection& ex)
diff --git a/glom/libglom/connectionpool_backends/mysql_self.h b/glom/libglom/connectionpool_backends/mysql_self.h
index 4216154..ac46f60 100644
--- a/glom/libglom/connectionpool_backends/mysql_self.h
+++ b/glom/libglom/connectionpool_backends/mysql_self.h
@@ -90,7 +90,7 @@ private:
   Glib::ustring m_saved_database_name, m_saved_username, m_saved_password;
 
   bool m_temporary_password_active; //Whether the password is an initial temporary one.
-  Glib::ustring m_initial_password_to_set;
+  Glib::ustring m_initial_password_to_set, m_initial_username_to_set;
   Glib::ustring m_temporary_password;
 };
 
diff --git a/glom/libglom/connectionpool_backends/postgres.cc b/glom/libglom/connectionpool_backends/postgres.cc
index 3fb50aa..519ecff 100644
--- a/glom/libglom/connectionpool_backends/postgres.cc
+++ b/glom/libglom/connectionpool_backends/postgres.cc
@@ -65,6 +65,7 @@ Postgres::Postgres()
 {
 }
 
+//TODO: We need to specify TCP for the connection: https://bugzilla.gnome.org/show_bug.cgi?id=691069
 Glib::RefPtr<Gnome::Gda::Connection> Postgres::attempt_connect(const Glib::ustring& port, const Glib::ustring& database, const Glib::ustring& username, const Glib::ustring& password, bool fake_connection)
 {
   //We must specify _some_ database even when we just want to create a database.
@@ -100,7 +101,7 @@ Glib::RefPtr<Gnome::Gda::Connection> Postgres::attempt_connect(const Glib::ustri
         cnc_string, auth_string,
         Gnome::Gda::CONNECTION_OPTIONS_SQL_IDENTIFIERS_CASE_SENSITIVE);
 
-      connection->statement_execute_non_select("SET DATESTYLE = 'ISO'");
+      //connection->statement_execute_non_select("SET DATESTYLE = 'ISO'");
       data_model = connection->statement_execute_select("SELECT version()");
     }
   }
diff --git a/glom/mode_design/users/dialog_users_list.cc b/glom/mode_design/users/dialog_users_list.cc
index 1eee143..8dada10 100644
--- a/glom/mode_design/users/dialog_users_list.cc
+++ b/glom/mode_design/users/dialog_users_list.cc
@@ -332,6 +332,8 @@ void Dialog_UsersList::on_button_user_edit()
 
       if(!user.empty() && !password.empty())
       {
+        //TODO: Can this change the username too?
+        //Note: If using MySQL, we need MySQL 5.6.7 for ALTER USER:
         const Glib::ustring strQuery = "ALTER USER " + DbUtils::escape_sql_id(user) + " PASSWORD '" + password + "'" ; //TODO: Escape the password.
         const bool test = DbUtils::query_execute_string(strQuery);
         if(!test)