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.

Filter by
Sorted by
Tagged with
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 '...
user997943's user avatar
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,`...
TheDataGuy's user avatar
  • 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 ...
Adrian M 's user avatar
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 ...
user22668153's user avatar
-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, ...
rwfitzy's user avatar
  • 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 ...
user avatar
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 ...
RedRum69's user avatar
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 ?
user avatar
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 ...
user3239193's user avatar
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 ...
orddie's user avatar
  • 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 ...
DSZ's user avatar
  • 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 ...
Hannah's user avatar
  • 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 &...
Newbuntudude's user avatar
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) ...
Rameez Raja's user avatar
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, ...
Shahid Thaika's user avatar
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.
randomdba's user avatar
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 ...
JollyRoger's user avatar
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 ...
pradeep3009's user avatar
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 | +---------------+-------+ | ...
Potter's user avatar
  • 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 ...
Muhammad Faizan Khan's user avatar
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 ...
user avatar
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 ...
MadHatter's user avatar
  • 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 ...
Alen Šimunic's user avatar
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 ...
jay's user avatar
  • 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-...
clarkk's user avatar
  • 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 ...
anderlaini's user avatar
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'@'...
clarkk's user avatar
  • 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 ...
Sarah Lewis's user avatar
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,...
cargt4's user avatar
  • 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, `...
atlan's user avatar
  • 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. ...
datascinalyst's user avatar
-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 ...
Nestor's user avatar
  • 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 ...
datascinalyst's user avatar
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": &...
suborno das's user avatar
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 ...
datascinalyst's user avatar
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 ...
leburik89's user avatar
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. ...
Kiler_630's user avatar
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 ...
therobyouknow's user avatar
-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 ...
Dr.G.Singh's user avatar
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 ...
toros's user avatar
  • 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 ...
blagi's user avatar
  • 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 ...
Madona Samy's user avatar
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 @&...
Dan Kelly's user avatar
  • 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 ...
Stefx's user avatar
  • 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 ...
chinthakaS's user avatar
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 ...
imbrian21's user avatar
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 ...
Marcus's user avatar
  • 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 ...
Rajat Aggarwal's user avatar
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/...
user855's user avatar
  • 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 ...
ZiiMakc's user avatar
  • 135

1
2 3 4 5
445