Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

New answers tagged

0 votes

Mysql - Table structure to hold history log without any primary key

Short answer: Yes, you can simply and safely remove id and the PK. Long answer: The Question is vague on how many rows are deleted every 5 minutes, so my answer is a bit vague. For one thing, if all ...
user avatar
  • 68.2k
0 votes

MySQL COUNT with GROUP BY shows multiple items

This works but I'm not sure it's the best in terms of performance: SELECT COUNT(activities.id) over() AS COUNT FROM activities GROUP BY activities.id LIMIT 1
user avatar
0 votes

MySQL COUNT with GROUP BY shows multiple items

Something like SELECT activities.id, COUNT(1) GROUP BY activities.id Since you aren't giving it content in your select worth grouping by you should have no reason to group by anything at all. Your ...
user avatar
0 votes

Mysql - Table structure to hold history log without any primary key

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows. In other words ,the reason for auto_increment is to ensure uniqueness of the rows , so you do not need to worry ...
user avatar
0 votes

Nested aggregation and grouping on multiple columns in MySQL

I tried to run the one by @oNare but I think it is not robust because if I change min to max, it outputs the wrong vendor. My approach is to join two tables, table_date and table_price. The table_date ...
user avatar
  • 1
1 vote
Accepted

MySQL 8 trailing spaces being evaluated in equals comparison

MySQL collations have a pad attribute, which has a value of PAD SPACE or NO PAD Reference For nonbinary strings (CHAR, VARCHAR, and TEXT values), the string collation pad attribute determines ...
user avatar
1 vote

MySQL 8 trailing spaces being evaluated in equals comparison

I've been wondering about this. Perhaps this explains it (from the changelog for 8.0.1): ----- 2017-04-10 8.0.1 Development Milestone -- Character Set Support -- ----- The pad attribute for Unicode ...
user avatar
  • 68.2k
0 votes

phpmyadmin schema dump, how to avoid alter table for key definitions?

I, too, wish the dump had the entire table definition in one spot. To get the entire definition afterwards, do SHOW CREATE TABLE tablename Such is often very helpful in programming questions about ...
user avatar
  • 68.2k
0 votes

Import 5gb sql file into MYSQL database

i increased connect_timeout to 120 and it helped to import big file
user avatar
1 vote

What are the possible downsides or consequences of using ALGORITHM=INPLACE, LOCK=NONE when running alters on a table?

Some drawbacks of INPLACE algorithm are: Long-running online DDL operations can cause replication lag in slaves. Online DDL operation must finish running on the master before it is run on the slave. ...
user avatar
-1 votes

How to make sure that mysqldump command is working in command prompt

Please add C:\xampp\mysql\bin to this path at your user variables and system variables; if you are using a wamp server, add C:\wamp64\bin\mysql\mysql8.0.27\bin to this path at your user variables and ...
user avatar
  • 1
2 votes
Accepted

InnoDB buffer getting higher every day. How to flush the buffer pool?

That's normal. The buffer_pool is a "cache". Blocks (16KB each) are loaded as needed from disk. Modified blocks are eventually written back to disk and left in the buffer_pool. A typical ...
user avatar
  • 68.2k
1 vote
Accepted

LIKE query with leading wildcards in large MySQL production database

Approach 1: A fulltext index can't do everything a LIKE condition can do. The most common problem I see preventing use of fulltext is that MySQL's implementation only indexes whole words, so if your ...
user avatar
  • 9,373
0 votes

count of a value from different column by column wise count

You can use conditional aggregation SELECT COUNT(CASE WHEN A = 1 THEN 1 END) AS aCount, COUNT(CASE WHEN B = 1 THEN 1 END) AS bCount, COUNT(CASE WHEN C = 1 THEN 1 END) AS cCount FROM YourTable
user avatar
  • 5,214
0 votes

count of a value from different column by column wise count

Let's use a string trick. Try this: SELECT LENGTH("0,1,1") - LENGTH(REPLACE("0,1,1", "1", "")); It returns 2. So: SELECT LENGTH(A) - LENGTH(REPLACE(A, "1&...
user avatar
  • 68.2k
0 votes

SELECT only IS NOT NULL columns with LEFT JOIN

it's better to use INNER JOIN instead LEFT JOIN, but if you don't want to change your query, you can add this at the end of your query: WHERE p.product_id = 1 AND m.material_label IS NOT NULL AND v....
user avatar
0 votes

What should be the max_connection for 48 core PROCESSORS and 256 gb RAM?

Start with max_connections = 2000. Keep an eye on the STATUS Max_used_connections for a week. If that approaches max_connections, then raise the limit. If not, leave it at 2000 and move on to other ...
user avatar
  • 68.2k
1 vote

Max allowed packet changed still replication not starting

This error is usually caused by a hardware glitch or a bug so obscure that it hasn't been found in over a decade. The optimal solution that will definitely fix it without any risk of anomalies ...
user avatar
0 votes

Database becoming unresponsive after time (4 to 24 hours)

I have probably found the cause and it's a weird edge case. I have the cts (from proxmox) running on lvm instead of lvm-thin. And had a backup job using snapshot for the database every 4 hours. My ...
user avatar
0 votes

I'm getting an error when I ran mysql_secure_installation

Please follow below steps to fix the error. Login as a root user without using any password sudo mysql Then use the following command to change the authentication parameter: ALTER USER 'root'@'...
user avatar
1 vote

Improving a MySQL query that result in a Lock wait timeout exceeded; try restarting transaction

Sargable If the datatype of executed is DATE, then remove the function call. If it is TIMESTAMP or DATETIME, then change the test to: executed >= UTC_DATE() AND executed < UTC_DATE() + ...
user avatar
  • 68.2k
0 votes

Organize IDB tables in subfolders in MySQL data folder

Multiple tables and is bad for maintenance and code and MySQL. You should not be touching .ibd files in the file system. There is nothing you can do with the files that would not risk damaging the ...
user avatar
  • 68.2k
0 votes

SQL Syntax Error when using Group by conditional expression

You are in a stored procedure? You have the tools there to "construct" the query. Use the proc's IF statement or function to help you decide what to CONCAT together to make the query. SET @...
user avatar
  • 68.2k
0 votes

MySQL RDS: Drop formerly huge table without freezing DB?

It's true that the InnoDB buffer pool may still contain pages for the table you want to drop. When you drop a table, MySQL has a tendency to "lock" the buffer pool as it scans for pages ...
user avatar
  • 9,373
0 votes
Accepted

Auto assign value-message to rows in a table with MySQL trigger

In MySQL backticks are used for table and column identifiers and are necessary when the identifier is a MySQL reserved keyword or when the identifier contains whitespace characters, even though it is ...
user avatar
0 votes

how to execute mysql queries concurrently/parallelly

We upgraded to MySQL 8.0. I think it handles parallelism better than 5.5 I ran these queries in parallel from 10 different sessions and execution time came down to 2.4hrs from 11hrs.
user avatar
0 votes

Inconsistent results with MySQL, query never ends on MariaDB

Indexes needed: table1: INDEX(location_id, organization_id, status, id) link1: PRIMARY KEY(table2_id, table1_id) link1: INDEX (table1_id, table2_id) LEFT JOIN ... ON EXISTS (...) is a very ...
user avatar
  • 68.2k
2 votes
Accepted

Why is the query planner only using one column of two in the index?

Please provide SHOW CREATE TABLE; it is more descriptive than DESCRIBE. Add INDEX(archive, col1), it may help performance. key_len=5 for a 4-byte INT means that it is NULLable; nothing else. (key_len ...
user avatar
  • 68.2k
0 votes

Could MySQL perform better on a 10GB network storage with raid vs local HDD?

A Hardware RAID controller with Battery Backed Write Cache makes nearly all writes take essentially zero time. If your app is reporting on Data Warehouse data, the I/O decreases dramatically if you ...
user avatar
  • 68.2k
0 votes

how to execute mysql queries concurrently/parallelly

If you are not using that ENGINE=INNODB, say so; this answer is totally wrong. Regardless of how you run it, the same amount of data must be read from disk. That is why parallelism is useless, at ...
user avatar
  • 68.2k
0 votes

MySQL RDS: Drop formerly huge table without freezing DB?

This is a classic problem in any environment (cloud, VMWare, bare-metal) What you need to do you are already doing: Delete the rows in the table in chunks but you need to delete all the rows. Why ??? ...
user avatar
-1 votes

very simple amateur question. Build a table then do group_concat

You want to learn about WITH. You can combine the two queries by adding WITH appQuote as (<select statement from your first script>) <select statement from your second script>. Whenever ...
user avatar
0 votes

Is it possible to run multiple versions of MySQL Workbench?

Download an older/archive version MySQL Workbench from the official site https://downloads.mysql.com/archives/workbench/ looking for the ZIP Archive / ..._noinstall.zip option. Unzip near the current ...
user avatar
0 votes
Accepted

MySQL 8.0 Migrating Roles from one server to Another

mysqldump dumps users and roles as INSERT statements to populate data in the system tables in the mysql schema. So to dump these, you would use: mysqldump mysql The new tool mysqlpump has an option ...
user avatar
  • 9,373
2 votes
Accepted

hierarchical data best practice in relational database

As with all denormalization decisions, it boils down to two things: How important is it to optimize the query performance? If the query with the JOIN provides satisfactory performance, then use that, ...
user avatar
  • 9,373
0 votes

How to recover deleted records by mistake in Mysql

For InnoDB tables, if binary logs are enabled, one could use: mysqlbinlog path_to_binary_log_file > query_log.sql If binary logs are not enabled, there is a recovery tool provided by Percona which ...
user avatar
  • 101
1 vote

How to deal with invoices that reference products that don't exist any more?

1: What is the best way to store data of a product in invoice? Is it by bringing the id of the product, or by bringing all info related to the product? Actually is it a good practice to store id of ...
user avatar
0 votes

Is it possible to remove a JSON array element by value in MySQL?

I had a syntax error with the above query, but it really helped me sort it out. This worked for me. UPDATE role AS r SET permissions = JSON_REMOVE(r.permissions, JSON_UNQUOTE(JSON_search(r....
user avatar
  • 101
4 votes

Are enums still evil (in MySQL 8.0)?

Nothing has changed between MySQL 5.5 and 8.0 to make ENUMs any more or less evil than they were before. I think calling it "evil" is hyperbolic. A programming language feature isn't evil. ...
user avatar
  • 9,373
1 vote
Accepted

MySQL information.schema.tables shows create_time later than update_time?

You are right, it shouldn't. Unfortunately, I strongly feel there is a bug. I created a test in the Windows version of MySQL 8.0.23 TEST CODE SHOW GLOBAL VARIABLES LIKE '%version%'; DROP DATABASE IF ...
user avatar
2 votes

Are enums still evil (in MySQL 8.0)?

Unfortunately, I am one of those who cringe at the sight of blood, uh ... ENUMs. I referenced that same article 10.5 years ago : Advantages and Disadvantages to using ENUM vs Integer types? My only ...
user avatar
1 vote
Accepted

Json extract of ['a','b','c']

SELECT * FROM mytable WHERE JSON_CONTAINS('["a","b","c"]', JSON_QUOTE(a_column)); I assume that the column a_column is a string-type column which contains a value like '...
user avatar
  • 17k
0 votes
Accepted

SQL Syntax Error when using Group by conditional expression

Your parenthesis are wrong. The wrapped construction is not single scalar expression. You cannot use ASC/DESC modifier as a subpart of the expression. If txn.id is numeric then use simple ORDER BY ...
user avatar
  • 17k
0 votes

SQL Syntax Error when using Group by conditional expression

Where is tnx_order coming from? You have additional parenthesise and you can’t return asc or desc within the case statement. You might want ORDER BY CASE WHEN tnx_order THEN txn.id ELSE NULL END ASC , ...
user avatar
  • 2,368
-2 votes

Why is the query planner only using one column of two in the index?

Right now you have no index on col1 even though that's the first item in your where clause. You should definitely create an index on the aggregate of col1, archive and created_at. This should solve ...
user avatar
  • 107
-2 votes

MySql Workbench csv data import error

Save your csv file as MS DOS instead of utf-8.
user avatar
1 vote

Could MySQL perform better on a 10GB network storage with raid vs local HDD?

You won’t get close to SSD speeds no matter how you set up HDDs (well, unless you give them a cache). That said, the impact on your application performance is going to be against your IO time only. If ...
user avatar
  • 2,368
-2 votes

Mysql service down with [ERROR] InnoDB: Page log sequence number is in the future

This issue will usually happen after one of the following changes: If the InnoDB log files were deleted and one or more of these events preceded that deletion (none of the events can on their own ...
user avatar
0 votes

Noob SQL question - Need to pull where condition is TRUE on one row but never FALSE on another row

I recommend this schema design pattern. I call it History + Current. The History table contains many rows per client; one per contract. The Current table contains only "current" info about ...
user avatar
  • 68.2k
0 votes

Noob SQL question - Need to pull where condition is TRUE on one row but never FALSE on another row

It may not be an elegant solution, but you could use two selects here and a ROW_NUMBER() function in each partitioned by the client, ordered by the action date. One query would be reinstating, the ...
user avatar

Top 50 recent answers are included