MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
31 views

InnoDB deadlock with INSERT .. SELECT

I am trying to understand why a deadlock occurs when two workers in parallel are running INSERT .. SELECT queries on a table which has both 1) auto incremented primary key, 2) a unique index on ...
0
votes
0answers
33 views

Storing DB credentials in an application source code?

I am making an app which connects to a DB, and the app will have plenty different userslogins. Is it a good design choice to have a specific DB login/password for the application, using which the ...
0
votes
0answers
10 views

How to migrate a mysql server in production to Percona XtraDB Cluster

I need some help : I have a mysql server in production and i want to migrate all the database to an existing cluster (percona xtradb). For now , my idea is : 1) Lock all tables 2) do a dump per ...
0
votes
1answer
17 views

I cant figure out the error probably with the date field in this mysql stored procedure

This is my empDetails table I am trying to create a stored procedure on. mysql> describe empDetails; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | ...
0
votes
0answers
43 views

Double subquery is very slow, but independently they are fine

I have this query: SELECT mac, pid, node_category.name, status, computername, user_agent, detect_date, regdate, unregdate, last_dhcp, node.notes, (SELECT ip FROM iplog WHERE iplog.mac = node.mac ...
1
vote
1answer
15 views

MySQL - reformat a column value dependant on existing value

Am needing to provide a concatenated field from a select query which is dependant on existing values. SELECT `delegate`.`FirstName` AS `First Name`, `delegate`.`LastName` AS `Last Name`, ...
0
votes
1answer
25 views

Recovering MySQL root password (XAMPP)

I posted this on StackOverflow and was told to come here. I have an XAMPP MySQL installation and want to recover the root password. I am aware that questions have asked this already, however they are ...
0
votes
0answers
19 views

MySQL master/slave replication - replicate all tables

I am doing a master/slave replication, but getting errors about my tables does not exist. Is there not a way where i can make the whole table structure replicate aswell? Thought it did on default tbh. ...
0
votes
1answer
36 views

Proposal: MySQL blob handling revision [closed]

Why does MySQL store blobs in the direct table rather than off to the side so that if it needs to read the corresponding stuff it will read the stuff off to the side? Essentially it would create its ...
0
votes
0answers
34 views

Unresponsive MySQL database

I've setup a MySQL server on Ubuntu machine. When running a heavy query (the table has over 1M rows), the database becomes unresponsive. Upon connection it returns: Can't connect to MySQL server ...
0
votes
0answers
19 views

Optimised way to search over 2 milllion poi data in mysql

We have over 2 million poi data in our mysql db. At any time we need to find what is the nearest point based on the following formula found at this link http://sqlfiddle.com/#!2/abba1/4 select *, ( ...
0
votes
1answer
33 views

SymmetricDS not using Lookup Transform on primary key

I'm syncing records between two MySQL databases using SymmetricDS, but need to keep the primary key (fieldname = "id") fields unique. Symmetric doesn't seem to want to obey the "lookup" transform_type ...
1
vote
0answers
40 views

MySQL 5.1 High Cpu usage on CentOS 6

I am running MySQL 5.1 on a CentOS 6 server with 16G ram. I got also apache on it. MySQL is constantly using too much cpu. top - 17:40:36 up 112 days, 12:13, 1 user, load average: 1.24, 1.72, 1.82 ...
0
votes
1answer
26 views

Useful guide & FAQ for Application+SQL [closed]

Of course when you are making your first application for fun it doesn't matter that much how you deal with things. But when you need everything to be up to the current standards you don't always know ...
0
votes
0answers
61 views

Change a query so it no longer takes 20 mins and uses a 93G tmp table

Edit: So how would I go about changing this query to something more sane (given that I can work around the hibernate madness) This might be a whole different problem, but Hiberate has created a ...
0
votes
1answer
50 views

Can a primary key of one table be a foreign key of another table?

I am working on a scenario like this : I have an employee table which has columns like this Employee_ID First_Name Last_Name Employee_Designation Also I have a Joining_Vital table Employee_ID ...
2
votes
1answer
27 views

Innodb DB Consuming IO even when no operations are being done

I am currently doing some stress testing where I am pushing inserts to the database as fast as I can. The simulation inserts 10000 records per second. After inserting around 200 million records I ...
2
votes
2answers
209 views

“All joins can be rewritten to sub queries”, is this statement true?

I wonder if there any case that a join can not be converted to a sub-query. BTW: I am not discussing advantages and disadvantage of using sub-query or using join.
1
vote
1answer
40 views

How to change rows to columns in mysql?

I have data as following(it is just an sample, I have 100+ different types.): advertiser_id activity_type counts 1 1 2 1 2 5 2 1 ...
0
votes
2answers
45 views

Retrieving row ranking in a table

I'm aware there are already many questions alike however none of those answers helped me achieve row rankings in a table. I have a table set up as so: ...
0
votes
0answers
13 views

mysqldump replace option - ubuntu / mediawiki

I know this is probably a silly question, but I'd like to be sure, rather than make assumptions. There's the option to use 'replace' in a mysqldump, which uses the 'REPLACE' rather than the 'INSERT' ...
0
votes
0answers
33 views

How to setup complex multi-column index for massive table

I've searched what I could search on Google, SO, etc and haven't found an answer that would seem to fit what I'm looking for. I'm hesitant to make a post about this as I'm sure the answer is out there ...
0
votes
1answer
34 views

Can't restore from mysql dump file

I used mysqldump command to export/dump .sql file with rows like: String cmd1 = mysqldump + " -h" + host + " -u" + username + " -p" + password + " " + "--compact --no-create-info " + "--where=ID=" + ...
1
vote
1answer
26 views

mysqldump - select table in database only if it contains a specific string

(I'm new to Database Administrators, and am not a Database Administrator. However I originally asked this question on Stack Overflow (here) but didn't get any answers. Sorry if this is not the right ...
0
votes
2answers
36 views

0xDBE how to connect over local socket?

I want to connect to mysql over socket. This is what I'm trying to do: and it's not working. This is how it works in mysql workbench:
0
votes
1answer
26 views

how to setup [master] - [master-slave] - [slave] mysql replication

i am trying to setup a mysql replication architacture between 3 mysql server. (by following some tuto like ...
0
votes
1answer
29 views

duplicate entry issue with large mysql database table

I have a very large my sql database table. The other day, for no apparent reason, I now keep getting :- Duplicate entry '148245' for key 'PRIMARY' If I remove the entry at with primary of 148245, ...
0
votes
4answers
31 views

How to subtract fields and insert the value in other field automatically?

id val1 val2 total 1 10 4 10-4(automatically get the value) . . . . I am using php and mysql. I have a total column I need to make calculation automatically in a field, how it ...
1
vote
1answer
23 views

MySQL Delete Rows from Slave

I want to truncate the majority of data on a slave such that it only has the most recent records. I'll eventually demote the current master, making it a read-only archive, and promote the truncated ...
0
votes
2answers
17 views

How to delete a column named grave accent in MySQL

As title. Every time when i try to execute the query: ALTER TABLE tablename DROP COLUMN ```, got syntax error, which is understandable. So, what should i do to delete this column whose name is the ...
0
votes
2answers
117 views

Getting a Deadlock,

I have this code : public void saveItems(List<Pair<Item, MapleInventoryType>> items, int id) throws SQLException { PreparedStatement ps = null; PreparedStatement pse = null; ...
0
votes
0answers
12 views

MySQL loading XML

Hello i have this XML format (illustration) [Im using MySQL 5.6] <ROWS xmlns:xsi="..." xmlns="..." xsi:schemaLocation="..."> <TABLE col1="7169340714" col2="2" col3="2013-09-05T15:19:42" ...
2
votes
3answers
123 views

Why MAX() of SUM() giving wrong output?

I have a table employee id name salary city 1 ram 50000 c1 2 sham 20000 c2 3 jadu 80000 c1 4 madhu 90000 c4 5 hari 10000 c2 6 gopal 34000 c3 7 ...
1
vote
2answers
44 views

left join not working

CREATE TABLE `table1` ( `id` INT(10) NOT NULL ) ENGINE=InnoDB; CREATE TABLE `table2` ( `id` INT(10) NOT NULL, `value` INT(10) NOT NULL ) ENGINE=InnoDB; INSERT INTO table1 VALUES (1), (2), (3), (4); ...
0
votes
0answers
24 views

MySQL DATE vs DATETIME read/write performace

Did anyone perform accurate read/write performance comparisons of MySQL's DATE vs DATETIME column types? Please do not bother writing "It should be faster" or "I think it's faster". I am looking for ...
0
votes
2answers
34 views

Need Help maintaining multiple databases for a multi-tenant application?

i need some information about multiple databases for a multi-tenant application ? I am working on a project in which we are maintaining separate database for every company? The Companies Database ...
0
votes
1answer
26 views

Mysql speed issues with SHOW COLUMNS or SHOW FIELDS

To avoid making code changes every time we have a database change, we just use the SHOW FIELDS (Alias of SHOW COLUMNS) to get all of the columns for Inserts. We call this multiple times for multiple ...
0
votes
1answer
30 views

Schema check or “DATE as separate table”

I'm dusting of my SQL, and would love to know if I've got my mental ER-diagram right. http://sqlfiddle.com/#!2/c5c59 Basically, I've got 10-15 different datasets (actividades) from several locations ...
0
votes
0answers
27 views

how to sync local mysql database and live server database?

my plan is to store data in my local mysql database and after a time intervel it will update my live mysql database remotely. Is it possible? i'm planing a inventory management script in php mysql. ...
0
votes
0answers
19 views

Infinite loop in MySQL when connecting to database

I am running xampp on Windows - Apache and MySQL start fine, but when I try to go to the directory where I have my example app, it loops infinitely waiting for localhost: The log shows: 2014-08-22 ...
0
votes
2answers
60 views

How to break table into two without losing performance?

According to http://stackoverflow.com/a/174047/14731, splitting away infrequently-needed columns frees up the cache allowing faster retrieval of the commonly-used columns. I've got a table whose ...
0
votes
1answer
50 views

Multi-Column Full Text Search Going Very Slow

I have a full text index on a table articles for the columns content, title and keywords When doing the search on each of the columns, like so select count(1) from articles where ...
0
votes
1answer
6 views

Determining which database instance makes biggest IO

Assuming that I have a dedicated server on which I am running multiple instances of mysql and postresql servers. How without iotop determine which instance in particular time (proc/pid/io shows data ...
0
votes
0answers
21 views

Combining 2queries: One filtered Users on Role, One Select users with only one specific role

First query: Selects All users with specific rid ( 3 or 4 or 19 or ...). SELECT u.uid AS uid, u.name AS name, fn.field_full_name_value AS field_full_name_value FROM users u LEFT OUTER ...
6
votes
1answer
154 views

Huge differences of performance of MySQL in two servers

We have a MySQL server installed in two different machines, a testing server and a production server, both windows, which is used by a web application. The problem is that there are HUGE performance ...
1
vote
1answer
37 views

Revoke write privileges vs setting database to readonly

I am planning to do a database migration to a new server. During the transition stage, I do not want Apache user to be able to write anything to the database. I have two options. Revoke write ...
-1
votes
1answer
48 views

What makes yum difficult with RAID [closed]

I as working with someone on a redhat server using RAID 10 with MySQL, and they mentioned it is hard to use yum when having a raid setup. Is this true? If so, what actually makes it 'difficult'?
1
vote
2answers
53 views

Difference between On Delete Cascade & On Update Cascade in mysql

I have two tables in MySQL database- parent, child. I'm trying to add foreign key references to my child table based on the parent table. Is there any significant difference between ON UPDATE CASCADE ...
0
votes
1answer
35 views

MySQL configuration to connect to multiple local applications

I've installed FreeRADIUS, MySQL and FreeRADIUS management (a web-based app) on the same machine, with both FreeRADIUS and FreeRADIUS management using same user to connect MySQL. My problem is when I ...
2
votes
2answers
38 views

Copying datadir directory enough for backing up a database with InnoDB tables?

I have an innodb table with 80 million rows and it's extremely slow with mysqldump so I'm thinking of copying the datadir directory for a backup / transfer. I used to copy database with only MyISAM ...