Skip to main content

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
2 answers
394 views

MySQL 8 - Performant query for exact words match

We have a database table with more than 10 million entries, and we are struggling to find the best SQL query to find the entries having all the exact words in a field irrespective of the order. Below ...
1 vote
1 answer
5k 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
0 answers
14 views

Can MySQL Accept DDNS for Group Replication Setup?

I'm currently setting up MySQL Group Replication between a local server and a cloud server. However, the local server doesn't have a static IP address, so I'm considering using Dynamic DNS (DDNS) for ...
0 votes
0 answers
14 views

How to Efficiently Manage Multiple Databases in a Multi-Tenant Application on GCP

I am building a multi-tenant application using Google Cloud Platform (GCP). My application is deployed on App Engine, and I am using MySQL Cloud SQL for the database. To ensure data isolation, I have ...
1 vote
1 answer
835 views

Moving from MySQL 5.7 to MySQL 8.0

I'm currently running MySQL 5.7 in one of the key servers. This version reaches end of life soon, so it's time to move to 8.0. Or give it a shot and jump on 8.1? This one is also running on dated ...
1 vote
0 answers
38 views

Mysql Crashing on: [FATAL] Semaphore wait has lasted > 600 seconds

Once a day or two we get the bellow error and the mysql crashingt: 2024-07-14T05:31:59.327696Z 0 [ERROR] [MY-012872] [InnoDB] [FATAL] Semaphore wait has lasted > 600 seconds. We intentionally crash ...
1 vote
1 answer
21 views

Decrease high response time when reading large table in MySQL

I have a table with around 80~ million rows called parts. I have a simple query like this SELECT line_code, part_number, compressed_part_number, ...[Others columns] FROM parts WHERE (@partNumber = ...
0 votes
1 answer
566 views

Is it safe to manually delete binary logs after disabling it?

After disabling log-bin by skip-log-bin option, I cannot use PURGE command to remove binary logs. Refer to the documentation PURGE BINARY LOGS requires the BINLOG_ADMIN privilege. This statement has ...
0 votes
0 answers
10 views

why the mysql 8 database auto generate a long running transaction

Today when I run an update command run spring boot application, the MySQL 8 generate a long running transaction and block the next update command. This is the output when I check the transaction: ...
0 votes
0 answers
24 views

MySQL Causing OOM after Upgrading to 8.0.37

Recently, we faced severe & unrecoverable corruption of database (a bug from MySQL 8.0.27, but only hit us 2 years in). So we upgraded to MySQL 8.0.37, and everything's good so far. But there's a ...
0 votes
1 answer
2k 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 ...
0 votes
1 answer
1k views

MySQL How to Script to create indexes on all date columns in database

I have a lot of tables in the database that are using a datetime stamp - I need to create a script that will create indexes on all columns in each table that does not already have an index . So far I ...
0 votes
0 answers
39 views

Random long writes (waiting for handler commit)

I have some INSERT/UPDATE queries who takes a long time randomly. I try to launch the same INSERT several times with profiling enable. And I remark that the duration is always in waiting for handler ...
0 votes
1 answer
19 views

Failing to run dba.configureInstance against MySQL cluster 8.4 server on Ubuntu noble

I want to run the commands listed here: https://dev.mysql.com/doc/mysql-shell/8.4/en/check-instance-configuration.html I've added the latest MySQL package mysql-apt-config_0.8.30-1_all.deb to install ...
0 votes
0 answers
12 views

Variable time execution for INSERT/UPDATE

I do an INSERT/UPDATE in a table who has around 1+ million rows in MySQL 8.0. I don't know why I have different execution times for the same data to insert. For example, I try to insert exactly the ...
0 votes
1 answer
641 views

MASTER-MASTER replication failed on Production: Coordinator stopped because there were error(s) in the worker(s)

This error occured after I was importing a database dump to master1. In production our MASTER-MASTER replication failed UPDATE SELECT CHANNEL_NAME, WORKER_ID, THREAD_ID, SERVICE_STATE, ...
0 votes
1 answer
2k views

Tuning MySQL 8.0.33 - increasing innodb_buffer_pool_size not working?

I'm trying to tune a MySQL 8.0.33 on Ubuntu 22.04 (32G RAM, 8 cores). (The same machine is also a web server, with 4 apache/php-fpm virtual hosts running). I'm not sure if this is the right thing to ...
2 votes
2 answers
319 views

Unexplained MySQL memory usage

I am experiencing an issue with MySQL memory usage increasing over time to a point where OOM Killer kills the MySQL process. I have 3 servers configured with Group Replication (1 Primary and 2 ...
0 votes
1 answer
1k views

problem to import load data infile

I am trying to load data into mysql database form text file. LOAD DATA LOCAL INFILE 'P:\file.txt' INTO TABLE table_january FIELDS TERMINATED BY '\t' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ...
0 votes
1 answer
1k views

Creating MySQL table with 100+ columns

I have a dataset to be loaded to MySQL that contains 100+ columns of assorted data items. I'd planned to pivot the dataset to be row-oriented in the vein of RDBMS best practice but absent a PIVOT ...
3 votes
2 answers
294 views

Why innodb locks index till the end?

Why a query BETWEEN two IDs locks index till the end? UPDATE elem SET c='' WHERE id BETWEEN 2 AND 5; -- locks id>5 for some reason The database is MySQL 8.0.33 GA. Here's the full example, taken ...
0 votes
1 answer
2k views

Left Join with only one of multiple matches in the right table

I have a database, where every entry may have multiple names, therefore I made a name database. While all names will be displayed otherwhere I need a list of all main entries with one representative ...
0 votes
1 answer
42 views

Conditional update

I would like to create a conditional update on an audit table, such that if the update on the main table succeeds, then the update on the audit table is performed. Right now I have this CREATE DEFINER=...
0 votes
1 answer
975 views

MySQL 8.0.31 crashing frequently on Windows 2012R2

I'm running MySQL 8.0.31 on Windows 2012 R2 64-bit on a AWS EC2 VM. Frequently, the MySQL service crashes. I tried to reinstall MySQL 8.0.31 and rebuild the database from a dump, but it still crashes ...
0 votes
2 answers
2k 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
1 answer
1k 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 ...
2 votes
1 answer
5k views

MySQL 8 Replication

I've got this error message: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin....
0 votes
2 answers
490 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
1 answer
382 views

mysql 8 ndbcluster distributed privileges

I've setup a mysql 8 ndbcluster but cannot get the distributed privileges working. following how to setup distributed privileges using NDB_STORED_USER (https://dev.mysql.com/doc/refman/8.0/en/mysql-...
0 votes
1 answer
2k views

get row/value by primary key number

I'm writing my own python wrapper on top of official mysqldb connector. I'm trying to design method that would dynamically extract row data knowing KEY ID. SELECT * FROM cameras; +----------+--------...
1 vote
1 answer
1k views

Optimal way to SELECT all rows in a table that are not referenced by a foreign key?

In MySQL I have a table "resources" that represents a list of resources. eg: CREATE TABLE resources ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, ...etc.....
1 vote
1 answer
375 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
1 answer
475 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 ...
0 votes
1 answer
1k 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 ...
2 votes
0 answers
29 views

Concurrent update to one record in same table created a deadlock between primary key and secondary index

Concurrent reqs under odd case has triggered the same update query twice which surprisingly led to deadlock between primary key and secondary index update in the same table. The update query will ...
1 vote
1 answer
3k views

I can't properly set permissions to MySQL config files

Yesterday I ran out of hard drive space for the database in the middle of a dump; Luckily this was in development and I had my backups... but I like to see these cases in detail and document them in ...
0 votes
1 answer
33 views

MySQL 8.0 no longer updates information_schema.tables after optimizing a table

We have recently been undertaking a purge of data from our databases, which has left some tables with huge amounts of free space (in some cases hunderd of GB's). So I have started a process of going ...
0 votes
1 answer
51 views

Removing partitions from MySQL table

I have table containing 3.2TB data in partition named default_partition, & all writes/read goes to this partition, as we have defined partitions on table like below : PARTITION BY RANGE (to_days(...
0 votes
1 answer
981 views

How to take single database backup using mysqlbackup command in Mysql 8.0.18 version DB

I have a mysql instance(8.0.18-commercial MySQL enterprise server) with 8 databases in it and out of those I only need to take the backup of DB named 'Test1'. Please note that its 300 GB sized DB ...
1 vote
0 answers
49 views

MySQL root password reset

I've spent many an hour trying to reset my mysql root password with no luck. I'm running an up to date patched Ubuntu server 22.04.4 with mysql 8.0.36. My latest attempt is using sudo mysqld_safe --...
0 votes
0 answers
15 views

Lost connection to MySQL server during query for a not-so-complex long query

I have this query : SELECT some_15_fields, (SELECT GROUP_CONCAT(tb_inv_private_ip SEPARATOR ', ') FROM `network_interface` WHERE network_interface.tb_inv_id=id) as 'private_ip', (SELECT GROUP_CONCAT(...
0 votes
0 answers
28 views

Adding a new fulltext column results in degraded performance

I have a search table with a schema something like this: CREATE TABLE `product_search` ( `product_id` int NOT NULL, `search` text NOT NULL, PRIMARY KEY (`product_id`), FULLTEXT KEY `search` (`...
1 vote
0 answers
48 views

MySQL InnoDB Stored Procedure locks table?

MySQL 8.0 InnoDB I have some sql statement like SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION; INSERT INTO MyTable SELECT Name FROM MyTable ...
0 votes
1 answer
497 views

MySQL upgrading from 5.7 to 8 - Throwing strange error and won't start up

I followed the instructions on the MySQL web site and got MySQL server 8 mostly installed but it won't launch because it fails pretty quickly during the upgrade process. The apt-get portion installed ...
0 votes
0 answers
85 views

problems with lower case table names

I just had to reinstall on my Mac the clean OS (monterey), installed a time machine backup and noticed that the mysql DB server 8.0.28 (oracle mysql community edition) would not start, or well it ...
1 vote
1 answer
3k views

MySQL does not use index, does full table scan instead

EDIT: See further developments below. I have an InnoDB table with the following schema: CREATE TABLE `data` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `app_id` CHAR(27) NULL DEFAULT NULL,...
0 votes
0 answers
101 views

Upon PC restart, MySQL service on local computer "started and then stopped"

I'm working in MySQL on a local (Windows) computer. I have a somewhat recurring issue I have with managing the server. I cannot replicate this error because I do not know what causes it. Sometimes, I'...
0 votes
1 answer
482 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 ...
0 votes
1 answer
1k views

mysql 8 datetime default throwing and error when forward engineering

I am trying to create a schema by forward engineering a model eer diagram. But its giving an error on the datetime fields when using the default current_timestamp which is allowed in mysql 8 for ...
3 votes
1 answer
3k views

How to performance tune high /wait/io/redo_log_flush values in "commit"

According to the "Top SQL" view in AWS Performance Insights, commit tops the list of SQL statements in terms of average active session (AAS). And most of the time seems to be spent in wait/...

1
2 3 4 5
14