Questions tagged [mysql-8.0]
MySQL version 8.0 - please also tag with mysql for search purposes.
290
questions
1
vote
1answer
21 views
MySQL shows CPU usage over 40% though total CPU usage is less than 30% as per TOP - How to debug this issue?
Not sure if I am reading this incorrectly. In the below screenshot of TOP command, it shows that mysql process CPU usage is 41.2% but at the 4 core CPU shows a total of 26% usage.
is MYSQL not ...
0
votes
0answers
12 views
Mysql says it failed to start but is up anyway?
I'me running MySQL 8 under Ubuntu 20.04 WSL2
I'm starting MySQL with sudo service mysql restart
Output is:
* Stopping MySQL database server mysqld [ok]
* Starting MySQL database server mysqld ...
0
votes
1answer
41 views
MySQL table queries extremely slow
I am using MySQL 8.0.17 and I have a table with just 7 million rows defined like
CREATE TABLE `measurement` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`field1` int(11) DEFAULT NULL,
`field2` ...
0
votes
1answer
18 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 ...
0
votes
1answer
21 views
How to SUM DISTINCT by other columns?
I have a CTE that resulting something like this:
i x y
A 4 ...
A 4 ...
A 4 ...
A 4 ...
B 2 ...
B 2 ...
C 3 ...
And already have a query that uses most of them:
WITH cte AS ( ... ) -- above is the ...
0
votes
1answer
24 views
Stored procedure not displaying data when null is passed into input parameter
I recently started learning stored procedures in MySQL and created a procedure like this:
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_getAllDetails`(IN _student_no INT,
IN _class_id INT, IN ...
0
votes
1answer
55 views
MySQL index performance
I'm running a relatively simply query against a very large DB (~2B records) which, even with indexes, is taking a very long time (currently several minutes and running).
The script relies on two ...
1
vote
2answers
29 views
How do I enable full text search on MySql 8?
I'm using MySql 8. I would like to speed up searches on my table of the form
select * FROM dirctory_coop where name like '%mystr%';
and someone mentioned using Full text searches -- https://dev....
0
votes
3answers
40 views
Is it normal for MySQL to gradually increase memory usage over time?
Is it normal for MySQL to increase memory usage over time? See image below of my server's memory usage over the last two weeks. After "service mysql restart" it drops to 40%. The database is ...
1
vote
1answer
36 views
MYSQL Stored procedure to fetch entire row from a table
I am a beginner to SQL and currently practicing the concept of the stored procedure. I have a table Fetch_Marks that contains 20 records with Student_No and Marks as columns. Now I would like to ...
-1
votes
1answer
32 views
MySQL Query Fetch Time increases when LEFT JOIN with big table [closed]
I am fetching the same amount of data with 2 different queries. However, one of them have a fetching time of ~x130 compared to the other. Being the only difference between the two queries a LEFT JOIN ...
0
votes
0answers
19 views
MySQL router Access denied for user 'routeruser'@'%' to database 'mysql_innodb_cluster_metadata' (1044)
I am trying to deploy MySQL-router in kubernetes, I had some problems previously and they were fixed.
Now mysql-router pod is showing following error in kubernetes logs mysql-router-pod -n mysql-...
1
vote
1answer
32 views
How can I enforce that my composite key columns do not equal one another in MySQL?
I have a pivot table in which there is a composite key made up of two columns that reference the same table. The columns are requester_id and addressee_id and both of them refer to the id field on a ...
1
vote
1answer
43 views
Why does MySQL think I'm using SRID 1?
I can run the query on my local machine no problem (Server version: 10.4.6-MariaDB), but when I run it on my remote one (Server version: 8.0.17 MySQL) it poops out this error message:
ERROR 3548 (...
-1
votes
1answer
37 views
UPDATE with variables to renumber column gives syntax error [closed]
Searching for a way to renumber a column within mysql, I've found multiple articles showing the same approach:
Renumbering an Ordering Field in MySQL
How to update a MySql column with ascending ...
0
votes
1answer
29 views
Mysql 8 - Count(*) - performance issue
I have this table with 2 million records
CREATE TABLE `my_table` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`ID_USER` int(11) DEFAULT NULL,
`BODY` mediumtext CHARACTER SET utf8 COLLATE ...
0
votes
2answers
56 views
LEFT JOIN not working as intended
I'm using mysql 8.0.21. I'm trying to join two tables such that I can find the index name of foreign keys. This is my query:
SELECT KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA,
KEY_COLUMN_USAGE....
0
votes
0answers
82 views
Query performance become slower after upgrade to MySQL 8.0.20
I have found bug/issue after update the MySQL Server from 5.7 to 8.0.20.
When I run a query with the user-define function in 5.7(same session), the duration for each query is constant and stable.
But ...
1
vote
1answer
30 views
5
votes
2answers
3k views
Why is this MySQL query taking so long?
I'm looking to do a relatively simply filter from one table called prices.
prices is a very large table (~2G records). Sample data, query and indexes included below. I realize the table being ...
1
vote
1answer
79 views
Issues with MySQL 8.0.17
Recently we upgraded MySQL version on our UAT database server to 8.0.17 from 5.7.18.
We are facing issues with our web application, Website is loading slowly if there are more users accessing it I ...
0
votes
0answers
46 views
MySQL Group Replication - Member won't join after clone, access denied for for user mysql_innodb_cluster
I have a problem with a MySQL group replication cluster member not joining the cluster group after cloning operation.
Question: How do I get the failed member to join the group again and be able to ...
2
votes
0answers
43 views
Cannot access MYSQL safe mode using skip-grant-tables on Windows
I am running a MySQL server 8.0.21 on a Windows Server system.
Recently I ran into an issue where my root user lost all its privileges.
I am trying to gain access to the MySQL server using:
mysqld ...
1
vote
0answers
16 views
MySQL - can connect via “reverse engineer” but not by double-clicking the connection using the same connection info
A connection to MySQL Server on the same network fails when double-clicking the connection in MySQL Workbench (i.e. trying to connect the standard way). It gives the error message:
"Cannot ...
1
vote
0answers
15 views
Process with Command 'Killed' and State 'Executing'. Not getting killed
Last day, when I ran a count(*) query on a table. It didn't provide any results. It ran forever even. So I tried killing those queries (I have tried it multiple times). As I'm using AWS RDS, I killed ...
2
votes
2answers
45 views
Diagnose slow script executions on specific server
I work with an unmanaged server in a remote datacenter to host web based applications. When we run the SQL scripts to create and populate an application database the whole process is painfully slow. A ...
2
votes
1answer
25 views
Mysql self Join query with Limit never terminates
I'm running an experiment with the query
Select Distinct table_1.id1, table_2.id1
FROM image as table_1, image as table_2
WHERE table_1.id2 = table_2.id2
LIMIT K;
When I run the query ...
0
votes
0answers
12 views
Does MySQL 8 on Ubuntu 20.04 allow password authentication for root user?
Just installed MySQL on a new development server and ran into an issue where root access seems to be denied to all Linux users other than root, even if they use the correct password. Also MySQL root ...
0
votes
0answers
22 views
Mysql - which cnf file to update
I have mysql 8 running on a ubuntu 20.4 server. I want to apply some setting to optimize the database so that it uses less ram for a web application. typically switching of the performance_schema.
...
0
votes
1answer
42 views
Downgrade MySQL 8.0.20 to 8.0.19 on Ubuntu for using XtraBackup
With MySQL 8.0.20 currently installed on Ubuntu, I am unable to use Percona XtraBackup 2.3.7 due to breaking changes introduced in MySQL 8.0.20.
How can we downgrade MySQL 8.0.20 to 8.0.19 on Ubuntu, ...
-1
votes
1answer
27 views
Stored Proc hangs when no rows exist in cursor? [closed]
I'm on 8.0.19 if that matters.
Here is my test proc:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `testNullProc`()
main:BEGIN
DECLARE fileID INTEGER;
DECLARE finished INTEGER ...
1
vote
0answers
40 views
Getting error while starting slave on MySQL slave./ Backups in MySQL 8.0.20
I want to set up below command for daily MySQL database backup.suppose We have more than 50 MySQL database in server.
I am using below command to take backup of all databases on server.
Note: I am ...
0
votes
1answer
59 views
Known pitfalls with MySQL partitioning
Are there any known pitfalls with MySQL partitioning when server behavior differs a lot from the expected one?
I'll try to explain what I mean. Let's create partitioned table and fill it with random ...
0
votes
2answers
30 views
Poorly configurated mysql server
Recently I took all MySQL servers that we had over a few servers and put in a single VPS dedicated to MySQL. As a source of information, I used Aurimas Mikalauskas ebook "Speedemy - MySQL ...
0
votes
1answer
30 views
Mysql Alter table with Instant algorithm
I just read this MySQL 8 official docs for new features of online DDL operation, where new algorithms added INSTANT. but I am a little bit confused about its working. Can someone please help me to ...
0
votes
0answers
35 views
Remote Access in Centos 8 and MySQL 8
I cannot access MySQL 8.0 on a Centos 8 server remotely with an error:
ERROR 2026 (HY000): SSL connection error: unknown error number
I can see port 3306 when doing a port scan remotely using nmap ...
1
vote
1answer
16 views
Is it ok for MySQL to always use 18-25% of Memory (I have 2 GB Ram)?
I have MySQL 8 in my Server with Ram of 2GB. And MySQL is always using 18-25% of Ram even when everything is off. Is it normal? If not then plz let me know how to debug it.
1
vote
1answer
34 views
How to “UPDATE table SET col = (CASE WHEN … THEN (SELECT col FROM table ORDER BY col DESC LIMIT 1) END)”?
How would I update a column using a CASE expression where the inner THEN clause needs to calculate a value based on that same column of other rows in the same table?
i.e. Here's a list table with six ...
1
vote
1answer
207 views
Can't set log_bin_trust_function_creators=1 on machine startup - MySQL 8.0
I'm trying to put the log_bin_trust_function_creators=1 parameter into the my.cnf configuration file to set the variable correctly on startup on a machine with Ubuntu 20.04 LTS. But every time I save ...
0
votes
0answers
7 views
MySQL8 leaves railing connections open
We're looking to upgrade our stack from MySQL5.7 to MySQL8. We are using a standard LAMP system on ubuntu16, php7.0 and symfony 2.3 .
On our tests, I have noticed that there is a lot of trailing ...
4
votes
2answers
183 views
Does MySQL 8 ASCII vs utf8mb4_0900_ai_ci size differ when only using ASCII characters?
If I only use only ASCII characters, will VARCHAR (255) with utf8mb4_0900_ai_ci be larger on disk than VARCHAR (255) using ASCII?
1
vote
1answer
34 views
Unexpected query execution plan on MySQL 8 when using Datetime range
I have a MySQL 8 database with about 900 millions of rows. When I run the following query the response is very slow because table B doesn't seem to be using the right index:
SELECT B.name
FROM A ...
1
vote
1answer
26 views
Make sure date of departure is different than date of arrival?
I have the following tables:
Flights (id_flight)
Airports (id_airport)
Departures_Arrivals (id_flight, id_airport), which is the Junction Table between Flights and Airports and it has the following ...
0
votes
0answers
29 views
MYSQL storage increasing unexpectedly (RDS)
I am total newbie to database game.
& I am using mysql 8.0 RDS 20GB free tier.
I don't have much data on it.(just 300 Mb)
But I always get a mail that you have exceeded 85% of storage limit.(...
0
votes
0answers
24 views
Methods for tracking processing time for long running ADD INDEX call in MySQL
I've set off index creation on a very large table in MySQL and while I expected it to take a long time, I'm 5 days in and wondering if there's any way to debug potential issues or simply let it run. ...
0
votes
0answers
27 views
How to “catchup” data after a backup
I need to transfer a database to another server with hopefully 0 downtime.
I have Percona Mysql 8 on the destination and Mariadb on the source.
I have successfully used Percona XtraBackup to create ...
0
votes
2answers
26 views
How to make MySQL 8 alert when integer overflow happens during INSERT … ON DUPLICATE KEY?
Let's consider we have a simple table with auto-incrementing integer ID and some unique column:
CREATE TABLE `test` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`value` tinyint DEFAULT NULL,
...
0
votes
0answers
12 views
Syntax error when update root password
I forgot MySQL root password so I am trying to change it by starting it in safe mode and running following command but getting syntax error. MySQL version is 8. What is the correct syntax to update ...
0
votes
0answers
95 views
SQL Polybase - Connect to MySQL ODBC - Issue with decimal data type
hope someone has a clue about this...
I'm using SQL 2019 Polybase to connect to a MySQL 8 Server using an ODBC (MySQL 8.0.20 ODBC Driver).
CREATE EXTERNAL DATA SOURCE eds_test1
WITH (
LOCATION = '...
1
vote
0answers
21 views
MySQL condition filtering estimate while planning includes `A.col op B.col` but excludes `A.col op val or B.col op val`
In the condition filtering feature that MySQL introduced from version 5.7, has filter estimates for various condition formats in the join order, to estimate the number of rows that enter the join ...
