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
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 ...