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.
18,291
questions
1
vote
0answers
17 views
Deduplicate table based on createdate
I'm new to SQL and I'm trying to get my head round a lot of stuff but so far with little success. So I'm trying to get some help, here goes
I have a table "Userdata"
Email | FirstName | LastName | ...
0
votes
0answers
17 views
fixing broken convert_tz() in Mysql?
I come across the documentation for convert_tz() in MySQL and it says
If the value falls out of the supported range of the TIMESTAMP type when converted from from_tz to UTC, no conversion occurs. ...
0
votes
2answers
23 views
How to select Overdue Rows with Date Frequencies?
+------------------------+--------+
| Invoice_id | due_date | amount |
+-------------+----------+--------+
| 20 |2020-01-18| 1250 |
+-------------+----------+--------+
| 21 |2020-...
0
votes
1answer
14 views
MYSQL - Run a query based on the results of a query
Trying to do some cleanup in the database, per manager request. However, I can't seem to get Mysql to run the inner query as written.
select @tbl:=column_name as'Table',
( select max(length(@...
0
votes
2answers
22 views
How does MySQL skip ranges (nodes) in a loose index scan?
I've read the MySQL loose index scan, and it makes sense as optimization, however, when using an example B+tree, I don't understand how nodes can be efficiently skipped, due to potential matches ...
0
votes
1answer
22 views
MySQL query help
I need a query that returns the number of liked messages and orders them by the number of likes (from most to least).
I have tried:
SELECT users.user_id,
messages.author,
messages....
3
votes
1answer
43 views
Why mysql throws “invalid default value for field” only when two or more timestamp fields exist in the create statement?
In mysql 5.7.28 I create a table like this:
create table t1 (
id int not null,
d1 timestamp)
engine=innodb;
which works fine and creates d1 as non-null with default value current_timetamp,on update ...
0
votes
1answer
39 views
How to normalize a lookup table containing NULL? - or should I bother?
I have a table fee listing different fee descriptions and another table fee_amount holding the amount of the fee. The fee amount is different for each year, whether it's a joint fee or not and what ...
0
votes
1answer
19 views
Is there an option to turn existing global variant to constant in MySQL or MariaDB?
I'm trying to prevent changing some global variables which I set from the ini file.
For example:
server_audit_logging=ON
I want to prevent it from changing by:
SET GLOBAL server_audit_logging = ...
0
votes
2answers
26 views
Optimization: Increase Innodb_pool_size useful?
We run an AWS RDS instance with 8vCPUs and 64GB memory.
All tables are using InnoDB engine.
I have kept the default setup of innodb_pool_size to be half the memory.
What I see is that almost 30Gb of ...
0
votes
0answers
24 views
MySQL: InnoDB data dictionary has unknown flags
I tried to delete and recreate a MySQL service and when I tried to start the service again I can't start it and I'm getting this error in error logs:
InnoDB: Warning: table 'sampledb/table1' InnoDB: ...
0
votes
1answer
25 views
How to “properly” back up a MySQL/MariaDB database?
I have a small (<10GB) database backing an Intranet WSGI website. In the Apache logs I frequently see timeout errors whose timestamp coincides with the daily backup cron job. I'm running ...
0
votes
1answer
39 views
Why doesn't MySQL use indexes for subqueries?
This query takes forever to run (30+m - infinity).
select date,
sc,
( select count(fingerprint_id)
from stats
where hit_date >= t.date
and hit_date ...
0
votes
1answer
100 views
Understanding INSERT … ON DUPLICATE KEY UPDATE deadlock scenario
I'm trying to understand a scenario where an INSERT ... ON DUPLICATE KEY UPDATE statement is causing deadlocks under high concurrency.
The two tables in question:
hosts:
"CREATE TABLE `...
1
vote
1answer
24 views
Optimizing a query that gets the opening balance
I have a query like this
select org, `Balance` from oss_collection_history och2
where och2.`DateFrom` =
(select min(`DateFrom`) from oss_collection_history och3 where och3.org = och2.org)
It ...
0
votes
1answer
28 views
MariaDB auto increment problem
Using OpenSUSE leap 15.1
10.2.29-lp151.2.9.1-x86_64 from vendor OpenSUSE (installed)
CREATE TABLE testtab2(
my_id2 INT AUTO_INCREMENT,
name2 VARCHAR(10),
comment2 VARCHAR(20),
PRIMARY ...
0
votes
1answer
28 views
Why is `max_allowed_packet` shows 2048 even though it's set as 16M in mysqld.cnf
I've got the error related to max_allowed_packet:
[S1000] Packet for query is too large (2,127 > 2,048). You can change
this value on the server by setting the ‘max_allowed_packet’ variable.
So ...
0
votes
1answer
20 views
Identify when Table Schemas were last Modified
I'm trying to identify all tables that have had indices added/altered since January 15. Is this possible?
I've run:
select * from INFORMATION_SCHEMA.PARTITIONS where UPDATE_TIME >= '2019-01-15'
...
0
votes
1answer
81 views
MySQL - [Note] Got an error reading communication packets
I have deployed MySQL on OpenShift (kubernetes) with GlusterFS as persistent storage and i am getting following error after every 2 seconds in MySQL Container logs
2020-01-17T18:00:04.787586Z 1541 [...
0
votes
2answers
35 views
How to force mysqldump to produce /*!40101 SET character_set_client = utf8mb4 */;
I am attempting to dump my database, which is set to, and only contains, utf8mb4 and appropriate collations. I use the following line:
mysqldump --add-drop-table --add-locks --allow-keywords --...
0
votes
1answer
45 views
Mariadb is not starting
Please help me with this issue MariaDB not starting. Its a production server so not able to reinstall and clean the data at all.
(This command executed by me)
systemctl -l status mariadb.service
...
0
votes
1answer
52 views
MySQL database not loading table data
My centOS 7 server had an unexpected shutdown. For some reason one of the mysql database shows that all tables are empty, but if I browse the files in /var/lib/mysq/database I can see the files(.frm ...
0
votes
2answers
24 views
MySql replication failed with error 1236 data position > file size with relay-bin file not increasing
My slave machine reset and now on SLAVE SHOW STATUS\G I have the error:
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start ...
0
votes
0answers
19 views
How to use MariaDB server_audit plugin on MySQL
I need to log connections on MySQL on Windows.
When I google it, I saw some advice to use MariaDB server_audit plugin on MySQL. (for example: here)
So, I gave it a try, but I failed.
I copied the ...
0
votes
0answers
26 views
Should I use two indexes? [duplicate]
I would like to create some tables (including users table and products table) inside MySql Database with those index for each tables:
Private Index (uses int), for relational table purpose only
...
0
votes
1answer
111 views
What is wrong with this query? I am getting syntax errors while running this query
Consider a table employee storing the details of the employee.
ssn : social security number of the employee
address : storing the address of the employee
Looking at the address field in the ...
0
votes
2answers
46 views
MySQL replica is lagging without resource saturation
I am running MySQL 5.6.41 on AWS RDS. I have one multi-AZ master and one multi-AZ read replica. It is a vanilla configuration MySQL configuration, so the binlog is in MIXED format and the replication ...
0
votes
1answer
31 views
Does ORDER BY COUNT(*) DESC suffer a penalty?
I understand that older versions of MySQL parse the following but has no effect:
INDEX (col DESC)
For the following example, does performance suffer and is there a way to overcome it?
SELECT col, ...
0
votes
1answer
54 views
Doubt about Database design (newbie)
I am learning about Database design, I have doubt about some database design conepts
I want to know which is better design between two design
Design 1
there is two tables:
Object and Field
...
0
votes
1answer
47 views
MySQL: I need to return rows according to certain conditions
I have a 3 tables: work, recording, release
1 work can have multiple recordings and 1 recording appears in only 1 release and 1 release can be in m recordings.
TABLE: work
+---------+-----------+
| ...
1
vote
2answers
38 views
Filtering down specific criteria for all rows and return only one row for each column with the same value
I have house_leases and house_lease_terms (see table schemas below). A house_lease can have multiple house_lease_terms however there can only be one "active" term at a time.
Table Definitions:
...
1
vote
1answer
32 views
How to count between two users unread messages in SQL
I am creating between two users unread message count script in SQL.
My script problem not counting between two users unread messages.
My script problem all users unread messages display as a same ...
-1
votes
2answers
27 views
How to count field with multiple values in MYSQL [closed]
I have a MySQL database and Table1 has a column that can get four distinct values e.g 1,2,3,4. Is it possible to create one query to count 4s and all the other values together?
For example
...
0
votes
1answer
20 views
Additional Select statement within Cursor terminates looping unless Select statement returns a results for all cursor rows
I have a simple database called 'held_toys' (a held toy is a toy which is not on sale yet) which is comprised of a primary key and a foreign key for a Toy. I have a second table called 'toys_on_sale' ...
1
vote
0answers
21 views
Set Maximum Tables In A Database [duplicate]
I need to set up a MySQL database with a maximum number of tables which can be created in it.
For example, if I have my maximum at 5 tables for a single database, and someone attempts to create a 6th ...
2
votes
1answer
30 views
Lost connection to MySQL server at 'handshake after truncating table
I have been using Heidi SQL's latest version through an SSH tunnel to truncate a 2.5GB table. When I clicked truncate MSQL lost the connection immediately and it's not starting up ever since then, no ...
1
vote
1answer
42 views
Can/Should rarely used columns be placed in a different table for performance?
I have a table that contains data (blobs). Data is often read, rarely inserted, and never modified.
For all of this data, I compute hashes that I also store in the DB. These hashes are only checked ...
1
vote
2answers
48 views
Is it better to have a CASE expression or a JOIN with a dimensional table?
I have a fact table which has a timestamp and I want to express the day of the week. I could do something like this:
SELECT CASE WHEN date_part(timestamp,'week') = 0 then 'Monday'
WHEN date_part(...
0
votes
1answer
23 views
How to split field and run sub query using it?
Given two tables T1 and T2 below, is there a way in MySQL 5.7.x to get such a list field from T1, so that none of its entries are present in T2?
T1.list is a regular VARCHAR, containing T2.vals ...
0
votes
2answers
60 views
How to select all columns plus one alias column in MySQL
It is a simple question, but i could not find the answer.
I want to select all the columns in my table with a SELECT * statement, but i also need to alias a UUID column this way: BIN_TO_UUID(ID) as ...
0
votes
1answer
28 views
Rotate MySQL log files
I want to daily rotate MySQL log files on Windows. I want to keep the files, I don't want to use tables instead.
There are 3 log files: general.log slow-queries.log and error.log
I can do it easily ...
0
votes
0answers
14 views
Tracing what went wrong with MySQL server?
I have a local in-house client application that relies on steel server MySQL 5.6 database in our closet. This morning, people suddenly couldn't log in. The client server said it couldn't connect ...
0
votes
1answer
41 views
Best strategy to correctly index complex join query
This is the query running on MySQL 5.7 with INNODB:
SELECT SQL_CALC_FOUND_ROWS
calendar.id_calendar AS calendar_id,
calendar.event_datetime AS calendar_event_datetime
FROM
users
INNER ...
0
votes
1answer
33 views
Filter the query result
I have one query by which I am getting duplicate ComapnyID and I want to filter that and remove duplicate ComapnyID but don't want change the order by report.
table 1 reports
Table 2 Company
Here ...
0
votes
0answers
14 views
I'm trying to fetch unique values from one column (registration_no) which has duplicate values in column(mobileno) and column(email)
This is my table - I want to get the unique registration_no's which have same mobileno, email and date_of_action.
For instance row no 8,9 and rows 10,11,12,13
0
votes
1answer
24 views
Generated DDL doesn't match with CREATE TABLE statement
I'm using MySQL with InnoDB engine. I create a new table that contains a foreign key, eg:
CREATE TABLE rooms (
id INTEGER NOT NULL AUTO_INCREMENT,
my_id VARCHAR(15),
house_id INTEGER,
...
0
votes
1answer
17 views
mismatch between server schema content and exported sql rows [duplicate]
I am using MySQL 5.6.44 within phpMyAdmin on Go Daddy shared hosting and experimenting with some duplicate table data.
I am trying to figure out, when I export my table from phpMyAdmin the exported ...
0
votes
0answers
17 views
(MySQL) Finding the first, second, …, nth movie directed by a group of directors [duplicate]
There's a table titled "movies". The table can be found here. (Please scroll down to the bottom of the page to see the table).
I want to find the list of first movies directed by each director in the ...
0
votes
0answers
19 views
Master slave replication - DATETIME columns not replicating the time
I have master which is mariadb 5.5 and slave mariadb 10.3, the replication works fine except for the DATETIME columns which are syncing the date part but not the time.
On master: LastLogin 2020-01-15 ...
0
votes
2answers
82 views
Why is this SELECT creating temp tables?
When I run the following SELECT, it takes quite long, typically 30 or 40 minutes (I'm on Debian, MySQL 5.5.62):
SELECT ga.currency, o.organisation_name, os.hostname, g.global_name,
SUM(IF(ga....
