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

0
votes
0answers
4 views

get datetime difference of a specific time and value of date time column

I have a datetime column in my mysql table. I need to get the difference between a definite time everyday say 12.00 pm and the value of that column.
0
votes
2answers
228 views

Logging the Execution times of dynamically constructed mysql stored procedures

I have a MySQL Server. The MySQL server contains a huge network data collection. The client API's will connect to the MySQL server and execute some dynamically created stored procedures and delete ...
2
votes
1answer
25 views

How to UPDATE by COUNT from JOINed tables

I have a schema as CREATE TABLE companies ( company_id int(11) NOT NULL AUTO_INCREMENT, company varchar(255), projects int(11), PRIMARY KEY(company_id) ) ENGINE=InnoDB CREATE TABLE employees ( ...
0
votes
1answer
33 views

MySQL Commands out of sync error

I am new to MySQL, normally use SQL Server. I have a MySQL server (version 5.1.73) and I have two databases, syslog and syslog_archive. There are three tables: syslog.logs syslog_archive.logs ...
1
vote
0answers
19 views

Open source & cheap 'data at rest' encryption solutions

So I am exploring some options about database encryption. The best options are commercial (TDE). I am looking for an open-source implementation. Recent releases of MySQL and MariaDB have data-at-rest ...
3
votes
1answer
60 views
+50

MySQL locking in Duplicate Key Error

From the docs: If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to ...
0
votes
1answer
7 views

Why is my statement results in a syntax error?

I tried to run this SQL statement in MySQL 5.5 and 5.7.1 but always got error. DELIMITER // CREATE PROCEDURE one_off_update_user_info_view_type() BEGIN DECLARE @batchsize BIGINT DEFAULT 1; ...
2
votes
1answer
40 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 ...
1
vote
1answer
37 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 ...
2
votes
1answer
144 views

Can we protect data form Database Administrator by encrypting the data?

How can we protect data on my database which is MySQL form database administrator stealing it? Will encrypting it prevent it from being stolen? If yes, how as he already has access to the database ...
2
votes
2answers
26 views

Is DROP/ADD UNIQUE constraint in ALTER TABLE atomic?

I have a UNIQUE constraint of a number of fields on a table. Image that I want to change the fields in this constraint (remove one field). Normally I would do something like: DROP INDEX unique_name ...
1
vote
1answer
270 views

Tuning Mysql and Monitoring

I would like to know if anybody can point me towards a tool/way for MySQL database tuning. Actually the tuning of my.cnf is like below: My database engine is MyISAM | Mysql 5.1.73 key_buffer = ...
2
votes
2answers
44 views

In MySQL, how exactly does data flows from query to disk?

I would like to have clarity on how exactly data flows from the the time of firing a query till it goes to disk. (I have rough idea it will go to buffers, then to redo logs then disk files) but then ...
1
vote
0answers
6 views

get max item in sub query join mysql

i have a table it contain buy of each user , other table contain a star ranking According to buys all user, my scenario very sample i create it and show in sqlfiddle . example if in table buy have ...
1
vote
0answers
20 views

Remove result set doesnt meet criteria

this is part of a query result : I'm going to add a constrain to my query to remove the orders that doesnt have an invoice_type = final ,so first three rows should be removed and the second three ...
1
vote
1answer
51 views

1054 - Unknown column in subquery

i have 2 table one store data of relation user other buy user path table +-------------+---------------+-------------+ | ancestor_id | descendant_id | lenght | +-------------+---------------+--...
19
votes
3answers
24k views

How can I change the default storage engine in phpmyadmin?

I use InnoDB almost exclusively in my applications. However, if I'm not careful when setting up the table, I forget to change it and phpmyadmin sticks me with MyISAM. Is there a way to change the ...
3
votes
2answers
3k views

Problem with new MySQL Workbench connection

I'm working on Debian 8 and wanted to create database using MySQL Workbench. I did everything in this tutorial, but when I created a new MySQL Connection, the program returned: Failed to Connect ...
2
votes
2answers
40 views

Efficient schema to handle time interval queries on a huge table?

We have huge table (+10 millions rows) where we aggregate the values by a using a time interval search on a Datetime column. And right now, to build single page on our app, we are querying this table ...
1
vote
1answer
14 views

Group by multiple column acquired from join

this is part of a query result : all three columns are user_ids acquired from multiple tables join . as you see each row has only one user_id and two other user_ids are necessarily null . I'm ...
1
vote
1answer
23 views

Simple Database Design - Many to many? - First database (newbie)

This is my first database I am designing. However I am getting confused on the many to many relationships. I have a products table, each product can have inventories in many different warehouses. So ...
1
vote
2answers
2k views

Mysql Deadlock on update query on same row

My server is experiencing occasional deadlocks on two update queries on same row both using index (primary key). The conflicting queries are: UPDATE auth_user SET rep_score=185 WHERE (auth_user....
1
vote
2answers
145 views

How to solve intermittent MySQL performance issues on simple queries?

We are currently experiencing an intermittent issue with UPDATE/DELETE/INSERT performance. Simple queries like the following take a long time: DELETE FROM ip_filter WHERE filter_id = '348'; ...
1
vote
0answers
10 views

Mysql multisource replication : on slave map masters dbname to something like mastername-originaldbname

I need to implement a mysql multisource replication using mysql >=5.7.11, there are already a certain number of mysql servers running which will become masters for the single slave which is going to ...
1
vote
2answers
37 views

Inserting BLOBs through PHP are always 0 bytes

I'm making a site that allows users to upload small thumbnails for books they post. I want to store them as a BLOB. The first row there was inserted manually through phpMyAdmin. The second row was ...
4
votes
3answers
73 views

Behavior of transactions on multiple connections

I have a MariaDB database with InnoDB tables. I have two connections to the same database. One connection is using a transaction around a large chunk of work. The other connection is tasked with ...
1
vote
2answers
71 views

Fix MySQL server crash [on hold]

We recently suffered a pretty severe server crash on one of our linux machines (Fedora Core 5), which has resulted in a lot of corruption of data, unfortunately the mysql database is amongst the data ...
0
votes
1answer
129 views

Not all MySQL processes are killed when restarting

We have a MySQL 5.6 server running on Ubuntu 12.04 using the MySQL 5.6 PPA. The service comes up fine when rebooting the box, but whenever we restart the MySQL process via /etc/init.d/mysql restart, ...
3
votes
2answers
1k views

Trying to recover/import/transfer database from MySQL data dir

I used phpMyAdmin->export to create a backup of my database but I have now discovered it did not export the whole database, some tables was missed/refused. However at the time of doing the export I ...
8
votes
3answers
4k views

AWS RDS MYSQL How do I reduce the DB size? [duplicate]

Possible Duplicate: How do I reduce the database size on AWS RDS MySQL? I have a mysql DB on Amazon. It is bigger than I need and I don't want to pay so much. How do I make it smaller? Their ...
0
votes
1answer
576 views

MySQL 5.6 replication causes 'waiting for table lock'

All of the sudden queries on slave server stopped with status "Waiting for table level lock" I restart mysql service and stop replications and locking does not show up anymore. Once I turn ...
0
votes
2answers
592 views

my.ini configuration for a huge server

I have developed a web based app in PHP MySQL and Flash Builder. It was running fine until a few days ago when the client began to connect a lot of users. Then the app started to freeze and sent ...
0
votes
2answers
319 views

MySQL-How to find that value in a column is subset with all other values in same column multiple time

I have a table named as Team where table contain 3 column (ID,Team,Task). Its datatypes are (INT,VARCHAR(255),INT). Below you will find a sample table where I would like to perform the query on column ...
1
vote
1answer
19 views

Referring alias name in subselect returns “Unknown column in 'where clause'”

I have following table: + ---------- + --------- + --------- + -------- + ------------ + ---------- + | Field | Type | Null | Key | Default | Extra | + ---------- + -----...
1
vote
1answer
490 views

How to calculate MySQL Transactions per second

How can I calculate MySQL Transactions per second. I tried to use formula (global com_commit+global com_rollback)/uptime since flush. I had also tried (global insert+ global delete+ global update)/...
1
vote
1answer
41 views

Some tables don't have primary key, how can I fix this issue?

I have the following message when running myqsltunner.pl in past this was not present. How can I solve this issues? -------- Galera Metrics ------------------------------------------------------ [...
1
vote
0answers
31 views

MySQL lost connection during query after specific record

I started getting the usual Error Code 2013 Lost connection to MYSQL server during query at a random moment when working on a PHP script. Some stuffs in the software still works, but the specific ...
0
votes
1answer
2k views

How do I change the MySQL data folder path in a Mac?

How do I change the MySQL data folder path in a Mac? I am running OS X 10.10 & MySQL 5.6 In Windows I can edit datadir in my.ini For Mac, mysqld does not have parameter for --defaults-file=
0
votes
1answer
28 views

MySQL database has way too many connections

I have a MySQL version 5.7.12 database that has 4 applications running against them. Two of these applications are single webservers running Rails apps. The other two applications are also Rails ...
-3
votes
0answers
40 views

Get user rankings based on points from database [on hold]

I've created a website where members collect points to get a reward at the end of the contest. I want to create a page that shows the top 10 members who have the most points. I've tried a few pieces ...
2
votes
0answers
46 views

Database design for auto-incrementing, grouped, unique hashes?

I've come up with a (hopefully) better way to save unique company data, which now consists of unique hashes for each entity. Basically, there are multiple hashes associated with a company: hash1: ABC ...
1
vote
1answer
13 views

Setting sql_slave_skip_counter is not working on Percona 5.6.24

I am currently running a master - slave replication where on my slave there is an sql error due to which my replication was stopped. when I tried to run the following good old solution SHOW SLAVE ...
1
vote
1answer
28 views

ERROR 1091 (42000): Can't DROP 'PRIMARY'; on “mysql_upgrade”

I updated MySQL from 5.5.50 to 5.6.31 on a Centos 6.8 box with Plesk 12.5.30. Using those steps. The upgrade was successful but I ran into trouble with mysql_upgrade. I had to rebuild the permission ...
1
vote
0answers
22 views

MySql - Update the defualt table schema on production server

The story starts with a MySql table called table on the production server: As you can see, I f***ed up when specifying defaults - 0000-00-00 00:00:00 is not a valid MySql datetime. I need to ...
2
votes
2answers
388 views

Java Spring application Database triggers auditing - how to provide the right user that made the change

We are developing a new web application. one of the most basic requirement is to audit all entities changes into a separate table. We would like to use DB triggers for that purpose. We use MySQL as ...
1
vote
1answer
27 views

How to restore MySQL databases from database files?

Our MySQL on Ubuntu 15.04 stopped working after we moved the computer to a new location and kept giving the missing socket file error. We couldn't really fix it after trying sudo apt-get install -f ...
4
votes
1answer
712 views

Stuck in “query end” status every 5 minutes

I'm sick of seeing data in the slow query log, so I decided to check out what the fuss was all about. An UPDATE query that is running up to tens of times per second is sometimes really slow. Here is ...
0
votes
3answers
529 views

MySQL - Alter Table Fails As Workbench Tries To Create Existing Table

I have been trying to add another column to a table in my database using MySQL Workbench. This generates an 'ALTER TABLE' command which then fails as it fails attempting a 'CREATE TABLE, which it ...