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

2
votes
1answer
4k views

Repairing Myisam Table when there was no additional Disk space & Table corrupted

I had a table which MyIsam as storage Engine contains million rows. There is a purging task and need to remove 35 days old data. i measured up the rows in following manner : mysql> select ...
2
votes
1answer
4k views

How to check the consistency of tables in mysql

I have few questions regarding consistency in Mysql in two scenarios 1th scenario We have two hosts with the same database A and B (the same data, different copy) if there any way to check if the ...
2
votes
3answers
3k views

How to detect a running mysqldump?

Here are the things I can't change: We got a mySQL myISAM replication running a master and some slaves. One slave is being used for nightly backups using mysqldump. The backups is using heavy locks ...
2
votes
1answer
963 views

Calculate percentage of false records to total records within a aggregate function

I'm looking for a solution to calculate a percentage rate of a boolean value: Model: Products 1 <-> n Components Components.essential is a boolean attribute What I want to calculate ...
2
votes
1answer
314 views

What's wrong with this update RANK query?

It was updated only the first time I ran it, after that the users_tbl_rank stays the same. $sql = "SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC"; In ...
2
votes
1answer
102 views

How to insert fields to different tables at once?

5 Main Tables: TEMP, PAYMENT, CUSTOMER, PURCHASE, and PRODUCT. TEMP: TempID, Email, Name, Product1, Product2, Date, Amount PAYMENT: PaymentID, Status, Amount, Date CUSTOMER: CustomerID, Email, ...
2
votes
2answers
1k views

Which first: upgrade mysql version or convert storage engine?

I inherited a production mysql server at 5.0. It uses MyISAM exclusively. It is not a high volume database but it does have some large datasets. I am planning on: upgrading to 5.5 converting ...
2
votes
2answers
572 views

Any problem will all InnoDB and one MyISAM table?

I have a MySQL 5.5 DB with all InnoDB tables. For some reason, one of the developers decided to create a MyISAM table. It is a log table for user sessions. I don't see why we should mix in a MyISAM ...
2
votes
3answers
193 views

Synchronise two databases (but not really)

I'm trying to design the following system. An arbitrary number of of embedded systems will each record data constantly and indefinitely. It is required that these devices upload their data to a ...
2
votes
1answer
3k views

mysql db backup excluding specific tables

Are there any mysql scripts for taking a database backup by excluding specified tables? Also, what is the best way to take backup of a running mysql system?
2
votes
1answer
1k views

MySQL - How do you enter a record with only whitespace “ ”?

I want to enter the record with white-space as a record in MySQL. I had tried to enter it through shell and phpmyadmin, the record enters but when I try to retrieve the record it returns as null. I ...
2
votes
2answers
141 views

How do I configure MySQL '5.1.49-1ubuntu8' to show multibyte characters?

I am using MySQL version 5.1.49 and I have now enabled UTF8 character encoding. The default character-set for MySQL is latin1. How can I change it show UTF8 characters? Even when I query a table ...
2
votes
1answer
738 views

Estimating query time

Is it possible in MySQL to know beforehand query execution time? I mean how long it will take to execute the query. Generally its worthwhile to know how long any query can take to execute. At the ...
2
votes
2answers
3k views

Does mysql SUPER privilege include ALL?

I'm granting the following rights to a user: grant super on . to 'user'@'host'; does this include the rights on dbname given by: grant all on dbname.* to 'user'@'host'; thank you
2
votes
2answers
474 views

MySQL Data Synchronization

I have a user who will be away from the office for a while, but still wants to be able to insert a load of records to our MySQL database. The user will have a laptop, but no reliable internet ...
2
votes
2answers
888 views

Is MySQL Replication appropriate for keeping a laptop in sync?

I've setup Mysql master slave replication with the production server as master. I have three other machines as slaves: 1 laptop, 1 desktop, and 1 server. The desktop and laptop both get suspended to ...
2
votes
1answer
9k views

store result set in temporary table, variable or separate variables within a trigger

I'm in the process of creating some triggers to store the the average rating for a given product on the product table. I've also been asked to store the total number of reviews for the product. So - I ...
2
votes
1answer
930 views

Query between two related tables

I have two tables. One of them (user_profile table) has a field named "user_profile_id" and the other one (user_friend table) has two fields named "user_profile1_id" and "user_profile2_id" which are ...
2
votes
1answer
284 views

databases/tables with nonconventional names

Disclaimer: not an administrator How does one deal with non-kosher names such as 'data?base' ? I'm always one to ensure the cleanliness of my databases, but after finally installing phpmyadmin to ...
2
votes
1answer
736 views

Question on Design for User + User Groups + User Types + Subscriptions Model

I am redeveloping an existing web application with both weak source code and a weak underlying database design. I'm trying to develop things in a way that future expansion will be much easier, ...
2
votes
1answer
446 views

MySQL MyISAM Table Locking and Delayed Inserts for Webserver Access Logs

We will install a webserver that writes access logs to a MySQL table. Access logs of course will get really really big, so this will be a separate MySQL instance from the regular data and we will more ...
2
votes
1answer
26 views

“TRUNCATE doesn't run ON DELETE trigger” true or false

I was searching on the topic of why TRUNCATE is placed in DDL instead of DML for which I found following answer here Why is truncate DDL? I this answer I found a sentence as given below "The fact ...
2
votes
1answer
54 views

Memory Table and Disk Table

I'm running a pretty big (or maybe I should call it SELECT & INSERT-intensive) database. Now I've heard here and there that a MEMORY-based table is better for my situation (since I now run on a ...
2
votes
3answers
65 views

Remove duplicates in sequence based on keys

I am looking to remove duplicates on a row by row comparison based on keys Member and Group and data sorted ASC on Member, Group and StartDate. Just return the records in the select query and not ...
2
votes
3answers
79 views

MySQL query takes over a minute to complete

I am trying to pull calls associated with website visits from our huge database... I have got SQL that works, pulls correct results it just takes over minute to complete... Here is the SQL SELECT ...
2
votes
2answers
37 views

Set variable with dynamic name in trigger (MySQL)

I need to create some kind of generic trigger that will alter value of columns which names are stored in another table. So basically I wonder if is possible to do something like this: delimiter ;; ...
2
votes
1answer
42 views

Why might a query not run until after I restart the MySQL server?

Why might MySQL not execute a query? Why might it freeze? It simply sits there and does nothing. Show processlist gives a result of "sending data" for as many hours as it takes for me to give up and ...
2
votes
2answers
57 views

Multi level hierarchy relationship

My table design is users (id, username..., parent_id) Data is id username parent_id ------------------------------- 1 | admin | null 2 | reseller 1 | 1 3 | client 1 | 1 4 ...
2
votes
2answers
74 views

SELECT with WHERE and CASE

I do not really know if this is possible to do this but I will expose my problem. I have two tables cases and progress cases +----------+--------------+---------------------+---------+------+ | ...
2
votes
1answer
48 views

Joining two fast subqueries is very slow

DB: MySQL 5.5.20 (WampServer) OS: Win 7 HDD: Western Digital 3TB Caviar Green, 3.5", IntelliPower, 64MB, Sata3 (WD30EZRX) Memory: 8GB my.ini: http://pastie.org/private/s7l9aadyi0qhfl5bcrnnqq I've ...
2
votes
1answer
68 views

Mysql Server 5.6 high memory consumption

I have a problem with my mySQL server. It consumes 9.7 GB ram. I have only a very limited amount of data on it (13 mb) I have been searching the internet and seval places a solution like this one ...
2
votes
1answer
111 views

Slave can not handle replication events with the checksum that master is configured to log

My slave is not connecting to the master and is showing the error Got fatal error 1236 from master when reading data from binary log: 'Slave can not handle replication events with the checksum ...
2
votes
1answer
37 views

Join two SELECT results with the same post_id value

I need to do a query to the MySQL database of my site built in WordPress / WooCommerce. The 'wp_posts' table keeps products, articles and more. Every row in 'wp_posts' has a 'post_id' to connect ...
2
votes
1answer
36 views

Selecting only from multiple tables in a transaction

I need to be able to extract rows from multiple tables in a way that I don't get half changed transactions from other users whilst I'm extracting data. The extraction will take some time (over 100 ...
2
votes
1answer
91 views

Convert MySQL database from latin1 to utf8mb4 - and take care of German umlauts

I am using 5.6.28 MySQL Community Server to host WordPress + ProPhoto blog in German language at CentOS 6.7 Linux server: mysql> show tables; +-----------------------+ | Tables_in_blog | ...
2
votes
1answer
31 views

Changing system time on MySQL server

I've discovered that my MySQL server is 10 minutes behind the current time because NTP was not running for some reason. It is a single node and running MySQL version 5.6.15-56. I know that ...
2
votes
1answer
39 views

Display equal ranking if count is the same

I am trying to figure out how to display ranking correctly where if multiple authors have the same article count then they get the same ranking and then the ranking resume at the correct spot. For ...
2
votes
1answer
72 views

How to maintain Database audits?

I am using MySQL database (with Django ORM). I want to maintain the database audits similar to StackOverflow, Quora, Wikipedia, etc. These websites maintain the revisions of the changes in the ...
2
votes
1answer
34 views

Table attribute for sorting

I have 3 Tables, a User table, a Tracks table and a Likes table. Users can like as many tracks as they want and one track can be liked by multiple users. In my Likes table I have two foreign keys to ...
2
votes
1answer
71 views

Disk I/O Utilization at 95%

My disk I/O utilization is at 95% at my MySQL server. What are steps I could take to fix this? It is a Ubuntu VP with 16 GB Memory and 8 cores. Here is a screen-shot of the load:
2
votes
1answer
72 views

MySQL performance problem

I have the following medium size table (less than 3M rows) on a 1 core, 8GB RAM server: +-------------+----------------+------+-----+---------+-------+ | Field | Type | Null | Key | ...
2
votes
3answers
128 views

UPDATE query periodically is slower by factor of 5000x

I run a site (Magento) which have a problem with a specific UPDATE query. Under heavy load, this query runs ~200 times/minute, and takes in average 1 ms (stdev 0.2 ms). However, at intervals of 1-2 ...
2
votes
1answer
54 views

How to match column names and values from joining and related tables?

I have the following DB structure: cars: id make features: id name cars_feature: id car_id feature_id value I want to be able to select all cars with all features and return results with ...
2
votes
2answers
45 views

error 1045(28000):anonymous user problem

I have removed anonymous users from mysql server but when one user trying to login from a specific host, current_user() still showing anonymous user. mysql> select user,host from mysql.user\G ...
2
votes
2answers
76 views

Average value of a row in MySQL

I have a row which primary key value is 4 and there are another 8 values. Those are 45 78 89 54 98 14 19 70. I want to select the average value, ie 58.37 from this row. How do I do this ? The ...
2
votes
1answer
53 views

Highest value of a row in MySQL

I searched and found many results using "Grep highest value of a row in MySQL" but I don't understand the results. I have a row which primary key value is 4 and there are another 8 values. Those are ...
2
votes
1answer
130 views

what happens when I try to insert data into mysql while table is locked?

Simple question but somehow impossible to find a clear answer: What happens when I try to insert data into mysql while the table is locked? 1) INSERT query fails 2) INSERT query waits until lock is ...
2
votes
1answer
44 views

Design for user profile information and track changes

I have a web page with a login functionality. Logging in is based on email addresses. Also, I have to keep basic user profile information like: street city zip code mobile phone country contact name ...
2
votes
3answers
107 views

create difference column of time series in MySQL

I have a table with 2 columns, the date and the value for the date, e.g. date value 2015-09-01 15 2015-09-02 17 2015-09-03 21 and I want to create a column with the difference between ...
2
votes
1answer
93 views

No mysql database in mysql server - how do I give users permissions?

I have a mysql server (5.5.44) running on my local Ubuntu 14 machine. I can start the interactive shell via $ mysql When I ask to see all the databases, there is only one mysql> SHOW DATABASES; ...