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.
19,517
questions
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-...
