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
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....

1 3 4 5 6 7 366