Tag Info

New answers tagged

0

The question is not very clear. If what you want is to have as result **every object that is related to name 'orange' but not to name 'banana', here is one way: SELECT o.* -- Change this to only the columns needed. FROM objects AS o -- If there are data needed from other tables, -- join here. WHERE ...


1

I've been having the exact same issues on my OpenVZ VPS's recently. Both running Debian 7 (official OpenVZ template), both with MySQL 5.6.23 from the Dotdeb repository. Random InnoDB corrupted tables, with the dump in the error log showing strange data that is definitely not part of any of my own databases, nor any files stored in my container. Both started ...


1

In addition to what Derek says, kill 119634; -- Since it is in Sleep mode, this will disconnect the client. That, in turn, terminates the runaway transaction. look in your code for autocommit=0. Don't do transactions that way; it leads to the problem you are experiencing. (No, I can't be sure this is the cause this time.)


1

You are right that the transaction is 'running' for several hours. This basically means you have a query that has failed to run 'commit' and is holding the transaction open. If you are running in the default REPEATABLE READ transaction isolation level, you will also see your 'history list length' growing large in the TRANSACTION section of the SHOW ENGINE ...


1

InnoDB makes it impossible to just cut and paste a database because each .ibd files has tablespace_id that are referenced in the data dictionary within c:/ProgramData/Mysql/Mysql Server 5.5/data/ibdata1. Here is a Pictorial Representation of InnoDB (from Percona CTO Vadim Tkachenko) You cannot recover just a database. You must restore the entire data ...


3

Background info... The "torn page" problem occurs when part of an InnoDB block is written to disk, but the physical write died before writing all the low level (usually 512-byte) blocks. This leads to an unreadable block for InnoDB. The double-write buffer, and its extra write, makes it possible to recover from a torn page. The hardware needs to ...


1

I'll bet that you are currently I/O bound. This means that nothing can speed it up. (And Rolando's suggestions may be futile.) Let's look deeper. Is this LOAD a recurring task? If so, how often? Is everything blocked waiting for table to be reloaded? Simple solution: Load into a different table, then do a double RENAME TABLE to swap it in. Only ...


-1

Although LOAD DATA INFILE can work against InnoDB, there are too many ways InnoDB gets tapped to its limits before swapping and bottlenecks takeover. Here is a Pictorial Representation of InnoDB (from Percona CTO Vadim Tkachenko) The bottlenecks would be goring through the following structures InnoDB Buffer Pool Transaction Logs (ib_lofile0, ...


0

I think Rick James is on the right track, the many 30k table handles are jamming memory. In MySQL 5.5, InnoDB uses the system tablespace, which places all table data in a single OS file. This means that individual internal table data allocation, as well as handle management, happens inside MySQL (which is to say, completely in RAM as you have no swap ...


1

BEGIN or START TRANSACTION initiates a transaction. It is ended by COMMIT or ROLLBACK in the same connection. SET TRANSACTION provides optional attributes for the transaction; it is not often used. If you need a long-running transaction, or something that spans multiple web page invocations, you need to do it some other way. (This is probably a common ...


0

This entry in the log seems to indicate a possible corruption problem with mysqld-bin.000032. Those mysql bin files are part of the MySQL binary log process. They were probably being used for replication or for point-in-time backups. InnoDB: Last MySQL binlog file position 0 106756973, file name ./mysqld-bin.000032 18:39:59 UTC - mysqld got signal 11 ; ...


1

According to this, your problem is not with VARCHAR fields. However: SELECT id, LENGTH(field1)+LENGTH(field2)...+LENGTH(field_n) AS total_length WHERE total_length > 8126 Can be done using 2 queries for each field. (This can be done in a complex query if you want to use INFORMATION_SCHEMA): SELECT MAX(LENGTH(field1)) INTO @max_length1 FROM table_name; ...


1

Someone make this bench: http://mimdl.tumblr.com/post/115308901909/benchmarking-handlersocket-mysql-innodb-memcached Get Operation with HandlerSocket was completed in 1.20499610901 Sec, i.e; making 8,299 QPS (Queries per second) Get Operation with InnoDB Memcached Plugin was completed in .93115 seconds i.e; making 10,739 QPS Select Query for MySQL took ...


0

I ended up doing this, to do it in batches of 1krow (which takes about .2s per batch if there's nothing to update, 2s otherwise; basically it has the same total speed per row if it's in batches of 10k, 5k or 1k): SET SESSION sql_mode = 'strict_all_tables'; SET SESSION sql_warnings = 1; \W DELIMITER // DROP PROCEDURE IF EXISTS update_dmf_lifespan// CREATE ...


0

SHOW CREATE TABLE -- need to see the PRIMARY KEY and the "index". 100-1000 is optimal (10K might be ok) for "batching" INSERTs -- That is, a single INSERT statement with 100 to 1000 rows in it. You have shown a single row being inserted. Will it work change to values ('a', 'b', 3), ('c', 'd', 4), ... That might give you a 10-fold speedup. That is also ...


0

Roland's ideas are good. Also Turn off the query_cache; it is unused overhead. (_type=0, _size=0) Batch size of 10 is probably good. How big are the BLOBs/TEXTs? How much data is there? Sounds like terabytes? To simply read and write terabytes takes hours -- just for the disk I/O. (So 40 hours may be reasonable.) comment out log-bin (no use writing a ...


1

InnoDB Architecture (from Percona CTO Vadim Tkachenko) What are the values recommended for the batch insert size, considering the size of the columns (longtext, longblob)? Increase innodb_log_buffer_size to 256M Increase innodb_log_file_size to 2G I wrote about increasing these to accommodate TEXT/BLOB fields in the past Apr 20, 2011 : MySQL ...


1

phpMyAdmin will only do an actual select count(*) if the estimated number of rows are within the set threshold (default is 500k depending on your version): $cfg['MaxExactCount'] For InnoDB tables, determines for how large tables phpMyAdmin should get the exact row count using SELECT COUNT. If the approximate row count as returned by SHOW TABLE ...


0

I don't think the issue is InnoDB table size limit at all. I have posts about InnoDB Tables having a Table is Full Condition in the InnoDB Plumbing May 31, 2013 : How to solve "The table ... is full" with "innodb_file_per_table"? Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full My guess ...


0

I wanted to do a backup of all data before trying anything but had repeated problems because of the mysql and performance_schema databases - Dump All Databases in MySQL - Fails on mysql and performance_schema . In the end I decided to back up my data and try something different with the option of reinstalling everything if it all went wrong. This is what I ...


0

Recommend you use parentheses to make sure: FROM ( a JOIN b ) RIGHT JOIN c or FROM a JOIN ( b RIGHT JOIN c ) depending on which is more correct.


3

When you use a column from a table on the "wrong"* side of an OUTER join, the join usually becomes equivalent to an INNER join. * "wrong": right side of a LEFT join and left side of a RIGHT join. So, the solution is usually to move the condition from the WHERE to the ON of the outer join. Like this: SELECT cre.id, rer.idChambre -- ...


0

On files, add these compound indexes: INDEX(filesystem_fk, file_status, file_tsuid, created_time), INDEX(filesystem_fk, file_status, created_time) (I am not sure which will help more.)


0

Many things can be done to speed things up... Use FLOAT or DECIMAL, not VARCHAR for latitude and longitude. (This is one of many things to shrink the record size.) Do not INDEX boolean values like clocked_in and public_post. (The optimizer is unlikely to ever use the index. And it is costly to update.) Don't split DATE and TIME; use DATETIME instead of ...



Top 50 recent answers are included