Questions tagged [mysql-8.0]
MySQL version 8.0 - please also tag with mysql for search purposes.
375
questions
1
vote
3answers
49 views
Fill missing data points in `GROUP_CONCAT`
I need to generate data to build some charts.
The current results have missing data points and I'd like them to fill them with 0's.
Data is stored in MySQL 8. Simplified data sample & query fiddle ...
0
votes
0answers
11 views
Docker container mysql 8 fail to start with './slave57_binlog.~rec~' not found (OS errno 13 - Permission denied)
I have a mysql (percona) master and slave setup on docker containers running.
master57 - master container - 5.7.34 Percona Server
slave57 - slave container - 5.7.34 Percona Server
root@docker-host:~# ...
1
vote
0answers
21 views
MySQL Replication stop after a huge amount of transactions on Master
Running MySQL 8 for months now, without much troubles, I still have randomly some problem of replication between the Master and its Slave.
Problems means, all works, both instance works, still the ...
0
votes
1answer
10 views
MySQL 8 - connecting to server with different port
I have MySQL 8 on Debian 9 vps, and my problem that I can connect to server from php Adminer (web) with any port I've chosen:
localhost:3307, 33650, any port.
And php Adminer showing - MySQL » ...
-1
votes
1answer
40 views
MySQL 8 insert values into selected columns only
I have a huge insert sql script for insertion and the problem that this insertion was possible in MySQL 5.5 version, but not in Mysql 8:
CREATE TABLE IF NOT EXISTS `accounts` (
`ID` int(11) NOT NULL ...
0
votes
1answer
41 views
8 vCPU, 32 GB memory, 250 GB SSD server needs 1 to 2 seconds for a query with 12 SUM functions and a JOIN on a table with 80,000 rows
I was recommended that I ask my question here at DBA. Sorry, should that be considered "double posting". A bit of information in advance:
I have already made the following settings (my.cnf):
...
3
votes
1answer
318 views
Why 0/false returns true when using JSON_EXTRACT in MySQL
I have a condition to select a JSON document value that is falsy.
My rows:
1. {"my_value": false}
2. {"my_value": 0}
3. {"my_value": "0"}
4. {"my_value&...
0
votes
0answers
6 views
Is there a plugin for MySQL 8.0.24 that can log user behavior?
Is there a plugin for MySQL 8.0.24 that can log user behavior (such as user login success, failure, add/delete users, modify content, etc.)?
0
votes
2answers
35 views
Sort index doesn't work for MySQL
I have a table with more than a million records. I get the data by using LIMIT, but also ORDER BY. The latter causes a huge performance problem.
My query ends with this:
... ORDER BY `record`.`...
0
votes
1answer
42 views
Delete large number of rows and change index - what steps in which order and things to watch out for
We daily collect data with multiple records per second. The table my_xpa_data has currently close to 100 Million rows and is growing fast.
table size around 8 GB (used to be 4 GB a couple of days ago)...
0
votes
2answers
14 views
GROUP CONCAT and GROUP BY using LEFT JOIN If no record exist Must return something
I have 2 tables with below structures.
question table
id, audio, s_id
1, ad1, 500
2, ad2, 500
3, ad3, 500
answers table where q_id is FK of question table.
id, s_id, q_id, number, answer
1, 500, 1, ...
0
votes
1answer
17 views
Why doesn't MySQL optimizer do index merge from indexes in two different tables?
Query1 takes almost 2000x more time than Query2.
The criteria is of form
tbl1.key1 = x or tbl2.key1 = y
Query 1
SELECT
`t1`.`t1_PK`
FROM
`t1`
JOIN
`rel_t1_t2` ON `t1`.`t1_PK` = `...
1
vote
1answer
39 views
Massive performance degradation in upgrade from MariaDB 5.5 to MySQL 8
As part of a complete server move and upgrade (OS, Apache, and db) went from MariaDB 5.5 to MySQL 8.0.21. Hardware specs unchanged. Unfortunately despite many hours of reading and experimental tuning ...
1
vote
1answer
35 views
Large 30+ mil row table needing to use where IN clause
We have a table that contains, as of today, 30+ million rows of addresses. We have a field status int that is either 1 or 0. We need to be able to query the table select count(*) from addresses where ...
0
votes
1answer
28 views
MySql Replication: Access Denied
I am trying to migrate between cloud providers, using MySql Replication to copy data across live so we can switch with no downtime.
Current setup:
2x Windows servers with old provider, with MySql 8 ...
0
votes
1answer
23 views
How to alter AWS RDS tables in `mysql` schema?
I want to alter tables like mysql.rds_sysinfo, mysql.rds_monitor, mysql.rds_heartbeat2 which belong to mysql schema.
The alter sql for that is:
ALTER TABLE mysql.rds_sysinfo ROW_FORMAT=DYNAMIC;
But ...
2
votes
1answer
206 views
Why am I not able to add a user?
I'm running command grant all privileges on *.* to 'username'@localhost identified by 'strong password';, but I get the error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the ...
1
vote
1answer
44 views
mysqlbinlog - Binlog has bad magic number error when reading dumped file
I am learning MySQL and performing a test point in time restore using MySQL Server 8.0 on Windows 10. I am having an issue re-playing the binary log as follows.
First, set up the test database
mysql -...
0
votes
1answer
81 views
How to fix file format in tables after upgrade of mysql 5.6 -> mysql 8.0.23 in AWS RDS?
I upgraded mysql 5.6 -> mysql 8.0.23 in AWS RDS.
There is a warning:
Creating indexes larger than 767 bytes on tables with redundant
row format might cause the tables to be inaccessible. You have ...
1
vote
1answer
32 views
Data truncation on spatial query after updating from MySql 5.7 to 8.0
I have the following table:
create table places
(
id bigint auto_increment
primary key,
position point SRID 4326 not null
);
create spatial index position
on places (position);
--...
0
votes
0answers
11 views
MySQL 8 Innodb clustering add instance error
During MySQL 8 innodb clustering add instance.
Cluster.addInstance: The port '33101' for localAddress option is already in use. Specify an available port to be used with localAddress option or free ...
1
vote
2answers
39 views
In MySQL User table and Login history log table with Left join gives an error for contains nonaggregated column
I am trying to get all user information data that is on the USER table and also only the user's last login information(which is on another table called LOGIN_HISTORY).
I tried with Left join but MySQL ...
0
votes
0answers
12 views
Checksum difference between table in MariaDb 10.4 and Mysql 8
We have a script that transfers databases between clusters. To make sure the data is still valid, we run a checksum on some of the tables. If those are ok, we mark transfer succesful.
The problem is ...
1
vote
1answer
33 views
Is it possible to join MySQL 8.0 to a cluster of MySQL 5.7
I have a MySQL 5.7 (5.7.19-17-57-log Percona XtraDB Cluster) cluster using Galera, but I am in the middle of upgrading to 8.0 -- 8.0.23-14 Percona Server (GPL), Release '14'. I wasn't sure if it was ...
0
votes
2answers
34 views
Why does Casting this string as a decimal fail?
Why does casting this result of REGEXP_SUBSTR() to a DECIMAL fail?
SELECT
REGEXP_SUBSTR('Cost (-$14.18)', '(?<=Cost [(]-[$])[0-9.]+') AS _extracted,
CAST(REGEXP_SUBSTR('Cost (-$14.18)', '(?&...
2
votes
2answers
131 views
MySQL 8.0 Tuning Advice Needed - Slow Locking & Various Issues
I recently upgraded my database to MySQL 8.0 and I'm using phpmyadmin to get status information. I also upgraded my virtual server to 4GB RAM and 2 vCPUs, intended to be a MySQL-only server for my ...
1
vote
1answer
51 views
Why is the execution time on Datagrip longer on MySQL 8 than MySQL 5.7 on a new Linux Server?
I am migrating from a 4 year old setup to a brand new high performance server and am experiencing slower performance than on the old machine.
The old setup is a Ubuntu 16.04 Server on bare metal Intel ...
0
votes
0answers
44 views
Data truncated after importing a MySQL 5.7 dump into MySQL 8
I am trying to import a 12G MySQL 5.7 dump (on ubuntu 16.04) into a MySQL 8.0 database (on ubuntu 20.04).
Half way through the import exits and returns the following error:
ERROR 1265 (01000) at line ...
-3
votes
1answer
17 views
MySQL misunderstands the value with the column name [closed]
I have executed a simple query
select * from content_model_reference cm where cm.element_id = "1.B.1.g";
The MySQL gives me this error message
ERROR 1054 (42S22): Unknown column '1.B.1.g' ...
0
votes
1answer
49 views
MySQL 8.0.23: Removeing huge '#sql-ib****.idb files?
I had a massive mysql database crash this morning and realized it was due to the server running out of disk space. I recently upgraded mysql to version 8.0.23 last week from 5.7.
As I was looking for ...
0
votes
0answers
15 views
How does the slave database server back up the logs of the master database server?
Now that the master-slave replication architecture for MySQL 8.0.24 has been built, how does the slave database server back up the logs of the master database server?
I'm looking for transaction logs ...
0
votes
1answer
20 views
How to alter column to make it primary key when one already exists mysql8?
id | bigint unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(255) | NO | | NULL | |
| category | varchar(255) | NO | | NULL |...
1
vote
2answers
125 views
MySQL 8.0.24 Windows 10 crashes [not] resolved by reboot, [requires mySQL optimizer tweak]
MySQL 8.0.24 on Windows 10 (10.0.18363.0) crashes near the same point when running a final results query, but only after data input queries with many more records have been run.
A restart of the MySQL ...
0
votes
1answer
33 views
Mysql 8 tables size
We have a problem with an unreasonably large table size on the server, for example:
Yesterday, a table with partitions was created, data began to be written to a specific partition.
mysql> show ...
0
votes
0answers
58 views
MySQL server startup error
My Issue
After shutting down and starting my system up, my MySQL daemon gives me this in the logs (the pertinent snippet is as follows):
2021-05-03T12:48:32.458594Z 1 [ERROR] [MY-013183] [InnoDB] ...
0
votes
1answer
31 views
SQL Assignment query
I have 2 databases - one with customers' data and the second one with discount codes. I would like to assign code to each customer. The code can be assigned only once, so I would like to mark the code ...
2
votes
1answer
55 views
How to write a query to get wine pairing for a pizza based on pairing weight
I am working on a query to pair drinks to pizzas.
Currently I am able to show the pizza_name, beverage and the pairing_weight being the amount of toppings on a pizza that the beverage pairs with. From ...
0
votes
1answer
29 views
MYSQL 8, Hierarchy Adjacency list, How each rows to know the top node of the alias root.?
MYSQL 8.
Here are some rows I have in a table containing hierarchical data.
+-----+-----------+------+--------------------------------------------+
| id | parent_id | sort | name ...
-1
votes
2answers
31 views
Is it possible to have a table and its index on different drives?
I'm running an Ubuntu MySQL server that has HDD and SSD internal drives. The HDD has larger capacity, but of course the SSD has much faster read/write speeds.
Is it possible for me to hold a (2.5 ...
0
votes
0answers
28 views
MySQL 8 can not enable Mast-Master Replication after rollback from GTID Replication
can you help me with this issue please?
I have rolled back from GTID replication to traditional (master-master replication with binlog), and at the same time I’m adding new node to the cluster as ...
0
votes
0answers
30 views
MySQL8 instance crashes on CloudSQL
I had a MySQL 5.7 instance on Google CloudSQL. Now i created a MySQL 8 instance.
The configuration is pretty much the same (except i use 2 CPUs instead of one, 3.75GB of RAM). Now the default config ...
4
votes
2answers
652 views
Is using strings as keys for reference to other tables bad in terms of memory usage?
So i have to deal with the following scenario:
I have a table , users , which holds all of the users of my application .
Each user has an email which is unique and thus could be the primary key of ...
0
votes
1answer
16 views
How to add a volume to current in use MySQL database
related to the problem I faced here No space left on device, I want to adopt a long term solution about the point no space left on device. One of the action includes to add a volume to the machine in ...
2
votes
1answer
59 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
28 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
216 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
238 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
70 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
44 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
42 views
mysql: ERROR 1040 (HY000): Too many connections and can't restart because of Error: 28 (No space left on device) [closed]
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
