Merge lp:~perconardba/mydumper/0.5.2_lesslocking into lp:mydumper/0.5

[Max Bubenick]

what we did was to add a new connection (lock_conn) that locks all non-InnoDB tables after getting metadata and then releasing the FTWRL. In this way we can release the locks on InnoDB tables earlier.

[David Busby (d-busby)]

This merge also include patch proposed here: https://bugs.launchpad.net/mydumper/+bug/1125997 for the addition of utc-tz

[lp:~perconardba/mydumper/0.5.2_lesslocking]

110. By Max Bubenick on 2013-06-25

Added .partial to metadata, removed when finished

111. By Max Bubenick on 2013-08-22

no-lock issue fixed

112. By Max Bubenick on 2013-09-04

MyISAM lock tables fail message lvl changed, and better description

113. By Max Bubenick on 2013-09-05

ensure satetement is not bigger than statement_size

114. By Max Bubenick on 2013-10-04

less loccking is optional now, default is disabled

115. By Max Bubenick on 2013-10-07

fix #1124106 at myloader

116. By Max Bubenick on 2013-10-07

documentation added

117. By Max Bubenick on 2013-10-09

bug fix #987344 #1075611

118. By Max Bubenick on 2013-10-16

small fixes

119. By Max Bubenick on 2013-10-21

--success-on-1146 added

Unmerged revisions

119. By Max Bubenick on 2013-10-21

--success-on-1146 added

118. By Max Bubenick on 2013-10-16

small fixes

117. By Max Bubenick on 2013-10-09

bug fix #987344 #1075611

116. By Max Bubenick on 2013-10-07

documentation added

115. By Max Bubenick on 2013-10-07

fix #1124106 at myloader

114. By Max Bubenick on 2013-10-04

less loccking is optional now, default is disabled

113. By Max Bubenick on 2013-09-05

ensure satetement is not bigger than statement_size

112. By Max Bubenick on 2013-09-04

MyISAM lock tables fail message lvl changed, and better description

111. By Max Bubenick on 2013-08-22

no-lock issue fixed

110. By Max Bubenick on 2013-06-25

Added .partial to metadata, removed when finished

=== modified file '.bzrignore'
--- .bzrignore	2011-05-16 12:12:33 +0000
+++ .bzrignore	2013-10-21 13:19:20 +0000
@@ -17,3 +17,4 @@
 docs/man/
 docs/_build/conf.py
 docs/_build/sources.cmake
+.project

=== modified file 'CMakeLists.txt'
--- CMakeLists.txt	2012-11-19 17:38:52 +0000
+++ CMakeLists.txt	2013-10-21 13:19:20 +0000
@@ -1,6 +1,6 @@
 cmake_minimum_required(VERSION 2.6)
 project(mydumper)
-set(VERSION 0.5.2)
+set(VERSION 0.5.3)
 set(ARCHIVE_NAME "${CMAKE_PROJECT_NAME}-${VERSION}")
 
 #Required packages

=== modified file 'docs/files.rst'
--- docs/files.rst	2011-06-14 09:17:12 +0000
+++ docs/files.rst	2013-10-21 13:19:20 +0000
@@ -6,8 +6,9 @@
 
 Metadata
 --------
-When a dump is executed a file called ``.metadata`` is created in the output
-directory.  This contains the start and end time of the dump as well as the
+When a dump is executed a file called ``metadata.partial`` is created in the output
+directory and is renamed to ``metadata`` when mydumper finish without error.  
+This contains the start and end time of the dump as well as the
 master binary log positions if applicable.
 
 This is an example of the content of this file::

=== modified file 'docs/mydumper_usage.rst'
--- docs/mydumper_usage.rst	2011-06-20 06:32:35 +0000
+++ docs/mydumper_usage.rst	2013-10-21 13:19:20 +0000
@@ -151,3 +151,21 @@
    .. warning::
       
       This will cause inconsistent backups.
+
+.. option:: --[skip-]tz-utc
+
+   SET TIME_ZONE='+00:00' at top of dump to allow dumping of TIMESTAMP data 
+   when a server has data in different time zones or data is being moved 
+   between servers with different time zones, defaults to on use --skip-tz-utc 
+   to disable.
+
+.. option:: --less-locking
+
+   Minimize locking time on InnoDB tables grabbing a LOCK TABLE ... READ 
+   on all non-innodb tables.
+
+   .. warning::
+
+      Do not use this option if you are UPDATING or DELETING MyISAM or Memory 
+      tables. This could cause mutual locks between mydumper and application 
+      threads. 

=== modified file 'mydumper.c'
--- mydumper.c	2012-10-20 17:40:00 +0000
+++ mydumper.c	2013-10-21 13:19:20 +0000
@@ -53,7 +53,7 @@
 guint rows_per_file= 0;
 int longquery= 60;
 int build_empty_files= 0;
-
+int skip_tz= 0;
 int need_dummy_read= 0;
 int compress_output= 0;
 int killqueries= 0;
@@ -76,9 +76,12 @@
 
 gboolean no_schemas= FALSE;
 gboolean no_locks= FALSE;
+gboolean less_locking = FALSE;
+gboolean success_on_1146 = FALSE;
 
 GList *innodb_tables= NULL;
 GList *non_innodb_table= NULL;
+GList *non_innodb_table_block= NULL;
 GList *table_schemas= NULL;
 gint non_innodb_table_counter= 0;
 gint non_innodb_done= 0;
@@ -105,12 +108,16 @@
 	{ "ignore-engines", 'i', 0, G_OPTION_ARG_STRING, &ignore_engines, "Comma delimited list of storage engines to ignore", NULL },
 	{ "no-schemas", 'm', 0, G_OPTION_ARG_NONE, &no_schemas, "Do not dump table schemas with the data", NULL },
 	{ "no-locks", 'k', 0, G_OPTION_ARG_NONE, &no_locks, "Do not execute the temporary shared read lock.  WARNING: This will cause inconsistent backups", NULL },
+	{ "less-locking", 0, 0, G_OPTION_ARG_NONE, &less_locking, "Minimize locking time on InnoDB tables, WARNING: be carefull, please READ documentation first", NULL},
 	{ "long-query-guard", 'l', 0, G_OPTION_ARG_INT, &longquery, "Set long query timer in seconds, default 60", NULL },
 	{ "kill-long-queries", 'k', 0, G_OPTION_ARG_NONE, &killqueries, "Kill long running queries (instead of aborting)", NULL },
 	{ "binlogs", 'b', 0, G_OPTION_ARG_NONE, &need_binlogs, "Get a snapshot of the binary logs as well as dump data",  NULL },
 	{ "daemon", 'D', 0, G_OPTION_ARG_NONE, &daemon_mode, "Enable daemon mode", NULL },
 	{ "snapshot-interval", 'I', 0, G_OPTION_ARG_INT, &snapshot_interval, "Interval between each dump snapshot (in minutes), requires --daemon, default 60", NULL },
 	{ "logfile", 'L', 0, G_OPTION_ARG_FILENAME, &logfile, "Log file name to use, by default stdout is used", NULL },
+	{ "tz-utc", 0, 0, G_OPTION_ARG_NONE, NULL, "SET TIME_ZONE='+00:00' at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones, defaults to on use --skip-tz-utc to disable.", NULL },
+	{ "skip-tz-utc", 0, 0, G_OPTION_ARG_NONE, &skip_tz, "", NULL },
+	{ "success-on-1146", 0, 0, G_OPTION_ARG_NONE, &success_on_1146, "Not increment error count and Warning instead of Critical in case of table doesn't exist", NULL},
 	{ NULL, 0, 0, G_OPTION_ARG_NONE,   NULL, NULL, NULL }
 };
 
@@ -130,7 +137,7 @@
 void no_log(const gchar *log_domain, GLogLevelFlags log_level, const gchar *message, gpointer user_data);
 void set_verbose(guint verbosity);
 MYSQL *reconnect_for_binlog(MYSQL *thrconn);
-void start_dump(MYSQL *conn);
+void start_dump(MYSQL *conn, MYSQL *lock_conn);
 MYSQL *create_main_connection();
 void *binlog_thread(void *data);
 void *exec_thread(void *data);
@@ -330,6 +337,10 @@
 		g_critical("Failed to start consistent snapshot: %s",mysql_error(thrconn));
 		errors++;
 	}
+	if(!skip_tz && mysql_query(thrconn, "/*!40103 SET TIME_ZONE='+00:00' */")){
+		g_critical("Failed to set time zone: %s",mysql_error(thrconn));
+	}
+
 	/* Unfortunately version before 4.1.8 did not support consistent snapshot transaction starts, so we cheat */
 	if (need_dummy_read) {
 		mysql_query(thrconn,"SELECT /*!40001 SQL_NO_CACHE */ * FROM mysql.mydumperdummy");
@@ -553,8 +564,10 @@
 		g_source_remove(sigsource);
 	} else {
 		MYSQL *conn= create_main_connection();
-		start_dump(conn);
+		MYSQL *lock_conn= create_main_connection();
+		start_dump(conn, lock_conn);
 		mysql_close(conn);
+		mysql_close(lock_conn);
 	}
 
 	sleep(5);
@@ -612,8 +625,10 @@
 		g_async_queue_pop(start_scheduled_dump);
 		clear_dump_directory();
 		MYSQL *conn= create_main_connection();
-		start_dump(conn);
+		MYSQL *lock_conn= create_main_connection();
+		start_dump(conn,lock_conn);
 		mysql_close(conn);
+		mysql_close(lock_conn);
 		mysql_thread_end();
 
 		// Don't switch the symlink on shutdown because the dump is probably incomplete.
@@ -671,19 +686,23 @@
 	return NULL;
 }
 
-void start_dump(MYSQL *conn)
+void start_dump(MYSQL *conn, MYSQL *lock_conn)
 {
 	struct configuration conf = { 1, NULL, NULL, NULL, NULL, 0 };
 	char *p;
+	char *p2;
+	char *p3;
 	time_t t;
+	struct db_table *dbt;
+	int main_lock = 1;
 
 	if (daemon_mode)
-		p= g_strdup_printf("%s/%d/metadata", output_directory, dump_number);
+		p= g_strdup_printf("%s/%d/metadata.partial", output_directory, dump_number);
 	else
-		p= g_strdup_printf("%s/metadata", output_directory);
+		p= g_strdup_printf("%s/metadata.partial", output_directory);
+	p2 = g_strndup(p, (unsigned)strlen(p)-8);
 
 	FILE* mdfile=g_fopen(p,"w");
-	g_free(p);
 	if(!mdfile) {
 		g_critical("Couldn't write metadata file (%d)",errno);
 		exit(EXIT_FAILURE);
@@ -718,11 +737,11 @@
 				continue;
 			if (row[tcol] && atoi(row[tcol])>longquery) {
 				if (killqueries) {
-					if (mysql_query(conn,p=g_strdup_printf("KILL %lu",atol(row[icol]))))
+					if (mysql_query(conn,p3=g_strdup_printf("KILL %lu",atol(row[icol]))))
 						g_warning("Could not KILL slow query: %s",mysql_error(conn));
 					else
 						g_warning("Killed a query that was running for %ss",row[tcol]);
-					g_free(p);
+					g_free(p3);
 				} else {
 					g_critical("There are queries in PROCESSLIST running longer than %us, aborting dump,\n\t"
 						"use --long-query-guard to change the guard value, kill queries (--kill-long-queries) or use \n\tdifferent server for dump", longquery);
@@ -800,9 +819,43 @@
 		mysql_free_result(databases);
 
 	}
-	struct db_table *dbt;
-	if (!non_innodb_table) {
-		g_async_queue_push(conf.unlock_tables, GINT_TO_POINTER(1));
+
+	if (!no_locks && less_locking) {
+		GString *query = g_string_sized_new(1024);
+		gchar *dt = NULL;
+
+		if (!non_innodb_table) {
+			g_async_queue_push(conf.unlock_tables, GINT_TO_POINTER(1));
+		}else{
+			int first = 1;
+			/* Create a read lock for non_innodb_table */
+			non_innodb_table_block = non_innodb_table;
+			for (non_innodb_table_block= g_list_first(non_innodb_table_block); non_innodb_table_block; non_innodb_table_block= g_list_next(non_innodb_table_block)) {
+				dbt= (struct db_table*) non_innodb_table_block->data;
+				if(!(strcasecmp(dbt->database,"mysql") == 0 && (strcasecmp(dbt->table,"general_log") ==0 || strcasecmp(dbt->table,"slow_log") ==0 ))){
+					dt = g_strjoin(".",dbt->database,dbt->table,NULL);
+					if(first){
+						g_string_printf(query, "LOCK TABLES ");
+						first = 0;
+					}else{
+						g_string_append(query, ", ");
+					}
+					g_string_append(query, dt);
+					g_string_append(query, " READ");
+				}
+			}
+			g_free(dt);
+			
+			/* if the LT fails do not realease the FTWRL */
+			if(mysql_query(lock_conn, query->str)){
+				g_warning("MyISAM lock tables fail: %s", mysql_error(lock_conn));
+			}else{
+				g_message("Unlocking Innodb tables");
+				mysql_query(conn, "UNLOCK TABLES");
+				main_lock = 0;
+			}
+		}
+		g_string_free(query, TRUE);
 	}
 
 	for (non_innodb_table= g_list_first(non_innodb_table); non_innodb_table; non_innodb_table= g_list_next(non_innodb_table)) {
@@ -833,17 +886,20 @@
 		get_binlogs(conn, &conf);
 	}
 
-
 	for (n=0; n<num_threads; n++) {
 		struct job *j = g_new0(struct job,1);
 		j->type = JOB_SHUTDOWN;
 		g_async_queue_push(conf.queue,j);
 	}
 
-	g_async_queue_pop(conf.unlock_tables);
 	if (!no_locks) {
-		g_message("Non-InnoDB dump complete, unlocking tables");
-		mysql_query(conn, "UNLOCK TABLES");
+		g_async_queue_pop(conf.unlock_tables);
+		if(less_locking) {
+			mysql_query(lock_conn, "UNLOCK TABLES /* Non Innodb */");
+			g_message("Non-InnoDB dump complete, unlocking tables");
+		}
+		if(main_lock)
+			mysql_query(conn, "UNLOCK TABLES");
 	}
 
 	for (n=0; n<num_threads; n++) {
@@ -856,6 +912,9 @@
 		tval.tm_year+1900, tval.tm_mon+1, tval.tm_mday,
 		tval.tm_hour, tval.tm_min, tval.tm_sec);
 	fclose(mdfile);
+	g_rename(p, p2);
+	g_free(p);
+	g_free(p2);
 	g_message("Finished dump at: %04d-%02d-%02d %02d:%02d:%02d\n",
 		tval.tm_year+1900, tval.tm_mon+1, tval.tm_mday,
 		tval.tm_hour, tval.tm_min, tval.tm_sec);
@@ -954,8 +1013,10 @@
 			estimated_step = (nmax-nmin)/estimated_chunks+1;
 			cutoff = nmin;
 			while(cutoff<=nmax) {
-				chunks=g_list_append(chunks,g_strdup_printf("%s%s(`%s` >= %llu AND `%s` < %llu)",
+				chunks=g_list_append(chunks,g_strdup_printf("%s%s%s%s(`%s` >= %llu AND `%s` < %llu)",
+						!showed_nulls?"`":"",
 						!showed_nulls?field:"",
+						!showed_nulls?"`":"",
 						!showed_nulls?" IS NULL OR ":"",
 						field, (unsigned long long)cutoff,
 						field, (unsigned long long)(cutoff+estimated_step)));
@@ -1180,9 +1241,13 @@
 
 	query= g_strdup_printf("SHOW CREATE TABLE `%s`.`%s`", database, table);
 	if (mysql_query(conn, query) || !(result= mysql_use_result(conn))) {
-		g_critical("Error dumping schemas (%s.%s): %s", database, table, mysql_error(conn));
+		if(success_on_1146 && mysql_errno(conn) == 1146){
+			g_warning("Error dumping schemas (%s.%s): %s", database, table, mysql_error(conn));
+		}else{
+			g_critical("Error dumping schemas (%s.%s): %s", database, table, mysql_error(conn));
+			errors++;
+		}
 		g_free(query);
-		errors++;
 		return;
 	}
 
@@ -1306,15 +1371,20 @@
 	guint i;
 	guint num_fields = 0;
 	guint64 num_rows = 0;
+	guint64 num_rows_st = 0;
 	MYSQL_RES *result = NULL;
 	char *query = NULL;
 
 	/* Ghm, not sure if this should be statement_size - but default isn't too big for now */
 	GString* statement = g_string_sized_new(statement_size);
-
+	GString* statement_row = g_string_sized_new(0);
+	
 	if (detected_server == SERVER_TYPE_MYSQL) {
 		g_string_printf(statement,"/*!40101 SET NAMES binary*/;\n");
 		g_string_append(statement,"/*!40014 SET FOREIGN_KEY_CHECKS=0*/;\n");
+		if (!skip_tz) {
+		  g_string_append(statement,"/*!40103 SET TIME_ZONE='+00:00' */;\n");
+		}
 	} else {
 		g_string_printf(statement,"SET FOREIGN_KEY_CHECKS=0;\n");
 	}
@@ -1327,9 +1397,14 @@
 	/* Poor man's database code */
  	query = g_strdup_printf("SELECT %s * FROM `%s`.`%s` %s %s", (detected_server == SERVER_TYPE_MYSQL) ? "/*!40001 SQL_NO_CACHE */" : "", database, table, where?"WHERE":"",where?where:"");
 	if (mysql_query(conn, query) || !(result=mysql_use_result(conn))) {
-		g_critical("Error dumping table (%s.%s) data: %s ",database, table, mysql_error(conn));
+		//ERROR 1146 
+		if(success_on_1146 && mysql_errno(conn) == 1146){
+			g_warning("Error dumping table (%s.%s) data: %s ",database, table, mysql_error(conn));
+		}else{
+			g_critical("Error dumping table (%s.%s) data: %s ",database, table, mysql_error(conn));
+			errors++;
+		}
 		g_free(query);
-		errors++;
 		return num_rows;
 	}
 
@@ -1348,38 +1423,57 @@
 		gulong *lengths = mysql_fetch_lengths(result);
 		num_rows++;
 
-		if (!statement->len)
-			g_string_printf(statement, "INSERT INTO `%s` VALUES\n(", table);
-		else
-			g_string_append(statement, ",\n(");
+		if (!statement->len){
+			g_string_printf(statement, "INSERT INTO `%s` VALUES", table);
+			num_rows_st = 0;
+		}
+		
+		if (statement_row->len) {
+			g_string_append(statement, statement_row->str);
+			g_string_set_size(statement_row,0);
+			num_rows_st++;
+		}
+		
+		g_string_append(statement_row, "\n(");
 
 		for (i = 0; i < num_fields; i++) {
 			/* Don't escape safe formats, saves some time */
 			if (!row[i]) {
-				g_string_append(statement, "NULL");
+				g_string_append(statement_row, "NULL");
 			} else if (fields[i].flags & NUM_FLAG) {
-				g_string_append(statement, row[i]);
+				g_string_append(statement_row, row[i]);
 			} else {
 				/* We reuse buffers for string escaping, growing is expensive just at the beginning */
 				g_string_set_size(escaped, lengths[i]*2+1);
 				mysql_real_escape_string(conn, escaped->str, row[i], lengths[i]);
-				g_string_append_c(statement,'\"');
-				g_string_append(statement,escaped->str);
-				g_string_append_c(statement,'\"');
+				g_string_append_c(statement_row,'\"');
+				g_string_append(statement_row,escaped->str);
+				g_string_append_c(statement_row,'\"');
 			}
 			if (i < num_fields - 1) {
-				g_string_append_c(statement,',');
+				g_string_append_c(statement_row,',');
 			} else {
-				/* INSERT statement is closed once over limit */
-				if (statement->len > statement_size) {
-					g_string_append(statement,");\n");
+				g_string_append_c(statement_row,')');
+				/* INSERT statement is closed before over limit */
+				if(statement->len+statement_row->len+1 > statement_size) {
+					if(num_rows_st == 0){
+						g_string_append(statement, statement_row->str);
+						g_string_set_size(statement_row,0);
+						g_warning("Row bigger than statement_size for %s.%s", database, table);
+					}
+					g_string_append(statement,";\n");
+
 					if (!write_data(file,statement)) {
 						g_critical("Could not write out data for %s.%s", database, table);
 						goto cleanup;
 					}
 					g_string_set_size(statement,0);
 				} else {
-					g_string_append_c(statement,')');
+					if(num_rows > 1)
+						g_string_append(statement,",");
+					g_string_append(statement, statement_row->str);
+					num_rows_st++;
+					g_string_set_size(statement_row,0);
 				}
 			}
 		}

=== modified file 'myloader.c'
--- myloader.c	2012-10-20 17:40:00 +0000
+++ myloader.c	2013-10-21 13:19:20 +0000
@@ -207,7 +207,7 @@
 	gchar** split_file= g_strsplit(filename, ".", 0);
 	gchar* database= split_file[0];
 	// Remove the -schema from the table name
-	gchar** split_table= g_strsplit(split_file[1], "-", 0);
+	gchar** split_table= g_strsplit(split_file[1], "-schema", 0);
 	gchar* table= split_table[0];
 
 	gchar* query= g_strdup_printf("SHOW CREATE DATABASE `%s`", db ? db : database);
@@ -279,6 +279,8 @@
 		mysql_query(thrconn, "SET SQL_LOG_BIN=0");
 
 	mysql_query(thrconn, "/*!40101 SET NAMES binary*/");
+	mysql_query(thrconn, "/*!40101 SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */");
+	mysql_query(thrconn, "/*!40014 SET UNIQUE_CHECKS=0 */");
 	mysql_query(thrconn, "SET autocommit=0");
 
 	g_async_queue_push(conf->ready, GINT_TO_POINTER(1));