MySQL : Open-Source, Relational Database Management System
1
vote
1answer
75 views
Database Designing for a Ecomerce website
I am new to database designing. I am designing a database for an eCommerce website, there is a lot of products to be updated, but while designing for product specification table I do not understand ...
1
vote
1answer
70 views
I would like to query a range of criteria on multiple columns in MySQL
I have 3 columns in a mysql table. I'm using Innodb engine. I want to be able to search for some values on those columns and also anything close to those values. For example :
We have 3 columns as ...
1
vote
1answer
60 views
Configuring MySQL (my.cnf) for MyISAM and InnoDB
I want to convert some (not all) the MyISAM tables to InnoDB.
Here is my current my.cnf: pastebin.com/FW8YXtLQ
What would be the best configuration? (need to add InnoDB settings, byecause there are ...
0
votes
1answer
79 views
Modify MySQL foreign key related column without dropping foreign key relation
I need to modify a foreign key related column. When I tried to alter it I got "Error on rename of 'x' to 'y' (errno: 150)" error. So I googled and found foreign key relation is the 'villain'. I ...
0
votes
2answers
283 views
How to sync MySQL database between two servers
I need to sync a MySQL database between two servers.
If a new record will be added in server A, then server B fetch new record.
If a new record will be added in server B, then server A fetch new ...
0
votes
0answers
31 views
Are there any disadvantages to partitioning on financial year?
Our current set up has one table per financial year (May 1- April 30). Each table has approx 1.5 million rows. We have about 8 years of data, and will obviously be adding each year.
The majority of ...
0
votes
2answers
49 views
How to use and optimize subquery on 100 million rows
What I'm trying to do is running a job on more than 100 million domains which haven't processed before.
I have two tables, "domain" and "domain_setting", on every batch (10.000 domains per batch) I'm ...
1
vote
2answers
347 views
Mysql innoDB write operations are extremely slow
I'm having serious performance problems with MySQL and the InnoDB engine. Even the simplest table makes writing operations (creating the table, inserting, updating and deleting) horribly slow, as you ...
-2
votes
0answers
32 views
Database Design [closed]
I have a requirement like creation of database design for examination of school student.
There we have different types of exams like quarterly exam, half yearly exam and the final exam. For each class ...
0
votes
1answer
172 views
What is the actual difference between innodb_autoinc_lock_mode 0,1,2
I would like to understand the differences between innodb_autoinc_lock_mode options 0,1 and 2 when parallel load data infiles are given.
I see in "0" option, it locks the entire table and does the ...
0
votes
2answers
52 views
mysqlslap chokes on strings that contain “;” delimiter?
I'm having a problem passing preexisting files full of SQL statements into mysqlslap.
For example, I have a file named create.sql that contains my table structure (as dumped by mysqldump), with ...
1
vote
1answer
182 views
Queries getting stuck on very simple COUNT queries
process list:
| 16004 | metrics | xxx:64616 | metrics | Query | 29064 | Sending data | SELECT COUNT(*) FROM Plugin where LastUpdated >= '1356856200' | ...
0
votes
1answer
24 views
Foreign key with multiple references
I have the following three tables in mysql database named "THE_COLLEGE"
mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | ...
0
votes
1answer
62 views
Lock wait time out exceed restart transaction
I had got error on the lock wait time out so below I got 3 samples taken. One I took before the increase innodb_lock_wait_timeout to 120. So is there anything else I must tweak based on the logs ...
1
vote
1answer
73 views
Should I increase max_connections in AWS RDS t1-micro for MySQL?
I have an AWS RDS t1-micro running MySQL 5.5. It gives me too many connections error. I checked and it allows 34 maximum connections concurrently. What I have read is that i can increase this max ...
2
votes
1answer
53 views
Load different date formats in mysql
I have a date field in my datafile which I am trying to load in MySQL, but the date field is in dd-mon-yy format and MySQL only understands YYYY-MM-DD.
Is there a way by which I can load DD-MON-YY ...
2
votes
1answer
29 views
To make a LVM backup of a MySQL database, would I have to reduce the logical volume size?
I am trying to learn how to do a Ubuntu LVM backup of a MySQL database.
For this test I installed a fresh copy of Ubuntu 12.04.2 LTS on a new box, along with MySQL 5.5. The box has one hard drive. ...
2
votes
2answers
97 views
Why does adding LIMIT to my query make it crawl?
Simple query:
select sum(score) total,name,gender,dob,country
from users join scores on users.id = scores.user_id
where date between '2012-01-01' and '2012-01-31 23:59:59'
group by scores.user_id ...
0
votes
0answers
21 views
Req: Typical location of mysql query cache
My MVC framework (CodeIgniter) wants to know where my database (mysql 5.5) writes its query cache. My Ubuntu installation is unexceptional. Would that be /var/lib/mysql/ or further down the chain, ...
2
votes
1answer
47 views
What is the syntax for querying a spatial column?
What is the right syntax to get all the points within the bounding box? The table consists of a lat and lng column and a spatial column "pt" generated from the lat/lng values.
calculated the topleft ...
0
votes
1answer
58 views
Complicated join with where clause
I have four tables:
sales
sales_thumbs
sales_images
sales_sizes
sales table:
+--------------+---------------------+------+-----+---------+----------------+
| Field | Type | ...
0
votes
1answer
46 views
Query that ranks students order by marks and give the position of student even when there are students with equal marks
I need an sql that will rank students order by marks scored in a specific examtyp e.g CAT1 and give exact position even if the students have equal marks e.g in this sample data admNo 2525 and admNo ...
0
votes
1answer
149 views
Execution of a java program by a trigger
Can an update trigger execute a java program?
I have a table T1 with a column named Flag. Whenever Flag changes, I want to run a trigger that results in an execution of a java program.
3
votes
1answer
43 views
Way to add referencing and referenced entities of a specific entity in E-R Diagram MYSQL
Is there any way to add entities that are referencing a specific entity in an E-R Diagram. I have a database containing 400 tables. I want to see a view containing a specific entity and its ...
1
vote
1answer
121 views
Master updates Slave but not Master itself
I have a setup master/slave in which applications are pointed only to master. Yesterday Master had got crashed due to "multi bit error on dimm detected" in Front indication panel in orange color.
...
0
votes
1answer
830 views
MySQL incredibly slow on WAMP and XAMPP
I've installed WAMP on my new Windows 8 machine and for some reason when I use PHPMyAdmin to import an sql file (it's only about 5mb), it takes several minutes before timing out. And then when I check ...
0
votes
1answer
56 views
Update one table from another table while sorting that table based on one column
This is the problem I'm trying to figure out in MySQL. We have an old table contains some forms submitted by our users. Somehow, the previous decision was each time a user comes to this survey, a new ...
0
votes
1answer
15 views
Is the key_buffer_size applicable to myisam tmp tables?
I have a database about 750GB in size. It's all innodb.
Larger analytical queries often need to group by several columns or use distinct so it's common that MySQL will have to create tmp tables. The ...
1
vote
1answer
163 views
increasing mysql table open cache?
I often read that it is best to increase this variable slowly. Can someone explain why? My status indicates that I should increase it... What is best practice / "slowly"?
Thanks!
1
vote
1answer
87 views
select count(*) in mysql 5.5 innodb— rewrite advice?
I need advice on how to rewrite a select count(*) query for innodb tables mysql 5.5. in new environment its very slow...
select count(*)
from mails3
join questions using (question_id)
where ...
0
votes
1answer
173 views
MySQL5.6 on Mac OS X 10.6.8 problems when granting permissions to root
I'm having serious problems with a MySQL 5.6 instance on a Mac Server.
We had to upgrade MySQL to a newer version, but it turned to be a bad idea, as we lost control to it. We had a backup of the ...
2
votes
2answers
99 views
EMS SQL manager permission problem
I have a strange problem with EMS SQL Manager.
I'm using MySQL 5.5 with linux servers. One of my developers need to edit store procedure, so I grant him:
GRANT CREATE ROUTINE, ALTER ROUTINE ON ...
0
votes
2answers
38 views
Why does this procedure raise a privilege error?
I am having trouble getting the following procedure to run on a remote MySQL database - the given error is privilege based (#1227). Locally, the procedure runs fine.
QUESTIONS
Could somebody help ...
0
votes
1answer
37 views
MySQL Replication and Triggers
I have stumbled upon an interesting MySQL Error message that I do not really know how to interpret.
The setup:
There are two tables A and B.
When data is written or updated in the table A, then a ...
1
vote
1answer
270 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 ...
3
votes
1answer
57 views
How can I get this query to use it's index?
Queries matching this query below shows up in the slow log (with different contentVersionId & modifiedDateTime).
As I don't really know what I'm doing I tried adding indexes to all columns in ...
7
votes
2answers
4k views
What is the reason of Host 'host_name' is blocked in MySQL?
When I use incorrect password to connect to mysql server, and the test count is over the max_connect_errors, and I find the Aborted_connects is also arise as the count I test, but the host is still ...
2
votes
1answer
63 views
Bi-directional replication for the same MySQL table
AppA stores/retrieves data from dbA.tableA
AppB stores/retrieves data from dbB.tableA
tableA definition is the same across these databases. To start with dbB.tableA was copied from dbA.tableA ...
0
votes
1answer
52 views
Plugin 'FEDERATED' is disabled
Im trying to start Mysql using easyPHP : the response is an alert window to a log file. The main error is <-- Plugin 'FEDERATED' is disabled -->.
Through my research on internet i found that the ...
3
votes
2answers
1k views
Trigger in MySQL
Please help me for few question in SQL..
I want to create the trigger which prevents insertion in the reservation table of flights (numflights), which does not exist.
Tables
...
1
vote
1answer
39 views
Archive partition before delete it?
I have manage to create an event and with the following scripts could both create and delete the partition. The issue now I need help on how to save the partition before its deleted? Should I use ...
2
votes
4answers
217 views
TokuDB not much faster than MySQL
I have converted a MySQL database with 80.000.000 rows to TokuDB.
Now when I run:
select count(id) from xxx where active=1
it takes 90% of the time of the normal MySQL request.
What do I have to ...
3
votes
3answers
210 views
How to change data structure in mysql using mysqldump without deleting files
Essentially what I'm trying to do is sync a production server with a sandbox server, but only the table structures and stored procedures. The procedures aren't any problem since they can be overriden, ...
2
votes
2answers
49 views
Is it okay to use rsync on InnoDB database if the MySQL server is shutdown?
Is it okay to use rsync on InnoDB database if the MySQL server is shutdown ?
For example :
/etc/init.d/mysql stop
rsync -av /home/mysql/mydb/ root@newserver.com:/home/mysql
1
vote
1answer
290 views
MySQL performance tuning + queries stuck on “Copying to tmp table”
The latter part of the question's title (queries stuck on "Copying to tmp table") has been addressed many times, and I have spent a fair amount of time researching this. I would appreciate it if you ...
2
votes
4answers
560 views
Two-sided UNIQUE INDEX for two columns
Creating a table with composite PRIMARY KEY or using UNIQUE INDEX for two columns guarantee uniqueness of col1, col2. Is there a tricky approach to make the reverse order of two columns UNIQUE too ...
-1
votes
1answer
49 views
Why is this mysql query wrong? [closed]
I try to run the following sql query in mysql
SET @rank:=0;
WITH TEMP
(
SELECT @rank := @rank + 1 AS ROW_ID , UUID() as RANDOM_VALUE,D.*
FROM house_information_new D
ORDER BY ...
2
votes
1answer
96 views
innobackupex is failing while exporting backup
I am using perl script which is working fine in one of my other box which used to take individual schema backups & finally take all schema together using percona innobackupex.
I have following ...
0
votes
0answers
15 views
Range query from month and year [migrated]
I have a table like this:
ID month year content
1 4 2013 xxxxx
2 5 2013 yyyyy
3 6 2013 zzzzz
4 8 2014 fffff
I want to query it based on a year ...
1
vote
2answers
115 views
MySQL information_schema doesn't update
I have a database, say abc, in mysql server. It has only one table named test. test uses innodb engine and I've set innodb_file_per_table to true.
After I run the query delete from abc.test, I want ...
