MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

2
votes
2answers
338 views

MySQL “Limit” clause grabbing more results than requested

I'm trying to add pagination to my points system (100 results per page) and came up with this : SELECT A.username, A.points FROM users A INNER JOIN ( SELECT points FROM users WHERE points ...
1
vote
1answer
403 views

Mysql 5.0 on Windows 2008

I have a problem with MySQL on Windows Server 2008 x64. This is the first time I've installed MySQL 5.5 with performance problems. I decided to go back to the older version that worked well with ...
0
votes
1answer
493 views

How to run this MySQL statement?

See the MySQL example on http://dev.mysql.com/doc/refman/5.5/en/loop.html: I copy it and paste on MySQL command line but it stop at every semicolon. I wonder how to make these sentences work? I ...
1
vote
2answers
775 views

Slow query with straight_join for small result

I have a performance-problem with a query in my application and I don’t understand the behavior of mysql. The query consists of different joins, especially the join to the mentioncache-table. If the ...
0
votes
0answers
60 views

How to check on my partitions?

I am a newbie into partition. First I have a table with auto increment with its primary key and many other fields. I wanted to partition based on dataInsertDateTime column. It have me error so I had ...
1
vote
3answers
2k views

How to grant multiple users privileges; MySQL

I have the following code: GRANT SELECT,INSERT,UPDATE ON db1.* TO ‘user’@example.com; I am a beginner in mySQL, I was wondering if there is a method in which I can specific the privileges for every ...
1
vote
0answers
814 views

Why can I not start mysql server on ubuntu after change the innodb_flush_log_at_trx_commit value to be 2?

Why can I not start mysql server on ubuntu after change the innodb_flush_log_at_trx_commit value to be 2 ? updated to answer the 3 questions: mysql> show variables like 'version%'; ...
2
votes
2answers
1k views

how to reduce mysql overhead?

My machine has 2GB RAM, I have some cron job run every minute insert into some data. My mysql always overhead. I think it is very frequently, if some cron job mysql insert run, 5-10 minutes, the db ...
3
votes
3answers
598 views

High insert rate on MySQL

I have a project at the moment that is close to be finished. I have been asked (at the very end) to log a specific event. The event will occur very often (easily a few times each 5sec). Each event ...
0
votes
2answers
493 views

microsoft webmatrix deleted my wamp databases?

I've been using WAMP server for a while and I have several databases on the mysql server. today I installed microsoft webmatrix and when I opened one of my sites on wamp I got a mysql error saying ...
2
votes
1answer
263 views

Where are partitions defined in mysql

I have a large table that I want to spread across multiple disks/LUNs. It will contain blobs and I want to have it separated out from the other data. How do I go from new disks (lets call them ...
4
votes
3answers
8k views

Need to Check if a Table Exists Before Deleting a Record

I have the following SQL query in my Ruby app: sql = "DELETE FROM #{database}.table1 WHERE same_id = #{some_id};" The problem is that in rare circumstances 'table1' might not exist. I need to ...
1
vote
1answer
787 views

MySQL Replication Error, can't stop slave

When I run command SHOW SLAVE STATUS\G, I see Slave_IO_Running : 'Yes' Slave_SQL_Running : 'No' What I've usually done to fix this is to run STOP SLAVE; SET GLOBAL sql_slave_skip_counter = 1; ...
3
votes
2answers
167 views

Tablestructure for fast inserts/deletes with foreign keys

Current Situation We have a table called c with ca. 300,000 rows. In this table we store the competitors for a specific product. Example: id | competitors | some infos about competitor | ...
1
vote
0answers
209 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 ...
2
votes
1answer
74 views

Preventing concurrent sends of emails from my application

I have a system that sends emails for me and my clients every day in the morning. This process is triggered automatically through access to my website, via an iframe. Often, I receive the same email ...
7
votes
1answer
5k views

MySQL INNODB corruption after server crash during concurrent truncate command

My server crashed today I think due to a concurrent truncate table command on one of our INNODB tables. The server could be restarted, but after it starts up, everytime I try to issue an SQL command, ...
3
votes
1answer
157 views

Backup and off-site restore setup

I've been reading a lot of threads about different backup options for MySQL and was hoping for some more insight so I can settle on one. Our current situation is ~100 GB of InnoDB and MySAM data in ...
2
votes
2answers
2k views

Event Scheduler: No data - zero rows fetched, selected, or processed?

MySQL 5.5.28 The error log keeps getting this error: 121004 12:08:59 [ERROR] Event Scheduler: [user@ip][reportingdb.tvc_bookingonline_update_status] No data - zero r ows fetched, selected, or ...
3
votes
1answer
2k views

MySQL Slave Relay Logging but not logging Binary Log

I have a master slave setup both mysql 5.5, the setup is fine except that the slave won't Binary log to the standard mysql-binlog only to the relay logs. I fear this may be due to my lack of ...
0
votes
1answer
61 views

Grabbing data from a query with specific criteria, then the data around it

The title may be slightly vague... however, this is the basis of what I want to do: I have people in a database ranked on a statistic, for example, 'Kills'. I want to order the kills by highest to ...
5
votes
3answers
6k views

How can I configure MySQL Innodb to handle 1000s of inserts per hour?

I have a very high traffic website where it is possible that 1000s of new records are inserted every hour. This one error is crippling the site: PDOException: SQLSTATE[40001]: Serialization failure: ...
3
votes
2answers
673 views

How to concatenate a column after x rows?

I have a table with columns object varchar(255), frequency tinyint(3) unsigned, I want to concatenate the objects if the sum of frequencies reach a value. Currently I am doing this in PHP as .. ...
3
votes
2answers
468 views

Character Set Encoding in a Table

I have the following data:Trollhättan If I print out and setting up the header to utf-8, then this is the output in the browser. If I'm not setting up the utf-8 in the header, Trollhättan. ...
2
votes
1answer
403 views

trigger in MySQL to disallow multiple row deletions at a time on a table

How do we create a trigger in MySql to disallow deletion of more than 2 rows from (say) employee table by a single query? delimiter \\ create trigger not_more_than_2_rows before delete on employee ...
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 ...
4
votes
1answer
1k views

Error migrating a big table from mySQL to SQL Server 2012

I'm trying to convert several mySQL tables to Microsoft SQL Server 2012 tables using the SQL Server Migration Assistant for MySQL. The small tables get converted but when it comes to the big one ...
0
votes
1answer
73 views

Getting a “type” column from each table in query

Let's assume I have 3 tables: videos, books, albums and I would like a query that gives me the 3 latest entries in each table, along with a column "media_type" valued Video, Book, or Album, ...
1
vote
2answers
289 views

Table partitioning

Recently I have been looking to optimize my database and its size is 7GB. I have an option to partition big table. But partitioning would just divide the big table into small ones while remaining on ...
1
vote
2answers
1k views

Update Large Table

I have these two tables: user_profile: user_profile_id_int int primary key user_profile_id varchar user_activity: user_profile_id_int int key user_profile_id varchar both fields in user_profile ...
3
votes
1answer
358 views

how to add removed partition?

This is the structure of my table: -- Table: t_keyword_conversion_ga -- Create Table: CREATE TABLE `t_keyword_conversion_ga` ( `id` int(11) NOT NULL AUTO_INCREMENT, `own_domain_id` int(11) DEFAULT ...
3
votes
1answer
119 views

Does combining columns into 1 column helps to optimize a large table?

I have user, items and user_items tables, and as usual user has many items. Tables contains: User: ~50K Items: ~3.5M UserItems: ~5M I am using MySQL and most of my queries are selecting items of a ...
3
votes
2answers
285 views

Database choices for big data

I have many text files, their total size is about 300GB ~ 400GB. They are all in this format key1 value_a key1 value_b key1 value_c key2 value_d key3 value_e .... each line is composed by a key and ...
1
vote
2answers
2k views

MySQL not responding, Writing to net status

I just had to reboot MySQL to regain access to some of my website. It had under 300MB of RAM used. Another product was working fine though, accessing it's own tables which are InnoDB. Here's the show ...
1
vote
2answers
117 views

Cannot INSERT into database on a shared server

I have launched an application on a shared server but I cannot INSERT any data. I thought maybe I don't have privileges so I did SHOW GRANTS in my application then die() The result was [ EDITED ]: ...
0
votes
0answers
1k views

Dynamically Converted rows into columns (Pivoting)

Query SELECT Performparam.performparam_Name,studfeedback.studfeedback_Rating, SUM(studfeedback.studfeedback_Rating) AS total, COUNT(studfeedback.studfeedback_Rating) AS totastudents, ...
0
votes
2answers
8k views

Use of CASE statement in a MySQL query

I have 3 tables: TASK (id_task (PRIMARY KEY), name) PROJECT (id_project (PRIMARY KEY), name) PROJECT_HAS_TASK (id_project, task_id) PROJECT_HAS_TASK is the (n,m) relation between PROJECT and TASK. ...
3
votes
1answer
833 views

A function to check if a column allows NULL

Is there a way to write an insert/update query that checks if a column allows NULLs: If it does set the column to NULL, and '' (empty string) otherwise? I would be something like: UPDATE mytable ...
1
vote
0answers
57 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
2answers
467 views

SQL queries break our game! (Back-end server is at capacity)

We have a Facebook game that stores all persistent data in a MySQL database that is running on a large Amazon RDS instance. One of our tables is 2GB in size. If I run any queries on that table that ...
5
votes
1answer
1k views

How much does table size affect performance?

Lets say I have a MyISAM table (amongst other tables) with 2938347 rows. This table is merely being used for logging and doesn't get emptied very often. By not regularly archiving or deleting these ...
1
vote
1answer
3k views

mysqldump not escaping single quotes properly

I use mysqldump to backup my mysql database. The problem is that the sql file generated by mysqldump doesn't escapes single quotes properly. Here is an example of the mysqldump generated sql script : ...
2
votes
1answer
2k views

MySQL Explain: Using index, using temporary, using filesort. Can this query be improved?

I'm working on a event tracking system which uses a handful of lookup tables as well as the primary logging table. In a report I'm writing, an object can be selected to view statistics against. The ...
2
votes
1answer
115 views

MySQL server taking multiple seconds to run COUNT(*) query using an index on a MyISAM table

I have a MySQL database on a shared webhost, which has been running with its application for a couple years. Today, this simple query: SELECT COUNT(*) FROM tickets WHERE id = 123 Took 16 seconds to ...
1
vote
1answer
929 views

InnoDB, What would cause: “db1/tableABC contains 6 indexes inside InnoDB, which is different from the number of indexes 5 defined in MySQL”

MySQL 5.5.24 . We have InnoDB tables that grew almost by double (from 50GB to 100GB - file-per-table enabled) in a few weeks due to a project we are working on. The tables were freshly dumped and ...
3
votes
4answers
3k views

MySQL unique constraint across two columns

I have a table defining relationships Src smallint(5) unsigned NOT NULL, Dst smallint(5) unsigned NOT NULL, other fields I need to add a constraint that says if a given values is present in one of ...
0
votes
2answers
104 views

MySQL query optimisation advice for search query

We are currently in the process of trying to re-write a very large and nasty dynamic search query. The search term for this query was 'red christmas napkins' and these 3 keywords have been added to ...
2
votes
1answer
671 views

Does mysqld wait for init-script code to complete before opening the instance?

I'm trying to automate the execution of an application code at MySQL startup. The init-file server option seems handy and I need to know if mysqld waits for all the code in the init-file to complete ...
3
votes
1answer
6k views

how to retrieve rows of different dates using IN clause

My table structure: +-----------------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
3
votes
1answer
474 views

adding another table to existing MySQL replication scenario

I'm running a simple MySQL replication scenario MASTER ➙ BLACKHOLE ➙ SLAVE, replicating only a number of tables via replicate-do-table on BLACKHOLE and SLAVE. Now I would like to add several more ...