MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

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
106 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
148 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
456 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
9 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
75 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
30 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
23 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
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'@'%') ...
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
18 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
80 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
59 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 ...
1
vote
0answers
17 views

MySQL database stops working after log enabled

I have this strange issue. I have MySql workbench, which I use to make querries to mysql database (xampp 3.2.1). Everything works fine, but recently I found myself having to profile the querries my ...
1
vote
1answer
22 views

How to make MYSQL smart enough to understand when it should take into account stop word and when it shouldn't?

Ok, here is the problem. I have a table Text text nature .... text2 nature .... a lot of nature ... rows here.. .... go with nature with nature her go it Now, i select: select * From MyFullText ...
0
votes
0answers
12 views

Amazon rds MySql -> concurrent connections deadlocks

I normally hang around in StackOverflow but I think this issue is better suited here. I've built an application in C#.NET that uses MySql as a database. My application inserts and updates data in the ...
2
votes
1answer
1k 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. ...
1
vote
1answer
190 views

Subselect dependent on first select value

I wish to do a select of all data matching a particular id. I would also like to return a selection of all data matching the alt_id field in the first select. This example works fine for the main ...
0
votes
0answers
12 views

MySQL - How to grant read access to all tables, write access to a few, to one user for multiple databases?

Ubuntu Server 11.04 - a testing server with at least 10 copies of the production code. Each copy has its own database. And there are two sets of database prefixes in operation. There is an add-on ...
0
votes
2answers
2k views

Xampp 3.2.1 error: MySQL shut down unexpectedly

I logged into Xampp today and it wouldn't launch MySQL. It said there was an error and it shut down unexpectedly. Here are the errors from the error log. Hoping someone has seen this before, ...
0
votes
2answers
33 views

Mysql 5.5.25 must be restarted to connect

We have MySQL v5.5.25 running on Windows Server 2008 R2. Occasionally people are unable to connect, and the only way we have found to fix this is to restart the mysql service. This happens every 2-3 ...
0
votes
1answer
226 views

Why does max_connections in my.ini not match concurrent connections in Mysql instance config wizard?

MySQL 5.1.x | Windows Server 2003 Can someone please clarify why max_connections in my.ini is much larger than the manual value I specify in the Concurrent Connections Dialogue? For example, if I ...
0
votes
1answer
74 views

Please suggest my.cnf configuration based upon mysqltuner findings

I have been facing a lot of slow browsing on my site nerdeky.com. My server administrator suggested me to use mysqltuner.pl to find and tune my databases. Can anyone select me what should be the good ...
1
vote
1answer
48 views

Optimizing a slow MySQL query

I'm trying to optimize a ~100GB database. All data values are in one single innodb table, is this bad? Here's what a common query looks like: SELECT `CensorCode`, `LocalDateTime`, `UTCOffset`, ...
0
votes
1answer
13 views

How to link a MySQL Table with a binary key into Access?

In MS Access I've created a linked table via ODBC to a MySQL table with a binary(16) primary key column (UUID). MS Access only shows the first row, all other rows have #deleted entries. I guess the ...
0
votes
2answers
140 views

Hierarchical Structure

Every single user has say, 3 of GROUP_A, 10 GROUP_B's per GROUP_A, and 20 GROUP_C's per GROUP_B. And each of the 20 GROUP_C's involve lots of inserts/deletes... I'm not an expert, but I've done ...
0
votes
1answer
1k views

The InnoDB log sequence number is in the future

Our site was getting intermittent database errors and I asked my webhost to check what's happening. After some inspection they found that the database has few issues and tried repairing it. Finally I ...
0
votes
2answers
68 views

Why is MySQL not using the index with the higher cardinality?

I have an index called source with cardinality of 1122 and a second index called state with cardinality of 22. MySQL keeps using the state index when when query SELECT C1 FROM tbl WHERE source = 'x' ...
0
votes
2answers
32 views

How to join two result sets from same table with different conditions on same column?

select * from unit_of_measure; +--------+-----------+--------+------------+--------------+-----------------+-------------+---------------+------------------+ | uom_id | uom_name | symbol | created_by ...
0
votes
1answer
276 views

How do I restore a SQL backup, while excluding a specific table?

How do I exclude a specific table during a SQL backup RESTORE operation? My question is specific to restoring a backup sql file. I am using Putty and bash server.
-1
votes
0answers
18 views

?deactivate facebook for multi-site forcing reactivation site-wide? [on hold]

I am connecting to a remote mysql database of my wordpress-website using SSH and mysql-workbench, and have found "key 32228 - facebook activated - wp_databasetab3_changes_tracker". Can I deactivate ...
2
votes
1answer
74 views

Select different time

I looked on the internet and I found your post on the stackexchange. I will try to explain in more detail what we really need I do not know well to write English It is a system for airporttaxi id ...
2
votes
1answer
51 views

How to stop the execution of a long running insert query?

I've executed an INSERT INTO ... SELECT as a job on my MySQL instance and then I realized that number of records that is selected by the WHERE condition is more than my patience. It's already taken 12 ...