Questions tagged [mysql-8.0]
MySQL version 8.0 - please also tag with mysql for search purposes.
224
questions with no upvoted or accepted answers
4
votes
0
answers
233
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 ...
- 141
4
votes
0
answers
624
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 ...
- 41
3
votes
1
answer
118
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 ...
- 125
3
votes
0
answers
96
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 ...
3
votes
0
answers
667
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/...
3
votes
0
answers
2k
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 ...
3
votes
1
answer
258
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 ...
- 141
3
votes
0
answers
674
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 ...
- 31
3
votes
0
answers
1k
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 ...
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 (username@1.2.3.4:3306), I have created ...
- 2,046
3
votes
0
answers
200
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 ...
- 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 ...
3
votes
1
answer
991
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), ...
- 31
2
votes
1
answer
166
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
...
- 161
2
votes
0
answers
193
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 ...
- 21
2
votes
1
answer
4k
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....
Ayaa Ahmed
2
votes
1
answer
136
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,
`...
- 121
2
votes
0
answers
927
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 ...
2
votes
0
answers
451
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, '...
- 155
2
votes
0
answers
242
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 ...
- 60.3k
2
votes
1
answer
298
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
...
- 175
1
vote
0
answers
28
views
+100
Handling server upgrades through packaging system (apt) when running group replication
I'm running a test cluster with three nodes - db-[1-3]. All three were happy members of a single primary group replication cluster. They were all configured with group_replication_start_on_boot=on. ...
- 11
1
vote
1
answer
51
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'...
- 191
1
vote
1
answer
66
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 ...
- 11
1
vote
0
answers
15
views
Search for a record which has an exact set of many-to-many related records
I have a straightforward many-many arrangement:
scans
id
1
2
products
id
1
2
3
product_scan:
scan_id
product_id
1
1
1
2
2
1
2
2
2
3
When I'm about to create a new scan entry with a ...
- 261
1
vote
0
answers
33
views
Ways to reduce latency in importing data from csv files into MySQL Workbench when connected to an RDS MySQL db Instance
This question is a follow-up to this question I asked on Stack Overflow yesterday after answering my own question by creating a Aurora MySQL db Instance and establishing a connection between it and ...
- 227
1
vote
0
answers
44
views
Persistent problems connecting MySQL Workbench to an AWS RDS DB
I started off my attempt to figure out how to connect my local MySQL Workbench (8.0.32) client/application to an RDS Database on my analytics team's AWS Account by asking Bing AI and this is what it ...
- 227
1
vote
0
answers
132
views
Crashing after upgrade to MySQL 8.0.31
We recently upgraded to MySQL 8.0.31 on RDS. The upgrade went fine, but a couple days later the instance crashed because of data corruption and wouldn't restart. We ended up having to go boot in ...
- 111
1
vote
0
answers
8
views
How xtrabackup restores data to the replica server
The product is a microservice architecture, divided into multiple databases. In order to facilitate the background management program to facilitate cross-database linked list query, a replica server ...
- 11
1
vote
0
answers
244
views
Upgrade Replication MySQL from 5.7.34 to 8.0.28 without database downtime
I have to upgrade a MySQL replication topology from 5.7 to 8.0 without database downtime. It’s MySQL Community Edition.
There is one source and 5 replicas in the topology and connections happen via ...
1
vote
1
answer
874
views
I can't properly set permissions to MySQL config files
Yesterday I ran out of hard drive space for the database in the middle of a dump; Luckily this was in development and I had my backups... but I like to see these cases in detail and document them in ...
user237107
1
vote
1
answer
528
views
Optimal way to SELECT all rows in a table that are not referenced by a foreign key?
In MySQL I have a table "resources" that represents a list of resources.
eg:
CREATE TABLE resources (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
...etc.....
- 139
1
vote
0
answers
82
views
MySQL - Too many nested sub-expressions in a full-text search
I have a full-text search query with many sub-expressions like this:
SELECT DISTINCT id
FROM search_table s
WHERE MATCH (s.search) AGAINST ('+(failure* protection*) +(math* police*) +(insect* hall*) +(...
- 644
1
vote
1
answer
165
views
MySQL - Nested loops count too high resulting in query taking too long
I am seeing high response time for a MySQL query. I have two similar queries:
SELECT `orders`.`id` FROM `orders` WHERE (`orders`.line_count = 0 OR
`orders`.account_type_id IS NULL OR
...
- 153
1
vote
1
answer
2k
views
MySQL does not use index, does full table scan instead
EDIT: See further developments below.
I have an InnoDB table with the following schema:
CREATE TABLE `data` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`app_id` CHAR(27) NULL DEFAULT NULL,...
- 155
1
vote
0
answers
113
views
Aurora Serverless 2 MySQL 8.0 doing a mysqlpump to dump users
Recently i ask about using roles in MySQL 8.0 using aurora 3.0 in a Serverless 2 RDS cluster.
They answered me that the correct way to do that is using mysqlpump with the --users parameters to dump ...
1
vote
0
answers
38
views
How to create a comprehensively auditable database that is updated from multiple concurrent sources?
I have various different Python scripts running concurrently updating a MySQL database from a circa 15 API and web scraping sources. In another script a continuous loop runs that queries batches of ...
- 131
1
vote
0
answers
2k
views
Join JSON column using an index
I have two tables:
definition (7,000 rows):
+--------------+----------+
| definitionId | nameRef |
+--------------+----------+
| 1 | [1] |
| 2 | [1,2] |
+--------------+-...
- 155
1
vote
0
answers
503
views
Error MY-000067 - unknown variable 'validate-password=FORCE_PLUS_PERMANENT'
I'm struggling with password valaidity settings in 8.0.28. In this version component is in use instead of plugin.
Which means password validity component installed via:
INSTALL COMPONENT 'file://...
1
vote
3
answers
47
views
Will a query which uses IS NULL always perform bad on large tables?
I've got the following table :
CREATE TABLE `file` (
`id` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`track_id` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`...
- 153
1
vote
0
answers
122
views
MySQL: Can't login after forgotten root password
I have a CentOS Linux 8 machine with mysql Ver 8.0.26 I forgot the root user password tried to reset with the following commands
systemctl stop mysqld
mysqld_safe --skip-grant-tables &
mysql
...
- 11
1
vote
1
answer
98
views
The performance_schema database of MySQL has been deleted by mistake. Is there any way to recover it?
Yesterday I dealt with a production data problem for them. I used Navicat during operation. I didn’t know how to press the backspace key. Because it is a Mac system, this backspace key was deleted. I ...
- 11
1
vote
0
answers
106
views
Why Isn't the MySQL Index Immediately Available?
We are in the process of moving from AWS RDS MySQL 5 to AWS RDS MySQL 8. We have a script that runs a few dozen queries. The last few look like this:
ALTER TABLE my_right_table ADD INDEX (col_a, col_b)...
- 11
1
vote
1
answer
284
views
replicate-wild-do-table=channel_name:db_name.% doesn't work in MySQL 8.0 for channel based replication
I am trying to configure a replication topology where there will be one slave which will connect to two masters. Percona Server 8.0.25.
Master1- main master
bkp_server - slave to master1 and ...
1
vote
0
answers
413
views
Slow query count with MySQL 8 where table has JSON columns
Note, I think I'm encountering this bug which was marked as "Can't replicate": https://bugs.mysql.com/bug.php?id=97709
That said, I'm interested if anyone has a workaround to query ...
- 111
1
vote
0
answers
248
views
MySQL Replication stop after a huge amount of transactions on Master
Running MySQL 8 for months now, without much troubles, I still have randomly some problem of replication between the Master and its Slave.
Problems means, all works, both instance works, still the ...
- 111
1
vote
1
answer
159
views
Massive performance degradation in upgrade from MariaDB 5.5 to MySQL 8
As part of a complete server move and upgrade (OS, Apache, and db) went from MariaDB 5.5 to MySQL 8.0.21. Hardware specs unchanged. Unfortunately despite many hours of reading and experimental tuning ...
- 11
1
vote
1
answer
130
views
Is it possible to join MySQL 8.0 to a cluster of MySQL 5.7
I have a MySQL 5.7 (5.7.19-17-57-log Percona XtraDB Cluster) cluster using Galera, but I am in the middle of upgrading to 8.0 -- 8.0.23-14 Percona Server (GPL), Release '14'. I wasn't sure if it was ...
- 113
1
vote
1
answer
613
views
MySQL8 crash recovery
My dev MySQL-8 server crashed last night and would not restart. The error is:
[ERROR] [MY-012646] [InnoDB] File ./ibdata1: 'open' returned OS error 71. Cannot continue operation
I tried the classic &...
- 111
1
vote
1
answer
246
views
In MySQL 8, can we make group replication db as readonly?
I have configured group_replication on 4 db. Out of which I am trying to make 2 db as slave(only read). Even though I make read_only, user can write data in it.
Is possible to make 2 as master and 2 ...
