The most common cause of the MySQL server has gone away error is that the server times out and closes the connection. By default, if nothing happens, the server closes the connection eight hours later. You can change the time limit by setting the wait_timeout variable when starting mysqld. You can run mysqladmin version and check the normal running time to check that MySQL has not died. If you have a script, you only need to issue a query to enable the customer service to automatically reconnect again. In this case, you usually get the following error code (you get OS-related ):
The CR_SERVER_GONE_ERROR client cannot send a problem to the server. CR_SERVER_LOST when writing data to the server, the customer did not make an error, but it did not get a complete answer (or any answer) to the question ).
If you send incorrect or too many queries to the server, you may also get these errors. If mysqld gets a large or abnormal package, it considers the customer has an error and closes the connection. If you need a large query (for example, if you are processing a large BLOB column), you can use the-O max_allowed_packet = # option (default: 1 M) start mysqld to add query restrictions. Extra memory is allocated on demand. In this way, mysqld uses more memory only when you send a large bad query or when mysqld must return a large result!
Considerations for using MySQL 4.0.21
1. max_allowed_packet = 16 M. The default value is 1 M.This involves BLOB and so on, and usually needs to be modified.
2. I have not carefully looked at the following section, which is in manual.html of mysql. Use LONGBLOB and LONGTEXT when creating a table.BLOB TEXT
A blob or TEXT column with a maximum length of 65,535 (2 ^ 16-1) characters.
MEDIUMBLOB MEDIUMTEXT
A blob or TEXT column with a maximum length of 16,777,215 (2 ^ 24-1) characters.
LONGBLOB LONGTEXT
A blob or TEXT column with a maximum length of 4,294,967,295 or 4 GB (2 ^ 32-1) characters.
3. for Chinese characters, the MySQL server uses the default latin1 encoding and does not require any settings.Linux has not been tested on windows. The database Driver com. mysql. jdbc. Driver (org. gjt. mm. mysql. Driver is invalid in this method)
Jdbc: mysql: // localhost/risenet? UseUnicode = true & amp; characterEncoding = gb2312
Do not directly use "&" in xml, but use the conversion character in XML: & amp;
A MySQL client can connect to the mysqld server in two different ways: Unix socket, which uses a file in the file system (default: "/tmp/mysqld. or TCP/IP, which is connected by a port number. Unix sockets are faster than TCP/IP, but only servers used to connect to the same computer. If you do not specify a host name or if you specify a special host name localhost, use a Unix socket. Error (2002) Can't connect... it usually means that no MySQL server is running on the system or you are using an incorrect socket file or TCP/IP Port when trying to connect to the mysqld server. Started by checking (using ps) A process named mysqld on your server! If there is no mysqld process, you should start one. See 4.15.2 about how to start the MySQL server. If a mysqld process is running, you can check the server by trying these different connections (of course, the port number and socket path name may be different in your installation ):
Shell> mysqladmin version
Shell> mysqladmin variables
Shell> mysqladmin-h 'hostname' version variables
Shell> mysqladmin-h 'hostname' -- port = 3306 version
Shell> mysqladmin-h 'IP for your host' version
Shell> mysqladmin -- socket =/tmp/mysql. sock version
Note that the hostname command uses the reverse quotation mark (') instead of the forward quotation mark ('). These causes the hostname output (that is, the current host name) to be replaced by the mysqladmin command. This may cause the Can't connect to local MySQL server error because mysqld is not running. You are running on a system using MIT-pthreads. If you are running on a system without native threads, mysqld uses the MIT-pthreads package. See the Operating System Supported by MySQL 4.2. However, MIT-pthreads does not support Unix sockets. Therefore, when you connect to a server, you must specify the host name explicitly on such a system. Try to use this command to check the connection to the server:
Shell> mysqladmin-h 'hostname' version
Someone deleted the Unix socket used by mysqld ("/tmp/mysqld. sock" by default "). You may have a cron task that deletes a MySQL socket (for example, a task that deletes an old file from the "/tmp" directory ). You can always run mysqladmin version and check whether the socket mysqladmin is trying to use exists. In this case, the solution is to delete the cron task without deleting "mysqld. sock" or placing the socket somewhere else. You can use this command to specify a different Socket location during MySQL Configuration:
Shell>./configure -- with-unix-socket-path =/path/to/socket
You can also use the -- socket =/path/to/socket option to start safe_mysqld and set the environment variable MYSQL_UNIX_PORT to the socket path name before starting your MySQL client. You can use the -- socket =/path/to/socket option to start the mysqld server. If you change the socket path name of the server, you must also notify the MySQL client about the new path. You can set the environment variable MYSQL_UNIX_PORT to the socket path name or use the socket path name as the customer's parameter. You can use this command to test the socket:
Shell> mysqladmin -- socket =/path/to/socket version
You are using Linux and the thread is dead (the core is dumped ). In this case, you must kill other mysqld threads (for example, you can use the mysql_zap script before starting a new MySQL Server ). See Section 18.1. What if MySQL always crashes. If you get the error Can't connect to MySQL server on some_hostname, you Can try the following steps to find out what the problem is: run telnet your-host-name TCP/IP-port-number and press enter several times to check whether the server is running properly. If there is a MySQL running on this port, you should get a response containing the version number of the running MySQL server. If you get an error similar to telnet: Unable to connect to remote host: Connection refused, no server runs on the port in use. Connect to the mysqld daemon on the local machine and use mysqladmin variables to check the TCP/IP port (variable port) configured for mysqld ). Check that your mysqld server is not started with the -- skip-networking option.
If you get an error like this:
Host 'hostname' is blocked because of specified connection errors.
Unblock with 'mysqladmin flush-hosts'
This means that mysqld has obtained a large number of connection requests that are interrupted in the middle of the host 'hostname' (max_connect_errors. After max_connect_errors failed requests, mysqld identified an error (like a hacker attack) and blocked the site from further connection until someone executed the command mysqladmin flush-hosts. By default, mysqld blocks a host after 10 connection errors. You can easily adjust the server by starting it like this:
Shell> safe_mysqld-O max_connect_errors = 10000 &
Note: If you get this error message for a given host, you should first check whether the TCP/IP connection of the host is correct. If your TCP/IP connection is not running,
Adding the max_connect_errors variable value will not help you!
If the Too connector connections error is returned when you try to connect to MySQL, it means that max_connections clients have connected to the mysqld server.
If you need more connections than the default value (100), restart mysqld and use a larger max_connections variable value.
Note that mysqld actually allows (max_connections + 1) client connections. The last connection is reserved for a user with the Process permission. By not granting this permission to general users (they should not need it), an administrator can log on and use show processlist to find out what may cause an error. See the 7.21 SHOW syntax (get the table and column information ).
If you issue a query and get an error similar to the following:
Mysql: Out of memory at line 42, 'malloc. c'
Mysql: needed 8136 byte (8 k), memory in use: 12481367 bytes (12189 k)
ERROR 2008: MySQL client ran out of memory
Note that the error points to MySQL. The reason for this error is that the customer does not have enough memory to store all results.
To solve this problem, first check whether your query is correct. Is it reasonable to return so many rows? If so,
You can use mysql -- quick to retrieve a set of results using mysql_use_result. This places less burden on the client (but more servers ).
When a MySQL client or mysqld server obtains a Packet longer than max_allowed_packet, it issues a Packet too large error and terminates the connection. If you are using a mysql client, you can use mysql -- set-variable = max_allowed_packet = 8 m to specify a larger buffer to start the client program. If you are using other customers (such as DBI) that do not allow you to specify the maximum package size, you need to set the package size when you start the server. You can use the command line option of mysqld to set max_allowed_packet to a larger size. For example, if you want to store a full-length BLOB into a table, you need to use the -- set-variable = max_allowed_packet = 24M option to start the server.
This error occurs when the temporary memory table becomes larger than tmp_table_size. To avoid this problem, you can use the-O tmp_table_size = # option of mysqld to increase the size of the temporary table, or use the SQL option SQL _BIG_TABLES before you issue a query in question. See the 7.25 set option syntax. You can also use the -- big-tables option to start mysqld. This is exactly the same as using SQL _BIG_TABLES for all queries.
If You get Commands out of sync in your customer code; You can't run this command now, You are calling the customer function in the wrong order!
This may happen. For example, if you are using mysql_use_result () and try to execute a new query before you have called mysql_free_result.
If you try to execute two queries that return data between mysql_use_result () or mysql_store_result (), it may also happen.
If you get the following error:
Found wrong password for user: 'Some _ user @ some_host '; Ignoring user
This means that when mysqld is started or when it loads the permission table again, it finds an entry with an invalid password in the user table.
As a result, the entries are ignored by the permission system. Possible causes and resolutions:
You may be running a new version of mysqld with an old user table. You can run mysqlshow mysql user to check whether the password field contains less than 16 characters. If so, you can run the scripts/add_long_password script to correct this situation. The user has an old-fashioned password (8 characters long) and you didn't use the -- old-protocol option to start mysqld. Use a new password to update the user in the user table or use -- old-protocol to restart mysqld. You have not used the PASSWORD () function to specify a PASSWORD in the user table. Use mysql to update users in the user table with a new password. Make sure to use the PASSWORD () function:
Mysql> update user set password = PASSWORD ('your password') where user = 'xxx ';
If you get the error Table 'xxx' doesn' t exist or Can't find file: 'xxx' (errno: 2 ), this means that no table named xxx exists in the current database.
Note that because MySQL uses directories and files to store databases and tables, the database and table name are case sensitive! (On Win32, the database and table names are not case sensitive, but the same case must be used for reference to all tables in the query !)
You can use show tables to check which table you have in the current database. See 7.21 SHOW syntax (get table and column information ).
When a disk overflows, MySQL does the following:
It checks every minute to check whether there is enough space to write data to the current row. If there is enough space, it continues as if something had happened. Every 6 minutes, it writes a warning about disk overflow to the log file.
To alleviate this problem, you can take the following actions: continue, you only need to release enough free disk space to insert all records. To discard the thread, you must send a mysqladmin kill to the thread. During the next disk check, the thread will be dropped (within 1 minute ). Note that other threads may be waiting for tables that cause the "disk overflow" condition. If you have several "locked" threads, killing the thread that is waiting for the disk overflow condition will allow other threads to continue.
In general, mysql customers are interactively used, like this:
Shell> mysql database
However, you can also put your SQL command in a file and tell mysql to read its input from the file. To do this,
Create a text file "text_file", which contains the command you want to execute. Call mysql as follows:
Shell> mysql database <text_file
You can also start a text file with a USE db_name statement. In this case, it is unnecessary to specify the database name on the command line:
Shell> mysql <text_file
See the overview of different MySQL programs in 12.1.
MySQL uses the value of the TMPDIR environment variable as the path name of the directory for storing temporary files. If TMPDIR is not set, MySQL uses the system default value, which is usually "/tmp" or "/usr/tmp ". If the file system containing your temporary file directory is too small, you should edit safe_mysqld to set TMPDIR to a file system with enough space! You can also use mysqld's -- tmpdir to select a project to set a temporary directory. MySQL creates all temporary files with "hidden files. This ensures that if mysqld is terminated, temporary files will also be deleted. The disadvantage of using implicit files is that you will not see a large temporary file that fills up the file system where the temporary file directory is located. When sorting (order by or group by), MySQL usually uses one or two temporary files. The maximum disk space is:
(Length of stored items + sizeof (Database pointer ))
* Number of matched rows
* 2
Sizeof (Database pointer) is usually 4, but in the future it may increase for tables that are indeed large.
For some SELECT queries, MySQL also creates temporary SQL tables. These are not implicitly named in the "SQL _ *" format.
Alter table and optimize table create a temporary TABLE in the same directory of the original database TABLE.
If you have this problem, in fact anyone can delete the MySQL communication socket "/tmp/mysql. sock ", in most Unix versions, you can set sticky (t) bits for it to protect your"/tmp "file system. Log On As root and do the following:
Shell> chmod + t/tmp
This protects your "/tmp" file system so that files can only be deleted by their owner or root user.
You can run ls-ld/tmp to check whether the sticky bit is set. If the last permit bit is t, this bit is set.
See how the 6.6 permission system works. In addition, the reason for the Access denied error caused by 6.13 is particularly important.
MySQL Server mysqld can be started and run by any user. To change mysqld to user_name, a Unix user, you must do the following:
If it is running, stop the server (use mysqladmin shutdown ).
Change the database directory and file so that user_name has the permission to read and write files (you may need to be a Unix root User ):
Shell> chown-R user_name/path/to/mysql/datadir
If the directory or file in the MySQL data directory is a symbolic link, you will also need to follow those links and change the directories and files they direct. Chown-R cannot follow the symbolic link.
Start the server as user_name, or if you are using MySQL 3.22 or later, start mysqld as a Unix root user and use the -- user = user_name option, mysqld will switch to run as a Unix user_name user before accepting any connection. If you use the mysql. server script to start mysqld when the system is restarted, you should edit mysql. server and run mysqld with user user_name using su, or call mysqld using the -- user option. (It is necessary not to change safe_mysqld .) Now, your mysqld process should be running properly as the Unix user_name user. Although one thing remains unchanged: the content of the permission table. By default (after the script mysql_install_db installation permission table is run), the MySQL user root is the only user with the permission to access the mysql database or create or discard the database. Unless you change those permissions, they will remain. When you log on as a Unix user rather than the root user, this should not prevent you from accessing MySQL as the MySQL root User; you only need to specify the-u root option for the client program. Note that-u root is provided on the command line to access MySQL as root. It does not matter if MySQL is run as Unix root or other Unix users. The access permissions and user names of MySQL are completely separated from those of Unix users. The only thing related to the Unix user name is that if you do not provide a-u option when calling a client program, the customer will try to use your Unix login name as your MySQL user name for connection. If your Unix machine is not secure, you should at least put a password for the MySQL root user in the access table. Otherwise, any user with an account on that machine can run mysql-u root db_name and do whatever he prefers.
If you forget the password of the root user of MySQL, you can use the following process to restore it.
By sending a kill (not kill-9) to the mysqld server, you can disable the mysqld server. Pid is saved in A. pid file, usually in the MySQL database directory:
Kill 'cat/mysql-data-directory/hostname. Pi'
You must be a UNIX root user or the same user on the running server.
Use the -- skip-grant-tables option to restart mysqld.
Use mysql-h hostname mysql to connect to the mysqld server and use a GRANT command to change the password. See section 7.26 GRANT and REVOKE syntax. You can also use mysqladmin-h hostname-u user password 'new password. Use mysqladmin-h hostname flush-privileges or the SQL command FLUSH PRIVILEGES to load the permission table.
If you have issues related to file license, for example, if mysql sends the following error message when creating a table:
ERROR: Can't find file: 'path/with/filename. frm' (Errcode: 13)
The environment variable UMASK may be set incorrectly when mysqld is started. The default umask value is 0660. You can start safe_mysqld to change its behavior as follows:
Shell> UMASK = 384 # = 600 in octal
Shell> export UMASK
Shell>/path/to/safe_mysqld &
If you get ERROR from MySQL '... 'Not found (errno: 23), Can't open file :... (errno: 24) or any other error with errno 23 or errno 24, it means that you have not allocated enough file descriptors for MySQL. You can use the perror utility to get the description of the error number:
Shell> perror 23
File table overflow
Shell> perror 24
Too program open files
The problem here is that mysqld is trying to open too many files at the same time. You can also tell mysqld not to open so many files at a time, or increase the number of file descriptors that mysqld can obtain. To tell mysqld to keep fewer files open at a time, you can use the-O table_cache = 32 option of safe_mysqld (the default value is 64) to make the table buffer smaller. Reducing the max_connections value also reduces the number of opened files (the default value is 90 ). To change the number of file descriptors available for mysqld, modify the safe_mysqld script. The script contains a comment line ulimit-n 256. You can delete the '#' character to remove the comment of the row and change the number 256 to the number of available file descriptors of mysqld. Ulimit can increase the number of file descriptors, but it can only impose restrictions on the operating system. If you need to increase the OS limit on the number of file descriptors available for each process, see your operating system documentation. Note that if you run the tcsh shell, ulimit will not work! When you request the current limit, tcsh also reports incorrect values! In this case, you should use sh to start safe_mysqld!
The format of a DATE value is 'yyyy-MM-DD '. Other formats are not allowed according to ansi SQL. You should use this format in the WHERE clause of the UPDATE expression and SELECT statement. For example:Mysql> SELECT * FROM tbl_name WHERE date> = '2017-1997 ';
For convenience, MySQL automatically converts a date to a number (and vice versa) if the date is used in the numeric context ). When a DATE is updated and compared with a TIMESTAMP, DATE, or DATETIME column in a WHERE clause, it is flexible enough to allow a "loose" string format. (Loose format means that any punctuation character is used as a delimiter between parts. For example, '2017-08-15 'and '2017 #08 # 15' are equivalent .) MySQL can also transform a string that does not contain a delimiter (for example, '20140901') if it is used as a date. The special date '2017-00-00 'can be stored and retrieved as '2017-00-00. When MyODBC is used to use a '2017-00-00 'date, it is automatically converted to NULL in MyODBC 2.50.12 and later versions because ODBC cannot process this date.
Because MySQL implements the preceding transformation, the following statements can work:
Mysql> insert into tbl_name (idate) valuees (19970505 );
Mysql> insert into tbl_name (idate) VALUES ('20140901 ');
Mysql> insert into tbl_name (idate) VALUES ('97-05-05 ');
Mysql> insert into tbl_name (idate) VALUES ('2017. 66661 ');
Mysql> insert into tbl_name (idate) VALUES ('2014, 1997 05 05 ');
Mysql> insert into tbl_name (idate) VALUES ('2017-00-00 ');
Mysql> SELECT idate FROM tbl_name WHERE idate> = '2017-1997 ';
Mysql> SELECT idate FROM tbl_name WHERE idate >=19970505;
Mysql> SELECT mod (idate, 100) FROM tbl_name WHERE idate >=19970505;
Mysql> SELECT idate FROM tbl_name WHERE idate> = '2013 ';
However, the following will not work:
Mysql> SELECT idate FROM tbl_name where strcmp (idate, '2013') = 0;
STRCMP () is a string function, so it converts idate into a string and performs string comparison. It does not convert '200' to a date and implements date comparison.
Note: MySQL does not check whether the date is correct. If you store an incorrect date, such as '2017-2-31 ', the error date will be stored. If the DATE cannot be converted to any reasonable value, a value of 0 is stored in the DATE field. This is mainly a speed issue and we think that checking the date is the responsibility of the application, not the server.
If you have a problem, select now () returns the value in GMT instead of your local time, you must set the TZ environment variable for your current time zone. This should be done in the server running environment, for example, in safe_mysqld or mysql. server.
Without worry, MySQL search is case-insensitive (although some character sets are never case-insensitive, such as Czech ). This means that if you search by col_name LIKE 'a % ', you will get all column values starting with a or. If you want to make this search case sensitive, use INDEX (col_name, "A") = 0 to check A prefix. Or if the column value must be "A", use STRCMP (col_name, "A") = 0.
Simple comparison operations (> =,>, =, <, <=, sort, and aggregation) are based on the "Sort value" of each character ". Characters with the same sort value (such as E, e, and 'E) are considered to be the same character!
LIKE is compared on the upper-case values of each character (E = e but E <> 'E ).
If you want a column to always be case sensitive, declare it as BINARY. See 7.7 create table syntax.
If you use Chinese data encoded in the so-called big5, you need to make all the character columns BINARY, which is feasible because the order of big5 encoding characters is based on the ASCII code order.
The concept of NULL value is a common cause of confusion among SQL beginners. They often think that NULL is the same as an empty string. No! For example, the following statements are completely different:
Mysql> insert into my_table (phone) VALUES (NULL );
Mysql> insert into my_table (phone) VALUES ("");
The two statements insert values into the phone column, but the first statement inserts a NULL value and the second statement inserts an empty string. The first one can be thought of as "the phone number is unknown", while the second one can mean "she has no phone number ". In SQL, the NULL value is always FALSE when any other value or even NULL value is compared ). An expression that contains NULL always generates a NULL value, unless it is specified in the document containing operators and functions in the expression. In the following example, all columns return NULL:
Mysql> select null, 1 + NULL, CONCAT ('invisable', NULL );
If you want to find a column whose value is NULL, you cannot use = NULL for testing. The following statement does not return any rows because expr = NULL is false for any expression:
Mysql> SELECT * FROM my_table WHERE phone = NULL;
To search for a NULL value, you must use the is null test. The following example shows how to find a NULL phone number and a blank phone number:
Mysql> SELECT * FROM my_table WHERE phone is null;
Mysql> SELECT * FROM my_table WHERE phone = "";
In MySQL, just like many other SQL servers, you cannot index columns with NULL values. You must declare that such a column is not null, and you cannot insert NULL to the index column. When you use load data infile to read DATA, use ''to update the empty column. If you want to include a NULL value in a column, you should use \ N in a text file. The literal 'null' can also be used in some cases. See the 7.16 load data infile syntax. When order by is used, the NULL value is first displayed. If you use DESC to sort data in descending order, the NULL value is finally displayed. When group by is used, all NULL values are considered equal. To help with NULL processing, you can use the is null and is not null operators and IFNULL () functions.
For certain column types, NULL values are specially processed. If you insert NULL into the first TIMESTAMP column of the table, the current date and time are inserted. If you insert NULL into an AUTO_INCREMENT column, insert the next number in the sequence.
You can use an alias to reference columns in the group by, order by, or HAVING section. An alias can also be used to get a better name for a column:
Select sqrt (a * B) as rt FROM table_name GROUP BY rt HAVING rt> 0;
SELECT id, COUNT (*) AS cnt FROM table_name group by id HAVING cnt> 0;
SELECT id AS "Customer identity" FROM table_name;
Note that your ansi SQL does not allow you to reference an alias in a WHERE clause. This is because the column value may not end when the WHERE code is executed. For example, the following query is invalid: SELECT id, COUNT (*) AS cnt FROM table_name WHERE cnt> 0 group by id;
The WHERE statement is executed to determine which rows should be included in the group by section, while HAVING is used to determine which rows in the result set should be used only.
Because MySQL does not support sub-selection or multiple tables in the DELETE statement, you should use the following method to DELETE rows from two associated tables:
SELECT rows based on a WHERE condition in the primary table.
DELETE rows in the primary table based on the same condition.
Delete from related_table WHERE related_column IN (selected_rows)
If the total number of characters in the related_column query exceeds 1,048,576 (max_allowed_packet by default), you should divide it into smaller parts and execute multiple DELETE statements. If related_column is an index, deleting only-related_column IDs at a time may lead to the fastest DELETE operation. If related_column is not an index, the speed is independent of the number of parameters IN the IN clause.
If you have a complex query that involves multiple tables but does not return any rows, you should use the following process to find out what is wrong with your query:
EXPLAIN test the query and check whether you can find something that is clearly wrong. See 7.22 EXPLAIN syntax (obtain information about a SELECT statement ).
Select only the fields used in the WHERE clause. Delete a table from the query at a time until it returns some rows. If the table is large, it is a good idea to use LIMIT 10 for queries.
Make a SELECT statement for the columns that match a row and delete the table after the query is performed. If you compare FLOAT or DOUBLE columns with decimal numbers, you cannot use = !. This problem is common in most computer languages because floating point values are not accurate values.
Mysql> SELECT * FROM table_name WHERE float_column = 3.5;
->
Mysql> SELECT * FROM table_name WHERE float_column between 3.45 and 3.55;
In most cases, changing FLOAT to a DOUBLE will correct it!
If you still cannot find out what the error is, create a minimal test that runs mysql test <query. SQL to display your problem. You can use mysqldump -- quick database tables> query. SQL creates a test file, edits the file in an editor, deletes some Insert rows (if there are too many of these statements), and adds your selection statement to the end of the file. You still have problems with the test. You can do this:
Shell> mysqladmin create test2
Shell> mysql test2 <query. SQL
Use mysqlbug to mail test files to the mysql@lists.mysql.com.
If alter table died of the following error:
Error on rename of './database/name. frm' to'./database/B-a.frm '(Errcode: 17)
The problem may be that MySQL crashed in the previous alter table and left an old database TABLE named "A-xxx" or "B-xxx. In this case, go to the MySQL data directory and delete all files whose names start with A-or B. (You can move them to another place rather than delete them ). Alter table is used to create A new TABLE named "A-xxx" as required.
Copy all rows from the old table to "A-xxx ".
The old table is renamed "B-xxx ".
"A-xxx" is renamed as the name of your old table.
"B-xxx" is deleted.
If some renaming operations fail, MySQL tries to restore the changes. If the error is serious (of course, this should not happen .), MySQL may leave an old table "B-xxx", but you should restore your data after you change the name.
The main point of SQL is to abstract applications to avoid data storage formats. You should always specify the order in which you want to retrieve data. For example:
SELECT col_name1, col_name2, col_name3 FROM tbl_name;
The columns are returned in the order of col_name1, col_name2, and col_name3, while:
SELECT col_name1, col_name3, col_name2 FROM tbl_name;
Columns are returned in the order of col_name1, col_name3, and col_name2.
In an application, you should never use SELECT * to retrieve columns based on their locations, because the order of returned columns is never guaranteed; A simple change to your database may cause dramatic failure of your application.
In any case, if you want to change the column order, you can do this:
Create a new table in the correct column order.
Execute insert into new_table SELECT fields-in-new_table-order FROM old_table.
Delete or rename old_table.
Alter table new_table RENAME old_table.
# Total number of connection queues
Back_log = 200
Max_allowed_packet = 2 M
# The total number of opened table caches to avoid overhead caused by frequent opening of data tables
Table_cache = 512
# Buffer required for sorting by each thread
Sort_buffer_size = 4 M
# Buffer required by each thread to read the index
Read_buffer_size = 4 M
# Cache required for re-sorting when the MyISAM table changes
Myisam_sort_buffer_size = 64 M
# Number of cache reusable threads
Thread_caching = 128
# Query result Cache
Query_cache_size = 128 M
# Set the timeout time to avoid persistent connections
Set-variable = wait_timeout = 60
# Maximum number of concurrent threads, cpu x 2
Thread_concurrency = 4
# Record slow queries, and then optimize slow queries one by one
Log-slow-queries = slow. log
Long_query_time = 1
# Disable unnecessary table types. Do not add this type if necessary.
Skip-innodb
Skip-bdb
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
