Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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
0 answers
108 views

Is it possible to configure replication between Mysql 8.0 (Master) and Maria 5.5.68 (Slave)?

Currently we have to upgrade 5.5.68-MariaDB to MySQL 8.0.x (Community) which will be a Master server in Replication configuration. Now my question is, Is it possible to configure replication between ...
Uday Raj Gupta's user avatar
0 votes
2 answers
118 views

Changing int column to bigint on table with blob column

I have an aws hosted RDS production table called external_documents as defined below. It has about 35k rows and each blob is about 0.5 MB. CREATE TABLE `external_documents` ( `id` bigint unsigned ...
mankowitz's user avatar
  • 156
1 vote
0 answers
64 views

Why doesn't mysql prompt password?

$ mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) $ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your ...
Gqqnbig's user avatar
  • 111
1 vote
1 answer
2k views

How do I change the Authentication Plugin for users mysql.sys, mysql.session, etc?

I use MySQL version 8.0.34 on a Debian server. With the latest update I get the message that mysql_native_password is deprecated and I should rather use caching_sha2_password instead. With my own ...
Ste2play's user avatar
0 votes
1 answer
191 views

What are the use cases for MySQL Generated Invisible Primary Keys (GIPKs)?

I have read in the MySQL documentation that it is possible to enable the generation of invisible primary keys (https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html). I'm quite a newbie in ...
Alex's user avatar
  • 3
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 ...
Cintya 's user avatar
1 vote
0 answers
43 views

Why does mysqld use much more memory than expected?

Running MySQL 8.0.24, I have been recently seeing frequent instances where the RHEL7 OS will kill the mysqld process as the system memory gets all used up. The server has 65 GB of memory, and I think ...
TFR3's user avatar
  • 11
0 votes
1 answer
52 views

Cannot pass parameter to select in MySQL 8.0

I'm writing a generic partitioning helper, and suddenly run into trouble. CREATE PROCEDURE `weekly_partition`(in schema_name_arg varchar(128), in table_name_arg varchar(128), in ...
Alex Povolotsky's user avatar
0 votes
0 answers
200 views

Comparing JSON Arrays with MySQL 8

I have a Table in the form of: Id (int) Tags (JSON) 1 [1, 2, 3] 2 [1, 4, 5] 3 [1, 3] I would like to construct a SELECT statement where I can compare the Array stored in the column Tags with ...
harald's user avatar
  • 121
2 votes
0 answers
60 views

Why does this query cause tmp table to be stored on disk even though its size is smaller than tmp_table_size?

I have MySQL server (8.0.29-21 - Percona Server) with: tmp_table_size=67108864 max_heap_table_size=67108864 log_slow_filter="tmp_table_on_disk" And in the slow query log, I can see lots of:...
elderapo's user avatar
  • 121
0 votes
0 answers
520 views

TempTable Memory Storage Engine Issue

I have a db.r5.2xlarge (64GB) AWS Aurora-MySQL instance (8.0.26 equivalent) and I have a long-running SQL which gives this error: ERROR 1114 (HY000) at line 1: The table '/rdsdbdata/tmp/#sqlfe_130_0' ...
Brian Estrada's user avatar
0 votes
2 answers
66 views

Can I force a BLOB to be stored externally in MySQL?

I have a table which, for historic reasons contains some HTML and PDF contents. It also contains a filenames/URLs in the case that the HTML and PDF contents have been written to storage. Currently the ...
mjaggard's user avatar
  • 125
0 votes
2 answers
39 views

Question about multi fields index in MySQL

I'm having an doubt about coumpound indexes in my application. My db engine is Mysql 8.0 with INNODB and my question is the following: My application has a model called Report with the follwing fields ...
Pedro Silva's user avatar
0 votes
2 answers
415 views

MySQL 8 is not indexing on Functional index

I'm using MySQL 8.0.30 and known that it's supporting for Functional index. However I tried to create an index base on sum of some fields, it's very easy as below, but index is not used, even in ...
Khoi Nguyen's user avatar
0 votes
1 answer
425 views

Cannot restore MySQL into existing DB - get ERROR 1050 (42S01): Table 'blahblah' already exists

I can restore from mysqldump wordpress-db > mysqldump-wpdb.sql to new DB fine with $ sudo mysql wordpress-db001 < mysqldump-wpdb.sql But if the DB already exists (as they generally do with a ...
TickboxPhil's user avatar
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 ...
Benito's user avatar
  • 1
0 votes
1 answer
247 views

How can I tell which query uses an index in MySQL?

I have investigated my indexes in MySQL using performance_schema.table_io_waits_summary_by_index_usage which has told me that one of my indexes is used in one database environment (Production EU) but ...
mjaggard's user avatar
  • 125
0 votes
0 answers
17 views

Can MySQL secondary member role high disk read affect primary member role performance?

We have a MySQL (8.0.27-commercial MySQL Enterprise Server - Commercial) topology of 3 servers, one with primary role (server01) and with two secondary role (server02 and server03). For monitoring, we ...
Anell Zheng's user avatar
0 votes
1 answer
43 views

How can I restore 2.5TB mysql dump into AWS RDS MySQL in just few hours?

I want to restore the 2.5TB mysql dump into AWS RDS MySQL. Data is without create info and destination tables have only two columns. Can anyone suggest the best strategy to restore the dump ...
Prince singla's user avatar
0 votes
0 answers
248 views

MySQL connection error : ORA-28500: connection from ORACLE to a non-Oracle system

Most of the time we don't have issues with the connection from Oracle (using DB link and ODBC driver) to the mySQL DB. However, every once in a while we get the error which persistes for like 30 min: ...
Amir's user avatar
  • 1
1 vote
1 answer
138 views

MySQL 8 Trick to Grant Permission on User from Several Distinct IP

I have 20+ static IPs that I want the same user to have grant access to. I am on Aurora-MySQL 8 and I wonder if there is a way to give permission to this user without specifying each IP. For example, ...
Brian Estrada's user avatar
4 votes
0 answers
265 views

MySQL: how to kill a user lock held by a terminated thread

We're using MySQL 8.0.32 hosted by AWS RDS. mysql> SHOW VARIABLES LIKE 'version'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | version | 8.0.32 | +-------...
Alex's user avatar
  • 161
4 votes
3 answers
491 views

failed pt-online-schema-change left behind triggers. How to delete?

We had a failed pt-osc - the server ran out of disk space. And now the triggers are left behind SHOW TRIGGERS; pt_osc_xxx_production_orders_ins pt_osc_xxx_production_orders_upd ...
phil's user avatar
  • 153
0 votes
1 answer
19 views

Sorting data from a given column where the sort order is after a dot

I have the following query: SET @counter = -1; UPDATE domains SET domains.order = @counter := @counter + 1 ORDER BY domains.extension ASC; Where the column extension is comprised of TLD's. Now we ...
Ricardo Mendes's user avatar
0 votes
1 answer
34 views

Fill empty row when have one id record on summary query in Mysql

Good afternoon, i want to ask about MySql query. I have the initial data as pict below. I want to calculate the number of hours difference by displaying several data columns with the MySql query ...
epm.007's user avatar
  • 29
0 votes
1 answer
252 views

How does MySQL handles GROUP BY?

I'm on Windows 10, MySQL Workbench. I'm on MySQL 8.0. Here's the dataset. northwind.sql. https://pastebin.com/bMgjXvfT Objective: Write a query to get the order ID, customer's name, grand total of ...
achhainsan's user avatar
1 vote
1 answer
1k views

AWS RDS MySQL upgrade from 5.7 to 8 failing due to ROW_FORMAT

We need to upgrade our DB to version 8, but we're getting an error in the migration log that's preventing us from upgrading. Creating indexes larger than 767 bytes on tables with redundant row format ...
user avatar
0 votes
0 answers
23 views

Last GTID on replica matches source but data is not there

We have a following set up: Multi A-Z source RDS instance (MySQL 8.0.32) Single #1 A-Z replica RDS instance (MySQL 8.0.32) Single #2 A-Z replica RDS instance (MySQL 8.0.32) ProxySQL (2.5.2) Binlog ...
Igor Yavych's user avatar
2 votes
1 answer
116 views

MySQL showing randomly poor performance with nested loop antijoin

I have a very simple query which is randomly performing poorly in production. I have the EXPLAIN ANALYZE outputs for both the good case and the bad case. MySQL version is 8.0.31. This is the query: ...
Agniva De Sarker's user avatar
1 vote
0 answers
75 views

Mysql8 Unable to order the result of WITH RECURSIVE query

I am using the WITH RECURSIVE query to generate a hierarchy of users. The data and the query used is shared in https://www.db-fiddle.com/f/oTbCcMQb5a4B6V96DAYJHG/0 . The query is returning the results ...
Nandakumar V's user avatar
1 vote
0 answers
32 views

MySQL8 database painfully slow after failed pt-osc

In another question we attempted to update a table in our MySQL8 db. That didn't work out, but since then our database (even after both DB and machine restarts) seems painfully slow at the strangest ...
phil's user avatar
  • 153
1 vote
2 answers
8k views

How to correctly replace 'default_authentication_plugin' with 'authentication_policy'?

I have MySQL 8.0.33 on Ubuntu. In the error log, I see the following warning: [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. ...
Erel Segal-Halevi's user avatar
0 votes
1 answer
329 views

pt-online-schema-change drop_swap doesn't work, so what to do?

After waiting 24 hours for a ptosc this: 2023-05-29T11:29:40 Copied rows OK. 2023-05-29T11:29:40 Max rows for the rebuild_constraints method: 2710 Determining the method to update foreign keys... 2023-...
phil's user avatar
  • 153
0 votes
0 answers
299 views

MySQL 5.7 vs. 8.0 Regression Performance on Ubuntu 22.04

I am testing the performance of MySQL 5.7.42 vs. MySQL 8.0.33 on Ubuntu 22.04 (amd64). Each of the test is using their own AWS EC2 instance (t2.2xlarge which has 8 core/32 GB). I ran a simple sysbench ...
Brian Estrada's user avatar
0 votes
1 answer
193 views

What is the difference between `SHOW OPEN TABLES;` and `SHOW GLOBAL STATUS LIKE 'Open_tables';`?

I need some help understanding a few things. We use Google Cloud SQL MySQL8.0 and had an incident today where the working theory is that the instance was under memory pressure and took longer than ...
Moritz Schmitz v. Hülst's user avatar
0 votes
0 answers
1k views

CPU Usage goes above 100% (200% to 300%) Mysql 8

We have been facing this issue recently on our MySQL 8 server which has 16 GB RAM and 8 Core Processor. The server runs an application that has the below statistics. During the time when the data ...
nXn's user avatar
  • 101
0 votes
2 answers
41 views

MySQL Stored Function - How to get @@warning_count?

I have the following MySQL 8.0 stored function: DELIMITER $$ CREATE FUNCTION maybe_utf8_decode(str text charset utf8mb4, rowid INT) RETURNS text CHARSET utf8mb4 DETERMINISTIC BEGIN declare ...
peppy's user avatar
  • 33
-1 votes
3 answers
641 views

I am very frustrated by problems trying to set up mysql-cluster. There are no service files for NDB daemons

I am trying to get mysql-cluster going on Ubuntu 22 from the official MySQL apt repo. I keep running into complications. Certain ndb-related daemons are required ... like ndbd. But there are no ...
elyograg's user avatar
  • 103
1 vote
2 answers
62 views

MySQL ORDER BY Indexes When ORDER BY May be Unknown

For some background lets say I have two tables: create table orders ( id int unsigned auto_increment primary key, uuid char(36) not null, status_id int unsigned not null, account_id int unsigned ...
jhdba113's user avatar
2 votes
1 answer
60 views

Handling server upgrades through packaging system (apt) when running group replication

I'm running a test cluster with three nodes - db-[1-3]. All three were happy members of a single primary group replication cluster. They were all configured with group_replication_start_on_boot=on. ...
MatsLindh's user avatar
1 vote
1 answer
1k views

Reduce 'wait/sql/table/io/handler' load of high frequent updates on the same rows

We have a time-series application that also writes all the records to kind of a 'head' table in MySQL. This is somewhat of a legacy, but very interwoven and hard to get rid of. The problem is, that it'...
Halfgaar's user avatar
  • 191
2 votes
1 answer
353 views

MySQL read replica using more storage and has more rows than master DB

We have a MySQL RDS instance, with a master and read replica setup. The master DB is used by the prod application, while the read replica is just for engineers to debug and analyse something. One of ...
Sidharth Samant's user avatar
1 vote
2 answers
366 views

Tuning MYSQL 8.0.32 for my server

EDIT: User @mustaccio let me acknowledge that I must present the problem that motivates the need for the tuning: There are querys running faster in an older server, less powerful, MySQL Version 5.7....
lio89's user avatar
  • 21
0 votes
2 answers
252 views

Can't drop an NDB database because the storage engine is unavailable

I tried to set up a MySQL cluster and failed. I finally got it switched back to standard MySQL, but now I have an ndbinfo database that I cannot drop. It causes my automysqlbackup cron job to report ...
elyograg's user avatar
  • 103
0 votes
3 answers
635 views

MySQL - low performance with WHERE, ORDER BY, LIMIT

I have a very weird issue, and I can't wrap my head around it. Given a rather big table (~5M transactions). (1) ⚠️ I am running this query - this wallet has about ~20000 transactions in total: SELECT ...
cweinberger's user avatar
3 votes
2 answers
266 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 ...
John Smith's user avatar
4 votes
1 answer
926 views

AWS Aurora Mysql seemingly not picking PRIMARY or secondary index

Relevant System Info: Aurora Mysql 8.0.mysql_aurora.3.03 rg6.xl instances (1 writer, 2 read replicas) Total size:5.5TB (all databases combined, or just looking at the most recent Snapshot) I have been ...
Randoneering's user avatar
1 vote
0 answers
26 views

Search for a record which has an exact set of many-to-many related records

I have a straightforward many-many arrangement: scans id 1 2 products id 1 2 3 product_scan: scan_id product_id 1 1 1 2 2 1 2 2 2 3 When I'm about to create a new scan entry with a ...
Synchro's user avatar
  • 261
0 votes
1 answer
127 views

Create MySQL event that executes first Sunday of every month

How do you create a MySQL scheduled event that executes the first Sunday of each month?
John C's user avatar
  • 233
2 votes
1 answer
2k views

How to Downgrade MySQL from 8.0.33 to 8.0.28

I need to downgrade because I cannot use MySQL for Excel add-in. How can I do it? I use windows 8. There is no information on the MySQL's website.
Ömer Faruk VERGİSİZ's user avatar

1 2
3
4 5
14