Questions tagged [mysql-8.0]
MySQL version 8.0 - please also tag with mysql for search purposes.
560
questions
0
votes
1
answer
9
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. ...
0
votes
1
answer
35
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-...
- 101
0
votes
0
answers
26
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
20
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 ...
0
votes
0
answers
39
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 ...
- 101
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
votes
2
answers
48
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 ...
- 103
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 ...
- 11
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
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
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
54
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
2
votes
1
answer
55
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 ...
- 121
1
vote
2
answers
35
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
40
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 ...
- 103
0
votes
3
answers
43
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
...
- 101
0
votes
0
answers
25
views
MySQL on RDS / AWS (Amazon): performance_schema has no SQL_TEXT and QUERY_SAMPLE_TEXT
We're using Mysql 8.0.32 on RDS, with Performance Insights and auto-managed performance_schema.
The performance_schema mostly works, but some fields are absent.
On performance_schema....
- 11
1
vote
1
answer
67
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
3
votes
1
answer
127
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
0
answers
24
views
Model resource ownership in a flat structure
I have a database model with different entities related to each other, but in the end they all have to be 'owned' by the master entity which in this case is the Organization.
I was wondering if it is ...
- 101
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
0
votes
1
answer
27
views
Create MySQL event that executes first Sunday of every month
How do you create a MySQL scheduled event that executes the first Sunday of each month?
- 235
2
votes
1
answer
117
views
How to Downgrade MySQL from 8.0.33 to 8.0.28
I need to downgrade because I cannot use MySQL for Excel add-in. How can I do it? I use windows 8.
There is no information on the MySQL's website.
1
vote
1
answer
28
views
Select all grouped by year if not equal to minimum of a column + filtered by joined non-id column
MySQL version: 8.x
Given this schema + data:
CREATE TABLE vehicle (
id int NOT NULL,
vin varchar(17) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE ubarequest (
vehicle_id INT NOT NULL,
...
- 113
1
vote
1
answer
115
views
LOAD DATA LOCAL INFILE with specifying first column as auto-increment value
LOAD DATA LOCAL INFILE 'index.csv'
INSERT INTO TABLE `aws_pricing`
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 6 LINES;
Thing is, my table ...
- 239
0
votes
1
answer
36
views
ASC vs DESC index when querying "smaller than"
I have a table populated very often with new entries.
Records older than 2 days get deleted every few minutes with:
DELETE FROM ip_log WHERE timestamp < NOW() - INTERVAL 2 DAY
This is the only ...
- 352
1
vote
0
answers
35
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
0
votes
0
answers
75
views
Mysql SHOW CREATE USER fails with error 1396, but user exists
I am trying to dump the users of a mysql server. Generally it works, but for a certain user, I get this error:
mysql> show create user 'usercacti'@'localhost'; ERROR 1396 (HY000):
Operation SHOW ...
1
vote
2
answers
124
views
Error with SQL DELETE statement with MariaDB
I have a multi-database application that has a query that is giving me execution problems with MariaDB 10.6.12 (Windows).
The same query works perfectly when run against MYSQL 8.0.27
However, when it ...
- 115
0
votes
3
answers
231
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
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
1
answer
104
views
MySQL restore of "mysql" database fails with error 3723
I have a backup process that once a day dumps all the mysql databases on one server (with mysqldump), rsyncs them to another, and then they are imported there (one at a time). I did it this way to ...
0
votes
1
answer
138
views
Alter command showing syntax error in MySQL 8.0
Below is my Table schema
create table employee(
emp_id numeric(4),
fname varchar(10),
lname varchar(10),
mgr_id numeric(4),
hire_date date,
job_id numeric(2),
dept_id numeric(3)
);
Now I want to use ...
- 19
0
votes
0
answers
22
views
Using MySQL 8's "--no-monitor" mode in a Windows service?
Cheers. For (reasons) I want to run MySQL 8.0.30 in the "no-monitor" mode, which keeps the service running as a single process. Docs here.
The documentation makes no reference to windows ...
- 23
0
votes
1
answer
41
views
Fetching 88 records via DISTINCT from a table of 1.5 million rows is taking 3 seconds
There is a table which are populated from an online service and updated every week.
This goes on a separate (second) database.
The table is like 300MB in size (1.5 million rows) processed by a cron ...
- 239
0
votes
0
answers
33
views
Help me understand EXPLAIN PLAN of mysql 8
-> Sort row IDs: users.userid (actual time=24875.751..34999.822 rows=2547647 loops=1)
-> Table scan on <temporary> (cost=0.00..0.50 rows=6083517) (actual time=0.027..2488.740 rows=...
1
vote
2
answers
42
views
combine multiple sql with same FROM clause and diff where clause
I am relatively new when working with queries so let me apologize in advance.
my sql is
$sql1 = SELECT
AVG( ST_Distance_Sphere( point( `event`.`event_location_longitude`, `event`.`...
0
votes
1
answer
80
views
Unable to start MySQL with SSL support
I've generated a handful of certificates for my MySQL 8.0.30 (mysql-server.x86_64 8.0.30-1.module+el8.6.0+16523+5cb0e868 @rhel-8-for-x86_64-appstream-rpms) on RHEL 8.7 and I'm running into an issue...
...
- 33
1
vote
1
answer
64
views
MySQL upgrade 5.7 to 8.0 with Keyring encrytption failed to complete upgrade
I ran the mysql community installer on Red Hat 7.9 over a 5.7.36 installation.
First, I ran xtrabackup for all dbs
Then I stopped mysqld
Then I ran yum install
Everything ended ok
I got the below ...
- 486
0
votes
1
answer
107
views
MySQL Select query for a calculated column that uses previous row value for current row
I am stuck at this. Trying to help PM team to translate a calculation from Excel using a MySQL select query to do reporting. Your help is appreciated.
Table has 3 columns - Employee, Month, Salary. ...
- 1
0
votes
1
answer
77
views
mysqld how to only listen on unix socket?
how do i make mysqld not use ip at all, only listening on an unix socket? if i do
mysqld --socket=mysql.sock
it still opens port 3306, and if i do
mysqld --bind-address='' --socket=mysql.sock
it ...
- 103
0
votes
0
answers
47
views
Mysqldump geometry field error
I have MySQL 8 database with some tables that have some column of type GEOMETRY.
When I back up the table with mysqldump, it outputs my geometry column as _binary '\æ\0\0\... and when I try to ...
- 155
0
votes
1
answer
34
views
Indexing for multiple ORDER BY in MySQL 8
I feel like I have optimized this query to the best of my ability, but it is still slow (usually takes 0.5s - 2s in development).
It seems like the ORDER BY is the culprit, as when I get rid of it, it ...
- 3
0
votes
0
answers
175
views
MySQL 8 taking 1000x times longer to execute this query vs 5.7 when ORDER BY is used
This query comes from an ORM (Laravel Eloquent) on a WordPress database. I upgraded to MySQL 8 from 5.7 (AWS RDS), and the performance went from pretty speedy to basically broken. API requests that ...
- 1
0
votes
1
answer
133
views
How to prevent double booking of a room in this hotel booking database design?
I have two tables for hotel booking. One is rooms table where I save room type, hotelId and total number of rooms that the hotel has for that type.
HotelId
RoomTypeId
RoomType
TotalRooms
100
2
...
- 101
2
votes
3
answers
230
views
MySQL: How to make Trigger to insert row in another table?
Please read everything, included the notes and edits at the end
Problem
I have a MySQL DB with 3 different table:
To save users
mysql> describe users;
+-------------+-----------------------------...
- 21
2
votes
1
answer
180
views
MySQL 8 Couldn't execute FLUSH TABLES Access denied
I have MySQL 8 database with a user to whom I grant ALL privileges as follows:
GRANT ALL ON test_db.* TO 'test_user'@'%';
When I login to mysql as this user and run the following command I get error ...
- 155
0
votes
1
answer
69
views
MySQL doesn't start
Recently I had an issue with MySQL for an unknown reason. It didn't start and produced an error message, so I decided to update it from 8.0.31 to 8.0.32.
The Windows version is 10 Pro for Workstations ...
1
vote
2
answers
32
views
Complicated query that compares subselects
I have pretty hard query to write and I have no idea how to approach this.
I also have no idea for my question title, so if someone has good idea how to describe my problem - please suggest it.
I'm ...
- 123
0
votes
2
answers
374
views
MySQL Server 8.0 - Increase innodb_buffer_pool_size
I have a question regarding MySQL Server 8.0 Configuration. I don't know, if this question is maybe to broad. If so, please let me know, then I can delete it, but I would be happy about any comments, ...
- 101