Questions tagged [mysql-8.0]
MySQL version 8.0 - please also tag with mysql for search purposes.
316
questions
0
votes
0answers
4 views
Python connector will only connect with outdated password
Python vers: 2.7.16
MYSQL vers : 8.0.22
Hi,
I have changed my user password on my DB. Weirdly my python script can only access the DB using the old password not the new one. The updated password works ...
1
vote
2answers
17 views
Mysql per thread memory, variables that lead to thread memory usage?
We recently upgraded from mysql 5.6 to mysql 8.0 on a few servers, one of the servers was fine, and has had no problems, but it
has significantly less load than one of our other servers which has ...
0
votes
0answers
11 views
Mysql keeps re-adding disable-log-bin=1 to my.cnf
We have mysql 8 server configured as master. Every time WHM updates mysql, disable-log-bin=1 is added to my.cnf. Not sure where is the issue, but it is already 3rd time it happens. Current problem was ...
0
votes
1answer
9 views
MySQL8 Binary installation - getting error msg “-bash: bin/mysql: Permission denied” after upgrading to 8.0.22
We have a MySQL 8 instance with Binary Installation. After upgrading to 8.0.22 (from 8.0.19) can't seem to connect using following cmd in CLI
bin/mysql -uusername -p
error msg -
-bash: bin/mysql: ...
-1
votes
1answer
17 views
Error 1005 errno 150 “Foreign key constraint is incorrectly formed” where no foreign key declared
I have an issue on a basic CREATE TABLE statement Error 1005 errno 150 "Foreign key constraint is incorrectly formed" returned where no Foreign key is declared -
CREATE TABLE Person
(
...
0
votes
1answer
17 views
Normalization using DENSE_RANK and LAST_INSERT_ID
db-fiddle
I have created a table called orders (orderId, customersId) and a table called orderDetails (orderId, productId). My goal is to denormalize a table called temp which is like
scanId, ...
0
votes
1answer
21 views
How to properly replicate and archive at once
I have a 300GB database that is growing around 5GB a day. It stores time-based data, and as such, I currently have around 60 days of data. My main database server has a 450 GB NVMe, so I want to get a ...
3
votes
0answers
43 views
Mysql memory usage increasing after upgrading to 8 and converting some tables from MyISAM to InnoDB
I am having an issue with MySQL gradually consuming more memory every day which seems to have coincided with converting some tables from MyISAM to InnoDB two weeks ago. You can see the trend in memory ...
0
votes
1answer
98 views
Migrate MySQL 5.7 to 8.0 via mysqldump
I have an Ubuntu server 18.04 LTS with MySQLl 5.7
I have new Ubuntu backup server 20.04 LTS with MySQL 8.0
MySQL 5.7 backup created:
mysqldump --all-databases > "filename" -u root -p
...
0
votes
0answers
11 views
MySQL InnoDB Cluster - Add instance issue with local port
I have an issue to add instance to MySQL InnoDB Cluster using MySQL Shell. Here are the steps that I have done:
Login to one DB-01 node
Run command: mysqlsh
shell.connect('username@ip-address:port');
...
0
votes
2answers
49 views
MySQL one file per table not working
I've followed all of the instructions for enabling the one file per table in MySQL (v8, on Ubuntu 20):
Added innodb_file_per_table=ON
Restarted MySQL
Dumped all databases, one by one.
However, when ...
0
votes
1answer
22 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 ...
1
vote
1answer
22 views
Mysql same DB, same query, different users - different error messages
On my AWS RDS Mysql 8.0.16 server I have one DB (my-db) and 2 users: the master user (created automatically by RDS) and an app user created by me:
create user if not exists `appuser`@`%` identified by ...
0
votes
1answer
131 views
How to properly downgrade mysql 8 to 5.7 in centos 8
I would like to downgrade MySQL 8 to MySQL 5.7 on centos 8, can I get the proper steps/commands. Thanks in advance.
1
vote
2answers
69 views
Mysql Windows Performance Problem
My system:
2X E5 2680 V3 Cpu
32 GB Ram
Windows 7 Pro
Apache 2.4.41
Mysql 8.0.17
I have a script and this for Customer Online Chat Script. I have 10 clients for customer support. It uses php json ...
0
votes
1answer
10 views
Help needed with several engines use case [closed]
We are developing an app, aprox 50k RPM read, 1k RPM write, it ask via key and get a JSON.
The search is always made by key.
I'm inclined to use one MySQL 8 table with a Id field and JSON field with ...
2
votes
1answer
94 views
Missing mysql, sys, performance_schema
I have Percona Mysql 8.0.19-10 on debian 10.
A year ago I had some issues restoring a backup that required me to mess with tablespaces. I forget what all I had done but eventually I got the database ...
0
votes
0answers
16 views
Master node freezes when second master unreachable in master-master replication
I have a 2 node master-master setup in mySQL 8.0.22. I have successfully got the nodes to both be writeable and update each other in normal operation. However, When I disconnect a node, it is not ...
-1
votes
2answers
60 views
MySQL 8 - optimize all tables + indexes
I have a bunch of tables (InnoDB) within several databases (MySQL 8), that I run Garbage Collect scripts on, on daily basis, removing stale records that are no longer needed. All tables do have ...
0
votes
2answers
84 views
Investigating RDS MySQL 8.0.16 stalls on write-heavy workload
I'm trying to track down some sporadic and difficult-to-reproduce issues with a MySQL 8.0.16 RDS instance connected to a web application with a write-heavy workload on a couple tables with large JSON ...
0
votes
1answer
25 views
Can MySQL server send a “server started/running” signal to a remote server?
I have a MySQL 8 server on a virtual machine. I'd like to start it up when another server wants to communicate with it, and shut it down some time after there hasn't been any connections or queries.
...
0
votes
1answer
160 views
MySQL event scheduler waiting on empty queue since server restarted 12 days ago
I noticed a process on the server which has been running for more than 12 days, which I think coincides with the last time MySQL was restarted.
mysql> SHOW FULL PROCESSLIST;
+---------+------------...
0
votes
0answers
43 views
How to restore a MySQL InnoDB with ONLY .ibd files?
I had a Bitnami Joomla! stack running on a computer that suddenly kicked the bucket. I had neglected to make a back-up before this happened so I do not have any dumps or backed up files, but was able ...
3
votes
1answer
65 views
Testing MySQL adaptive_hash_index option
We recently migrated our application to an entirely new infrastructure, going from a single server managing everything to a kubernetes cluster and dedicated database server running on AWS RDS. On ...
0
votes
1answer
20 views
Is it safe to use the Windows “Compress directory to save space” feature on the directory that contains my MySQL general log file?
I have changed MySQL 8 on my Windows 10 development machine to write logfiles (general log and slow queries log) to "E:\mysql logfiles". I'd like to compress this directory using the Windows ...
0
votes
0answers
152 views
MySQL 8.0 - Could not acquire management access - Administration issue
I am using MySQL Workbench for many months, it was working fine. Today, when I tried to open Server Status in the MANAGEMENT tab, below error occurred.
MYSQL80 and Windows Management Instrumentation ...
0
votes
1answer
22 views
MySQL Shell dump utility - maximum possible chunk size?
The documentation https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html mentions that the min chunk size is 128kB. What is the maximum possible chunk size?
-2
votes
3answers
76 views
can I write the below query using Over()in mysql?
I am trying to optimise the below sql query but am not finding any way to do it:
SELECT DISTINCT
(
SELECT
COUNT(followers.follower_id)
FROM
followers
WHERE
followers....
1
vote
3answers
79 views
MySQL 8.021 = 100% CPU when many UPDATE queries to 1 table
I just install fresh LEMP stack at Freebsd 12 with MySQL 8.021
Noticing high usage of CPU
I tested my queries in normal conditions individual and every is about 0.2s execute time - which is absolutely ...
0
votes
1answer
13 views
How to know if mysql server is using sha_256 or caching_sha_256 authentication plugin?
Seems like I am using an older version on Mysql Server which uses sha_256 plugin for Authentication (which is now deprecated) .Because of this, I am unable to connect to my server with third party ...
0
votes
1answer
28 views
What is happening under the hood in MySQL 8 when I apply an auto-increment primary key to a raw table of data?
I'm responsible for a production database using MySQL 8.0 (InnoDb engine). It has become necessary for me to archive data from a sizeable table (the data file takes up approximately 50% of the hard ...
0
votes
2answers
94 views
Help me tune my.conf MySQL [closed]
Currently, I have been surfing around for ideas and copying some of the processes and details found and playing around with my Server and now seems to be pretty good but with some anomalies in ...
0
votes
0answers
26 views
Issues with mydumper
Recently I changed my default backup tool from mysqldump to mydumper because it was FAST! I was quite happy with the results, and a few days ago I decided to import one of my dumps. For my surprise, ...
2
votes
1answer
46 views
Query works manually, not from stored procedure - Safe update error
I have a relatively simple update query that works fine when I manually execute it from a Mysql Workbench query window, but will not work when I call it as a stored procedure. The stored procedure in ...
1
vote
2answers
29 views
Why MySQL tends to use more and more swap while there is enough RAM?
I have a server with 64GB RAM and 64 GB swap.
It's running MySQL 8.0.19 and few php scripts.
Here is my config:
innodb_buffer_pool_size=40G
innodb_buffer_pool_instances=4
innodb_buffer_pool_chunk_size=...
0
votes
1answer
27 views
MySQL constraint to prevent combinations of columns
I have a table that represents files in directories.
dir | file
----------------
dir_A | file_0
dir_A | file_1
dir_A | file_1
dir_B | file_2
dir_C | file_3
... | ...
I want to put a constraint on ...
0
votes
0answers
20 views
Is it necessary to copy dump to MySQL 8 replica?
I am trying to make a simple master-slave replication topology with MySQL 8.
Official documentation says that if source database has data, it is necessary to update replica with source's dump.
So is ...
1
vote
2answers
212 views
MySQL slow importing on hyper-v virtual server compared to host
Database dump file:
6 MB size
14k rows
10 tables
Server specs (Ubuntu 20.04, virtual (hyper-v)):
OS: Ubuntu 20.04.1 LTS
MySQL version: Ver 8.0.21-0ubuntu0.20.04.4 for Linux on x86_64
((Ubuntu))
4 ...
1
vote
1answer
36 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
24 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
60 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
28 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
24 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
29 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
57 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
36 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
78 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
55 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
53 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
59 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-...
