MySQL - Revision history

Leo: /* Incorrect definition of table mysql.column_stats */

2022-09-06T22:13:23Z

Incorrect definition of table mysql.column_stats

← Older revision		Revision as of 22:13, 6 September 2022
Line 369:		Line 369:
	OK		OK
	\| lang = terminal		\| lang = terminal
	}}~~If you still get these errors after upgrading the database manually and re-running mysql_upgrade reports the database~~ is ~~up to date, then you're best to just dump the database and re~~-~~import it in a clean instance and start over~~. ~~This is what I had~~ to do to fix ~~a NextCloud database~~.		}}This is a bug in MariaDB 10.8.3 (https://jira.mariadb.org/browse/MDEV-28866). Upgrade to 10.8.4 or higher to fix.

	~~Going forward~~, it might be helpful to include the <code>MARIADB_AUTO_UPGRADE=1</code> environment variable in the container so that the database schema is automatically upgraded when a newer version of the database is used.{{Navbox Linux}}[[Category:Linux]]		If this is still affecting you, then your next best course of action is to dump the database and re-import it in a clean container. This is what I had to do to fix a NextCloud database.

			Additionally, it might be helpful to include the <code>MARIADB_AUTO_UPGRADE=1</code> environment variable in the container so that the database schema is automatically upgraded when a newer version of the database is used.{{Navbox Linux}}[[Category:Linux]]
	[[Category:Todo]]		[[Category:Todo]]

	{{Navbox Services}}		{{Navbox Services}}

2022-09-06T22:02:11Z

Incorrect definition of table mysql.column_stats

← Older revision		Revision as of 22:02, 6 September 2022
Line 369:		Line 369:
	OK		OK
	\| lang = terminal		\| lang = terminal
	}}{{Navbox Linux}}[[Category:Linux]]		}}If you still get these errors after upgrading the database manually and re-running mysql_upgrade reports the database is up to date, then you're best to just dump the database and re-import it in a clean instance and start over. This is what I had to do to fix a NextCloud database.

			Going forward, it might be helpful to include the <code>MARIADB_AUTO_UPGRADE=1</code> environment variable in the container so that the database schema is automatically upgraded when a newer version of the database is used.{{Navbox Linux}}[[Category:Linux]]
	[[Category:Todo]]		[[Category:Todo]]

	{{Navbox Services}}		{{Navbox Services}}

2022-09-03T00:01:11Z

Troubleshooting

@@ Line 335: / Line 335: @@
 After restarting the database server, it seemed to be happy again.
-{{Navbox Linux}}[[Category:Linux]]
+=== Incorrect definition of table mysql.column_stats ===
 [[Category:Todo]]
 {{Navbox Services}}

2020-08-26T17:55:37Z

Loading Timezone Data

← Older revision		Revision as of 17:55, 26 August 2020
Line 257:		Line 257:

	If Timezone data is not available, certain PHP scripts might break with a {{code\| Couldn't change session tz_session!}} error.		If Timezone data is not available, certain PHP scripts might break with a {{code\| Couldn't change session tz_session!}} error.

			=== Show Table Sizes ===
			To show the sizes of each table in a database, run:

			{{highlight\|lang=terminal\|code=
			> SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES
			WHERE table_schema = "slurm_acct_db"
			ORDER BY (data_length + index_length) DESC;<nowiki>
			+-------------------------+-----------+
			\| Table \| Size (MB) \|
			+-------------------------+-----------+
			\| step_table \| 6586.00 \|
			\| job_table \| 5679.42 \|
			\| assoc_usage_hour_table \| 180.89 \|
			\| event_table \| 21.55 \|
			\| assoc_usage_day_table \| 15.55 \|
			</nowiki>
			...
			}}

	== Troubleshooting ==		== Troubleshooting ==

2020-01-12T04:02:53Z

= MariaDB 10 on CentOS 7

← Older revision		Revision as of 04:02, 12 January 2020
Line 2:		Line 2:

	== Installation ==		== Installation ==
	=== MariaDB 10 on CentOS 7 ==		=== MariaDB 10 on CentOS 7 ===
	Create a new yum repo with the following:		The system default version of MariaDB is 5.5 on CentOS 7. To use the latest version of MariaDB, use the official MariaDB repo. Create a new yum repo by editing {{code\|/etc/yum.repos.d/MariaDB.repo}} with the following:
	{{highlight\|lang=text\|code=		{{highlight\|lang=text\|code=
	[mariadb]		[mariadb]

2020-01-12T04:01:57Z

← Older revision		Revision as of 04:01, 12 January 2020
Line 1:		Line 1:
	This article contains various common tasks involving MySQL and MariaDB.		This article contains various common tasks involving MySQL and MariaDB.

			== Installation ==
			=== MariaDB 10 on CentOS 7 ==
			Create a new yum repo with the following:
			{{highlight\|lang=text\|code=
			[mariadb]
			name = MariaDB
			baseurl = http://yum.mariadb.org/10.1/centos7-amd64
			gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
			gpgcheck=1
			}}

			Then install the MariaDB package.

	== Docker ==		== Docker ==

2020-01-12T03:49:51Z

Tasks

@@ Line 17: / Line 17: @@
 These instructions can be used with MariaDB.
 }}
 === Reset MySQL Root Password ===

2019-09-22T06:06:15Z

Tasks: added .my.cnf

← Older revision		Revision as of 06:06, 22 September 2019
Line 50:		Line 50:
	}}		}}

			=== {{code\|.my.cnf}} ===
			You can make the mysql client not prompt for username or password if it is given in a {{code\|~/.my.cnf}} file.

			{{highlight\|lang=text\|filename=~/.my.cnf\|code=
			[client]
			password="xxxxxxx"
			user=root
			}}

	=== Binary Log Management ===		=== Binary Log Management ===

2019-09-22T06:03:57Z

Tasks: Added change mysql account password

← Older revision		Revision as of 06:03, 22 September 2019
Line 33:		Line 33:

	See Also: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html		See Also: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

			=== Change MySQL Account Password ===

			{{highlight\|lang=terminal\|code=
			## Before MySQL 5.7.5 / MariaDB 10.1.20
			> SET PASSWORD FOR 'user-name'@'localhost' = PASSWORD('NEW_USER_PASSWORD');

			## After MySQL 5.7.5 / MariaDB 10.1.20
			> ALTER USER 'user-name'@'localhost' IDENTIFIED BY 'NEW_USER_PASSWORD';

			## Or manually modifying the mysql users table
			> UPDATE mysql.user SET authentication_string = PASSWORD('NEW_USER_PASSWORD') WHERE User = 'user-name' AND Host = 'localhost';

			## In any of the above cases, flush privileges after to apply
			> FLUSH PRIVILEGES;
			}}


	=== Binary Log Management ===		=== Binary Log Management ===

2019-09-01T06:20:56Z

Text replacement - "Category:Linux{{Navbox Linux}}" to "{{Navbox Linux}}Category:Linux"

← Older revision		Revision as of 06:20, 1 September 2019
Line 239:		Line 239:
	After restarting the database server, it seemed to be happy again.		After restarting the database server, it seemed to be happy again.

	[[Category:Linux]]~~{{Navbox Linux}}~~		{{Navbox Linux}}[[Category:Linux]]
	[[Category:Todo]]		[[Category:Todo]]

	{{Navbox Services}}		{{Navbox Services}}