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
1 answer
18 views

MySQL thread states: update vs updating

This should be a simple question, but surprisingly, I can't find much about it: the difference in MySQL thread states update vs updating. The MySQL docs say: update: "The thread is getting ready ...
user avatar
  • 141
1 vote
1 answer
48 views

ERROR 1045 (28000) & access denied for user 'root'@'localhost'

UBUNTU 22.04.1, mysql ver 8.0.30 (1) I get sudo apt install mysql-server then sudo mysql_secure_installation Get in loop this error for each complicated passwords I tried: (contained: '<>&(*...
user avatar
  • 131
1 vote
3 answers
55 views

Forcing InnoDB to build the secondary index

When INSERTing, InnoDB defers the building of secondary indexes. For example, CREATE TABLE item_creators ( item_id int(11) unsigned NOT NULL, creator_id int(11) unsigned NOT NULL, INDEX(creator_id), ...
user avatar
  • 4,261
0 votes
0 answers
14 views

speeding up JOIN by LIKE or REGEXP

I have two tables of CREATE TABLE titles ( title_id int(11) unsigned NOT NULL AUTO_INCREMENT, title varchar(255), INDEX(title), PRIMARY KEY(title_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE ...
user avatar
  • 4,261
0 votes
1 answer
16 views

faster INSERT INTO InnoDB table with composite PRIMARY KEY

I have a simple tag_map CREATE TABLE tag_map ( article_id int(11) unsigned NOT NULL, tag_id int(11) unsigned NOT NULL, INDEX(tag_id), PRIMARY KEY(article_id,tag_id) ) ENGINE=InnoDB The table is quite ...
user avatar
  • 4,261
0 votes
1 answer
11 views

Alternative/Substitute to Partial index in MySQL?

MySQL does not allow partial index creation. How can you achieve partial indexing in My SQL without using triggers. For example: TABLE: STUDENT NAME STATUS XYZ FAIL XYZ FAIL XYZ PASS ABC PASS ...
user avatar
0 votes
1 answer
24 views

MySQL - Nested loops count too high resulting in query taking too long

I am seeing high response time for a MySQL query. I have two similar queries: SELECT `orders`.`id` FROM `orders` WHERE (`orders`.line_count = 0 OR `orders`.account_type_id IS NULL OR ...
user avatar
0 votes
1 answer
10 views

Inserting a "date-time" into TIMESTAMP column -- "Incorrect datetime value"

I have a date-time data that comes in this format: 2016-02-10T12:00:00.000Z I've created a MariaDb database and a column for that date of type TIMESTAMP However, when inserting the data into the ...
user avatar
  • 27
0 votes
0 answers
22 views

how to print message 'included' along with limit_1 value, when limit_1 is selected with condition

select max(Contract_Num) as contract_num, max(Lic_Plate_Num) as lic_plate_num, max(cast(Revision_Eff_Date as date)) as rev_eff_date, max(contract_name) as cn_name, ...
user avatar
1 vote
1 answer
15 views

Mysql point in time restore using event position from 100 binary log files

i stuck on one question! the question is that i hava a large database in production environment and i have a full, incremental backup like till 2pm and aftre 1 hour i suddenly run the "drop ...
user avatar
0 votes
1 answer
13 views

Error Code: 3548. There's no spatial reference system with SRID 1498173264 - MySQL

I am trying to run a mysql query below: select * from geo where st_distance_sphere(Point(-118.2842557, 34.0676834), `geometry`) <= 1609 where geometry is of type Polygon. and get an error: Error ...
user avatar
  • 129
0 votes
0 answers
9 views

Execute SQL file from S3 in MySQL RDS

I have a large (~10Gb) .sql file that I need to run against a MySQL RDS instance. I have placed the file in a bucket in S3. How can I execute that large file against my RDS instance? This is a one-off ...
user avatar
-1 votes
0 answers
25 views

Syntax Error in insert into statement

i don't seem to understand why I keep on getting a syntax error whenever i do insert something. I do check my database and it's the exact name as well as my declaration in my designer form con....
user avatar
0 votes
1 answer
21 views

MySQL Trigger to prevent update in one table if particular data is available in another table

I need a MySQL trigger that can prevent update of a particular column data, if some specific data is available in another table. I have two tables product and sales. Product Sale I need to prevent ...
user avatar
0 votes
0 answers
21 views

Mariadb equivalent to MySQL CONNECTION_CONTROL

MySQL introduced a security plugin library in version 5.7.17 including the plugin CONNECTION_CONTROL, which checks incoming connection attempts and adds a delay to server responses as a result of ...
user avatar
0 votes
1 answer
75 views

Problem with corrupted MariaDB database file

When the MariaDB database on my Docker container keeps restarting itself, I figured out that the problem is in my database (I think it's corrupted). root@website:~# docker-compose ps Name ...
user avatar
1 vote
0 answers
19 views

Finding sub-groups by relationships within groups

In this example, two tables define the groups and the relationship of persons within each group CREATE TABLE groups ( group_id int(11) unsigned NOT NULL, person_id int(11) unsigned NOT NULL, INDEX(...
user avatar
  • 4,261
-1 votes
1 answer
42 views

SQL Multiple CASE conditions with SUM and count Functions along with JOIN Condition

Kindly help me with the right syntax of mine, really appreciate it, if you could just edit my existing syntax into correct one without alternate options. thanks in advance Table schema CREATE TABLE ...
user avatar
-1 votes
2 answers
14 views

Is it possible to save the password in .my.cnf or similiar on Windows for mysql?

Is it possible to save the password in .my.cnf or similiar on Windows for mysql?
user avatar
0 votes
0 answers
38 views

ERROR 1045 (28000): Access denied (after import a complete dump)

This one got my head scratching... I was going to set up replication (the server is running 8.0.26), so I fire up a was EC2 VM and installed mysql-server (8.0.30) from the official MySQL repo. After ...
user avatar
-1 votes
1 answer
54 views

MS SQL Server Express alternatives, choosing between Relational-DB / NoSQL [closed]

This question relates to choosing the best database storing data on a pc running windows. The pc is part of a commercial product which generates a big amount of data over time. Data consists of Test ...
user avatar
  • 177
-1 votes
0 answers
27 views

Many to many from another many to many relationship

I have a course planning project where a curriculum has many subjects (called a curriculum framework), and subjects can belong to many curriculums. This works perfectly for graduate courses. But in ...
user avatar
0 votes
0 answers
9 views

SQLcode in event is not working

I am trying to make a summary table of another table with an event that runs every hour in mySQL (it is a mySQL database of my website). If i just run my code in the query box it works. And if i add ...
user avatar
0 votes
1 answer
16 views

select row matching exact list of uids

Given table below id | groupid | memberid 1 | g1 | m1 2 | g1 | m2 3 | g2 | m1 4 | g2 | m2 5 | g2 | m3 I'm trying to build a query where I want to get the groupid ...
user avatar
  • 1
0 votes
0 answers
19 views

My sql does not start on Linux

I have a problem that mysql doesn't start anymore although nothing has been changed. The following error message appears when starting mysql. This is the Error-log: To report this bug, see https://...
user avatar
0 votes
1 answer
12 views

How can I remove binlog files after disabling bin-logging in mysql 8

In mysql 8, what is a recommended and safe way of removing old bin logs other than with "PURGE BINARY LOGS..." After disabling binary logging on mysql (with skip-log-bin) I have 500GB of ...
user avatar
0 votes
1 answer
26 views

What version of Windows does MySQL 5.5 support

As the question suggest I need to know what versions of Windows Server will MySQL 5.5 run on. I have looked online but cannot find much for 5.5. The version 5.7 seems to be run on Windows Server 2016 ...
user avatar
0 votes
1 answer
18 views

Issue with table size after upgrading to MySQL 8

I have two MySQL servers. One is running on 5.7.24 and other one is running on 8.0.29 version. In 5.7.24 I have table with 71M (millions) records and its size is about 6GB. The same table on 8.0.29 ...
user avatar
1 vote
2 answers
52 views

Why MySQL 5.7 timestamp not null requires default value?

When I try to create a table with non null timestamp, it requires me to add a default timestamp to it or use the implicit default. But I am not sure why this is necessary. Have a look at https://...
user avatar
  • 111
1 vote
0 answers
33 views

What transaction isolation level prevents MariaDB from locking tables during a join?

I am using MariaDB 10.5.12. My query will join the tbl_cus table to a previous view, creating a new view. I am using MySQL Workbench with auto-commit enabled. During testing, the app connected to the ...
user avatar
0 votes
1 answer
70 views

MariaDB 10.5 slower than mysql 5.5

Since i moved from mysql 5.5 to mariadb 10.5 queries much slower even with many different tuning config and new server is ssd but old mysql was faster did anyone has such experience ? even with simple ...
user avatar
0 votes
0 answers
19 views

How to determine server with most recent transactions when re-starting MySQL Group Replications?

We're implementing our first database cluster with MySQL Group Replication, and are setting up the procedure for handling the re-start if all member servers of the group fail due to something like a ...
user avatar
0 votes
1 answer
9 views

Select rows that are not present in the table based on criteria

I have two tables student table and fee table: First Table (Student): std_id std_name 1 A 2 B 3 C 4 D 5 E 6 F Second Table(Fee): fid std_id amount Month 1 1 500 2022-07 2 2 500 2022-07 3 4 ...
user avatar
0 votes
2 answers
63 views

What is the bottleneck in SELECT from InnoDB tables?

For an InnoDB table with 500 million rows (on a separate NVMe drive), SELECT COUNT(*) takes about 3 min. SHOW ENGINE INNODB STATUS\G shows ROW OPERATIONS of about 2M reads/s, which is consistent with ...
user avatar
  • 4,261
0 votes
2 answers
21 views

Is it possible to create a MySQL partition and then create a subpartition by another column?

I read the docs about subpartitioning, but I think it doesn't cover what I'm trying to achieve. https://dev.mysql.com/doc/refman/5.7/en/partitioning-subpartitions.html We have this table: CREATE TABLE ...
user avatar
  • 1
1 vote
0 answers
46 views

MariaDB Server crashes when performing mysqlcheck

I'm checking my databases and I'm using this command : mysqlcheck --all-databases -c -uroot -pxxxxxxx But I'm getting this error : mysqlcheck: Got error: 2013: Lost connection to MySQL server ...
user avatar
0 votes
0 answers
103 views

ib_logfile0 size 44895232 is not a multiple of innodb_page_size after Ubuntu upgrade

I originally posted here: https://serverfault.com/questions/1106565/ib-logfile0-size-44895232-is-not-a-multiple-of-innodb-page-size-after-ubuntu-upg?noredirect=1#comment1444562_1106565 One of the ...
user avatar
  • 103
0 votes
1 answer
24 views

Select rows that are not present in the table

I have two tables student table and fee table: First Table (Student): std_id std_name 1 A 2 B 3 C 4 D 5 E 6 F Second Table(Fee): fid std_id amount 1 1 500 2 2 500 3 4 500 I want to fetch ...
user avatar
0 votes
1 answer
35 views

Function that pulls data from backend database [closed]

The function is as following ExecuteSQL('SELECT o.order_id,o.name,o.price from Orders as o where o.account_id = '+Request.parameter('account_id')); The function is trying to extract the account_id ...
user avatar
2 votes
1 answer
42 views

Imporve MySQL InnoDB Full-text Search Performance

1.Background: DB: MySQL 8.0, InnoDB engine; Table size: About 2M rows of 2G data; FULL_TEXT index column: sentence (TEXT data type) https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html Old ...
user avatar
  • 21
0 votes
0 answers
20 views

mysql Trigger to prevent entry being written to a table AFTER writing to another table

Is there a way to use SIGNAL in a trigger to prevent a query being written to a table, but only AFTER you have logged the details in another table? for example: CREATE TABLE `test2` ( `txtvals` ...
user avatar
  • 1,932
-1 votes
1 answer
40 views

Combine output from 3 queries into a single query

I have the following three queries: SELECT NAME, ABS(sum(UNITS)) as WK1 FROM products inner join stockdiary on stockdiary.PRODUCT = products.ID where SUB_CATEGORY='LB40' and DATENEW ...
user avatar
  • 11
2 votes
1 answer
27 views

Calculating membership expiry for overlapping subscriptions

The setup: people pay for membership subscriptions, they can pay before or after their expiry date, so there are gaps and overlaps. In case of a gap, subscription starts anew, in case of overlapping ...
user avatar
  • 23
1 vote
1 answer
20 views

Unknown charset: utf8mb4

Context: I send queries to my MariaDB server(Ver 15.1 Distrib 10.3.34-MariaDB) through a Python script and mysql.connector module. (I don't know which one it's among those 3 in my list: mysql-...
user avatar
  • 43
0 votes
0 answers
42 views

Poor performance with MySQL 5.7 compared with 5.1 when 3 or more users are connected

I inherited a MySQL 5.1 on Win 2008 Server with 20GB Ram installation now in production, and a new MySQL 5.7 Win 2012 Server with 32GB Ram ready for migration. Same data, same apps. Tens of thousands ...
user avatar
0 votes
0 answers
17 views

Pivot table but with cross product

I' currently in the following situation: I have a measurements table that looks something like this: +----+-----------+-------+---------+ | id | germplasm | trait | m_value | +----+-----------+-------+...
user avatar
  • 165
1 vote
1 answer
35 views

Cannot assign local variable using ":=" inside subquery

I have a query which counts the total of users by month or by day. So what I have to do is to accumulate in a variable that total. My code is: select * from (select createdDate,newUsers, ( csum := ...
user avatar
0 votes
1 answer
30 views

Can you create a new field in a rollup summary or does it require a nested query?

The result will be a table where the original table contents are 450,000 rows (or records) of precinct election results for each statewide race in Texas. Here's my query the way it is: select UPPER(...
user avatar
1 vote
2 answers
29 views

How do you check if two tags exists in a tag_map GROUP By article_id?

In a fairly simple tag_map, CREATE TABLE tag_map ( article_id int(11) unsigned NOT NULL, tag_id int(11) unsigned NOT NULL, INDEX(tag_id), PRIMARY KEY(article_id,tag_id) ) ENGINE=InnoDB; How can I ...
user avatar
  • 4,261
0 votes
1 answer
17 views

Date missing from timestamp in mysql log

Currently, my log entries look like: time message 03:53:08 PM ERROR 05:40:32 PM ERROR ... ... I want the date included, like this: time message 06/30/2022 03:53:08 PM ERROR 06/30/2022 05:40:32 ...
user avatar
  • 11