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
