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

phpmyadmin is no longer letting me set auto_increment value

I am following these instructions to reset the value of auto_increment to the last highest value using phpMyAdmin. Example: If I have this table and data: DROP TABLE IF EXISTS `Tbl_Lis_Agencias`; ...
1
vote
2answers
147 views

MySQL slow importing on hyper-v virtual server compared to host

Database dump file: 6 MB size 14k rows 10 tables Server specs (Ubuntu 20.04, virtual (hyper-v)): OS: Ubuntu 20.04.1 LTS MySQL version: Ver 8.0.21-0ubuntu0.20.04.4 for Linux on x86_64 ((Ubuntu)) 4 ...
1
vote
2answers
55 views

MYSQL Server shows it is master server in replication. How to turn off this?

I'm using Ubuntu 20.04 version. I have installed MySQL server using apt-get install mysql-server and used mysql_secure_installation for security. Then I have installed phpMyAdmin using apt-get install ...
0
votes
0answers
10 views

Sharing MySQL data folder between Windows and Linux [duplicate]

I have dual boot laptop with Windows 7 and Linux. I've been working for a long time on Windows programming in php / Apache / MySql. I share NTFS partition with Linux which is mounted using ntfs-3g, it ...
0
votes
1answer
12 views

How do I select the total count of records over the past week, including days which have no records in MySQL

Essentially, I need a query which can output something like: +------------+-------+ | date | count | +------------+-------+ | 2020-09-30 | 10 | | 2020-09-29 | 3 | | 2020-09-28 | 0 | | ...
0
votes
2answers
37 views

MYSQL tuning to optimize CPU usage

I sometimes have a spike in CPU to 70% (usually 20%) and looking if there is anything I can do to improve. I am using mysql rds 5.6.44 I am using m4.large instance on aws Variables and version https://...
1
vote
0answers
23 views

How can I fix mariaDB in debian server?

I'm already too tired of trying so many different things and I can't even bypass an error which is running # mysql -u root -p when it asks for password, no matter what I type it shows Welcome to the ...
1
vote
1answer
35 views

I have 600% high CPU usage mysqld

I have an insane usage of cpu mysqld +600% here is the file my cnf # Example MySQL config file for medium systems. # # This is for a system with little memory (32M - 64M) where MySQL plays # an ...
1
vote
1answer
65 views

Include all records in between two dates

I have a table with the columns ID, start_time, and end_time. I would like to extract all the records that are active during a date range. ID | start_time | end_time ----|------------|-----------...
0
votes
2answers
72 views

Foreign key constraint is incorrectly formed (MySQL) [closed]

So, I have these two tables CREATE TABLE IF NOT EXISTS `inart_ps_categories` ( `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, `inart_ps_category_id` int(11) UNSIGNED NOT NULL, `parent_id` int(11)...
0
votes
0answers
39 views

Does commit after a normal select can call any fsync or flush operations?

I'm using Mariadb 10.2.16. 3 Node cluster. Coms counts taken at particular time for all 3 (insert,update,commit). Com_inserts are in 1.1K Com_updates are in 1.6k Com_commit are in 10K When ...
-1
votes
1answer
41 views

Getting duplicate value when using join

I have 4 tables accordingly that i want join query. Each contact Center id have one annual budget value in second table. Each contact center id have multiple row value in remaining two table i.e ...
0
votes
0answers
39 views

MySQL replication error 1452

could you help me to figure out the solution for a MySQL replication error? mysql replication error: (Last_Errno: 1452) Last_SQL_Error: Error
'Cannot add or update a child row: a foreign key ...
0
votes
0answers
15 views

MySQL and MariaDB both hanging up with any driver when connecting from a remote client to my VPS

What I did: 1- Changed the bind-address to 0.0.0.0. It didn't work. Then I commented it out. It didn't work. 2- Enabled and disabled skip-networking to no avail. 3- My users are using native password. ...
0
votes
1answer
24 views

MySQL / MariaDB - Start multi-source replication with mariabackup (xtrabackup)

Many times I successfully performed procedure described in the https://mariadb.com/kb/en/setting-up-a-replication-slave-with-mariabackup/ - it's fastest and easiest because replica is restored by ...
0
votes
1answer
23 views

Combine Multiple Rows Results From Left Join in a unique cell

¡Hi guys!, I don't have any idea how to create a new column that contains all information of results from a Left Join if exists the value Example Table 1 -------------------------------------- | ...
-1
votes
1answer
55 views

Optimising SQL Query

Currently I have a query to calculate the averages of auctions that are present within a game, in order to do this I have the following query: SELECT item_name, tier , CAST(SUM(highest_bid) AS ...
-1
votes
1answer
18 views

Database design normalization elearning portal

Table : Class Here the className is unique classId| className -------|--------- 1 | 1st Class 2 | 2nd Class 3 | 3rd Class 4 | 4th Class Table: Subject In the Subject table ...
0
votes
0answers
20 views

Problem in model to save score

I am building an application in which users will have a score in which they can receive it (+10 XP) when they do certain activities, but they can also lose it when using that score to make "...
0
votes
1answer
20 views

MySQL in Ubuntu Linux - I cannot connect to the server

I am new in MySQL. I installed Workbench and server. When i try to connect to the database server i receive a message: Your connection attempt failed for user 'root' to the MySQL server. Ann AppArmor ...
0
votes
0answers
10 views

Mysql_upgrade fails when running from Xampp

Recently I had to reinstall Xampp and I added some older databases to MySQL after which all seemed to run just fine. Now, a few months later, I got an error while using IsNumeric in a query, and was ...
1
vote
1answer
57 views

How can I get LOAD DATA LOCAL INFILE to work for MySQL 8.0.21 on a Windows machine?

I've created a file: 'C:\fff.txt' which contains data for my database table. Then I'm trying to load data from that file like this: LOAD DATA LOCAL INFILE 'C:\\fff.txt' INTO TABLE fff LINES TERMINATED ...
0
votes
0answers
9 views

is it possible to see the rows MySQL 5.7 locked

I am using this sql to update, but now I amd not sure how much rows it locked. UPDATE r_room_seat s SET s.status = 1, s.room_play_id = (SELECT @update_id := max(room_play_id)), ...
0
votes
2answers
23 views

row lock cause dead lock in MySQL 5.7

Today my app tell me encount a dead lock, error like this: rror querying database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try ...
0
votes
0answers
6 views

How to Fail-over of MYSQL 8.0 on Red Hat Cluster without Application to Re-login & avoiding service interruption

Getting the error after every fail-over test case is run :- host is blocked because of many connection errors . We need to login back again to database and use Flush Host command to allow new ...
0
votes
0answers
18 views

How to connect to remote MySql db with user that has '@' symbol in name?

I am having trouble dumping database schema into a self contained file using this command mysqldump -u 'dbadmin@morecharacters' -p -h 'remotehost' -P 31337 --no-data dbname > schema.sql. The error ...
0
votes
2answers
51 views

Database normalization for sensor data

I'm working on the backend of a quality evaluation tool. And I hit a the limit of too many columns. SQLSTATE[HY000]: General error: 1005 Can't create table xxx.xxx (errno: 185 "Too many columns&...
1
vote
3answers
62 views

How to not allow to insert a new record if count of current records exceeds a specified limit

I have two tables as the following team `id` BIGINT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `member_limit` INT NOT NULL team_member `id` BIGINT PRIMARY KEY AUTO_INCREMENT, `team_id` ...
-1
votes
1answer
15 views

why my union index not working in MySQL 5.7

I create a union index in my table like this: CREATE INDEX app_list_idx ON report_envelope_app_list (created_time,round_id,app_id); this is the created result: then I using this to see, but not ...
1
vote
1answer
40 views

MySQL takes huge memory by ignoring buffer pool size

I am facing a very strange problem, looks like on mysql startup, mysql is taking so much memory than 4x of buffer pool size. I am using Ubuntu VM (4 Core 8 GB) with MySQL 5.6.33. /etc/mysql/my.cnf is ...
0
votes
0answers
7 views

What files of Mysql lib directory safe to be deleted and to be recreated with innodb_file_per_table settings?

I have brand new mysql 8 setup and installed on a centos 7. Unfortunately I forget to set innodb_file_per_table. I would like to dump the database and recreate with the innodb_file_per_table settings. ...
-2
votes
2answers
40 views

How can I save the MySQL query output to file on a Windows machine?

Let's say I want to save the whole list of the collations supported by MySQL into file. How can I save the result of the show collation into .txt file? The OS I'm using is Windows. I tried something ...
0
votes
1answer
13 views

is it possible to reset innodb_row_lock without restart MySQL 5.7

Now I could using this command to check the innodb_row_lock values: show status like 'innodb_row_lock%'; Innodb_row_lock_current_waits 4 Innodb_row_lock_time 22471927 Innodb_row_lock_time_avg ...
0
votes
0answers
8 views

Is it possible to replicate different Galera MAIN-MAIN clusters (MariaDB) with another Galera MAIN-MAIN cluster but as MAIN-SECONDARY asynchronously?

We are a small team of developers and we usually deal with single database access locally or remotely. We are working on a project that needs to have not only replication on the local network (we'll ...
0
votes
1answer
66 views

Why does our MySQL server become non-responsive with the processlist showing tons of processes waiting in STATE=init, INFO=commit?

I work for a company that's been around for a while and has a large MySQL (5.6.48) monolith running in RDS. Recently, the database has starting going unresponsive for 10-30 minutes at a time during ...
2
votes
1answer
46 views

MySQL Client can Connect but Replication Slave Can't

We have an odd issue we've never come across. We have roughly 14 pairs of master/slaves all running MySQL 5.6.35 without issue. We are trying to add a slave to a slave (which we have two other ...
0
votes
0answers
123 views

InnoDB Failing to start / MySQL not loading plugins

I installed MariaDB on my system (Debian 9), however, upon running sudo service mysql start, I get these errors: Sep 22 12:32:37 bremea mariadbd[30151]: 2020-09-22 12:32:37 0 [Note] /usr/sbin/mariadbd ...
2
votes
1answer
41 views

MySql: does max_user_connection concern SUPER user?

On MySql 5.7 I've setup variable max_user_connection = 35. I have several users, their number of simultaneous connections is effectively limited. Now I have created a brand new user with SUPER ...
0
votes
1answer
21 views

GeoJSON MultiLineString import to MySQL with SRID/CRS 27700, 'out of range'

Version 8.0.20 I'm importing a single link of the OSM road network with the intention of creating a buffer in MySQL. This is it: select st_srid(st_geomfromgeojson('{ "type": "Feature&...
0
votes
1answer
45 views

Prevent unique constraint violations caused by concurrent inserts

I have a situation where I need to insert a unique record into the database: SELECT user FROM users WHERE id = 'some_randomly_generated_id' If user does not exist, INSERT INTO users WHERE id = '...
0
votes
0answers
33 views

MySQL: Configuring the right innodb_buffer_pool_instances and innodb_buffer_pool_chunk_size on RDS with huge RAM

We are on a db.r4.8xlarge MySQL (version 5.7.23) AWS RDS instance with 16 cores, 32 vCPUs, and 244GB RAM. We set the Buffer Pool Size to around 144GB at the default innodb_buffer_pool_chunk_size and ...
0
votes
0answers
10 views

Is it possible to update multi lines with different values in one query

Now I am using MySQL 5.7. this is my sql command I am write to update records: set @update_id := 0; UPDATE r_room_seat SET status = 1, room_play_id = (SELECT @update_id := max(room_play_id)), user_id ...
1
vote
0answers
11 views

MySQL replication setup for high load Master

We have simple replication set up with 1 master and 1 slave. However, setting this up (after restart for instance) is always a difficult task. The moment between taking my database dump, noting the ...
0
votes
0answers
29 views

MySQL group replication plugin won't recognize host's IP address?

I have pulled the current mysql-server image (mysql-server) on 2 Ubuntu 18.04 machines and I want to set up group replication. This is how I start the mysql container on each machine (--server-id is 2 ...
1
vote
2answers
34 views

MySQL performance degraded after database migration?

I migrated my MySQL database from GCP to Azure (both 5.7), but it seems to have affected performance. Server before migration: 2 VCPUS with 7.5GB memory Server after migration: 2 VCPUS with 8GB memory ...
0
votes
1answer
26 views

How to do safety mirroring innodb table on mysql?

I have one Mysql InnoDb table with rush trafik on row update. The table is also frequently used for heavy select query (especially group by for summary report). As I know this heavy select query will ...
1
vote
1answer
15 views

How to know how full the MySQL redo log is?

Setting innodb_log_file_size to a higher value improves write performance, but increases crash recovery time (or even restart time, if I understand correctly). Is there a way to know, at any time, how ...
1
vote
1answer
85 views

Swap Usage but high Freeable memory

We have an AWS RDS with a MySQL database. Buffer Pool size is only 144GB out of the total available 244GB RAM. However, even at such a low RAM config, we see Swap Usage happening. Does anyone have any ...
0
votes
0answers
17 views

How to pass query string containing variable names into mysql stored procedure

I have this procedure into which I need to pass variable parameters. Problem is this: once inside the procedure, the IN parameter needs to pick up already created variables in order to create the ...
0
votes
2answers
13 views

MySQL upgrade version output seems odd

I just upgraded my old MySQL instance to 5.7.31. When I do a select version(); I get 5.7.31-log I am used to seeing something like (on my older version) 5.5.2 What does the -log stand for in 5.7.31-...

1
3 4
5
6 7
391