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.
21,288
questions
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 ...
- 1
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)
CommunityBot
- 1
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 ...
- 68.1k
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 | ...
- 68.1k
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 ...
- 68.1k
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 ...
- 68.1k
-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
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 |
|-...
CommunityBot
- 1
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. ...
- 173k
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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
CommunityBot
- 1
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:
...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
- 1
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 ...
CommunityBot
- 1
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
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 >=...
CommunityBot
- 1
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).
...
CommunityBot
- 1
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 ...
CommunityBot
- 1
-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 ...
- 1
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 ...
- 101
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
...
- 2,872
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 ...
- 9,328
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 ...
CommunityBot
- 1
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 ...
- 9,328
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,872
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?
- 101
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
...
CommunityBot
- 1
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:...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
- 11
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 ...
CommunityBot
- 1
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
&...
CommunityBot
- 1
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'
...
CommunityBot
- 1
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
...
- 33
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 ...
CommunityBot
- 1
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,872
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 |
+------------+
| ["...
- 2,797
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 | ...
- 11
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 ...
CommunityBot
- 1
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 ...
- 21.2k
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:...
CommunityBot
- 1