i need to change storage engine from 'InnoDB' to 'MyISAM' on some tables of different databases. So i made a script file to do so: #!/bin/bash DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select distinct(table_schema) from information_schema.tables where table_schema not in ("mysql","information_schema","performance_schema")') for D in $DATABASES do TABLES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select table_name from information_schema.tables where table_schema="'$D'" and engine="InnoDB"') for T in $TABLES do echo "ALTERING TABLE $T" mysql -u XXXXX -pXXXXX -e "USE $D; ALTER TABLE $T ENGINE=MYISAM" done done It works on some tables, but on those with foreign keys, displays the following error message: ERROR 1217 (23000) at line 1: Cannot delete or update a parent row: a foreign key constraint fails Is there a way to change storage engine on those tables?

Recently, in our MySQL replication setup, the replication slave crashed due to 'too many open files' error. We brought it backup, but trying to figure out what caused the unexpected hike in the number of open files. We habitually see too many connections in 'sleep' status. Would these 'sleeping' connections open files other than the sockets they keep open? MySQL version is 5.6.20. OS is RHEL 6.4 Edit: Our MySQL is started with a huge number of 'open-files' limit and the OS has already allowed mysql user to open that many number of files. I am doing an RCA and would like to know whether the sleeping connections would open files other than the network socket they open.

I'm migrating a 300 million row MyISAM table to add some indexes. Just because it might be relevant, here's my ALTER statement: ALTER TABLE my_table ADD UNIQUE INDEX my_table_abcd (a, b, c, d), ADD INDEX my_table_abc (a, b, c), ADD INDEX my_table_c (c), ADD INDEX my_table_ce (c, e), ADD INDEX my_table_d (a); It currently says Stage: 1 of 2 'copy to tmp table' 74.8% of stage done and the processlist says that the progress of the ALTER statement is 37.440. I don't remember the exact timings, but the 'Stage 1' value jumped up to 60% in the first hour or so, and has been I crawling up to 70% in the last 12 hours. In the lat 3 hours it has increased 1.5%. I have a decision to make about whether I abort this and try changing some configuration settings or let it run. It's not linear based on previous observations. If it's slowing down exponentially then it's a waste to let it continue when I could be trying another approach. Any ideas for bases on which to make a decision? NB this question is relevant but not a duplicate. I'm not asking about predicting the time, I'm asking about whether the progress report has a linear relationship with time.

I have a MySQL table

in a database

:

mysql> SHOW TABLE STATUS WHERE Name = 'Blog' \G
*************************** 1. row ***************************
           Name: Blog
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 42
 Avg_row_length: 10142
    Data_length: 425984
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 114
    Create_time: 2024-03-24 10:39:25
    Update_time: 2024-03-23 07:10:41
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

When I do

on the table I get:

mysql> select * from blog limit 10 \G
ERROR 29 (HY000): File './blog/blog.MYD' not found (OS errno 2 - No such file or directory)

which, I think, indicates that MySQL thinks the table engine is

. But a directory listing of my

directory for the database shows nothing but

.idb

files:

bash3.2/usr/local/mysql-8.0.28-macos11-arm64$ sudo ls -Al data/Blog/
total 39744
-rw-r-----+ 1 _mysql  _mysql   557056 Mar 23 07:10 blog.ibd
-rw-r-----+ 1 _mysql  _mysql   114688 Apr 24  2023 blogpoll.ibd
-rw-r-----+ 1 _mysql  _mysql  9437184 Mar 24 10:00 comment.ibd
-rw-r-----+ 1 _mysql  _mysql   114688 Aug  2  2022 novelpoll.ibd
-rw-r-----+ 1 _mysql  _mysql   114688 Apr 22  2022 pandg.ibd
-rw-r-----+ 1 _mysql  _mysql   114688 Apr 22  2022 pandgsecret.ibd
-rw-r-----+ 1 _mysql  _mysql   114688 Apr 22  2022 produceshort.ibd
-rw-r-----+ 1 _mysql  _mysql   114688 Mar 14 07:47 recipient.ibd
-rw-r-----+ 1 _mysql  _mysql   114688 Mar  7 11:50 tanka1.ibd
-rw-r-----+ 1 _mysql  _mysql  9437184 Mar  6 13:30 viewing.ibd
-rw-r-----+ 1 _mysql  _mysql   114688 Apr 22  2022 writing.ibd

shows no

.MYD

files, just

.idb

files, which makes me think the engine ought to be InnoDB. If I attempt to change the table's engine to

, I get:

mysql> ALTER TABLE Blog ENGINE = InnoDB;
ERROR 29 (HY000): File './blog/blog.MYD' not found (OS errno 2 - No such file or directory)

which is the same error I get when trying to do the

. Trying to repair the table results in the same error message:

bash3.2/usr/local/mysql-8.0.28-macos11-arm64/bin$ ./mysqlcheck -u root -p -r Blog Blog
Enter password: 
blog.blog
Error    : File './blog/blog.MYD' not found (OS errno 2 - No such file or directory)
Error    : Can't find file: 'blog' (errno: 2 - No such file or directory)
error    : Corrupt

And attempting to

the table result in the same error message again:

bash3.2/usr/local/mysql-8.0.28-macos11-arm64/bin$ ./mysqldump -u root -p Blog Blog > ~/blog.dump
Enter password: 
mysqldump: Got error: 29: File './blog/blog.MYD' not found (OS errno 2 - No such file or directory) when doing LOCK TABLES

So, MySQL *really* wants this table to be a

table, but there are no MYD files. So, how do I convert the

files to

files to make MySQL happy?

I'm interested in this for InnoDB mostly, but also for MyISAM. I looked around in information_schema and did not see this data anywhere. I'm using MySQL 5.5.16.

I have a mysql **5.7.44** database with **myIsam** storage engine for few tables and I need to setup a cluster with 3 nodes that should be available for write process at same time, active/active for all 3 nodes. Could some one tell me please if it`s possible to achive it using Vitess ? I know there are some contraints regarding replication mysql version 5.7.44 version and myIsam. PS: There is no discussion about upgrade of DB in any circumstances. Regards, Thank you.

I have the following two tables CREATE TABLE tbl_products_temp ( cd_id INT(6) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT, cd_structure MEDIUMBLOB NOT NULL, cd_smiles TEXT NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', cd_formula VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', cd_molweight DOUBLE NULL DEFAULT NULL, cd_hash INT(11) NOT NULL, cd_timestamp DATETIME NOT NULL, cd_pre_calculated TINYINT(1) NOT NULL DEFAULT '0', iupac_name VARCHAR(600) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', price_currency VARCHAR(3) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', price1 DECIMAL(10,2) NULL DEFAULT NULL, price2 DECIMAL(10,2) NULL DEFAULT NULL, price3 DECIMAL(10,2) NULL DEFAULT NULL, last_update TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), deleted_dt DATETIME NULL DEFAULT NULL, PRIMARY KEY (cd_id) USING BTREE, INDEX tbl_products_temp_hx (cd_hash) USING BTREE, INDEX tbl_products_temp_px (cd_pre_calculated) USING BTREE, INDEX idx_iupac_name (iupac_name) USING BTREE, INDEX idx_formula (cd_formula) USING BTREE, INDEX idx_molweight (cd_molweight) USING BTREE, INDEX idx_smiles (cd_smiles(100)) USING BTREE ) COLLATE='latin1_swedish_ci' ENGINE=MyIsam CREATE TABLE tbl_products_temp2 ( cd_id INT(6) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT, cd_structure MEDIUMBLOB NOT NULL, cd_smiles TEXT NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', cd_formula VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', cd_molweight DOUBLE NULL DEFAULT NULL, cd_hash INT(11) NOT NULL, cd_timestamp DATETIME NOT NULL, cd_pre_calculated TINYINT(1) NOT NULL DEFAULT '0', iupac_name VARCHAR(600) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', price_currency VARCHAR(3) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', price1 DECIMAL(10,2) NULL DEFAULT NULL, price2 DECIMAL(10,2) NULL DEFAULT NULL, price3 DECIMAL(10,2) NULL DEFAULT NULL, last_update TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), deleted_dt DATETIME NULL DEFAULT NULL, PRIMARY KEY (cd_id) USING BTREE, INDEX tbl_products_temp_hx (cd_hash) USING BTREE, INDEX tbl_products_temp_px (cd_pre_calculated) USING BTREE, INDEX idx_iupac_name (iupac_name) USING BTREE, INDEX idx_formula (cd_formula) USING BTREE, INDEX idx_molweight (cd_molweight) USING BTREE, INDEX idx_smiles (cd_smiles(100)) USING BTREE ) COLLATE='latin1_swedish_ci' ENGINE=MyIsam Table tbl_products_temp has 241633 rows. Table tbl_products_temp2 has 218380 rows. I'm trying the following update query but its taking forever. UPDATE tbl_products_temp INNER JOIN tbl_products_temp2 ON tbl_products_temp.iupac_name = tbl_products_temp2.iupac_name SET tbl_products_temp.price1 = tbl_products_temp2.price1, tbl_products_temp.price2 = tbl_products_temp2.price2, tbl_products_temp.price3 = tbl_products_temp2.price3, tbl_products_temp.price_currency = 'EUR' When I run explain using the following I get: explain select * from tbl_products_temp inner join tbl_products_temp2 on tbl_products_temp.iupac_name = tbl_products_temp2.iupac_name id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra -------------------------------------------------------------------------------------------------------------------------------------------- 1 | SIMPLE | tbl_products_temp2 | ALL | idx_iupac_name | (NULL) | (NULL) | (NULL) | 218380 | 1 | SIMPLE | tbl_products_temp | ref | idx_iupac_name | idx_iupac_name | 603 | mydb.tbl_products_temp2.iupac_name | 1 | Using where How can I speed up this update query?

I have a large MyISAM table which has crashed. Repairing the table will take some time. The table only INSERTed to and SELECTed from, never updated. To allow the application to continue working, albeit with reduced capability, I thought of - renaming the crashed table - creating a new table with the original name - switching processing back on - repairing the backup table - switching off processing - merging the repaired and new data - switching on processing The other steps in this process do not pose any risk due to the nature of the application. Is it safe to rename a crashed MyISAM table? How? I believe that I can't simply do ALTER TABLE...RENAME.... as this always does a row-by-row copy into a new table. Apparently Peter Zaitsev uses a "tiny script which moves out all MyISAM tables out of MySQL database directory " but doesn't seem to give details of what this script does (presumably stops database first?).

I have a table that is a read-only table (real-time view), I used Aria based on the recommendation for read-heavy workload. Based on this https://dba.stackexchange.com/questions/1/what-are-the-main-differences-between-innodb-and-myisam?noredirect=1&lq=1 When it comes to cache is Aria like MyISAM or like InnoDB? meaning, Can I cache only indexes or can I also cache the data? (under the assumption I have large enough RAM)

Currently our Database all tables are using MyIsam as a storage engine. We tried changing storage engine of our mysql tables from MyISAM to InnoDB from PHPMyAdmin operations tab. However during the change table records are dropped. So what can be the possible factor causing this problem. This is what we tried: We converted test_user table which was having storage engine MyIsam and was having total records 11k. When we converted it into InnoDB the records droped to 9k. This happened for many tables. Not sure what we are doing wrong.

I know I need to change the delimiter when manually creating a MySql Procedure but I can't find evidence on how to do it when using the PHP MyAdmin new procedure form or is it even necessary to change it when using this form? Can anyone help? Thanks in advance.

I have a users table where there is the basic information of each user: CREATE TABLE users ( user_id int(11) NOT NULL AUTO_INCREMENT, account_type varchar(10) NOT NULL, email varchar(255) NOT NULL, password varchar(500) NOT NULL, registration_date timestamp NULL DEFAULT NULL, account_status tinyint(2) NOT NULL DEFAULT '0', PRIMARY KEY (user_id) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 On the other hand, I have a `user_data' table in which we put every other data: CREATE TABLE user_data ( data_id int(11) NOT NULL AUTO_INCREMENT, user_id int(11) NOT NULL, name varchar(50) NOT NULL, value text, PRIMARY KEY (data_id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 Basically, the data is stored as a name => value I fetch the data in the following fashion: SELECT email, ud_desc.value as description, ud_lang.value as language FROM users JOIN user_data as ud_desc ON ud_desc.user_id = users.user_id AND ud_desc.name = 'description' JOIN user_data as ud_lang ON ud_lang.user_id = users.user_id AND ud_lang.name = 'language' I made that second table this way because there is a ton of different data for each user, certain depending on their users.account_type. Also my client keeps adding, removing and changing different kinds of data all the time. So I needed something quite flexible and this is the best idea I came up with so far, from a developer point of view. Because I fear that this so called "best idea" is just a faint dream and will be too slow on the long term. Knowing that I might have to perform LIKE and MATCH AGAINST queries on the large volume of data that user_data will hold. Also, you might take note that I am not very familiar with indexes. But at this point, I have no idea what is the most efficient, given that the data is fetch based on two columns user_data.user_id and user_data.name. Should I make both of those columns (user_id and name) indexes? Should user_id be a foreign key (for the users table JOIN)? Is there a way to improve either my database model or my queries? I thank you all in advance. I am a bit lost at the moment and need some fresh points of view on this matter. Don't hesitate to tell me if you need more details.

I'm making a database which should be able to handle over 1,000,000,000 rows. in order to optimize it's performance i've decided to split tables. so instead of having 1,000,000,000 entries in one table for over 10 years i would split it to 3650 tables and one table for each day. there would be no more than join or union of 2 queries and i want to know whether having too many tables would degrade performance or not and if so How much ?

I'm trying to recover a corrupted MyISAM table because a sudden power loss. The problem is that this table is very heavy size around 100GB with 2M rows. I have tried with the command myisamchk -rvf table it throws me some errors “Wrong bytesec” but it keeps running it gets to the point where I get error “Errcode: 28 ” and when I check the table .TMD it size around 1.5TB eating all my free space. My questions are. 1. How can I repair the table 2. How is it possible the repair of this table weighs so much, I think even if I put a 4TB disk it will always fill it. What are my other options of recovering the table?

We have database with low amount of inserts/updates and much more reads. Sometimes it operates normally for months, but sometimes it gets stuck on insert into main, quite big myisam table, for hundreds of seconds. Other connections are waiting for this one to finish. This makes the whole database stuck for quite a long time. A few days ago, I couldn't even kill the query. In processlist, its Command was "Killed". Other inserts and updates' State was "Waiting for table level lock". The processlist was over 1000 queries long, in comparison to usual 10-30. The only thing that always helps is restarting the database server. We have Icinga for monitoring, but I don't really know where to look... I suspect the disks or wrong configuration of virtualization can be the reason, but I have no evidence for this claim. It's just my instinct. Any ideas?

Can someone tell me how to change my default storage engine to InnoDB using wamp? Currently it is MyISAM.

I've started to use MySQL 8 (8.0.12), and I'm realizing that the "inserts" are slower in this version than MySQL 5.6 or MySQL 5.7. It doesn't matter if the table is MyISAM or InnoDB, with just one "insert" with only one record, the server takes about 0.2 ~ 0.3 secs with MySQL 8. The same "insert" in MySQL 5.7 takes just 0.003 secs or less. I already discarded Windows Defender or another Antivirus Program, the writing speed of the hard disk is not the problem because I have a Virtual Machine in the same server, the VM has MySQL 5.7 and the result is pretty good too: 0.003 secs or less. I've tested the same "insert" in different computers with different S.O. and the result is always the same: MySQL 5.7 (or 5.6) takes 0,003 secs or less, meanwhile MySQL 8 takes 0.2 ~ 0.3 secs in every insert with just one record. The "creates" are slower too. What seems strange to me is that simple "selects" or "selects with joins" are faster in MySQL 8 than previous versions. I think the problem is with the queries that write on disk, but not a limitation with the disk, maybe an assigned limitation in my.ini or in another MySQL settings. Here I leave you the scripts with the slow performance in MySQL 8: CREATE TABLE ciiu_test2 ( id INT(11) NULL DEFAULT NULL, codbut VARCHAR(11) NULL DEFAULT NULL, ciiu VARCHAR(8) NULL DEFAULT NULL ) COLLATE='utf8_general_ci' ENGINE=MyISAM ; **Execution time in MySQL 5.7: 0.016 sec** **Execution time in MySQL 8.0.12: 0.140 sec** INSERT INTO ciiu_test2 (id, codbut, ciiu) VALUES (1, '18237', '2750'); INSERT INTO ciiu_test2 (id, codbut, ciiu) VALUES (2, '18238', '9491'); INSERT INTO ciiu_test2 (id, codbut, ciiu) VALUES (3, '18245', '9411'); INSERT INTO ciiu_test2 (id, codbut, ciiu) VALUES (4, '18248', '2221'); INSERT INTO ciiu_test2 (id, codbut, ciiu) VALUES (5, '18264', '3520'); INSERT INTO ciiu_test2 (id, codbut, ciiu) VALUES (6, '18265', '4645'); INSERT INTO ciiu_test2 (id, codbut, ciiu) VALUES (7, '18268', '6202'); INSERT INTO ciiu_test2 (id, codbut, ciiu) VALUES (8, '18276', '6512'); INSERT INTO ciiu_test2 (id, codbut, ciiu) VALUES (9, '18290', '4210'); **Execution time in MySQL 5.7: 0.002 sec** **Execution time in MySQL 8.0.12: 0.681 sec** Please, don't recommend me optimize the "inserts" with multiples records. Specifically, I need to get the same times in MySQL 8 with every "insert" that I had once in MySQL 5.7. I thank you in advance for the help you can give me with this issue.

I use InnoDB almost exclusively in my applications. However, if I'm not careful when setting up the table, I forget to change it and phpmyadmin sticks me with MyISAM. Is there a way to change the default storage engine?

I am using MySQL 8 on Ubuntu 22.04. All my tables are MyISAM. Recently I bought 1 TB SSD Samsung 980 PRO to replace the existing 7200 rpm HDD in order to boost read performance. My test machine is one old Dell Optiplex 3050 micro with Core i3 (6th gen) and 8 GB RAM. So the machine configuration with HDD and SSD is absolutely the same. So is the test database. Before upgrading to SSD I did a lot of tests with HDD. I run the same tests with SSD. In some cases SSD performance for query executions is far better, in other just a little better, but there are cases when it is slower. So far I thought that hard drive read operations are the bottleneck, but now I doubt it since the fact that the performance of the configuration with SSD is not better (or even worse) in some of the cases listed below. Using KDiskMark shows following comparison of Read [MB/s] for both storage devices: | Test | SSD | HDD | | ----------- | ----- | ---- | | SEQ1M Q8T1 | 3044 | 84 | | SEQ1M Q1T1 | 2114 | 84 | | RND4K Q32T1 | 660 | 0.7 | | RND4K Q1T1 | 66 | 0.3 | The query joins the statistical table

with a temporary table

, which is populated with a list of objects

for a user-selected cluster definition. The result set is a table with a number of KPIs, which are aggregated to cluster level values for each date of the reporting interval. The reference between both tables is

. The statistical table

contains daily values for a number of counters for each

for each day. This table contains 2 years of statistics for about 50k cells for each day. Data in

was imported with

DATA INFILE

in chronological order. After importing

TABLE

was run. No changes to table happened afterwards.

SET @DATE1='2019-04-01';SET @DATE2='2019-06-30';
CREATE TEMPORARY TABLE tmptab(cell CHAR(8) NOT NULL,INDEX Cell (Cell) USING BTREE) Engine=MyISAM;
INSERT INTO tmptab SELECT cell FROM clusters_cust WHERE cluster='cluster3k';
SELECT 'cluster3k' AS Cluster,Time,
ROUND(SUM(counter1)/sum(counter1+counter2)*100,2) as 'KPI1',
.........
FROM h_cell
INNER JOIN tmptab ON tmptab.cell = h_cell.cell
WHERE time >= @DATE1 AND time  SHOW TABLE STATUS IN xxx;
+---------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+--------------------+----------+--------------------+---------+
| Name          | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation          | Checksum | Create_options     | Comment |
+---------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+--------------------+----------+--------------------+---------+
| h_cell        | MyISAM |      10 | Dynamic    | 39090988 |           1969 | 76975355392 | 281474976710655 |   1736918016 |         0 |              1 | 2024-03-19 22:30:50 | 2024-03-19 23:51:03 | 2024-03-20 00:17:21 | utf8mb4_0900_ai_ci |     NULL | row_format=DYNAMIC |         |
+---------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+--------------------+----------+--------------------+---------+

I'm trying to optimize a large(4M rows) and busy table of mine. It's currently

CREATE TABLE person_search (
    token CHAR(63), -- each word of the person's name
    person_id BIGINT(20) UNSIGNED, -- foreign key on person.id; not constrained because MyiSAM
    birth_date DATE NULL, -- for ordering the results
    gender ENUM('male', 'female') NULL, -- additional filtering
    INDEX (token, birth_date, person_id),
    INDEX (person_id, token)
) ENGINE='MyISAM';

Searches are performed by INNER JOINing this table with itself as many times as needed for the number of tokens in the user's query. (For performance, searches are always prefix searches (LIKE 'foo%'); not sub-string searches (LIKE '%foo%')). To potentially improve performance, I tried MODIFYing the token column to be a massive ENUM():

ALTER TABLE person_search MODIFY token ENUM(
    'AAB',
    'AABYE',
    'AACH',
    -- snip ~110'000 values; longest value is 20 chars
    'ZYSTRA',
    'ZYWYLO',
    'ZYZAK'
) NOT NULL;

but after a few seconds, MySQL returned the error ERROR 1117 (HY000): Table definition is too large. [Googling it](https://www.google.com/search?q=mysql+%22Table+definition+is+too+large%22) didn't help. I think MySQL/MyiSAM can't do this, and I'll have to handle the enumeration myself in the application instead of the DB, or make a person_search_token_enum table and add more inner joins. But maybe I'm missing a setting or something. It's not a shared server; I can make any configuration changes I want.