MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
0answers
17 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
78 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
63 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
56 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
107 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
37 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
47 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
37 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
142 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
24 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
130 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
59 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
308 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
80 views

Randomizing the Auto Increment in Mysql

We have a database, where we insert data into table whenever we receive an order from our services. We use auto_increment to generate the ID and assign the order with the same. This is usually same ...
1
vote
0answers
62 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
60 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
94 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
40 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
409 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
25 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
18 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
80 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
305 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
449 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
82 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
44 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
45 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
79 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
805 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 ...
1
vote
0answers
46 views

Querying number of tries since last successful log, checking IP as well as user

I asked this question before on stackoverflow, but it might fit better here, since it is mainly about database performance. The actual question With performance in mind, how would you go about ...
1
vote
0answers
78 views

Significant difference between duration for querys in SHOW PROFILE and SHOW PROFILES?

I have use mysql profiler to optimize an opencart query which takes more than 10 seconds to executes. mysql> SHOW PROFILES; ...
1
vote
0answers
489 views

MySQL dump error (#1005 - Can't create table 'novephp_cz_bv.jos_jquarks_customsets' (errno: 150))

I´m trying dump mysql database from MySQL 5.1. to MySQL 5.5. But I´m getting an error "#1005 - Can't create table 'novephp_cz_bv.jos_jquarks_customsets' (errno: 150)". Problematic part of script is ...
1
vote
0answers
44 views

Getting upcoming world wide releases from a database

From my database I want to get upcoming world wide release dates for an itemId along with the platformIds. If I have a table that looks like this and the current date is 2014-05-14 ...
1
vote
0answers
390 views

How to generate data for News Feed efficiently like facebook , googleplus , twitter e.t.c?

I have created table as post ( post_id int(15) auto_increment primary key , user_id int(11) , post_text varchar(2000) , post_type int(1), ...
1
vote
0answers
80 views

MySQL: Slow Update Queries

Our database server hangs for exactly 30 seconds on random UPDATE queries. It doesn't matter what field type the query is updating, it just seems like random selection. There are a few applications ...
1
vote
0answers
77 views

Why doesn't this query limit results by first and last name?

I have an ugly query, horrible table names courtesy of MySQL Workbench, and a severe lack of planning on my part to change to less verbose names prior to actually writing the code. Try to move past ...
1
vote
0answers
3k views

MySQL server taking up 100% CPU

I'm managing a site that does streaming video, the site is running off: Intel(R) Xeon(R) CPU E31220 @ 3.10GHz 16 GB RAM 2 x 2TB RAID1 1gbit unmetered bandwidth is not an issue as the ...
1
vote
0answers
178 views

What is a loose index scan?

I was reading that MySQL can not use an index on 2 range conditions due to not having a "loose index scan" What exactly does this mean? Is it related to the underlying or the implementation of the ...
1
vote
0answers
2k views

Table already exists error when trying to import sql file

I am trying to upload a backup sql file through phpMyAdmin. I create the empty db with the same db name as in my import file in phpMyAdmin then use the import function selected from within this empty ...
1
vote
0answers
158 views

MULTIPLE INSERT VALUES with INSERT into another table

here a scenario to have a more clear picture. The DB is MySQL. I've 2 tables events and *event_users* : EVENTS id int type int date date time time ... EVENT_USERS event_id ...
1
vote
0answers
121 views

Best database for fast read all in one table

I currently have a large database in SQLite that I was using for rapid-prototyping for a software I am working on. Now that I am planning to move it to another database that should give better ...
1
vote
0answers
381 views

using Aria vs InnoDB vs MyISAM

I was wondering (if anyone knows) if Aria database is crash safe . I had some tables with MyISAM which were getting corrupted very often and switch them to InnoDB . However innodb is a bit slower and ...
1
vote
0answers
57 views

Move all InnoDB databases into one subdirectory (innodb_file_per_table is ON)

First of all, excuse my English. I want to run a MySQL 5.6 on ZFS volume, and need to move all InnoDB databases to one subdirectory (which in fact will be a different ZFS filesystem). For example, by ...
1
vote
0answers
22 views

Is it possible for unique key to break master-master replication in mysql?

I have a master-master mysql setup with 2 servers running the exact same application making writes to such a table: CREATE TABLE `metric` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, ...
1
vote
0answers
148 views

What do spikes in InnoDB lock structures indicate?

I co-maintain a high traffic site similar to StackExchange. It runs MySQL 5.1 (working on replacing it with MySQL 5.5 or MariaDB). All our tables are InnoDB. MySQL goes down a couple of times a day. ...
1
vote
0answers
34 views

“dynamic set” in a MySQL table

I have a table where one column will have a few values many times (maybe 20 different values). Now I would like to use something like the set to improve performance and memory consumption. The problem ...
1
vote
0answers
1k views

Solved Mysql connection lost with HAProxy

I have master-master replication with mysql. and haproxy configured front of my master-master replication. When I connect my replication via HaProxy, mysql says that "MYSQL has gone away". But when I ...
1
vote
0answers
176 views

Master and slave tables with different character sets - will replication break?

We have some huge tables in our MySQL 5.6 database that have latin-1 as their character set, and want to convert them all to UTF-8 (along with converting the database's global character set). The ...