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
3 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 ...
0 votes
1 answer
329 views

How to test MySQL master-slave replication latency and get the exact time of slave library latency (accurate to milliseconds)

How to test MySQL master-slave replication latency, I need to get the exact time of slave library latency (accurate to milliseconds)
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 ...
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 | ...
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 ...
0 votes
1 answer
30 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 ...
-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 ...
1 vote
1 answer
2k views

MySQL/Pivot table for two tables

I was trying to get multiple rows into a single row with dynamic columns created as per the data. I have two tables combined with foreign key. Table 1: | id | name | Invoice value | invoice_date | |-...
1 vote
1 answer
11 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. ...
2 votes
3 answers
852 views

Maximum possible memory usage: 3.2G (163.34% of installed RAM)

I get frequent database crashes... explainable since mysqltuner tells me mysql has an appetite for memory. Since I'm not a database expert I hope you can point me in the right direction where to look ...
1 vote
1 answer
391 views

MySQL data warehouse calculate difference over years in percentage

I'm trying to create a query to calculate the percentage difference based on a time range (e.g. WHERE dim_date.year BETWEEN 2009 AND 2011) spreaded on months. My question is how to add the new ...
0 votes
2 answers
262 views

Is query executed when mysql server has gone away?

How can I be sure that a query was not executed? For example, I try to execute update `table` set `a`='b' and got the error 2006 server has gone away
0 votes
1 answer
1k views

Why do I have to set MySQL user password with mysql_native_password to connect with Workbench?

I've noticed if I set the mysql user's password like below, I cannot connect with MySQL Workbench. ALTER USER 'root'@'localhost' IDENTIFIED BY 'myPassword' If I use this below I can connect to it: ...
3 votes
1 answer
249 views

How to prevent MariaDB mysqldump from prepending database name in CREATE VIEW?

I am having the exact same problem as this person here: How can I prevent mysqldump from prepending the database name in CREATE VIEW? I am using MariaDB 10.4.7 x64 on Windows. For just ONE of my ...
0 votes
1 answer
35 views

very simple amateur question. Build a table then do group_concat

I am very new to SQL and don't have much intuition for all this. I have lots of coding experience but nothing in databases. I have two scriptthat work in isolation, but I don't know how to stitch ...
2 votes
2 answers
2k views

Importing a large mysql dump while replication is running

So we have a simple master/slave mysql replication running between two CentOS Servers. The master has multiple databases. eg.. Database1 Database2 Database3 The issue is we have a mysql dumpfile ...
0 votes
4 answers
3k views

Is it possible to run multiple versions of MySQL Workbench?

I'm doing an application migration from MySQL 5.5 to workbench v8... 5.5 supports MySQL Workbench up to version 6.3... However, MySQL server 5.5 only supports up to version 6.3 of the workbench. I ...
1 vote
3 answers
155 views

Why does MySQL require two or more redo log files for InnoDB?

A single redo log file can also be written in a circular fashion. In fact, MariaDB has changed its InnoDB redo log to a single file, and its developers claimed that "the log file was ...
7 votes
1 answer
1k views

RDS MYSQL instance rebooted and won't load INNODB table

So my production db in RDS apparently either crashed or was rebooted during a version update, and upon being restarted mysql couldn't load one of the innodb tables: as evidenced by the following error ...
0 votes
1 answer
124 views

MySQL: how to implement a text search without delimiter

I have a table table1 that contains several fields, some textual and other non-textual, on which I do search queries. Let's focus on some text fields, which I simply call field1, field2, field3. The ...
0 votes
2 answers
3k views

MySQL 5.1.73 change default log location

How do you change the default location of the log_error file? If you point it to a new path, will it automatically move the existing file? Or will it create a new file, and the original file can be ...
0 votes
1 answer
118 views

MySQL client goes down after using setPrimaryInstance() in InnoDB clustering, but the cluster remains up with all nodes functioning

So, I have a cluster of 3 nodes, each in a different data center located in the same city. My architecture is a single primary one where one node (let's call it Node 1) is primary and the other two ...
1 vote
2 answers
2k views

Time series data - question for daily aggregation query

I'm trying to query a MySQL database with hourly timeseries data but I'm stuck with (daily) aggregation. My raw data: mysql> SELECT * FROM emoncms.custom_registers -> WHERE timestamp >=...
2 votes
1 answer
3k views

Access denied error in trying to connect to a MySQL db in Python

I am trying to write a script that connects to a server, then connects to a MySQL db (which I currently can do via Navicat - so I know my username and password for the MySQL connection are correct). ...
1 vote
1 answer
1k views

MySQL/MariaDB connection in asp code encodes wrongly charset utf8 at insert or update in data base

I can't get a simple update or insert to save special äöü characters. Instead they are being stored as äöü. The website displays äöü characters correctly if they are already stored as äöü in the ...
-2 votes
0 answers
15 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 ...
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 ...
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 ...
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 ...
2 votes
1 answer
2k views

Use another disk for "optimize table" temporary file

I have a large table (70 Go) on a disk with only 9 Go free space, which I need to optimize ("optimize table .."). I have changed the "tmpdir" parameter so that the temporary file is created on ...
1 vote
1 answer
18 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 ...
0 votes
1 answer
837 views

Create triggers on slave only in mysql replication with a slave with different schema

I have a mysql replication setup with one master reporting to one slave. The schema on the slave is the same than the master but for each table I have an additional synchronized column only on the ...
2 votes
2 answers
14k views

How to recover deleted records by mistake in Mysql

I am using Mysql.And by mistake I deleted very important info.Is there a way to recover or even to see the deleted info over a month ago?
2 votes
1 answer
499 views

MySQL using variable in SELECT command with view affecting performance

sorry if this has been asked already. I've just moved from MySQL 5.6 to 5.7.17 and I'm struggling to understand this issue. I have a view "delete_me_test_" which has around 26k rows. SELECT ...
0 votes
0 answers
9 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 ...
0 votes
1 answer
1k views

How to load data from another sql file into a main sql file? - MYSQL

I am trying to process the scripts dbcreate_f.sql and dbload_f.sql to create and to load data into a script solution6.sql. I am doing this in MYSQL command line. In solution6.sql I have two statements:...
3 votes
1 answer
2k views

(Got an error reading communication packets) but no error on client

i've search a lot about this thread without results. we don't know how to resolve this: DB : mariaDB SERVER: Microsoft Azure enviroment: Nodejs with Knex (pool connections, min 0 max 300) our client ...
0 votes
1 answer
106 views

MySQL slave stuck on Applying batch of row changes (read)

Might be duplicate of: MySQL slave stuck on Applying batch of row changes (write) or MySQL8.0 Replication Got Stuck "Applying batch of row changes (delete)" I am trying to use MySQL 8.0.26 ...
1 vote
0 answers
44 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 ...
0 votes
1 answer
251 views

What are the pros and cons of adding secondary columns to a table's primary key?

In MySQL, I understand that with InnoDB the contents of the primary key get attached to the leaf node of every other index on the table. I have a photos table. Queries on it come in many different ...
1 vote
1 answer
2k views

MySQL - Problems caused by SQL Security definer or invoker

I have a view used to be defined as: > **CREATE > ALGORITHM = UNDEFINED > DEFINER = `user1`@`localhost` > SQL SECURITY DEFINER VIEW `schema1`.`view1` AS > select &...
1 vote
1 answer
660 views

Exported CSV from MySQL doesn't appear in specified directory

I'm trying to figure out the syntax to export the results of a MySQL query to the desktop as a CSV file via SSH. Here's what I'm trying: SELECT * INTO OUTFILE 'C:\Users\Jim Smith\Desktop\zyzyz.csv' ...
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 ...
0 votes
2 answers
1k views

MySQL duplicate entry error opon import to new server

I do have a DB on MySQL Galera 5.6.34 with a table that contains an index on ID,date. The table is fully functional and I have used repair and check to verify the table. However, upon backup/restore ...
1 vote
1 answer
34 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 ...
2 votes
2 answers
2k views

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

In MySQL, it's possible to remove an array element by index: SELECT JSON_REMOVE('["a", "b", "c"]', '$[1]') `result`; +------------+ | result | +------------+ | ["...
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 | ...
1 vote
2 answers
4k views

Query to retrieve index details for a particular column in all tables

There is a column created_at in most of tables. I would like to get the details if that field exists in the table and it is not indexed. Is there any thing can be done with the information_schema to ...
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 ...
0 votes
1 answer
383 views

Multiple files found for the same tablespace

how to fix this error? 2021-11-22T19:40:23.065929Z 0 [System] [MY-010116] [Server] c:\openserver\modules\database\MySQL-8.0-Win10\bin\mysqld.exe (mysqld 8.0.24) starting as process 4892 2021-11-22T19:...

1
2 3 4 5
426