MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
0answers
343 views

SELECT + INSERT + Query Cache = MySQL lock up

MySQL server seems to constantly lock up and stop responding on certain types of queries and eventually (after couple of minutes of not responding) give up with an error "MySQL server has gone away", ...
1
vote
0answers
113 views

Delete operation mysql cluster

I am trying to delete some entries from table on a cluster and I get this error ERROR 1297 (HY000): Got temporary error 233 'Out of operation records in transaction coordinator (increase ...
1
vote
0answers
74 views

phpmyadmin Lock Down

I have a client that wants to use myphpadmin to manage his mysql database 5.x on my server. I found this article on how to hide databases from myphpadmin but I don't like this because if I don't ...
1
vote
0answers
164 views

MySQL replication error 1146

Any idea why there is '1146 - table doesn't exists' on 'CREATE TABLE IF NOT EXISTS...' statement. It actually happened only on one of the slaves (there are two in total): Last_SQL_Error: Error 'Table ...
1
vote
0answers
47 views

Table Structure: 2 Related Entities Share the Same Entity

I was hoping I could get some advice on my table structure. Im writing a simple workout tracker for me and my buddies. These are the requirements: Multiple users A user can have multiple workouts A ...
1
vote
0answers
71 views

Deleting the MySQL 'root' user on purpose

I created a new mysql user with all the same privileges as the current 'root' user. For security reasons I thought why not create another user for this so people at least won't know the username of my ...
1
vote
0answers
102 views

Producing an outstanding fees report in MySQL

This question is about a MySQL stored procedure to select the records from two tables by joining them and also do some arithmetic operations in that. I have 3 tables for fees one is to store fee ...
1
vote
0answers
31 views

How to use a database across two availability zones

I have servers in the USA and Japan for my website. At the moment they are both connecting to a MySQL server in the USA, which is obviously perfect for my local USA clients but quite slow for my ...
1
vote
0answers
266 views

How do I safely import MySQL data in a directory to a running MariaDB server?

We're integrating another office into ours. That includes some of their data. I have a new installed MariaDB 5.5 server. The data dir = /home/data/MariaDB. I've done a new/fresh install into that ...
1
vote
0answers
60 views

Is it possible to keep track of changes to tables structure?

Is it somehow possible to keep track of changes made to the STRUCTURE (not the data) made to tables? I mean, i don't want to know when rows are inserted/updated/deleted, what i want is to know when ...
1
vote
0answers
106 views

Mysql key_buffer_size already 16GB but some fulltext searches are still slow

I have a query w/ full text search which takes 5 - 10 secs. key_buffer_size is already 16GB. Table has 15 million records and has a compound fulltext index on fields: categories and company. Table ...
1
vote
0answers
356 views

Mysql raising intermittent “system error: 32” errors on connect

I'm running mysql 5.6.12 on OSX 10.8 and getting an error like this one constantly: ERROR 2013 (HY000): Lost connection to MySQL server at 'sending authentication information', system error: 32 ...
1
vote
0answers
129 views

Ignore foreign keys in mysql workbench model synchronization

I've made an ERD in MySQL Workbench, where I added foreign keys to visualize the relationships. In the actual database I want to work without the foreign keys (I'll organize that in my application). ...
1
vote
0answers
92 views

Any solution to fix circular replication fail-over?

I've developed a point-of-sale system for our corporation, which has 25+ branches across the country. Each branch has its own MySQL POS database on a WAMP server. I want every event that occurs in ...
1
vote
0answers
137 views

Duplicate GROUP_CONCAT values

Writing a plugin for Wordpress using a custom post type and an additional alias table to hold repetition of the post events. The problem comes with duplicate post meta values. wp_post_meta table: ...
1
vote
0answers
114 views

innobackupex: What causes Slave_open_temp_tables did not become zero after waiting 15 minutes?

As you might know, in order to assure a consistent replication state, the slave should be backup only when there are no open temporary tables: ...
1
vote
0answers
80 views

Can't get rid of filesort on joined query sorting on second table

For some reason, this query: EXPLAIN SELECT * FROM biz as b INNER JOIN listings as l ON b.id = l.biz_id WHERE l.pub_id = 14 AND b.cat_id=310 ORDER BY l.level DESC, l.random DESC LIMIT 5; says it ...
1
vote
0answers
75 views

MySQL Partitioning Performance

I have a MyISAM table with billions of rows that is still causing me problems because the indexes, even after I shortened them as much as possible, do not fit in my 64GB of RAM. I am unable to index ...
1
vote
0answers
215 views

How to sync mysql remote database daily ?

I have a database that contains orders , products ..etc. I have another system that generates reports based on this database. Due to heavy load i want to separate those systems Where i have the master ...
1
vote
0answers
445 views

How can I join one to one to (many to one) without using group_concat

I have a table structure as follows: -- -- Table structure for table `emails` -- CREATE TABLE IF NOT EXISTS `emails` ( `ID` int(5) NOT NULL AUTO_INCREMENT, `email` varchar(255) COLLATE ...
1
vote
0answers
96 views

System Preferences quits unexpectedly due to MySQL plug-in

I've installed MySQL Preference Pane on my MacBook to start/stop MySQL from my System Preferences and it works ok, except that once in a while System Preferences just quits and shows this message: ...
1
vote
0answers
79 views

Why is this query creating 300+ temporary tables in Mysql?

SHOW global STATUS LIKE '%tmp%'; set @UID = 132; set @param2 = 'cde'; set @param3 = 20130331; set @param4 = 'C'; select p.id, p.column44, p.column42, ue.column35, u.id, p.column43, p.column45, ...
1
vote
0answers
91 views

Error in alter table reorganize partition for MySQL Cluster

My cluster contains 48 million rows and cluster contains 4 storage nodes . After that I add online storage node and executed alter table 'customer_tbl' reorganize partition. Some parameters of ...
1
vote
0answers
72 views

MySQL Partitions

I've done a lot of work with MySQL but generally on a much smaller scale than this, so please excuse my ignorance. We've got an InnoDB table which is currently storing ~3.5 million rows, which I know ...
1
vote
0answers
214 views

MySQL 5.1.67 - Replication failure Mysql, Master Log Truncated or corrupted

I have run into some issues with replication After an in-place upgrade of mySQL from 5.0.77 -> 5.1.67 rel 14.4 Percona replication between a master and multiple slaves is causing issues. slaves are ...
1
vote
0answers
998 views

Achieve PostgreSQL hstore functionality in MySQL

Please suggest any MySQL 5.5+ Engine that can support collection of key-value pairs be stored in any column of a table. I don't know if this feature is available out of the box in MySQL like ...
1
vote
0answers
106 views

Data Warehousing strategy

I have a busy MySQL database with 1.6 million records a day, I'm looking to replicate this data to another server, then run a clean up job on production database to maintain small database in ...
1
vote
0answers
114 views

Most efficient way to sort data fields into SQL

I'm trying to decide on the most efficient way to sort various data values. Here's how the data arrives: Device X sends a text string "name=value&name2=value&name=value" On arrival that ...
1
vote
0answers
602 views

“Lost connection to MySQL server during query” error

I have MySQL 5.5.20 and this table: mysql> desc country; +----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
0answers
1k views

'Incorrect datetime value: for column 'last_login' at row 1'

i am getting below error: Last_SQL_Errno: 1292 Last_SQL_Error: Error 'Incorrect datetime value: '2013-03-10 02:00:00' for column 'last_login' at row 1' on query. Default database: 'platform'. ...
1
vote
0answers
133 views

Replication issue - CREATE SELECT alternative?

I've an MySQL 5.1 slave for our BI team. They need to make some CREATE SELECT with big select queries (several million lines). As CREATE SELECT is a DDL, if the replication attempts to update some ...
1
vote
0answers
67 views

mysql update unique values from 2 different tables based on 2 index fields

i have 2 tables with the same unique id. i want to change table1 unique_id sequence from (1-100) to random 8 digit ids (43342359 - 83083224) and cascade that to table2. what i have: - table1 id1 ...
1
vote
0answers
431 views

mysql remote connect

Having troubles with this and I'm not sure why.. I'm trying to access a mysql database from another server. I changed the my.cnf file (uncommented the bind-address to access from all): port=3306 ...
1
vote
0answers
598 views

Migrating from Oracle to MySQL using WorkBench Migration Tool

I am currently migrating from Oracle to MySQL and I am going to use the migration tool in workbench to help me with the procedure. I have entered my source connection details as follows: Database ...
1
vote
0answers
122 views

MySQL Workbench and Optional Many's

I have used workbench before to make databases code wise and I just started looking into its EERD functions. I created a small database for a test project and found some odd things about how MySQL ...
1
vote
0answers
40 views

Is there a canonical list of MySQL defaults which should be changed

I'm setting up a new mysql server as we're building a new app at work. Most of the developers/operations guys are familiar with mysql. Many of mysql's settings (eg strict mode) are set badly by ...
1
vote
0answers
131 views

Calculate lower and upper quartile of SQLiteDB via query

I want to calculate the upper and the lower quartile of the value what I call "diffs" in these fiddle with my DB and the query How can I do this with SQLite? There's an extension on the ...
1
vote
0answers
131 views

TMD auto create

I used mysql as part of a project and found it to be a very practical tool. I have a question about mysql in regards to how it creates temporary table types TMD in /var/lib/mysql/. This partition ...
1
vote
0answers
262 views

What's complexity of select * from table order by id desc limit a, b?

I'm displaying about 1,000,000 records separated by 10 records per page. select * from table order by id DESC limit a, b; In this query id is indexed, and b is 10. The querying time is fast when ...
1
vote
0answers
177 views

Dynamic MySQL partitioning based on UnixTime

My DB design includes multiple MYISAM tables with measurements collected online, Each row record contains auto-incremented id, some data and an integer representing unixtime. I am designing an aging ...
1
vote
0answers
109 views

MySQL Partitioning, Reorganize Max Value partition (Known Bug / Feature)

I have written a crone job which reorganizes last max value partition every week into 2 new partitions , one for the previous week , and one for the future data (new max value partition which ...
1
vote
0answers
173 views

big mysql database optimization

My Joomla mysql database has about 3.4 million rows in content table. It is just static article. Very slow and often use out of resources. To speed up it, I segmented it into 6 sub-tables and added ...
1
vote
0answers
63 views

pt-table-checksum generates no output

I've been using pt-table-checksum on our staging server successfully, but when I run ./pt-table-checksum h=localhost,u=user --ask-pass --lock-wait-time=50 --databases prod It just sits there. No ...
1
vote
0answers
109 views

Why is this table structure so slow to CRUD?

I have a table that I am using for queuing emails. Unfortunately, adding to or reading from this table becomes horrendously slow after adding about 50 records. -- Table "mail_queue" DDL CREATE TABLE ...
1
vote
0answers
134 views

Database instance keeps restarting

From the past few days, our MySQL database instance has crashed and had to be restarted a few times. My MySQL version is 5.1.57-log. The CPU and memory usage was way below 50% during the crash time. I ...
1
vote
0answers
49 views

memcached+mysql cluster maximum memory

I'm deploying a system with memcached and mysql cluster. The command line is: memcached -E lib/ndb_engine.so -e "connectstring=maddy:1186;role=dev" I can't specify the -m argument since I used -E ...
1
vote
0answers
90 views

How to create multi volume file backup using symantec backup.exec

I am using symantec backup .exec for backup and restore in mysql server . Now I am in need to take larger database backup in tape .But it exeeds the tape storage and I am trying to create a multi ...
1
vote
0answers
74 views

Mysql: Large Query with subqueries optimization

Any query optimization gurus in the house?? Anyway, I am trying to attack this on two fronts, one being to code a way around it but secondly, I'm thinking some real optimization might help in the ...
1
vote
0answers
72 views

MySQL: Assessing performance of a query by using EXPLAIN

It is said that the number of rows are scanned is smaller the performance is higher when use Explain to optimize query. So I wonder When the EXPLAIN output includes many rows, is there any formula ...
1
vote
0answers
101 views

MS SQL to MySQL Conversion

Is there a tool available that converts queries written as triggers, stored procedures and functions written in MS SQL 2008 to MySql? We switched databases and now I need to rewrite a bunch of queries ...