Questions tagged [mysql-8.0]

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

176 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
4 votes
0 answers
491 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 ...
user avatar
3 votes
0 answers
522 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 ...
user avatar
  • 31
3 votes
0 answers
907 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 ...
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 (username@1.2.3.4:3306), I have created ...
user avatar
  • 1,920
3 votes
0 answers
152 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 ...
user avatar
  • 131
3 votes
1 answer
829 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), ...
user avatar
  • 31
2 votes
1 answer
45 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, `...
user avatar
  • 121
2 votes
0 answers
629 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 ...
user avatar
2 votes
1 answer
140 views

Mysql self Join query with Limit never terminates

I'm running an experiment with the query Select Distinct table_1.id1, table_2.id1 FROM image as table_1, image as table_2 WHERE table_1.id2 = table_2.id2 LIMIT K; When I run the query ...
user avatar
2 votes
0 answers
204 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 ...
user avatar
  • 56.3k
2 votes
1 answer
244 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 ...
user avatar
2 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 ...
user avatar
1 vote
0 answers
13 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 ...
user avatar
1 vote
0 answers
344 views

MySQL Workbench mysqldump: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

When using workbench on Mysql 8, I get the following error: mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces I'...
user avatar
1 vote
0 answers
30 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 ...
user avatar
  • 131
1 vote
0 answers
62 views

Join JSON column using an index

I have two tables: definition (7,000 rows): +--------------+----------+ | definitionId | nameRef | +--------------+----------+ | 1 | [1] | | 2 | [1,2] | +--------------+-...
user avatar
1 vote
1 answer
61 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 ...
user avatar
  • 121
1 vote
0 answers
62 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://...
user avatar
1 vote
0 answers
51 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 ...
user avatar
  • 11
1 vote
1 answer
38 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 ...
user avatar
1 vote
0 answers
42 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)...
user avatar
  • 11
1 vote
0 answers
102 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 ...
user avatar
  • 111
1 vote
0 answers
89 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 ...
user avatar
1 vote
1 answer
98 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 ...
user avatar
  • 11
1 vote
1 answer
67 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 ...
user avatar
1 vote
1 answer
157 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 ...
user avatar
1 vote
0 answers
90 views

Help me tune Mysql instance to support more users

I'm currently supporting, A Moodle 3.10 instance, running MySQL 8.0.20. This is running through an Azure Provider as a Standard DS11(2 vcpus, 14 GiB memory). Running as a LAMP stack server. Currently, ...
user avatar
1 vote
0 answers
353 views

MySQL 8.0.x: Discard temporary table left behind during a pre-migration (5.7.x) crash when the parent schema is already dropped

We had a MySQL 5.7.x instance serving a production schema foo, and the server itself was migrated to 8.0.21 on 13 Sep 2020. Recently we migrated data from foo to a new schema bar. After migration we ...
user avatar
1 vote
1 answer
4k views

MySql InnoDB crash recovery - File ./ibdata1: 'open' returned OS error 71. Cannot continue operation

Mysql 8.0.23 on CentOS 7.9 with WHM I ended up with a corrupted InnoDB table. I gave up on a long query and killed it. Then the query was still blocking so I did a forceful server reboot. I know now ...
user avatar
  • 11
1 vote
4 answers
910 views

Mysql 8.0.21 long lived prepared statements have a memory leak ? or are we doing something weird

-- updated the question because it was easy to initially assume I was asking for mysql tuning tips, although some of the tips did help me narrow down the problem -- -- further update. After doing ...
user avatar
1 vote
2 answers
309 views

Mysql Windows Performance Problem

My system: 2X E5 2680 V3 Cpu 32 GB Ram Windows 7 Pro Apache 2.4.41 Mysql 8.0.17 I have a script and this for Customer Online Chat Script. I have 10 clients for customer support. It uses php json ...
user avatar
1 vote
0 answers
4k views

MySQL 8.0 - Could not acquire management access - Administration issue

I am using MySQL Workbench for many months, it was working fine. Today, when I tried to open Server Status in the MANAGEMENT tab, below error occurred. MYSQL80 and Windows Management Instrumentation ...
user avatar
  • 11
1 vote
0 answers
146 views

MySQL - can connect via "reverse engineer" but not by double-clicking the connection using the same connection info

A connection to MySQL Server on the same network fails when double-clicking the connection in MySQL Workbench (i.e. trying to connect the standard way). It gives the error message: "Cannot ...
user avatar
  • 121
1 vote
0 answers
285 views

Process with Command 'Killed' and State 'Executing'. Not getting killed

Last day, when I ran a count(*) query on a table. It didn't provide any results. It ran forever even. So I tried killing those queries (I have tried it multiple times). As I'm using AWS RDS, I killed ...
user avatar
1 vote
0 answers
86 views

Getting error while starting slave on MySQL slave./ Backups in MySQL 8.0.20

I want to set up below command for daily MySQL database backup.suppose We have more than 50 MySQL database in server. I am using below command to take backup of all databases on server. Note: I am ...
user avatar
1 vote
1 answer
581 views

Mysql Alter table with Instant algorithm

I just read this MySQL 8 official docs for new features of online DDL operation, where new algorithms added INSTANT. but I am a little bit confused about its working. Can someone please help me to ...
user avatar
1 vote
0 answers
28 views

MySQL condition filtering estimate while planning includes `A.col op B.col` but excludes `A.col op val or B.col op val`

In the condition filtering feature that MySQL introduced from version 5.7, has filter estimates for various condition formats in the join order, to estimate the number of rows that enter the join ...
user avatar
  • 165
1 vote
0 answers
326 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, '...
user avatar
  • 123
1 vote
1 answer
28 views

Grant privileges to all users on a single table

Is it possible to grant privileges (such as SELECT, INSERT, UPDATE, etc) to all the users for a specific table at the same time? Here is what I tried but it didn't work out... GRANT SELECT ON ...
user avatar
1 vote
0 answers
90 views

MySQL 8.0.18 - ST_Area returns invalid value

I have a problem with ST_Area function in MySQL 8.0.16. The result of the procedure call returns a different result than any library used to calculate the area of a polygon. Example: I have a ...
user avatar
  • 111
1 vote
1 answer
3k views

MySQL CURDATE() and CURRENT_TIMESTAMP return different value?

I am facing a little confusion because of CURRENT_DATE value and CURRENT_TIMESTAMP value shows different date on MySQL. Scenario : 1) User calls API from Lumen backend to insert data on a table. One ...
user avatar
1 vote
1 answer
79 views

LOAD DATA keeps CPU usage high after it finishes

I'm loading multiple files into the database using the LOAD DATA LOCAL INFILE command combined with Golang database/sql driver like this: mysql.RegisterLocalFile(fileName) _, err = dbConnection.Exec(...
user avatar
  • 11
1 vote
0 answers
91 views

Character set issue

Error 'Character set '#255' is not a compiled character set and is not specified in the '/user/share/mysql'. Facing error in replication channel from mysql 8.0.16 to mysql 5.7.25.Is there any ...
user avatar
1 vote
0 answers
290 views

Master MySQL5.6 - Slave MySQL 8.0 slow relay log reads

We have 4 Servers, 1 Master and 3 Slaves. 2 slaves are on 5.7 version and working perfectly, whereas 3rd slave that is mysql 8 (test version). 3rd slave imported dump in 1 hour when other took 3-4 ...
user avatar
  • 111
1 vote
0 answers
260 views

Why is my InnoDB table size so much larger than the data?

I have a MySQL table (version 8.0.13). One table is unreasonably large and doesn't correspond to the current data. I rebuilt the table by running ALTER TABLE users FORCE and OPTIMIZE TABLE users, but ...
user avatar
  • 111
1 vote
0 answers
328 views

MySQL 8 more than doubles the size of MyISAM table after optimization

I recently upgraded from MySQL 5.7 to 8.0. I have noticed some changes from 5.7 to 8. But I have not been able to find any discussion on following situation that I have been puzzling for a while. I ...
user avatar
1 vote
2 answers
36 views

Showing data that is not already in the data base

I have the following problem. I want to search a query in an existing data base and that the result shows me the entries of the query that are NOT already in the database. Minimal example: | ID | ...
user avatar
0 votes
0 answers
15 views

How does mysqlbinlog restore work with gtid_mode=ON?

It doesn't seem to work at all for me if gtid_mode is on. Is this the reason? "In addition, once a transaction with a given GTID is committed on a given server, any subsequent transaction having ...
user avatar
  • 101
0 votes
1 answer
54 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,...
user avatar
0 votes
0 answers
18 views

MySql query not using spatial index

We have the below query that we are calling to find all rows within another table's row geometry. When I do a explain, it shows the spatial index spidx_table2 as a potential index, but says it is ...
user avatar
  • 146