MySQL : Open-Source, Relational Database Management System
0
votes
1answer
43 views
Large INSERTs performance optimization
I have 15 Amazon AWS EC2 t1.micro instances which simultaneously populate Amazon RDS MySQL d2.m2.xlarge database with data using large INSERTs (40000 rows in query).
The queries are send ...
0
votes
0answers
22 views
How to perform ETL from RDBMS to Neo4j
I have a mature 50+ tables web application based on mySQL. In order to do some advanced data mining I want to use Neo4j and the goodnes of cypher. However I'm having a hard time migrating my data from ...
0
votes
1answer
24 views
Ubnutu/MySQL - Error “Unable to locate package innotop”
I am using Ubuntu 12.04.2 LTS, with MySQL 5.5.29.
Im trying to install innotop for database monitoring, but getting an error "Unable to locate package innotop"
root@server1:~# apt-get install ...
2
votes
0answers
37 views
Why is MySQL order by performance poor within a single partition of a partitioned table?
I have to store some sequence numbered data in MySQL. I have about 300,000 data items per day for about a 10 year span. Let's say the table structure is just sequence number (a big int) and data (a ...
0
votes
0answers
23 views
MySQL poor order by performance on partitioned table [duplicate]
I have to store some sequence numbered data in MySQL. I have about 300,000 data items per day for about a 10 year span. Let's say the table structure is just sequence number (a big int) and data (a ...
1
vote
2answers
21 views
Is it okay to rename the MySQL data files (FRM) manually?
Like everyone, I realized the case sensitivity issues in MySQL of late. I wanted to convert my upper case table names to lower case using this solution but I forgot to do Step 1 and completed the ...
1
vote
1answer
40 views
Drop database locked the server
I tried to drop one of the databases on the server. That locked the entire server up so I stopped the mysql process via the console.
I restarted the server, started mysql and now it seems hanged on:
...
0
votes
2answers
48 views
mysql replication successful but slave not replicating
I have created a mysql master-slave configuration and things look fine .
the " show master status;" on slave doesnt show any error . this is the output
Slave_IO_State: Waiting for master to send ...
2
votes
1answer
36 views
How to ensure that date-range queries involving multiple time zones are sargable
I am building a web analytics application for search engine traffic only. You can see some screenshots here: http://myhappyanalytics.com/
It works similar to Google Analytics but it only saves and ...
0
votes
1answer
23 views
passing --single-transaction in mysqldump command line tool
I'm using MySQL GUI tool to backup my innodb database and I use the option '--single-transaction' to get a consistent copy as backup.
I have however, noticed that if I backup using command line ...
0
votes
1answer
38 views
Order of where clauses in MySQL
If I have a MySQL table which has content from the last 3 months I actually do the following:
SELECT SQL_BUFFER_RESULT SQL_CACHE count(id) cnt
FROM `mDelivered`
where _campaign = 2
and `session` = ...
-1
votes
0answers
39 views
open source ETL tool works with cloud [closed]
I am trying to be familiar with ETL tool. I will use ETL tool to build database on the cloud. For this reason, are there ETL open source projects which have ability to build database on cloud ? ( all ...
0
votes
1answer
25 views
How to “swap” out production tables in MySql
I have a MySQL table that holds a few thousand records which go out of date very quickly. A few times a day we run a slow process which needs to pull data from multiple sources and re-populate the ...
0
votes
0answers
20 views
'Offline' MySQL backup - server not starting
The MySQL server is no longer starting because the partition is too full. The issue seems to be ibdata (40GB). I wanted to follow these instructions (at 'InnoDB Infrastructure Cleanup')
Now I already ...
0
votes
1answer
28 views
In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table
On Windows, I upgraded from 5.1 to 5.5 no problem.
Copied my 5.1 data folder into my 5.5 instance
Started mysqld skipping grants
Ran mysql_upgrade
All good, but going from 5.5 to 5.6:
Copied ...
0
votes
2answers
37 views
Using a keywords table with multiple other tables
Using MySQL
I need help determining how to model a Keywords table to three different tables (Products, Certifications, and Exams) - I have included my model which is using what I refer to as ...
0
votes
1answer
41 views
MySQL DB Design
I am designing a db for a Social Network type website where users enter lot of information varying from family member details, education, employment, personal favorite such as TV, movie, music, food, ...
1
vote
1answer
29 views
How to add additional tables/fields into a query with multiple inner joins?
Given this query (many thanks to RolandoMySQLDBA from question Select records that do not have associations outside a certain list), how do I add other fields from other tables into my result?
SELECT ...
1
vote
1answer
42 views
Select records that do not have associations outside a certain list
I have a mySQL table of nodes and each node can be associated with many categories. I have a list of category ids that are acceptable (498, 499). I want to select only nodes who do not have ...
0
votes
1answer
98 views
MySQL : How to get rows where count() is null?
This is my SQL request :
SELECT COUNT( b0_.id ) AS sclr0, d1_.codeLieu AS codeLieu1, d1_.nomLieu AS nomLieu2, d1_.lngLieu AS lngLieu3, d1_.latLieu AS latLieu4, m2_.libelleMention AS libelleMention5, ...
0
votes
1answer
45 views
MySQL Synchronise Data From One DB Table To Another & Vice-Versa Using Triggers
We are migrating from one database structure to another - very slowly. The system is MySQL. There are two databases. For example's sake, we will call the databases old_db and new_db. Both databases ...
0
votes
0answers
30 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
0answers
27 views
Optimising table efficiency format integers only
I currently have a table with 1,136,053 rows and 5 columns in a MySQL table.
They are all integer values and appear to be slowing down as I am increasing the rows.
I have indexed each of the ...
1
vote
1answer
49 views
SQL Server-MySQL master-master replication
We are looking into setting up a master-master replicated MSSQL database and I am interested to hear of any potential pitfalls that we may run into along the way and generally any advice that you ...
0
votes
0answers
15 views
Should store procedure cached in Mysql
Should store procedure cached in Mysql? If yes, How long it is stay in cache?
In my case, When I call one store procedure first time, It is giving me result in 1sec, after that it gives me result in ...
-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
2answers
48 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
1answer
56 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 ...
1
vote
1answer
35 views
MySQL - how can I make a select statement get blocked?
Hello I am trying to intentionally make a SQL select statement get blocked by another simple SQL delete or update statement, for the purpose of learning. I prefer only InnoDB tables.
To prepare the ...
0
votes
2answers
40 views
MySQL Slow query joining on subqueries with max
I'm creating a report based on user input (i.e. a search tool) that grabs data from eight tables and will output them to the screen.
Here are the create table statements. I'm very aware of how badly ...
2
votes
1answer
51 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 ...
1
vote
1answer
55 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
2answers
87 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
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
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, ...
0
votes
1answer
44 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 ...
2
votes
1answer
105 views
+100
MySQL Hanging Completely when `ALTER TABLE… ENABLE KEYS`
I know very little about database administration but I have to deal with some very large tables on my site.
This server has 64GB of RAM and Intel Core i7-3820 (4 x 3600 MHz). Most of everything it ...
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
31 views
MySQL: Slow INSERTs to MEMORY table
I've been trying to work this issue out, been really killing me! We've been experiencing some slow database queries for a Prestashop installation that we host, and a look into the query log revealed ...
0
votes
1answer
14 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 ...
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 ...
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 ...
0
votes
1answer
35 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 ...
0
votes
1answer
31 views
How to link several tables in MySql?
I'm a newbie at Database Designing. What I want to do is perform several actions at once.
I have a main users table which holds all the data for a user at a website. Then I have a primary key as the ...
2
votes
2answers
48 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
0
votes
1answer
48 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 ...
-1
votes
1answer
48 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 ...
0
votes
1answer
57 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 ...
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 ...
0
votes
2answers
50 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 ...
