MySQL Forums - General

Gap lock failure (1 reply)

Eve Li — Wed, 08 Apr 2026 14:36:36 +0000

Hello. I learned that in MySQL, we might insert multiple rows with the same id into a primary key field because of its gap lock failure. Does anyone have any insight about the details regarding the gap lock failure?

Gap lock failure (no replies)

Eve Li — Wed, 26 Nov 2025 18:33:37 +0000

Hello.

I learned that in MySQL, we could possibly insert multiple rows with the same id into a primary key field because of the gap lock failure.

I was trying to look for documentation about this, but I couldn't find any. Does anyone have any insights about this issue?

Thanks.

Best practices for storing sensitive numeric data in MySQL (no replies)

Rasheed Mosan — Tue, 11 Nov 2025 07:21:28 +0000

Hi everyone,

I’m designing a MySQL database to store user information that includes sensitive numeric data. I have a few questions about best practices:

What’s the recommended way to store partially sensitive numbers (like card or account identifiers) securely?

Should I use encryption at the column level, or rely on application-level encryption?

Are there any performance considerations when frequently querying encrypted numeric fields?

Also, if anyone knows any reliable tutorials, tools, or resources for securely handling and querying sensitive numeric data in MySQL, I’d love to check them out.

I’d really appreciate any guidance, examples, or resources for designing a secure and efficient schema.

Thanks in advance!

Statistics on a table : List fields with count not empty, min and max values (no replies)

Camion Petit — Thu, 06 Nov 2025 13:00:38 +0000

Hello

I want to establish a query that can list all fields of a given table, with count of not empty values and min and max values

Thanks in advance :)

Handling MySQL Performance Bottlenecks During Peak Traffic (no replies)

Kasper Infotech — Sat, 06 Sep 2025 04:55:39 +0000

Hi all,

I’m currently optimizing a MySQL-based system that handles high read and write traffic. I’ve already applied some common improvements like

Proper indexing

Query optimization (avoiding SELECT *)

Partitioning for large tables

Still, when peak traffic hits, I notice a performance dip—mainly in queries involving joins across multiple large tables.

I’d love to hear from this community:

Do you rely more on denormalization, or do you prefer to stick to strict normalization even for large datasets?

Have you found connection pooling to significantly reduce overhead in such cases?

What’s your favorite monitoring tool to quickly identify slow queries in production?

It's always beneficial to gain knowledge from practical experiences rather than relying solely on theoretical concepts.

(P.S. I mostly work on web applications where database performance is critical, so discussions like these directly help me and my team at Kasper Infotech when we’re scaling client systems.)

MySQL 5.6 not writing any logs (no replies)

Debanjali Sarkar — Tue, 02 Sep 2025 05:04:06 +0000

We are using MySQL version 5.6 in our Windows Server 2012 R2 environment. We have enabled the error, general & slow logging in the config file, but the logs are not being written even in case of errors. The below is a snippet from the ‘my.ini’ file:

# Commented lines aren’t included
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
log_error = "C:\Program Files\MySQL\MySQL Server 5.6\Log\error.log"
slow_query_log = 1
slow_query_log_file = "C:\mysql_logs\mysql_slow.log"
long_query_time = 2

The MySQL is not directly managed, rather it is managed by Plesk Obsidian version 18.0.56 Update #4 ( Web Abmin Edition), as it was installed as a component of Plesk. As a result, we are unable to change any permissions to folder, such as providing ‘Full’ permission for the MySQL account through the mysql command line. We have given Full permissions through the Windows NTFS folder permission but still not working.

The troubleshooting steps tried by us are :

1.Checked whether the intended log file is present in the path before mentioning it in the my.ini file.
2.Restarted the mysql services after modifying the config fil.e
3.Checked the permissions to the folder in which the intended log file path resides, after coming across this link. The logs are not writing even after giving full permission as mentioned above.
4.Replaced the entry for the file path by removing the double quotes , replacing with single quotes, checking for any inadvertent spaces.
5.We have also added SET global general_log = 1; but logs are still not being written.

After every changes to the ‘my.ini’ we have restarted the MySQL service and checked.

Please assist us in resolving the issue. If any further information required then do let me know.

Thank you

MySQL 5.6 not writing any logs (no replies)

Debanjali Sarkar — Mon, 01 Sep 2025 05:07:23 +0000

We are using MySQL version 5.6 in our Windows Server 2012 R2 environment. We have enabled the error, general & slow logging in the config file, but the logs are not being written even in case of errors. The below is a snippet from the ‘my.ini’ file:

# Commented lines aren’t included
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
log_error = "C:\Program Files\MySQL\MySQL Server 5.6\Log\error.log"
slow_query_log = 1
slow_query_log_file = "C:\mysql_logs\mysql_slow.log"
long_query_time = 2

The MySQL is not directly managed, rather it is managed by Plesk Obsidian version 18.0.56 Update #4 ( Web Abmin Edition), as it was installed as a component of Plesk. As a result, we are unable to change any permissions to folder, such as providing ‘Full’ permission for the MySQL account through the mysql command line. We have given Full permissions through the Windows NTFS folder permission but still not working.

The troubleshooting steps tried by us are :

1. Checked whether the intended log file is present in the path before mentioning it in the my.ini file.
2. Restarted the mysql services after modifying the config fil.e
3. Checked the permissions to the folder in which the intended log file path resides, after coming across this link. The logs are not writing even after giving full permission as mentioned above.
4. Replaced the entry for the file path by removing the double quotes , replacing with single quotes, checking for any inadvertent spaces.
5. We have also added SET global general_log = 1; but logs are still not being written.
After every changes to the ‘my.ini’ we have restarted the MySQL service and checked.

Please assist us in resolving the issue. If any further information required then do let me know.

Thank you.

When I use JOIN to connect 4 tables, the query becomes extremely slow. (no replies)

Jobs Wang — Wed, 27 Aug 2025 07:05:41 +0000

When I use JOIN to connect 4 big tables, the query is extremely slow. How can I speed up multi-table JOIN queries? Please provide an accurate solution including SQL or parameters configuration. Thank you very much sincerely.

When I use JOIN to connect 4 tables, the query becomes extremely slow. How can I speed up the join query? (no replies)

Jobs Wang — Wed, 27 Aug 2025 07:00:33 +0000

MySQL database gets stuck while adding a foreign key during frequent write activity (no replies)

Tayler Sokalski — Fri, 15 Aug 2025 23:15:08 +0000

I am encountering a strange issue wherein MySQL 8.4 appears to get stuck when adding a new foreign key to a table. I've found a workaround where I can kill some INSERT statements to unstick things, but I don't believe the database should be getting stuck in the first place.

The schema of the impacted database has a bunch of tables; 3 of them are relevant to the issue in question. For the sake of example, let's imagine the database is tracking health information, and let's call these tables `person`, `heart_rate`, and `health_target` (this example is entirely made up, but the same impact applies). The `health_target` table does not yet exist in the database; the new table definition contains a foreign key pointing to the `id` column on the `person` table. The existing `heart_rate` table also has a foreign key pointing to the `id` column on the `person` table, and receives frequent writes, as it is tracking heart rate values over time.

Adding the new `health_target` table without the foreign key works successfully as follows:

mysql> CREATE TABLE `health_target` (`id` varchar(36) NOT NULL, `person_id` varchar(36) NOT NULL, `target_data` json NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.06 sec)

Trying to add the foreign key as below causes the database to lock up completely:

> ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id);
(never completes)

Examining the contents of `information_schema.processlist` after executing the ALTER TABLE statement, I can see that the ALTER TABLE is stuck in a "Waiting for table metadata lock" state, as are a great number of INSERT and SELECT statements that reference the `person` table (which is a lot of statements, since this table has many references and the system is pretty busy servicing requests).

mysql> SELECT * FROM information_schema.processlist WHERE INFO IS NOT NULL ORDER BY time DESC;
+-------+------+--------+------+---------+-------+---------------------------------+-------------------------------------------------------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+-------+------+--------+------+---------+-------+---------------------------------+-------------------------------------------------------------------------------------------------------------------+
| 29362 | root | | NULL | Query | 99 | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| 28866 | root | | NULL | Query | 89 | Waiting for table metadata lock | SELECT person.id, person.name, ... |
| 29590 | root | | NULL | Query | 89 | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| 29295 | root | | NULL | Query | 88 | Waiting for table metadata lock | SELECT person.id, person.name, ... |
| 29713 | root | | NULL | Query | 86 | Waiting for table metadata lock | SELECT person.id, person.name, ... |
| 29571 | root | | NULL | Query | 84 | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| 29625 | root | | NULL | Query | 84 | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| 29745 | root | | NULL | Query | 81 | Waiting for table metadata lock | SELECT person.id, person.name, ... |
+-------+------+--------+------+---------+-------+---------------------------------+-------------------------------------------------------------------------------------------------------------------+
(output truncated for brevity)

After some investigation, I've discovered that I can un-stick the system by killing off a number of INSERT statements that appear to be in lock contention with the ALTER TABLE statement. When the system gets into the stuck state, the `performance_schema.metadata_locks` has data similar to the following:

mysql > SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, PROCESSLIST_STATE, PROCESSLIST_INFO FROM performance_schema.metadata_locks ml LEFT JOIN performance_schema.threads t ON ml.owner_thread_id = t.thread_id ORDER BY lock_type DESC;
+-------------+----------------------------+---------------------+---------------+-------------+---------------------------------+-------------------------------------------------------------------------------------------------------------------+
| OBJECT_TYPE | OBJECT_NAME | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | PROCESSLIST_STATE | PROCESSLIST_INFO |
+-------------+----------------------------+---------------------+---------------+-------------+---------------------------------+-------------------------------------------------------------------------------------------------------------------+
| TABLE | heart_rate | SHARED_WRITE | TRANSACTION | GRANTED | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| TABLE | heart_rate | SHARED_WRITE | TRANSACTION | GRANTED | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| TABLE | heart_rate | SHARED_WRITE | TRANSACTION | GRANTED | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| TABLE | person | SHARED_UPGRADABLE | STATEMENT | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| TABLE | person | SHARED_READ_ONLY | STATEMENT | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| TABLE | person | SHARED_READ | TRANSACTION | PENDING | Waiting for table metadata lock | SELECT person.id, person.name, ... |
| TABLE | person | SHARED_READ | TRANSACTION | PENDING | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| TABLE | person | SHARED_READ | TRANSACTION | PENDING | Waiting for table metadata lock | SELECT person.id, person.name, ... |
| TABLE | person | SHARED_READ | TRANSACTION | PENDING | Waiting for table metadata lock | SELECT person.id, person.name, ... |
| TABLE | person | SHARED_READ | TRANSACTION | PENDING | Waiting for table metadata lock | SELECT person.id, person.name, ... |
| TABLE | person | SHARED_READ | TRANSACTION | PENDING | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| TABLE | person | SHARED_READ | TRANSACTION | PENDING | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| GLOBAL | NULL | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| GLOBAL | NULL | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| TABLESPACE | db/health_target | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| SCHEMA | NULL | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| GLOBAL | NULL | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| GLOBAL | NULL | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| GLOBAL | NULL | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| BACKUP LOCK | NULL | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| SCHEMA | NULL | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| TABLE | #sql-7_e71d | EXCLUSIVE | STATEMENT | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| FOREIGN KEY | fk_health_target_person_id | EXCLUSIVE | STATEMENT | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| TABLESPACE | db/#sql-7_e71d | EXCLUSIVE | TRANSACTION | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| TABLE | health_target | EXCLUSIVE | TRANSACTION | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| TABLE | person | EXCLUSIVE | STATEMENT | PENDING | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
+-------------+----------------------------+---------------------+---------------+-------------+---------------------------------+-------------------------------------------------------------------------------------------------------------------+
(output truncated for brevity)

If I issue a `kill ` command for each of the entries in the above table marked as LOCK_TYPE=SHARED_WRITE and LOCK_STATUS=GRANTED (which are trying to INSERT into the `heart_rate` table), the ALTER TABLE command completes immediately and the system unlocks itself. Alternatively, killing the process for the ALTER TABLE statement will unlock the system as well, but that doesn't help with adding the foreign key.

After running some experiments to reproduce this scenario and taking frequent dumps of the process list (I can provide samples if useful), it appears that:
- Some INSERT statements on the `heart_rate` table that were running when the ALTER TABLE was initially issued complete successfully before the system gets stuck
- Other INSERT statements on the `heart_rate` table that were running when the ALTER TABLE was initially issued end up competing for a lock on the `person` table and must be killed in order for the ALTER TABLE to complete
- INSERT statements on the `heart_rate` table that are issued after the system gets stuck complete successfully once the system is un-stuck

Based on what I've observed, I would guess that I might be encountering an issue somewhere in the lock acquisition handling for the ALTER TABLE and INSERT statements. It seems like there is a brief period where a write lock on the `heart_rate` table is being granted to one or more INSERT statements, which may include some sort of implicit lock on the `person` table due to the foreign key that is also being granted (I'm guessing at this part). The ALTER TABLE statement is subsequently attempting to acquire an exclusive metadata lock on the `person` table, which it needs in order to create the new foreign key, but it's unable to acquire this lock because the INSERT statements are blocking it. Meanwhile, the INSERT statements are unable to move forward because they are blocked by the ALTER TABLE's acquisition of the metadata lock on the `person` table, so the system gets stuck. There's some nuance here in that the `person` table that appears to be causing the lock contention isn't directly involved in the ALTER TABLE or the INSERT statements; rather, it's referenced by foreign keys on each of the tables in those statements.

I'm not sure if this is relevant, but I managed to capture a process list that indicated the ALTER TABLE statement initially had a STATE of "After create" shortly after the statement was issued. Maybe the INSERT statements that are issued during this initial stage are granted write locks, and then get deadlocked once the ALTER TABLE tries to acquire a metadata lock?

I've tried dumping the contents of `SHOW ENGINE INNODB STATUS;`, and there's no mention of a deadlock being detected in there. It has indication that the ALTER TABLE statement is attempting to acquire a lock, but no mention of a deadlock.

Similar ALTER TABLE statements were working just fine using MySQL 5.7; we upgraded to 8.0 and then to 8.4, and have started encountering this issue. This seems like it might align with the metadata locking on both tables involved in a foreign key that was introduced in MySQL 8.0.3.

While there is some form of workaround here, it involves data loss (as valid INSERT statements need to be killed), so it's not particularly viable. It also seems problematic that the database would effectively deadlock itself on a table without identifying it as a deadlock, so I'm tempted to call this a bug, but maybe there's something else going on here. I'd be curious on hearing any thoughts or feedback on this issue, and can submit a bug report if appropriate.

coufused about ibtmp1 (4 replies)

victor victor — Thu, 20 Nov 2025 20:22:19 +0000

i read 8.0/8.4 documents, the user created temp tables are in #innodb_temp dir, the parameter innodb_temp_tablespaces_dir defines the #innodb_temp dir.

but why i also see the below description about user-created temporary tables will be in ibtmp1:
InnoDB user-created temporary tables and on-disk internal temporary tables are created in a temporary tablespace file named ibtmp1 in the MySQL data directory.

https://dev.mysql.com/doc/refman/8.4/en/temporary-files.html

How can I speed this query up? (no replies)

Kristy Atkins — Wed, 18 Jun 2025 21:28:01 +0000

I just ran this query and it took 21 seconds.
Other short queries are almost instantaneous so it's not bandwidth.

The answers table is the largest table in the database having under 17,000 records total. This app is still in development not in operation yet.

RecordID, SurveyID and AttributionID are all indexed.
SolutionID, Table1ID and Table2ID make up a unique ID in the organization3_division3 table.

SELECT COUNT(DISTINCT surveys.RecordID)
FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID
WHERE surveys.SolutionID = 3 AND surveys.RecordID IN
(SELECT SurveyID FROM answers WHERE AttributionID IN
(SELECT AttributionID FROM attrib_organization3_rl WHERE FieldID
IN (SELECT TABLE1ID FROM organization3_division3 WHERE TABLE2ID = 2)))
AND surveys.RecordID IN
(SELECT SurveyID FROM answers WHERE AttributionID IN
(SELECT AttributionID FROM attrib_forklift3_rl WHERE FieldID
IN (SELECT RecordID FROM forklift3 WHERE lnk_fuel_type = 1)))

Why do these 2 queries return different results? (no replies)

Kristy Atkins — Wed, 18 Jun 2025 20:25:51 +0000

I have 2 queries that appear to me to be logically the same.
They are returning different counts.

I need the count of all surveys where
the SolutionID = 3, the division is 2 and the fuel type is 1.

However, I also need access to tblRec1 and tblRec2.

Why are they returning different counts?
How can I get an accurate count AND have access to tblRec1 and tblRec2?

SELECT COUNT(DISTINCT surveys.RecordID)
FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID
WHERE surveys.SolutionID = 3 AND surveys.RecordID IN
(SELECT SurveyID FROM answers WHERE AttributionID IN
(SELECT AttributionID FROM attrib_organization3_rl WHERE FieldID
IN (SELECT TABLE1ID FROM organization3_division3 WHERE TABLE2ID = 2)))
AND surveys.RecordID IN
(SELECT SurveyID FROM answers WHERE AttributionID IN
(SELECT AttributionID FROM attrib_forklift3_rl WHERE FieldID
IN (SELECT RecordID FROM forklift3 WHERE lnk_fuel_type = 1)))

SELECT COUNT(DISTINCT surveys.RecordID) AS Cnt
FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID
INNER JOIN attrib_organization3_rl AS tblAtr
INNER JOIN organization3_division3 AS tblMlt
INNER JOIN division3 AS tblRec1
ON tblMlt.Table2ID = tblRec1.RecordID
ON tblAtr.FieldID = tblMlt.Table1ID
ON answers.AttributionID = tblAtr.AttributionID
WHERE surveys.SolutionID = 3 AND tblRec1.RecordID = 2 AND surveys.RecordID IN (
SELECT DISTINCT SurveyID FROM answers
INNER JOIN attrib_forklift3_rl AS tblAtr
INNER JOIN forklift3 AS tblLnkd
INNER JOIN fuel_type3 AS tblRec2
ON tblLnkd.lnk_fuel_type = tblRec2.RecordID
ON tblAtr.FieldID = tblRec2.RecordID
ON answers.AttributionID = tblAtr.AttributionID
WHERE tblRec2.RecordID = 1)

Persisent root issues through multiple re-installs (no replies)

Andrea P — Mon, 09 Jun 2025 09:52:26 +0000

I am experiencing for the first time in many years of using MySQL, an issue with the root user which persists across reinstallations - even of different mysql versions.

I have now cycled through 9.2, 9.3, 8.4.5 and 8.0.42

After a first connection to the server, whether this is done from Workench or command line, I am unable to connect again.

8.0.42 rejects the password and after a few attempts outputs the classic error
"Access denied for user 'root'@'localhost' (using password: YES)"

8.4.5, 9.2 and 9.3 instead, report that it can't login using the "native_password" plugin, although the root user was clearly set to use sha authentication

I am clueless

Hello from Cytion – Curious About MySQL in Bioinformatics (no replies)

Cytion Info — Thu, 05 Jun 2025 05:14:34 +0000

Hi everyone,

Glad to join the community! I’m part of a biotech company called Cytion, where we work extensively with biological data — including research on HeLa cells. While we’re solid on the science side, we’re now diving deeper into managing complex datasets more efficiently, and that’s what led me to MySQL.

Quick question for the community: Has anyone here used MySQL (or similar relational databases) to manage or analyze biological datasets — like cell lineage tracking, gene expression data, or HeLa cell experimental logs? We’re exploring different database architectures and would love to hear what’s worked for others in the bio/medical research space.

Looking forward to learning from all of you!

Best,
Cytion

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)) (2 replies)

Adrian Worley — Mon, 02 Jun 2025 15:40:48 +0000

I am working from a book, (Learning PHP, MySQL & JavaScript, 6th Edition, by Robin Nixon), and have started the MySQL section of the work.The first thing I need to do is change to C:/"Program Files/Ampps/mysql/bin", which I have done, and can see this from the C: prompt. It then says to enter mysql -u root -pmysql. This results in the message:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES))

Simple question, what is wrong here?

mysql should have a faster storage engine than "InnoDB" like MariaDB "RocksDB" .... (no replies)

André verwijs — Tue, 29 Apr 2025 10:19:39 +0000

MySQL should have a faster storage engine than "InnoDB" (like MariaDB "RocksDB").
InnoDB is rather slow and existing (other) storage engines are not useful for daily use.

thanks

Tables Optimization (no replies)

Mahadevan Ganesan — Mon, 21 Apr 2025 03:24:04 +0000

Hello team,
We have 2 tables. tableA with 7,285,239 rows and tableB with 178,921,615 rows.
We have to join these 2 tables and use Distinct and ORDER BY 2 columns.
We have added the required indexes in both the tables.

It is taking more than 15 seconds to show the response in the UI.

Please let us know how to optimize this query.

Table1 is having 75 columns.

Table2 DDL
CREATE TABLE `Table2` (
`Id` bigint NOT NULL AUTO_INCREMENT,
`FolderId` int NOT NULL,
`Table1_Id` bigint NOT NULL,
`ParentTable1_Id` bigint DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `IX_Table2_Table1_Id` (`Table1_Id`),
KEY `IX_Table2_FolderId` (`FolderId`),
KEY `IX_Table2_ParentTable1_Id` (`ParentTable1_Id`),
CONSTRAINT `FK_Table2_Table1_Table1_Id` FOREIGN KEY (`Table1_Id`) REFERENCES `Table1` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_Table2_Folders_FolderId` FOREIGN KEY (`FolderId`) REFERENCES `Folders` (`Id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Semantic usage of Insert and Update is wrong (1 reply)

Richard Dunne — Sun, 11 May 2025 07:13:24 +0000

From a semantic standpoint:
Insert should only be used to insert data into any empty or partially empty record(s).
Update should only be use to update (change) existing data.

Taking a 5 column table as an example:

Either
Insert into table (Column1,Column2,Column3) values (value1,value2,value3)

Or
Insert into table values (value1, value2, value3, value4, value5)

In the first example, a partial insert, multiple columns remain unpopulated.
As I stated above, semantically speaking, it makes sense to use insert to populate columns for the first time.

If column1 is Email:
Insert into tableName (column4, column5) values (value4,value5) where column1 = "EmailAddress"

Then
Update table tableName set columnName = "newValue" where Column1 = "EmailAddress"

Semantically correct, but unfortunately, not how SQL was designed or implemented.

Dynamic SQL with Dynamic xpath variable (3 replies)

P Rike — Mon, 12 Jan 2026 10:37:42 +0000

I have a loop that I am using to populate a variable with an xpath.

@xpath = concat('/record[',i,']/firstName');

I then have a variable I used to populate my sql statement.

@sql = 'select @value:=EXTRACTVALUE(xml,@xpath)';

then I use this statement to call the dynamic SQL.

PREPARE dynamic_statement1 FROM @valuesql;
EXECUTE dynamic_statement1;
DEALLOCATE PREPARE dynamic_statement1;

What is weird is it works some of the time and sometimes not and I can't find a pattern. I know my xpath is correct because if I just run this with the static xpath it returns a value.

SELECT @value:=EXTRACTVALUE(XML,'/record[1]/firstName');

Any ideas?

How can I find out the creation date of a MySQL user? (no replies)

Carlo Valencia — Fri, 21 Mar 2025 00:55:08 +0000

Hi team good morning/evening

How can I find out the creation date of a MySQL user?

Thanks a lot.

Backup / Restore does not respect umlauts (no replies)

Harald Bacik — Thu, 20 Mar 2025 12:30:40 +0000

Hey
I am new here in this forum.

I have an old mySQL 5 Server (5.5.28)
The access is by remote.
Within the server, I have a database, which has the following settings:
Charset = latin1
Collation Name = latin1_german1_ci

I do a mysqldump, like this (from my new computer):
mysqldump -u -p -h --skip-set-charset --default-character-set=latin1 --routines -r ~DESTINATION FILE~

This creates my sql file as expected.
When I open the file with Notepad++, it is opened as ANSI, where all umlauts are correct shown.

My local mySQL Server is Version: 8.4.4
The database table has charset: utf8mb4
and Collation Name = utf8mb4_0900_ai_ci.

I try to import the file with the following command:
mysql -u -p -h --default-character-set= utf8 ~databaseName~ < ~DataFile~

but the problem is, that the umlauts are not shown correctly.
For Example.
The original value is 'Nürnberg'
The value I get in my database = 'NÃ¼rnberg'
And the hex of the new value = 4EC3BC726E62657267

I also tried to use this on my new server:

SET character_set_client = latin1;
SET character_set_connection = latin1;
SET character_set_results = latin1;

But without positive result.
Can someone please help me.

THX a lot in advance

How does MySQL differ from other relational database management systems? (no replies)

jefferson sam — Mon, 10 Mar 2025 06:41:28 +0000

I want to know How does MySQL differ from other relational database management systems?

mysql crashing on starup in windows 11 (1 reply)

Martin H — Thu, 20 Mar 2025 12:46:32 +0000

I am using mysql community version on windows 11 profession version. Mysql sometimes crash when my workstation restart up. I set mysql as server and when I start mysql manually, I will not have any problem, the issue always comes windows 11 startup, I check event viewer, I got the following message

Faulting application name: mysqld.exe, version: 8.0.41.0, time stamp: 0x67601309
Faulting module name: mysqld.exe, version: 8.0.41.0, time stamp: 0x67601309
Exception code: 0x80000003
Fault offset: 0x0000000001134d82
Faulting process id: 0x1AC8
Faulting application start time: 0x1DB8D176B3C86B2
Faulting application path: C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe
Faulting module path: C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe
Report Id: d4516675-a262-483c-8b82-e5ecae2fb3ee
Faulting package full name:
Faulting package-relative application ID:

I check error log, I don't get any error message,

mysql is 8.0.41.

does anybody know where the issue.

remove letters and left zeros at the same time (no replies)

Hugo ARaujo — Wed, 05 Feb 2025 11:58:23 +0000

how do i do to remove letters and left zeros at the same time

Example:
F00801

Pretended:

801

I did TRIM(LEADING 'F' FROM i.external_code) AS Cod_Forn
and it came 00801 but, i want 801

Thank you

Watching for schema changes (2 replies)

Igor Korot — Sun, 04 May 2025 19:29:06 +0000

Hi, ALL,

Is there a way to get a notification when somebody did CREATE/ALTER/DROP TABLE/INDEX?

I know mySQL didn't provide anything in the past but maybe new version changes this?

Thank you.

IS the compare in where clause caused different? (no replies)

翊玮赵 — Mon, 23 Dec 2024 03:28:53 +0000

IN Mysql 8.0.26， the following two select sql result is different, why?

CREATE TABLE testjs(js json);

CREATE TABLE testtext(tx text);

INSERT INTO testjs values('"abc"');

INSERT INTO testext values('abc');

SELECT js = tx FROM testjs, testtext;
-------------------------------------------------

1

SELECT * FROM testjs, testtext WHERE js = tx;
-------------------------------------------------

empty set

IS the compare in where clause caused different?

Why the two sql result is different? (1 reply)

翊玮赵 — Thu, 20 Mar 2025 12:53:38 +0000

MySQL table with partitions on remote servers (only paritions) (2 replies)

Ali Saeidi — Mon, 12 Jan 2026 10:41:33 +0000

Hello, in PostgreSQL you can create a table with let's say 4 paritions (hash)
and locate each parition on different servers (remote PostgreSQL servers) and it's called (Foregin Data Wrapper). Now in MySQL i heard there is a Federated engine which you can connect to a remote server with it but it just work for a table not its paritions. I know i can use NDB but i want to have multiple MySQL servers and manage data spread by my self (based on the table paritions). Is there any solution in MySQL to make a table (Innodb) and spread its paritions on multiple remote MySQL servers ?

Copy Subset of Table to another Table (1 reply)

John Noble — Wed, 04 Jun 2025 12:40:51 +0000

Hi folks,

I have a batch file that copies a table from one database to another...

"\\mysqlPath\mysqldump.exe" -h 10.1.1.1 -uroot -ppassword dbname customers > \\sqlPath\customers.sql
"\\mysqlPath\bin\mysql.exe" -uroot -ppassword -h localhost dbname2 < \\sqlPath\customers.sql

It all works fine.

But what I really want is a subset of the source table to be copied across.
Instead of all Customers, I would like customers with a given country ID. e.g SELECT * FROM customers WHERE countryID = "DE". Can this be done?

I tried to create a VIEW insted of a table...

"\\mysqlPath\mysqldump.exe" -h 10.1.1.1 -uroot -ppassword dbname customersgermanyVIEW > \\sqlPath\customers.sql
"\\mysqlPath\bin\mysql.exe" -uroot -ppassword -h localhost dbname2 < \\sqlPath\customers.sql

But it just creates a VIEW with the same name on the destination which is of no use.

Is there any around this ?

J