Hot answers tagged

145

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;


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


38

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


30

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


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


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;


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


12

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


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

I am a big fan of dump, edit and insert. but you do not have to open the text file (dump file) to change it (this is especially usefull when it is several milion lines long). if you want to dump the database MYDATABASE. mysqldump MYDATABASE > mydump.sql then use sed to replace the old database name with new one like this sed -i 's/MYDATABASE/...


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

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

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

I would suggest that this is indeed indicative of a problem with db3. By default, mysqldump generates "extended" insert statements, containing more than one row's worth of insert per line. INSERT INTO table_name VALUES (...), (...), (...), ...; This is an optimization, since multiple inserts in a single statement are much faster than executing individual ...


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

Running locate my.cnf found the file /usr/local/etc/my.cnf Edit that file and set bind-address = 0.0.0.0 Restart mysql: brew services restart mysql


6

ISSUE #1 : Upgrade Path Your grant tables can get screwed up because you are leaping two versions instead of one. I just helped someone a week ago because they did just that (MySQL service stops after trying to grant privileges to a user). The solution for that question was to manually fix the mysql.user. Rather than going through that heavy-handed route, ...


5

Since you are running MySQL 5.5, you may want to consider configuring InnoDB to access multiple cores Here are the settings you should be using innodb_thread_concurrency sets the upper bound on number of concurrent threads that InnoDB can hold open. Best round number to set for this is (2 X Number of CPUs) + Number of Disks. UPDATE : As I learned firsthand ...


5

According to Mysql Official Doc : Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is NULL. You also save some storage space, one bit per column. If you really need NULL values in your tables, use them. Just avoid the default setting that ...


5

You missed three(3) things On DB1, mysql -uroot -ppassword -e"SET GLOBAL innodb_fast_shutdown = 0" On DB1, service mysql stop (DB1) Copying the backup (/var/lib/mysql) on DB1 Copy /etc/my.cnf on DB1 to /etc folder on DB2 Importing DB1 backup to DB2 (/var/lib/mysql) On DB2, chown -R mysql:mysql /var/lib/mysql Start mysql on DB2 Step 1 flushes everything ...


5

you need to add semicolon at the end of your command. show databases; show tables;


5

I hope you need to create the user for remote access with '%' wildcard and grant permissions on DB's. Step 1: In my.cnf (https://serverfault.com/questions/9107/how-do-i-find-where-mysql-is-loading-its-config-from) comment out your bind address # bind-address = xxx.xxx.xxx.xxx And restart MySQL Server. Step 2: Then you need to grant permissions ...


4

To answer the question here is the query SELECT A.*,B.* FROM mytable A INNER JOIN mytable B ON A.id = B.id -1 WHERE MOD(A.id,2)=1; I made this query up based on staring at the output It would have been nice to have a table called name_country perhaps like this: CREATE TABLE name_country SELECT A.id name_id,B.id country_id FROM mytable A INNER JOIN ...


4

One can even search and replace each occurrence of Insert into with Insert Ignore into in the mysqldump file.


4

To complete the answers, Oracle's push for "replacing" the query cache functionality is the memcached integration.


4

The 16.04 seconds is a timer on the client that measures the amount of time that passed between the call and the execution of the query (if you want to be more specific than that, the wall clock time between calling start_timer() and mysql_end_timer(), something that can lead to hilarious results like this one I got). The reason that you think that it took ...


3

I don't see a lot of opportunity for improvement. The index you added was probably a big help, because it's being used for the range matching on the WHERE clause (type => range, key => tran_date), and it's being used as a covering index (extra => using index), avoiding the need to seek into the table to fetch the row data. But since you're using ...


3

I think TINYINT would be a good choice. I would suggest not indexing relationship_status at all. You should not index relationship_status by itself because the index cardinality is 2. The Query Optimizer would never use the index. You could index as follows: ALTER TABLE my_table ADD INDEX (relationship_status,user_id); That way, in the worst case, the Query ...


3

If the file is what you have in hand and you are manipulating it from a shell / console, I would use sed to do string replacements on lines starting with CREATE DABATASE, CREATE TABLE, USE and optionnally -- (mysqldump comments) Replacing the db name on lines that matches Create Database, Create Table, Use and mysqldump comments dbfile="yoursqldumpfile.sql"...


3

I think your explain plan is doing a heavy index scan. What you might need is an index with those exact three columns. Please create this index ALTER TABLE t_group_tag_relation ADD INDEX resource_id_domain_id_resource_type_index (resource_id,domain_id,resource_type); and try your query again.


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