MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
0answers
22 views

Need advice on optimizing database on a recurring basis

I have a search engine with approx 3.5 million rows in the searchable table. There are many suppliers that feed into the table each day, so primary keys are constantly being removed and added and the ...
1
vote
0answers
47 views

Handling this multi-threading scenario in the database?

I am working on sample bus ticket booking application,in I am facing these two multi-threading scenarios. Scenario 1 1: Let's say there are two users, they both pick a particular seat and submit ...
1
vote
0answers
21 views

MySQL - Transactions and causality

I'm new to MySQL's LOCK and TRANSACTION and have a question about different isolation levels of a transaction: Given two transactions executing the same code (auto-commit is false): (1) get data of ...
1
vote
0answers
86 views

Handling SQL updates for production deploys

I'm researching for quite a long time now (about two months) and haven't found a good approach for this, so I'll ask you experts out there in the hope of some enlightenment. I run a pretty ...
1
vote
0answers
43 views

Can't seem to optimize joined select query

Can't seem to figure this one out. Query is taking upwards of 7-8 seconds which is just not acceptable and seems as though it will get worse as the table(s) get larger. Without going into a ton of ...
1
vote
0answers
77 views

MySQL table partition strange behavior (slow query suddenly)

(I asked this question in "stack overflow" and was recommended to ask in here). (MySQL version: 5.6.15) I have a huge table (Table_A) with 10M rows, in entity-attribute-value model. It has a ...
1
vote
0answers
56 views

MySQL not using multicolumn index on self join

I'm trying to optimise a query that performs an inner join with itself. The table size is currently 10GB ~50 million rows. /* Count all occurrences between start and end site id of a given speed ...
1
vote
0answers
323 views

Lost Connection to mysql server at 'reading authorization packet', system error 0"

OK, this has been bugging me all afternoon now, I have a new MySQL Database '5.5.40-0+wheezy1-log' running on Debian Linux. It is to all extents and purposes a clean install. I have created two ...
1
vote
0answers
27 views

Any way to mitigate the large server load during LOAD DATA LOCAL INFILE operations?

I'm using MySQL LOAD DATA LOCAL INFILE to load csv files, and have noticed that during these loads, server output speed to other users diminishes eg long page download times, logins timeout. The LOAD ...
1
vote
0answers
57 views

Skipping stopwords (and indexing small words) for one fulltext index only, in MySQL

I have a rather odd situation where it might be preferable to not use stopwords and to have a value of ft_min_word_len equal to 1. The index in question would be dealing with only a few words per row ...
1
vote
0answers
72 views

ORA-00937 when calculating a sub-value in an aggregate select statement

The problem described below applies to Oracle 11g databases. I also tested the statements on MySQL and PostgreSQL with no problems of the kind mentioned in this question. I want to use an ...
1
vote
0answers
93 views

MySQL permissions or privileges (User does not have access to metadata required)

I am using MySQL and I need to give one user to web service for access database. The web service need all permission related to stored procedure, like procedure execution. I executed the following ...
1
vote
0answers
116 views

“Unexpected EOF” during SELECT on huge MyISAM table

When executing a SELECT query that uses filesort on a huge table (2 billion rows) the execution stops after ~30mins with the following error message: ERROR 9 (HY000) at line 1: Unexpected EOF found ...
1
vote
0answers
198 views

MySQL master slave replication not working

I had MySQL master slave running and working fine but suddenly it stopped. I don't know which commands have been executed but now replication is not working. I am very new to MySQL can anyone help me? ...
1
vote
0answers
158 views

How can I correct MySQL replication while using federated tables?

This is an overview of what I have going I have 2 server A (10.0.4.22) AND B (10.0.4.29) A is a replication of B and B is a replication of A (yes it is a ring replication) ON A I have 2 table uat and ...
1
vote
0answers
41 views

Combining fields to make a unique record or keeping them separated

I've been asked to implement what has before, been a bunch of CSV files, into a MySQL database. I'm at a point in the design where all the schemas, tables and fields have been mocked-up; however I'm ...
1
vote
0answers
45 views

Copying to tmp table slow for query … but only sometimes

I have a query, and I admit it may not be the prettiest query in the world. Sometimes this query runs fairly fast (e.g. 200ms) and other times it runs super slow (e.g. 30s). I don't know why, and ...
1
vote
0answers
31 views

MySql Audit Changes from Db Admin, Security

I know that you can use triggers (before insert, update, delete) to track the changes in history tables. But this only works if the changes are made by the applications. But in some circumstance, ...
1
vote
0answers
29 views

How to calculate geographic distances using MySQL

Given a MySQL table with a series of latitude and longitude points, stored as strings, how can I find the distances between each point in the table and a given point, call it "originlat, originlon" ? ...
1
vote
0answers
20 views

Conversion to Barracuda/Compressed Rows Doesn't Free Disk Space

I'm currently converting large tables to the Barracuda file format. We are using file_per_table. I had one table that decreased from 4.3gb in size down to 1.8gb and we're very happy about this. ...
1
vote
0answers
92 views

Best practice for storing image order number in a database

I am designing a social networking website(mysql with InnoDB engine + php) where I need to store image sequence in a database. The table structure is as follows:- PicId (BIGINT, AUTO INCREMENT, PK) ...
1
vote
0answers
73 views

Indexed query fails to return rows in large (15 billion rows) MyISAM (MySQL) table

I have a very large table (15 billion rows) with a structure like this: CREATE TABLE `textlines` ( `PGUID` binary(16) DEFAULT NULL, `StatID` int(11) DEFAULT NULL, `Time` datetime(3) DEFAULT ...
1
vote
0answers
60 views

Tuning MySQL On Azure Windows Server Setup

I have a MySQL 5.5 running on Azure VM Windows Server 2012. Once per second the log db gets about 2K inserts through LOAD DATA INFILE... once a day I need to run stats and when the stats are crunching ...
1
vote
0answers
139 views

Database Migration with Liquibase using different DBMS

Our customer is using Oracle and we want to use MySQL for development. Therefore our MySQL Scheme has to be in sync with Oracle. I tried to use Liquibase, but I get problems at the time of applying a ...
1
vote
0answers
45 views

Mysql syntax error when i add on completion preserve

I have written this event in mysql DELIMITER $$ CREATE EVENT le_drop_database ON SCHEDULE EVERY 1 WEEK ON COMPLETION PRESERVE STARTS CURRENT_DATE + INTERVAL 4 - ...
1
vote
0answers
52 views

Many small queries or one single query

This is not exactly a question, more of a survey. Here at BlackBean we have a time series aggregation and analysis platform built on top of MySQL, where each client have your own database, and each ...
1
vote
0answers
44 views

MySQL Memory & max_connections Performance Tuning

So, our site runs WordPress and in the past few days we have been getting tons of spam comments etc. I believe that we are becoming the victims of some kind of bot army that has been crashing our ...
1
vote
0answers
162 views

How to measure bandwidth requirements for MySQL, Postgres, …?

TLTR: how to configure MySQL/Postgres to get a fair measurement of the I/O requirments for analytical queries? Hello, for certain database and especially database hardware experiments for want to ...
1
vote
0answers
26 views

Why do these two queries (which I think are equivalent) rely on different keys?

We have a query that we don't like, since it ends up doing a full table scan. EXPLAIN SELECT count(*) AS c, location.name FROM study LEFT JOIN location ON study.location_id = location.id ...
1
vote
0answers
174 views

Problem when using gtid with MySQL replication

I have faced to problem when using gtid in MySQL replication: "When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check ...
1
vote
0answers
82 views

One table for article comments, video comments and photo comments or multiple tables?

First DBA question. Greetings people. I hope I'm not duplicating a question but it's very hard finding database design related questions here (for multiple comment tables), as all 'design' questions ...
1
vote
0answers
392 views

Replication of a remote MySQL database to local MySQL database

I'm the developer of my employers corporate private website, a HTML5/PHP/CSS powered site for company reports etc. The site, hosted on a shared hosting package, connects to a remote read-only MySQL DB ...
1
vote
0answers
71 views

MySQL getting killed by AppArmor

I have copied this link from StackOverflow as it is marked as out of topic there and is waiting to be transfered here. So, here it goes: Occasionally, without any identifiable pattern, MySQL ...
1
vote
0answers
34 views

pt-archiver and foreign keys?

How does pt-archiver handle foreign keys? Does it archive associated data? (seems not but the documentation is not very clear to me) And if not, how could one perform an archive of a table with all ...
1
vote
0answers
67 views

MySQL WRITE LOCK Not Preventing Inserts

This should be a simple task. Can someone tell me why the below code is still inserting rows? lock table wp_postmeta write; insert into wp_postmeta values (null, 1,'test','test'); I've checked my ...
1
vote
0answers
106 views

Question on MySQL insert race condition

I'm using MySQL 5.1, PHP 5.3 and the table is MyISAM. Here's the DTD of this very fake table: CREATE TABLE `temp1` ( `value` varchar(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 This ...
1
vote
0answers
42 views

Multiple selects ordering desc from single table

I'm trying to do multiple selects to give multiple ordered columns. I'm struggling to get the below mysql query to achieve the output I want. SQLfiddle: http://www.sqlfiddle.com/#!2/a1002/3 ...
1
vote
0answers
502 views

Optimization Tips For MYSQL Tuner/Primer

So I have a site that has a crap ton of traffic. I see a decent amount of latency when the site has over 1,000 active visitors on it. I have new relic installed and it seems Im only using 10% of the ...
1
vote
0answers
27 views

Architecture for a MySQL MDBS

My coworker and I are tasked with redesigning our current Access databases in a MySQL environment. Between the two of us, this wont be a problem but we need to connect to other external databases that ...
1
vote
0answers
19 views

Is it ever a good idea to use different storage engines on MySQL for a given database?

I asked this question at http://stackoverflow.com/questions/24477834/mixing-mysql-storage-engines-in-a-database, but it was closed as being off topic. I have needs for different functionality ...
1
vote
0answers
94 views

Dealing with Multiple User Roles

I'm working on a project based on a CMS, where there is a built-in roles table. However, the CMS allows a user to have one role only using role_id And these are the requirements that struggling me: ...
1
vote
0answers
382 views

Why does this MySQL date_sub statement return wrong values?

I had a complex statement that was returning strange values and I traced it to the following line: SELECT id, updated FROM status WHERE updated > date_sub(NOW(), INTERVAL 6 MINUTE) ORDER BY ...
1
vote
0answers
548 views

“Temporary file write failure” when trying to change primary key in MySQL

In MySQL, When I trying to change primary key by this query: ALTER TABLE `resources` DROP PRIMARY KEY, ADD PRIMARY KEY (`role_id`, `category_id`, `lang`, `model`, `rule`); I execute this query ...
1
vote
0answers
88 views

Ordering a sequence of INSERT…SELECT statements to avoid a deadlock

I have the following two tables in a MySQL database: CREATE TABLE `names` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(60) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY ...
1
vote
0answers
51 views

Modulus calculation for compound primary keys in MySQL Cluster (and other sharded databases)

In MySQL Cluster, and generally in sharded databases, how is a compound primary key's modulus calculated? Say, for example, there are two tables that have primary keys like so: CREATE TABLE Orders ...
1
vote
0answers
47 views

Corrupted Filesystem: Possible to restore databases from the contents of /var/lib/mysql?

Using CentOS 6.4 x64. Not sure what happened but somehow my VPS filesystem got corrupted and it is now in a permanent read-only state. I've been working with my hosting provider's sys. admin. but ...
1
vote
0answers
91 views

MySQL replication of single DB to same server

I have a DB Server running CentOS 6.4 and MySQL 5.5 from RPMs (not source). I have dozens of databases but one of them is being hit quite hard with large selects which locks out other updates which ...
1
vote
0answers
854 views

Error when syncing MySQL database (XAMPP) on Dropbox

I'm working on a MySQL database for local development using XAMPP, and I would like to work on two different computers. Looking online, I saw this link that gives a tutorial on syncing your database ...
1
vote
0answers
32 views

How to update all primary keys in the existing database

I'm moving mysql multi-master replication to production using tungsten. I had setup the auto increment and offset values in my.cnf file. To achieve this i'll have to update my all primary keys of all ...
1
vote
0answers
40 views

Need help in optimizing a Slow Query

I am using userpoints module to provide points for different actions that they do on my site. mysql> desc userpoints; +-------------+---------+------+-----+---------+----------------+ | Field ...