We’re rewarding the question askers & reputations are being recalculated! Read more.

Hot answers tagged

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;


77

Reposting my answer to a similar question regarding SQL Server: In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause. So, to answer your question: MySQL ...


67

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` ...


58

You can let mysqldump create the dump in such a way that it does not create or select the database. EXAMPLE : You are dumping the database db1 and loading it into database db2 This will put in the CREATE DATABASE and the USE commands in the dump mysqldump -u... -p... --routines --triggers --databases db1 > /root/db1.sql This will not put in the ...


39

You need the history of InnoDB to understand why. Here it goes: WAR STORY InnoDB and the query cache are in a constant state of war. InnoDB tends to be very heavy-handed when inspecting changes in the InnoDB Buffer Pool and then crosschecking the Query Cache for the same changes. PEACE TREATY Before MySQL 5.0, the query cache was disabled for InnoDB. Now,...


29

It will be handled immediately on mysql startup. You do not have to wait for a mysql restart. First, set expire_logs_days to be 10 in /etc/my.cnf [mysqld] log-bin server-id=1 binlog-do-db=foodb1 binlog-do-db=foodb2 expire_logs_days=10 Next, log in to mysql and run this PURGE BINARY LOGS BEFORE (date(now()) + interval 0 second - interval 10 day); Note ...


26

The order of the rows in the absence of ORDER BY clause may be: different between any two storage engines; if you use the same storage engine, it might be different between any two versions of the same storage engine; Example here, scroll down to "Ordering of Rows". if the storage engine version is the same, but MySQL version is different, it might be ...


20

I have been banging my head against this error today on OSX Yosemite with MySQL 5.7 recently updated with Homebrew. Following suggestions on StackOverflow and elsewhere, I hunted around after my.cnf files all of which specified bind-address=0.0.0.0. I even removed and reinstalled MySQL following these instructions and then reinstalled using brew install ...


19

I provided links to tutorials. Just keep mind that on Ubuntu, the my.cnf file is in /etc/mysql/my.cnf and not in /etc/my.cnf like in the howtoforge tutorial. In my setup, I didn't use FLUSH TABLES WITH READ LOCK; on the master. If your master server has a lot of write activity, you may need to lock your tables by running that command before backing up. ...


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;


14

Both excel at a simple task like this. If you end up having big queries where you search for entities that share many attributes ("relational division"), I would expect PostgreSQL at an advantage for its superior index handling. In particular, multiple joins can be combined with bitmap index scans - a feature that is not present in MySQL. It has an "...


12

SLAVE If your Slaves are not Masters, then Slaves do not need binary logging at all. You can put a cap on the amount of relay log space accumulated by a Slave. In order to throttle relay logs at 4G, add relay_log_space_limit to /etc/my/.cnf on every Slave [mysqld] relay_log_space_limit=4G and restart mysql If you cannot set this, at least you should have ...


11

Cardinality Even with high cardinality, the tipping point used by the MySQL Query Optimizer is either the key distribution or the storage engine. Back on November 13, 2012, I discussed how lopsided keys can make the Query Optimizer choose different indexes (sometime not choose and index at all) : Must an index cover all selected columns for it to be used ...


10

Insertion is unordered, chaotic, on arrival. The index which is created does have order where elements are inserted in proper location in the linked list which is the index. Think of a triply linked list for an index, where you have a forward moving link from one index element to the next, a backward looking link for the traversal and integrity purposes, ...


10

IMHO You do not need to physically split it up. Yet, it would be nice to cache it. If the users table uses the MyISAM Storage Engine, you have a nice advantage. Since MyISAM only caches indexes, you could do two things You could create a custom key cache just to load MyISAM index for the users table only You could index the username and password to force ...


9

Rolando's answer worked for me with some additions. I had the same problem, with these 5 tables showing via SHOW TABLES, but SELECT or other operations on the table resulted in table not found. To resolve the issue, using Rolando's answer, I needed to: DROP TABLE <tablename> -- all 5 tables In the file system, delete the remaining .ibd files (the ....


8

if you put count(*), count(1) or count("test") it will give you the same result because mysql will count the number of rows, for example: select count(fieldname) from table; will display the same result that select count(*) from table; or select count(1) from table mysql> select * from language; +-------------+----------+---------------------+ | ...


8

I have a blog post explaining why this is here. The short version: The query cache causes scalability issues on multi-core machines. So it is now disabled by default.


7

Using a trigger You can't use functions as defaults in MySQL, except for CURRENT_TIMESTAMP for TIMESTAMP columns


7

I was having this issue, and thought I'd mention that running "mysql_upgrade --force" fixed it for me. I upgraded from 5.0 to 5.1 to 5.5. The interesting this is that I had already run mysql_upgrade which is why I needed to add the --force option. Hope this helps someone.


7

Mysqldump is fast, but restoring dumps can be very slow for a big DB, and locking tables is not acceptable on a live site. A much better and faster way of setting up slaves is to use Percona's XtraBackup. XtraBackup imposes little load on the master, requires no locks and the restore on the slave is very fast. This mechanism does produce a complete clone of ...


7

It is impossible. The reason? The binary logs are incompatible. To be more specific, the binary logs are not backwards compatible. Here is why: An empty binary in ... MySQL 5.0 and back is 98 MySQL 5.1 is 106 MySQL 5.5 is 107 I have made reference to this subtle difference in ServerFault https://serverfault.com/questions/231369/mysql-master-binlog-...


7

The only harm I could see is doing COUNT queries against large tables. Doing SELECT COUNT(*) FROM mytable on an InnoDB table should produce a full table scan. However, think of what COUNT() actually does. The * represents a whole row. COUNT() can determine if non-NULL columns are present. The easiest way for that to happen it to make sure PRIMARY KEY ...


7

There are several places in your code where you declare prepared statements using query strings you're crafting with CONCAT(). If any of the arguments to CONCAT() is NULL, the function's return value is also NULL... so it looks like at some point you're unintentially doing (effectively) this: mysql> SET @oops = NULL; Query OK, 0 rows affected (0.00 sec) ...


7

Wait for 5.7 where generated fields are supported mysql> CREATE TABLE sales ( -> name VARCHAR(20), -> price_eur DOUBLE, -> amount INT, -> total_eur DOUBLE AS (price_eur * amount), -> total_usd DOUBLE AS (total_eur * xrate), -> xrate DOUBLE); Query OK, 0 rows affected (0,16 sec) http://mysqlserverteam.com/...


6

COUNT(somecolumn) ignores NULLs COUNT(*) won't You can see this with SELECT count(id),count(*) FROM test WHERE id IS NULL Coincidently, all aggregate functions ignore NULL except COUNT(*)


6

In a shared hosting environment, you cannot access my.cnf You can see the variables see other ways: SHOW VARIABLES SHOW VARIABLES; will show all variables that are set. Some can be dynamically changed within you session with the SET command. Some can be set in the server so that new DB Connections going forward can use the newly set value. SHOW VARIABLES ...


6

Yes. If you want to import the structure of the Database on some other machine there are some steps as follows 1.on Machine A(generate the dump without the data) mysqldump -uuser -ppass --no-data --databases db1 db2 db3 > database_structure.sql using mysqldump with --no-data will generate the dump without the data. if you want only data dump do as(...


6

MySQL only caches the indexes in MySQL's memory, not the data. The data is only cached by the underlying operating system's filesystem caches. The most important value to tune in my.cnf is key_buffer_size which will allocate memory for MyISAM to cache indexes. However, unless you have some specific reason for using MyISAM tables you should convert ...


6

There is an error in your query near AS w` Alias name "w" should instead be `w` Try this: UPDATE wp_posts AS `w` SET w.post_content = REPLACE(w.post_content, 'my-old-site.com', 'my-new-site.com');


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