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
0
votes
0answers
14 views

View additional relevant keywords used for query expansion match queries [MySQL]

Using mysql's match against a fulltext index with query expansion, we can do blind query expansion with WHERE MATCH (matchField) AGAINST ('keyword_to_expand_on' WITH QUERY EXPANSION); And this returns ...
0
votes
2answers
17 views

Is there any performance difference between UNIQUE INDEX and INDEX with the same cardinality on MySQL?

I have a table that has id and created_at. Column | Non Unique | Cardinality ------------------------------------- id | 0 | 1000 created_at | 1 | 1000 id is unique, and ...
0
votes
0answers
14 views

Migrating data to new mysql rds instance

I have 1TB size mysql database for production . Now i want to migrate this database on new mysql RDS instance . Please suggest me which backup policy should i apply here like Mysqldump,XtraBackup . ?
0
votes
0answers
9 views

Sql combination matching criteria - The icrecream kiosk issue

Let's create this case Icecream type | Available |Normally eats (person) | Price Small cup 2 1 1.50 Medium cup 0 1.5 2.00 ...
0
votes
0answers
23 views

Recover data from MySQL Incremental Backup files (.FRM and .IBD files)

I have MySQL Enterprise full and incremental backups (.FRM and .IBD files). I have copied the full backup files to a different server, created table structures and imported the data the data using ...
3
votes
2answers
109 views

How can I list database names within a mysqldump file?

I have a large mysqldump and I need to create the databases before importing them one-by-one. How can I search and list only the database names using the linux or mysql terminal?
-1
votes
2answers
60 views

MySQL UUID() behavior

4 years ago, I answered a question on SO, "how to set an UUID for each row in bulk". I answered, after trying it, UPDATE table SET uuidcol = UUID(); It worked for me, but about half (it ...
0
votes
1answer
23 views

Change default timezone to America Pacific

SELECT @@GLOBAL.time_zone, @@SESSION.time_zone; Result: SYSTEM SYSTEM SELECT CURRENT_TIME; Result: 06:25:07 But MY System time is 11.55 AM IST and PST TIME is 11.25 PM How can I change ...
2
votes
1answer
28 views

How does Database Snapshots Work (MySQL)?

I have a MySQL database hosted on an AWS RDS instance. The database has many tables and I observed the following: I picked a table with about 1-2 million records that is indexed on a created_at ...
1
vote
1answer
15 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
33 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
8 views

Create junction table where one referenced table has a composite key as PK

I have a category table which currently has only a title field (PK) CREATE TABLE `category` ( `title` varchar(20), PRIMARY KEY (`title`) ); and a product table with a composite key as a primary ...
1
vote
1answer
20 views

What happen if innodb_log_file become full?

Once innodb_log_file ib_logfile0 & ib_logfile1 got full while running big transaction,so what is mysql next action in this situation ?
1
vote
0answers
13 views

is there any method should i use for trigger update?

I try to modify the syntax of SQL trigger to mysql trigger [check 32:01 for reference] ]1 someone suggested me to use this syntax in mysql, however I got error in mysql. DELIMITER // CREATE TRIGGER ...
0
votes
2answers
27 views

Issue with Auto increment in MYSQL

Current Structure in MYSQL: CREATE TABLE `JOB_ONPREM` ( `JOB_ID` bigint(20) NOT NULL AUTO_INCREMENT, `JOB_NAME` varchar(255) NOT NULL DEFAULT '', `SANDBOX` varchar(100) NOT NULL ...
0
votes
1answer
29 views

MySQL: which innodb parameters should I tune if disk I/O is slow

Which InnoDB parameters should I tune if disk I/O is slow? Please give your suggestions. I have another question regarding innodb_log_file :- What happen if innodb_log_file become full ? so what ...
1
vote
0answers
71 views

MySQL Slave SQL Running State - “Waiting for dependent transaction to commit” confusion

I'm trying to debug a replication lag issue. We have a job that we run over the weekend, where we delete a large number of rows (potentially millions) from a table, currently in batches of 100. While ...
0
votes
0answers
17 views

Why the thread got system lock?

I got a thread from the processlist showing System Lock on this query, running about 87 seconds update zxmb_tac set status=1, verify_date=now() where uid=? and tac=? and status=0 and now() ...
1
vote
1answer
28 views

How to calculate running balance for many accounts?

please help me fix query below to calculate running balance correctly. What I need to do is: Calculate running balance (debit- credit) by account_no Reset the balance when account_no changes Reset ...
1
vote
1answer
25 views

Normalize SQL to the same format

I want to write a package that will manage mysql/pgsql views, but for that I need to check if the view defined in the code is the same as the view in the database which I get with show create table ...
1
vote
1answer
21 views

How to split a list of number into ranges with a fixed interval with SQL?

Let's say I have a table like this I want to calculate the frequency ( How many times that product exists in that price range ), in intervals of "50" So eventually it will give me a table like ...
0
votes
0answers
19 views

Xtrabackup Incremental Backups Failing After Enabling Change Page Tracking on MySQL 5.7

A week back I'd enabled the parameter innodb_track_changed_pages=ON on one of our Percona MySQL 5.7.10 database instances that we use as a Data Warehouse. The OS is Ubuntu 16.04. So far the ...
0
votes
0answers
20 views

Replication affects mysql service

I've got a problem with my MySQL slave which receives updates from two masters. Steps to reproduce: 1) I start the slave 2) The slave receives updates for 1-2 hours, after which fatal error crashes ...
0
votes
2answers
142 views

MariaDB Insert queries stuck frequently

I am currently doing high insertions into my MariaDB 10.4.12 database via scripts. However, sometimes the insertion queries just get stuck/piled up at what it seems to be at the Update or Commit state....
0
votes
0answers
19 views

DB design for Shifts for Services/Events

I need to design DB to store Employee Shifts where employees work on different Services. Shifts for Services will be mostly weekly and for someday there could be specific shifts for employees like ...
4
votes
2answers
163 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?
3
votes
2answers
35 views

Python MySQL prepared statements slower than regular queries

I'm trying to use prepared statements with python to improve query performance, but my prepared statements are slower (by a factor of ~7.5 no matter how many iterations I set in my benchmark script). ...
0
votes
2answers
13 views

Possibility on huge MySQL database replication

I have a mysql database with size 2.4TB which has lots of reads and writes happening continuously. I want to replicate this database as a backup. But it's almost impossible to get a dump from this ...
1
vote
1answer
35 views

SQL Trigger that creates new table when a new record is added to current table

It's been quite a few years since I have done DB work. I am working through mysql workbench (using MS Access as a frontend). I have a base table that I am multilinking (basically a name index type ...
0
votes
0answers
14 views

MySQL/MariaDB extract multiple values from a JSON column

My goal is to extract about 20 fields out of JSON column with 100 fields, some are strings, total length of json text is around 40kb. The only way I found it's possible is to write it like this: ...
0
votes
1answer
24 views

MySQL: Foreign key constraint on composite key with the referenced table a function of a constituent column of the key

I have two MySQL tables customFieldsText and customFieldsMultipleChoice. Both tables have an auto-incremental unsigned int primary key. I'd like to create a new table called customFields with the ...
1
vote
3answers
16 views

MySQL master slave replication-troubleshooting

Why does the Procedure/function created on the master replicates to slave even after enabling table filters using "replicate-do-table". This create a lot of problems in the production setup as it ...
0
votes
1answer
19 views

mysql database update possible timing issue

I have a strange situation. I have a cronjob running queue tasks one after the other. I have a delay of 1s between the tasks. After a task is run, it sets processed=1, and continues with the next one,...
0
votes
1answer
15 views

How to configure MySQL master master replication but use only one server for writing? [closed]

How to configure MySQL master master replication but use only one server for writing? Is it done at LB end and how? If two users work on same db and same table and they both enter a data into it which ...
-1
votes
0answers
26 views

Optimizing product variant db schema

I am trying to model a db schema for product variant. Here is a snippet of my db schema related to product variant ---------------------- ------------------------ |skus | |...
1
vote
2answers
31 views

Almost unique constraint in MySQL

Is is possible to have a key in MySQL 5.7, which is unique for all values except one. Let's say -1 is a not assigned wildcard. Is it possible to have a table with the following structure user | car | ...
0
votes
1answer
22 views

MySQL Stored Procedure Compiles Without Error (I want the error to occur)

I have a MySQL stored procedure that runs fine and does its job quite well. Yesterday, I edited it in MySQL Workbench and introduced a spelling mistake on purpose. For example: - Valid statement: ...
1
vote
1answer
116 views

1822, "Failed to add the foreign key constraint. Missing index for constraint

I have 3 tables: users, photos, avatars (photo of user_1 that currently see user_2) when user deleted, all his photos and avatars, that shown to other users also removes What I do wrong? cursor....
0
votes
3answers
29 views

What is wrong with this syntax of a multi-table DELETE…ORDER BY?

I have the following query: DELETE proxies, pxpl FROM proxies JOIN proxy_xref_proxy_list AS pxpl ON proxies.ID=pxpl.ProxyID WHERE pxpl.DeactivationTS < (NOW() - INTERVAL 24 HOUR) ORDER BY pxpl....
0
votes
1answer
17 views

Set variable inside cursor for to use in select statement [closed]

This is my query to create procedure, but its displaying error like : 'DECLARE curBalRows CURSOR FOR select if( What I'm missing here? DELIMITER $$ drop procedure if exists `...
0
votes
1answer
13 views

how to convert query related to calculate work hours in MySQL to MS Access [closed]

I want to calculate work hours in each day using MS Access.I have query for same thing in MySQL but this thing is not working in MS Access using pdo php. query in MySql SELECT MachineNo, ...
0
votes
0answers
19 views

MySQL InnoDB Weird Query Performance

I designed two tables, both using InnoDB. The first one has columns "Offset, Size, and ColumnToBeIndexed" with BIGINT "Offset" being the primary key, and the second one has columns "OffsetAndSize, ...
0
votes
2answers
58 views

RDBMS: Proper way to store data - comma separated variables or in different fields or tables?

Fundamental question that I cannot seem to find an answer on. I have a database that stores line items from receipts along with the username and receipt number. Right now the lineItem column is just ...
0
votes
4answers
54 views

Difference between two rows in MySQL Workbench, but LAG is not authorized

I have a dataset as in this example : id | product_id | date | weight 1 | 454 |2019-06-26 16:08:45| 900 2 | 454 |2019-06-27 13:24:16| 900 3 | 454 |2019-...
0
votes
0answers
28 views

MariaDb: Create a nested json object query

I'd like to generate an array of json objects in MySql select query. :: Update :: Here is a db structure for which i'd like to return the result as below format. CREATE TABLE IF NOT EXISTS `...
0
votes
1answer
20 views

MySQL Group Replication Multi-Primary Setup

We are running three MySQL Servers in a Group Replication Multi-Primary Setup. Here are is one of my configuration of one member of that GR group: # General replication settings gtid_mode = ON ...
0
votes
0answers
17 views

MySQL: Use a LIMIT for a JOIN table added

View The view of the app contains a page which shows a bunch of carousels (each carousel = a tag), each of each should contain 5 products: +-------------------------------------+ |Page ...
0
votes
1answer
26 views

Validate database backup For Innodb tables

Every fortnight, I take MySQL database dump from slave and then perform following steps as backup validation. Number of Tables in every database. Validate Schema of tables. Validate random records ...
0
votes
0answers
42 views

MariaDB: SQL query to return array of json objects and remove `\` from response

I am creating a sql query for api response in javascript. But i stuck on one part that is creating a array of json objects of same group. I have checked with the json_arrayagg and also ...
0
votes
0answers
13 views

restore mysql database from ibdata1 and frm files

I am trying to restore some database data from my tables frm files. I am running a mariadb database. The data structure works fine and i can see the tables etc. But as soon as i add the ibdata1 and ...