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.
17,707
questions
1
vote
1answer
25 views
MySQL Password Validation - No previous passwords
I wonder if there is any possibility to use the password validation for MySQL 5.6 (https://dev.mysql.com/doc/refman/5.6/en/validate-password.html) to prevent for example the last 4 passwords.
The ...
1
vote
1answer
86 views
AWS Aurora MySQL Serverless CPU utilization is growing for unknown reason
We have been using Aurora serverless databases in staging and production environments for a few months now, and find the CPU remains in the range 15-20% almost all the time. However in the past week ...
0
votes
1answer
23 views
Any way of forcing MySQL to use smaller files?
This is actually Maria DB. I'm using it on W10 and Linux Mint 18.3.
The issue here is that I recently strated to use Google Backup & Sync to sync between my various machines running W10 (I'm ...
0
votes
1answer
86 views
How can Update table column with the virtual column?
I want to update repetitive columns, And I build virtual column for did this. I can "select" but can't "update"
Here is SQL Fiddle :
http://sqlfiddle.com/#!9/d09189/3
Also Code review :
I have ...
-5
votes
0answers
18 views
Please tell me query to solve the following question? [on hold]
Display the doughnut “Name”, “Cost” and “Sale Cost”. A sale cost is a calculated column of one
doughnut at 30% reduction.
0
votes
1answer
18 views
MySQL: Flush Privileges of Single User
On Mysql 5.1.72 I created a replication user. Right before running FLUSH PRIVILEGES; this thought passed my mind what if there are any unapplied privilege updates?
How can I flush privileges of a ...
0
votes
1answer
16 views
Need help mariadb optimization for performance configuration
I have server with 20cpu, 40 core th, 96GB ram, 2x3.8TB Nvme disk. This server for shared hosting, and about 1000 account hosted.
Current my configuration on my.cnf
[client-server]
!includedir /etc/...
1
vote
0answers
7 views
mysql data node not connecting to node manager on AWS EC2
I am trying to setup mySql manager + data node + sql server (Server A) and another data node cluster on (Server B). Both running on Ubuntu 16.04 amd_64
Server A
/etc/hosts also on Server B
Both ...
0
votes
1answer
11 views
Merge multiple rows in table setting one column based on value in rows
I have some data that looks something like this:
key B
1 true
1 false
2 false
2 false
And I want to roll it up so that if column B has two false values then the resulting value for B is ...
1
vote
0answers
8 views
ALTER TABLE DROP COLUMN - server status 'Stopped'
I am in the process of removing 4 columns from my (MySQL/innodb) table,
simple code,
ALTER TABLE database_name.table_name
DROP COLUMN A,
DROP COLUMN B,
DROP COLUMN C,
DROP COLUMN D;
It has been ...
0
votes
0answers
11 views
MySQL - too large temporary table on disk
I have two servers with identical MySQL versions (5.5.50, pretty old), identical my.cnf, identical SHOW VARIABLES and three identical tables with identical indexes and different data.
And I have a ...
0
votes
1answer
170 views
Change Storage Engine on Referenced Tables
i need to change storage engine from 'InnoDB' to 'MyISAM' on some tables of different databases.
So i made a script file to do so:
#!/bin/bash
DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names -...
0
votes
1answer
200 views
Automatic MYSQL failover
We have a wordpress environment however this is not a wordpress question. Our scenario is that front Ends connect to a MYSQL backend (MYSQL VM1) which is replicated to a backup vm (MYSQL VM2). The ...
0
votes
1answer
198 views
MySql crashes in VPS
I have a VPS in GoDaddy, which is crashing MySql service at frequent times.
Here is the log file that may help to help me.
160425 09:24:53 mysqld_safe mysqld from pid file /var/lib/mysql/s166-62-85-...
0
votes
2answers
10 views
Mysql - tablename.ibd file is huge in size
I'm using MySQL 5.6 and innodb file per table has been enabled.
I noticed that one file named as tablename.ibd is 211GB, then query the information schema there it's showing 21GB.
I have done a huge ...
0
votes
1answer
215 views
Poor performance on MySQL 5.7 on joined tables ( scanning wrong table ?)
I have a joined table (News, Publishers) with indexing. The query is working fine on MySQL 5.5.
After I upgrading one of the server to MySQL 5.7, I start noticing high load, high CPU, and slow query.
...
8
votes
3answers
23k views
How to COUNT number of rows with LIMIT?
I want to catch X rows, thus, I set LIMIT X; but how can I simultaneously count the total number of rows too?
Currently, I use two separate queries to do so as
SELECT COUNT(*) FROM col WHERE CLAUSE
...
0
votes
1answer
243 views
Restore InnoDB from .frm files
My database crashed and my PC and I now only have the .frm and .ibd files left.
Is there any way to restore my database?
2
votes
2answers
2k views
fix `mysqldump: Error 2013: Lost connection` on “cloud” server
I have 6 months of backups left to perform and a single day can take up 200MB in raw mysqldump --extended-insert=false --complete-insert form. If I try to dump more than a day worth of data I get ...
0
votes
1answer
244 views
Use of MySQL trigger to insert row's week number
I have a table of orders in a MariaDB database that currently has a datetime column. For the dashboard I am building for this database, I have a statistic I would like to display which is "Orders This ...
0
votes
0answers
10 views
Getting Connection Error on MySQL5.1 to MySQL8.0 ROW Based One Way Replication
Trying to replicate MySQL5.1 to MySQL8.0, when running starting the slave on MySQL8.0, I get
[ERROR] [MY-010584] [Repl] Slave I/O for channel '': error connecting to
master 'rep_user@XXXXXXXX.YYY:...
0
votes
1answer
244 views
Percona XtraDB cluster backup solution
We have a 3 node percona xtradb cluster and two slaves attached to it. One of the slaves we want to use it for taking backups. We have GTID based replication setup between the slave and the Xtradb ...
0
votes
1answer
217 views
Mysql Master-Master Replication Error
Configured Master-Master Replication
But both servers slave shows the below error !!
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from
binary log: '...
0
votes
1answer
310 views
commit or rollback on a statement based replication, effect on auto increment id
How commit or rollback is safe on statement based replication in terms of auto_increment column?
If I execute below queries, shoudn't there be discrepancy between master and slave in auto_increment ...
0
votes
1answer
24 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
1answer
190 views
How speed up mysql dump without data?
There are a lot of questions (1,2,3) about speed up MySql restoring. But all of them speed up data import. Is there a way to speed up schema tables creation? For example dump iundepended table in ...
-3
votes
0answers
13 views
How to read millions of records effectively with more joins? [on hold]
Im developing an enterprise application using node js as backend and mysql for database for one of the telecommunication company. I'm currently working in rewards module like grabbing and redeem ...
0
votes
1answer
198 views
combine two select queries?
I have two Mysql select queries:
select TTS.SSML, TTS_TAG.SORTID, TTS.VoiceId , TTS_TAG.TAGID FROM TTS
left join TTS_TAG
on TTS_TAG.TTSID = TTS.TTSID
where TTS.MID = 15
select max(TTS_TAG.SORTID) ...
1
vote
1answer
181 views
Delete query does not end
I have a following table with 1 million records :
CREATE TABLE `activity` (
`id` int(9) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`subscription_id` int(10) unsigned NOT NULL,
`...
1
vote
2answers
50 views
SELECT LATEST ENTRY ON GROUP BY
This query doesn't return the "latest" entry, rather it returns the "older" rows:
SELECT MAX(ID), ID, NEW_BALANCE, DATE, NAME, ACC_NO
FROM
(
SELECT ID, NEW_BALANCE, DATE, NAME, ACC_NO
FROM ...
0
votes
2answers
53 views
MySQL - Column value depends on values from two different tables
I have a MySQL database that contains these tables (* denotes primary key):
TableA:
----------------------------------------------
| *id | time | price | special_price |
---------------------...
2
votes
2answers
4k views
MySQL5.7 (Windows) how to install from zip file without the Data Directory?
This has probably been asked before, but I am struggling to find the answer.
Historically I have always installed MySQL (on Windows) from a template based on the mysql-xxx-win64.zip file.
I would ...
0
votes
1answer
17 views
Unable to start mysql on ubuntu
I have an issue with re-starting my MySQL 8 service on Ubuntu 19 after a force shutdown in normal mode.
Originally I was able to re-start the service in the save mode (had to do
...
0
votes
1answer
244 views
Mysql slave status is completely perfect but data is not reflecting
I have setup replication using mysql utilities. Slave can able to read and execute master's position and binlog files perfectly. seconds_behind_master is also 0. But data is not reflecting into slave. ...
0
votes
1answer
27 views
Fetch correct stats from database
I am trying to get count of all quiz users who have made purchases. Main table for storing users is user and quiz_client is linked with user table. sales_order table holds data of users who have ...
1
vote
2answers
167 views
Trying to understand MySQL Replication with SSL
I'm trying to get my head around the use of SSL with MySQL replication.
I've spent all day reading blog posts / articles about this, and I'm still no closer to getting an answer to this question:
...
1
vote
1answer
893 views
Status shows InnoDB: 128 rollback segment(s) are active waiting for purge to start
MySQL process was killed due to memory issues. When start command was issued, server started and is working but a status command shows the following:
InnoDB: 128 rollback segment(s) are active.
...
0
votes
1answer
238 views
MySQLI stored procedure return PK/AI insertID
I am using a PHP framework called CodeIgniter for my application. This is using the MySQLI driver for its database work.
This framework has built in support for things like a query builder where I ...
1
vote
2answers
493 views
Repeatable Read: Which one is correct?
Which one is correct?
With transaction 1 as Repeatable Read, other transactions can not update a row after it has been selected by transaction 1.
With transaction 1 as Repeatable Read, other ...
0
votes
1answer
411 views
How to gracefully handle MySQL deadlocks involving SAVEPOINT?
I have a concurrency problem where 2 (or more) processes are making inserts at the same time and are all locking the same index page (I guess) among with a on a SELECT FOR UPDATE clause
Here is a ...
0
votes
1answer
231 views
MySQL out of Memory, but Only Through MSSQL Linked Server
I am getting an error "MySQL client ran out of memory" when trying to do some simple MySQL selects or inserts from Microsoft SQL through a linked server. It was working fine for a week or so, and for ...
0
votes
2answers
17 views
Group by specific value and return results in generated column
I am attempting to figure out how I can pull this off, but I have no clue how to do it.
I have a table (below) that has object_id's, types and values. The values are always unique and the type is ...
1
vote
3answers
432 views
Significantly Slower DB Dumps on AWS RDS After Turning on Encryption?
Turning on encryption on an AWS RDS MySQL instance results in significantly slower database dumps (using mysqldump).
mysqldump -u test -p -h xxx.aws.com > dump.sql
Is there a reason for ...
0
votes
2answers
237 views
Unexpected SLAVE STATUS after CHANGE MASTER
I'm trying to setup MySQL replication but I am getting duplicate keys when I start the slave.
I've got master and relay. I've also got backup, which is intended to be slaved to relay, but my issue ...
0
votes
1answer
89 views
Which MySQL shows CPU usage?
I am monitoring MySQL server using phpMyAdmin [Status => Monitor] function. And it shows a number of useful graphs.
Which variables (or which mechanism) is phpMyAdmin using to pull this data?
ps. ...
0
votes
1answer
493 views
How do partitions handle new data?
I have a database with nearly 100M rows of stock data. Pulling the data for the last 7 days to generate a chart takes a couple seconds.
My thought is to create partitions that represent the normal ...
0
votes
1answer
16 views
GDPR compliant multitenant MYSQL DB
I wonder what are the GDPR constraint on a MySQL DB for a multitenant SaaS application.
While a solution with a single DB per each multitenant customer and restricted access will probably be fine, ...
0
votes
1answer
20 views
Select count date from multiple where conditions for each day
I have lot of coupons. I would like to learn for each day how many coupons from each campaign have been received by users. But I cannot write something like assigned_date for each select row
SELECT ...
1
vote
1answer
295 views
MySQL: Expanding table columns and insert/update values
I need to combine two tables into one.
Table s_ogr
id | ad | numara | yurt_id |
Current Table s_kontrol
id | ogr_id | yurt_id
What is needed s_kontrol_k
id | ogr_id | ogr_numara |ogr_ad | ...
1
vote
1answer
290 views
mysql to mongodb migration
I have table, with around 125 million records in MySql. I want to move it to mongodb.
What would be the best approach to migrate this huge table to mongodb?
I was thinking to fetch one by one row ...
