MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

2
votes
1answer
288 views

datetime index skipped for same query different params

I am running two queries with same query but different parameter and one of them is extemely slow while the other is fast. Both the results are alomost the same. (500 and 575) Any idea why it skips ...
2
votes
1answer
70 views

how to grant a user permission to create databases?

I use phpMyAdmin powered by Mamp (free version) to manage Mysql databases. I need to know how to grant a user permission to create databases.
2
votes
1answer
268 views

Replication with only update and insert

I have set up my replication but I want to delete some outdated data from my master tables. Is it possible that say if I have 10 records at master and I delete 5 of them but I want those 10 records to ...
2
votes
1answer
83 views

MySQL replication constantly checking tables

I recently moved one of our slave databases to a new server, the database is purely used for offsite replication/backup and doesn't handle any of the live traffic. Since moving, the new server seems ...
2
votes
1answer
8k views

How to properly stop MySQL server on Mac OS X?

I installed MySQL Community Server following instructions at http://dev.mysql.com/doc/refman/5.0/en/macosx-installation.html with the Startup Item and the MySQL Preference Pane. I can stop the ...
2
votes
1answer
187 views

Why does OS login not exist in the user table, but I can still login to MySQL?

mysql> select user, password, host from user; +------+----------+---------------------------------------------+ | user | password | host | ...
2
votes
1answer
344 views

FLUSH TABLES stays in the processlist even after execution

Here is my code from Stored Procedures in Mysql FLUSH TABLES; TRUNCATE TABLE tbl_cdrload; TRUNCATE TABLE tbl_cdrdetails_temp; Then i execute a query to load a file in to database as below LOAD ...
2
votes
1answer
4k views

Very Large Log Files

I'm running Windows Server 2008 R2 and executed a large query by accident. Next, the sever ran out of hard drive space. I now have a file in my administrtor documents (log.log) that is over 18GB. I ...
2
votes
1answer
860 views

innodb_flush settings on Amazon RDS

Setting innodb_flush_log_at_trx_commit = 2 improves my application performance considerably on my local development environment. The response time and number of requests per second on a 40 concurrent ...
2
votes
1answer
683 views

MySQL: Show full processlist has empty Host name. Why?

I am executing SHOW FULL PROCESSLIST and from the response I get the "Host" field is empty. Quoting from the docs here Host The host name of the client issuing the statement (except for ...
2
votes
2answers
1k views

MySQL Server is Restarting automatically after some minutes

I am getting an Issue after everey 2-3 minutes mysql> show tables; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 57 I am really not able ...
2
votes
1answer
250 views

MySQL : Does 'bytes_sent' and 'bytes_received' include mysqldump data?

I'm trying to estimate my db traffic not including backups (mysql 5.1.41). I use show global status; to get the parameters bytes_sent and bytes_received. Questions Do these numbers include ...
2
votes
1answer
8k views

How to compare (in MySQL) a DATETIME value to a TIMESTAMP value?

I'm trying to understand how to compare a DATETIME value (ostensibly inserted as GMT) and a TIMESTAMP value (automatically generated at INSERT time). As I understand it, the DATETIME is set to ...
2
votes
2answers
2k views

Will an ALTER TABLE ADD COLUMN on a slave break replication?

I need to add a last_modified column to a MyISAM table tbl_items. The crux of this problem is that tbl_items houses several gigabytes of data. Also of note, I am using a master-slave deployment with ...
2
votes
2answers
1k views

Model a family tree database in Access 2007

I designed my database with the intention of implementing it with MySQL, but have now decided to implement it in Access 2007 using form and subforms. While creating my UML diagram, I realised I have ...
2
votes
1answer
91 views

Change-set SQL optimization

I'm trying to clean up a change-set query that returns rows from a table that have changed since a given time and also do not exist in a known list of already previously returned results. select ID, ...
2
votes
3answers
1k views

InnoDB UPDATE slow need a better option

I'm using MySQL 5.5 with a table in InnoDB running: deleteme tinyint NOT NULL DEFAULT 0;` UPDATE `import` SET `deleteme` = 1; -- Set the delete field LOAD DATA LOW_PRIORITY LOCAL INFILE ...
2
votes
1answer
4k views

Concept to keep track of changes in all tables

In my current project I use about 10 MySQL tables (Doctrine as DBAL). I'd like to keep track of all changes in the databases (insertions, updates and deletions). At the moment there are two ...
2
votes
1answer
203 views

Query results in a strange explain output

So I have a medium sized query that hits 15 tables. The query takes 36 sec on MySQL to complete. I ran EXPLAIN on the query and one of the tables returned this. '1', 'SIMPLE', 'mount_adapters', ...
2
votes
1answer
905 views

How to use the 'select benchmark' command on a procedure

I am trying to check which of my procedures is slowest and thus needs optimization. I currently have around 160 procedures and about 30 functions. I am using the select benchmark() to check the ...
2
votes
1answer
326 views

Redesigning a multi-instance MySQL server for performance and resiliency

I've got a single MySQL server which currently runs N separate MySQL instances on, which are rapidly growing beyond the capabilities of that box. Is it possible to have an active-active MySQL ...
2
votes
2answers
3k views

Synchronize MySQL databases between local and hosted servers

I'm developing a software in Microsoft Blend and I need it to be synchronized with my online server MySQL database. I have local MySQL database which I use for the app. The app also have an online ...
2
votes
1answer
336 views

MySQL performance on Mac vs Linux

I know that MySQL is tested more on Linux (Oracle has it's own linux distribution and you canot have LAMP wit MySQL (meaning MySQL has always focused more on Linux) My intuition says that MySQL has a ...
2
votes
1answer
284 views

Check against multiple duplicate records

I have data that i should insert into table. But before inserting i need to check against duplicate records and report list of those records. Table: CREATE TABLE `test` ( `A` varchar(19) NOT NULL, ...
2
votes
1answer
590 views

3 Databases, Best Replication Setup?

We have 3 DB servers, two of them i.e. db1, db2, are located in the same data center. The other one i.e. drdb, is located in a different site. Currently, the replication setup for these servers is of ...
2
votes
1answer
1k views

MySQL import csv only gets half the lines

I use the following command to read in a csv: LOAD DATA INFILE '/Users/Tyler/Desktop/players_escaped.txt' INTO TABLE players FIELDS TERMINATED BY ',' ENCLOSED BY '^' LINES TERMINATED BY '\n'; The ...
2
votes
1answer
381 views

select for update gives error on indexed column

here is my code: mysql_query("start transaction"); $q="select max(id) from test for update"; $r=mysql_query($q); $row=mysql_fetch_array($r); for ($k=0;$k<100000;$k++) { $q="insert into test values ...
2
votes
1answer
888 views

Correct SQL Server linked server connection settings to MySQL that will allow inserts

I'm in linked server hell! I've created a linked server to a remote MySQL database. I'm able to query and do updates. However when I attempt an insert I get an error: "[MySQL][ODBC 5.1 ...
2
votes
1answer
644 views

Dual management node for mysql clustering

I currently have mysql cluster setup and running. Here is my current setup: 1 - management node 2 - data nodes 2 - mysql nodes I wish to incorporate a failover for the management node. The ...
2
votes
1answer
2k views

Why can't I get a lock on an InnoDB table?

I have problem with lock table command: LOCK TABLE v3_cam_date WRITE; the command always running with state: "Waiting for table metadata lock". Try to find out which thread was hold the lock on ...
2
votes
1answer
781 views

Number of locks exceeded when adding foreign key constraint

I'm adding a foreign key constraint to table a (17mil rows) that references table b (15mil rows): >ALTER TABLE a ADD FOREIGN KEY (b_id) REFERENCES b (id); ERROR 1206: The total number of locks ...
2
votes
1answer
1k views

MySQL using trigger instead of check

Is there any way that I could use trigger instead of check? like I have CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER test BEFORE UPDATE ON apply FOR EACH ROW BEGIN ...
2
votes
1answer
1k views

How can I use a MySQL query in an openquery query on Microsoft SQL Server.?

I have the following query which works fine in MySQL: DELETE sb FROM stockbatches sb INNER JOIN batchestodelete btd ON sb.batch_batch = btd.batchno AND sb.batch_part = btd.batchpart ...
2
votes
1answer
443 views

How to prevent high memory, CPU and time consumption by MySQL restore?

I have a local test machine with 2 GB RAM and a dual core processor. I imported a dump on that using mysql -uuser -psecret < script.sql script.sql is a 700 MB file which contains a single ...
2
votes
1answer
1k views

Is it possible to convert a .mysql file to a .sql file?

I am facing an issue with a MySql dump file (i.e mydump.mysql) generated by a Drupal export module. I am trying to import it on a local Drupal installation via Acquia Dev Desktop. When browsing for ...
2
votes
1answer
105 views

Which of the following data duplication options across shards is recommended?

High performance mysql book suggests that for sharding a blog application, one may want to put comments data across 2 shards: first, on the shard of a person posting comment, and on the shard where ...
2
votes
1answer
230 views

Innodb mutex locking ?!? Inserts start stacking after 18-24hours, restart required

We have a fairly high end box running mysqld 5.5.20. The cpu configuration is dual quad core xeons. It has 32 gb ram. It has a quad intel ssd configuration running raid 0+1. On a fairly busy ...
2
votes
2answers
769 views

Need help in restore some rows into mySQL database frm myd myi

I am using one mySQL database for several sections of the website and one of the section is not working, when I go to the link of that section it gives the error "1146 Error @ Line 39: Table ...
2
votes
1answer
161 views

Mysql takes up to 50% cpu after aborting DELETE query

I have a problem I have never met before. I was clearing yesterday client's db and table with stats of visits that had 15GB of data. I decided to delete all rows older than 1 Feb, because we have all ...
2
votes
1answer
382 views

myisampack running really slow for large DBs

I was going through compressing a bunch of archival myisam tables using myisampack It was making decent progress until it got to the most recent tables. The overall database is a set of archives for ...
2
votes
1answer
282 views

Converting 3 Master + 1 Slave to 4 Master - Any advice?

so we have a beautiful 3 master 1 slave cluster running. we would like to convert this to a 4 master cluster. here is how the topology currently looks s1 enslaved by s3 s2 enslaved by s1 s3 ...
2
votes
1answer
133 views

mysql 5.5 strange bug

I am trying to create a database in MySQL Server 5.5. The database is created, I know this as when I do SHOW DATABASES; It shows me my database in the list of all databases from the commandline. ...
2
votes
1answer
88 views

Moving the binary location of MySQL for replication

Is it possible to move the contents of /var/lib/mysql when setting up replication instead of doing mysqldump > for export and mysql < for insert on the slave, because in our current setup it takes ...
2
votes
1answer
772 views

How do you verify that all InnoDB tables are in good shape?

Without using a script, Is there a way to get mysql to check all the tables at once to make sure they're not corrupted?
2
votes
1answer
1k views

MySql Switching Masters During Failover with log-slave-updates

I am still pretty newbie to mysql. This question is regarding promoting slave with option --log-slave-updates to become a master. The typical schema of promoting slave to master was described in ...
2
votes
1answer
597 views

InnoDB not enabled

I have a mysql database that when I run SHOW ENGINES; innodb does not show up as an engine. This seems strange as I have an identical server setup where innodb is enabled and in use. I have checked ...
2
votes
2answers
2k views

MySQL Replication via DNS Failure

I'm having some trouble using replication when the machines are configured to use host names instead of IP addresses. The "skip-name-resolve" is OFF. If I use IP addresses, everything is fine. Using ...
2
votes
2answers
654 views

MySQL Views Authorization

Questions: Is there a way, using SQL, to identify how many views are in the database per user? How can you authorize/deny users access to delete views created by another user?
2
votes
1answer
5k views

MySQL Workbench 5.2 edition - Unable to establish the connection

I have installed MySQL (mysql-workbench-gpl-5.2.37-win32.msi ) which is a Windows Installation Workbench. Couple of times, I have done re-installation on my computer. Now, I am not able to establish ...
2
votes
1answer
343 views

MySQL Trigger problem with the email address (cause contains the @ sign)

I'm trying to set a trigger in a table (users) to "copy" the email address of a new row to another table (prova). Here is the trigger: CREATE TRIGGER inserisciemail AFTER INSERT ON users FOR EACH ROW ...