Questions tagged [mysql-8.0]

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

286 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
4 votes
0 answers
232 views

MySQL: how to kill a user lock held by a terminated thread

We're using MySQL 8.0.32 hosted by AWS RDS. mysql> SHOW VARIABLES LIKE 'version'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | version | 8.0.32 | +-------...
Alex's user avatar
  • 161
4 votes
1 answer
814 views

AWS Aurora Mysql seemingly not picking PRIMARY or secondary index

Relevant System Info: Aurora Mysql 8.0.mysql_aurora.3.03 rg6.xl instances (1 writer, 2 read replicas) Total size:5.5TB (all databases combined, or just looking at the most recent Snapshot) I have been ...
Randoneering's user avatar
4 votes
0 answers
849 views

MySQL replication - Follower stuck and behind

I understand there are lots of similar problems and threads but non of those solution helped me. Please read through the thread I will try to give as much information as possible: I have setup a ...
Sisir's user avatar
  • 141
4 votes
0 answers
717 views

How to get the Slave commit timestamp using MySql replication?

I've been looking for ways to collect information about the replication delays on MySql 8. I found an alternative solution, but I imagine there's a more straightforward way to do that. By reading its ...
Wilson Neto's user avatar
3 votes
1 answer
47 views

Row Locks in Partitioned Aurora MySQL Table

I'm experiencing many row locks in a AWS Aurora AWS MySQL MySQL version=8.0 Aurora Version=3.04.0 The table is pretty big close to 1 Billion rows. The table is partitioned in 24 partitions based on ...
sonus21's user avatar
  • 131
3 votes
0 answers
3k views

Upgrade from MySQL 5.7 to MySQL 8 massive drop in performance occasionally

I have recently upgraded from MySQL 5.7 to 8 on several databases innodb engine with the same schema but different data hosted on RDS, performance was fine on all of them until the last one which was ...
Colm Canavan's user avatar
3 votes
2 answers
249 views

Why innodb locks index till the end?

Why a query BETWEEN two IDs locks index till the end? UPDATE elem SET c='' WHERE id BETWEEN 2 AND 5; -- locks id>5 for some reason The database is MySQL 8.0.33 GA. Here's the full example, taken ...
John Smith's user avatar
3 votes
0 answers
223 views

Slower delete performance in MySQL8 compared to MySQL5.7

I'm looking for some insights into why deletes from a fairly simple table seem to be much slower in MySQL8 than in MySQL5.7 Apologies in advance if this is a little fluffy at this stage and I've ...
Neil Database's user avatar
3 votes
1 answer
2k views

How to performance tune high /wait/io/redo_log_flush values in "commit"

According to the "Top SQL" view in AWS Performance Insights, commit tops the list of SQL statements in terms of average active session (AAS). And most of the time seems to be spent in wait/...
Evgeniy Berezovsky's user avatar
3 votes
0 answers
3k views

Replication instantly fails with 1032 error; Coordinator stopped because there were error(s) in the worker(s)

I have two databases, which I'm attempting to configure in a master-slave relationship (the eventual goal is to set them as master-master, but I'll settle for getting master-slave working at this ...
Peter Brunton's user avatar
3 votes
1 answer
300 views

How to create a file in query with read and write permission for all users in mysql 8

When I run this query on mysql 5.7: SELECT CURDATE() INTO OUTFILE '/var/lib/mysql-files/test.csv'; It creates test.csv with the following permissions: -rw-rw-rw- 1 mysql mysql 11 Mar 18 04:27 ...
Ahtisham's user avatar
  • 151
3 votes
0 answers
760 views

Mysql memory usage increasing after upgrading to 8 and converting some tables from MyISAM to InnoDB

I am having an issue with MySQL gradually consuming more memory every day which seems to have coincided with converting some tables from MyISAM to InnoDB two weeks ago. You can see the trend in memory ...
Ken P's user avatar
  • 31
3 votes
0 answers
2k views

Cannot access MYSQL safe mode using skip-grant-tables on Windows

I am running a MySQL server 8.0.21 on a Windows Server system. Recently I ran into an issue where my root user lost all its privileges. I am trying to gain access to the MySQL server using: mysqld ...
Augustus Buckman's user avatar
3 votes
0 answers
2k views

MySQL Group Replication - Peer address is not valid

I have been trying to set up an MySQL Innodb Cluster, initially with two databases, both MySQL 8.0.19. But I keep getting the same errors. On the first one ([email protected]:3306), I have created ...
IGGt's user avatar
  • 2,126
3 votes
0 answers
262 views

check if MySQL is using the Henry Spencer implementation for REGEX

Prior to MySQL 8.0.4, MySQL used the Henry Spencer implementation for REGEX and you could use this for word boundaries: '[[:<:]]' and '[[:>:]]' From 8.0.4 MySQL is using the ICU implementation ...
freejack's user avatar
  • 131
3 votes
0 answers
1k views

mysql 8 is failure to start after upgrade from 5.7

I've upgraded one of the slave server from mysql 5.6 to 5.7 and now 5.7 to 8. But after up-gradation steps done when i use systemctl start mysqld.service or systemctl restart mysqld facing error and ...
anand aundhekar's user avatar
3 votes
1 answer
1k views

MySQL creates tables very slow

On my home machine running Ubuntu 18.04 and MySQL version 8.0.12 with better hardware, table creation, database creation and so on are very slow. On my other computer with older hardware (slower SSD), ...
user3343767's user avatar
2 votes
0 answers
30 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
2 votes
2 answers
176 views

Unexplained MySQL memory usage

I am experiencing an issue with MySQL memory usage increasing over time to a point where OOM Killer kills the MySQL process. I have 3 servers configured with Group Replication (1 Primary and 2 ...
O Genthe's user avatar
2 votes
0 answers
18 views

Cloning failing in Mysql Group replication

We have a 3-node MySQL group replication set up in K8s. After all the respective setups, during the cloning process from the primary node to rebuild or add a secondary node to the cluster, the process ...
Pravat Dash's user avatar
2 votes
0 answers
57 views

Why does this query cause tmp table to be stored on disk even though its size is smaller than tmp_table_size?

I have MySQL server (8.0.29-21 - Percona Server) with: tmp_table_size=67108864 max_heap_table_size=67108864 log_slow_filter="tmp_table_on_disk" And in the slow query log, I can see lots of:...
elderapo's user avatar
  • 121
2 votes
1 answer
115 views

MySQL showing randomly poor performance with nested loop antijoin

I have a very simple query which is randomly performing poorly in production. I have the EXPLAIN ANALYZE outputs for both the good case and the bad case. MySQL version is 8.0.31. This is the query: ...
Agniva De Sarker's user avatar
2 votes
1 answer
756 views

MySQL consumes much more memory than configured in innodb_buffer_pool_size

Long story short: I have the buffer pool size = 40G. I have 64GB RAM on my server. But MySQL actually uses 73G of memory (53G physical and the rest swapped). MySQL version: 8.0.22-0ubuntu0.20.04.2 ...
Stalinko's user avatar
  • 181
2 votes
0 answers
254 views

MySQL 5.7- vs 8.0 referenced table update performance difference

I recently upgraded my MySQL server from 5.1 > 5.7 > 8.0. The server has over 3k databases and more than 1TB of data. All tables are InnoDB. After upgrading, I noticed huge performance ...
test_43210's user avatar
2 votes
1 answer
5k views

MySQL 8 Replication

I've got this error message: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin....
user avatar
2 votes
1 answer
192 views

Conditional aggregate on existing query

I have following table structure (I'm using MySQL 8): CREATE TABLE `listings` ( `id` bigint unsigned NOT NULL, `url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `...
Arman P.'s user avatar
  • 121
2 votes
0 answers
1k views

MySQL multi-master group replication on kubernetes

We are trying to setup MySQL multi-master group replication (GR) on kubernetes Group replication configuring instances. GR is starting on one pod after all the configurations. However the second node ...
Raghavendra V's user avatar
2 votes
0 answers
545 views

MySQL 8 - Optimize query using ST_Contains or ST_Within

I have a table called locations with ~70000 records which includes a geometry column of points and a spatial index as follows: UPDATE locations SET geom = ST_GeomFromText(CONCAT( 'POINT(', latitude, '...
adam78's user avatar
  • 155
2 votes
0 answers
282 views

What kind of complications would I face downgrading from MySQL 8 to MySQL 5?

Let's assume I wanted to downgrade to from MySQL 8 to MySQL 5.7. I know this isn't supported, but what kind of complications am I likely to encounter. The docs say, The only supported alternative ...
Evan Carroll's user avatar
  • 63.1k
2 votes
1 answer
422 views

Spatial Query using MySQL 8.0

I have a database in MySQL 5.7 where I create a Lat/Long column using "POINT", then I create a Index on this column with main key as second column. So my database is like PID, SurveyID, GeoPoint ...
Sumit Gupta's user avatar
1 vote
0 answers
10 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
1 vote
0 answers
25 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
  • 223
1 vote
0 answers
29 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
0 answers
62 views

Force index not working in Aurora MySQL 8

I have recently upgraded to Aurora MySQL engine version 8.0.mysql_aurora.3.05.2 from Aurora MySQL 5.7. I see huge performance issue after upgrading. Recently I found that I have queries whose indexes ...
Ali Mehdi's user avatar
1 vote
1 answer
62 views

How should I troubleshoot a very slow MySQL server droplet?

I'm new to the DBA forum, but not SO, and I've searched for answers on this, and tried what I've found. We have a Rails app that lives on a Digital Ocean droplet, and a bigger and more powerful ...
nimmolo's user avatar
  • 111
1 vote
0 answers
66 views

AWS Mysql unknown variable Warning Log

I upgraded AWS Aurora Mysql 5.7 to 8, but aws aurora log outputs like this. 2024-01-19T00:24:16.179185Z 0 [Warning] [MY-000067] [Server] unknown variable '...
B_B_Tod's user avatar
  • 11
1 vote
1 answer
65 views

SQL query classifying types of nodes in a binary tree

I am using MySQL in trying to solve a problem relating to a SQL query classifying types of nodes in a binary tree. Specifically, the question states as follows: You are given a table, BST, containing ...
Squirrel-Power's user avatar
1 vote
1 answer
106 views

MySQL Multi-Valued Indexes

In the following SQL, Multi-Valued Indexes are generated for the json array. ALTER TABLE table_name ADD INDEX index_name((CAST(json->'$.field' AS json_type ARRAY))). ALTER TABLE table_name ADD ...
feilong gao's user avatar
1 vote
0 answers
44 views

Error 1216, not 1451, despite reference privilege on MySQL 8

MySQL 8.0.34 Limited-privilege users see less verbose errors (1216/1217) per the design intent. I've granted reference privileges, as suggested here, in an attempt to show more verbose errors (1451/...
Chris Broz's user avatar
1 vote
0 answers
66 views

How to correctly set the database, table, and column collation in MySQL?

Background I am experiencing problems setting the collation in my local MySQL database I am using for a Drupal site I am developing. I attempted to create a local database with charset utf8mb4 and ...
GeorgeCiesinski's user avatar
1 vote
1 answer
60 views

MySQL - Slow Performing Subquery when DISTINCT Is Used

I have this query that runs very slowly when DISTINCT is used; SELECT COUNT(*) FROM (SELECT DISTINCT query_text FROM search_query WHERE (store_id IN (1)) AND (num_results > 0) ORDER BY popularity ...
Marisa's user avatar
  • 23
1 vote
0 answers
39 views

Performance degradation after upgrading to MySQL 8

We have upgraded MySQL in our application from version 5.7.43 to version 8.0.35. We are getting below exception while trying the fetch the records from the database. 231104 04:04:03 : com.mysql.jdbc....
Kishore's user avatar
  • 11
1 vote
0 answers
59 views

MySQL slow running query - Need help with tuning

I am using the Aurora MySQL Serverless V2 database and facing an issue with the below query taking longer. In the end, I have listed different approaches that I tried to fix this. The table has around ...
Ravindra's user avatar
1 vote
0 answers
57 views

Adjust SQL query to select only one record

I have three tables: An adjacency-list table, territories with fields id, alignment_id, three_left, tree_right, depth and deleted_at (omitted from table for brevity, all values null). id alignent_id ...
ioni's user avatar
  • 61
1 vote
1 answer
414 views

Moving from MySQL 5.7 to MySQL 8.0

I'm currently running MySQL 5.7 in one of the key servers. This version reaches end of life soon, so it's time to move to 8.0. Or give it a shot and jump on 8.1? This one is also running on dated ...
Marek's user avatar
  • 13
1 vote
0 answers
57 views

Why doesn't mysql prompt password?

$ mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) $ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your ...
Gqqnbig's user avatar
  • 111
1 vote
0 answers
38 views

Why does mysqld use much more memory than expected?

Running MySQL 8.0.24, I have been recently seeing frequent instances where the RHEL7 OS will kill the mysqld process as the system memory gets all used up. The server has 65 GB of memory, and I think ...
TFR3's user avatar
  • 11
1 vote
0 answers
70 views

Mysql8 Unable to order the result of WITH RECURSIVE query

I am using the WITH RECURSIVE query to generate a hierarchy of users. The data and the query used is shared in https://www.db-fiddle.com/f/oTbCcMQb5a4B6V96DAYJHG/0 . The query is returning the results ...
Nandakumar V's user avatar
1 vote
0 answers
31 views

MySQL8 database painfully slow after failed pt-osc

In another question we attempted to update a table in our MySQL8 db. That didn't work out, but since then our database (even after both DB and machine restarts) seems painfully slow at the strangest ...
phil's user avatar
  • 153
1 vote
1 answer
1k views

Reduce 'wait/sql/table/io/handler' load of high frequent updates on the same rows

We have a time-series application that also writes all the records to kind of a 'head' table in MySQL. This is somewhat of a legacy, but very interwoven and hard to get rid of. The problem is, that it'...
Halfgaar's user avatar
  • 191

1
2 3 4 5 6