139
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;
70
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 ...
69
The architecture of InnoDB demands the use of four basic types of info pages
Table Data Pages
Table Index Pages
Table MetaData
MVCC Data (to support Transaction Isolation and ACID Compliance)
Rollback Segments
Undo Space
Double Write Buffer (background writing to prevent reliance on OS caching)
Insert Buffer (managing changes to non-unique secondary ...
69
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` ...
54
One of the silent killers of MySQL Connections is the MySQL Packet.
First, let's figure out what a MySQL Packet is.
According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining MySQL Packets:
MySQL network communication code was
written under the assumption that
queries are always reasonably ...
37
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 ...
36
Please review my answer to this recent question. I believe the circumstances are identical.
Do not change your MySQL configuration at this point, as MySQL is not the problem -- it's only a symptom of the problem... which is that you appear to have a system with a small amount of memory and zero swap space.
Your server is not crashing "because" memory ...
34
I have good news, and bad news. The good news is, your filesystem and mysql are most likely fine... but check /var/log/syslog or equivalent to see what else was happening on your system before 10:21:05.
When the first message you posted was logged, your mysql server had already died.
120927 10:21:05 mysqld_safe Number of processes running now: 0
So, ...
32
When a Slave is read-only, it is not 100% shielded from the world.
According to MySQL Documentation on read-only
This variable is off by default. When it is enabled, the server permits no updates except from users that have the SUPER privilege or (on a slave server) from updates performed by slave threads. In replication setups, it can be useful to ...
29
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;
...
27
That is expected behavior. Refer to the docs online, but in summary:
long_query_time is the threshold for query execution time beyond which it is logged. Any queries taking longer than the threshold are logged, regardless of whether they use an index or not.
log_queries_not_using_indexes tells MySQL to additionally log all queries that do not use an index ...
26
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 ...
24
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 ...
20
One bottleneck to be aware of is the InnoDB Log Buffer. The size is set by innodb_log_buffer_size. Here is what the MySQL Documentation says about it:
The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 8MB. A large log buffer enables large transactions to run without a need to write the log to disk ...
19
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 ...
19
The two answers given from @RickJames and @drogart are essentially the remedies. (+1 for each).
Right from the error log you present, the last two lines say:
InnoDB: Error: log file ./ib_logfile0 is of different size 0 134217728 bytes
InnoDB: than specified in the .cnf file 0 268435456 bytes! `
At that point, it was evident that you set ...
18
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,
...
18
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 '...
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;
18
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 ...
16
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 ...
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 (...
16
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 ...
15
A single connection will only use a single core. (OK, InnoDB uses other threads, hence cores, for some I/O processing, but that is not significant.)
You had 3 ALTERs, so you were not using much more than 3 core's worth.
Alas, not even PARTITION uses multiple cores.
Until recently, multiple connections would max out after 4-8 cores. Percona's Xtradb (...
15
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.
15
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 ...
14
You can toggle InnoDB to store tables per file by adding innodb-file-per-table to your cnf.
Innodb really just cares about pages of data at a basic level. In fact, you can set up InnoDB to use just a raw block device with no filesystem what so ever! http://dev.mysql.com/doc/refman/5.5/en/innodb-raw-devices.html
There are conveniences to storing tables for ...
14
I have addressed this in StackOverflow back in October 2010.
Keep in mind the busiest file in the InnoDB infrastructure : /var/lib/mysql/ibdata1
This file normally houses four types of information
Table Data
Table Indexes
MVCC (Multiversioning Concurrency Control) Data
Table Metadata (List of tablespace IDs)
Running OPTIMIZE TABLE against an InnoDB table ...
14
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 ...
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. ...
Only top voted, non community-wiki answers of a minimum length are eligible
