Questions tagged [mysql-8.0]
MySQL version 8.0 - please also tag with mysql for search purposes.
393
questions
0
votes
0answers
11 views
replicate-wild-do-table=channel_name:db_name.% doesn't work in MySQL 8.0 for channel based replication
I am trying to configure a replication topology where there will be one slave which will connect to two masters. Percona Server 8.0.25.
Master1- main master
bkp_server - slave to master1 and ...
0
votes
1answer
12 views
How to create a root user / admin database with access to child databases
New user here, excuse me if that isn't grammatically correct, but this is what I want to do -
I have a server set-up with multiple sites on one VM instance - I want to create an admin mysql database ...
0
votes
1answer
28 views
MySQL slave stuck on Applying batch of row changes (read)
Might be duplicate of:
MySQL slave stuck on Applying batch of row changes (write)
or
MySQL8.0 Replication Got Stuck "Applying batch of row changes (delete)"
I am trying to use MySQL 8.0.26 ...
2
votes
1answer
63 views
does the order of index columns matter when we have equality operators?
Given that I have the tables
CREATE TABLE `replies` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`repliable_id` bigint unsigned NOT NULL,
`repliable_type` varchar(255) COLLATE ...
0
votes
0answers
21 views
Is it possible perform a fulltext search on MySQL 8.0, with double quotes and wildcard?
Based on past answers, I see that it was previously possible to perform fulltext searches with double quotes and wildcard, like the following:
SELECT COUNT(*) FROM mytable MATCH (mycolumn) AGAINST ('&...
0
votes
1answer
48 views
Performance issues after upgrading to Mysql 8.0.19 version
Few queries are performing very poorly after the upgrade. Here are the examples.
****Example 1**:**
SELECT
x.CI_TITLE 'ciTitle',
x.DEPARTMENT_MEANING 'departmentMeaning',
x....
0
votes
1answer
29 views
Database scheme for re-testing records
Goal
Monitor records of temperatures for each room, daily.
What I have created so far
Firstly, this table shows the rooms and their minimum & maximum values of the temperatures they should meet.
...
1
vote
2answers
64 views
Avoid Deadlock on concurrent insert
I am trying to avoid a future problem.
Table has 2 fields:
id -> bigint Primary key
hash -> varchar(65) unique key
there will be concurrent inserts into the table for hash.
I need the last ...
0
votes
1answer
23 views
Mysql - how to create an id column for a specific column
i want to create a table that have 3 column :
visit_times : have a unique id for every specific visitor
visitor_ID
Date
i tryed to create the table as showing this code
CREATE TABLE test.table ( ...
0
votes
0answers
25 views
select which algorithm a view is using
I have a view in a MariaDB 10.4.13 database that feeds into a database documentation process and gathers data on tables and views used in the database.
Amongst other fields it selects which algorithm ...
0
votes
0answers
23 views
Creating a foreign key on an empty MySQL InnoDB table takes a very long time
I have created a table called asset_versions:
CREATE TABLE "asset_versions" (
"asset_id" int NOT NULL,
"version" varchar(50) NOT NULL,
"created_at" ...
0
votes
1answer
45 views
MySQL table + partitioning + spatial data
We have a table that currently has every postal address for a single state, currently around 13 mil records. Among postal addresses, it also contains spatial coordinates for each address along with ...
2
votes
1answer
29 views
Conditional aggregate on existing query
I have following table structure (I'm using MySQL 8):
CREATE TABLE `listings` (
`id` bigint unsigned NOT NULL,
`url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`...
0
votes
2answers
73 views
MySQL 8.0 Hanging and Disconnecting At Random Times - Slow Downs - Need Help
Yesterday, I noticed in my web logs that most traffic disappeared for 2 hours. I decided to do a routine Ubuntu 20.04 upgrade yesterday that I haven't done in 1-2 months, and it seems like a lot of ...
0
votes
0answers
31 views
MySQL 8.0 Full Index Scan in RAM sluggish
I have a MySQL 8.0 table on Amazon RDS. This table contains ~35 columns, most of which are sizable JSON blobs. The primary key is an UNSIGNED INT32. There are 8M+ rows in this table and has a size of ...
1
vote
0answers
37 views
Slow query count with MySQL 8 where table has JSON columns
Note, I think I'm encountering this bug which was marked as "Can't replicate": https://bugs.mysql.com/bug.php?id=97709
That said, I'm interested if anyone has a workaround to query ...
1
vote
1answer
24 views
MySQL: Socket file wrong location
I have gone over dozens of articles related to such issues without solving mine.
MySQL 8 over CentOS 8, my.cnf file indicate the socket as following:
[mysqld]
datadir=/data/mysql8/mysql
socket=/data/...
1
vote
1answer
21 views
Mysql8 fail to upsert generated uuid
I have a mysql8 table with 2 columns: ID(Int) AND UUID(Varchar36):
CREATE TABLE `upsert_test` (
`ID` int unsigned DEFAULT NULL COMMENT 'id;',
`UUID` varchar(36) DEFAULT NULL
) ENGINE=InnoDB;
I ...
0
votes
1answer
32 views
How to speed up query which takes ~5s on Mysql5, but longer than 10mins on Mysql8
My company went through a database upgrade recently, from Mysql5 to Mysql8, and some of our queries have slowed down massively. (Going from taking in the order of a few seconds, to timing out after ...
0
votes
1answer
22 views
Process ID max limit and what happens after
Process ID is bigint in information schema.
What happens when limit is reached?
Do I need to restart MySQL to 0 it out again?
0
votes
1answer
103 views
MySQL 8 - Primary Keys - Sequential UUID or INT with AI?
My DB has ~100 tables and 3 for which I need UUIDs.
For these 3 tables, the main goal is simply to hide sensitive IDs to visitors.
MySQL 8 has introduced UUID support and I was planning for these 3 ...
-1
votes
1answer
17 views
Counting column which has index is slow mysql [closed]
EDIT: I'm not asking about count(*) which won't take indexes into account, I'm also not asking about the difference between actual counts on table vs information_scema as noted in the suggested ...
1
vote
3answers
77 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
24 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
27 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
13 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
43 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
44 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
338 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
36 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
47 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
15 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
19 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
55 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
43 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
39 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
61 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
781 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
71 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
131 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
39 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
17 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
74 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
14 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
39 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
346 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
77 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
130 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 ...