enterprise-support team mailing list archive

[ludy <ludyhhm@xxxxxxx>]

Public bug reported:

mydumper 0.9.1
OS centos6.6 X86_64
mysql  5.6.25-log

mydumper -h $HOST -u $USER -p $PASSWORD -P $PORT -G -E -R -c -x '^(mysql|db1|db2)'
mysql schema is MyISAM

Describe

Began to execute regular backup script, after a few minutes application error "Cannot get a connection,pool error Timeout or idle object", and execute command show processlist have a lot of “Waiting for a table flush” and several “Waiting for global read”, check the backup process
56793, 39481: backup 127.0.0.1 NULL Query 3671 Waiting for table flush flush TABLES WITH READ LOCK
Apparently are blocked by long queries,use cat list.txt |awk '{if($6>3671) print $0}',grep larger than 3671
46109	app1	*.*.*.*:25545	db_name	Query	13626	Sending data	/* dynamic native SQL query */  SELECT  ... 
executed 13626/3600=3.7h,from the process id and the status, that is blocked by this query

Question:Why doesn't terminate the backup process before the backup?
Normal logic is to show  processlist check whether there is a long
query, if you have it aborting dump to avoids stalling whole server with
flush.

source code:


	/* We check SHOW PROCESSLIST, and if there're queries
	   larger than preset value, we terminate the process.

           This avoids stalling whole server with flush */

	if (mysql_query(conn, "SHOW PROCESSLIST")) {
		g_warning("Could not check PROCESSLIST, no long query guard enabled: %s", mysql_error(conn));
	} else {
		MYSQL_RES *res = mysql_store_result(conn);
		MYSQL_ROW row;

		/* Just in case PROCESSLIST output column order changes */
		MYSQL_FIELD *fields = mysql_fetch_fields(res);
		guint i;
		int tcol=-1, ccol=-1, icol=-1;
		for(i=0; i<mysql_num_fields(res); i++) {
			if (!strcasecmp(fields[i].name,"Command")) ccol=i;
			else if (!strcasecmp(fields[i].name,"Time")) tcol=i;
			else if (!strcasecmp(fields[i].name,"Id")) icol=i;
		}
		if ((tcol < 0) || (ccol < 0) || (icol < 0)) {
			g_critical("Error obtaining information from processlist");
			exit(EXIT_FAILURE);
		}
		while ((row=mysql_fetch_row(res))) {
			if (row[ccol] && strcmp(row[ccol],"Query"))
				continue;
			if (row[tcol] && atoi(row[tcol])>longquery) {
				if (killqueries) {
					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(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);
					exit(EXIT_FAILURE);
				}
			}
		}
		mysql_free_result(res);
	}

** Affects: mydumper
     Importance: Undecided
         Status: New

-- 
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1587390

Title:
  mydumper occurred Waiting for table flush

To manage notifications about this bug go to:
https://bugs.launchpad.net/mydumper/+bug/1587390/+subscriptions