Questions tagged [mysql-8.0]

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

Filter by
Sorted by
Tagged with
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... ...
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 ...
-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. ...
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 ...
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; +-------------+-----------------------------...
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-...
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 ...
user avatar
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 ...
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 ...
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 ...
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) ...
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 ...
user avatar
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 = ...
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 ...
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 ...

1
2 3 4 5
11