Questions tagged [mysql]
All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.
22,205
questions
1
vote
1
answer
29
views
MySQL throws NULL value error on non-NULL update
The following query throws a Column cannot be null error, on trying to update the value to 0.
update schema.table set is_historical = 0 where id = 5;
[Code: 1048, SQL State: 23000] Column '...
- 111
0
votes
2
answers
20
views
Improve MySQL concurrent updates
Are there any suggestions to run the concurrent updates on a table (100+ concurrent updates on a specific row)? Same row, like updating the clicks count for an user.
UPDATE `tbl`
SET `cli`=`cli`+ 1,`...
- 1,910
0
votes
1
answer
20
views
Out of my depth with post mysql 8 update (Warning: #1287 Setting user variables within expressions is deprecated)
Now I can get my way around PHP, and I can get around basic MySQL queries, but I'm not really comfy with MySQL, and have inherited an issue that occurred today after our hosting company updated their ...
- 1
1
vote
2
answers
23
views
MySQL 8- Add a Default Partition to a Table Partitioned on a Field Value
I have a table partitioned by values of a KPI field. There are some kpi with a large amount of rows some with small numbers. Partitioning by KPI name (string) keeps query performance good.
The data ...
- 13
-1
votes
2
answers
23
views
Mysql adding slave to a master->master
I have an existing MySQL database as master1 <-> master2 -> slave and I want to add another slave off master2. If I issue the FLUSH TABLES WITH READ LOCK; on master2 necessary to dump the db, ...
- 99
0
votes
0
answers
3
views
MySql not starting on Xampp : InnoDB plugin not initializing due to missing MLOG checkpoints
Unexpectedly MySql server not starting. I checked error logs and found two errors: Aria recovery failed and InnoDB plugin not initializing. Aria issue is solved but InnoDB not initializing.
The error ...
Labid Bin Humayun
0
votes
1
answer
14
views
io.debezium.DebeziumException: Client requested master to start replication from impossible position
I am using Kafka and Debezium to connect to a MariaDB
I have a problem where in the event the MariaDB loses power and shutsdown abruptly, on restart Debezium is no longer able to connect
I have this ...
- 1
0
votes
0
answers
10
views
Recover a database accidentally deleted in phpmyadmin
I have accidentally deleted a dztabase from phpmyadmin plateforme that had no backup copy anywhere. Is there any solution to bring back this database ?
Mohamed Ait Ahmed
0
votes
1
answer
48
views
mysql min with where clause optimization
I want to get min user_id for a tenant_id.
mysql engine - innodb
table - users(id, tenant_id, is_deleted, creation_date)
pk (id)
sk (tenant_id, creation_date)
query1 select min(id) from users where ...
0
votes
0
answers
8
views
MaySQL InnoDB cluster log fill on one server
in my mysqld.cnf file, I enabled logging via the following
general_log = 1
general_log_file = /var/log/mysql/mysql.log
It is a three-node cluster. I can see all the queries in the log ...
- 1
1
vote
3
answers
46
views
MySQL Temporary Tables and Connection Pools
This is more of a "please confirm/correct my understanding" question.
Background
I connect to MySQL using JDBC, the connection pool has around 250 connections, most of which seem ...
- 25
0
votes
0
answers
28
views
MariaDB Error - Access denied for user 'root'@'%' (using password: YES)
I am trying to grant file privilege and grant a role to the user but unsuccessful:
ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES);
I am able to assign select on . directly ...
- 1
1
vote
1
answer
16
views
Identify sequences of events starting at well-known start locations, end locations not predetermined, and WITH MYSQL 5.x
I'm working within the limitations of MySQL 5.x.
Several security guards are responsible to walk around a property and visit checkpoints. Two specific gates permit entry and each guard must begin a &...
- 11
0
votes
1
answer
21
views
Best practice to implement count based subscription model?
Currently I have implemented a system where user subscribes to a plan for specific number of events. Whenever a new event is being created I am checking count against that user, say
SELECT COUNT(*|id) ...
- 101
0
votes
2
answers
40
views
MySQL 8 query doesn't prefer index with higher cardinality
I have a query similar to the following:
FROM example_table
WHERE
`date` BETWEEN '2023-11-26' AND '2023-11-28'
AND location_id IN (3, 4, 6, 7, 8, 10, 11, 12, 14, 18, 19, 22, 23, 24, 28, 29, ...
- 213
0
votes
0
answers
20
views
cant login to mysql cli due to this error unknown variable 'default-authentication-plugin=mysql_native_password'
I already tried adding default-authentication-plugin=mysql_native_password in my.cnf but still error occurs.
0
votes
1
answer
15
views
aurora mysql - migrating large tables
We have write intensive application using aurora mysql 3.02 with 2xlarge instance size in prod. I want to migrate 10 tables (biggest one being 450GB) from 2nd aurora mysql into this prod db.
Tried ...
0
votes
0
answers
26
views
Unable to resolve pyodbc dependency error while migrating SQL Server database to MYSQL
I am using MySQL workbench 8.0.35 to Migrate Database from SQL Server to MySQL.
I have installed python 3.12.0 and pyodbc 5.0.1.
When i select Start Migration in the MySQL workbench i am getting ...
1
vote
1
answer
45
views
MySQL - Is a user with the "REQUIRE SSL" option still able to log in without providing any SSL or CA certificate?
My MySQL Server has the following parameters configured properly
mysql> show global variables like '%have_ssl%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| ...
- 630
0
votes
1
answer
27
views
Merging old backup in current database mysql
I have a backup database that contains data up to the specific date 27-11-2023. Currently, I have the latest version of the database with updated records. However, the issue arises when I consider ...
0
votes
0
answers
35
views
Mounting mysql /var/lib/mysql on Azure File Share
With Azure Container Apps, I'm trying to mount mysql on Azure file share, and the container shows running status but as soon as I enable ingress on TCP 3306, the container fails.
InnoDB ...
user1930027
0
votes
1
answer
15
views
MySQL Drop Federated Table When Remote Server is Down
We have a production database (MySQL 8.0) that has a federated table in it. The remote server had an IP address update, and whenever a client opens this DB, it takes forever (~5 minutes) since table ...
- 101
0
votes
0
answers
32
views
mysqld got signal 6 - MySQL server crashes every 2-3 minutes
MySQL server crashes every 2-3 minutes due to the issue below, how do I understand where exactly does the error occur and how to stop this from occurring? Any help with how to properly read this to ...
- 101
0
votes
1
answer
34
views
cpulimit/iolimit per mysql/mariadb user
I can set cpulimit, iolimit, etc for mysql process by moving its pid into cgroup.procs file of a cgroup.
But single mysql process handles all the queries(threads) and I cant set cpulimit to the entire ...
- 1
0
votes
0
answers
41
views
upgrade data dir from mysql 5.7 to mysql 8
Seems like mysql_upgrade is deprecated but how to upgrade the database then?
When starting mysql 8 with datadir from mysql 5.7 the server can not start
2023-11-27T22:45:15.292059Z 0 [System] [MY-...
- 147
1
vote
1
answer
29
views
how to speed up and optimize mysql search with indexes
I need to optimize performance on a search with lots of strings like Z1719121.
my table structure:
codfabrica => varchar 191 => nullable
codoriginal => varchar 191 => nullable
aplicacao ...
- 113
0
votes
1
answer
67
views
No privileges to create databases
I just upgraded to mysql8
Have created a new user for phpmyadmin, but the user can not create new databases?
in phpmyadmin
No privileges to create databases
create user
CREATE USER 'phpmyadmin'@'...
- 147
-1
votes
1
answer
19
views
select user with least amount of tiquets between all other users
I have a table where I store users info like this
user
tickets
s.lewis
0
a.barrantes
2
x.mendez
3
j.robert
0
I want to create a query to select the user that has the least amount of tickets ...
0
votes
1
answer
71
views
Error on observer while running replication hook 'before_commit'. On 200k rows update
I have script to encrypt columns in table. >200k rows. On 100k its working.
UPDATE table
SET col1 = TO_BASE64(AES_ENCRYPT(col1, @keyBase, @static_iv)),
col2 = TO_BASE64(AES_ENCRYPT(col2 , @keyBase,...
- 3
0
votes
0
answers
38
views
batch insert dead lock
Can anyone help me? Why is this deadlocked?
mysql: 8.0.22
design tables
Note that this is the desensitized table structure
CREATE TABLE `room_db`.`Untitled` (
`id` bigint NOT NULL AUTO_INCREMENT,
`...
- 1
0
votes
1
answer
31
views
Reset root password in MySQL
I am logging in with root user in MySQL, and the server has been hardened, so this parameter is set default-authentication-plugin=caching_sha2_password, and this is currently the only user I have.
...
-1
votes
0
answers
37
views
MySQL backup SQL contains wrong creation order
I have created a full backup SQL file from a production database (v8.0.32) using the official mysqlbackup tool with default parameters.
I tried to restore it on a localhost MySQL server using MySQL ...
- 101
0
votes
1
answer
53
views
innodb_buffer_pool_size complications
I had some complications regarding innodb_buffer_pool_size. I read the official documentation, and I found out the formula innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.
so if my ...
1
vote
0
answers
17
views
''No database selected'' error in Render
I created a project on Node js(Express js) with sequelize ORM and earlier, I used sequelize-cli to create and migrate database and tables respectively.
this is my package.json:
{
"name": &...
- 11
1
vote
3
answers
51
views
MySQL query optimisation
This is the MySQL table DDL:
CREATE TABLE `users` (
`username` varchar(250) NOT NULL,
`jid` varchar(250) NOT NULL,
`nick` text NOT NULL,
`subscription` char(1) NOT NULL,
`ask` char(1) NOT ...
1
vote
1
answer
22
views
how to generate dummy data in mysql using stored procedure in relatable table?
I want to generate dummy data for country and states table and the states table have a relation with the country table using a foreign relation ship here are the tables
MYSQL table
CREATE TABLE ...
- 11
0
votes
0
answers
14
views
MySQL access denied problem - random, sudden password changes?
It looks as if my MySQL password is randomly changing after some time. I lose access - i can't log in with the password. I have to set it up again for it to work, and after about one hour do it again.
...
0
votes
1
answer
30
views
Query for converting a column of numbers matching a regex in MySQL
I have a column which contains string values of the format e.g AB12345678 - two letters followed by 8 numeric digits.
I need to write a MySQL query to insert a 5 before each set of 4 digits thus ...
- 133
-1
votes
0
answers
71
views
SELECT query with ORDER BY is slow
A simple SELECT with ORDER BY on a table of 25 columns and 20 Lakh rows is slow, taking almost 40 seconds.
When ORDER BY is id (PK) the no issue takes around 0.003 seconds but when ORDER BY is on a ...
0
votes
2
answers
36
views
MySql binlog file getting truncated/shrinked during replication
There is a master-slave setup of a mysql db. The replication was going on fine until pos y when an error was thrown Client requested master to start replication from position > file size.
I tried ...
- 23
0
votes
0
answers
27
views
MySQL time field type conversion issue with AWS DMS
I have a DB MySQL v5.6 community edition as a source DB and AWS RDS MySQL v5.7 as target in AWS DMS and have an issue with time field type. DMS task is full load and replication. During full load all ...
- 1
-1
votes
1
answer
46
views
CURDATE() is not valid at this position expecting an identifier
MYSQL
create table Emp
(
Eid int primary key,
Ename varcharacter(20) not null,
Eage int,
Eadd varcharacter(21) default ' Cairo ',
HireDate date DEFAULT ***CURDATE( )***,
);
I want to set a ...
0
votes
0
answers
20
views
MySQL 8 host_cache returning wrong HOST for IP address
We're trying to troubleshoot a number of issues with the host_cache on a MySQL 8 server
The issues are all tied to a recent update where we tightened our user "From Host" definitions from @&...
- 181
0
votes
0
answers
86
views
Moving from MySQL to MariaDB, slower performance with some query's
recenty we've have moved all of our server to new hardware and to MariaDB . I've installed a new server with VirtualMin for managing all of our company's applications. at a first glance most of the ...
- 9
0
votes
1
answer
39
views
MySQL and PostgreSQL database table schema comparison
Is there any direct method to compare tables between MySQL and PostgreSQL.Comparing should include field name, types, default values, etc; As an example below,
Below is a dump of PostgreSQL
...
0
votes
0
answers
27
views
Replication between two galera clusters
I currently have two 3 node mariadb (10.5.9) Galera clusters set up in different locations, I'm using standard replication to copy data between the primary and secondary.
What I've recently noticed is ...
1
vote
2
answers
31
views
Why does MySQL puts ANALYZE queries into `System Lock` state, when there is intense I/O
On MySQL 8.0.33, I'm experiencing a form of contention (reproduced on multiple instances) that is not explained by the documentation.
As example:
I boot an instance
on a console client, I invoke a ...
- 368
2
votes
1
answer
52
views
how percona hot backup works / innodb crash recovery works?
I am trying to understand the process of how percona does hot backup.
from what in understand is, they use innodb crash recovery mechanism and execute the following steps.
store/remember start LSN ...
- 123
0
votes
1
answer
28
views
Why does MySQL need double-write buffer if there is redo log and undo log?
Why does MySQL need double-write buffer if there is redo log and undo log?
I read other posts that answer why MySQL needs double write buffer. They point to this article. http://dimitrik.free.fr/blog/...
- 101
1
vote
1
answer
67
views
Database design, one to one relation split into multiple tables
Let's assume we have following database:
Or in pseudo sql format (all columns are not null):
create table road (
id int PK
)
create table item_on_road (
id int PK
road_id int FK road.id
...
- 135