MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
8 views

Mysql join two tables and limit by first input?

i am only starting to play with MySQL and i seem to have been stumped by one problem. I have one table containing a number of fields including a primary id field. I have a second table containing ...
0
votes
1answer
27 views

MySQL - When Command column in Processlist shows “Prepare” or “execute”

Can someone please highlight when a "Command" shows "Prepare" or "Execute" for "Show Full Processlist" Any example which shows this would be helpful.
2
votes
1answer
37 views

Relational modeled design to store versioned files

The scenario I have images in a database table and some of these need to be altered then re-saved(not overwriting the original) whilst providing a reference back to the original. I also save the ...
0
votes
1answer
16 views

Does having many databases affect MySQL performance?

I have huge unused databases in MySQL. Do they affect my MySQL engine performance even if they are unused? Do they consume memory on their own? Thanks
2
votes
1answer
21 views

Optimizing indexes

I just began optimizing my schemas with indexes (pretty late I guess). I never dealt with huge dataset but I'll be soon coping with about 1 to 5 millions rows. Here is the schema I'm using right now ...
3
votes
1answer
253 views

Deadlock free MERGE-alike in MySQL?

I am trying to implement some parts of MERGE in the MySQL driver in Drupal. Of course, Drupal has something but in truth it only works because the most frequent MERGE issuer just eats exceptions. So, ...
1
vote
1answer
39 views

Installing MySQL in Solaris

I am trying to install MySQL 5.5 on Solaris 10. I executed the code useradd -r -g mysql mysql where a group called mysql is already created using groupadd command. I get the following error : UX: ...
1
vote
1answer
853 views

Replicating data from Oracle to MySQL

I work with a vendor that does data analytics, and they currently receive a replication stream from some of our databases using a product called Goldengate (which is very expensive). Goldengate has ...
1
vote
2answers
67 views

MYSQL joining 3 tables with left join

I have these tables: pupils: (id_pupil, name, surname, email, user, pass, level, class) incidents: (id_incidents, date, time, type_incident, comments, id_pupil, id_user, subject, id_trimester) ...
0
votes
1answer
15 views

Can I estimate the row count of a large mysql table using the disk space?

Sysadmin here (not a developer by trade), so go easy please ;) I have a mysql table, on a linux server that uses innodb_file_per_table. When I try to do select count(foo) from table; It doesn't ...
1
vote
1answer
12 views

How to store temp tables in memory during table “optimize”?

When optimizing tables, SHOW PROCESSLIST lists it as "copying to tmp table". I'm trying to ensure the copy takes place in memory to speed up the process. I've tried adjusting the tmp_table_size to ...
-1
votes
1answer
16 views

MySQL ran out of space, now won't start

So the partition MySQL was mounted on ran out of space. As a quick fix, I decided to move mysql's data directory to /home/minecraft/mysql/. I then changed the datadir in the config to the correcy ...
0
votes
2answers
111 views

How to duplicate related rows

Tables: Payments with auto-increment ID and one-to-many relationship to Projects such that one payment (say £10) has two records in Projects allocating the £10 between the projects. Now next month, ...
0
votes
1answer
85 views

MySQL Table rename and Grants

We are in the process of spring-cleaning our production MySQL 5.0 database, and this includes the renaming a number of tables. We are using the following syntax for the rename ALTER TABLE ...
0
votes
1answer
62 views

DB disk space requirements comparision between MySQL versions

I have 2 labs where MySQL is installed. DataBases are created and deleted dynamically by certain internal processes. InnoDB is the Engine used. In one lab, MySQL version 5.0.77 is installed. The DB ...
0
votes
0answers
10 views

No privileges on the admin

I am using MySQL on AWS, and I came to horrific realization that privileges were flushed shut N across the board today. I was able to view this from an other user has very few privileges, but one of ...
1
vote
2answers
55 views

Will redirecting queries to dev/null warm up indexes?

I'm trying to warm up the buffer pool. Will running a query and re-directing the output to /dev/null suffice? e.g. mysql -u username -p -e "select id from tbl where 1=1" > /tmp/results.txt Thanks
1
vote
1answer
55 views
+50

Replication from EC2 Master to RDS Slave - Connecting to master

I am migrating my company's database from an EC2 instance to an RDS instance. I have already migrated a snapshot of data and am now trying to set up replication to get the data that has been added ...
0
votes
2answers
48 views

How to autoincrement Prod_id based on Product_Name using trigger in mysql

Product_Details table : (Fields) Prod_id(Primary key), Product_Name, Prefix and Slno(Auto incremented) i need to increment Prod_Id based on the product name field and prefix field (Input will be ...
0
votes
2answers
59 views

How do I group the data in the following format?

Using the following table OrderDate OrderID [dates] [id] I want to display in the following format : quater name nooforders q1 10 q2 10 q3 20 I ...
0
votes
1answer
105 views

Restoring a single database from mysql enterprise full backup

I am having command to restore all databases from full dump taken by enterprise full backup .Can anyone help me how to restore single database from full dump .
3
votes
2answers
146 views

How to optimize a log process in MySQL?

In my project, I have about 100.000 users and can't control their behavior. Now, what I would like to do is log their activity in a certain task. Every activity, is one record which includes columns ...
1
vote
1answer
50 views

MySQL login after install

I was install mysql5.5 version.When i was install mysql at that time i was completed secure installation precedure by using "sudo mysql_secure_installation" this command. Now when i trying to login ...
3
votes
1answer
2k views

MySQL: Lock wait timeout exceeded

I have a developer that has been trying to alter a large table (~60M rows). Via LiquidBase/JDBC they're running ALTER TABLE foo DROP FOREIGN KEY fk_foo_1; Today while it was running I checked in ...
4
votes
1answer
683 views

Designing a database for a site that stores content from multiple services?

I'm building a site that implements David Allen's Getting Things Done that pulls in your email, Facebook newsfeed, tweets from those you follow on Twitter, and more services are planned. The problem ...
1
vote
3answers
41 views

How to remove master role from MySQL server

I have 2 MySQL servers, one is the master and other one is slave, that's all good. The problem I have is that the slave is also set up as a master. I would like to disable or remove the master role ...
3
votes
1answer
454 views

Select * from statement execute very slowly, innodb io read speed is low

I have a very simple query " select * from ap_statistic " running in my servers. the servers have the same hardware and software configuration (CPU 8 core, mem :32G, OS: redhat 5.5, mysql version: ...
0
votes
2answers
142 views

Why is my RDS mysql slow query log full of server restarts?

One of my RDS mysql databases is giving me warnings about the slow query logs taking up significant space. The logs are rotated once an hour, and contain a dozen repetitions of: ...
0
votes
1answer
63 views

MySQL cluster concurrency

I have a little application that executes select queries against a MySQL cluster. By using 1 thread to read a record the latency is x. By using 2 threads to read records concurrently the avg latency ...
0
votes
0answers
14 views

Issues with Inserting due to FK constraints

I have two tables: Employee, and Department. They are setup like so, : CREATE TABLE IF NOT EXISTS `mydb`.`Department` ( `Dname` VARCHAR(25) NULL, `Dno` INT NULL, `Total_sal` INT NULL, ...
0
votes
1answer
20 views

SQl comand works in Workbench but not from Java Program

I am working on a program that manages a database of client information. I am currently stuck on a problem where the SQL statement created by my java program but get the error SQLException: You ...
0
votes
0answers
8 views

“Display width out of range for column” error when importing mysql 5.1 dump into 5.5

I am migrating to a new server, and trying to move my database from a 5.1 to 5.5 server. I created a dump with mysqldump -u root -p --add-drop-database --triggers dbname > dbname.sql When I ...
0
votes
1answer
72 views

Problem with mysqldump and view

When using mysqldump to backup MySQL, I got following error. mysqldump --all-databases --routines >> all.sql mysqldump: Couldn't execute 'show table status like 'hdkien'': SELECT command denied ...
0
votes
1answer
1k views

Error “#1118 - row size too large” on the first row of the table only

I ran into a problem using a mySQL database. I have some columns as type text but when I try to enter data into the first row I get the error code "#1118 - Row size too large. The maximum row size for ...
0
votes
1answer
29 views

How to decide whether to enable MySQL query cache using InnoDB

How should the decision to enable / disable the MySQL query cache be made, on a server that uses just InnoDB tables. Say for example, if the cache is enabled, then how should the output of: SHOW ...
0
votes
1answer
70 views

How to find the same salaries and department names?

Write a query to display last name ,department number,and salary of any employee whose department number and salary both match the department number and salary of any employee using the following ...
0
votes
1answer
21 views

How to grant super privilege to the user?

I have created a user and given privileges to the user1. `grant all privileges on db1.* to user1@'%' with grant option; Am using mysql workbench to import dumps to my database. While importing ...
1
vote
2answers
21 views

Mysql Master Offline

In a unidirectional Master-Slave setup, should the slave be manually stopped in cases where the Master is offline due to Maintenance, Crash etc. ? Or should one just leave it so that it keeps trying ...
0
votes
1answer
41 views

Best Indexing Strategy for Query with Equality[A], Range[B], Group By[C], AND Order By[count(P)]

I have a poorly performing query: SELECT user_id, count(item_id) as count FROM table items WHERE category = 'magazine' AND created_at > 1384754400 GROUP BY user_id ORDER BY count(item_id) desc ...
0
votes
0answers
17 views

Many temporary tables created on disk since mysql 5.6 upgrade

I recently upgraded MySql (from 5.5 to 5.6) on a Debian Wheezy server. Drupal 7 and a PunBB board are installed on this server (Drupal 7 database size: 54 M / board database size < 800 M). These ...
0
votes
0answers
42 views

Avoid transaction rollback when Deadlock is encountered (mysql)

I'm currently trying to handle concurrent inserts correctly using MySQL, I'm basically doing the following: CREATE TABLE foo (id INT NOT NULL PRIMARY KEY) ENGINE=INNODB; CREATE TABLE bar (id INT NOT ...
0
votes
1answer
1k views

Database user specified as a definer

I have a view in my database. problem is below Error SQL query: SELECT * FROM `lumiin_crm_prod`.`v_contact` LIMIT 1 ; MySQL said: 1449 - The user specified as a definer ('lumicrm'@'%') ...
0
votes
0answers
12 views

Can't Add Databases through PHPMyAdmin

EDIT (should've mentioned this sooner): I'm using a Mac, running OS X 10.9.2 Being fairly new to PHPMyAdmin, I thought I was removing settings/privileges for one single user through PHPMyAdmin, but ...
4
votes
1answer
51 views

sql indexing and performance

I am using mysql5.1, i have table which has about 15 lakh (1.5 million) records.This table has records for different entities i.e child records for all master entities. There are 8 columns in this ...
0
votes
0answers
16 views

MySQL CPU high usage

My MySQL service started to using a lot of CPU recently. Few second after restart it takes 100% CPU's. I tried already changing my.cnf file to optimize cache/buffers. I have no idea what causing that ...
0
votes
1answer
79 views

INSERT take too much time

I have in my MySQL DB a few tables Most of the time, I'm only doing INSERT in my db. I have up to 4 connections to the DB, each one inserting into a different table. There cannot be two connections ...
0
votes
1answer
27 views

Can't force mysql to use index

There are many similar questions about force index but mine is something different because i successfully use index at local dev server but can't on production server. I have a table with more than ...
0
votes
2answers
57 views

How to determine the optimal sort_buffer_size?

I read from a sample configuration file which says the following: # Sort buffer is used to perform sorts for some ORDER BY and GROUP BY # queries. If sorted data does not fit into the sort buffer, a ...
0
votes
1answer
22 views

Check for table corruption

I monitor MySQL databases by querying various things remotely. I recently came across an issue where by a table had become corrupted but I did not know about it until the daily mysqldump failed. Is ...
1
vote
2answers
58 views

Latitude and Longtitude Distance Calculation

I'm using MySQL and I am trying to calculate the distance in meters between two different longitude, latitude coordinates. I wrote a stored function in MySQL but it calculates just 1 for each row in ...