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
0answers
66 views

Is not having 'named' timezone support common in MySQL?

I'm trying to set the timezone the database uses using: "SET time_zone = 'UTC'; Whilst this works on my development server it doesn't work on my staging server; I get this error: General error: ...
1
vote
0answers
42 views

Organizing databases and tables in phpMyAdmin

I have 2 servers, on one server the access6_identical sql file is on the second directory under identical as can be seen here: On the other server, it is on the parent directory as can be seen ...
1
vote
0answers
28 views

MySQL/Percona Audit Log Plugin Not Working

I've been looking to implemement the Percona Audit Log plugin. So far I have installed it and configured it, and it seems to be working ok. Except, I have installed it on a slave Database as I don't ...
1
vote
0answers
52 views

Waiting for table level lock on wordpress site

We are experiencing issues with mySQL loading 100% of the server's CPU. While checking the running processes we found many of the following: Waiting for table level lock | SELECT option_name, ...
1
vote
0answers
60 views

Space usage when copying

Short version Copy table took up more space than I expected or tested Why would that be? Could it be the replication / binlog? If so, what then is written to the binlog during this query: INSERT ...
1
vote
0answers
173 views

Update remote MySQL database upon changes in local SQL Server database

I have a Web store running with MySQL on the remote host. And I have inventory management software using Microsoft SQL Server on my local system. I want to update product stock in the web store when ...
1
vote
0answers
75 views

MySQL server slow due to too many information_schema queries

Recently our server has been slow due to too many slow information_schema queries, which I am unable to kill. Please see the screenshot below: These queries are taking over an hour to run each. ...
1
vote
0answers
43 views

Replicate existing database live

New to db admin here and I have a question. My setup is Windows Server 2012 R2, MySQL, using MySQL Workbench. I have Database A on Server A, how can I make an exact clone of this database (Database ...
1
vote
0answers
57 views

insertion creates duplicate with same data

I am using MySQL with PHP. I fetch data from a third party using SOAP. I have an issue with duplicate insertion of data. This occurs once or twice a day. Say there are 10 records ready to enter into ...
1
vote
0answers
163 views

MySQL Master-Master Crash-Safe Replication: Slow UPDATE times

Environment 2 MySQL version 5.6.26 Dedicated MySQL Servers connected on a LAN. Servers Have 16 Cores with 32 GB RAM each and are really fast. Master-Master Crash Safe Replication is setup on both ...
1
vote
0answers
192 views

MySQL Server Instance Configuration Wizard does not start for the first time

I tried to install MySQL on my machine (win 8 64 bit). I downloaded mysql-5.6.24-winx64.exe and after completing the MySQL 5.6 setup wizard, MySQL Server Instance Configuration Wizard does not start ...
1
vote
0answers
90 views

mysqld stops right after starting because of corrupted tables (disk full + hardware reboot) - how to fix it?

I'm dumb, I did the stupidest thing I could. On one of my servers, the SSD was full and I wasn't aware of it. The high-traffic website became working really slow, then started displaying errors, ...
1
vote
0answers
173 views

Update row value with a sub-select join

I am trying to update 1 column in all rows in a table to have an appropriate count value. Sadly, the SELECT query I am using won't let me join on a field in update table. The query looks like this ...
1
vote
0answers
121 views

Slow Tally Tables

I have a pain point with a relatively large application concerning 'tally tables', which are used for keeping track of the number of items against 'campaigns' (placed by a client), and 'publishers' / ...
1
vote
0answers
58 views

MySQL deadlock in simple table

I'm having a deadlock issue that I don't get. Possibly it's because of the composite index (idSite,timestamp). Maybe it's range locking on it? I have this table: CREATE TABLE `logs` ( `idLog` ...
1
vote
0answers
58 views

Tuning MySQL MyISAM for large joined updates

I have a database with two tables that I would consider small. I need to update values on the larger of the two tables (1,100,000 rows, 75M MYD, 120M MYI) from the join of the two tables. The smaller ...
1
vote
0answers
28 views

Caching impression counts to avoid mysql inserts

We currently get a LOT of mySQL inserts/updates on duplicate daily (millions) and we're looking at speeding up the application by using some form of counter with memcached and then clearing it on ...
1
vote
0answers
96 views

Get average of multiple rows with criteria and return those rows

I'm attempting to get any items that are the same type, but are ranked one higher that have an average equal or greater than the item ranked below. I have a table and query like ...
1
vote
0answers
26 views

How do you output field changes in mysql the most efficiently?

I have a rather large employee feed coming in and each employee has about 50 fields. I take the employee data and add on a bunch of other stuff and this gets outputted to one of our vendors. Most ...
1
vote
0answers
350 views

Select records where difference between datetime column1 and column 2 is greater than X amount of time

As the title indicates, I'm trying to return records where the datetime difference between two datetime columns is greater than X amount of time. I'm having trouble wrapping my head around this ...
1
vote
0answers
103 views

Single Redshift table vs several mysql/ pgsql (on RDS) tables for small (200 tables * 20 columns * 1 million) monthly aggregation data

We're providing SaaS Data Analytics Dashboard for around 200 clients. We're using Amazon Redshift for storing data in in around 5 tables. We're getting around 30 million hits per day. Currently our ...
1
vote
0answers
42 views

I need to find the “rank” in a sub-set of entries

I am currently working on some sort of a highscore list. But values displayed are individual for each user, ie. limited by constraints. One constraint that I am currently working on is "being part of ...
1
vote
0answers
73 views

How to pass the value of PK to FK

Ok, so here it goes. I have a table called employee. then I have another table called languages. I need to copy the value of PK of employee table to foreign key in Languages table, rather than ...
1
vote
0answers
144 views

Master/Slave and Master/Master replication - MySQL

What is the difference between having a master-slave replication and having a master-master( where in the second master acting as hot-standby - active-passive) replication? Also, what are the issues ...
1
vote
0answers
25 views

Migrating tables from SQL Server to MySQL

I am using MySQL Migration Wizard to copy some tables from SQL server to MySQL local instance. When I define the source and target connection and test them I get a Connection Successful message and ...
1
vote
0answers
273 views

How to find where an unauthenticated user connects from

We are seeing some "unauthenticated users" when I run the command show full processlist in mysql (v5.6). | Id | User | Host | db | Command | Time ...
1
vote
0answers
61 views

MySQL Get first and last day of multiple records

I have a table HC_RSPI_data: | RADIO_TYPE | HOP | TOWARDS | RSPI | TP_NAME | DATE_TIME_END | TIME_LENGTH | RLTM_min | RLTM_max | ...
1
vote
0answers
163 views

Ticket booking engine

I am going to build a flight ticket booking system. My client has own planes. Under is basic concept of engine. Admin will add flights. Passengers, agents, franchises or admin will book the tickets. ...
1
vote
0answers
66 views

DatabaseRestart

I have strange problem, we are using circulation replication, between 2 different network with 5.6.25 and 5.6.22 version of MySQL and RAM is 64GB with 45GB innodb_buffer_pool Master Server is running ...
1
vote
0answers
18 views

Optiming ACT_RU_JOB Query

We're using Activiti version 5.15.1 with timers in our workflow which sends reminders to users who are in the workflow. Problem is that we're creating jobs in ACT_RU_JOB table and even though there ...
1
vote
0answers
39 views

Can an insert procedure trigger unintentional loops?

Is it possible for a stored procedure like this to produce a loop of unintentional inserts? I'm asking since this exact procedure produces this resulting loop. (I'm pretty convinced it isn't within ...
1
vote
0answers
45 views

Speeding Up Query - Hangs at Sending Data - Indexed Correctly

I am looking for ways to speed up the below query. I currently have indices on FIRST, LAST, UNIT_PARENT, HomeAddressLine1, HomeAddressLine2, HomeCityNAme, HomeStateAbbreviation, HomeZipCode, HomeZip4, ...
1
vote
0answers
33 views

Taking Backup of a database in mysql

I have database but somehow it has been deleted. Can i take the back-up of deleted database. Please Help. Thanks In Advance
1
vote
0answers
48 views

Migrated servers… Innodb tables stayed innodb, but database is showns as MyISAM. Problem?

I moved servers. I exported DB on old, imported to new. Kept getting syntax error on import until I tried no compression. Weird, but it worked. I was skeptical so I compared the two carefully and ...
1
vote
0answers
87 views

Is it a good idea to have too many named locks?

I am using MySQL as a data store and a Java App on the top. There are many state changes that happen, and I am seeing some concurrency bugs in there. I wanted to know if I can use named locks ...
1
vote
0answers
30 views

prioritize one query over the other in mysql

Is it possible to prioritize one thread over the other in mysql. The scenario that I'm trying to address is as follows :- 2 threads T1 and T2 are both trying to acquire a lock on a table. T1 is a ...
1
vote
0answers
52 views

selecting from information_schema increases free disk space

Some background: There are 3 database servers - two in a master/master replication config with only one server accepting writes. And one other slave replica replicating from the write-master. The ...
1
vote
0answers
47 views

Creating a dynamic database

I'm using mysql server connected to Access, and I want to do a form, to fill the table. My question is how to do it. I need to select the data from a listbox, that's okay this is already done. And I ...
1
vote
0answers
61 views

Get all data from two tables and group it into different category in mysql

These are my tables to build an FAQ: CREATE TABLE `tbl_section` ( `section_id` int(11) NOT NULL AUTO_INCREMENT, `section_title` varchar(100) NOT NULL, PRIMARY KEY (`section_id`), ...
1
vote
0answers
72 views

Keep selected tables synchronized across MySQL databases

I've a need that requires me to keep a number of tables from a database (db1) synchronized with another database (db2). db1 & db2 have different structures and a set of tables and their data from ...
1
vote
0answers
200 views

How do I restore a mysql dump that was created in xml format?

I have a rather big XML formatted MySQL dump It was created with the --xml param (-X) param of mysqldump. I need to import it in. This article from 2010 indicates that there was no "built in" way ...
1
vote
0answers
55 views

Complicated MySQL table reorder

I should do really complicated tables reorder. ****The main table looks like:**** id - some data 1 - ... 2 - ... 3 - ... 6 - ... 9 - ... 12 - ... As you can see, there are "empty" IDs (no 4, 5 ...
1
vote
0answers
103 views

MySQL: Using DATE_SUB to subtract business days instead of all calendar days

I am looking for a robust solution to subtract the number of business days from a date. I am using DATE_SUB however it subtracts all calendar days. For example, if today is Monday and I subtract 2 ...
1
vote
0answers
12 views

Adding new sproc to the exisiting database using connectors?

The project I'm currently working on is mostly pythonic, by this reason I'll likely use mysqldb python connector or sqlalchemy. The question arose when I've tried to find out the way of adding new ...
1
vote
0answers
54 views

Mysql master slave performance

Im running a mysql database on a linux server with 24gb ram and im trying to run a performance test of my system. Now the thing is, when im running it without a slave replication and in particular, ...
1
vote
0answers
46 views

Mysql Connector C++ data retrieval question

So, the result of a query is stored in a ResultSet. And that type has multiple methods for data retrieval such as getString, getInt etc. I'm curious how the data is stored in that object. Is all the ...
1
vote
0answers
58 views

Unique ID for related records in a MySQL Table

I have a requirement to allocate a unique RecordID (ImportID) to multiple rows in a table. We are importing a text file which contain related transactional information but variable record types and I ...
1
vote
0answers
89 views

Database design/layout

I have an objective to record and store test data. A secondary objective is to track the progress of the test programme (it is a large programme). So I want to use MySQL (not Access or DOORS) to ...
1
vote
0answers
291 views

Why my sql query run faster than my stored procedure?

I am trying to write a script in MYSQL Workbench to update my database every day using stored procedure but I find it too slow compared normal query or temporary tables. If i use temporary table the ...
1
vote
0answers
201 views

MySQL - Preventing lock wait timeout when deleting rows from self

I have a table of around 500k rows (not big data), that I need to query against itself and then delete some of the rows which don't match the filters. At the moment my query looks like this: DELETE ...