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
vote
3answers
697 views

Why does this procedure raise a privilege error?

I am having trouble getting the following procedure to run on a remote MySQL database - the given error is privilege based (#1227). Locally, the procedure runs fine. QUESTIONS Could somebody help ...
1
vote
1answer
17 views

MySQL to use all cores 20 cores I have

I am currently setting up a website that uses a MySQL database having more than 40 million rows of data. I am using one server for files and separate mysql server. Two servers are having 20 core, 64G ...
1
vote
1answer
166 views

Benefits of using prepared statements in stored procedures?

Except from helping avoid SQL injection, what are the benefits - if any - of using prepared statements inside a store procedure (assume query is only executed once in the procedure)? Does the engine ...
0
votes
1answer
105 views

How to group by continuous days in mysql

I have table where I am storing some sequential data that indicate occurrences of some event given day. id from_id to_id date 1 2 1 2014-08-01 2 2 ...
0
votes
2answers
62 views

Queries per second avg

I have Installed MySQL Server 5.6.23 on Centos server and the configuration of the server is 16 GB RAM 8 Core CPU op-mode(s): 32-bit, 64-bit When I check the status of the server it is showing me ...
0
votes
0answers
34 views

DBMS comparison [on hold]

I am interested in getting to know differences in some of the DBM systems. I am mainly thinking of MS SQL, PostgreSQL and mySQL, but am also willing to dig into some other DBM systems, like Oracle. ...
0
votes
1answer
118 views

InnoDB compression: memory usage

InnoDB offers table compression for Barracuda-type tables and the recommendation seems to be to "adjust" buffer_pool_size. The documentation is rather vague about the definite impact of compression on ...
5
votes
1answer
148 views

Find Shoe Size Selection

I'm developing a feature that allows users to input their foot dimensions and have the proper shoe size returned to them. I'm having difficulty deciding on the best way to implement this feature. The ...
1
vote
0answers
15 views

log_bin_trust_function_creators deterministic always throws an error on server restart [duplicate]

Everytime my rackspace server restarts because of some maintenance or an outage, all of my mysql functions stop working due to this deterministic error. To fix this, I have to manually pull up the ...
2
votes
1answer
53 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 ( ...
1
vote
1answer
32 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 ...
2
votes
2answers
65 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 ...
2
votes
1answer
308 views

InnoDB internals - checkpoint, LSN, dirty pages

I'm looking into InnoDB internals regarding buffer pool, checkpoint and dirtypage. However there isn't enough information to answer my question. When I run show global status, I can get ...
2
votes
1answer
39 views

Wordpress using varchar(255) for index with InnoDB and utf8mb4_unicode_ci?

I'm trying to learn a bit more about the details of proper database construction and ran across something that confuses me. In a current Wordpress installation, I'm finding varchar fields used for ...
0
votes
1answer
128 views

Cannot set character_set_database and character_set_server to utf8mb4

I'm trying to convert my MySQL server and its already existing schemas to the utf8mb4 character set. Preliminary Notes: I'm using Xampp (with MySQL 5.6.24). Default options are read from the ...
1
vote
2answers
36 views

Why does my statement result 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; ...
1
vote
1answer
46 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 ...
0
votes
2answers
845 views

MYSQL Row Count Limits?

What is the maximum row count for a table in MYSQL? I have a system with around 600,000,000 rows in three tables. Average Data Length (from data_length column) for these tables is 58,000,000,000 ...
0
votes
1answer
38 views

Set indexes for database used for login and register actions

I am thinking on how to set indexes to improve performance of a database for register and login actions.I have seen that alphabetical indexes are not good so I don't know how to do...Can you help me?...
0
votes
1answer
117 views

Want to create partitions of a MySQL table and change the charset of new partitions

I have a database table which contains large amount of data. I have written a script to create new partitions every week and drop old partitions (which are of no use). Currently, I have a column in ...
3
votes
0answers
78 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 ...
0
votes
1answer
133 views

Update records that match like regex from another table

I have two tables - ProductCategories and Products ProductCategories --- productid categoryid 1 2 3 4 Products -------- productid productname 1 Apple 2 ...
3
votes
2answers
54 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
2answers
7k views

MySQL shutdown unexpectedly

2013-09-09 10:21:44 5776 [Note] Plugin 'FEDERATED' is disabled. 2013-09-09 10:21:44 1624 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future ...
1
vote
0answers
21 views

MySQL keeps crashing

MySQL keeps crashing randomly - recent errors in error logs says this 160626 21:50:42 [Warning] IP address '113.108.21.*' could not be resolved: Name or service not known 160626 22:42:57 [Warning] IP ...
5
votes
4answers
3k views

Exporting/Importing a large MySQL database without mysqldump

I'm attempting to export about 150GB of data from a database with hundreds of tables, and have found mysqldump to be very buggy. After running a simple --all-databases dump succeeds, but the import ...
3
votes
2answers
4k views

One MySQL user from 2 hosts not on same subnet

I've googled around for this with no luck. I have a master database with one slave. The perms for the slave are set and it runs fine. I'd like to add another slave using a clone of the original server....
1
vote
0answers
30 views

What exactly is MySQL Spin rounds per wait in SEMAPHORES section?

I am facing slowness issue on MySQL Slave. I am suspecting this due to OS I/O as i compared Spin rounds per wait values with PROD. Slave have higher values. Also slave has less resources compared to ...
1
vote
2answers
427 views

Recommendation about mysqldump?

I have approx 90 GB sized database, and I need to schedule a mysqldump backup. How long will it take to perform a full hot backup approximately? I'm asking because I've never had to backup a database ...
-3
votes
1answer
52 views

Get user rankings based on points from database

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 ...
0
votes
2answers
230 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 ...
0
votes
1answer
36 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
35 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 ...
1
vote
1answer
38 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
146 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 ...
3
votes
1answer
33 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 = ...
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 ...
3
votes
2answers
51 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
18 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
31 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
11 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
74 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
75 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
130 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, ...