MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

-2
votes
1answer
293 views

Second time query execution using different constants makes faster? [closed]

Can someone explain or direct me how execution on indexes happen with different constants at intervals in Mysql. I notice only for the first execution on the table it takes time, after that with ...
0
votes
1answer
188 views

Is using the alpha version of Percona 5.6 safe? [closed]

I'm really sorry if my question is kind of very basic, but is something that is really making me thinking a lot about it for a long time. As Percona has a lot of lines of optimizing codes, I run it ...
1
vote
3answers
139 views

MySQL Permissions

A web application that we're working on creates a new database for each user that signs up. With this, I'm setting up a database user for each of these new databases and wonder what the best ...
1
vote
2answers
3k views

How to continuously keep two databases in sync

Me and a friend are developing a range of programs which uses a basic currency called "points" each user can gain and spend points for free. The problem we face is MySQL we can connect to the ...
0
votes
2answers
1k views

Does MySQL Replication hamper the performance of my DB?

I am not exactly a 'Qualified' DBA, but yes i am in charge of my own DB which we use in our social app. I have recently implemented Master/Slave Replication on my Database for obvious reasons. What ...
0
votes
1answer
163 views

Large queries on MyISAM tables crashing MySQL

I have a PHP website, which is using the third-party software ResourceSpace, that when I installed it, it used my default storage engine to create the tables, which at the time was MyISAM. Now that ...
2
votes
1answer
2k views

Two insert statements inside trigger - Duplicate entry '521-2' for key 'PRIMARY'

I'm trying to create an insert trigger so that when a new user is inserted into users table (via site, httpd and php), part of this information is inserted in another table that manages access to a ...
0
votes
2answers
100 views

Find the average number of n:m connections of two tables?

I want to know to how many lists a user is assigned in average. User ==== id name Lists_Users =========== user_id list_id List ==== id title Is there a good way of receiving this number via ...
2
votes
2answers
180 views

Tinyint structure is confusing

I am confused to see this tinyint(3) structure. If tinyint can store only 1 byte then what is the purpose of giving the size in brackets? Is there something special with this parameter in MySQL?
1
vote
1answer
83 views

Single entry point for mysql cluster

I have a mysql cluster setup and working. When connecting to it I specify one of the mysql nodes (mysqld) in my connection string. However, if this node goes down it doesn't automatically fail over to ...
2
votes
1answer
2k views

Why does that query cause lock wait timeouts?

From time to time, I find a lot of these errors in my PHP error log: MYSQL.1213: Deadlock found when trying to get lock; try restarting transactionSQL The problem persists for about 2 or 3 minutes. ...
4
votes
1answer
932 views

How can I determine the size of a dump file prior to dumping?

How can I determine or estimate the size of the SQL dump file prior to using something like mysqldump?
0
votes
1answer
320 views

Is there an easy way to replicate MySQL data?

My software is deployed in various cities, each have a database, but they have different authorizations such as: central DB /\ / \ DB1 ...
0
votes
2answers
2k views

Replication on MySQL server

I had to stop the slave server to test something. After I started the server again there is a problem with replication on MySQL server On the problematic server mysql> SHOW SLAVE STATUS\G ...
1
vote
2answers
86 views

Database and query optimizacion

I have a database containing three tables: tbl_database (main table is the primary), tbl_cmdatabase and tbl_blacklist. The three tables share the same structure but differ in the number of records ...
2
votes
2answers
709 views

Mysql notifier Error

i'm getting error when opening Mysql notifier High Severity Error Element 'UpdateCheck' was not found. Line 3, position 2. My mysql server is running. why it's showing this error.
0
votes
2answers
1k views

Fastest way to delete matching rows in MySQL?

I have more than 10 million records in a table. I want to delete the records where the email column contains .ac.. What is the fastest method to perform the task? Table Schema CREATE TABLE `master` ...
0
votes
1answer
125 views

Looking for performance improvements for this query

This query is taking a long time to execute. We are expecting results in 0 sec, if possible. Please help me to do any other following: Rewrite the query Suggest any indexes Any other optimization ...
1
vote
2answers
97 views

What settings should I use in my.cnf

We have about 5-6 different internal webapps (staff use only) that we will be transferring across to a new virtual server, however I need to setup our my.cnf settings first and I am lost (I am web ...
1
vote
1answer
23 views

Reserving dates with quantities, table schema wrong?

Okay I have a table like so. ID Productname Quantity Image QuantityOut LastDateReserved 1 productOne 5 'image.jpg' 3 2013-03-15 2 ...
1
vote
1answer
50 views

INNODB real-time locks?

How can I view real-time locks in innodb when information_schema.innodb_locks is not available?
2
votes
1answer
441 views

Need to suppress rowcount headers when using \G

Is there a command to suppress the rowcount headers and asterisks when using '\G' to execute a SQL statement? I am executing mysql with the -s and --skip-column-name options, but these don't suppress ...
0
votes
0answers
52 views

Checking of one specific element of one array is in another array?

Suppose I have two arrays. One looking like (3, 9, 10, 39), and one looking like (4, 10, 9, 48, 391, 2). How would I (through pure MySQL) check if one of the elements in the first array is inside the ...
2
votes
1answer
186 views

MySQL monitor prompt does not return for slave

I'm trying to fix a replication error (or an ongoing failure to replicate) between our master and slave MySQL databases. The procedure I have asks me to use MySQL Monitor to stop the slave. stop ...
0
votes
4answers
1k views

How can I replicate some tables without transferring the entire log?

I have a mysql database that contains some tables with private information, and some tables with public information. I would like to replicate only the tables containing public information from one ...
-1
votes
1answer
194 views

How can I move 5 GB data from one database to another faster?

I'm using MySQL as database with Windows server. I'm having 5 GB of data in database. Now I need to change my server and the question is that how can I transfer complete 5 GB data to one mysql ...
1
vote
2answers
267 views

Why would splitting up a query make it faster, and can I/should I fix this?

The following query is for a web-based game. It sets the isTiedForHighScore column to true for all solutions that tied the score for a given "map." UPDATE solutions INNER JOIN ( SELECT mapID, ...
1
vote
1answer
553 views

Segmentation fault (core dumped) with self-complied mysql-5.1.9-beta.tar.gz

I am compiling mysql-5.1.9-beta.tar.gz source code under Ubuntu 12.04. My steps are following: tar -xzf mysql-5.1.9-beta.tar.gz cd /home/gaoyu/mysql-5.1.9-beta ./configure ...
0
votes
2answers
1k views

MySQL data too long error

One of the column in my table was initially created as a varchar(1000). Now there is a need to increase the size of that column to hold around 5000 characters. I used the alter statement to increase ...
0
votes
2answers
1k views

how to set a row's value from a certain row's value?

I have a MySQL table named "activities" with fields id(int), tstamp(timestamp), amount(decimal) balance(decimal). Decimal fields hold money values. id tstamp amount balance ...
1
vote
1answer
106 views

big db for managing user files and settings

Hi im about to start a project that will hopefully eventually hit milion+ users which is some kind of a site managment, problem is how to set the db in mysql, some people suggest way 1 and others way ...
2
votes
1answer
321 views

I have multiple sources INSERTing into a MySQL innodb table. My periodic aggregation script never makes accurate aggregates. Why?

I apologize in advance if this is a repeat. I'm not really sure how to properly ask for what I'm running into. I have a large InnoDB table set up. I have 3 sources that all INSERT concurrently at a ...
-4
votes
1answer
301 views

where can I get mysql-5.1.7.tar.gz or mysql-5.1.9.tar.gz? [closed]

I need mysql-5.1.7.tar.gz or mysql-5.1.9.tar.gz, but I can not find them anywhere, who can give me a link to find them, thank you very much!!!
0
votes
2answers
1k views

MySQL Slaves lag behind master

I have one master and four slaves. Sometimes all my slaves lag behind the master. I have implemented the heartbeat for monitoring replication lag. Now I am trying to find why the slaves are lagging ...
2
votes
1answer
837 views

Selecting minimum value using a subquery

I need to write a query to grab the lowest price from each merchant and output the price link (p_link) and some other information such as merchant name + rating. We have a table for prices ...
0
votes
1answer
418 views

MySQL replication between VPS and shared host

I have a VPS where I have ssh access and shared host with CPanel. Both have MySQL. I need to create a master/slave setup among the two server. As per this tutorial: How To Set Up Database Replication ...
1
vote
1answer
167 views

LOAD DATA INFILE on a MyISAM table blocks user read-queries, can we get around this?

I've got a large reference table, 100GB, in a MyISAM table, load it up from scratch goes well (4ish hours) using LOAD DATA INFILE. But we have 1GB each day we want to update in a batch process using ...
3
votes
3answers
1k views

Slave SQL thread got hanged

We have a master - slave setup with ROW based replication. We are seeing huge delay's on the salve even though there is no activity running wither on master or slave. When we looked in, we observed ...
1
vote
1answer
40 views

Synchronize creation of rows between tables

Lets say I have two tables, posts(id_pk,title) and posts_content(post_id_pk,blob). Whenever I create a row in posts, I want a complimentary row to be created in posts_content, with the same id. If I ...
1
vote
1answer
1k views

MySQL 5.6 OS X 10.8: how tell if my.cnf read?

With mysqld running, I expect to see that /etc/my.cnf has been used by at least one of the commands ps aux | grep mysql | grep -v grep mysqladmin variables | grep defaults-file showing the value ...
2
votes
1answer
672 views

Minimize locking on UPDATE with multiple WHERE columns

I have a problematic query that's causing a lock timeout: UPDATE <some_table> SET row1=<some value> WHERE row1 IS NULL AND row2 > <some value> ORDER BY row2 LIMIT 100 ...
1
vote
1answer
162 views

Does MySQL limit the number of users (not connections)

I have always believed that you can create as many users as you like. I have been creating a user per app with limited database/table access for only what that app requires. Recently I was told there ...
2
votes
1answer
120 views

Search within same ID accross several rows

My table has the following structure: +----+---------+------------+-------------+ | id | word_id | meaning_id | sentence_id | +----+---------+------------+-------------+ | 1 | 1 | 15333 | ...
3
votes
1answer
214 views

Can I use mysql to serve 100's of millions of small image files?

I need to serve 300,000,000 small image files totaling 1.5TB. Trying to unpack these files to individual files on the file system is next to impossible (1 MByte/sec throughput rates to create the ...
0
votes
1answer
7k views

How to get only month from MySQL date/time?

In my MySQL server, I have rows that have the date of my created content. Example: 2013-03-17 02:53:47 For now, I am using: $date = $fetch[thedaterownamegoeshere]; <?php echo $date; ?> But ...
4
votes
1answer
1k views

MySQL: Can't find .ibd files after turning on innodb_file_per_table

Question: This a question about MySQL. Let me give the specifics of my setup: Mac OSX 10.6.8 MySQL 5.5.28 Workbench 5.2.44 for all administration InnoDB engine exclusively In order to manage ...
1
vote
1answer
44 views

Multiligual database design

I assume that the best approach for many languages (5 and more) is concept based on: CREATE TABLE `language` ( `language_id` char(2) NOT NULL, `collation` varchar(64) NOT NULL, ...
1
vote
1answer
1k views

MySQL user created via command line can't access PHPMyAdmin

I'm trying to create a read-only user with access to one database in PHPMyAdmin. Since I'm a server newbie I followed some instructions line-by-line on setting up my database and PHPMyAdmin. As ...
0
votes
2answers
333 views

Remotely shutdown a MySQL server

I have MySQL 5.0.27 running on a Linux server. It is consuming too many resources (it is running about 80 tasks loading a database). I am unable to log into the server to reduce the number of tasks ...
0
votes
1answer
28 views

content types of insertion

I have a CSV file that contains a lot of integer values. Is it better to insert these individual values in bulk as in a full transaction or should I be inserting the CSV file itself into the MySQL?