MySQL/notes - Revision history

Woozle: /* SQL Examples */ updated syntax highlight tags

2023-12-11T19:30:42Z

SQL Examples: updated syntax highlight tags

← Older revision		Revision as of 19:30, 11 December 2023
Line 38:		Line 38:
	==SQL Examples==		==SQL Examples==
	'''creating a table''': If the Query Browser crashes when you try to create or edit a table, use the command line interface to create the table:		'''creating a table''': If the Query Browser crashes when you try to create or edit a table, use the command line interface to create the table:
	'''create table''' <u>table_name</u> (<u>field_name</u> <u>field_type</u>, <u>field_name</u> <u>field_type</u>{{~~optargs~~\|, ...}});		'''create table''' <u>table_name</u> (<u>field_name</u> <u>field_type</u>, <u>field_name</u> <u>field_type</u>{{arg/opt\|, ...}});
	Example:		Example:
	<sql>~~create table~~ example (key varchar(255));</~~sql~~>		<syntaxhighlight lang=sql inline>CREATE TABLE example (key varchar(255));</syntaxhighlight>
	* More examples: [[importing data into ZenCart]]		* More examples: [[importing data into ZenCart]]
	* Documentation: [http://dev.mysql.com/doc/refman/5.1/en/create-table.html CREATE TABLE Syntax]		* Documentation: [http://dev.mysql.com/doc/refman/5.1/en/create-table.html CREATE TABLE Syntax]
Line 47:		Line 47:
	'''ALTER TABLE''' <u>tablename</u> '''MODIFY''' <u>fieldname</u> <u>fieldtype</u> '''AUTO_INCREMENT''';		'''ALTER TABLE''' <u>tablename</u> '''MODIFY''' <u>fieldname</u> <u>fieldtype</u> '''AUTO_INCREMENT''';
	Example:		Example:
	<sql>ALTER TABLE Tracks_Queued MODIFY id INTEGER AUTO_INCREMENT;</~~sql~~>		<syntaxhighlight lang=sql inline>ALTER TABLE Tracks_Queued MODIFY id INTEGER AUTO_INCREMENT;</syntaxhighlight>

	'''changing the name of a field (column)''':		'''changing the name of a field (column)''':
Line 54:		Line 54:

	Example:		Example:
	<sql>ALTER TABLE cat_items CHANGE isInStock qtyInStock INT COMMENT 'number of pieces currently in stock (calculated from stock table)';</~~sql~~>		<syntaxhighlight lang=sql inline>ALTER TABLE cat_items CHANGE isInStock qtyInStock INT COMMENT 'number of pieces currently in stock (calculated from stock table)';</syntaxhighlight>
	* Documentation: [http://dev.mysql.com/doc/refman/5.1/en/alter-table.html ALTER TABLE Syntax]		* Documentation: [http://dev.mysql.com/doc/refman/5.1/en/alter-table.html ALTER TABLE Syntax]

	==Address Binding==		==Address Binding==
	If you get this message on attempting to connect to a remote mysql server (named "rizzo" in this case):		If you get this message on attempting to connect to a remote mysql server (named "rizzo" in this case):

Woozle: merge deleted older page contents, so recovering that manually; removed "notes" header and elevated newer sections to level 2

2017-03-20T10:18:12Z

merge deleted older page contents, so recovering that manually; removed "notes" header and elevated newer sections to level 2

← Older revision		Revision as of 10:18, 20 March 2017
Line 1:		Line 1:
	~~==Notes==~~		==Repairing==
	===Repairing===
	* [[mysqlcheck]] -A --auto-repair -p		* [[mysqlcheck]] -A --auto-repair -p
	===SQL error messages===		==SQL error messages==
	* 1271 - Illegal mix of collations for operation 'concat'		* 1271 - Illegal mix of collations for operation 'concat'
	** http://www.issociate.de/board/post/274187/CONCAT_with_IF.html		** http://www.issociate.de/board/post/274187/CONCAT_with_IF.html
	** When the problem expression was a CONCAT(...) function, doing CAST(CONCAT(...) AS BINARY) fixed it.		** When the problem expression was a CONCAT(...) function, doing CAST(CONCAT(...) AS BINARY) fixed it.
	===munged MySQL installation===		==munged MySQL installation==
	If you can't reinstall ''or'' remove MySQL because the "{{faint\|/etc/init.d/}}mysql start" command fails during (un)installation, check for rogue "mysql" and "mysql-safe" processes which aren't getting stopped by "{{faint\|/etc/init.d/}}mysql stop".		If you can't reinstall ''or'' remove MySQL because the "{{faint\|/etc/init.d/}}mysql start" command fails during (un)installation, check for rogue "mysql" and "mysql-safe" processes which aren't getting stopped by "{{faint\|/etc/init.d/}}mysql stop".
	===debian-start===		==debian-start==
	The /etc/mysql/debian-start file appears to contain a minor error, at least on the MySQL install I just reinstalled. It sets the variable MYCHECK, and then tries to invoke the command to which it is set by saying just "mycheck;", resulting in the error message "/etc/mysql/debian-start: line 17: mycheck: command not found" although the mysql daemon is apparently restarted successfully. To fix the error, change "mycheck;" to "$mycheck;". (This also has the benefit of actually running whatever command MYCHECK is set to, which is typically some kind of integrity check to help keep your databases from becoming corrupted.)		The /etc/mysql/debian-start file appears to contain a minor error, at least on the MySQL install I just reinstalled. It sets the variable MYCHECK, and then tries to invoke the command to which it is set by saying just "mycheck;", resulting in the error message "/etc/mysql/debian-start: line 17: mycheck: command not found" although the mysql daemon is apparently restarted successfully. To fix the error, change "mycheck;" to "$mycheck;". (This also has the benefit of actually running whatever command MYCHECK is set to, which is typically some kind of integrity check to help keep your databases from becoming corrupted.)
	===external connections===		==external connections==
	If mysql refuses to respond to connection attempts from anywhere but the local machine, you need to edit /etc/mysql/my.conf and comment out the following line:		If mysql refuses to respond to connection attempts from anywhere but the local machine, you need to edit /etc/mysql/my.conf and comment out the following line:
	bind-address = 127.0.0.1		bind-address = 127.0.0.1
	: and then restart mysqld.		: and then restart mysqld.
	===miscellaneous===		==miscellaneous==
	To restart the mysql [[daemon]] (mysqld) when you don't have a user with SHUTDOWN privileges but you do have system root:		To restart the mysql [[daemon]] (mysqld) when you don't have a user with SHUTDOWN privileges but you do have system root:
	sudo /etc/init.d/mysql restart		sudo /etc/init.d/mysql restart
	===Command-line Client Example===		==Command-line Client Example==
	'''editing a blank table''': To work around the Query Browser bug (fixed in the latest version) which won't let you edit a blank table, create the first entry using MySQL's command line interface:		'''editing a blank table''': To work around the Query Browser bug (fixed in the latest version) which won't let you edit a blank table, create the first entry using MySQL's command line interface:

Line 37:		Line 36:

	...where <u>yourhost</u> is the name of your MySQL server, and can be anything that can resolve to an IP address, e.g. localhost, yourmachinename, yourserverdomain.com, or an actual IP address (I think).		...where <u>yourhost</u> is the name of your MySQL server, and can be anything that can resolve to an IP address, e.g. localhost, yourmachinename, yourserverdomain.com, or an actual IP address (I think).
	===SQL Examples===		==SQL Examples==
	'''creating a table''': If the Query Browser crashes when you try to create or edit a table, use the command line interface to create the table:		'''creating a table''': If the Query Browser crashes when you try to create or edit a table, use the command line interface to create the table:
	'''create table''' <u>table_name</u> (<u>field_name</u> <u>field_type</u>, <u>field_name</u> <u>field_type</u>{{optargs\|, ...}});		'''create table''' <u>table_name</u> (<u>field_name</u> <u>field_type</u>, <u>field_name</u> <u>field_type</u>{{optargs\|, ...}});
Line 57:		Line 56:
	<sql>ALTER TABLE cat_items CHANGE isInStock qtyInStock INT COMMENT 'number of pieces currently in stock (calculated from stock table)';</sql>		<sql>ALTER TABLE cat_items CHANGE isInStock qtyInStock INT COMMENT 'number of pieces currently in stock (calculated from stock table)';</sql>
	* Documentation: [http://dev.mysql.com/doc/refman/5.1/en/alter-table.html ALTER TABLE Syntax]		* Documentation: [http://dev.mysql.com/doc/refman/5.1/en/alter-table.html ALTER TABLE Syntax]
			==Address Binding==
			If you get this message on attempting to connect to a remote mysql server (named "rizzo" in this case):
			ERROR 2003 (HY000): Can't connect to MySQL server on 'rizzo' (111)
			...and "telnet rizzo 3306" returns this (where rizzo is at 192.168.0.103):
			Trying 192.168.0.103...
			telnet: Unable to connect to remote host: Connection refused
			...then the problem may be that mysql is only "listening" on its loopback interface. Look in the [[my.cnf]] file for:
			bind-address = 127.0.0.1
			...and change it to:
			bind-address = 192.168.0.103
			...using your mysql server's actual IP address instead of the one given.

			It's not clear if there's any address-nonspecific way of doing this so that (e.g.) if rizzo were to be assigned a different IP address later on, my.cnf would not need to be changed.

			==MySQL on a DNS server==
			Some observations on 2007-09-21, from the owner/operator of a small web hosting provider. These only apply if:
			* the web sites are on a ''different machine'' from the MySQL server
			* the web site machine is connected to the MySQL server directly (typically via crossover cable), rather than going through the LAN

			What happened, and the solution (in brief):

			: Tonight, my cluster bit the dust. Every time we would restart MySQL, we would immediately be deluged with huge amounts of "unauthenticated users" hitting up the DB server. Thanks to the good folks in #MySQL on EFNet IRC, they turned me onto http://hackmysql.com/dns
			: For /etc/my.cnf
			: After [mysqld]
			: ADD this, on its own line: skip-name-resolve
			: ---------------------
			: If you're running your DNS on the same machine as your DB server, make SURE you add the three following lines to /etc/[[resolv.conf]]:
			:: nameserver 127.0.0.1
			:: nameserver 192.168.0.10
			:: nameserver 192.168.0.20
			: Modifying the two 192.168.x.x ranges to be the IP's of your internal private network.
			: At one point, my DB server was deluged with over 2300 requests that were trying to do reverse DNS on 192.168.0.10 (which is the web server, .20 is the DB server). Since the entire 192.168.X.X block is set aside for internal private networks, chances are that you will NOT have those resolving into hostnames. The problem was that my DB server was trying to resolve all 2300 requests into a hostname before it allowed DB queries to go through.
			: You'll have to restart (or shut down/kill off all MySQL processes, remove mysql.sock and your pid file) and then start MySQL up again.
			: I hope this saves someone the two hours of headache I've just gone through getting this sorted.

			Full post is [http://www.therealms.net/forums/index.php/topic,243.0.html here].

Woozle: Created page with "==Notes== ===Repairing=== * mysqlcheck -A --auto-repair -p ===SQL error messages=== * 1271 - Illegal mix of collations for operation 'concat' ** http://www.issociate.de/bo..."

2016-11-16T01:33:21Z

Created page with "==Notes== ===Repairing=== * mysqlcheck -A --auto-repair -p ===SQL error messages=== * 1271 - Illegal mix of collations for operation 'concat' ** http://www.issociate.de/bo..."

← Older revision		Revision as of 01:33, 16 November 2016
Line 1:		Line 1:
	==~~Navigation~~==		==Notes==
	[[~~computing]]: [[software]]: [[database software\|databases]]: [[database engines\|engines]]: [[MySQL]]: [[MySQL notes\|notes~~]]		===Repairing===
	==~~Overview~~==		* [[mysqlcheck]] -A --auto-repair -p
	~~This page is~~ for ~~any loose notes on [[MySQL]] for which there isn~~'~~t yet~~ a ~~specific page~~.		===SQL error messages===
	==~~Address Binding~~==		* 1271 - Illegal mix of collations for operation 'concat'
	If you ~~get this message on attempting to connect to a remote~~ mysql ~~server~~ (~~named~~ "~~rizzo~~" ~~in this case):~~		** http://www.issociate.de/board/post/274187/CONCAT_with_IF.html
	~~ERROR 2003 (HY000): Can~~'t ~~connect~~ to MySQL ~~server on 'rizzo' (111)~~		** When the problem expression was a CONCAT(...) function, doing CAST(CONCAT(...) AS BINARY) fixed it.
	...and "~~telnet rizzo 3306~~" ~~returns this (where rizzo~~ is ~~at 192~~.~~168~~.0.~~103~~):		===munged MySQL installation===
	~~Trying 192.168.0.103...~~		If you can't reinstall ''or'' remove MySQL because the "{{faint\|/etc/init.d/}}mysql start" command fails during (un)installation, check for rogue "mysql" and "mysql-safe" processes which aren't getting stopped by "{{faint\|/etc/init.d/}}mysql stop".
	~~telnet: Unable~~ to ~~connect~~ to ~~remote host: Connection refused~~		===debian-start===
	~~...then~~ the ~~problem may be that~~ mysql ~~is only "listening" on its loopback interface~~. ~~Look in~~ the ~~[[my.cnf]] file for~~:		The /etc/mysql/debian-start file appears to contain a minor error, at least on the MySQL install I just reinstalled. It sets the variable MYCHECK, and then tries to invoke the command to which it is set by saying just "mycheck;", resulting in the error message "/etc/mysql/debian-start: line 17: mycheck: command not found" although the mysql daemon is apparently restarted successfully. To fix the error, change "mycheck;" to "$mycheck;". (This also has the benefit of actually running whatever command MYCHECK is set to, which is typically some kind of integrity check to help keep your databases from becoming corrupted.)
	bind-address = 127.0.0.1		===external connections===
	.~~..and change it to~~:		If mysql refuses to respond to connection attempts from anywhere but the local machine, you need to edit /etc/mysql/my.conf and comment out the following line:
	~~bind~~-~~address~~ = ~~192.168.0.103~~		bind-address = 127.0.0.1
	~~...~~using ~~your mysql server~~'s ~~actual IP address instead of the one given.~~		: and then restart mysqld.
			===miscellaneous===
			To restart the mysql [[daemon]] (mysqld) when you don't have a user with SHUTDOWN privileges but you do have system root:
			sudo /etc/init.d/mysql restart
			===Command-line Client Example===
			'''editing a blank table''': To work around the Query Browser bug (fixed in the latest version) which won't let you edit a blank table, create the first entry using MySQL's command line interface:

	~~It's not clear if there's any address~~-~~nonspecific way of doing this so that (e~~.g.~~) if rizzo were~~ to ~~be assigned a different IP address later on, my~~.~~cnf would not need~~ to be changed.		woozle@camilla:~ $ mysql -h<u>yourhost</u> -u<u>yourusername</u> -p<u>yourpassword</u>
			Welcome to the MySQL monitor. Commands end with ; or \g.
			Your MySQL connection id is 12531702 to server version: 4.1.13-standard

			Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

			mysql> use <u>yourdatabasename</u>
			Reading table information for completion of table and column names
			You can turn off this feature to get a quicker startup with -A

			Database changed
			mysql> INSERT INTO <u>tablename</u> () VALUES();
			Query OK, 1 row affected (0.05 sec)

			mysql>

	==~~MySQL on a DNS server~~==		...where <u>yourhost</u> is the name of your MySQL server, and can be anything that can resolve to an IP address, e.g. localhost, yourmachinename, yourserverdomain.com, or an actual IP address (I think).
	~~Some observations on 2007-09-21~~, ~~from~~ the ~~owner/operator of a small web hosting provider. These only apply if~~:		===SQL Examples===
	* the web sites are on a ''~~different machine~~'' ~~from the MySQL server~~		'''creating a table''': If the Query Browser crashes when you try to create or edit a table, use the command line interface to create the table:
	* the web site machine is connected to the MySQL server directly (~~typically via crossover cable~~)~~, rather than going through the LAN~~		'''create table''' <u>table_name</u> (<u>field_name</u> <u>field_type</u>, <u>field_name</u> <u>field_type</u>{{optargs\|, ...}});
			Example:
			<sql>create table example (key varchar(255));</sql>
			* More examples: [[importing data into ZenCart]]
			* Documentation: [http://dev.mysql.com/doc/refman/5.1/en/create-table.html CREATE TABLE Syntax]

	~~What happened, and the solution (in brief)~~:		'''making a field autonumbered''':
			'''ALTER TABLE''' <u>tablename</u> '''MODIFY''' <u>fieldname</u> <u>fieldtype</u> '''AUTO_INCREMENT''';
			Example:
			<sql>ALTER TABLE Tracks_Queued MODIFY id INTEGER AUTO_INCREMENT;</sql>

	~~: Tonight, my cluster bit~~ the ~~dust. Every time we would restart MySQL, we would immediately be deluged with huge amounts~~ of ~~"unauthenticated users" hitting up the DB server. Thanks to the good folks in #MySQL on EFNet IRC, they turned me onto http~~:~~//hackmysql.com/dns~~		'''changing the name of a field (column)''':
	~~: For~~ /~~etc~~/~~my.cnf~~		'''ALTER TABLE''' <u>tablename</u> '''CHANGE''' <u>oldname</u> <u>newname</u> <u>new_fieldtype</u>;
	~~: After [mysqld]~~		This also changes the field's definition, if new_fieldtype is not the same as the field's prior definition.
	~~: ADD this, on its own line: skip-name-resolve~~
	~~: ---------------------~~
	~~: If you're running your DNS on the same machine as your DB server, make SURE you add the three following lines to~~ /~~etc~~/~~[[resolv.conf]]:~~
	~~:: nameserver 127.0.0.1~~
	~~:: nameserver 192.168.0.10~~
	~~:: nameserver 192.168.0.20~~
	~~: Modifying~~ the ~~two 192.168.x.x ranges to be the IP~~'s ~~of your internal private network.~~
	~~: At one point, my DB server was deluged with over 2300 requests that were trying to do reverse DNS on 192.168.0.10 (which is the web server~~, ~~.20~~ is the ~~DB server). Since~~ the entire 192.168.X.X block is set aside for internal private networks, chances are that you will NOT have those resolving into hostnames. The problem was that my DB server was trying to resolve all 2300 requests into a hostname before it allowed DB queries to go through.
	~~: You~~'~~ll have to restart (or shut down/kill off all MySQL processes, remove mysql.sock and your pid file) and then start MySQL up again.~~
	~~: I hope this saves someone the two hours of headache I've just gone through getting this sorted~~.

	~~Full post is~~ [http://~~www~~.~~therealms~~.~~net~~/~~forums~~/~~index~~.~~php~~/~~topic,243.0~~.html ~~here~~].		Example:
			<sql>ALTER TABLE cat_items CHANGE isInStock qtyInStock INT COMMENT 'number of pieces currently in stock (calculated from stock table)';</sql>
			* Documentation: [http://dev.mysql.com/doc/refman/5.1/en/alter-table.html ALTER TABLE Syntax]

Woozle: /* Overview */ Address Binding

2008-01-04T01:01:26Z

Overview: Address Binding

@@ Line 3: / Line 3: @@
 ==Overview==
 This page is for any loose notes on [[MySQL]] for which there isn't yet a specific page.
 ==MySQL on a DNS server==
 Some observations on 2007-09-21, from the owner/operator of a small web hosting provider. These only apply if:

Woozle: /* MySQL on a DNS server */ more info from Doug

2007-09-22T13:29:56Z

MySQL on a DNS server: more info from Doug

← Older revision		Revision as of 13:29, 22 September 2007
Line 4:		Line 4:
	This page is for any loose notes on [[MySQL]] for which there isn't yet a specific page.		This page is for any loose notes on [[MySQL]] for which there isn't yet a specific page.
	==MySQL on a DNS server==		==MySQL on a DNS server==
	Some observations on 2007-09-21, from the owner/operator of a small web hosting provider:		Some observations on 2007-09-21, from the owner/operator of a small web hosting provider. These only apply if:
			* the web sites are on a ''different machine'' from the MySQL server
			* the web site machine is connected to the MySQL server directly (typically via crossover cable), rather than going through the LAN

			What happened, and the solution (in brief):

	: Tonight, my cluster bit the dust. Every time we would restart MySQL, we would immediately be deluged with huge amounts of "unauthenticated users" hitting up the DB server. Thanks to the good folks in #MySQL on EFNet IRC, they turned me onto http://hackmysql.com/dns		: Tonight, my cluster bit the dust. Every time we would restart MySQL, we would immediately be deluged with huge amounts of "unauthenticated users" hitting up the DB server. Thanks to the good folks in #MySQL on EFNet IRC, they turned me onto http://hackmysql.com/dns
Line 19:		Line 23:
	: You'll have to restart (or shut down/kill off all MySQL processes, remove mysql.sock and your pid file) and then start MySQL up again.		: You'll have to restart (or shut down/kill off all MySQL processes, remove mysql.sock and your pid file) and then start MySQL up again.
	: I hope this saves someone the two hours of headache I've just gone through getting this sorted.		: I hope this saves someone the two hours of headache I've just gone through getting this sorted.

			Full post is [http://www.therealms.net/forums/index.php/topic,243.0.html here].

Woozle: New page: ==Navigation== computing: software: databases: engines: MySQL: notes ==Overview== This page is for any loose notes on...

2007-09-22T11:39:52Z

New page: ==Navigation== computing: software: databases: engines: MySQL: notes ==Overview== This page is for any loose notes on...

New page

==Navigation==
[[computing]]: [[software]]: [[database software|databases]]: [[database engines|engines]]: [[MySQL]]: [[MySQL notes|notes]]
==Overview==
This page is for any loose notes on [[MySQL]] for which there isn't yet a specific page.
==MySQL on a DNS server==
Some observations on 2007-09-21, from the owner/operator of a small web hosting provider:

: Tonight, my cluster bit the dust. Every time we would restart MySQL, we would immediately be deluged with huge amounts of "unauthenticated users" hitting up the DB server. Thanks to the good folks in #MySQL on EFNet IRC, they turned me onto http://hackmysql.com/dns
: For /etc/my.cnf
: After [mysqld]
: ADD this, on its own line: skip-name-resolve
: ---------------------
: If you're running your DNS on the same machine as your DB server, make SURE you add the three following lines to /etc/[[resolv.conf]]:
:: nameserver 127.0.0.1
:: nameserver 192.168.0.10
:: nameserver 192.168.0.20
: Modifying the two 192.168.x.x ranges to be the IP's of your internal private network.
: At one point, my DB server was deluged with over 2300 requests that were trying to do reverse DNS on 192.168.0.10 (which is the web server, .20 is the DB server). Since the entire 192.168.X.X block is set aside for internal private networks, chances are that you will NOT have those resolving into hostnames. The problem was that my DB server was trying to resolve all 2300 requests into a hostname before it allowed DB queries to go through.
: You'll have to restart (or shut down/kill off all MySQL processes, remove mysql.sock and your pid file) and then start MySQL up again.
: I hope this saves someone the two hours of headache I've just gone through getting this sorted.