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
58 views

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

I've checked the documentation, but couldn't find anything that could explain this. mysql> SELECT * FROM information_schema.`TABLES` WHERE table_schema='myschema' AND TABLE_NAME ='mytable' \G; *****...
user avatar
0 votes
1 answer
23 views

Organize IDB tables in subfolders in MySQL data folder

I can't seem to find the answer elsewhere, and i apologize if the question has been done before, but is it a way to force a MySQL server (community edition) to organize the idb files into subfolders ? ...
user avatar
  • 1
0 votes
0 answers
16 views

MYSQL -> SELECT, JOIN, GROUP BY to UPDATE

I get the data I need with the following select statement: SELECT tk.actiondate, tk.completedate, tk.closedate, MAX(com.date) AS realcomplete, MAX(clo.date) AS realclose FROM ticket tk LEFT JOIN ...
user avatar
0 votes
0 answers
12 views

why MySQL is trying to resolve '**ip**.client.mchsi.com'?

I have a mysql instance on Amazon RDS and recently I'm receiving this error message in: error/mysql-error-running.log. [Warning] [MY-010058] [Server] Hostname '<ip>.client.mchsi.com' does not ...
user avatar
  • 1
0 votes
3 answers
25 views

SQL Syntax Error when using Group by conditional expression

I'm trying to run SQL procedure with group by conditional expression ORDER BY CASE (WHEN tnx_order = TRUE THEN `txn`.id ASC) END, (WHEN tnx_order = FALSE THEN `txn`.id DESC) END ...
user avatar
0 votes
1 answer
12 views

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

My json is of the format ["a","b","c"]. I want to write a sql query such that select * from mytable where a_column in json_extract["a","b","c&...
user avatar
1 vote
1 answer
50 views

Inconsistent results with MySQL, query never ends on MariaDB

We have some really strange behaviour on MySQL (8.0.29) that we can't explain. We have 2 tables + 1 link table in-between, illustrated by this schema : We run a query whereby we need to fetch the id ...
user avatar
  • 107
1 vote
2 answers
51 views

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

I have a table on MySQL 5.7 with the following definition and indexes: +-------------------------+---------------+------+-----+---------+----------------+ | Field | Type | ...
user avatar
  • 130
0 votes
0 answers
25 views

What size of transactions are supported on MySQL InnoDB Cluster?

I was differentiating between the MySQL's Cluster offering, which are MySQL Innodb Cluster and MySQL NDB Cluster. I got an info about MySQL NDB Cluster, that it is good when the applications have ...
user avatar
1 vote
2 answers
36 views

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

Compared to using a HDD on local computer and do many MySQL read / writes on it, could it have more queries per second if I install 10GBe network cards and I store the MySQL data on a raid network ...
user avatar
  • 131
0 votes
3 answers
52 views

how to execute mysql queries concurrently/parallelly

I have to find out the "EXACT" row count of 5000 tables running MySQL 5.5 on RHEL5. As far as I know the only method is "SELECT COUNT(*) from table_name". But this is taking too ...
user avatar
0 votes
0 answers
12 views

MySQL enable GTID on Replica MySQL instance

We are currently using binary replication from our master to slave. Is it possible to enable the GTID on the slave only? (this is required from some other application reading from the replicas) Are ...
user avatar
  • 111
-1 votes
1 answer
18 views

Mysql Order By Json Count

I try that code; select * from `diary` where date(`created_at`) = 2022-05-10 order by JSON_LENGTH(like) asc limit 1 Like column in my table; +----+--------------+-------------------------+ | id | ...
user avatar
0 votes
3 answers
24 views

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

I'm querying contract data where clients have contracts that get termed and reinstated. Each row on the table has the Client ID, Effective Date, and Term date of each contract so clients can show up ...
user avatar
  • 1
0 votes
0 answers
25 views

Design Ideas for a pricing system that has different prices for different clients

I am attempting to redesign our current product and pricing structure at my company. Currently, we have products in a products table that has a client's SKU, to make it scan in our app, we associate ...
user avatar
0 votes
0 answers
33 views

Use of comments to define index relationship to another table

I am building a CRUD system using PHP and MySQL for custom applications I am working on. The question I have relates to the use of comments to define indexes from one table and return the required ...
user avatar
1 vote
1 answer
41 views

Sync Common Data into Multiple MySQL Databases

My Situation I have MySQL databases, each with the same schema (table structure and relationships exactly the same). The data in the Green database is shared by the other three. I have a nightly ...
user avatar
  • 113
0 votes
1 answer
31 views

php mysql aggregating/ combining queries to reduce db connections

I have a use case where a large number of clients are reporting their operational states every minute using an HTTP connection to a php script. For each client, I have a row in a mysql table with ...
user avatar
  • 101
0 votes
1 answer
16 views

MySql (local-Online) replication

I am trying to apply MySql replication between a local server (Master) and an online server (Slave) and need your help. ** All names (username – password – server name) are just an example and not the ...
user avatar
0 votes
1 answer
35 views

Running balance of a ledger with aggregated balances

Imagine I have a ledger where I write id, event_id, quantity, datetime and I want to get up to date balances of the quantity grouped by event_id. The goal is to get the aggregated quantity per ...
user avatar
  • 1
0 votes
0 answers
66 views

AWS RDS database replication replicates everything regardless my parameters group

I have a database in RDS that I want to replicate. I made a parameter group where I described which tables I want to be replicated. I applied it to my replica, and still it replicated the whole ...
user avatar
0 votes
0 answers
29 views

What is the best way of implementing expiration semantics on MySQL?

Presently I am using Redis as a data store, it all goes well until it doesn't namely we have records that suddenly disappear but there's no rhyme or reason we can find it and it's generally one offs. ...
user avatar
0 votes
1 answer
50 views

mysqld Shutdown every 1 hours

Please help me solve a problem which initiates a mysql shutdown every hour. Naturally the site on the server can't connect to the database and I get a bunch of errors in the site's logs. config: ...
user avatar
2 votes
1 answer
31 views

Change a value (just a flag 0/1) depending on an entry in another table and - important - leave everything else unchanged

I'm trying to replace a taxonomy entry with a flag. The fields are kept in different tables. Here's what I did so far on db<>fiddle: CREATE TABLE `job_postmeta` ( `meta_id` bigint(20) UNSIGNED ...
user avatar
  • 21
0 votes
1 answer
105 views

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

I have a Centos 7.9 server with MariaDB version 10.2.43. All of a sudden on May 2 2022, I have started receiving mysql down alerts. On checking I could see below log information in logs. Error 2:- ...
user avatar
1 vote
1 answer
29 views

Selecting a floating value yields incorrect results

Consider the following. CREATE TABLE IF NOT EXISTS `docs` ( `id` int(6) unsigned NOT NULL, `qty` float NOT NULL, `price` float NOT NULL, `rebate` float not null, PRIMARY KEY (`id`) ) DEFAULT ...
user avatar
0 votes
1 answer
29 views

Can database confirm the sequential read is really sequential in SSD

In SSD, overwriting a data file means SSD has to first erase it and then write data. This can change the data layout in the SSD for the data file. MySQL has some policies to optimize random IO, e.g., ...
user avatar
  • 83
1 vote
1 answer
31 views

mysql optimization for intensive schema-altering queries (DDL)

In the context of CI would like to massively parallelise the running of tests. The code under testing require a database to be present, so MySQL is used (as the application goes with that right now). ...
user avatar
0 votes
2 answers
30 views

Why linear read ahead (prefetch) may improve performance

In MySQL, Linear read-ahead is a technique that predicts what pages might be needed soon based on pages in the buffer pool being accessed sequentially... if you set innodb_read_ahead_threshold to 48, ...
user avatar
  • 83
1 vote
1 answer
17 views

dual-password-like feature for MySQL before v 8.0

tldr; Is there a dual-password-like feature for MySQL v.5.x ? Problem I have a MySQL server that needs to temporarily allow two passwords for one account, due to legal constraints that impose DBMS ...
user avatar
  • 13
0 votes
1 answer
33 views

Mysql 8.0 "ERROR 1030 (HY000): Got error 100" when adding index to very large table

I'm attempting to add an index to a very large table (2B rows) and the command fails after a few hours with: ERROR 1030 (HY000): Got error 100 - 'InnoDB error' from storage engine I'm finding the ...
user avatar
  • 3
1 vote
2 answers
79 views

how to downgrade mysql on Debian 10 from 8.029 to 8.02x

I have MySQL installed on my test server (Debian 10). I recently updated it to the newest version (8.0.29), but there are some issues with it so I want to go back to an older version (8.0.28). But I ...
user avatar
  • 1,920
-1 votes
1 answer
11 views

Getting 502 on join eloquent laravel

I need to get distinct value using inner join. Here is query SELECT distinct(ad_camp_sid.cid) from ad_camp_sid inner join ad_dsp_sites ON ad_dsp_sites.site_id = ad_camp_sid.sid ...
user avatar
1 vote
0 answers
40 views

Which MariaDB-Galera Node Should I Start After Cluster Crushing or Failing?

Sometimes my 3-Node GaleraCluster fails and doesn't serve. When I check I see all of them are activated but two of them are not working, so I check log files I see one of them could not operate an ...
user avatar
0 votes
0 answers
42 views

How to create correct mysql update query using window function for grouped items

We have a table that you could label as a summary table that we need to update every so often from another table that contains dated sales rows. How do you create a query, along with the index for it, ...
user avatar
  • 146
0 votes
1 answer
22 views

Check corrupt mysql Database table

I am trying to check for corrupt tables in mysql database while it is running. This check will be done in a service which will run continues. I was looking at mysqlcheck, but the documentations says ...
user avatar
0 votes
2 answers
47 views

mariadb: innodb_file_per_table=On and still have a huge ibdata1

I have a mariadb server for TEST on a Linux OS. I set innodb_file_per_table=1 in config file and restarted the database. I checked in Database and it was activated. I checked the ibdata1 file and it ...
user avatar
  • 155
1 vote
3 answers
59 views

slow mysql query need help

I want to retrieve count of unread message for each phone number from the table user_message. Below mentioned query takes 2996 ms to give result which is super slow. Extra Info of table: table ...
user avatar
0 votes
0 answers
29 views

A INSERT SQL is MySQL use memory too much, and mysql oom

when MySQL is running a insert data, the count of number is about 20w, and is about 100M but the memory useage is about more than 4G *************************** 1. row *************************** ...
user avatar
0 votes
0 answers
28 views

Optional int column for mysql table with 3m rows?

I have 3m rows and 20k of them need to have int value of column "y". Should I make new column "y" with default NULL or 0 or making mess with new table and joining? I've already ...
user avatar
  • 177
0 votes
1 answer
40 views

MySQL Indexes on all column combinations

I have a table that basically represents the log of records CREATE TABLE records ( id varchar(36), -- uuid text blob, user_id bigint, cluster_id bigint, status_id ...
user avatar
0 votes
0 answers
26 views

How do I connect to mysql database with password

Using PHP 7.4.27 (now using 7.4.29) with MariaDB in xampp I can access everything using phpmyadmin. I am using the following on my localhost $con = new mysqli($host, $user, $pwd, $db); I can connect ...
user avatar
-1 votes
1 answer
45 views

How does Two Phase Locking know when to release locks?

I have been doing some reading up on Two Phase Locking and understand that it involved 2 phases: 1. Growing phase: locks are acquired gradually and no locks are released. 2. Shrinking phase: locks ...
user avatar
  • 1
0 votes
0 answers
63 views

How to understand and decrease Innodb_rows_read.avg (RDS INNODB)?

I am looking to understand what's behind Innodb_rows_read value and how to decrease it. For some unknown reasons my RDS server has been over 90% of CPU for more than 5 days and after investigation, ...
user avatar
  • 109
6 votes
5 answers
1k views

Optimize MYSQL Select query in large table

Given the table: CREATE TABLE `sample` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `vendorid` VARCHAR(45) NOT NULL, `year` INT(10) NOT NULL, `title` TEXT NOT NULL, `...
user avatar
  • 163
1 vote
1 answer
29 views

Create new MySql user with same grants as existing user

To create a new user usr_B with same grants as an existing user usr_A we can always SHOW GRANTS for usr_A and then GRANT those privileges to usr_B, but is there a built-in command for this purpose?
user avatar
  • 245
0 votes
1 answer
23 views

How do MySQL-Workbench and mysqldump return dumps with vastly different file-sizes?

I observed that the dump of my DB done "by hand" in the CLI is a lot bigger (>800MB) than the one produced by the Workbench (v8.0) (<500MB) and I am trying to figure out why that's the ...
user avatar
  • 113
1 vote
1 answer
48 views

Mysql.innodb_table_stats & mysql.innodb_index_stats not updating

I believe I have persitent stats configured correctly and there are records in INFORMATION_SCHEMA.STATISTICS table but the following tables have no data for many of the tables/indexes. mysql....
user avatar
  • 51
1 vote
5 answers
119 views

Optimising a query for matching multiple values on a related table

I am trying to optimise a query for fetching all game IDs where certain units (Archer, Cavalry, etc.) were present. I am using the players table, which contains the game_id, and the units table, which ...
user avatar
  • 113
0 votes
2 answers
27 views

comparing a date value in one table against multiple values in another to categorise by age

I have created a form which allows users to enter multiple, unlimited month number values and label them to use against inventory to categorise the age of each item. Table 1 - AgeConfig - Manually ...
user avatar