MySQL - Revision history

Leaky: /* innodb_log_file_size */

2018-01-22T08:42:03Z

‎innodb_log_file_size

Leaky: Added info about resetting password

2014-02-26T10:35:22Z

Added info about resetting password

Leaky at 16:39, 24 September 2013

2013-09-24T16:39:11Z

Leaky at 09:40, 23 September 2013

2013-09-23T09:40:48Z

Leaky at 09:40, 23 September 2013

2013-09-23T09:40:35Z

Leaky: Add note about changing innodb_log_file_size variable

2013-09-23T09:36:34Z

Add note about changing innodb_log_file_size variable

Leaky: Created page with 'If you start to use innodb a lot and see the following error in the mysql error log, you need to tune the innodb_log_file_size variable. 130923 9:27:06 InnoDB: ERROR: the age…'

2013-09-23T09:09:30Z

Created page with 'If you start to use innodb a lot and see the following error in the mysql error log, you need to tune the innodb_log_file_size variable. 130923 9:27:06 InnoDB: ERROR: the age…'

New page

If you start to use innodb a lot and see the following error in the mysql error log, you need to tune the innodb_log_file_size variable.

130923 9:27:06 InnoDB: ERROR: the age of the last checkpoint is 9448377,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

← Older revision		Revision as of 08:42, 22 January 2018
Line 13:		Line 13:
	mysql> execute s;		mysql> execute s;

		+
		+	== MySQL server has gone away ==
		+
		+	During import of a large file (using 'source databasebackup.sql'), you might see something like:
		+
		+	Query OK, 16 rows affected (0.11 sec)
		+	Records: 16 Duplicates: 0 Warnings: 0
		+
		+	ERROR 2006 (HY000) at line 188 in file: 'databasebackup.sql': MySQL server has gone away
		+	No connection. Trying to reconnect...
		+	Connection id: 2537
		+	Current database: mydatabase
		+
		+	Query OK, 0 rows affected, 1 warning (0.00 sec)
		+
		+	This is likely to be because the max_allowed_packet variable hasn't been set high enough on either the server or the client. Edit /etc/my.cnf and add to the [mysqld] section:
		+
		+	set max_allowed_packet=1073741824
		+
		+	And add the same to your ~/.my.cnf in the [mysql] section. Restart the MySQL server and retry the import.

	== innodb_log_file_size ==		== innodb_log_file_size ==

← Older revision		Revision as of 10:35, 26 February 2014
Line 48:		Line 48:

	This results in 30MB per hour so we may want to increase the value on this server to 15M instead of the default of 5M (half the overall MB per hour since there are two logfiles).		This results in 30MB per hour so we may want to increase the value on this server to 15M instead of the default of 5M (half the overall MB per hour since there are two logfiles).
		+
		+	== Reset mysql password ==
		+
		+	To reset the mysql password for a user (other than root), login with the root account and enter the following:
		+
		+	mysql> update mysql.user set password=password('XXXX') where user='username';
		+	mysql> flush privileges;
		+
		+	If it's the root user you need to reset, stop mysql and then start it
		+
		+	# /usr/sbin/mysqld_safe -u root --skip-grant-tables --skip-networking
		+
		+	In another terminal, use the above SQL to reset the password for user root.
		+	Once done, restart the server as normal:
		+
		+	# mysqladmin shutdown
		+
		+	At this point the first terminal will display some messages about the server exiting and then return to the prompt.
		+
		+	# service mysqld start

← Older revision		Revision as of 16:39, 24 September 2013
Line 1:		Line 1:
		+	== SELECT * (but not x) FROM table ==
		+
		+	mysql> set @table = "sometable";
		+
		+	mysql> set @database = "somedb";
		+
		+	mysql> set @cols=(select group_concat(column_name) from information_schema.columns where table_name=@table and table_schema=@database and column_name not in ('somecolumn'));
		+
		+	mysql> set @sql=concat("SELECT ", @cols, " FROM ", @table);
		+
		+	mysql> prepare s from @sql;
		+
		+	mysql> execute s;
		+
		+
		+	== innodb_log_file_size ==
		+
	If you start to use innodb a lot and see the following error in the mysql error log, you need to tune the innodb_log_file_size variable.		If you start to use innodb a lot and see the following error in the mysql error log, you need to tune the innodb_log_file_size variable.

@@ Line 17: / Line 17: @@
 row in set (59.99 sec)
-−
   Log sequence number 83 3167469638
 row in set (0.00 sec)

← Older revision		Revision as of 09:40, 23 September 2013
Line 10:		Line 10:

	To change the variable innodb_log_file_size, you have to shutdown cleanly, rename the logfiles as backups, edit the config and restart the server. Once new logfiles are being created, the old ones can be deleted. The server will crash on startup if you change the value without renaming the old logfiles.		To change the variable innodb_log_file_size, you have to shutdown cleanly, rename the logfiles as backups, edit the config and restart the server. Once new logfiles are being created, the old ones can be deleted. The server will crash on startup if you change the value without renaming the old logfiles.
		+
		+	mysql> pager grep sequence; show engine innodb status\G select sleep(60); show engine innodb status\G pager;
		+	PAGER set to 'grep sequence'
		+	Log sequence number 83 3166929032
		+	1 row in set (0.00 sec)
		+
		+	1 row in set (59.99 sec)
		+
		+	Log sequence number 83 3167469638
		+	1 row in set (0.00 sec)
		+
		+	Default pager wasn't set, using stdout.
		+	mysql> select (3167469638 - 3166929032)/(1024*1024) as MB_per_min;
		+	+------------+
		+	\| MB_per_min \|
		+	+------------+
		+	\| 0.5156 \|
		+	+------------+
		+	1 row in set (0.00 sec)
		+
		+	This results in 30MB per hour so we may want to increase the value on this server to 15M instead of the default of 5M (half the overall MB per hour since there are two logfiles).

← Older revision		Revision as of 09:36, 23 September 2013
Line 8:		Line 8:

	http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/		http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
		+
		+	To change the variable innodb_log_file_size, you have to shutdown cleanly, rename the logfiles as backups, edit the config and restart the server. Once new logfiles are being created, the old ones can be deleted. The server will crash on startup if you change the value without renaming the old logfiles.