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

MySQL 8.0 Some pointers may be invalid and cause the dump to abort

MySQL 8.0 server is randomly crashing. Server Info: Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit CPU(s): 14 On-line CPU(s) list: 0-13 Thread(s) per core: 1 ...
  • 101
0 votes
1 answer
6 views

mysqldump on percona 8

I have a production server running on AWS EC2 with GP2 SSD disk, it is working fine without any problems, but I noticed something weird, when I dump a database first time I take the dump it take ...
0 votes
1 answer
12 views

MySQL FULLTEXT Match exponentially slower with search phrase

I'm trying to track down some performance issues in my FULLTEXT MATCH() AGAINST() query, and it seems to be related (at least in part) to queries requiring an exact phrase. My table stores system ...
  • 3
0 votes
1 answer
20 views

I can't bring single data

SELECT * FROM ( SELECT DISTINCT ads.product_id, products.imageversion, ads.id, ads.stock, ads.miad, ads.level_of_sales, products....
1 vote
0 answers
16 views

aws rds password change triggers updates to which fields in mysql.user?

I'm using AWS RDS with MySql (5.7.mysql_aurora.2.10.2) Due to internal policy I updated db password via AWS RDS modify DB instance menu. What I can't grasp is, when I updated pw for dev db instance, ...
  • 33
0 votes
0 answers
9 views

optimising export from sqlite to mysql

i have a database i want to convert from sqlite to mysql. i can't use the .dump command because the mysql schema is slightly different, and there are small differences between sqlite and mysql. the ...
  • 161
0 votes
0 answers
14 views

Why am I getting error: Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint

I am quite new to SQL and learning by doing this exercise.... I'm working on a problem in MySQL and I cannot get my head around this error message... the Modules & Course_modules tables populated ...
1 vote
0 answers
22 views

Upgrade Replication MySQL from 5.7.34 to 8.0.28 without database downtime

I have to upgrade a MySQL replication topology from 5.7 to 8.0 without database downtime. It’s MySQL Community Edition. There is one source and 5 replicas in the topology and connections happen via ...
0 votes
1 answer
18 views

What units are wait times in MySQL performance schema?

The documentation at https://dev.mysql.com/doc/mysql-perfschema-excerpt/8.0/en/performance-schema-wait-summary-tables.html explains what the columns are, but does not mention the unit. I assumed it ...
-1 votes
0 answers
13 views

how to fix the problem in creating sales table

It give error code; unknown column.. what s wrong? CREATE TABLE sales_table (order_date DATE, order_no varchar(100), p_code int, p_name varchar(100), qty int, price int); INSERT INTO ...
1 vote
0 answers
18 views

MySQL from backup won't start due to possibly corrupted data folder or permissions

A few days ago I was forced to format my PC due to an issue with a Windows update, I managed to make a backup first and now I'm trying to restore the files. I have a mysql instance that I'm having ...
  • 111
5 votes
2 answers
212 views

Advantage of "LOAD DATA ... REPLACE INTO TABLE" over "UPDATE table_name SET"

I inherited a system in which all updates (even to a single row/record) to MySQL tables are not done using UPDATE table_name SET. Rather, they are done by: Exporting the existing table to a CSV (text)...
  • 153
1 vote
1 answer
29 views

When I use group_concat on two fields, can I count on the two concatenations having the same order?

If I have a table like this: bagid fruit color ----- ----- ----- 1 apple red 1 banana yellow and I select bagid,GROUP_CONCAT(fruit),GROUP_CONCAT(color) grouped by bagid, can I ...
3 votes
1 answer
35 views

Incorrect database name `` after mysql@8 upgrade

After MySQL upgrade we ran into a breaking change as we have some really strange queries like this one: use database; SELECT * FROM `Table` where ``.`Table`.`id` = 1 With MySQL@5 this query runs ...
1 vote
1 answer
22 views

Force Index does not work on aws mysql

I have a table sales with a compound index on (user_id, eastern_date, state) CREATE TABLE sales ( id int not null auto_increment, user_id int not null, eastern_date date not null, ...
  • 113
0 votes
0 answers
15 views

Getting previous row values and comparing it to the current row in mysql

So I want to calculate the three month performance of certain stocks. Here's a portion of the raw table: dt stock price 2022-09-01 abc 100 2022-09-01 xyz 50 2022-09-02 abc 110 2022-09-02 xyz 55 ...
  • 21
1 vote
0 answers
22 views

How do I show only the value of the first orderdetail in MySQL

MYSQL version 5.7.38-google-log I have orders and orderdetails table. Follow this https://dbfiddle.uk/bdosg8aa SAMPLE DATA CREATE TABLE orders ( orderID int, orderAmount int ); INSERT INTO orders(...
0 votes
1 answer
14 views

MariaDB: Missing FILE_CHECKPOINT

Running MariaDB v10.9.3 on Arch Linux. I've hosted a file storage cloud with it for years. Recently decided to create a database for another project, and am very new to MySQL interactions, so it's ...
  • 1
1 vote
1 answer
24 views

Update rows recursively based on previous rows by individual

I am struggling in finding a faster way to update the data I am currently working with. More precisely, I have workers' contracts data with starting and ending date for each contract. However, ...
0 votes
0 answers
15 views

Failed to open file '{dump.sql}', error: 13 Windows 10

I have installed XAMPP with PHP 7.2.23 and MySql v8 and want to import database dump on Windows 10 I used this commands in Command promt running by Administrator: C:\xampp\mysql\bin\mysql -u root ...
0 votes
0 answers
21 views

How to get unique data between 2 dates with a date range?

I've got a query where I am trying to find the unique items that were sold between 2 dates, for example 01/01/2022 and 01/09/2022 (DD/MM/YYYY). I want to find the unique items which were sold between ...
0 votes
1 answer
42 views

N-to-many relationship

Let's consider the following data structure. There are teams of N employees, where each of them has one of four professions (A, B, C or D). Each employee belongs to only one team, and in every team, ...
1 vote
1 answer
22 views

How can I detect failure in CONVERT( tbl AS utf8)?

MySQL provides the CONVERT(`tbl` AS utf8) expression to transcode text from one charset to another. Sometimes that conversion cannot succeed, because the destination charset does not include ...
0 votes
1 answer
13 views

I can't properly set permissions to MySQL config files

Yesterday I ran out of hard drive space for the database in the middle of a dump; Luckily this was in development and I had my backups... but I like to see these cases in detail and document them in ...
1 vote
1 answer
26 views

'Group by' on indexed TEXT columns not working

I'm creating the following table in MySQL: CREATE TABLE group_by_test ( txt1 TEXT, txt2 TEXT, txt3 TEXT, val INTEGER, INDEX (txt1(10), txt2(10)) ); I then insert a bunch of rows, ...
  • 145
0 votes
0 answers
15 views

Unknown column od.id_warehouse in where clause

SELECT LEFT(invoice_date, 10) as fix_date, COUNT(*) as countOrders, SUM((SELECT SUM(od.product_quantity) FROM ps_order_detail od WHERE o.id_order = od.id_order)) as countProducts, ...
0 votes
1 answer
19 views

MariaDB Multi level replication (replicate from slave)

I am looking for a way to set up MariaDB replication from slave. My configuration looks as following: First datacenter: Master + 3 slaves connected via private switch Second datacenter: Master + 3 ...
  • 3
-3 votes
0 answers
17 views

Grant permission on all stored procedures to a user to my database mysql [closed]

how to grant permission of my live database (stored procedures and all ) to a user in mysql
0 votes
0 answers
9 views

Loading connection string via appsettings gives me mysqlexception user

I am am running a asp.net web core app on Linux. I am using MySQL as the back-end. When connecting to the database I use a string stored in one of my classes. This is the connection string: "...
0 votes
1 answer
30 views

Most efficient way to move huge amounts of data between MySQL and SQL Server Databases

I'm looking for an efficient way to move more or less 300GB of data between a MySQL Database and a new SQL Server Database. Any suggestions?
1 vote
1 answer
20 views

How can I detect double-encoded MySQL columns and rows, and validate the repair?

My database provider, bless their hearts, migrated our MySQL databases to another server recently, and introduced double-encoding of UTF-8 data via Latin1 into our text data. Strings like 'emdash—here'...
0 votes
1 answer
15 views

* wildcard only matching whitespace in mysql match against

I am trying to set up a search function on my web site. I have a table called projects with the following content: +----+--------+------------------------------------------------------------+----------...
-2 votes
0 answers
13 views

Why does my Group Replication plugin fail in MySQL 8.0.27?

Why does my Group Replication plugin fail in MySQL 8.0.27 ? Can't open shared library '/usr/local/mysql/lib/plugin/group_replication.so' (errno: 0 /usr/local/mysql/lib/plugin/group_replication.so: ...
0 votes
1 answer
28 views

mysql range clause with month and date

I'm trying to define a frame using the range clause. Here's my code: select dt, pair_ticker, close, min(dt) over(partition by pair_ticker order by dt range between interval 3 ...
  • 21
0 votes
0 answers
17 views

Writing SQL with selected value as title and calculated sub-total

I need help writing my SQL query. I've got 3 tables. Here's a simplified example. Items Name Description First desc 1 Second desc 2 Item Detail Name Supplier Reorder Level First Amazon 1 Second ...
  • 448
0 votes
0 answers
18 views

selecting the date to be used in calculating percentage change

I have a table named stock_info with the following columns: dt, stock, close_price. I would like to make a query that would calculate the rolling three month price change for each stock. The previous ...
  • 21
-1 votes
0 answers
20 views

finds the COUNT of orders placed in 2016 by customer e-mail address

Customers: customer_id first_name last_name email address city state zip_code 1 abc a abc@mail.com 1st street chennai TN 60001 2 efg e efg@mail.com 2nd street Bangalore KA 50002 3 ijk i ijk@mail....
0 votes
2 answers
31 views

High Load MariaDB Cluster (Galera Multi Master Replication) - Same hardware needed for Slaves?

My Galera Cluster is on very high load. To run it fine, I´m using strong hardware and for sure a lot of custom MariaDB settings and custom OS tweaks. Now, I want to add Slaves to get "realtime&...
0 votes
3 answers
37 views

Does MySQL have an "order" type that enforces data integrity? Or does any database support this?

Web dev here, typical scenario at work - I have a list of items that can be reordered using an UI. Upon reordering, I want the database to update. Using MySQL I've always found this process very ...
-3 votes
1 answer
22 views

How to create table with default insert from another table?

I have created a table units as create table if not exists unit (SNO integer primary key auto_increment, Category varchar(20), Unit Varchar(20)) now I want to create another table supplier as create ...
0 votes
2 answers
21 views

Bad practice? Use inner join id for future joined tables

I have 2 or more tables that will frequently be used for JOINs in database VIEWs but they don't have any field in common so I'm considering adding the same generated id key to be inserted into a "...
0 votes
2 answers
20 views

Mysql UUID function, does it prevent duplicates?

lets say i use INSERT INTO database(column1) VALUES (uuid()); if i do the same again INSERT INTO database(column1) VALUES (uuid()); (lets assume the UUIDs are both the same, somehow) will mysql ...
1 vote
2 answers
39 views

Merging multiple rows into one in MySQL

I have the following table : Column1 Column2 Column3 Column4 Account One Two Three Data1 Acc1 One Two Three Data2 Acc2 One Two Three Data3 Acc3 Five Six Seven Data3 Acc4 Five Six Seven Data1 ...
0 votes
0 answers
12 views

When does InnoDB acquire gap locks on the post gap of the selected/updated records?

I am currently learning about Gap Lock in the InnoDB engine, In MySQL doc they say, InnoDB will acquire a gap locks only on the preceding gap, but on this Percona article as I understood from their ...
0 votes
0 answers
25 views

Get only three rows for each type value

Here is my query: SELECT su.userid AS uid, su.display_name AS username, su.user_type, Ifnull(a.totalinvites, 0) AS invites, ...
0 votes
0 answers
15 views

Error 2020: Got packet bigger than max_allowed_packet' when dumping one row from the table

I got the error message when I ran mysqldump: Error 2020: Got packet bigger than max_allowed_packet' bytes when dumping table `my_table` at row: 17223\n" According to the MySQL official user ...
  • 111
0 votes
0 answers
66 views

Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

Error trying to restore AWS MySQL RDS using backup generated by mysqldump MySQL 8 I have an AWS MySQL RDS, let's call it: productionrds.xxxx.us-east-1.rds.amazonaws.com I created a snapshot and ...
0 votes
1 answer
40 views

Mysql/Mariadb - Speed up query with varchar columns already indexed but index its first 2 characters (USxxxxxxxx, CAxxxxxxxx, etc)

On my project (MariaDB 10.3), I use a table for storing data per ISIN code (a financial asset identifier). For instance, "US3696041033" for General Electric. I have 4 Millions rows in my ...
0 votes
1 answer
21 views

What's the best way of going about storing information from different groups of users?

In my application, users (and all data) is grouped by company. So, for example, every customer in the database has a company assigned to it, as does every product etc. The user can log in with their ...
  • 101
0 votes
0 answers
13 views

Help needed to optimize query duplication

Here, is the exact query, SELECT Ifnull(Round(Sum(b.revtotal),2),0) AS comrevenuew, ifnull( ( SELECT Sum(daily_target) FROM `live_statsb` WHERE type= '".$...

1
2 3 4 5
434