Questions tagged [myisam]

MyISAM is the non-transactional storage engine for MySQL. It provides high-speed storage and retrieval, as well as fulltext searching capabilities. In addition, it is the default storage engine type for versions of MySQL prior to 5.5.

Filter by
Sorted by
Tagged with
0
votes
1answer
57 views

MariaDB crashed: Unknown/unsupported storage engine: InnoDB

I've a Debian GNU/Linux 9 (4GB, 2 CPUs) on Digitalocean. Tonight (I've done nothing) my DB (mariaDB) crashed with this errors. I ran a wordpress with InnoDB and myISAM tables: 2020-10-17 0:51:18 ...
1
vote
1answer
46 views

Why doesn’t a MySQL fulltext search “IN BOOLEAN MODE” fail when referencing a missing column?

First, the background on how I discovered this issue. This happened when using MySQL 5.7 and the DB — as well as the related table — are using MyISAM. I was debugging some code — and related MySQL ...
1
vote
4answers
46 views

Load index to cache is not using memory available in the key_buffer_size variable

Good afternoon folks, I have a problem when trying to load the indexes from the MyISAM tables into MySQL memory, even though there is space available in the key_buffer_size variable, it stops loading ...
0
votes
0answers
19 views

Do MyISAM tables crash from a “clean” mysqld restart?

I've researched this topic a little bit and available information indicates that MyISAM tables should not crash on their own from a simple server restart. Usually the following causes are put forth: ...
1
vote
1answer
86 views

Issues with MySQL 8.0.17

Recently we upgraded MySQL version on our UAT database server to 8.0.17 from 5.7.18. We are facing issues with our web application, Website is loading slowly if there are more users accessing it I ...
0
votes
1answer
42 views

What is branching factor of mysql myisam's b-tree

I am doing some research but I am struggling to find what is branching factor of MYISAM's b-tree, e.g. how many keys per page (node). I also would like to know how the char/varchar keys are stored - ...
1
vote
2answers
42 views

After changing engine from myisam to innodb tables are larger in size, is this an expected behaviour?

I updated a WordPress database from myisam to innodb on a server using apache and mysql, and I noticed that some tables, such as wp-posts grew in size after running the ALTER TABLE wp_posts ENGINE=...
1
vote
1answer
37 views

MySQL InnoDB Insertion Faster Than MyISAM

I wrote code that inserts about 15 millions rows into tables in MyISAM and InnoDB for comparsion. MyISAM and InnoDB are set up with no optimization configuration in /etc/mysql/my.cnf. And tables ...
0
votes
1answer
30 views

Join on large table Myisam & Mysql 5.0

I searched on some forums, perconna and more but i didn't find answers. We have a legacy server with Mysql 5.0 and some tables on Myisam. I'm trying to optimize my request. We have 3 tables mysql&...
1
vote
3answers
56 views

MyISAM around 5x slower than InnoDB

I thought, MyISAM is for simple tables and selects due to its architecture faster than InnoDB. Therefore I changed the engine for this table from InnoDB to MyISAM: CREATE TABLE `table1` ( `DateTime`...
0
votes
1answer
40 views

How to fix conversion error from InnoDB to MyISAM?

How can I fix conversion error from InnoDB to MyISAM for these tables? MariaDB [db_name]> ALTER TABLE wp_posts ENGINE = MyISAM; ERROR 1071 (42000): Specified key was too long; max key length is ...
0
votes
1answer
35 views

Changing storage engine to InnoDb from MyIsam is dropping records from table

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 ...
-1
votes
1answer
31 views

Using xtrabackup backup to recover MyISAM data

Why do I have a ibdata1 file when backing up MyISAM data? I don't have a InnoDB table. I can't find anything about recovering MyISAM data on the official website. How Do Irestore a single backup of ...
0
votes
1answer
46 views

Mysql separate username from client table for performance and use myisam for it

I'm creating a database for a very heavy messenger like Telegram ... I decided to use a different table for client-username and separate it from the clients table. This is My 'client' table: InnoDB ...
0
votes
2answers
505 views

MySQL - change all tables' ENGINE from MyISAM to InnoDB with a single query

I had some foreign key issues in my database when I change the table engine "MyISAM" to "InnoDB" but then it works fine. I found one query to change a single table's engine but I have many tables so ...
1
vote
3answers
112 views

From MySQL to MariaDB : bad performances issue with MyISAM tables

I'm currently migrating a Debian 6 MySQL server (5.1.73) to a fresh new Debian 10 MariaDB server (10.3.18). Please note that I'm not a database expert. All tables are under MyISAM engine storage, for ...
1
vote
1answer
88 views

troubleshooting mysqlimport very large text file

I've been attempting to import a very large (390 GiB) text file using the mysqlimport client. I've been successful at importing a 14 GiB text file as well as numerous smaller files using this method. ...
0
votes
2answers
1k views

Disk full of mysql .mad and .mai files

On a (owned) shared server sometimes mysql writes alott of .mad and .mai. This happens since i think 2 or 3 months, completely random. It may happen 3 times a day, then it does not happen for a ...
1
vote
1answer
476 views

How to change default engine in WAMP to InnoDB

Can someone tell me how to change my default storage engine to InnoDB using wamp? Currently it is MyISAM.
0
votes
2answers
76 views

How to repair mysql tables using myisam storage engine from multiple repairing threads?

I am loading a csv file into a mysql database table that uses MyISAM as storage engine. From the process list I can see that this is accomplished in two phases: 1) reading the csv file and 2) ...
0
votes
1answer
191 views

MySQL 5.6 row format changes when changing storage engine from MyISAM to InnoDB

I am testing an upgrade of all existing MySQL 5.6 tables from MyISAM to InnoDB. I converted all row formats to dynamic first for all the tables to be on Barracuda then running alter table <...
2
votes
2answers
136 views

InnoDB errors and crashes with MyISAM database?

I have one server with one mysql database containing only MyISAM tables. There is no other databases on the server (except system ones of course). Since it moved to mysql 5.7.27 there are InnoDB ...
1
vote
2answers
803 views

MySQL: Is it safe to set `key_buffer_size` to 0 if the db doesn't have any MyISAM table?

In our system, we don't use MyISAM - only InnoDB (and occasionally, MEMORY). Is it safe to set key_buffer_size to 0? Or there are unexpected side effects? Based on the manual, it seems it's safe.
0
votes
1answer
606 views

Will too many tables slow Mysql down?

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 ...
0
votes
1answer
235 views

Make Xtrabackup ignore myisam tables & backup only innoDB tables?

I'm using mysql 5.7 (or rather percona 5.7) Is it possible to backup only the innodb tables (easily, not to specify each table one by one because I have a lot of tables) with xtrabackup? and not take ...
0
votes
1answer
46 views

Do I need to change the delimiter when creating a procedure using PHP MyAdmin?

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 ...
2
votes
3answers
649 views

Estimate query speed for +1.5 billion rows mysql table

I'm working on audio fingerprinting problem where I need to query a very large table in terms of number of rows (at least 1.5 billion rows), but relatively OK in size (23G), and retrieve about 50K to ...
-1
votes
1answer
149 views

Table metadata lock MariaDB

I'm having serious problems in a DB, where a "Waiting for table level lock" box is loitering and locks the whole system: Based on some research, when the problem occurred to get the command "SELECT * ...
1
vote
1answer
151 views

Much slower replication on Innodb compared to myisam

Good morning. I wanted to test how InnoDB will work on my servers and for this I made a slave and connected it to my master with myisam. The problem is that when the slave works on innodb, replication ...
3
votes
2answers
1k views

MariaDB 10.3: InnoDB much slower than MyISAM

I'm using MariaDB 10.3.12 on Arch Linux and haven't modified any of the default settings under /etc/mysql. The following script takes 0.75 s to run on average using the default InnoDB engine: drop ...
1
vote
0answers
265 views

MySQL 8 more than doubles the size of MyISAM table after optimization

I recently upgraded from MySQL 5.7 to 8.0. I have noticed some changes from 5.7 to 8. But I have not been able to find any discussion on following situation that I have been puzzling for a while. I ...
2
votes
2answers
103 views

Table column length is fine in MySQL 5.5 and 5.6, but upgrading to 5.7 results in too many columns

So I have a CMS that is being upgraded from MySQL 5.5 -> 5.7. One table has a 1000+ columns, but it works fine in 5.5 and 5.6. When reaching 5.7, queries involving this table result in a 1117 error. (...
1
vote
1answer
136 views

Finding temporary table name in MariaDB

As I reported some days ago to MariaDB bug tracker the following bug: https://jira.mariadb.org/browse/MDEV-17420, slave replicas with version 10.2.x are leaking temporary tables on MyISAM files. Our ...
2
votes
0answers
944 views

Database design for Subscription, Coupons and Plans

I have searched a lot in SO and SE but I did not get anything particularly about this kind of database design with coupon codes included. Please take a look at this problem. I am developing a website ...
8
votes
3answers
8k views

Inserts in MySQL 8 are slower than Inserts in MySQL 5.7

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 "...
1
vote
1answer
69 views

What may cause MyISAM index corruption?

I've had an error message telling about index corruption on the .myi file Reparing the table fixed the issue This table is accessed with select only, so what is likely to corrupt indexes ? CREATE ...
1
vote
3answers
697 views

Event doesn't work

I have a working command: UPDATE ads SET ad_points = ad_points - 20 And I would like to perform this action every day. After creating the event in phpMyAdmin (MYSQL) nothing happens (on the next ...
0
votes
1answer
271 views

Zero Date Values & Converting Entire DB From MyISAM to InnoDB

I'm trying to update the MYSQL DB for an older wordpress/woocommerce site that's currently using MyISAM by switching the engine type to InnoDB. The issue I'm having using Alter Table is that there's ...
0
votes
1answer
90 views

MYSQL Multiple table optimization for advanced search

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(...
-1
votes
1answer
288 views

Website down with 3,000 concurrent user 4Gb Memory Dedicated Server (myIsam - MySQL) [closed]

Our site has 3,000+ concurrent users when we open our registration on our website. The site was too slow and get the server down sometime, even sub accounts websites are down. Free memory was 400MB. ...
-1
votes
1answer
45 views

Can I tweak Mysql [buffer?] variables to speed up indexes building?

I have disabled indexes updates with alter table my_table disable keys to speed up inserts Are there variables I can set [for the session] to speed up indexes rebuilding with alter table my_table ...
4
votes
2answers
3k views

“Create Unique Index” on big table taking too long time [closed]

I am trying to restore GHTorrent's database dumps (CSV files containing GitHub metadata). The commits table has more than 891 Million rows and the project_commits has more than 5.4 Billion rows. Since ...
0
votes
1answer
28 views

Mysql - Rows not updated in transactions without any errors

I have a stored procedure that updates a user's balance and inserts a transaction log. I don't know why, but sometimes it doesn't update the users table. Stored Procedure Code DELIMITER $$ USE `...
-1
votes
1answer
391 views

restoring MySQL MyISAM table from files

0 down vote favorite We have damaged disks on the server and partially lost data. Backup of the database was not the most relevant, so it was decided to restore the database from the .myd, .myi, .frm ...
0
votes
1answer
41 views

Optimitze Table, MyIsam

I was reading a post on Percona (https://www.percona.com/blog/2010/12/09/mysql-optimize-tables-innodb-stop/) that said dropping and adding an index on a innodb table was almost 20* faster than using ...
2
votes
1answer
264 views

Proper Way to Remove Mysql Tables

I was going to ask why mysqldump was trying to write dropped tables but I found that answer from this question, How do you remove reported non existing tables from mysql?. Removing the .frm files ...
0
votes
1answer
316 views

mysql_query() is NOT passing the command to the MySQL server using the C API

I have this question asked but after a deeper investigation I realized that hanging is not the issue. After reading this thread I decided to ask a new question. For example I pass this string to the ...
1
vote
1answer
173 views

C API hangs in mysql_query()

I have this very strange issue and I cannot reproduce it - it occurs once in ~12 hours only for this table: CREATE TABLE `ReferenceWaves` ( `ReferenceWave` datetime NOT NULL COMMENT 'DateTime of ...
1
vote
3answers
545 views

Optimize MAX or ORDER BY column DESC with LIMIT 1

I have a scenario where I need to Insert a new record and increase a sub-id. The Query is very slow. Exist any better Solutions for this? Table CREATE TABLE `link_` ( `id` bigint(20) NOT NULL ...
2
votes
2answers
210 views

Terribly slow Disk Read (I/O) When fixing corrupt MyISAM table (9GB)

So I've launched the myisamchk utility with repair option on all tables within the database. Smaller tables (1GB max) got fixed in no time, virtually seconds. However, my largest table (9GB) takes ...

1
2 3 4 5
10