MySQL - Revision history

Piousbox: /* find largest tables */

2025-12-11T17:05:11Z

‎find largest tables

2025-12-11T17:04:55Z

2025-06-12T04:54:36Z

‎some timeout

2025-06-12T04:54:25Z

‎some timeout

2025-06-12T04:53:44Z

‎Troubleshoot

2024-10-15T23:29:27Z

2024-04-12T17:09:48Z

‎mysql binlog files grow too large

2024-04-12T17:09:23Z

2023-08-29T23:17:29Z

2023-05-19T23:17:23Z

@@ Line 108: / Line 108: @@
 == find largest tables ==
-```
+<pre>
 SELECT
      table_name AS "Table",
@@ Line 115: / Line 115: @@
 WHERE table_schema = DATABASE()
 ORDER BY (data_length + index_length) DESC;
-```
+</pre>

← Older revision		Revision as of 17:04, 11 December 2025
Line 106:		Line 106:
	brew services list		brew services list
	brew services start/stop mysql		brew services start/stop mysql
		+
		+	== find largest tables ==
		+	```
		+	SELECT
		+	table_name AS "Table",
		+	ROUND((data_length + index_length) / 1024 / 1024, 2) AS "Size_MB"
		+	FROM information_schema.TABLES
		+	WHERE table_schema = DATABASE()
		+	ORDER BY (data_length + index_length) DESC;
		+	```

@@ Line 65: / Line 65: @@
    SHOW PROCESSLIST;
    KILL <id>;
    SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`;

@@ Line 65: / Line 65: @@
    SHOW PROCESSLIST;
    KILL <id>;
 == ActiveRecord::StatementInvalid: Mysql2::Error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) ==

← Older revision		Revision as of 04:53, 12 June 2025
Line 59:		Line 59:

	= Troubleshoot =		= Troubleshoot =
		+
		+	== some timeout ==
		+	From: https://stackoverflow.com/questions/2766785/fixing-lock-wait-timeout-exceeded-try-restarting-transaction-for-a-stuck-my
		+
		+	SHOW PROCESSLIST;
		+	KILL <id>;

	== ActiveRecord::StatementInvalid: Mysql2::Error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) ==		== ActiveRecord::StatementInvalid: Mysql2::Error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) ==

← Older revision		Revision as of 23:29, 15 October 2024
Line 1:		Line 1:
		+
		+

	== mysql binlog files grow too large ==		== mysql binlog files grow too large ==

@@ Line 7: / Line 7: @@
    SET PERSIST binlog_expire_logs_seconds = (60*60*24*3);
-and purge them:
+list them and purge them:
    PURGE BINARY LOGS TO 'binlog.000142';
 == Configure External Access ==

← Older revision	Revision as of 17:09, 12 April 2024
Line 1:	Line 1:
	+
	+	== mysql binlog files grow too large ==
	+
	+	set expiry of binlogs to 3 days:
	+
	+	SET GLOBAL binlog_expire_logs_seconds = (606024*3);
	+	SET PERSIST binlog_expire_logs_seconds = (606024*3);
	+
	+	and purge them:
	+
	+	PURGE BINARY LOGS TO 'binlog.000142';

← Older revision		Revision as of 23:17, 29 August 2023
Line 78:		Line 78:

	SHOW FULL PROCESSLIST;		SHOW FULL PROCESSLIST;
		+
		+	== Control on a mac ==
		+
		+	brew services list
		+	brew services start/stop mysql

← Older revision		Revision as of 23:17, 19 May 2023
Line 74:		Line 74:

	alter user 'username'@'localhost' identified with mysql_native_password by 'password';		alter user 'username'@'localhost' identified with mysql_native_password by 'password';
		+
		+	== kill long quaries ==
		+
		+	SHOW FULL PROCESSLIST;