146

The valid syntax is close to your second try, but you need to escape the column names with backticks not with single quotes: ALTER TABLE `blog` CHANGE COLUMN `read-more` `read_more` VARCHAR(255) NOT NULL;


109

A very good thread on this subject is to be found here and also here. The definitive guide for MySQL is, of course, the documentation, to be found here. In the SQL 2003 standard there are 5 different referential actions: CASCADE RESTRICT NO ACTION SET NULL SET DEFAULT To answer the question: CASCADE ON DELETE CASCADE means that if the parent record is ...


70

I previously addressed this issue in: Cannot open table mysql/innodb_index_stats These tables are created for you when you install MySQL 5.6. However, upgrading from MySQL 5.5 does not invoke the creation of these tables. Here are the scripts to create them manually: innodb_index_stats USE mysql; CREATE TABLE `innodb_index_stats` ( `database_name` ...


39

The slickest way to shutdown mysql when it does that is simply to run mysqladmin -uroot -p -h127.0.0.1 --protocol=tcp shutdown Here is why: The mysql service file (/etc/init.d/mysql) relies on the presence of the socket file. Historically speaking, going way back to MySQL 4.0, the socket file sometimes disappears inexplicably. This hampers a standard ...


32

To expand on @MitchWheat's answer (+1 for directly answering first): ANALYZE TABLE examines key distribution and stores them in INFORMATION_SCHEMA.STATISTICS. OPTIMIZE TABLE performs ANALYZE TABLE after doing some table compression. The equivalent of OPTIMIZE TABLE mydb.mytable; if the table was MyISAM is this: ALTER TABLE mydb.mytable ENGINE=MyISAM; ...


29

From the MySQL 5.5 manual: You cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column. Therefore, what you want to achieve will work in MySQL 5.5 if you add a TIMESTAMP column instead of a DATE column. The ...


27

To answer your immediate question, how to count rows of a subquery, the syntax is as follows: SELECT COUNT(*) FROM (subquery) AS some_name; The subquery should immediately follow the FROM keyword. (In MySQL it is also mandatory to assign a name to a subquery of this kind (it is actually called a derived table), which is why you can see the AS some_name ...


25

Data-wise, tinyint(1), tinyint(2), tinyint(3) etc. are all exactly the same. They are all in the range -128 to 127 for SIGNED or 0-255 for UNSIGNED. As other answers noted the number in parenthesis is merely a display width hint. You might want to note, though, that application=wise things may look different. Here, tinyint(1) can take a special meaning. For ...


24

So I'm answering this question almost 4 years late: InnoDB file formats were conceived at a time when InnoDB was independent of the MySQL Server (for example: MySQL 5.1 could run two different versions of InnoDB). The reason why you would not want to run Barracuda (in 2012) is that it could reduce flexibility in downgrading MySQL (i.e. after a failed ...


24

If the MySQL Debian-7 minimal cannot use local_infile, look around all the make files used for compiling to see if it is disabled by default or if local_infile is enabled for the Debian-7. Before taking that kind of time, please run the following: SHOW GLOBAL VARIABLES LIKE 'local_infile'; SET GLOBAL local_infile = 'ON'; SHOW GLOBAL VARIABLES LIKE '...


22

You have to use the mysql client to reload mysql -u root -p -Ddatabasename < /home/databasename_bkup.sql Another way to reload would be mysql -u root -p -Ddatabasename then from the MySQL prompt, do this mysql> source /home/databasename_bkup.sql If you would like the mysqldump to drop and recreate the database for you, create the dump like ...


20

It's OK to max out the max_allowed_packet to 1G. Whenever a MySQL Packet is constructed, it will not jump to 1G from the start. Why? First you need to know what a MySQL Packet. Page 99 of the Book explains it in paragraphs 1-3 as follows: MySQL network communication code was written under the assumption that queries are always reasonably short, ...


20

This variable controls whether binary logging should trust the stored function creators for not to create stored functions that will cause unsafe events. Eg. having UUID functions. This has been explained well in documentation: When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise,...


19

View definitions are stored in the information schema: SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'yourviewname'


18

ALTER TABLE `blog` CHANGE `read-more` `read_more` VARCHAR(255) NOT NULL; Above mentioned query is correct and there is no need to use "column" keyword and quotes around table and column name if you are using mysql database: ALTER TABLE blog CHANGE read-more read_more VARCHAR(255) NOT NULL;


17

Each connection carries the load of per-connection buffers as set by these parameters join_buffer_size sort_buffer_size read_buffer_size read_rnd buffer_size Changing the number of connections increases the amount of memory each connection can demand to this : (join_buffer_size+sort_buffer_size+read_buffer_size +read_rnd buffer_size) X max_connections I ...


17

There is a setting that was introduced in MySQL 5.5.30 : innodb_print_all_deadlocks When this option is enabled, information about all deadlocks in InnoDB user transactions is recorded in the mysqld error log. Otherwise, you see information about only the last deadlock, using the SHOW ENGINE INNODB STATUS command. An occasional InnoDB deadlock is not ...


17

Actually exists now for Maria DB 10.219 ALTER TABLE test ADD COLUMN IF NOT EXISTS column_a VARCHAR(255); Bonus, it works for MODIFY as well ALTER TABLE test MODIFY IF EXISTS column_a VARCHAR(255);


16

Depends on your version of MySQL and storage engine but in general: OPTIMIZE TABLE Analyzes table, stores the key distribution for a table, reclaims the unused space and defragments the data file. ANALYZE TABLE Only analyzes table and stores the key distribution.


16

There is not. Unless (until) one develops it (MySQL is open-source, anyone can contribute.) The ANSI/ISO SQL WITH keyword is used to define Common Table Expressions (CTEs) and it simplifies complex queries with one or several nested references. It's available in Oracle, Postgres, SQL-Server, DB2 but not in MySQL. The final query may have references (...


15

Ok, found about this in the documentation itself. This was a change introduced in mysql 4.1 so that the earlier password lengths of 16 characters and newer password lengths of 40 characters could be simultaneously supported. The Password column was made 41 bytes (chars) long, and the newer passwords would begin with a mandatory * to identify them. From the ...


14

A tinyint(1) can hold numbers in the range -128 to 127, due to the datatype being 8 bits (1 byte) - obviously an unsigned tinyint can hold values 0-255. It will silently truncate out of range values: mysql> create table a -> ( -> ttt tinyint(1) -> ); Query OK, 0 rows affected (0.01 sec) mysql> insert into a values ( 127 ); ...


13

Would you believe... SELECT col1, MIN(col2), MAX(col2), MIN(col3), MAX(col3) FROM table1 GROUP BY col1; ...? each row includes the first value of col2 and col3 for each unique value of col. That assertion is not exactly true. That may be what you're seeing, but do not assume this to be meaningful and do not write code based on this observation. ...


13

You said I heard that we can retrieve the data by creating a new table same as the old table with storage engine as innodb or myisam Whoever told you that should have told you to enable binary logs as @jynus commented. The BLACKHOLE Storage Engine does not store data at all. It is a special storage engine used in very meticulous setups. EXAMPLE #1 : Star ...


12

In MySQL 5.5.16 and later, you can use RESET SLAVE ALL to do all that RESET SLAVE does and reset the connection parameters from the memory, this way it doesn't require a mysqld restart.


12

What you need is a breakdown by user and hostname along with a total SELECT IFNULL(usr,'All Users') user,IFNULL(hst,'All Hosts') host,COUNT(1) Connections FROM ( SELECT user usr,LEFT(host,LOCATE(':',host) - 1) hst FROM information_schema.processlist WHERE user NOT IN ('system user','root') ) A GROUP BY usr,hst WITH ROLLUP; This will handle host ...


12

This error can occur when you attempt to import a file into MySQL Server 5.6.4 and earlier, when the database was created on and exported from MySQL 5.6.5 or later. As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only ...


12

Put the date into quotation marks: INSERT INTO DDD(datecolumn, open, high, low, close, volume) VALUES('2011-05-26', 12.09, 13.31, 12.05, 13.09, 1293441) MySQL will have silently done some mathematics with 2011-05-26 and got its knickers in a twist. It does warn you though: mysql> insert into d VALUES(2011-05-26); Query OK, 1 row affected, 1 warning (...


12

The two nested selects in your query are called derived tables. A derived table is not meant to be correlated with other datasets participating in the query, hence outer references to them in the nested query are not allowed. One way to resolve the issue is to rewrite your query so as to move the offending select to the context where correlation is allowed. ...


11

I think your problem boils down to a misalignment of grants. When you run this query SELECT COUNT(1) column_count FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user'; You should the following number If you get 43, MySQL 5.6 If you get 42, MySQL 5.5 If you get 39, MySQL 5.1 If you get 37, MySQL 5.0 It simply means you forgot ...


Only top voted, non community-wiki answers of a minimum length are eligible