Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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

Why do I have to set MySQL user password with mysql_native_password to connect with Workbench?

I've noticed if I set the mysql user's password like below, I cannot connect with MySQL Workbench. ALTER USER 'root'@'localhost' IDENTIFIED BY 'myPassword' If I use this below I can connect to it: ...
0 votes
0 answers
4 views

MySQL 8.0 fails to store 5.7-allowed symbols (Incorrect string value, Error 1366)

I'm upgrading MySQL 5.7 server to 8.0 with a bunch of user-input data that sometimes contain "malformed" unicode characters like x'eda0bdedb98f'. The latter seems to be 2 UTF-16 characters ...
0 votes
1 answer
820 views

How to Get the First in and Last out in night shift?

I already have the Query for Getting the Day shift but I'm Having a problem in the night shift, Which is this query. SELECT user_id, Min(verify_date) as First_IN, Max(verify_date) as Last_Out from ...
0 votes
2 answers
17 views

Should I create a separate table for non null user_id values in my MySQL database schema for performance?

I have a MySQL database schema for storing uploads, where each configuration can be associated with a user account (user_id) or uploaded as a guest without an associated user account (null user_id). ...
0 votes
1 answer
772 views

MySQL Stored Procedure Compiles Without Error (I want the error to occur)

I have a MySQL stored procedure that runs fine and does its job quite well. Yesterday, I edited it in MySQL Workbench and introduced a spelling mistake on purpose. For example: - Valid statement: ...
0 votes
2 answers
4k views

Query for Transactions per second?

For performance mornitoring, i would like to have a mysql query which gives the calculated output of TPS. I think tps= Com_commit + Com_rollback / second , but I don't know the query to calculate ...
0 votes
1 answer
969 views

Identifying processes that open MySQL connections

I need to identify which application process issues a large number of MySQL connections on a Linux system. The show processlist sql command or tools like innotop do not really help, as they only show ...
1 vote
1 answer
6k views

Top CPU consuming MySQL processes

I am trying to find out the Query which is consuming high CPU, but can't do it so. This command top -H gives me the high CPU consuming thread PID, but i cannot match them with my processlist output. ...
0 votes
1 answer
944 views

MySQL Workbench Model sync sql_mode

Using MySQL Work Bench, When I look at the system variables of a server I am connected to, it shows the SQL_MODE as "no engine substitution" When I open a Workbench Model, and use the "sync with any ...
0 votes
1 answer
8k views

MySQL DNS issue: skip-name-resolve connection fast but cannot log into email

MySql is used here with remote network connections, and after a reboot of the server these connections started taking 20-30 seconds to connect causing timeouts. Connections used to be nearly instant. ...
0 votes
1 answer
2k views

ProgressBar error and very slow import in MySQL

I'm trying to import a CSV file into MySQL, but the progress bar doesn't advance. When I cancel the import, a ProgressBar error appears, but I see that the file was partially imported. The same error ...
2 votes
1 answer
285 views

What dump file options or MariaDB setting is slowing down the load of data?

Context: Loading a copy of a legacy DB for a CRUD app at build by Spring Boot. Primary use case is developers testing the CRUD functionality as it is developed. Problem: The loading operation is ...
0 votes
1 answer
478 views

Monitor existing cluster using Galera Manager throwing: deployment status error: DB IP address must be provided

I am trying to monitor my existing galera cluster using galera manager. I installed galera manager and chose Monitor existing cluster. I added the ssh keys provided in the manager in the respective ...
1 vote
1 answer
2k views

Amazon RDS index creation on large table over 300 M records

I need to create a new index on large table in RDS MySQL. This index is needed to reduce the scan time on this table. Below is the configuration: Table size: 400 GB Records: 400 Million column - ...
2 votes
3 answers
9k views

Any MYSQL Client with fully dark theme?

I would like to find a MYSQL Client program which can be config as FULLY DARK THEME. Many of them can not be dark theme in displaying result in grid.
1 vote
1 answer
418 views

replicate-wild-do-table=channel_name:db_name.% doesn't work in MySQL 8.0 for channel based replication

I am trying to configure a replication topology where there will be one slave which will connect to two masters. Percona Server 8.0.25. Master1- main master bkp_server - slave to master1 and ...
0 votes
0 answers
13 views

mysql data recovery from Time Machine

Because of some unfortunate events I had to remove mysql from my Mac Ventura completely. There was only one database I was using. Let's call it my_db. Time Machine has backed up this database with all ...
0 votes
1 answer
559 views

How to enable group replication again after dissolving a MySQL Innodb Cluster using mysqlsh?

When I dissolve a innodb cluster using mysqlsh it automatically disables replication on the instance. How do I enable replication on the instance again so i can "start group replication" ...
1 vote
1 answer
386 views

migrate mysql DB between two servers using replication

I need to perform some steps, I have to migrate our company live DB to a new server, if I use normal way such as lock table, mysqldump, scp, mysql restore, it take around 5-6 hours, so I want to make ...
0 votes
2 answers
622 views

How to join to tables with wildcards?

I have table1, and table2 as shown below. I would like to run a left join to merge column C from table2 into table1 based on columnA & columnB. The caveat is that table2 containing wildcard ...
0 votes
1 answer
379 views

Update two tables, each on separate MySQL servers, on two different machines in one query?

I have two MySQL servers running on two different machines in a local network. I am using PHP and accessing the databases via IP. No problems there. The databases have identical schemas. One of them ...
0 votes
1 answer
738 views

MySQL | Measure overall query cost (performance, cpu usage, memory usage)

The Problem You have a MySQL query, let's say a SELECT and you need to identify its overall cost in all aspect (Host Resource Usage, Query speed / performance ...) so that your result can be ...
0 votes
1 answer
523 views

MariaDB LOAD DATA INFO can't import single column file to single column table?

I am trying to import the 10,000 common password list into a table to ban those passwords from being used in a given system. The file is a single column TXT file. Trying to LOAD DATA INFILE that into ...
2 votes
1 answer
326 views

What is causing my ERROR 2002 (HY000): Can't connect to local server through socket '/tmp/mysql.sock' (2)?

I'm using brew install mysql/brew install mariadb, and cannot find a way around the following error: ERROR 2002 (HY000): Can't connect to local server through socket '/tmp/mysql.sock' (2) Here are the ...
0 votes
1 answer
283 views

MySQL InnoDB add new instance to existing cluster

Server version: 5.7.23-log MySQL Community Server (GPL) I'm running an InnoDB cluster and trying to add a new node, the cluster is a couple of years old and binlogs are being cleared after 2 weeks. ...
0 votes
2 answers
370 views

innodb_force_recovery

I did an innodb force recovery. I went from 1-> and on 5 MySQL booted up. I was able to do mydqldump. Customer is now saying some of data is wrong. (References to another table) Is this possible?
0 votes
1 answer
1k views

Starting MySql Failed: check log for more info

I just recently started using MAMP, PHPmyAdmin, and wordpress. When I try to restart the server in MAMP PRO by pressing the server icon in top right corner (mamp pro), I get a pop up that reads the ...
1 vote
1 answer
769 views

How to make delete duplicates faster?

On a mysql table with about 1.7M rows, I tried to delete duplicates posts: delete a FROM comment a INNER JOIN comment a2 WHERE a.id < a2.id AND a.body = a2.body; The result was: ...
0 votes
2 answers
1k views

Import a large MySQL dump 68GB on RDS instance

I tried to import a large MySQL dump.sql (68GB) on RDS AWS instance MySQL 8.0 (db.t3.2xlarge: 32GB RAM). The import takes more than six hours: More than 500 tables I have 2 large tables: the first ...
0 votes
1 answer
375 views

MySQL crashed after Out of memory (Needed 1048528 bytes)

We are facing Out of memory issue from last few days. Doing research on that said to update some of the values, but we are not sure what values we should put as per our system configurations. System ...
0 votes
1 answer
2k views

MYSQL: InnoDB inserts wont rollback transaction inside a stored procedure

I have this stored procedure: START TRANSACTION; code_gen_loop: WHILE @Codes > 0 DO IF MOD( @Codes, 10000 ) = 0 THEN EXECUTE qry_status_stmt2; EXECUTE qry_status_stmt3; ...
0 votes
3 answers
5k views

SQL Query to filter from an Array

I wasnt sure how to title this question. I have a database of suppliers, a simplified version is shown below. Company Vehicles X ["Van", "Luton", "Artic"] Y ["Van&...
0 votes
1 answer
2k views

Mysql : user with privileges to create databases, users and grant all privileges on those dbs to users

Is it possible in mysql to create user who is able to create database, user and grant all privileges on this database to that user?I dont want to use root user in script
0 votes
1 answer
635 views

Detect IP subnet overlap from mySQL database

I have a database with a list of IP ranges in my corporate infrastructure. I want to identify any subnet ranges that are overlapping. I was using the code below which converts the IPs into decimal ...
0 votes
1 answer
1k views

Mysql not writing to binlog

Mysql is not writing events to binlog, I have tried change the binlog configurations, but can't solve. Conf: server_id = 2 log-bin = master binlog_format = row replicate-do-db = our_db log-slave-...
1 vote
1 answer
2k views

mysql deadlock when update some items whose id are selected from another table

I'm sorry, I just can speak a little English. I have two machines(10.10.102.101, 10.10.102.104) which connect to the same mysql datebase. This is a table and deadlock happened on it. mysql> ...
-1 votes
0 answers
9 views

Migration of MYSQL Replication set from AWS RDS to EC2 without data loss

Hi everyone i am having a MYSQL replica set with 1 primary and 3 read replicas hosted in AWS RDS, I want to migrate the data to AWS EC2 instance without any downtime and no data loss. Please provide ...
user avatar
0 votes
1 answer
28 views

MYSQL select similar records sorted by field from another table

I have these tables movie_scores id movie_id user_id score 1 1 1 10 2 2 1 5 movies id name collection 1 Interstellar 1 2 The Dark Knight 2 3 The Shining 1 4 Forrest Gump 2 Here we know that ...
2 votes
1 answer
2k views

How can I stop master from starting itself as a slave (in addition to master) on reboot?

In a simple MySQL replication Master-Slave configuration I have a problem where Master tries to connect to itself as a slave on reboot. So when I restart MySQL on Master, I see errors related to the ...
-1 votes
0 answers
31 views

How can I solve the ERROR 1064 (42000) in mysql?

I am trying to set up MySQL Master-Slave Replication. I have successfully done everything except Starting and Testing my Replication, after entering sudo mysql, I run CHANGE MASTER TO MASTER_HOST='xx....
1 vote
1 answer
2k views

How to sync easily between a master database from Google Cloud SQL and a master database from Amazon RDS

I've created a master - slave (replication) in Google Cloud SQL. I've also created a master - slave (replication) in Amazon RDS. I want to sync two master MysQL from Google Cloud SQL and Amazon RDS....
1 vote
2 answers
462 views

Should I join all of my polygons to make a single polygon for point-in-polygon search?

I have a set of of polygons. Sometimes they are completely disconnected (like separate patches). Other times, two sides of two polygons may touch each other (when the polygons are adjacent like on a ...
2 votes
1 answer
798 views

Class-table inheritance without flag column

I am trying to implement proper Class Table Inheritance (aka Table Per Type Inheritance) in mySQL. AFAIK it's not offered out of the box so some hacking around is needed to make it tick. Current ...
0 votes
1 answer
360 views

Possible to GROUP BY across different columns in mariadb/mysql

I have a table full of events similar to this: `date` | start_time | end_time | length -------------------------------------------- 2022-01-10 | 07:00:00 | 16:00:00 | 9 2022-01-11 | 07:00:...
0 votes
1 answer
377 views

MySQL: how to insert only if update fails

There is a similar question, but my requirement is different. The table schema is: CREATE TABLE `device` ( `id` int NOT NULL, `device_type` varchar(64) NOT NULL, `serial` varchar(128) NOT NULL, ...
0 votes
1 answer
334 views

Powershell script while executing mysqldump script

I'm having an error 1045: Access denied for user 'mysqlsuperuser'@'test.internal.cloudapp.net' (using password: YES) when trying to connect Here is the mysqldump command on my ps1 file. $command = [...
0 votes
2 answers
887 views

Single address table for company, company_branch, users

I'm a database noob and i'm asking this simple question because i found no answer to my question. I'm using MySQL. I have 3 tables Company, Company Branches, Users and these 3 tables have similar ...
0 votes
1 answer
20 views

A foreign key that references a table and another to reference the parent table (what's the alternative)

Background I am OK-ish at Database Design...and I am building out this layer first. It is for a KPI tracker app, for real estate investment businesses. The app's business requirements This KPI tracker ...
0 votes
1 answer
490 views

MySQL: which innodb parameters should I tune if disk I/O is slow

Which InnoDB parameters should I tune if disk I/O is slow? Please give your suggestions. I have another question regarding innodb_log_file :- What happen if innodb_log_file become full ? so what ...
0 votes
1 answer
355 views

Changing storage engine to InnoDb from MyIsam is dropping records from table

Currently our Database all tables are using MyIsam as a storage engine. We tried changing storage engine of our mysql tables from MyISAM to InnoDB from PHPMyAdmin operations tab. However during the ...

1
2 3 4 5
449