|
|
| Line 1: |
Line 1: |
| ==Navigation==
| | <hide> |
| <section begin=navbar />{{#lst:software|navbar}}: [[database software|databases]]: [[database engines|engines]]: [[MySQL]]<section end=navbar /> | | [[page type::article]] |
| | | [[thing type::software]] |
| ==Overview== | | [[software type::database engine]] |
| | [[license::libre/open source]] |
| | </hide> |
| | ==About== |
| [[MySQL]] is a [[FOSS]] database engine and server. It fills roughly the same niche as [[Microsoft SQL Server]]. | | [[MySQL]] is a [[FOSS]] database engine and server. It fills roughly the same niche as [[Microsoft SQL Server]]. |
|
| |
|
| The MySQL package (at least in Linux) comes with a number of helper utilities, including [[MySQL Administrator]] and [[MySQL Query Browser]]. The Administrator utility is useful for making backups, and the Query Browser serves as a front-end for interacting with databases. | | The MySQL package (at least in Linux) has a number of helper utilities available, including [[MySQL Workbench]] (a combination of the former [[MySQL Administrator]] and [[MySQL Query Browser]] applications). |
| ==Articles== | | ==Articles== |
| * '''Related''' | | * '''Related''' |
| Line 15: |
Line 18: |
| ** [[MySQL Administrator]] | | ** [[MySQL Administrator]] |
| ** [[MySQL Query Browser]] | | ** [[MySQL Query Browser]] |
| | | * [[/notes]]: disorganized information |
| ==Criticisms==
| | * [[/archive]]: older stuff that probably is no longer applicable |
| MySQL has been criticized by at least one user of lacking production-quality features, with version 5.0 only just starting to add them, where as other [[FOSS]] alternatives such as [[PostgreSQL]] had such features in their very earliest versions. MySQL has also been criticized for bad performance, e.g. locking entire tables when it only should have locked a row, as well as general incompatibility (Editor's note: with what?).
| |
| | |
| * '''Wikipedia''': [[Wikipedia:MySQL#Criticisms of MySQL|Criticisms of MySQL]] | |
| * '''2007-05-26''' [http://developers.slashdot.org/comments.pl?sid=236249&cid=19280725 Re:MySQL the db for people who don't understand db]
| |
| | |
| ==Files & Folders==
| |
| The [http://dev.mysql.com/doc/refman/5.1/en/installation-layouts.html Installation Layouts] manual page gives a listing of folders created by a mysql installation, but does not list all files (some of them added to existing folders) upon which a working installation is dependent.
| |
| | |
| ===RPM distributions===
| |
| (The Debian/Ubuntu setup is similar; folders marked with * indicate those found in Ubuntu and not given in the documentation.)
| |
| {|
| |
| |-
| |
| ! Directory || Contents of Directory
| |
| |-
| |
| | */etc/init.d/mysql || daemon start/stop/restart script
| |
| |-
| |
| | */etc/mysql || [[my.cnf]]: configuration file; also other configuration files
| |
| /etc/mysql/conf.d/
| |
| |-
| |
| | /usr/bin || Client programs and scripts
| |
| |-
| |
| | /usr/include/mysql || Include (header) files
| |
| |-
| |
| | /usr/lib/mysql || Libraries
| |
| |-
| |
| | /usr/sbin || The mysqld server
| |
| |-
| |
| | /usr/share/info || Manual in Info format
| |
| |-
| |
| | /usr/share/man || Unix manual pages
| |
| |-
| |
| | /usr/share/mysql || Error message and character set files; *also scripts
| |
| |-
| |
| | /usr/share/sql-bench || Benchmarks
| |
| |-
| |
| | /var/lib/mysql || Log files, databases
| |
| |}
| |
| | |
| ==Working Notes==
| |
| ===SQL error messages===
| |
| * 1271 - Illegal mix of collations for operation 'concat'
| |
| ** 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.
| |
| ===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".
| |
| ===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.)
| |
| ===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:
| |
| bind-address = 127.0.0.1
| |
| : 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:
| |
| | |
| 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>
| |
| | |
| ...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===
| |
| '''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|, ...}});
| |
| 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]
| |
| | |
| '''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>
| |
| | |
| '''changing the name of a field (column)''':
| |
| '''ALTER TABLE''' <u>tablename</u> '''CHANGE''' <u>oldname</u> <u>newname</u> <u>new_fieldtype</u>;
| |
| This also changes the field's definition, if new_fieldtype is not the same as the field's prior definition.
| |
| | |
| 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]
| |
| | |
| ==Links== | | ==Links== |
| ===Reference=== | | ===Reference=== |
