Questions tagged [mysql-8.0]
MySQL version 8.0 - please also tag with mysql for search purposes.
340
questions
1
vote
1answer
45 views
MySQL performance issue when a hash join is used
I'm having an issue where the optimizer seems to be doing a full table scan and exhibiting a big performance hit when a hash join is used.
Schema and sample data:
CREATE TABLE `px_po_status` (
`...
0
votes
0answers
16 views
MySQL keeps ignoring MAX_EXECUTION_TIME
I have an instance running which have mysql8 and a nodejs application server.
This query keeps popping up and freezing.
SELECT /*+ MAX_EXECUTION_TIME(2000) */ COUNT(*) FROM my_table;
Even though this ...
0
votes
0answers
58 views
Weird slow queries after update RDS from 5.7.26 to 8.0.21
We have ~500dbs having ~110 tables each. DB was running on t2.medium instance for multiple years. I noticed that IO was growing gradually and reached not really nice avg values of 220-250 iops. So we ...
1
vote
0answers
49 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 ...
0
votes
1answer
18 views
MySQL8 crash recovery
My dev MySQL-8 server crashed last night and would not restart. The error is:
[ERROR] [MY-012646] [InnoDB] File ./ibdata1: 'open' returned OS error 71. Cannot continue operation
I tried the classic &...
0
votes
1answer
24 views
SELECT max value for each group of values order by grouped by MYSQL 8.x
I need to get the max age of a group of records, even is the age is duplicate ORDER BY the age DESC
Person | Group | Age
---
Bob | 1 | 32
Jill | 1 | 34
Shawn| 1 | 42
Jake | 2 | ...
0
votes
1answer
30 views
mysql: ERROR 1040 (HY000): Too many connections and can't restart because of Error: 28 (No space left on device)
My app crashes because there are too many connections open and mysql does not make me log in as root showing the same problem.
After restarting mysql, it does not restart because of no space on disk
1
vote
1answer
26 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 ...
0
votes
0answers
52 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 ...
1
vote
1answer
19 views
Does row-based replication with “binlog_row_image=minimal” cause issues when ALTER TABLE reorders columns?
Just trying to get my head around the caveats of using binlow_row_image=minimal with row-based replication in MySQL 8.0.23... To avoid silent data drift, the requirements seem to be that:
All tables ...
0
votes
0answers
16 views
Non matching attribute 'INSTANT COLUMN(s)' between partition and table in mysql 8.0.20
I am using MySQL 8.0.20. I found a solution that recommended to include algorithm=Inplace or algorithm=copy in alter table exchange partition statement explicitly, but my DDL statement doesn't seem to ...
0
votes
0answers
10 views
MySQL Group Replication starts master node with super_read_only
I am trying to set up MySQL group replication. Only problem is, that when I try to start replication group, It starts with super_read_only.
Here the configurations in my.cnf file
[mysqld]
...
0
votes
0answers
29 views
MySQL InnoDB Cluster can't be created. Why?
I am trying to setup a MySQL InnoDB Cluster. I configured each node involved using dba.configureInstance(...). Everything was nominal until I ran var cluster = dba.createCluster('clusterNmae'). When I ...
1
vote
1answer
33 views
MySQL 8.0 Metadata Lock Extension for Foreign Keys causing requests to hang
We are using AWS RDS and we upgraded from MySQL 5.6.41 to MySQL 8.0.21 last December.
Since mid-January, we noticed some problems causing our replica to be stuck after running migrations adding ...
0
votes
0answers
18 views
MySQL 8.0.20 - Master Replica scheme, errors during replication process
This threads follows a previous one, given at this URL: MySQL 8.0.20 - Master Replica scheme, increasing delay between Source and Replica
Replica Server has been configured this way, which ensures a ...
0
votes
0answers
11 views
What is the differrence between mysql.innodb_table_stats and information_schema.innodb_tablestats?
I am using mysql-8.0.19. I want to find a way to detect changes in database tables. E.g. compare the value of a query to the value of the last query.
I see that there exist two tables from which I ...
0
votes
1answer
22 views
Optimize Table query causing Fatal error during execution with InnoDB table
I have a problem with OPTIMIZE TABLE query with one of my InnoDB database tables.
I have a database that contains several different tables and data is constantly accumulating in these tables. However ...
0
votes
0answers
12 views
Partial (selective) restore from physical backup of a MySQL instance, possible in version 8+?
I need to perform the restore of some selected tables of my MySQL 8.2 database.
I was wondering if it's possible to shutdown the service, replace the related files (from the physical cold backup) in ...
1
vote
1answer
53 views
MySQL 8.0.20 - Master Replica scheme, increasing delay between Source and Replica
We have a Source Database of about 1TB, and a Replica on another server (similar architecture).
We have setup Master Slave replication and delay between Master and Slave is always growing since.
...
2
votes
2answers
254 views
Having charset “utf8mb4” - does it mean every character takes 4 bytes, or only those that need 4 bytes?
I have a VARCHAR(80) utf8mb4 column, and I'm testing adding some ASCII & Emoji characters, and use LENGTH() and CHAR_LENGTH() to understand the differences.
From reading in different places, my ...
0
votes
1answer
18 views
Does the collation of ENUM columns impact their size?
What does it mean when we say that an ENUM has a charset of utf8mb4, versus latin1?
My understanding is that the ENUM holds a numeric index (1, 2, 3, 4...) that identifies which of the ENUM values (...
1
vote
0answers
36 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, ...
0
votes
0answers
11 views
mysql how to select these menuitems in order of parent1+all children, parent2+all children, etc?
How do I SELECT my data such that I can get the output in the proper order? I want all data (every row) from the table, but in a certain order.
In mysql 8.0.17, I have the following table:
mysql> ...
1
vote
1answer
33 views
Group concat all child ids against parent row using query in Mysql
Having records in table as below, And we can add record as endless hierarchy
-----------------------------------------
| id | name | parent_id |
---------------------------------------...
1
vote
0answers
23 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 ...
0
votes
1answer
52 views
MySQL - GROUP BY with JOIN query
I am using MySQL 8.0.23
I have 4 tables: houses, intervals, discounts, and availability.
Houses:
create table houses
(
id int auto_increment
primary key,
name varchar(255) null,
...
2
votes
1answer
85 views
Server quit without updating PID file when trying to start MySQL server?
So I created my .bash_profile, opened it in a text editor and added:
export PATH=$PATH:/usr/local/mysql/bin
and then I saved it. Next, I tried running
sudo /usr/local/mysql/support-files/mysql.server ...
3
votes
1answer
47 views
Mysql get records more then 3 in interval of 1 minute, return group_concat ID
Currently i have this dataset, i need to return grouped ids that are within the range of 60 seconds and have more than 3.
CREATE TABLE test
(
`id` bigint NOT NULL AUTO_INCREMENT,
created_date ...
0
votes
1answer
69 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:
...
0
votes
1answer
574 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 ...
1
vote
4answers
115 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 ...
0
votes
0answers
17 views
Mysql keeps re-adding disable-log-bin=1 to my.cnf
We have mysql 8 server configured as master. Every time WHM updates mysql, disable-log-bin=1 is added to my.cnf. Not sure where is the issue, but it is already 3rd time it happens. Current problem was ...
0
votes
1answer
28 views
MySQL8 Binary installation - getting error msg “-bash: bin/mysql: Permission denied” after upgrading to 8.0.22
We have a MySQL 8 instance with Binary Installation. After upgrading to 8.0.22 (from 8.0.19) can't seem to connect using following cmd in CLI
bin/mysql -uusername -p
error msg -
-bash: bin/mysql: ...
-1
votes
1answer
82 views
Error 1005 errno 150 “Foreign key constraint is incorrectly formed” where no foreign key declared
I have an issue on a basic CREATE TABLE statement Error 1005 errno 150 "Foreign key constraint is incorrectly formed" returned where no Foreign key is declared -
CREATE TABLE Person
(
...
0
votes
1answer
24 views
Normalization using DENSE_RANK and LAST_INSERT_ID
db-fiddle
I have created a table called orders (orderId, customersId) and a table called orderDetails (orderId, productId). My goal is to denormalize a table called temp which is like
scanId, ...
0
votes
1answer
24 views
How to properly replicate and archive at once
I have a 300GB database that is growing around 5GB a day. It stores time-based data, and as such, I currently have around 60 days of data. My main database server has a 450 GB NVMe, so I want to get a ...
3
votes
0answers
55 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 ...
0
votes
1answer
722 views
Migrate MySQL 5.7 to 8.0 via mysqldump
I have an Ubuntu server 18.04 LTS with MySQLl 5.7
I have new Ubuntu backup server 20.04 LTS with MySQL 8.0
MySQL 5.7 backup created:
mysqldump --all-databases > "filename" -u root -p
...
1
vote
1answer
51 views
MySQL InnoDB Cluster - Add instance issue with local port
I have an issue to add instance to MySQL InnoDB Cluster using MySQL Shell. Here are the steps that I have done:
Login to one DB-01 node
Run command: mysqlsh
shell.connect('username@ip-address:port');
...
0
votes
2answers
59 views
MySQL one file per table not working
I've followed all of the instructions for enabling the one file per table in MySQL (v8, on Ubuntu 20):
Added innodb_file_per_table=ON
Restarted MySQL
Dumped all databases, one by one.
However, when ...
0
votes
1answer
71 views
Mysql 8.0.22 master slave replication: why slave is trying to create user?
I am following this repo (https://github.com/alexanderfefelov/docker-mysql-replication) to setup one master one slave replication. I am not able to get it working. The master seems to be OK, but the ...
1
vote
1answer
25 views
Mysql same DB, same query, different users - different error messages
On my AWS RDS Mysql 8.0.16 server I have one DB (my-db) and 2 users: the master user (created automatically by RDS) and an app user created by me:
create user if not exists `appuser`@`%` identified by ...
0
votes
1answer
861 views
How to properly downgrade mysql 8 to 5.7 in centos 8
I would like to downgrade MySQL 8 to MySQL 5.7 on centos 8, can I get the proper steps/commands. Thanks in advance.
1
vote
2answers
90 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 ...
0
votes
1answer
12 views
Help needed with several engines use case [closed]
We are developing an app, aprox 50k RPM read, 1k RPM write, it ask via key and get a JSON.
The search is always made by key.
I'm inclined to use one MySQL 8 table with a Id field and JSON field with ...
2
votes
1answer
221 views
Missing mysql, sys, performance_schema
I have Percona Mysql 8.0.19-10 on debian 10.
A year ago I had some issues restoring a backup that required me to mess with tablespaces. I forget what all I had done but eventually I got the database ...
0
votes
0answers
17 views
Master node freezes when second master unreachable in master-master replication
I have a 2 node master-master setup in mySQL 8.0.22. I have successfully got the nodes to both be writeable and update each other in normal operation. However, When I disconnect a node, it is not ...
-1
votes
2answers
150 views
MySQL 8 - optimize all tables + indexes
I have a bunch of tables (InnoDB) within several databases (MySQL 8), that I run Garbage Collect scripts on, on daily basis, removing stale records that are no longer needed. All tables do have ...
0
votes
2answers
118 views
Investigating RDS MySQL 8.0.16 stalls on write-heavy workload
I'm trying to track down some sporadic and difficult-to-reproduce issues with a MySQL 8.0.16 RDS instance connected to a web application with a write-heavy workload on a couple tables with large JSON ...
0
votes
1answer
28 views
Can MySQL server send a “server started/running” signal to a remote server?
I have a MySQL 8 server on a virtual machine. I'd like to start it up when another server wants to communicate with it, and shut it down some time after there hasn't been any connections or queries.
...
