Questions tagged [mysql-8.0]
MySQL version 8.0 - please also tag with mysql for search purposes.
566
questions
0
votes
1
answer
39
views
How does MySQL handles GROUP BY?
I'm on Windows 10, MySQL Workbench. I'm on MySQL 8.0.
Here's the dataset. northwind.sql.
https://pastebin.com/bMgjXvfT
Objective:
Write a query to get the order ID, customer's name, grand total of ...
- 31
1
vote
1
answer
940
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 ...
CommunityBot
- 1
0
votes
1
answer
428
views
MySQL 8.0.31 crashing frequently on Windows 2012R2
I'm running MySQL 8.0.31 on Windows 2012 R2 64-bit on a AWS EC2 VM.
Frequently, the MySQL service crashes. I tried to reinstall MySQL 8.0.31 and rebuild the database from a dump, but it still crashes ...
CommunityBot
- 1
0
votes
1
answer
17
views
AWS RDS MySQL upgrade from 5.7 to 8 failing due to ROW_FORMAT
We need to upgrade our DB to version 8, but we're getting an error in the migration log that's preventing us from upgrading.
Creating indexes larger than 767 bytes on tables with redundant row format ...
- 12.6k
0
votes
1
answer
588
views
How to take single database backup using mysqlbackup command in Mysql 8.0.18 version DB
I have a mysql instance(8.0.18-commercial MySQL enterprise server) with 8 databases in it and out of those I only need to take the backup of DB named 'Test1'.
Please note that its 300 GB sized DB ...
CommunityBot
- 1
0
votes
0
answers
13
views
Last GTID on replica matches source but data is not there
We have a following set up:
Multi A-Z source RDS instance (MySQL 8.0.32)
Single #1 A-Z replica RDS instance (MySQL 8.0.32)
Single #2 A-Z replica RDS instance (MySQL 8.0.32)
ProxySQL (2.5.2)
Binlog ...
- 97
0
votes
2
answers
446
views
Known pitfalls with MySQL partitioning
Are there any known pitfalls with MySQL partitioning when server behavior differs a lot from the expected one?
I'll try to explain what I mean. Let's create partitioned table and fill it with random ...
CommunityBot
- 1
0
votes
4
answers
260
views
Performance issues after upgrading from MySQL 5.7 to MySQL 8.0
Recently I upgraded MySQL on my server from newest 5.7.x to newest 8.0.x and I imported my entire database with multi-indexes that I created when I was using MySQL 5.7.x. These indexes optimize SELECT ...
- 1
1
vote
0
answers
24
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 ...
- 111
2
votes
1
answer
65
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:
...
- 121
0
votes
1
answer
74
views
How to correctly replace 'default_authentication_plugin' with 'authentication_policy'?
I have MySQL 8.0.33 on Ubuntu. In the error log, I see the following warning:
[Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. ...
- 21
1
vote
0
answers
21
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 ...
- 111
1
vote
1
answer
542
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.....
CommunityBot
- 1
0
votes
0
answers
173
views
MySQL version Upgrade from 5.5 to 8 without no downtime/very less downtime
May I get instructions from anyone on how to upgrade MySQL from version 5.5 to 8 with no downtime/minimum downtime.
Make a plan like that:
Step01# 5.5 (Master)---> 5.7 (Slave): Slave will be ...
0
votes
1
answer
55
views
pt-online-schema-change drop_swap doesn't work, so what to do?
After waiting 24 hours for a ptosc this:
2023-05-29T11:29:40 Copied rows OK.
2023-05-29T11:29:40 Max rows for the rebuild_constraints method: 2710
Determining the method to update foreign keys...
2023-...
- 111
0
votes
1
answer
386
views
MySQL upgrading from 5.7 to 8 - Throwing strange error and won't start up
I followed the instructions on the MySQL web site and got MySQL server 8 mostly installed but it won't launch because it fails pretty quickly during the upgrade process. The apt-get portion installed ...
CommunityBot
- 1
1
vote
1
answer
250
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 ...
CommunityBot
- 1
0
votes
1
answer
298
views
MySQL slave stuck on Applying batch of row changes (read)
Might be duplicate of:
MySQL slave stuck on Applying batch of row changes (write)
or
MySQL8.0 Replication Got Stuck "Applying batch of row changes (delete)"
I am trying to use MySQL 8.0.26 ...
CommunityBot
- 1
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....
CommunityBot
- 1
0
votes
0
answers
113
views
MySQL 8.0 - Recover data from .IBD files after server failure (with files backup)
Recently the MySQL server I was using crashed (8.0), motherboard failure and NTFS corruption on the drive (Windows Server).
I had all the files backed up fortunately, so I thought I would be able to ...
CommunityBot
- 1
-1
votes
2
answers
57
views
I am very frustrated by problems trying to set up mysql-cluster. There are no service files for NDB daemons
I am trying to get mysql-cluster going on Ubuntu 22 from the official MySQL apt repo.
I keep running into complications.
Certain ndb-related daemons are required ... like ndbd. But there are no ...
0
votes
1
answer
1k
views
MySQL - Blank fields in tab-delimited .txt file causing errors in LOAD DATA INFILE
I am working with MySQL 8.0 through MySQL Workbench on a Windows machine.
I am having an enormous amount of problems importing an excel file into a MySQL database. I think the problem is that my data ...
CommunityBot
- 1
0
votes
0
answers
39
views
MySQL 5.7 vs. 8.0 Regression Performance on Ubuntu 22.04
I am testing the performance of MySQL 5.7.42 vs. MySQL 8.0.33 on Ubuntu 22.04 (amd64). Each of the test is using their own AWS EC2 instance (t2.2xlarge which has 8 core/32 GB). I ran a simple sysbench ...
0
votes
1
answer
25
views
What is the difference between `SHOW OPEN TABLES;` and `SHOW GLOBAL STATUS LIKE 'Open_tables';`?
I need some help understanding a few things.
We use Google Cloud SQL MySQL8.0 and had an incident today where the working theory is that the instance was under memory pressure and took longer than ...
- 74.9k
3
votes
1
answer
145
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
0
votes
1
answer
338
views
mysql 8 ndbcluster distributed privileges
I've setup a mysql 8 ndbcluster but cannot get the distributed privileges working.
following how to setup distributed privileges using NDB_STORED_USER (https://dev.mysql.com/doc/refman/8.0/en/mysql-...
CommunityBot
- 1
1
vote
0
answers
38
views
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
0
votes
0
answers
45
views
CPU Usage goes above 100% (200% to 300%) Mysql 8
We have been facing this issue recently on our MySQL 8 server which has 16 GB RAM and 8 Core Processor.
The server runs an application that has the below statistics.
During the time when the data ...
- 24.3k
0
votes
1
answer
17
views
MySQL Stored Function - How to get @@warning_count?
I have the following MySQL 8.0 stored function:
DELIMITER $$
CREATE FUNCTION maybe_utf8_decode(str text charset utf8mb4, rowid INT)
RETURNS text CHARSET utf8mb4 DETERMINISTIC
BEGIN
declare ...
- 33
1
vote
2
answers
47
views
Tuning MYSQL 8.0.32 for my server
EDIT:
User @mustaccio let me acknowledge that I must present the problem that motivates the need for the tuning:
There are querys running faster in an older server, less powerful, MySQL Version 5.7....
- 21
0
votes
2
answers
45
views
Can't drop an NDB database because the storage engine is unavailable
I tried to set up a MySQL cluster and failed. I finally got it switched back to standard MySQL, but now I have an ndbinfo database that I cannot drop. It causes my automysqlbackup cron job to report ...
- 24.3k
0
votes
1
answer
1k
views
Reclaim disk space after drop table from database in mysql
I have this version of MySQL server:
mysql Ver 8.0.15 for Linux on x86_64 (MySQL Community Server - GPL)
I use a some large tables. When I truncate database with truncate database <name>, I ...
CommunityBot
- 1
1
vote
2
answers
26
views
MySQL ORDER BY Indexes When ORDER BY May be Unknown
For some background lets say I have two tables:
create table orders (
id int unsigned auto_increment primary key,
uuid char(36) not null,
status_id int unsigned not null,
account_id int unsigned ...
- 74.9k
0
votes
1
answer
2k
views
Why do I have to set MySQL user password with mysql_native_password to connect with Workbench?
I've noticed if I set the mysql user's password like below, I cannot connect with MySQL Workbench.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'myPassword'
If I use this below I can connect to it:
...
CommunityBot
- 1
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,...
CommunityBot
- 1
2
votes
1
answer
58
views
MySQL read replica using more storage and has more rows than master DB
We have a MySQL RDS instance, with a master and read replica setup. The master DB is used by the prod application, while the read replica is just for engineers to debug and analyse something.
One of ...
- 74.9k
0
votes
0
answers
14
views
Save Japanese string to the mysql 8.0 database
To accept Japanese strings and save them in MySQL 8.0, I setup the field with the utf8mb4_0900_ai_ci collation, but on some strings, it returns the "Conversion from collation utf8_general_ci into ...
- 1
0
votes
2
answers
810
views
Import a large MySQL dump 68GB on RDS instance
I tried to import a large MySQL dump.sql (68GB) on RDS AWS instance MySQL 8.0 (db.t3.2xlarge: 32GB RAM).
The import takes more than six hours:
More than 500 tables
I have 2 large tables: the first ...
CommunityBot
- 1
1
vote
1
answer
70
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
0
votes
1
answer
518
views
High Memory Usage on Replica - Mysql 8
Hope you're doing well.
We've recently moved to Mysql 8 (from Mysql 5.7).
We're using AWS RDS Service with MySql. The AWS Team is also looking for a solution btw.
Since a few days, we're facing ...
CommunityBot
- 1
1
vote
1
answer
738
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 ...
- 133
0
votes
0
answers
16
views
ODBC overflow on unsigned tinyint [migrated]
I have an issue where unixODBC is not handling the upper-half numbers of an unsigned tinyint column. I'm able to "correct" this by increasing the size to an unsigned smallint. I'm wondering ...
- 101
1
vote
1
answer
286
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 ...
CommunityBot
- 1
5
votes
1
answer
3k
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'...
- 111
0
votes
1
answer
975
views
mysql 8 datetime default throwing and error when forward engineering
I am trying to create a schema by forward engineering a model eer diagram. But its giving an error on the datetime fields when using the default current_timestamp which is allowed in mysql 8 for ...
CommunityBot
- 1
0
votes
2
answers
417
views
How to speed up SHOW BINARY LOGS
I am working on migration from mysql 5.7 to mysql 8 (AWS Aurora). As part of the migration I set up AWS DMS tasks and I came to notice each task takes more and more CPU from the writer.
The underline ...
CommunityBot
- 1
0
votes
2
answers
238
views
how do I insert timzone-pois.sql into MYSQL?
I have read about how to insert the time zone into MYSQL as below:
I can't find time zone tables in sys database of MySQL
However, after I type show databases, there was no response except ...
CommunityBot
- 1
1
vote
1
answer
69
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 ...
- 179k
0
votes
2
answers
803
views
Significant processing time creating indexes on a large MySQL table
I'm creating indexes on a few tables I've recently created within MySQL.
The first (call below) completed within ~20 minutes and was applied to a table with a little over 20M records:
mysql> ...
CommunityBot
- 1
0
votes
3
answers
44
views
MySQL - low performance with WHERE, ORDER BY, LIMIT
I have a very weird issue, and I can't wrap my head around it.
Given a rather big table (~5M transactions).
(1) ⚠️ I am running this query - this wallet has about ~20000 transactions in total:
SELECT
...
- 868