Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [mysql]

All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

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

How do I change the MySQL engine ERROR 29 (HY000): File '*.MYD' not found (OS errno 2 - No such file or directory)

I have a MySQL table Blog in a database Blog: mysql> SHOW TABLE STATUS WHERE Name = 'Blog' \G *************************** 1. row *************************** Name: Blog Engine: ...
P. James Norris's user avatar
1 vote
2 answers
63 views

Why SSD performance is not better than HDD in specific cases?

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 ...
Ivaylo's user avatar
  • 133
1 vote
0 answers
20 views

Cyclic Grants in SQL

I wonder what the result of the following grant/revokes is according to the SQL standard. Suppose A created a table tab and there are four users (i.e., A, B, C and D). The SQL commands are: grant ...
anotherme's user avatar
3 votes
5 answers
846 views

Is COMPACT a better format for fixed length rows than DYNAMIC?

In MySQL / MariaDB I have this table with fixed-length rows (no VARCHAR, TEXT, etc) CREATE TABLE trigram ( id BIGINT(20) NOT NULL, trigram CHAR(3) NOT NULL COLLATE 'utf8mb4_general_ci', ...
O. Jones's user avatar
  • 563
0 votes
1 answer
37 views

Table of 4 columns "definition is too large"

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) ...
user avatar
0 votes
1 answer
60 views

How to check data type in mysql?

In PL/pgsql,pg_type() give use data type of a variable. As Example: select pg_typeof(3) as type_of; -- output: integer select pg_typeof(true) as type_of; -- output: boolean Similarly, how to check ...
Art Bindu's user avatar
  • 101
1 vote
1 answer
20 views

How to import MySQL RAW Database files without connection error

unfortunately I did not create a 'dump' file with the backup (Data Export) process on MySQL Workbench 8.0.26. When I copy the old database files to the new Data directory, the server fails to start ...
life777eternal's user avatar
1 vote
1 answer
34 views

Does MySQL have a way to limit update/insert statement times?

This is similar to the statement_timeout of postgresql. I'm looking to have the query error if it takes longer than x seconds. I'm aware of SET MAX_EXECUTION_TIME, but that only affects select queries,...
juacala's user avatar
  • 125
3 votes
1 answer
524 views

Does mysqldump lock entire database or just a table by table?

I'm wondering if during a mysqldump executed in the following manner mysqldump --all-databases --master-data > dump.sql all tables are locked for the entire process, or just a table by table? In ...
0xC0DEGURU's user avatar
0 votes
2 answers
36 views

How to AUTO_INCREMENT only on INSERT?

I have one of this situations : CREATE TABLE table ( `col1` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `col2` VARCHAR(512) NOT NULL COLLATE 'utf8mb4_general_ci', PRIMARY KEY (`col1`) USING ...
Max Muster's user avatar
1 vote
1 answer
27 views

Find the counts and occurrences of a column based on the name of another column in MySQL

I have a table like this: id user userid user_id ipaddr 1 'Client' 10 0 1.1.1.1 2 'admin1' 1 0 2.2.2.2 3 'Client' 3 0 ...
Saeed's user avatar
  • 113
0 votes
1 answer
26 views

How to implement historical data with this EERD?

I am looking for a way to implement historical data with the following EERD (what I have tried, below the image): What have I tried? Temporal tables Didn't work because I use MySQL and it has no ...
user23600793's user avatar
0 votes
0 answers
36 views

Is InnoDB Engine a B Tree or a B+ Tree? [duplicate]

I am confused about the InnoDB Engine. In the original document, it states that InnoDB uses a B Tree. However, in some other articles, they claim that InnoDB uses a B+ Tree. https://dev.mysql.com/doc/...
haint95's user avatar
0 votes
1 answer
55 views

What could cause replication to break on MySQL when replicating from 5.7.40 to 8.0 replica

We have a MySQL primary running on 5.7.40. We are in process of testing the upgrade to 8.0.36 (this is on AWS RDS). We had 2 MySQL-8 replicas and 2 MySQL-5.7 replicas replicating from the primary. At ...
Rahul's user avatar
  • 119
1 vote
3 answers
52 views

Last cell value in a grouped by query

I have a table related to game results with more than 300 000 records, where each row keeps player attack. This is simplified table example. I keep an attack id, date, when attack was done, unique ...
anton's user avatar
  • 117
0 votes
0 answers
22 views

Master to Slave Replication pausing

I would like to get insights from everyone here in this community. After we apply indexes on our Tables on Database. If some users generate big reports. Replication suddenly stopped and what we are ...
user avatar
0 votes
0 answers
17 views

faild to connect to Mysql at 192..x.x.x:3336 through ssh tunnel at [email protected]:22 ssh

enter image description here "This is the result when I attempt to connect: I'm trying to connect MySQL localhost to my session on the server. (I'm connected to my session on the server via SSH ...
user avatar
0 votes
1 answer
23 views

How can I sort from the least dependent view to the most dependent view?

I have this data: CREATE TABLE `ViewDependencies` ( `ViewName` varchar(255) DEFAULT NULL, `DependentOn` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;...
Saeed Neamati's user avatar
2 votes
1 answer
81 views

Composite index with a range column in the middle

My understanding is that when designing a composite index a column, which will be used for range tests should be placed at the end of this index, because the usage of the columns in composite indexes ...
Ivaylo's user avatar
  • 133
-1 votes
1 answer
22 views

Slow query on mariadb

I recently moved the database from mysql to mariadb, but the same query is executed in mysql in less than a second, but it takes 20 minutes in mariadb. query : SELECT km.beleg, km.art, ke.id as KID ...
amir hosein cheraghchi's user avatar
1 vote
2 answers
39 views

Finding files a user has access to is slow when ordering

I'm working on a system where users are sorted into groups, and then those groups are applied on files. At a high level, what I need to do is to be able to pull out the 10 most recently-updated files ...
chraume's user avatar
  • 11
0 votes
1 answer
62 views

MySQL CLI asks for password but doesn't use it

I'm trying to connect to a remote MySQL database for replication. The database is on a machine that's part of a secured network, so after opening the security enough to allow my replication ...
Spencer Ke's user avatar
0 votes
0 answers
46 views

Setting up a Docker compose for Mysql and Vitess

With the recent announcement of Planetscale shutting down its lower and free tiers I've found myself in a bit of a situation. I have a Serverless app which is connected to a Mysql database in ...
Leonardo Petrucci's user avatar
0 votes
1 answer
28 views

MySQL Amazon Aurora - why is CPU low while max vCPU is high?

In MySQL Amazon Aurora, is it normal for CPU to be be low while other metrics are above the max vCPU line?
mstrom's user avatar
  • 133
0 votes
0 answers
27 views

Using Clone for addInstance on MySQL Innodb Cluster uses 100% Memory

When trying to add a new node to a MySQL Innodb cluster using Cluster.addInstance() and selecting the Clone option, within 2 or 3 minutes the donor nodes memory usages hits 100% on a server with 36Gb ...
mwarble's user avatar
1 vote
2 answers
37 views

MySQL exclusive lock not appearing to work for new child rows

I have some logic in my application which I think results in the following MySQL calls, however when I get two of these run within a couple of milliseconds, I get two incompatible child rows. Start ...
mjaggard's user avatar
  • 125
0 votes
0 answers
23 views

mysql replication skip some batch delete query

I set up a master slave mysql replication. I have an Event that run stored procedure and delete some rows (about 3500 rows). recently it skip some delete query in slave. for example it does not delete ...
user avatar
0 votes
1 answer
48 views

After Insert and Update Trigger on the same table

I have the following table in MySQL: CREATE TABLE `userlogtype` ( `UserLogTypeID` int NOT NULL AUTO_INCREMENT, `UserLogType` varchar(150) DEFAULT NULL, `InsertUser` varchar(100) DEFAULT NULL, `...
Steve Miller's user avatar
0 votes
1 answer
46 views

Why is mycli history file appearing in home directory?

I started using mycli and want $HOME/.mycli-history to be generated and updated from $HOME/.cache/mycli/.mycli-history Moved $HOME/.myclirc -> ~/.config/mycli/.myclirc Created alias mycli='sudo ...
Vivek Jha's user avatar
  • 155
1 vote
1 answer
55 views

Which indexes create to speed up a query with where

I have a query but I do not know which index create to speed-up it. This is the query: select `id_my_table` from `my_table` where now() between created_data and ifnull(finish_data, now(...
Giacomo M's user avatar
  • 163
2 votes
1 answer
139 views

MySql Aurora - how often to run ANALYZE TABLE?

I have a database that has existed for about 10 years, with hundreds of tables, many of which have several million rows. We don't have a DBA and we have an increasing number of slow queries. We're ...
mstrom's user avatar
  • 133
0 votes
2 answers
24 views

getting records that don't have a corresponding record in another table

I'm using MySQL 8.3 and would like to get conversations that don't have a messsage with and index value of 0 based upon the following schema: conversations id messages id conversation_id index (...
timpone's user avatar
  • 647
0 votes
0 answers
27 views

Migrate cold database (freebsd) to mariaDb on ubuntu

I have a little bit of a pickle: I have a freebsd based jail that ran nextcloud with mysqldb The machine running the jail is broken. I still have the jail with the data. Now I was wondering if I can ...
chas0r's user avatar
  • 1
1 vote
1 answer
70 views

MySQL Amazon Aurora - a few slow queries causing extreme performance issues

I'm a dev, not a DBA, so pardon my ignorance. I'm running on MySQL Amazon Aurora, single-instance (with a replica for failover). A few days ago, my application had a handful of extremely slow queries (...
mstrom's user avatar
  • 133
1 vote
2 answers
51 views

Query to return latest record between 2 tables

I'm trying to pull the most current date (cusPurDt) and corresponding amount (subAmt) by customer (cusID/subID). Here is some sample data: create table customer ( cusID char(5), cusPurDt ...
tkmagnet's user avatar
0 votes
1 answer
54 views

How to check MySQL running queries when there are too many connections?

Situation: Something goes wrong, and too many queries accumulate until the limit is reached and new connections get an error: ERROR 1040 (HY000): Too many connections To list processlist, I need to ...
gilbertasm's user avatar
0 votes
0 answers
25 views

Percona XtraBackup questions about full and incremental backups

I had a few questions on the Percona forum, and I'm unsure if I was being clear in my questions or not, however I'm curious how full and incremental backups work with XtraBackup. When doing ...
alturic's user avatar
1 vote
0 answers
38 views

Create table statement gives 'ERROR 1030 (HY000): Got error 168' for MySQL 8

More specifically: mysql> create table test(`test` varchar(8) NOT NULL); ERROR 1030 (HY000): Got error 168 - 'Unknown (generic) error from engine' from storage engine Error log isn't showing much, ...
Chris's user avatar
  • 191
1 vote
1 answer
62 views

Is MariaDB Acquiring Exclusive Lock during Delete?

There is a Spring batch job with one delete and many inserts within a transaction with isolation level READ_COMMITTED: delete from TABLE_A; insert into TABLE_A values (...); insert into TABLE_A values ...
bboy's user avatar
  • 15
0 votes
0 answers
48 views

Mariadb is not truncating even Innodb Undo log Truncate is On

we have configured the below settings in our configuration file. --innodb_file_per_table=1 --innodb_undo_tablespaces=3 --innodb_undo_directory=/var/lib/mysql/ --innodb_undo_log_truncate=ON --...
chwajahat's user avatar
1 vote
1 answer
202 views

When I open XAMPP and click the start MySQL button, it gives me an error

2024-02-07 7:40:50 0 [Note] Starting MariaDB 10.4.32-MariaDB source revision c4143f909528e3fab0677a28631d10389354c491 as process 1524 2024-02-07 7:40:50 0 [Note] InnoDB: Mutexes and rw_locks use ...
user avatar
0 votes
0 answers
24 views

Getting OOM when trying to change id from int to bigint in MySQL w/ percona

We have 2.5tb table with id column as int in Aurora 3 (MySQL 8.0.26). We need to change it to bigint as ids will run out soon. We tested it with pt-online-schema-change and the server crashed because ...
Nir's user avatar
  • 533
0 votes
1 answer
177 views

Sequel Ace missing list of tables component

I am using Sequel Ace (version 4.0.13 on Mac) to access a database. There used to be a list of all the tabels in the database in a component on the left side that is now missing. I cannot figure out ...
JayG's user avatar
  • 1
0 votes
1 answer
44 views

MySQL services fails to start successfully after copying MySQL Data Directory from another server

Purpose 1 - The running application should not be stopped and run 24*7 while backup or copy MySQL data directory (/var/lib/mysql). Purpose 2 - To backup MySQL files directly and copy on another server ...
Vijay Singh's user avatar
0 votes
2 answers
27 views

List of characters managed by a collation

I'm migrating to MySQL 8.0.33. We are changing character set to utf8mb4. For collation, I'm wondering among different options. I'm looking for documentation to review the list of characters that are &...
yaki_nuka's user avatar
  • 141
3 votes
2 answers
276 views

What is the effect of using different COLLATE values in a WHERE clause?

I read the documentation 'Using COLLATE in SQL Statements' https://dev.mysql.com/doc/refman/8.0/en/charset-collate.html, but it doesn't say much about the effect of using different COLLATE values to ...
Computist's user avatar
  • 165
0 votes
0 answers
22 views

Error mysqlimport loading to symlink table

Getting ERROR 1030 (HY000): Got error 168 - 'Unknown (generic) error from engine' when trying to import a .sql file produced via mysqldump. Running MySQL 8 on a Ubuntu 22.04 system. chris@chris-X1C6:/...
Chris's user avatar
  • 191
0 votes
0 answers
12 views

XAMPP MySQL database problem

I am working on Microsoft Windows 11. Occasionally (seems random), when I open XAMPP it appears to open an old version of my MySQL database. When this happens, all new information I entered on a ...
Nugget's user avatar
  • 1
1 vote
1 answer
82 views

Optimize MariaDB for Drupal 10 to handle more users [closed]

I have problems with my Drupal 10/MariaDB 10.5/PHP 8.1 website when they are 20 users simultaniously. The website goes down and the response time is really big (up to 30seconds sometimes) because php-...
mt.i.1's user avatar
  • 11
0 votes
0 answers
116 views

Probably missing something simple, source of ERROR 1146 (42S02): Table doesn't exist error

Looking to import a mysqldump .sql file into a new db. Have tried both via command line and directly within a mysql session. The following call gives the assocaited error: chris@chris-X1C6:/media/...
Chris's user avatar
  • 191