Skip to main content

Questions tagged [mysql-8.0]

MySQL version 8.0 - please also tag with mysql for search purposes.

Filter by
Sorted by
Tagged with
0 votes
0 answers
4 views

Mysql Crashing on: [FATAL] Semaphore wait has lasted > 600 seconds

Once a day or two we get the bellow error and the mysql crashingt: [ERROR] [MY-012872] [InnoDB] [FATAL] Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears ...
idan ahal's user avatar
  • 101
1 vote
1 answer
16 views

Decrease high response time when reading large table in MySQL

I have a table with around 80~ million rows called parts. I have a simple query like this SELECT line_code, part_number, compressed_part_number, ...[Others columns] FROM parts WHERE (@partNumber = ...
fluid undefined's user avatar
0 votes
0 answers
10 views

why the mysql 8 database auto generate a long running transaction

Today when I run an update command run spring boot application, the MySQL 8 generate a long running transaction and block the next update command. This is the output when I check the transaction: ...
Dolphin's user avatar
  • 785
0 votes
0 answers
22 views

MySQL Causing OOM after Upgrading to 8.0.37

Recently, we faced severe & unrecoverable corruption of database (a bug from MySQL 8.0.27, but only hit us 2 years in). So we upgraded to MySQL 8.0.37, and everything's good so far. But there's a ...
Patrick Teng's user avatar
0 votes
0 answers
33 views

Random long writes (waiting for handler commit)

I have some INSERT/UPDATE queries who takes a long time randomly. I try to launch the same INSERT several times with profiling enable. And I remark that the duration is always in waiting for handler ...
Matt's user avatar
  • 1
0 votes
0 answers
12 views

Variable time execution for INSERT/UPDATE

I do an INSERT/UPDATE in a table who has around 1+ million rows in MySQL 8.0. I don't know why I have different execution times for the same data to insert. For example, I try to insert exactly the ...
Matt's user avatar
  • 1
0 votes
1 answer
17 views

Failing to run dba.configureInstance against MySQL cluster 8.4 server on Ubuntu noble

I want to run the commands listed here: https://dev.mysql.com/doc/mysql-shell/8.4/en/check-instance-configuration.html I've added the latest MySQL package mysql-apt-config_0.8.30-1_all.deb to install ...
J__'s user avatar
  • 1
0 votes
1 answer
41 views

Conditional update

I would like to create a conditional update on an audit table, such that if the update on the main table succeeds, then the update on the audit table is performed. Right now I have this CREATE DEFINER=...
tcelvis's user avatar
2 votes
0 answers
29 views

Concurrent update to one record in same table created a deadlock between primary key and secondary index

Concurrent reqs under odd case has triggered the same update query twice which surprisingly led to deadlock between primary key and secondary index update in the same table. The update query will ...
kumarssk8's user avatar
0 votes
1 answer
31 views

MySQL 8.0 no longer updates information_schema.tables after optimizing a table

We have recently been undertaking a purge of data from our databases, which has left some tables with huge amounts of free space (in some cases hunderd of GB's). So I have started a process of going ...
IGGt's user avatar
  • 2,146
0 votes
1 answer
49 views

Removing partitions from MySQL table

I have table containing 3.2TB data in partition named default_partition, & all writes/read goes to this partition, as we have defined partitions on table like below : PARTITION BY RANGE (to_days(...
tusharRawat's user avatar
1 vote
0 answers
47 views

MySQL root password reset

I've spent many an hour trying to reset my mysql root password with no luck. I'm running an up to date patched Ubuntu server 22.04.4 with mysql 8.0.36. My latest attempt is using sudo mysqld_safe --...
user2112's user avatar
0 votes
0 answers
15 views

Lost connection to MySQL server during query for a not-so-complex long query

I have this query : SELECT some_15_fields, (SELECT GROUP_CONCAT(tb_inv_private_ip SEPARATOR ', ') FROM `network_interface` WHERE network_interface.tb_inv_id=id) as 'private_ip', (SELECT GROUP_CONCAT(...
anjanesh's user avatar
  • 279
0 votes
0 answers
28 views

Adding a new fulltext column results in degraded performance

I have a search table with a schema something like this: CREATE TABLE `product_search` ( `product_id` int NOT NULL, `search` text NOT NULL, PRIMARY KEY (`product_id`), FULLTEXT KEY `search` (`...
Eaten by a Grue's user avatar
1 vote
0 answers
48 views

MySQL InnoDB Stored Procedure locks table?

MySQL 8.0 InnoDB I have some sql statement like SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION; INSERT INTO MyTable SELECT Name FROM MyTable ...
onets's user avatar
  • 11
0 votes
0 answers
78 views

problems with lower case table names

I just had to reinstall on my Mac the clean OS (monterey), installed a time machine backup and noticed that the mysql DB server 8.0.28 (oracle mysql community edition) would not start, or well it ...
Canelo Digital's user avatar
0 votes
0 answers
92 views

Upon PC restart, MySQL service on local computer "started and then stopped"

I'm working in MySQL on a local (Windows) computer. I have a somewhat recurring issue I have with managing the server. I cannot replicate this error because I do not know what causes it. Sometimes, I'...
ZZ Top's user avatar
  • 1
1 vote
0 answers
14 views

How to stop losing control of a user I just created due to SYSTEM_USER privilege in MySQL

We have recently been plagued by users getting created with the SYSTEM_USER privilege by users who don't have that privilege. What do we need to do from a systematic perspective to stop this from ...
juacala's user avatar
  • 125
0 votes
0 answers
47 views

MySQL Exec_Master_Log_Pos is slow to update in replica compared to actual data

I'm trying to use Exec_Master_Log_Pos from SHOW REPLICA STATUS to check whether the replica is caught up to the master. However, Exec_Master_Log_Pos appears to take a while to update. When I add a row ...
Victor L's user avatar
  • 101
0 votes
0 answers
43 views

Random MySql client error "got an error writing communication packets"

I have several different Linux servers running MySQL 8.0.x instances (ranging from 8.0.23 to 8.0.33). I occasionally get "error writing communication packets" from clients when connecting to ...
Alberto Pastore's user avatar
0 votes
0 answers
39 views

MySQL 8 window functions optimization with multiple greatest-n-per-group

I'm struggling to make efficient this sort of query: with a as ( select -- the battery cycle cycles_bat_1, -- interested on a single result row per cycle, so using ...
Michele Mariotti's user avatar
0 votes
0 answers
37 views

MySQL Table Locks Immediate Metric

This Datadog graph is related to the Table Locks Immediate defined as the "number of times that a request for a table lock could be granted immediately" based on this. For over a year, this ...
Brian Estrada's user avatar
0 votes
0 answers
34 views

Can't get hostname for your address flooded in error log even if skip-name-resolve is enabled and many unauth users in processlist in percona 8.0.25

Information about the currently configured percona servers : We are using percona 8.0.25 server. Mysql service is running with skip-name-resolve enabled. host_cache_size=753 but the ...
Sana Ansari's user avatar
3 votes
1 answer
495 views

"#1292 - Incorrect datetime value" when time is within one hour

I'm running into a bizarre error with a timestamp column in a MySQL DB. When inserting the date/time value, it fails with a #1292 error if the time is between 02:00:00 and 02:59:59. Here is a sample ...
lcdservices's user avatar
0 votes
1 answer
28 views

Is there a way to send DDL query asynchronously, disconnect client & poll for completion?

AFAICT when you send a query to MySQL from any client it always blocks and waits for the server to respond with success or failure. When performing a slow operation like adding a new index on large ...
Anentropic's user avatar
0 votes
1 answer
78 views

Why does my IOPS keep increasing during bulk batch inserts?

The database in question: AWS RDS, MySQL 8, InnoDB. GP3 storage. I am attempting to do a bulk insert of rows to multiple tables in the db. I have omitted all secondary indexes from the target table, ...
Anentropic's user avatar
-1 votes
2 answers
45 views

How important is the "clustered" property of MySQL PK index?

I am importing ~50M rows into MySQL 8, InnoDB. It's on AWS RDS with GP3 storage. The unique key of the rows is a uuid-like string. When querying we will never care about this unique key, except when ...
Anentropic's user avatar
1 vote
1 answer
24 views

Will a replicated event cause issues?

I've got the replication configuration shown in the image. Everything replicates to everywhere. One of the tables maintains session data which has rows that must be deleted due to expiration. I ...
user22044385's user avatar
1 vote
1 answer
27 views

MySQL tempfolder Full

I have a mysql server hosted in an Azure VM. This is a slave where the data is being replicated from an on premise Master. Configurations are mentioned below. users are frequently getting an error ...
Vinodh Ashok's user avatar
1 vote
1 answer
40 views

How to choose hardware for MYSQL Server in Azure - Flexible Server

I have a MYSQL Flexible Server in Azure with the following hardware configuration: MYSQL 8.0 Compute Tier: Business Critical - 16 vCores Size: E16ds_v4 Storage: 269 GB AutoScale IOPS RAM: 128 GB I ...
SakZepelin's user avatar
0 votes
0 answers
20 views

Issue with group by on left join attempting to calculate totals

I have the following table (dummy data): project keyword_id listing_id clicks ctr impressions pos date 1 1 2 3 4 5 6 2024-04-14 1 1 3 3 4 5 6 2024-04-14 1 2 4 5 6 7 8 2024-04-14 1 2 5 5 6 7 8 ...
CSystems's user avatar
1 vote
0 answers
56 views

During starting InnoDB, error log shows 'Could not open log file.'

2024-04-11T05:07:26.565563Z 0 [System] [MY-015015] [Server] MySQL Server - start. 2024-04-11T05:07:26.742860Z 0 [System] [MY-010116] [Server] /opt/homebrew/Cellar/mysql/8.3.0_1/bin/mysqld (mysqld 8.3....
wang ted's user avatar
0 votes
1 answer
24 views

Convert character encoding in big database

We have big database (~1TB) and we need to update character encoding for all tables. I am running following SQL: SET FOREIGN_KEY_CHECKS = 0; ALTER DATABASE wallmob_etail CHARACTER SET utf8mb4 COLLATE ...
Justinas's user avatar
  • 101
0 votes
1 answer
222 views

How to set up component_keyring_file in MySql 8.3

I am trying to set up tablespace encryption in MySQL 8.3 and I can activate the keyring-file plugin. The plugin, however, throws a deprecation notice suggesting migration to component_keyring_file. I ...
Simas Joneliunas's user avatar
2 votes
0 answers
84 views

'Deadlock found when trying to get lock; try restarting transaction' in AWS RDS MySQL 8.0.36 after upgrading from MySQL 5.7.42. Django v4.1.6

Following an upgrade of our AWS RDS instance from MySQL version 5.7.42 to version 8.0.36, we are encountering persistent deadlock issues across concurrent transactions, affecting even basic read ...
Karan Raj's user avatar
1 vote
1 answer
41 views

Many items in `IN` clause causing full table scan earlier than expected

We have a search index table, provided by a CMS, that is of the form: CREATE TABLE `craft_searchindex` ( `elementId` int NOT NULL, `attribute` varchar(25) CHARACTER SET utf8mb3 COLLATE ...
msbit's user avatar
  • 121
-1 votes
2 answers
108 views

MySQL 8 - very slow query when ORDER BY is used with hash join

I encountered an issue today and I'm not sure if this is a bug in MySQL 8 or if I'm doing something wrong. Preface At the bottom of the post I attached a query to reproduce the issue locally. I have ...
samu's user avatar
  • 101
0 votes
1 answer
222 views

drop database hangs with "Waiting for table metadata lock" in MySQL8 RDS on AWS

We have a large table in a database that a user tried to run DELETE * FROM table on. It was still running the next day so we killed the process. I tried renaming, dropping and truncating the affected ...
George's user avatar
  • 126
0 votes
0 answers
10 views

KILL QUERY Flooding in General Query log for MySQL 8.0.25

Problem : We have two MySQL servers in master master replication. We can see many KILL QUERY statements in MySQL general log specific to stored procedures as below : 2024-03-20T21:54:45.056981+05:30 ...
Sana Ansari's user avatar
1 vote
0 answers
26 views

How to find IP subnet that is decomposed into multiple sub-one?

I have a table which like like that: Id IpRange Mask 1 127.0.0.0 24 2 127.0.1.0 24 3 127.0.2.0 24 I'm checking if an IP is in the range thanks to this : (inet_aton('here is the IP') & ((POWER(...
Elikill58's user avatar
  • 221
0 votes
0 answers
44 views

Reduce varchar column size in a production database

We have a database running on AWS Aurora Mysql with version 8.0.mysql_aurora.3.04.1 as a regional database. There is a table in it with a column with varchar size 200. We want to reduce it to varchar(...
Tanmay Sule's user avatar
0 votes
1 answer
88 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
0 votes
0 answers
63 views

Performance Differences in JOIN query Between MySQL 5.7 and MySQL 8.0

Hello MySQL Community, I wanted to share some observations regarding the performance differences between MySQL 5.7 and MySQL 8.0, specifically related to join algorithms. In a recent project, I ...
Amarjit Singh's user avatar
0 votes
0 answers
29 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
0 votes
0 answers
99 views

Getting MySql Error 'aio read' returned OS error 0

MySql server goes down after running for couple of months. I can see below error in the MySQL error logs. 2024-02-12T14:15:58.414920Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' ...
thab's user avatar
  • 1
1 vote
2 answers
41 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
43 views

INNODB FULLTEXT Index Performance and Optimization Strategy without Down Time

We have an INNODB table specifically for text searching which has a text column and a FULLTEXT index. This table mostly get inserts and updates and almost no deletes in our model. A typical table ...
Jason Riffel's user avatar
1 vote
1 answer
141 views

high disk Wrrite IO on mysqld

I'm facing a rather weird issue with mysqld v8.0.36 there's constantly very high disk write operations(~300MB/s) coming from mysqld. Although there's a single database hosted on this server(wordpress) ...
Menelaos pc's user avatar
1 vote
0 answers
51 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
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

1
2 3 4 5
14