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
3 votes
1 answer
56 views

MySQL Processlist Seems to Show Incorrect User When Running a Custom Function

I am on MySQL 8.0.28 and I have this function: DELIMITER $$ CREATE FUNCTION DelayedHello() RETURNS VARCHAR(20) DETERMINISTIC BEGIN DO SLEEP(3); RETURN CONCAT('Hello ', 'World!'); END$$ DELIMITER ...
Brian Estrada's user avatar
3 votes
1 answer
40 views

Row Locks in Partitioned Aurora MySQL Table

I'm experiencing many row locks in a AWS Aurora AWS MySQL MySQL version=8.0 Aurora Version=3.04.0 The table is pretty big close to 1 Billion rows. The table is partitioned in 24 partitions based on ...
sonus21's user avatar
  • 131
1 vote
1 answer
43 views

Raid 0 nvme disk configuration is slower

I installed MySQL db for storing my simulation data in phd. First I used a pc with i7-8700 cpu, 24 gb ram and 1 TB nvme disk. Then I transferred data to a newer pc with i7-12700 cpu, 48 GB ram and 2x1 ...
HakanA's user avatar
  • 19
1 vote
0 answers
41 views

How would I connect to a SQL Server (running in a VM using Ubuntu Server), using Access or Visual Studio on my host?

I have tried to connect various ways already and have not been successful. I need a more specific guide on how to/if you can connect this way. I have specific limitations that you need to account for, ...
Vividlee's user avatar
3 votes
1 answer
55 views

How to create a folder within the MySQL datadir without MySQL treating it as a database?

For example, I needed to create a directory called 'backup' within the datadir (temporarily) to store a backup file, but I noticed that MySQL interprets this directory as a database within MySQL. Is ...
Astora's user avatar
  • 703
0 votes
1 answer
52 views

Database Structure/Planning Queries

I'm looking for any guidance possible and not necessarily asking for "the solution" as such. In a MySQL database, I want to understand how to do a capacity analysis for a company. Such that ...
Ed Stephenson's user avatar
1 vote
1 answer
54 views

Change server_uuid in MySQL

I want to know if there is any way to update server_uuid; I am trying master-slave replication, and I need different server_uuid. FYI - I don't have server_uuid in my my.cnf file, and when I update it ...
datascinalyst's user avatar
2 votes
0 answers
34 views

MySQL Client taking more than 14 sec to be connected

IN our on-prem server we have installed mysql 4 node cluster 2 for mgmt and 2 for datanode. All are physical server with 1TB ram and 64 core processor. When a user trying to connect to mysql server ...
Basudev Rout's user avatar
2 votes
0 answers
22 views

Export DB query into Csv giving error on output

I am trying to export all the information regarding exam ie instructor, studentName, questions asked and response but it is giving me this error. i am using wampserver, and can i have two options at ...
Blessing Banda's user avatar
1 vote
0 answers
62 views

MySQL Lower Query Cost in Explain Plan Resulted to Higher Time to Execute

I am running on a MySQL 8.0.28. I have a query that I want to optimize and I am confused with the explain plan and actual result. In the original query, this is the explain plan (I truncated what I ...
Brian Estrada's user avatar
2 votes
2 answers
40 views

MongoDB schema for visit and like

MySQL serves as our primary database, while MongoDB is employed for handling intensive write operations. I have been tasked with implementing a system for tracking visits and likes on products. The ...
BooRuleDie's user avatar
8 votes
1 answer
469 views

How to add index to a big table with 60M records without downtime?

we have been struggling with one issue in the past few days. We want to add an index to a huge table with 60M records. At first we tried adding it with basic mysql syntax. But it clogged our ...
Rohan's user avatar
  • 33
0 votes
1 answer
92 views

Is There a Workaround to Reduce MySQL Table size?

Long story short. I have a MySQL database that has only one table and is around 50GB, while my drive storage is almost full and cannot get reduced. So the only way is to reduce the size of the 50GB ...
Chris921's user avatar
1 vote
2 answers
62 views

Innodb_buffer_pool_pages_free - Can it be 1 or 0?

I have been researching this topic on whether the Innodb_buffer_pool_pages_free can be 0 or not and still has no impact on the site. Some DB admins are of the opinion that ...
Andy's user avatar
  • 13
0 votes
1 answer
21 views

Can we use gtid mode without enabling enforce_gtid_consistency? Is it safe?

If I set gtid_mode = on and enforce_gtid_consistency=off, the operations that are not allowed to use gtid is still replicated in other ways? From mysql documentation: Only statements that can be ...
Astora's user avatar
  • 703
0 votes
1 answer
30 views

SQL Query to Sort Data Based on Maximum Value

Here is my dataset: For any given 'time' (basically every second), about 3 to 5 'cell ids' are logged. What I need the know is the 'cell id' of the maximum 'ref signal _ received power' received for ...
user231744's user avatar
0 votes
1 answer
63 views

create view column name from other table rows and set value if column exists as value mySQL

I have the following table: two tables; one contains options value and another contains options name. option table something like the following : | id. | procedure | ...
Umar's user avatar
  • 1
1 vote
1 answer
35 views

MySQL Proxy no longer working noticed when backend MySQL at version 5.7.43-47-log

When developing data analytical applications, the workstation needs to use MySQL-Proxy (version 0.8) to connect to the MySQL data source. This method has been working fine for years, however, we ...
Mike's user avatar
  • 13
0 votes
1 answer
45 views

How to generate most recent nth day of the month?

Simple question, but I haven't found any examples of this on Stack Exchange: I need to be able to select all records in the current billing cycle, that's it. The catch is the billing cycle doesn't ...
InterLinked's user avatar
0 votes
1 answer
13 views

Ram Usage of Db rapildy increasing how free up it

In my db it is 32 gb ram but it fill now 24 gb rapidly. After hiiting the 32 mark the db restarted automatically . I have set the buufer size 27gb. then also it increasing.How do i free up the ram aor ...
user avatar
0 votes
0 answers
36 views

Running a validation rule on sql result

Requesting your help here. I am trying to find a user_id who’s “work” activity hour range contains the input start_ts and end_ts. Identified users (minimum Start time of the day +(Add) 1 hour) and (...
S-J's user avatar
  • 1
1 vote
1 answer
106 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
78 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,920
0 votes
1 answer
44 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
47 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
34 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
108 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
68 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
89 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
55 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
19 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
141 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
  • 125
1 vote
1 answer
20 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
26 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
73 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
38 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
23 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
87 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
55 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 | +---------------+-------+ | ...
Astora's user avatar
  • 703
0 votes
1 answer
47 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
87 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
23 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
1 vote
1 answer
49 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
  • 11
0 votes
0 answers
100 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
37 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
692 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
0 votes
1 answer
22 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
520 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
48 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
52 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