Tag Info

New answers tagged

0

Most people just can say that cannot be done in MySQL's dialect of SQL (and they are right), but they do not propose an alternative. If you cannot change the database structure, use @MatthewSwain option. If you can, however, transform it into the following structure (which you will only have to do once) it can simplify the query a lot, specially if you have ...


2

In SQL Server you can do this with a recursive CTE, but unfortunately this isn't supported by mySQL. Instead, you need a recursive function/sp which calls itself to get the full tree, such as the following: set @@session.max_sp_recursion_depth=255; delimiter $$ create procedure get_bu_tree ( in parent int ) begin create table if not exists ...


1

This is a very common question, and unfortunately the answer is that we've fallen into one of the most serious deficiencies that MySQL has. In MSSQL, we could use recursive CTEs to accomplish this type of result. Just about every other RDBMS has some implementation that will allow for recursive querying. Unfortunately, in order to accomplish this cleanly ...


1

If you are using MySQL 5.6, InnoDB is already the default. Please do not use storage-engine because it is deprecated and probably explains the error. You should use default-storage-engine or default_storage_engine in my.cnf instead [mysqld] default-storage-engine = InnoDB GIVE IT A TRY !!!


0

Actually, at a first sight, you may run out of physical memory and run into swappiness. Minimum memory per-thread, as in your configuration, is 51MB. All threads may use 51x100=5100MB (btw, what is the value of Max_used_connections?). Adding to 28000MB as the InnoDB_buffer is and the Query_cache_size of 500 MB (is it really needed??), you have 33600MB. ...


2

Moving to Galera would be an option. Then your application has to be aware of 'deadlock detected try restarting transaction' errors. Another possibility is to set up a read slave and configure your application to use this slave for read_only queries. If one slave isn't enough you can add more and use them with a loadbalancer of any kind. Examples are ...


1

You should be able to migrate to MySQL Cluster (NDB Cluster), as new versions support foreign keys already and it is easy for scaling the load. However, as stated by Rick, maybe it would be good first to make sure, if dealing with response times by putting additional servers is the only option here. Please also note MySQL Cluster keeps all data in memory, ...


0

I assume that you used the innodb_file_per_table option. In this case I'm afraid that there is no way to restore the database only from the ibdata1 and the two ib_logfiles, which are actually the innodb redo log files.


0

Sounds like you have your data file corrupted. You may try to start up your server using innodb Force Recover and then dump your table data using Select Into Command. If you had your binary log file enabled before the crash, you may restore data through Point In Time Recovery


1

Last Sentence of MySQL Documentation on Traditional InnoDB Auto-Increment Locking says You may see gaps in the sequence of values assigned to the AUTO_INCREMENT column if you roll back transactions that have generated numbers using the counter. Therefore, if the second INSERT had failed and rolled back, it makes the gap. You may have to set ...


1

if you have the mysql-utilities installed you can use mysqldbcopy this happily works as a shell_exec command in php too. example: /* copy db from source to destination - this is actually pretty fast */ $command = "mysqldbcopy --source=user:pass@localhost --destination=user:pass@localhost test:test_2"; $output = shell_exec($command);


0

The semantics of those isolation modes cannot be guaranteed in SBR since the Slave is unlikely to execute the statements of multiple threads in the same order. More specifically, from the Change Log: ----- 2008-11-14 5.1.30 General Availability -- Bugs Fixed -- ----- With statement-based binary logging format and a transaction isolation level of READ ...


0

600 inserts/sec is more than out-of-the-box MySQL can do. You have done some tuning; more is possible. innodb_flush_log_at_trx_commit = 1 is the default, but it incurs a write after every transaction. =2 can be significantly faster. Batching inserts, if feasible for your application, can speed up INSERTs 10-fold. How many secondary indexes are there? ...


1

I have mentioned ALGORITHM=INPLACE in two other posts Mar 27, 2014 : When tables are locked does MySQL queue queries? Feb 12, 2015 : How to evolve MySQL schema while maintaining integrity In the second post, I named some caveats on using online DDL The online schema change feature uses the folder mapped in tmpdir. Make sure the copy of the table can ...


0

if you would want to allow read writes when Altering you might want to your percona online schema change tool from percona. This tool is safe to use when altering the table. http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html Hope this helps


0

MyISAM is faster than innoDB in insertion and that is because it relies on OS to write data to disk while innoDB insures final disk write (fsync()). moreover, innodb MVCC feature reduces the write speed. If the little delay in write is not a problem, stick to innoDB. you don't want to face lots of table crash or long table locks while your table face read ...


1

IMHO and in the described use-case, you will never use more than one core. The reason is that your workload is IO bound, not CPU bound. As your 3 connections are creating a new Index, each of those needs to read the whole table from disk: this is what is taking time, not computing the Indexes.


0

SUGGESTION #1 Since you are having issues with swap and looking at your current InnoDB settings, I would suggest one of the following settings: innodb_buffer_pool_instances=2 # If your Server is DualCore innodb_buffer_pool_instances=4 # If your Server is QuadCore innodb_buffer_pool_instances=6 # If your Server is HexaCore innodb_buffer_pool_size=90G I ...


1

I don't have enough reputation to comment on the previous answers. So I thought I'd elaborate a bit. 1) ON DELETE CASCADE means if the parent record is deleted, then any referencing child records are also deleted. ON UPDATE defaults to RESTRICT, which means the UPDATE on the parent record will fail. 2) ON DELETE action defaults to RESTRICT, which means ...


0

Rick James covered it quite well, I just want to add one thing: [!!] Query cache is disabled [--] Reads / Writes: 94% / 6% - and [1K qps] for me it seems there might be some potential in enabling query cache - but it depends a lot on your queries - if lot of queries contain some dynamic conditions (datetime/timestamp for taking most recent something) or ...


1

Are you using MyISAM? Or InnoDB? Or both? [!!] Query cache is disabled This is "good", not "bad". [!!] Table cache hit rate: 0% (400 open / 78K opened) How many tables to you have? Thousands? Please elaborate on why so many. (This is usually a design flaw.) Meanwhile, increase table_open_cache to, say, 2000. What MySQL version are you ...


0

If you're mostly adding to a table, you can hook on AUTO_INCREMENT as a measure of updatetness. SELECT `AUTO_INCREMENT` FROM `information_schema`.`tables` WHERE `table_schema` = DATABASE() AND `table_name` = 'YOUR_TABLE'; But I'd prefer to refer to an otside source like a counter in Memcached which you will increment every time you change something in ...


0

The Slave should be irrelevant, unless you are using (misusing) some form of parallel execution on the Slave. Try other transaction_isolation_modes. GET_LOCK()/RELEASE_LOCK() may provide a sufficient "lock"; but all writers to that table(s) would need to use it. Do not turn on auto-reconnect in your connection. An network glitch will mess with both the ...


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


2

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


2

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



Top 50 recent answers are included