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
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 ...

1
2 3 4 5
8