Questions tagged [mysql-8.0]
MySQL version 8.0 - please also tag with mysql for search purposes.
526
questions
0
votes
0
answers
23
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
23
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
20
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...
...
- 11
1
vote
1
answer
21
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 ...
- 476
-1
votes
0
answers
21
views
Unable to inner join same table with two condition
I have a table named historical_data_eod
data will be added on a daily basis(around 1000+).
I would like to compare and filter the data between yesterday and today,along with two where conditions ...
0
votes
1
answer
22
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
57
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
15
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 ...
- 145
0
votes
1
answer
24
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
45
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
0
answers
37
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
54
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
1
vote
1
answer
56
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 ...
- 145
0
votes
0
answers
23
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
82
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
1
vote
1
answer
35
views
Percona for mysql 8.0.31 unresponsive every day at exact same time?
So… we’re hitting a weird case the last week.
Our percona 8.0.31 server on ubuntu 22.04 (128G RAM) becomes unresponsive every day at the same time.
The symptoms are a bit weird too. Our usual ...
- 21
0
votes
2
answers
22
views
Mysql query to return a value for every day from X to X even if table has no data for that day
I have this query:
SELECT
`sr`.`transactionDate`,
`b`.`title`,
`b`.`color`,
ROUND(SUM(sr.digitalEarnings), 2) AS earnings
FROM
`sales_report` AS `sr`
INNER JOIN `...
- 605
0
votes
0
answers
43
views
Help me in optimising the query which takes almost 50 seconds
Please help me optimise the following query running on MySQL 8.0.28 Community Edition.
explain select id,
created_at,
data,
deleted_at,
entity_id,
...
0
votes
0
answers
41
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 ...
1
vote
1
answer
52
views
MySQL 8.0.30+: redo_log_capacity vs log_file_size?
Cheers. We're trying to track down the cause of a significant increase in IO load after switching from MySQL 5.7 to 8.0.30, and one issue we've noticed is related to this part of documentation:
The ...
- 11
0
votes
1
answer
37
views
I can start mysql service as sudo and hence can login to MySQL only via sudo but otherwise can't
I am on Ubuntu 22.04 on WSL2 on Windows 11.
anjanesh@SOMEID:/mnt/c$ sudo service mysql start
* Starting MySQL database server mysqld ...
- 225
0
votes
1
answer
20
views
Mysql Trigger - Update status_id if the value exist in other Table
Currently, I have two Tables:
ocok_country
status
country_id
1
1
1
2
0
3
0
4
** country_id 3 and 4 not updated with the trigger **
ocok_zone_to_geo_zone
name
country_id
ABC
1
DEF
2
new-...
- 23
0
votes
0
answers
25
views
MySQL occasional performance issues
I am currently working on system with MySQL DB and SpringBoot (Hibernate). Both are located on the same server. All tables use InnoDB. Although there are no slow queries (5+ seconds), there is ...
- 1
2
votes
1
answer
60
views
Why does the page cleaner in MySQL 8 increase the number of active connections to the database?
I have noticed that when the page cleaner is triggered in my MySQL 8 database, it causes a temporary increase in the number of active connections. This can result in increased wait time for new ...
BadProgrammer
3
votes
2
answers
221
views
MySQL 8 - Access denied when dropping procedures
On our MySQL 8.0.23 server, when a user tries to drop a procedure created by another user they get the following error:
Error Code: 1227
Access denied; you need (at least one of) the SYSTEM_USER ...
- 133
0
votes
1
answer
28
views
MySQL 8: trying to alter two TIMESTAMP tables from NOT NULL to NULL and from '0000-00-00 00:00:00' to NULL as default value
I have this table
CREATE TABLE `acquisition` (
`id` int NOT NULL,
`received` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`parsed` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
) ...
- 142
1
vote
1
answer
121
views
Aurora MySQL 5.7 to 8.0 Upgrade Issue with general_log_backup
I am upgrading our Amazon Aurora from 5.7 to 8.0 and I got this error in the precheck logs.
20) Tables recognized by InnoDB that belong to a different engine
Error: Following tables are ...
0
votes
0
answers
84
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 ...
- 101
0
votes
1
answer
37
views
MySQL Database migration for 1.5 TB database size from MySQL 5.6 to MySQL 8.0.25
I have database in MySQL for an application which basically generates some kind of numbers with some other info attached with each number and the current size of the database is 1.5 TB . Now I want to ...
- 111
1
vote
1
answer
38
views
/var/lib/mysql/mysql.ibd big after disabling performance_schema
What I think is the reason for this issue
Recently, I was trying to reduce the memory usage by disabling the performance_schema (I read a tutorial that disabling it will reduce the memory usage.)
So, ...
- 21
3
votes
0
answers
109
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 ...
- 131
0
votes
0
answers
39
views
dynamic pivot table on mysql 8
I have a table (movimenti_budget) like this:
ID
DATE
movimento
importo
1
2023-01-15
1
1000
2
2023-02-15
1
1200
3
2023-03-15
1
400
4
2023-05-15
1
3000
5
2024-01-15
1
1150
And a table (CAUSALI) ...
- 133
1
vote
1
answer
86
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
...
- 151
0
votes
1
answer
109
views
AUTO_INCREMENT is not working in mysql8 :(
CREATE TABLE IF NOT EXISTS `users` (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
org_id int NOT NULL,
phonenum int ...
user266713
0
votes
1
answer
87
views
Modify Collation of a column
mysql Ver 8.0.25
Got this error in apache log:
Error query: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation ...
- 121
2
votes
1
answer
78
views
MySQL Locking and Isolation Level
Could someone help me to understand this MySQL behavior?
I was trying to add a new column on a table using this statement:
ALTER TABLE `tb_example`
ADD COLUMN `score` INT NULL,
ALGORITHM = ...
- 123
0
votes
1
answer
423
views
How to solve ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
ps ax | grep mysql
38737 pts/4 R+ 0:00 grep --color=auto mysql
[root@ ~]# chown mysql:mysql -R /var/lib/mysql/*
[root@ ~]# chmod 755 -R /var/lib/mysql/*
[root@ ~]# service mysql restart
...
0
votes
1
answer
98
views
Optimizing 2x 1.8GB tables takes 50 minutes
I am running MySQL 8.0.30 on Rocky Linux 9, cpu is 12 threads AMD Ryzen, 128 GB ram, a decent NVME SSD. And ... I struggle with relatively large databases, currently around 200GB in total, around 20 ...
- 153
0
votes
0
answers
83
views
How to remove a certain field from JSON column which stores array of object s
We are using MySQL 8.0. We have a JSON column(options) that stores an array of objects. I want to use this field in my Select query but I don't need the point field. I tried JSON_EXTRACT, and ...
- 229
1
vote
1
answer
86
views
Calculating MySQL server requirements
I almost didn't post on here as I know this is a somewhat open ended question and I'm in danger of being berated but I've been doing a lot of reading about various things and still don't really have a ...
- 605
3
votes
0
answers
67
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 ...
0
votes
0
answers
79
views
Backup constantly failing on secondary server with 'Cannot recover a truncated undo tablespace'
We are constantly getting failed backups with xtrabackup when the backup is run from a secondary server. How do we permanently fix this undo tablespace issue on the primary mysql server, so that the ...
- 185
1
vote
1
answer
75
views
Return each product with the earliest instance of its latest change
I'm trying to find the earliest created_at date of the latest category for all products. I've attached a sample table below:
CREATE TABLE IF NOT EXISTS `products` (
`id` int(6) UNSIGNED NOT NULL,
`...
0
votes
1
answer
79
views
How can I handle cycles / infinite loops in recursive CTEs in MySQL
Cycles in graphs lead to infinite loops in CTEs.
Dealing with them in Postgres is straightforward.
Since 8.0 MySQL also allows for CTEs. How I can detect cycles and infinite loops in MySQL CTEs?
The ...
- 749
0
votes
1
answer
20
views
MySQL 8.0.23 Snapshot Restoration and Buffer Pool
The company I work for recently had a production outage. This outage occurred because we restored a snapshot of our MySQL database, and the MySQL buffer pool needed to be refreshed. The database had ...
- 101
0
votes
1
answer
60
views
optimization with subquery not working as expected
I have a MySQL InnoDB database. One table called affymetrixProbeset contains more than 300 million rows.
Querying this table with INNER JOIN to other tables, with an ORDER BY and offset/limit takes ...
2
votes
0
answers
113
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
4
votes
2
answers
359
views
Unable to use result of a "subquery in select clause" in a "insert.. select.. on duplicate update" query
Possible Unanswered Duplicate Question: Insert select - using selected value in ON DUPLICATE KEY UPDATE
I am not able to use a subquery result column in the select clause as a value to update in a ...
- 207
3
votes
2
answers
243
views
mysql takes too long to just add a column
MySQL took 4.5 minutes to just add a column to a table having 150,000 rows.
Is there anything wrong in it or this is just normal. Is there any other alternative to fasten things?
ALTER TABLE job_posts
...
