Hot answers tagged

22

Please look at the Architecture of InnoDB (picture from Percona CTO Vadim Tkachenko) The rows you are deleting is being written into the undo logs. The file ibdata1 should be growing right now for the duration of the delete. According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace: Lots of Transactional Changes Very Long ...


20

I think we may have overcomplicated the answer that was in required in my case. I have no doubt that both Roland & Rick James are correct with their creation of a temporary table, injecting only rows that pass the filter NOT LIKE '-%' but the solution for me was "easier" because there was an important error I was unaware of until now and for that I ...


10

Roland's suggestion can be sped up some by doing both things at once: CREATE TABLE tablename_new LIKE tablename; ALTER TABLE tablename_new ENGINE = InnoDB; INSERT INTO tablename_new SELECT * FROM tablename WHERE `columnname` NOT LIKE '-%' ORDER BY primary_key; RENAME TABLE tablename TO tablename_old, tablename_new TO tablename ; DROP TABLE ...


7

It's not difficult. Just need a set section in your LOAD DATA INFILE command. mysql> create table tst ( datecol datetime ); Query OK, 0 rows affected (0.04 sec) mysql> File: [root@node1 ~]# cat /tmp/tst.dat 01-JAN-03 21-MAR-09 28-FEB-11 [root@node1 ~]# Test: mysql> load data infile '/tmp/tst.dat' -> into table tst -> fields ...


4

My first instinct would be to do multiple, smaller deletes by limiting the number of query results, and running the query multiple times: DELETE FROM `tablename` WHERE `columnname` LIKE '-%' LIMIT 1000000


4

The easiest solution is to simply not do that -- do a smaller delete, which can be more easily processed. In this case I would have recommended trying sequential deletes of the form: DELETE FROM `tablename` WHERE `columnname` LIKE '-a%'


4

For general case use your original query as derived table: SELECT cnt + (cnt * 0.75) AS total FROM ( SELECT COUNT(order_id) AS cnt FROM orders WHERE order_date BETWEEN CURDATE() + ' 00:00:00' AND NOW() ) AS tmp But in your case it is much simpler: SELECT (COUNT(order_id) * 1.75) AS total FROM orders WHERE order_date BETWEEN CURDATE() + ' 00:...


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

Your first link (Why is truncate DDL?) is about Oracle, not MySQL. While relevant, different DBMS implement same features differently. In Oracle and SQL Server TRUNCATE is DDL, not DML. In MySQL, it matter which version you use: Until version 5.1, the documentation says: Logically, TRUNCATE TABLE is equivalent to a DELETE statement that deletes all ...


3

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


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


2

If you will recreate all your views this way (and will create all new ones with this syntax): CREATE SQL SECURITY INVOKER VIEW applications AS SELECT id, product_id FROM modules WHERE item_type='application'; you will only need to make sure a user on another instance where you restored your database has grants to EXECUTE on your views and ...


2

MySQL 5.6 introduces Global Transaction Identifiers to replication, and complements these with new failover and switchover utilities which can automate fault recovery You can learn more about replication in MySQL 5.6 here: http://dev.mysql.com/tech-resources/articles/mysql-5.6-replication.html You can also watch a tutorial demonstrating how these utilities ...


2

The most common cause of this is virus scanning, as virus scanners tend to lock files. Try excluding the directory from scanning.


2

By all means, remove them. Those are just temporary tables mysqld had written to disk. In all likelihood, mysqld probably crashed when those temp tables were made. Look at the timestamps: Max is 2012-10-09 Min is 2012-08-21 The sum of these files are over 1G. They are just taking up room. I know they are temp tables because MySQL uses MyISAM as the ...


2

Your query SELECT b.* FROM a,b LEFT JOIN c ON hour(c.datetime) = a.hour; means (parentheses added) SELECT b.* FROM a, (b LEFT JOIN c ON hour(c.datetime) = a.hour); and that means a is not visible in the join. You may need to shuffle it around a bit SELECT b.* FROM (a LEFT JOIN c ON hour(c.datetime) = a.hour), b; http://sqlfiddle.com/#!9/4114a/2 ...


2

Specific comments PRIMARY KEY (id), UNIQUE KEY id_type (id,type) ) ENGINE=InnoDB The UNIQUE key is totally useless. InnoDB "clusters" the data with the PRIMARY KEY. That is looking up id lets you immediately find the rest of the columns, including type. Also, a PK is a UNIQUE key, so the unique key adds no useful constraint. WHERE id = X The PK ...


1

The problems got removed, when i swapped the my.cnf file, with a highly simplified version of it, which was earlier present in a machine, having MySQL 5.6. So possibly the issue was with my.cnf file. # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [mysqld] init-connect='SET ...


1

You can also try purge binary logs before '2016-04-29 08:00:00'. I sometimes find one works when the other doesn't for some reason. Failing that just delete them with rm like you said. It's not technically correct, but it shouldn't be a problem (I did this frequently in the past before I learned about the PURGE commands, and never had any problems). Of ...


1

I'd answer your confusion about auto-increment* variables. Remember: auto_increment_increment controls the interval between successive column values. auto_increment_offset determines the starting point for the AUTO_INCREMENT column value. So if you're setting 6 servers, you can set them as: auto_increment_offset=1,2,3,4,5,6 auto_increment_increment=6 ...


1

Your index KEY relationship (relationship_id) is obsolete. It's a duplicate index as the PK PRIMARY KEY (relationship_id,site_id,content_id) starts by sorting relationship_id so the queries on this field will use the PK. Note that I always create a ID field of type INT (or BIGINT for big tables, storing logs for instance) for the PK, then you can add a ...


1

Based on the suggestion I gave in the chat room, here is my answer CREATE TABLE cash_op LIKE cash_operation; Goto the datadir in your Windows server rename cash_op.MYD cash_op.MYX copy cash_operation.MYD cash_op.MYD login to mysql and run REPAIR TABLE cash_op; ALTER TABLE cash_operation RENAME cash_op_old; ALTER TABLE cash_op RENAME cash_operation; Go ...


1

Maybe you could do something like this: Add a new field called deleted. Do an update like UPDATE tablename SET deleted=1 WHERE `columnname` LIKE '-a%'. Set cron to delete this at night time.


1

You will want to use mysqld_multi, which is part of a typical MySQL server install. On my Ubuntu server, it was located at /usr/bin/mysqld_multi. MySQL's Documentation on mysqld_multi Here is a how to, I used to set up mysqld_multi: MySQL Multi How to Update, running a 2nd instance from the shell Start another mysqld using a different, port, socket and ...


1

The least disruptive method would be to use the general query log. The query log output can be a database table or a log (text) file; however it does not support filtering by DML operation or database so all SELECT statements on all databases will be logged as well. Obviously you can filter the logging output later. Also note: The session sql_log_off ...


1

Have ended up Querying information_schema.Table_Privileges and scripting Grants & Revokes based on the result.


1

The reason for the discrepancy is obvious. When you run SELECT A.*,B.* FROM (SELECT VERSION() MySQLVersion) A, (SELECT COUNT(1) MySQLProcColumns FROM information_schema.columns WHERE table_schema='mysql' AND table_name='proc') B; you get mysql> SELECT A.*,B.* FROM (SELECT VERSION() MySQLVersion) A, -> (SELECT COUNT(1) MySQLProcColumns FROM ...


1

I actually wrote about this back on August 27, 2012 : Proper tuning for 30GB InnoDB table on server with 48GB RAM. Here is an excerpt from my past answer (under the section entitled Log File Size): EXCERPT 5MB is the default size for innodb_log_file_size. Percona's mysqperformanceblog.com gave two good articles on computing the right size for your ...


1

Believe it or not, I just answered a question like this 5 days ago: MySQL Replication and High Availability From that answer I provided, I advise against automatic failover with pure MySQL Replication unless your specifically script it to check for replication lag or totally unavailability of the old master (STONITH) As an alternative, just setup a DRBD ...


1

There is no direct or simple answer to your question. MySQL replication provides one thing: replication. It's a mechanism that maintains replicas of your live data set, usually in very-near-real-time, on more than one server. Replication is used for a number of different purposes, including (but not limited to) fault/failure tolerance for your ...



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