Hot answers tagged

147

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;


71

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


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

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

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

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


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

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


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

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

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

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

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

Step 01 : Run this script to Dump Everything from MySQL 5.0 cd /root MYSQL_CONN="-uroot -p..." SQLSTMT="SELECT schema_name FROM information_schema.schemata WHERE" SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN" SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema')" MYSQL_OPTIONS="--skip-column-names -A" mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} -...


3

memlock was the issue. turns out, memlock settings were at default (see above), and this might have been preventing the allocation of 4+gb of memory to mysql. changed memlock settings in '/etc/security/limits.conf', and limit to 8GB. then, allocated 7GB to mysql buffer pool. worked. mysql now starts and stops without throwing any errors in the log. a few ...


3

It's not clear why you need this but technically you can do it with dynamic SQL like this SET @sql = NULL; SELECT column_name INTO @sql FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = SCHEMA() AND table_name = 'users' AND ordinal_position = 1; SET @sql = CONCAT('SELECT `', @sql, '` FROM users'); PREPARE stmt FROM @sql; EXECUTE stmt; ...


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 helped a company find which databases were accounting for the greatest load. We collected slow-query logs, and then ran a report with pt-query-digest. Except we subtotaled by db, instead of by query fingerprint. $ pt-query-digest --group-by db mysql-slow.log There are lots of other options in this tool, so you could filter or sort on the total IOPS, or ...


3

When referring to ib* files, are you also referring to the ib_log files? They also need to be removed when you remove the ibdata* files. Have a look at https://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql that might be of some help.


3

Here's what worked for me: ALTER TABLE vm_list CHANGE `vm_notes]` vm_notes VARCHAR(255); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 Yes, I somehow got a column named "vm_notes]" in there.


3

It may or may not be faster, depending on your data distribution and the queries you execute. What it will be is better. Normalisation isn't a goal in itself. It's a series of processes one applies to remove potential errors while changing data. The output is a schema which is resilient to changing data. Without normalisation you leave yourself open to ...


3

Try to set delimiter to something else than ; mysql> DELIMITER endl mysql> Then you can set pager with the semicolon: mysql> pager pager perl -nle 'print " $_";'endl PAGER set to 'pager perl -nle 'print " $_";'' Then you can come back to ; mysql> DELIMITER ;


3

If you are using Windows, you need not worry about named pipes at all. Why ? Years ago, MySQL for Windows had distributed three(3) different executables: mysqld.exe mysqld-nt.exe mysqld-max-nt.exe The two executeables mysqld-nt.exe and mysqld-max-nt.exe used the named pipes protocol. This is briefly mentioned in Chapter 23 Section 23.2 Page 353 Bulletpoint ...


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