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.

learn more… | top users | synonyms

-1
votes
1answer
25 views

Server Specifications for Web Application [on hold]

I am trying to select the configuration for a web application built in PHP 5.5 and MySQL 5.1. I will also be deploying PHPmyAdmin for the MySQL database. Both the web application and the database will ...
0
votes
0answers
26 views

Are read replicas affected by heavy writtings in master?

This is my problem: Sometimes one long lasting query (>60 secs, and I have a lot of those) which is always a SELECT makes my whole application stops working properly up until the query is solved. ...
0
votes
1answer
18 views

Do dormant databases impact server performance?

On our web server, my team has been creating new databases in MySQL to manage backups. They simply create new database_DATE and copy over our live site. Which made me think today... Can having a ...
0
votes
0answers
24 views

Can InnoDB use a clustered index as a covering index?

The scenario I have in mind is a query like: SELECT id FROM table WHERE id = ? or equivalently, SELECT 1 FROM table WHERE id = ? So here's some background: In a B+ tree (which InnoDB uses as ...
0
votes
1answer
40 views

MySql How to rollback my database to a certain point in time the fastest way?

I'm currently working in a testing database and we are not cleaning up after test because of our current configuration and we probably aren't changing that configuration soon, we are currently using a ...
0
votes
1answer
20 views

MySQL issue - startup

I have Percona Server 5.6 running in linux platform.. recently I faced an issue where mysql instance was continuously restarting for no reason.. mysql logs had this error: [ERROR] InnoDB: File ./...
1
vote
1answer
20 views

PROCEDURE ANALYSE suggests to turn timestamp into CHAR(19)

Using Procedure Analyse() under MySQL 5.6.30 on a table with about 4 million rows makes the following recommendations: change timestamp to char(19) tinyint to ENUM (I noticed it's overzealous with ...
0
votes
0answers
30 views

Backup /www/ directory and all mysql databases

I'm trying to write simple shell script which will zip all databases + www directory. I don't know what is the problem but I've got wired output. I've got zip with .sql file full of hieroglyphics... ...
0
votes
1answer
61 views

Select distinct values of two columns as single [duplicate]

For starters: yes, I've seen there are lots of questions on combining two/multiple columns - but so far I neither found the answer to my question nor something to get me started trying further (which ...
0
votes
0answers
16 views

failed to retrieve rpm info for /var/lib/mysql/ib_logfile0

SELinux is preventing /usr/sbin/mysqld from open access on the file /var/lib/mysql/ib_logfile0. For complete SELinux messages. run Jun 21 16:35:39 localhost.localdomain python[17438]: SELinux is ...
0
votes
1answer
28 views

Multiple subqueries in columns with joins to return multiple rows

I have a table which stores id(primary key) of users and not their names (Names are stored in another master table) and also the the department id of user and not the name of the departments and there ...
0
votes
1answer
37 views

any better way for data archiving?

I'm in one security company and our history data grows fast, causing data archiving sqls run slower and slower. Say, for the sake of data real-time need, the scripts must run in a high frequency for ...
0
votes
1answer
35 views

mysql performance test

We are planing to test the performance of the new DB server. For that we have the below the plan: get production sql queries replay those sql queries against the different DB I was planing to use ...
1
vote
0answers
34 views
+250

MySQL Online DDL Index Creation

I have a table with 25 million records and 10 columns on which I am trying to create indexes. According to the following documentation no table copy should occur but when I monitor disk usage it is ...
0
votes
0answers
8 views

MariaDB / mYSQL Order BY PrimaryKey DESC vs DESC set by Default

What do you guys think about: After each query, I put "Order BY PrimaryKey DESC" VS I set "PrimaryKey Sort by DESC Default" and each query I don't need to put Order By there. Which one will be ...
0
votes
0answers
34 views

Trigger to Get Last Row Value and Set

I have been having a problem with this one. Granted this is the first trigger I have ever made (pretty new to this). I think it's a formatting issue. I have a table called filleradown. I need for ...
0
votes
1answer
48 views

How to SELECT cells from mysql for a HTML table? [duplicate]

I have data for individual cells of a HTML (or excel) table as CREATE TABLE table_cols ( column_id smallint(5) unsigned NOT NULL AUTO_INCREMENT, column_number tinyint(3) unsigned, title varchar(255), ...
2
votes
2answers
82 views

Will hacking away ibdata1 destroy databases with only MyISAM tables?

I made a mistake. I have a huge database of MyISAM tables that I was playing with converting [a copy of] to InnoDB. I didn't set innodb_file_per_table=1 before doing so. I know that safely shrinking ...
1
vote
0answers
24 views

MySQL dump only has SET commands, no data

I've done an export of a Wordpress database. The MySQL file that was exported only contains the Host, Database name, server version and SET commands. Pastebin: http://pastebin.com/pFgdPDRa Another ...
1
vote
1answer
29 views

How should I configure mySQL 5.7 on Ubuntu for a physical Hot Backup?

I recent upgraded my Ubuntu 14.04 server to Ubuntu 16.04. I managed to get mySQL upgraded to 5.7. I don't currently have anything in the mySQL instance. Since I have been an Oracle DBA for almost 20 ...
0
votes
0answers
15 views

Mysql arabic text [on hold]

i have MYSQL DB on WAmp server, and a php website, if i enter arabic text from the php website it appears in arabic only at the php website, and appears like this on phpmyadmin هو و انا, and if ...
1
vote
0answers
24 views

MySQL Temporary Tables

I am facing Memory issue in our server. While debugging I came to know that, many number (millions) of temporary tables are created. Is there any way to identify which databases are creating this ...
-4
votes
1answer
40 views

Insert and Update a sql table at same time is not working [closed]

I am facing an issue with my SQL DB. I have a stored proc which inserts data into a tables [Table1],[Table2],[Table3]. But when I tried to update some data which already exists in those tables at the ...
1
vote
0answers
37 views

MySQL – BLObs Hitting Performance

One of my tables contains seven BLObs. Six of these refer to raw data and are typically 1.0 – 1.5Mb each. The seventh is the result of processing this data and can be nearly equal in size to the sum ...
1
vote
1answer
26 views

how to increase column values by 20 percent using mysql

How do i increase the values in a column with percentage increment using MySQL, for example: ID price 1 500 2 800 3 450 How do i increase the values in price column by 20% ...
3
votes
2answers
33 views

Need a help in Query to check whether Task is completed by other user or not before showing on dashboard

I have 3 tables in database Task, User & assignedTo. Task contains information of Task created by Admin, User is list of User & assignedTo contains information about which task assign to ...
1
vote
0answers
42 views

Keep one row per ID while keeping previous order (from ORDER BY)

I'm actually working on a big query and stuck at the very end. I'm working on MySQL 5.7. ID // SubID // Criteria 1 // Criteria 2 // ... // Critera n 1 // 2 // x // y // ... // ...
1
vote
2answers
48 views

Converting from InnoDB to InnoDB by file

I run MySQL and I have 7 schemas, 6 of the 7 schemas are MyISAM. In one of my schemas I have 20 tables that are InnoDB, the rest are MyISAM. I really want to convert those 20 tables over to InnoDB ...
1
vote
0answers
16 views

Command wbcopytables --table-range not working

I am migrating sql server tables in MySQL using wbcopytables command. I want to copy a specific range(id 1-10000) of data from MSSQL to MySQL, so I am using below syntax. wbcopytables --odbc-source="...
1
vote
1answer
48 views

1054 - Unknown column in subquery

i have 2 table one store data of relation user other buy user, in my senario must compress realtion with at last 1000$ path table +-------------+---------------+-------------+ | ancestor_id | ...
3
votes
2answers
41 views

How can I lock down my own database

I collect some personal information from users of my website and according to German law, the storage of this data is only allowed with consent of the user. I programmed my site in a way, that the ...
0
votes
0answers
13 views

get minimum from result with group by mysql

i have table it store hierarchy data in mysql this table store stable relation but if each user les than 1000 buy removed and user User a lower level replace thia is my code and work fine, after qroup ...
0
votes
1answer
21 views

Errors declaring variables in stored procedure?

I'm sort of new to SQL and stored procedures. I'm trying to set some variables to use later on in a transaction while also setting a rollback variable on an exception, but I'm not able to do this. I ...
0
votes
0answers
101 views

How to provide referential integrity in databases? [closed]

Previously I've used MS Access, so such databases as MySQL and Oracle is confusing for me. I want to know how to provide referential integrity, in Access it was easy (there you should bind tables by ...
0
votes
0answers
22 views

How to automatically update a table derived from another table when the latter is updated

I have a table from MySQL called References with 20 columns and 20 rows: CREATE TABLE bop_ppy ( start_time DATETIME (6) , end_time DATETIME (6) , high DECIMAL (5,2) , low DECIMAL (5,2) , close ...
1
vote
1answer
34 views

How to UPDATE NULL values by MAX()+1?

I want to assign values to null cells of a column as UPDATE table 1 SET number = MAX(number) + 1 WHERE number IS NULL but mysql return error Invalid use of group function.
0
votes
0answers
18 views

Import databases using /var/lib/mysql

I've got some issues with my server, leading me to completely reinstall my debian OS. Before reinstalling, I backed up some files on the server and now it reveals that I have a backup of my previous /...
0
votes
0answers
27 views

Does max_allowed_packet shows in bytes in query result?

We have 2 different same version of mysql server both max_allowed_packet set to 1G. The only difference is max_allowed_packet is set as 1G and the second is 1024M. The 1024 setup encounters and error ...
0
votes
1answer
34 views

How do I get mysql to start for the first time?

I want to get mysql to start for the first time. I installed it on RHEL version 6. All of the commands posted were executed as root. As a test, I ran this: # yum install mysql This is the output:...
1
vote
1answer
21 views

Rows Holding the Group-wise mysql

i have simple table and need to group by duplicate items , also i need to get min with holding row i read this tutorial work , but not sum grouped item level http://dev.mysql.com/doc/refman/5.7/en/...
0
votes
0answers
51 views

MySQL: Log only 'select' queries executed on a specific set of tables of a database

I have a Magento site in which I want to log only SELECT queries(after SELECT it could be any column or all columns, in short all SELECT statements) executed on a specific set of tables, also as it's ...
0
votes
0answers
22 views

InnoDB LSN in the future - how to insert the “required amount” of data?

Please forgive me - I'm not a DBA, I'm a lowly devops whose mysql DBA went on vacation the day before this happened and is gone for 10 days. I'm learning as much as I can about mysql/innodb/LSNs but I'...
0
votes
0answers
32 views

MySQL insert with sub select slow

We are using Python and LOAD DATA INFILE to load data from CSVs into our staging database. From staging we have sql scripts moving the data into our actual production database. LOAD DATA INFILE is ...
1
vote
1answer
35 views

Setting up Master-Master Replication in MySQL on a live system

I've just recently taken on the task of looking after our mySQL databases, and have been given the task of setting master-master replication. We have a live server which has a mySQL database which is ...
0
votes
1answer
24 views

MySQL Semi-synchronous replication with Multi-Master

Is it possible to use semi-synchronous replication with a Multi-Master setup? I've tried to follow this guide to setup a semi-synchronous replication for a master-slave setup: https://avdeo.com/2015/...
0
votes
0answers
44 views

Mysql: slow down with order by, global database problem

i create nasty query for testing reasons. Only for speed testing. EXPLAIN SELECT m.post_id FROM wp_postmeta m LEFT JOIN wp_postmeta mi ON m.`post_id` = mi.`post_id` LEFT JOIN wp_postmeta md ON (md.`...
0
votes
1answer
39 views

how to deal with mysql 500 million data per month?

this is a interview question a telecom billing system, there is a internet flow table (phone_number,start_time,stop_time,time_lasting,fare),500 million records per month(2G),every record size is 300K,...
0
votes
0answers
34 views

Matching Custom Exported CSV File with Main MySQL Database

I recently selected certain columns from a table from a huge MySQL db >10GB into a csv file and then filtered through the rows. I would now like to retrieve the rest of the data matching the filtered ...
0
votes
1answer
6 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
15 views

update all geometry points by adding/subtracting from XY in mysql

I want to update the POINT geometry column for all rows in a table. For example, I want to change POINT(1 1) to POINT(1.1 .9) UPDATE myTable SET geoPoint = asText(geoPointX+1 geoPoint Y-1) The ...