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
1answer
33 views

Why we can't have more than one inequality condition in MySQL indexing? [closed]

I am recently studying about database indexing in MySQL. I read some articles and found a great web page: http://mysql.rjweb.org/doc.php/index_cookbook_mysql I read in this page and some others that ...
0
votes
1answer
14 views

get base urls with different “endings” after the last slash

Okay, so have a wonderful script that checks the "base" url up to a certain number of slashes, but realized what I really need is checking the "ending" going backwards to the first non-escaped URL ...
0
votes
1answer
26 views

Query to sort data from a MYSQL 8 table

I request your kind support with the following case. I have this score table as follows: create table score (idscore int primary key auto_increment, ndeporte varchar(45), ...
0
votes
1answer
15 views

get duplicate “base” URLs with mySQL

I can't quite seem to figure this out, and hoping someone can help. I was trying something like this, but doesn't seem to work: SELECT * FROM testtable GROUP BY SUBSTRING_INDEX(urlTest,'/',3) ...
0
votes
1answer
36 views

I want to remove Json object from Json Array in Mysql 8. Column data type is Json

I'm using Mysql 8. I want to remove the specific object from JSON Array which has value like field_1568988989723 in Mysql JSON column Here is my JSON [{"section": {"name": "Basic Info", "order": 0, "...
0
votes
2answers
28 views

asp.net caused mysql 99% usage

I have an asp.net application which use Mysql. I test it on testing server running MySQL. When the application is running, it consumes 40% of cpu. I run the same code in production server which also ...
0
votes
1answer
10 views

how to get data from two tables in mysql query with where Pk of one table is set as foreign key on PK column for other table?

The query I am using to retrieve data is as "select sum(totalbill) as tbdb, sum(discinamount) as tddb, sum (billafterdisc) as tbaddb," + " sum(totalqt) as tqdb," + " sum(payed) as tpdb," + " sum(...
0
votes
2answers
68 views

MySQL table gets slow after big deletion?

I am having this problem in a recurrent way, in different projects, and different databases. MySQL version SHOW VARIABLES LIKE "%version%"; Variable_name Value immediate_server_version 999999 ...
0
votes
1answer
19 views

Why the MySQL query for where clause is very slow?

I was running a query, the rows of the table are about 10 millions: select * from zhihu.zhihu_answer_meta where voteup_count>100000 limit 5; However, it took more than 20 mins to finish. I also ...
0
votes
0answers
38 views

How do I stop Amazon Web Service's RDS from locking out connections during backups?

I've been using RDS for years, and this only became an issue a few months ago. I was pulling my hair out trying to find the source of the issue... only to figure out it's suddenly RDS. I've made no ...
0
votes
1answer
22 views

Trigger based on linked table

OK I have 3 tables: client: ClientID (int)auto-inc clientName (varchar) tnt_group: groupID (int)auto-inc ClientID (int)foreign-key tnt_item: itemID (int)auto-inc itemNo (int) groupID (int)...
6
votes
2answers
76 views

How to insert values into a highly normalized database without excessive duplicate checking?

The situation: The lead for our project has decided to use a highly normalized database as our database design. Meaning that literally every field of a big table is an ID instead of the actual value....
0
votes
1answer
39 views

Best way to replicate MySQL database tables

I'm trying to wrap my head around the many options on how to possibly sync database tables automatically from one database to the next (and next, and next, ...). We're trying to create a very simple ...
0
votes
1answer
19 views

distinct description

I have the following database: id | remote_id | title | description ------------------------------------- 1 | 12341234 | test | test-description 2 | 4535234 | blah | testestetetst 3 | ...
-1
votes
1answer
18 views

Is it possible for a foreign key referencing two different columns for different tables in mysql

I have projects table for example: Id | name | description | created_at | updated_at and tasks table like Id | project_id | description | created_at | updated_at I have an comments table Id | ...
-2
votes
1answer
45 views

Implementing a table that maintains a max heap property

For the last few days I was thinking about implementing data structures in MySQL. My idea was to create a table (or a set of tables) that allows me to implement the properties of a binary max heap. In ...
1
vote
1answer
28 views

Is there a MySQL character set and encoding that will allow for both emojis and accents?

I've got a database of terms that get added to by one group of users, and queried against by another. I was running into problems when people would query for an emoji in the database and my React app ...
0
votes
2answers
41 views

Can't create database even with all privileges

[mariaDB on RedHat 7.7] I just want to create a new database, but found "access denied": create database lamp_k; ERROR 1044 (42000): Access denied for user 'lamp'@'localhost' to database 'lamp_k' ...
1
vote
2answers
33 views

MySQL query took extremely long time to finish and it was in 'Sending data' state

This query took extremly long time and it never seemed to finish. Below is the query: INSERT INTO `zhihu_answer_meta_reconcile` SELECT a.* FROM `zhihu_answer_meta` a LEFT JOIN `...
1
vote
0answers
23 views

MySQL Won't Start - InnoDB Table Corruption

I've found lots of articles and questions related to this issue, but none that seem to apply or work in my situation. MySQL will not start after I had to do a fsck repair on my server's hard drive (...
1
vote
1answer
30 views

How to SELECT a sibling in an adjacency list type table?

I'm trying to get the next sibling in a list according to an integer position column. Rows are siblings when they have the same parent_id. Given that sibling foo.id = 192 and foo.position = 0, I want ...
1
vote
1answer
29 views

MySQL load data infile ERROR 1064

I am using MySQL version 5.5 and I am trying to use load data infile and I am getting errors. I've looked at the documentation and it looks like I am doing exactly what the docs specify. Running ...
0
votes
0answers
10 views

How does MySQL get its timezone information on Windows without the time zone tables package?

I setup MySQL deployments exclusively on Linux, but I have a development environment on Windows that has a MySQL server for testing purposes. To make things easier, MySQL runs with default-time-zone = ...
1
vote
0answers
24 views

Syntax error while using variables in stored procedure

This might look like a silly question so pardon me. I was trying to write a trigger in mysql and am constantly getting #1064 which, I believe is a syntax error. Here is the sql for the trigger: ...
0
votes
1answer
25 views

Group_concat on IF Select MYSQL

I have a query as below: SELECT o.order_id, c.customer_name, Group_concat(p.product_name, ' x', p.product_quantity ORDER BY receipt_order ASC SEPARATOR '<br>')...
3
votes
2answers
38 views

What happens when MySQL/InnoDB buffer pool size is small and a large transaction is run causing the buffer pool overflow?

Let's say we have a transaction running 50,000 inserts which dirties approximately 600 MB of the database. If the buffer pool size is 128 MB, there will be dirty pages in the buffer pool while the ...
-1
votes
2answers
35 views

MySQL Query missing from the slow query log

I've been looking at queries creating many temporary tables in the performance_schema.events_statements_summary_by_digest. FROM performance_schema.events_statements_summary_by_digest where ...
2
votes
0answers
29 views

SELECT Query runs fine but throws an error when used as source for REPLACE INTO

I have a query, that runs fine on its own: SELECT * FROM tabA WHERE colA != '--'; Now I would like to use it as a source for a REPLACE INTO statement. The query looks like the following: REPLACE ...
0
votes
1answer
30 views

Access denied for user 'me'@'localhost' (using password: NO)

I am am trying to learn mysql 101. Doing so, I broke my ability to login as root. At first I could simply connect to mysql by running sudo mysql In an attempt to alleviate a problem, I ...
0
votes
0answers
12 views

LEFT JOIN on AFTER INSERT Trigger in MariaDB or MySQL?

Is there any simple way to access records related to a new one that is causing a trigger? I am allocating expenses for vehicle use, by creating a general journal entry via AFTER INSERT Trigger when a ...
0
votes
1answer
25 views

mysql/aurora not immediately seeing results of committed transactions

In a system running against an AWS aurora-mysql database, the following (obviously extremely simplified) code runs. It's a highly concurrent system, multiple threads, multiple connections to db. The ...
1
vote
2answers
49 views

Join multiple tables for aggregate or create a single table with combined attribes

I have the following ER diagram. Now i have option of going with a single table ARTDATA as mentioned, or, I can go with 13 different tables linked with each other with relations. Problem is for ...
1
vote
2answers
39 views

MYSQL: How to optimize a date sorted table with a triple primary key index

I have been maintaining a MYSQL database for a few years, dating from back when i took database managing classes. Its biggest table has since grown (15 million entries for 1,8 GB) and this query began ...
1
vote
0answers
36 views

How to enforce “at most N child rows” in a foreign key relationship without a trigger?

I have a table that I will be using to save "template" messages for my users. The schema is CREATE TABLE listemailtemplates ( id int primary key auto_increment, userId int not null, ...
0
votes
2answers
18 views

MariaDB mysqldump lacks all data - how do I force including it?

for years I've been using mysqldump without much trouble. Recently I joined a project that is using "MySQL dump 10.14 Distrib 5.5.52-MariaDB, for Linux (x86_64)" and when I attempt to do a dump it ...
0
votes
1answer
14 views

MySQL datafiles on SMB3 network storage

We have MySQL-server on Ubuntu with InnoDB-datafiles on a local disk. As part of an upgrade program, the company is planning to put the datafiles on a Windows fileserver where they can be mounted with ...
0
votes
0answers
25 views

Implication in Increasing MySQL maximum keys allowed per table

We have a situation where we encounter a MySQL error ERROR 1069: Too many keys specified. Max 64 keys allowed In our database (using Magento E-commerce) we need to add 76 indexed key columns but ...
0
votes
1answer
45 views

Is it faster to split a large table into 12 rolling monthly tables & use UNION them for reports or keep large table & delete rows older than 1 year?

My co-worker wants to split a large 158M row stats table into stats_jan, stats_feb, ... and use UNION to select from them for reports. Is that standard practice and is it faster than to just use the ...
0
votes
1answer
18 views

Performance when using truncated VARCHAR as index in MySQL

According to the answer of choice of this question: https://stackoverflow.com/questions/3489041/mysqlerror-specified-key-was-too-long-max-key-length-is-1000-bytes CREATE INDEX example_idx ON ...
0
votes
2answers
23 views

Multi-Master Multi-Region MySQL Cluster

I am hosting an application (a game) in multiple regions around the globe. Performance is critical to this application since it's a game, and therefore I need to host a database instance in each of ...
0
votes
2answers
52 views

What disrupts the inherit logic order of autoincrement that leads to illogical ordering of ids in the absence of an ORDER clause?

On occasion I come across a database table on a project where when no sorting is applied (ORDER clause) that the ids are not in their inherit logical order. 1 178 3 144 2 Under the presumption that ...
0
votes
1answer
16 views

Mysql binary logs are not deleted after expiration

I have 3 Mysql 5.6 nodes in one galera cluster in the first DC and 3 MariaDB 10.3 nodes in another galera cluster in the DR site. One node on the 1st DC is the master for one slave node on the DR site....
0
votes
1answer
38 views

Optimizing mariabb/mysql queries as a DBA

I'm maintaining a database that's used by a bespoke software application that has some performance issues. On of the worst offenders is query (3) below, and I'd like to, if possible, optimize the ...
0
votes
0answers
16 views

retrieving all related rows via foreign key before deletion

I have a table A, Many other tables have foreign keys which cascade on delete. Is there anyway to get all the deleted rows before, using delete from A where id = 1 . Basically What I want is to ...
3
votes
0answers
31 views

Are there any tools that support Database query submission, review, approval and execution workflow? [closed]

We are a very small setup in the e-commerce space. More than often there is a need to correct data manually in our MySQL database via SQL query execution. There is 1 database administrator in our ...
0
votes
1answer
35 views

I need to remove duplicate values SQL Left Join [closed]

My SQL code: SELECT users.id, users.username, job.category, locations.location FROM users INNER JOIN job ON users.username = job.username INNER JOIN locations ON job.username = locations.username ...
0
votes
1answer
37 views

Unexpected slow queries

Using LEMP on popular cloud-service virtual server, with MyISAM tables. The problem is that sometimes once in few hours simple mysql queries (select from where) executing several seconds (5-9) ...
2
votes
2answers
53 views

InnoDB errors and crashes with MyISAM database?

I have one server with one mysql database containing only MyISAM tables. There is no other databases on the server (except system ones of course). Since it moved to mysql 5.7.27 there are InnoDB ...
2
votes
2answers
65 views

Select random database records between subset of records having the same count(*) in a query

I have this simple query: SELECT `value`, count(*) as `noHits` FROM `topic` WHERE identifier IN ('" . implode("','", array_keys($processedIdentifier)) ."') GROUP BY `value` ...
0
votes
0answers
16 views

Reclaim disk space after drop table from database in mysql

I have this version of MySQL server: mysql Ver 8.0.15 for Linux on x86_64 (MySQL Community Server - GPL) I use a some large tables. When I truncate database with truncate database <name>, I ...