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
1answer
1k views

GRANT EXECUTE ON PROCEDURE unable to USE database

I am attempting to grant a user execute privileges to a single stored procedure without any other privileges in a MySQL 5.1.45 instance. The stored procedure is defined with SQL SECURITY as DEFINER. ...
0
votes
1answer
7 views

MySql timeouts waiting for table lock, no apparent deadlocks

I started having the following error on two different applications that write to the same database and table: Lock wait timeout exceeded; try restarting transaction At the beginning I thought it ...
1
vote
1answer
85 views

Select the maximum aggregate value for a limited set of chunked/grouped rows in a large database?

I work on WordPress plugin which implements sitemap functionality. One of the steps is to build an index of sitemap pages for post entries. The logic required is roughly: determine last modified time ...
0
votes
0answers
16 views

Managing large database tables, large tables VS seperate databases

In my project I have a system where users sign up and perform daily tasks. Note that I am not a database expert, none the less I am trying to solve a potential issue that may arise. A problem has a ...
0
votes
1answer
543 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
33 views

Improving query performance on mysql star catalog

I have a mysql database that contains a table with roughly 645 million records. I am trying to improve the query performance. Below is the SQL for the table as well as the indexes I use. CREATE TABLE ...
0
votes
1answer
115 views

MySQL Circular Replication

I have this scenario where I have 4 MySQL servers (A,B,C,D), as this: Server (A) is always online, and serves as the main website. Servers (B,C and D) are online on random periods of the day, and ...
1
vote
0answers
6 views

mysql replication and GRANT that doesn't quite grant

I am trying to set up mysql replication. All's well, but I see an error 1045 for the slave: mysql> show slave status\G *************************** 1. row *************************** ...
0
votes
3answers
70 views

MIN / MAX values with Date

I am trying to get the MAX and MIN values for the current month and the dates for these values. I am getting the MAX and MIN values but I am not getting the dates for these values. Instead I get the ...
4
votes
1answer
8k views

MySQL Slave replication: Need to determine where replication left off to start slave in correct location

Idiotically I rebooted a machine that is used as a MySQL slave without running STOP SLAVE, FLUSH TABLES first. I had thought MySQL automatically handles all of this during a machine reboot, but ...
0
votes
0answers
31 views

Updating rows in multiple different tables using JOIN

Following is my MySQL SELECT query and it works. SELECT candidates.CandidateName, candidates.CandidateVotes, candidates.Party, candidates.win, candidates.NameOrder, ...
0
votes
1answer
51 views

improving performance of concurrent inserts on innodb with randomized primary

I was looking at my design for primary keys which is based on my own incrementing BIGINT key generator. It is designed to generate batches of 50 numbers for use on that process, but mostly it ends up ...
0
votes
1answer
7 views

Mysql master slave communication

I was just working on mysql master-slave setup for incremental data backup from master to slave. For that I have to enable "bin log" in the master machine. So my doubt is how does master and slave ...
1
vote
1answer
39 views

Slow MySQL query with Group by

I have a query that is taking a long time to execute minutes and more. The two tables orders and tickets have around 150k records. This is the query: SELECT orders.id, count(distinct tickets.id) ...
0
votes
1answer
2k views

How can I get Performance Statistics to work in MySQL Workbench?

I have a complex query that is performing in subsecond time on my laptop, while the same query takes 70 seconds on my production server under no load against an identical schema. The EXPLAIN plan is ...
1
vote
1answer
26 views
+200

Replicating into a Percona XtraDB Cluster

We have a 2 node (plus an arbiter) Percona XtraDB cluster. It is used for data indexing/archiving. It has it's own database called archive but it is also the replication slave for a database called ...
1
vote
1answer
23 views

How to handle id's referring to multiple tables in an SQL database

I'm working on an MySQL Database for my website but I'm not sure on how to proceed now. I get to the point where actions have requirements in multiple other tables, but also have influences in ...
0
votes
1answer
143 views

How do I safely upgrade xampp without losing database i have created?

Also when i try to upgrade the with older version being installed, i get this error that port is being used or similar sorts. Do I complete installation by uninstalling? I am searching for complete ...
4
votes
1answer
1k views

SQL set profiling

I'm setting up an automatic database backup for a website. When I try to do a backup it comes up with the error" MySQL error: The 'SHOW PROFILE' feature is disabled; you need MySQL built with ...
1
vote
1answer
11 views

Is there a way to set bind_address from command line in mysql 5.5 without restarting?

I made a modification to bind_address in my.cnf that knocked down a monitor. It has been repaired since, firewall allowing the monitor IP in. However, mysql is still running in its previous state ...
0
votes
3answers
511 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 ...
1
vote
1answer
13 views

MySQL: with CREATE_USER_PRIV, GETTING “Access denied” when attempting to change another user's password

In MySQL 5.6.27, user 'bmcrae' has create user privileges and is able to create a new user. However, when trying to change another user's password: SQL: SET PASSWORD FOR 'newuser' = ...
0
votes
1answer
360 views

custom product variant upload

I'm building a custom eCommerce solution for a client. I came up with this database design: +---------------+ +---------------+ | PRODUCTS |-----< SKUS | +---------------+ ...
0
votes
0answers
32 views

Combine WHERE NOT EXISTS with ON DUPLICATE KEY UPDATE

I have 2 tables uz_params and uz_params_exceptions where uz_params is supposed to be updated frequently by an external script. INSERT INTO uz_params (extension, type, subtype, paramvalue) VALUES ...
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
0answers
22 views

How can I detect if a function is built in to MySQL/MariaDB?

Is there a way I can detect if a function is built in, in MySQL/MariaDB? I'd rather use feature detection than version string matching in my application. I know about the responses here for user ...
0
votes
1answer
22 views

MySQL binary log - write operation

Consider a scenario, where you have a MySQL master and slave server in replication. As we know, binary log enabled on the master and the slave reads from the binary log of the master. From MySQL ...
1
vote
1answer
52 views

Different types of users in one table, How to add data off different users

I have 5 type of users: Super Admin Coordinator Supervisor Student External Examiner All of them have some common attributes like ID, name, email etc. I have inserted them in one table user ...
0
votes
2answers
37 views

How to set TIMESTAMP default value to 1970-01-01 00:00:01 UTC?

How can I set the default value for a TIMESTAMP column to be exactly 1970-01-01 00:00:01 UTC without changing time_zone? I tried the following: CREATE TABLE `foo` (`ts` TIMESTAMP DEFAULT CONVERT_TZ( ...
1
vote
0answers
26 views

phpMyAdmin export SQL not working with Linux VPS import [on hold]

I am a newb, just gonna put that out there. I am currently on a shared hosting plan with Godaddy. I have started a free trial with DigitalOcean to see how I would fare with a Linux VPS using putty. I ...
1
vote
2answers
139 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'; ...
0
votes
1answer
15 views

Chained MySQL replication from Master->Master replicated pair

This is a pictorial view of what I have: Master 1<----->Master 2 | | | | V V Slave 1 Slave 2 The masters are never written ...
0
votes
0answers
24 views

Should I use mysql stored procedure or regular inline statement?

So here is the problem: I have been currently working on a project that would be handling millions of data and thousands of records would be inserted as well as retrieved at the same time. Before ...
0
votes
0answers
10 views

What is the best practice of storing mysql connection details to use in ruby/python scripts

I have ruby and python scripts that connect to mysql DBs. They are controlled in github and can populate to different servers. They run in a schedule way (not adhoc). What is the best practice of ...
8
votes
2answers
7k views

Mail alert from MySQL when databases are edited

How do I get mail from MySQL when root or someone else edits or updates a particular database? It should trigger sending a mail and log the edits. I have tried Triggers but its not working. Server ...
0
votes
1answer
115 views

MySQL table replication on the same server

I have one the same server (Linux) two databases: db1 and db2 and I want to replicate db1.table1 to db2.table1 I added the following lines to my.cnf and restarted mysql service replicate-do-table = ...
1
vote
1answer
175 views

Sync MySQL database and Quickbooks Database

I use Quickbooks as an accounting software. I also have a MySQL database where I record data on orders, customers, inventory, employees and etc. Is there a way for me to sync the two databases? For ...
0
votes
2answers
35 views

Encrypting a Database row-by-row

I have a large database (millions of rows in hundreds of tables) that contains sensitive data (Think SSNs, account numbers, etc). I am tasked with safeguarding the data and have chosen to encrypt only ...
2
votes
1answer
41 views

Prevent duplicate entries

I have log in form with Username and Password. $con=mysqli_connect(LOGINDETAILS); /*if (!$con) { die("Can not connect: " . mysql_error()); } */ $Username = $_POST['Username']; $pswd = ...
0
votes
4answers
42 views

MySQL: Delete all rows older than 30 days, but only if count more than one

I need to cleanup my table of over 14.000.000 rows inside. I want to delete all entries, that are older than 30 days, but only if number of grouped items is larger than one. So, at least one item will ...
1
vote
1answer
30 views

weekly moving average from current_date to the last 52 weeks

I'm a new head to the mysql programming. I have to write a procedure for calculating a weekly moving average grouped by week from 'monday-friday'. Criteria is : current_date is the end date. start ...
0
votes
1answer
34 views

(mysql) Database replication between 2 DB while 3rd DB as backup

I am not an advanced DB admin or designer. I am sure there are articles on this kind of scenario, probably too many. So I got confused and not sure which solution is appropriate/best for me. I am ...
0
votes
2answers
21 views

MySQL create index with LOCK=NONE still locks a table

I have the following MySQL RoR Migrations: class ReindexRpushNotification < ActiveRecord::Migration def up execute("DROP INDEX `index_rpush_notifications_multi` ON rpush_notifications;") ...
1
vote
2answers
46 views

Should MySQL database be in dedicated server?

The application has about 10 databases. Both application and databases are in the same server since is client does not want any time delay that could possibly occur if the database is in a dedicated ...
-1
votes
1answer
23 views

systems hangs after specific time

I am having 2 applications (Java and PHP). Both are sharing same DB Server. After specific time, both systems stops working due to "Too many connections". I tried so many settings (increased ...
3
votes
1answer
125 views

Which is better MySQL search strategy, varchar x 2 or text column?

I have a MySQL database for an SMS application. For the sms_msg column, I initially setup varchar(255) expecting SMS to be short anyway. Now I realize that's not enough and some messages are getting ...
0
votes
1answer
29 views

Storing revisions and relations between revised items in 1:N and N:N scenarios for audits

I have the several of the following situations in a MySQL schema that I design for a custom CRM/ERP solution. 1:N related tables SO and SO_ITEMS (SO is just one example I have several) that both ...
1
vote
2answers
36 views

Insert and select the row autoincremented id

What I want to do is insert a row and than select its auto incremented id number, In a multhi-threaded enviroment. I know how to do it with a Stored Procedure. Will this line of code work: BEGIN ...
0
votes
1answer
28 views

How to use FULLTEXT in mysql innoDB?

Sorry, if my question is naive, but it has puzzled me, and cannot benchmark the performance. I want to use the newly introduced feature of innoDB for FULLTEXT index. I wonder what is the difference ...
8
votes
1answer
19k views

On duplicate key do nothing

I am inserting into the following table using LuaSQL with PtokaX API. CREATE TABLE `requests` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `ctg` VARCHAR(15) NOT NULL, `msg` ...