New answers tagged

1

The information_schema database is an all memory database made up of temporary tables. It is not stored in any of the InnoDB plumbing. I wrote about this 5 years ago : How is INFORMATION_SCHEMA implemented in MySQL? If you performed a mysqldump of the infomration_schema, reimporting it will do nothing. mysqld will correct and protect any outside manual ...


0

To shrink an .ibd file it's enough to run ALTER TABLE t1 ENGINE INNODB. It will rebuild the tablespace and the new one will be the most compact. I think OPTIMIZE TABLE does exactly the same internally. ALTER TABLE however is preferred because you can use pt-online-schema-change to avoid blocking the table. To get better understanding how data use the ...


0

We tried the same procedure on an Ubuntu workstation with MySQL 5.7.x on Ubuntu 16.04 and it worked without issue. Based on this, we decided to upgrade our server's OS to Ubuntu 16.04 which upgraded MySQL to v5.7.12. After changing my.cnf to reflect changes in some option names and restarting the service, the log files now have the correct sizes and the ...


1

The second option should be the fastest. It was made for this. Also it should have no bugs since it is used a lot and already for a long time. If you go for this then you do not even need to use a composite primary key. In my opinion the only reason to use the first option is if you need a numbering starting from 1 per client.


-1

What version of MySQL are you running. I needed to add the following to my.cnf: innodb = FORCE Other allowed options are innodb = ON I read that here: http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#option_mysqld_innodb


0

If using Mysql 7 and Jira 7 change the connection string in the mysql.properties file. sudo vi ${JIRA_APPLICATION_DIR}/atlassian-jira/WEB-INF/classes/database-defaults/mysql.properties Find the line starting with databaseUrl= ...


0

Empty frm files won't work because they have a specific syntax which is being checked by mysql. [root@server test]# touch test_one.frm [root@server test]# chown mysql test_one.frm [root@server test]# mysql test Although it is showing up in show table because it only checks for the existence of the file ... mysql> show tables; +----------------+ | ...


-2

I have practiced that as follows: BEGIN TRANSACTION; INSERT INTO table (r1) VALUES (...); SELECT id FROM table ORDER BY id DESC LIMIT 1; COMMIT; It gives the following result: BEGIN INSERT 0 1 id ---- 4 (1 row) COMMIT


1

Not matter what you configure innodb_file_per_table to be (0 or 1), the following still occurs MySQL creates a database folder MySQL creates every table's .frm in a database folder EXAMPLE Your database directory (datadir) is /var/lib/mysql You run the following commands CREATE DATABASE IF NOT EXISTS mydb; USE mydb CREATE TABLE tb1 (a INT NOT NULL) ...


0

I have found the solution. There is a variable called innodb_print_all_deadlocks which logs all the deadlocks in to the error log.


1

My understanding: The table contains 1M rows of which 250k are returned by the query. There are 500k rows with foreign_key_id = 1 and 500k rows with af.foreign_key_id2 IS NOT NULL. The query using full table scan (actually doing full index scan on the PRIMARY key in InnoDB) will read all 1M rows sequentially and check each of them for the conditions. The ...


0

After a long experimentation, I've found the problem to be the infamous swap insanity. The problem has been fixed by using the innodb-numa-interleave option.


1

I'll disagree with the assessment that "you are lucky enough to have a buffer pool with a perfect 100% hit rate" At the top of the output (which is chopped off), is a line something like: Per second averages calculated from the last 16 seconds This says to me that no reads happened in the last 16 seconds, thereby (artificially) giving you a perfect ...


2

The InnoDB/XtraDB is a table engine, it has no say in parsing SQL. Thats another layer, which should be mostly engine independent. MariaDB 5.5 is supposed to be 99.99% SQL compatible with MySQL 5.5 (there were some minor hiccups) and MariaDB 10.x is backward compatible with 5.5. With MariaDB 10 and MySQL 5.6 (and now 5.7) the paths diverged somehow - some ...


2

We have millions of tables deployed running ARIA in production. It is definitely a better choice over MyISAM as far as crashing and recovering from a crashed table. If you have high concurrent write neither are a option as they both only support table level locks. For tables with more write intensive operations stick with InnoDB, and with heavy write tables ...


1

EXPLAIN tells you the difference. If it shows DEPENDENT SUBQUERY it means that the subquery in IN() is executed once-per-row for the table1, that may be really many times if the table is big. Different MySQL versions may apply different optimizations to the first query to get rid of the DEPENDENT SUBQUERY or at least minimize its performance impact: use ...


1

You just asked Does: INSERT INTO table1... SELECT .. FROM table2 Also create a lock on table2? Yes, it does create a lock on table2. I wrote about this behavior back on Aug 08, 2014 (See my answer to MySQL consistent nonlocking reads vs. INSERT ... SELECT) In my old post, I mentioned from the MySQL Documentation: By default, InnoDB uses ...



Top 50 recent answers are included