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
0 answers
6 views

Make a request for each user to display the amount of the order for each date

There are three tables in the database. It is necessary to find the value of the ordered batch of products for each date by customers
user avatar
1 vote
1 answer
10 views

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

Im testing a trigger for an assignment where i need to set an specific value to each row on a table. I have 2 tables tbl_cursos and tbl_modalidad. After i insert an entry on tbl_cursos the trigger ...
user avatar
  • 11
1 vote
2 answers
15 views

MySQL RDS: Drop formerly huge table without freezing DB?

I am using MySQL 5.6 in AWS RDS. I want to drop a table that used to have 8 billion rows. Hearing that dropping a large table can freeze the database, I instead chose to delete the rows using a loop. ...
user avatar
-2 votes
0 answers
16 views

Import 5gb sql file into MYSQL database

i have exported sql file using mysql dump from a database and i am trying to import this big 5gb file into anohter mysql database. I am using mysql -u username -p -h hostname <file.sql command ...
user avatar
0 votes
0 answers
12 views

Generate MySQL/MariaDB SSL certificates from my wildcard certificate (*.mycompany.com)

I'm configuring a series of MariaDB servers with SSL protection. I was able to do it creating different self-signed certificates, as everybody can do just following some of the multiple tutorials you ...
user avatar
1 vote
1 answer
8 views

MySQL 8.0 Migrating Roles from one server to Another

Hi there in my company we are starting to implement MySQL 8.0 (because we are using AWS Serverless 2) and one of the "new features" that MySQL 8.0 that has is the "User Roles". My ...
user avatar
-1 votes
0 answers
25 views

Perhaps I've made a mistake about "ORDER BY" statement but I can't figure out

I want to show the summary order by the quantity of each group ascendingly then alphabetically but it's not working as expected. 🤔 [1] [1]: https://i.stack.imgur.com/SOCso.png ( SELECT ...
user avatar
  • 1
1 vote
1 answer
19 views

hierarchical data best practice in relational database

we have 1 table that also references itself in parent_id table high level structure: (let's assume that data is above 1M rows) category_table id int name varchar parent_id int the goal is to get ...
user avatar
  • 113
0 votes
0 answers
13 views

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

I have a table that tracks the action of my users (used for logging and debugging for their purpose). From time to time, I need to mark the data as treated by doing the following call : UPDATE actions ...
user avatar
  • 101
0 votes
0 answers
10 views

disable trigger command giving error - mysql

I have a master slave server configured(one master and one slave) in amazon ec2 ubuntu instances. below are the tables and the insert trigger is on the likes table. create table posts ( post_id ...
user avatar
0 votes
0 answers
15 views

SELECT only IS NOT NULL columns with LEFT JOIN

I have a query where i need to select product options from multi tables. SELECT p.option_id, p.product_id, p.sku, p.qty, p.price, c.color_label, s.size_label, m.material_label, v.variation_label ...
user avatar
  • 33
1 vote
0 answers
10 views

Replication between master and slave is running, but still there is a one row difference in a few tables when checked with pt-table-checksum

This is the master status mysql> show master status; +------------------+-----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | ...
user avatar
  • 11
1 vote
1 answer
37 views

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

I am developing an ERP using PHP and MYSQL. I have a product page and an invoice page. I am facing an issue, if I delete a product from products page, and I already created an invoice with this ...
user avatar
  • 11
0 votes
0 answers
9 views

Merge 2 database data with same schema but different items

I have 2 databases: CloudDB and LocalDB, turns out that ClouDB have more records in some tables and vice versa with LocalDB. I need to create third database where all the missing values are merged. I ...
user avatar
0 votes
0 answers
18 views

Design database in which a table structure depends on value of a column

I am designing a database which will hold results of some atomistic calculations and I face a design problem. Namely, in a table named physical_properties I have an ENUM field type_of_structure which ...
user avatar
0 votes
0 answers
17 views

could not delete index in MySQL 8.0.24

when I am using this command to delete index in MySQL 8.0.24: ALTER TABLE locker DROP INDEX name_uniq; the delete index process running for minites, but my table only have 6 rows. why could not ...
user avatar
  • 477
4 votes
2 answers
55 views

Are enums still evil (in MySQL 8.0)?

So I have a disagreement with a coworker about using enums vs lookup tables. This very old article from 2011 and MyQL5.5 still gets referenced again and again by those not liking enums. But we are ...
user avatar
1 vote
0 answers
46 views

Database becoming unresponsive after time (4 to 24 hours)

Since a couple of days my mariadb database randomly becomes unresponsive after random intervals (which have been between 4 and 20 hours). The database comes back to life when rebooting the ct. And ...
user avatar
1 vote
1 answer
44 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
0 answers
14 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
9 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
10 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
2 answers
19 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
9 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
41 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
  • 109
1 vote
2 answers
39 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
  • 129
0 votes
0 answers
21 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
27 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
2 answers
33 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
10 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
22 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
18 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
26 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
32 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
29 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
0 answers
15 views

How do you retrieve information from a MySQL database and post it on a text-area?

I have successfully created a MySQL database with the WAMP stack. I have created a web page that has a form that posts info gathered from a form into the database. What I need now is a way to create a ...
user avatar
0 votes
1 answer
25 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
57 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
25 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
42 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
1 vote
1 answer
22 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
  • 11
0 votes
1 answer
44 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
28 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
18 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
28 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
24 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
15 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
24 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
2 3 4 5
426