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
3
votes
1answer
23 views

Do I need to convert varchar(32) and varcha(64) to update from MySQL 5.7 to 8.0

I am upgrading a database from 5.7 to 8.0 in MySQL and ran across this: How to easily convert utf8 tables to utf8mb4 in MySQL 5.5 I understand converting the schema and tables to utf8mb4 and ...
0
votes
1answer
30 views

Search in JSON by id and name

I have this table: id number name text row for example: id name 1 {"vals":[{"id":123,"name":""}],"additional":"text"} 2 {"vals":[{...
-1
votes
1answer
33 views

How is the value of row column is estimated in EXPLAIN statement in mysql?

I have the following setup: create table Test(id integer, itemID integer, title varchar(100) ,PRIMARY KEY (id, itemID)); insert into Test(id, itemID, title) values(1,678, "Hello"); insert ...
0
votes
0answers
24 views

Taking InnoDB MySQL/MariaDB cold file backup and restoring on new Omeka DB

I have an unresponsive Omeka MySQL/MariaDB database on a Windows server. It is unresponsive in the sense that it will not start from the XAMPP panel: 10:44:15 AM [mysql] Attempting to start ...
0
votes
0answers
14 views

How to do incremental backup with limited privilege in mysql?

Env:MySql Community v5.7 @AWS RDS User Privs: Select_Priv, Create_View, Show_View, Show_Db, Execute, Create_tmp_table binary_log: ON Currently I can do full backup using mysqldump --single-...
3
votes
1answer
50 views

Incremental backups: MySQL Enterprise Backup vs Percona XtraBackup?

We're evaluating two tools incremental backup: MySQL Enterprise Backup and Percona XtraBackup. The only comparison I found is by Percona which has obvious cause for bias From this comparison, Percona ...
1
vote
1answer
26 views

MySql Event to Delete Data from 2 Tables Older Than 2 Months [closed]

Im new to MySql events and I never used them before so please to bear with me. With My DB I have order_details and invoices tables which I need to create an Event to delete all the records older than ...
0
votes
1answer
26 views

MySQL: Why am I getting a syntax error when using the FIELDS ESCAPED BY clause on a select statement

I am attempting to generate a csv file including headers from a table in my MySQL 8.0 database. After a lot of searching I found the following suggest syntax: SELECT "ansId","assmt&...
0
votes
1answer
93 views

MySQL 5.7 High Memory usage Killed by OOM

i upgrade my WHM server / Cent-OS 7 from MySQL 5.6 to 5.7. after that upgrade every few hours my server memory full by MySQL which after hours of MySQL restart it exceed 80% of whole memory. i ...
0
votes
2answers
36 views

Near identical MySQL deployments behaving very different - High CPU Usage problem

So I have five identical websites, running on five machines provisioned in the same way. The only thing that differs between these installations are the language files and the languages of the text ...
0
votes
1answer
27 views

“ENUM” is not vallid at his position, expecting ')' [closed]

I'm keeping this error and I'm very new to mySQL so I don't know what is going on. The only way I found to remove this error message is to put ')' before the ENUM and then the message change - "...
3
votes
1answer
45 views

Storing time pricing information

I have an application for a business customer and one of the new components is a time tracking system. The actual time tracking is done (pretty standard schema), but the structure this company uses to ...
0
votes
0answers
8 views

Sync update during semi-sync/async replication

Using MySQL, eventual consistency is realized within semi-sync/async replication. But is there any way to force strong consistency (while using semi-sync/async replication) for some concrete INSERTS/...
0
votes
0answers
12 views

Import SQL between VM slow

We have database backups that we sometimes have to inject into the databases concerned. If we copy the backup to the VM hosting mysql, then we perform the import, it is very fast (3 minutes on average)...
0
votes
1answer
15 views

MySQL rows in explain extended keeps increasing while the rows are being deleted continuosly

I have a table in Mysql with userid, status and other columns. I keep updating the status of the row as and when the job is complete. Periodically these rows are deleted from the table. JobTable (...
0
votes
0answers
14 views

Mysql user with grant option cannot grant access to another user

in my mysql server there is a user mgenkin with the following privileges: +---------------------------------------------------------------------------+ | Grants for mgenkin@% ...
0
votes
1answer
18 views

Why is MySQL using this index? Can I force it to use another?

I have a query, and when I call EXPLAIN for it, it appears that my db is using an unhelpful index and ignoring a useful index. EXPLAIN SELECT id, full_name, agent_id, brand_id FROM `mytable` WHERE ( ...
2
votes
1answer
40 views

CAP Theorem and MySQL

There's a CAP Theorem: R + W > N. Let's say I have W=1 (master), R = 6 (slaves), N = 6, so one slave can write and read. As the theorem says we could be sure in strong consistency (we always will ...
2
votes
2answers
40 views

Should I in general partition tables by tenant IDs in a multi-tenant system?

We're building a system where the data in 10 tables is associated with Accounts. A typical table looks like this: create table Things( accountId varchar(64) not null, internalId varchar(64) ...
0
votes
0answers
8 views

Deleted MySQL “root” user in phpMyAdmin through XAMPP local host…how to restore? [duplicate]

I was working in phpMyAdmin (XAMPP localhost) to add a user for a new database I created for a local WordPress website, and came upon the list of users named "root". Since I did not remember ...
0
votes
0answers
13 views

MySQL server on Microsoft Surface Pro X (ARM-64) work around?

I've been given a surface pro X laptop/tablet(?) which has been purchased for one of the hands-on finance directors who like to browse the database for the company I'm working for. On the looks of it ...
1
vote
0answers
26 views

Why is the authentication_string different from the other one if the passwords are the same?

I've set the same passwords for the following accounts: 'root'@'%', 'root'@'127.0.0.1'. When I type select user, host, authentication_string from mysql.user I get different values for the ...
1
vote
1answer
28 views

Why does CURRENT_USER() show the root@::1 when I login to MySQL?

I've set 3 different passwords for the following accounts: 'root'@'localhost', 'root'@'127.0.0.1' and 'root'@'::1'. By typing: mysql -u root -p I can only login with my 'root'@'::1' account password ...
0
votes
1answer
43 views

MySQL table queries extremely slow

I am using MySQL 8.0.17 and I have a table with just 7 million rows defined like CREATE TABLE `measurement` ( `id` int(11) NOT NULL AUTO_INCREMENT, `field1` int(11) DEFAULT NULL, `field2` ...
0
votes
1answer
19 views

How can I remove identical (but negative) rows that are also in some days of each other from a SELECT on a table

I have a MySQL table with two fields: date and value. date is of type date, and value is decimal(10,2). There are "pairs" of rows (non-consecutive) whose values are of equal magnitude but ...
0
votes
0answers
18 views

AWS RDS is showing very high wait/synch/mutex/sql/ values and EXPLAIN statements in performance insights

I'm running a CRON script which checks the database for work and executes anything that needs to be done. It does this across ~500 customers per minute, but we are using AWS RDS with a 16 vCPU ...
0
votes
2answers
19 views

Getting averages based on column values

Editing because I made a mistake transforming from my real example with lots of irrelevant details to the boxes example: I have a table BOXES like this: box_id | color | dimension | value 1 ...
0
votes
1answer
14 views

Log slow SELECT queries only

I'm enabling slow log query for MySQL but I notice that CREATE, INSERT, UPDATE, et. al are also included. I'm generally not interested in those. Is there any way to specify that only slow SELECTs are ...
0
votes
1answer
14 views

Sync MySQL schemas between different databases

I have a couple of databases that have the exact same structure than my "master" database, but after a couple of months, the developers did small changes (like, add another column, change a ...
0
votes
0answers
19 views

Migrate a DB2 table with BLOB to MySQL

I am trying to migrate a DB2 database (v 11.1) to MySQL (v 5.7). I have tried using DBConvert (https://dbconvert.com/db2/mysql/) which works quite well for "normal" tables but fails at a ...
0
votes
1answer
30 views

Why has my root password changed after I updated MySQL from 5.7 to 8.0?

I have set my root account passwords like this: mysql -u root SET PASSWORD FOR 'root'@'localhost' = PASSWORD('pswd1'); SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('pswd2'); SET PASSWORD FOR 'root'@'...
0
votes
0answers
10 views

How do I restore old db table files on a new MySQL server?

I have MySQL db files *.frm, *.MYD and *.MYI. But my newly installed MySQL only support *.ibd files. I throwed mySchema folder onto C:\ProgramData\MySQL\MySQL Server 8.0\Data and restarted MySQL ...
0
votes
0answers
7 views

Before deleteion trigger for self joined table sub rows

I want to write trigger before deletion to delete sub-children from same mysql table. My table is samp_c: **ID** **Name** **key** **Sub-key** 1 sample1 ab 0 2 Sample2 bc ab ...
0
votes
0answers
15 views

after update triggers in mysql

I have 4 tables comment,employee,mode and module(all are default NULL). I want to add an after update trigger in my comment table when emp_id,mode_id,module_id from comment table(which are foreign key ...
0
votes
0answers
22 views

How can I login to MySQL as 127.0.0.1 or ::1 instead of localhost?

I've set different password for 'root'@'localhost' and 'root'@'127.0.01' like this: mysql -u root SET PASSWORD FOR 'root'@'localhost' = PASSWORD('pswd1'); SET PASSWORD FOR 'root'@'127.0.0.1' = ...
1
vote
1answer
27 views

MySql crashing for no apparent reason

We have a relatively busy site with ca. 30.000 connections per day. Things run fine, except that 1 or 2 times per day the MySql server crashes for no apparent reason and then restarts automatically. ...
0
votes
1answer
35 views

Why does adding an index on a column containing a char version of a UUID decrease performance?

I have a table with 3.5 million rows containing UUIDs in their ascii representation (so 36 chars) and an integer amount. I need to SUM all amounts grouping by UUID, so the query is very simple: SELECT ...
0
votes
1answer
12 views

Return inserted entity in mysql stored procedure

I want to have inserted data in stored procedure, because of of sql injection I use prepare and execute statement. what should I've done to have the inserted data following code is only a part of ...
0
votes
0answers
30 views

Is there a way to pass the password more securely while scheduling backups in MySQL?

I run the batch file like this: mysqldump.exe --user=uuu --password=pswd --host=localhost --port=3306 --result-file="c:\mydata\backups\db_backup.%date:~0,2%-%date:~3,2%-%date:~6,4%_%time:~0,2%.%...
0
votes
1answer
25 views

MySQL - Dumping 1TB database with SED to replace definer

Im going to dump a large database more than 1TB using MySQLDUMP (unfortunately I have to stick with mysqldump, I can't use mydumper or any other backup tools) And I want to replace the definer on the ...
0
votes
2answers
22 views

Help ordering table query

I have a MySQL table with this structure: Gene | Impact | Frequency --------------------------- SCN1A | LOW | 0.8 --------------------------- CFTR | MODERATE | 0.1 -------------------------...
0
votes
1answer
27 views

MySQL: Shouldn't these two DATE queries be the same?

I have this query, it tells me what order has expired, immediately after it expires. ( SELECT * FROM orders as ord WHERE DATE_ADD(paid_on, INTERVAL ord.duration HOUR) < NOW() ...
0
votes
0answers
15 views

how to backup remote mysql server to local direcotry using innobackupex

I am using innobackupex to backup remote mysql 5.7 like this: innobackupex \ --host="mysql.google.com" \ --port=3306 \ --user="root" \ --password="passwod" \ --stream=...
1
vote
1answer
45 views

Why doesn’t a MySQL fulltext search “IN BOOLEAN MODE” fail when referencing a missing column?

First, the background on how I discovered this issue. This happened when using MySQL 5.7 and the DB — as well as the related table — are using MyISAM. I was debugging some code — and related MySQL ...
2
votes
2answers
40 views

How to do a recursive sum of children in mysql?

I've searched and tried for long hours, and can't seem to get what I need. I have a table of accounts as a tree -parents and children-, only leaf accounts can have transactions, any parent will NOT ...
0
votes
0answers
16 views

MySQL Cluster Data node can't get the node ID?

I have a MySQL Multi Node ( Cluster ) like this A is ndb_mgmd and also the client B and C is ndbd I have successfully set up everything but the Data Node just cant get the Node Id. In ndb_mgmd server ...
0
votes
1answer
32 views

Creating big shared database

I wish to create DB in Firestore. My program will have multiple user and each user will have calendar and can create an event. The user can share this event with any other user (Similar to Google ...
0
votes
2answers
14 views

Creating a column in dataset for later filter use (mysql)

I have a date column (format: 14.05.2018) and a count of sales. I would like to create an extra column that from the current date perspective flags fields that are +/-6 days from today. The following ...
0
votes
1answer
23 views

MySQL Insert/Update across 3 tables with 1m+ rows

To start with, I know nothing of database design, so I apologise if this seems obvious to others. I have been researching up to 3NF over the last few weeks, and I think I have a layout that works. I ...
0
votes
1answer
21 views

atomic insert select operation

So i have two tables: Project: ---------- project_id (PK) project_prefix Issues: ---------- issue_id project_id (FK) for every project with some prefix ex P1 i want all issues to be like P1-1 P1-2......