MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
278 views

pt-table-checksum from remote server

I am using pt-table-checksum for the first time and have some questions that I can't seem to resolve by reading the documentation on Percona's website. Per the documentation, pt-table-checksum ...
1
vote
1answer
2k views

Accessing XAMPP/MySQL through SSH

I'm working from a Mac OSx Lion that is running XAMPP on localhost. I've been trying to find a way to run MySQL through the terminal. Normally I would do mysql -u [username] -p but the command is not ...
4
votes
1answer
1k views

What relationship type to use for this messaging system?

I am trying to make a small messaging system. It should have these properties: One user can send a message to one or many users Messages can be sent only to registered users (i.e. to an existing ...
3
votes
1answer
2k views

MySQL Insert into two tables using new IDs

I'm trying to select data already in the database and insert it into two tables using the generated ID from table one. table1 contains configuration options, table2 contains pricing data. Table2 uses ...
3
votes
1answer
1k views

how to use “mysqld -O thread_stack=#”

I keep getting this error whether I run my query from the terminal or from within Java. Anyone has some instructions how to resolve this? Thread stack overrun: 5768 bytes used of a 131072 byte ...
1
vote
1answer
120 views

Issue with Storage Engine

Just got the below error in one of the slave. I just Googled it and changed the variable myisam_data_pointer_size from 4 to 6. Somehow, my ALTER TABLE was successful. Can you please let me know ...
1
vote
1answer
116 views

Circular Replication, prevent failure

I recently needed to setup MySQL replication across 4 servers, so that each one is a slave of another. After several hours of tweaking I finally got this working - it was actually quite simple. I am ...
2
votes
1answer
645 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 ...
1
vote
0answers
228 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 ...
0
votes
1answer
2k views

mysql fine tuning: open_tables, opened_tables

I'm a novice to database administration and have been depending on forum suggestions and answers to help resolve my database hang ups and too many connections errors; I've managed to reduce the ...
2
votes
1answer
695 views

Inserting into three different tables, using foreign keys — 500000x each

We're working on refactoring many of our offline (python) scripts. I'm very new to any kind of SQL, but have been trying to learn as much as I can, especially in the performance field. We have a ...
2
votes
1answer
862 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 ...
3
votes
2answers
8k views

Local database vs Amazon RDS

I am doing large INSERT into a local MySQL database I am at 2,000,000 rows and am really starting to notice the slowdown now. I always heard that MySQL wasn't very scalable I expect this database to ...
1
vote
1answer
1k views

MySQL InnoDB logfiles resized - review mysql-err.log for potential problems

I restarted mysqld this morning, to increase the size of the innodb logs and increase the amount of memory allocated to the innodb buffer pool. (MySQL 5.1.53-Community on Ubuntu) To increase the ...
1
vote
1answer
1k views

MySQL - Performing an IF statement for each row in a query

I have a hurdle I'm trying to overcome with a website I'm building. I know what I want to achieve but I'm still pretty new to mysql so I'm not exactly sure how it's supposed to fit together. Setup: ...
4
votes
2answers
229 views

MySQL | Complex (for me) SQL Statement, how to?

I have a table that contains posts and another table that contains meta options for each post in first table. The meta options table is a key value pare table. Lets say I have the posts tables that ...
1
vote
1answer
253 views

Efficient query plan for selecting all data duplicated on several columns

The flavour in question is PostgreSQL, but it is a generic enough query pattern that your techniques for optimising against other RDBMs should help as well. The purpose of the query is to show all ...
0
votes
1answer
80 views

Where can I read about views for MySQL?

I really don't know nothing about SQL.. I know how to insert and select data from database using PHP but besides that, nothing. I found this view And I am sure that 5 minutes for you to answer this ...
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 ...
6
votes
2answers
2k views

Amazon RDS I/O requests - 1 query = 1 billable I/O?

I have an InnoDB database that I'd like to migrate onto Amazon RDS. My current implementation, hosted on my own server, shows roughly 8M queries per month. The RDS site say that I/O Rate $0.10 ...
4
votes
2answers
1k views

Import and Export database in Mongo just like MySQL

I am newbie to the Mongo.Before this I have worked only with MySQL.In MySQL we can import and export database easily.I want to know is there any option available in Mongo to import and export database ...
1
vote
1answer
2k views

declare keyword error mysql

This is the code I've written: delimiter // create procedure `update_pts`() begin set @ctr=1; set @cnt=(select count(*) from participant); declare cs1 cursor for select sap from participant; open ...
2
votes
6answers
3k views

MySQL join two table if second table is not empty

I have 2 tables like below table 1: users id name 1 John 2 Jane 3 Mery 4 Alpha table 2 : notes id note owner created 1 alpha alpha 1 2013-02-03 10:30 2 yeah ...
1
vote
0answers
43 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
1answer
505 views

Automatic MySQL Sync from master, where Slave was offline for long period of time

Does anyone here know if there are any tools, not using mysql's built in replication that will sync up two databases when the slave has been inaccessible from the internet for extended periods of ...
1
vote
2answers
616 views

MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)

I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records) I'm going to be converting this table to InnoDB in a production environment. The DB is on a cloud server with ...
0
votes
0answers
405 views

MySQL Left Join Select all rows

Hi guys I have a query which is pulling data from database : SELECT sandwich_table.id, `sandwich_name`, salad, COUNT(salad), salad_type.type, ...
0
votes
0answers
33 views

MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+) [duplicate]

I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records) I'm going to be converting this table to InnoDB in a production environment. The DB is on a cloud server with ...
0
votes
1answer
764 views

MySQL InnoDB locking on combined UPDATE-JOIN statements

on querying statements which contain an UPDATE combined with JOIN, we are getting the following errors: Statements writing to a table with an auto-increment column after selecting from another table ...
0
votes
1answer
602 views

How do I check if a stored procedure is defined in MySQL

When I do : select routine_definition from information_schema.routines where routine_name = 'sp_name'; it returns a row, but the routine_definition column is NULL. Does this mean that this Stored ...
2
votes
1answer
955 views

Monitor user to query processlist

We have a corporate monitoring system based in Zabbix. Its MySQL monitoring is quite basic, so I want to add more info. To do so, I've done a script that retrieves this info from MySQL and sends it to ...
3
votes
2answers
7k views

How do I dump only the data for all tables using mysqldump

I have a new database with schema and primary key, FK, index constraints created for all tables. How do I dump only the data from an existing database using mysqldump, so that I can import only the ...
1
vote
1answer
376 views

MySQL selects/updates only using 1 core

I have a server with 4 physical Xeon processors, each with 8 cores and each core is capable of 2 threads. When I run MySQL queries I'm only seeing 1 CPU go to 100% in top and the total CPU usage at ...
0
votes
2answers
12k views

how to insert data from one table to another [closed]

SELECT a.nid, a.stock, b.qty, sum( b.qty ) FROM uc_product_stock a INNER JOIN uc_order_products b ON a.nid = b.nid GROUP BY a.nid the above query give me the following result: nid stock qty ...
1
vote
1answer
64 views

About GRANT statement for user creation

I am new to databases, learning MySQL through a textbook. My doubts are regarding grant statement. I am learning on a localhost environment (both server and clieat are my own system). FIRST DOUBT ...
1
vote
1answer
987 views

MySQL Point in Time Recovery For Just a Single Schema?

Is it possible to simply restore a single schema and bring it to a point in time (Say three hours ago) without bringing the entire database back? I can think of a lengthy method of backing up each ...
0
votes
1answer
51 views

é being converted from database to site

I have a MySQL database that is storing business data... In the database, when running a select against a business name that contains an accented e (é), it comes up correctly (showing me that it is ...
2
votes
1answer
2k views

creating table syntax error 1064

I'm using workbench to create a table. This script is giving me a problem. It is saying that there is a syntax error new '). DROP TABLE IF EXISTS `waitronmain`.`managers` ; CREATE TABLE IF NOT ...
1
vote
1answer
854 views

Secure method beside phpMyAdmin for remote mysql

Recently, one of my centos linux servers was hacked and the cause of due to phpMyAdmin. It was said due to this problem PhpMyAdmin vulnerability CVE-2011-2505 ...
1
vote
1answer
3k views

can't create schema for mysql database

I am trying to create a schema for a mySQL database through my sites phpMyAdmin section. However when I try to run the following script: CREATE SCHEMA `mydb` I get this error and I am unsure what ...
1
vote
1answer
719 views

MySQL Tables crashing randomly

This is one of the many random Tables that get corrupted. Any ideas why and what would be causing this? How do I keep MySQL tables from crashing and MySQL from crashing? Repairing USR_wp537 ...
1
vote
5answers
3k views

How should I save apache logs into a mysql table?

This is a php script to view apache logs. I want to save apache logs into a mysql database. Then add some rules for tagging urls using mysql REGEXP search, like: SET tag='some tag' WHERE url REGEXP ...
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 ...
0
votes
1answer
348 views

horizontal partitioning or multiple tables?

I'm developing an application and need to propose a database schema. We plan for a MySQL database with millions of records which should be distributed over multiple machines for performance and ...
1
vote
1answer
860 views

how to run limit query within condition in mysql

I have a huge database around ~20,000,000, presently I am running a php script, which verifies if user info is 'valid or invalid'. There are several entries named 'invalid' in the database. Problem is ...
1
vote
1answer
166 views

With MyISAM is there any index size savings when using INT vs BIGINT?

There is obviously a four byte per row space savings in a data column when using INT vs a BIGINT type. But I have been trying to figure out if there is also any space savings for myisam indexes on ...
1
vote
1answer
65 views

MySql include partition key in index?

Please look at the following Mysql table: http://pastebin.com/b0NDSbdz I've partitioned the table by sent_at and I'm making sure that most of the queries pass sent_at. do I need to include sent_at in ...
3
votes
3answers
2k views

Count consecutive rows in mysql

I would like to get the count of consecutive rows in MySQL. It is a large database I try to avoiding the joins. Here gadget_id means vehicle and every 20/30 second the vehicle sends the current ...
0
votes
1answer
147 views

mysqlslap running very slow on a specific computer

I've recently discovered the tool mysqlslap and have run some simple tests with it. I currently have three test environments (2 Win 7, 1 Linux Ubuntu) where I can run these tests. However, running a ...
4
votes
1answer
3k views

What is the proper way to backup MySQL database with rsnapshot?

I want to backup my website that contains both user uploaded images and data stored in MySQL database such that they are always consistent with each other in any particular point of time. While ...