MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
165 views

MySql - Reorder primary key with auto-increment

I have two table(s) in mysql as follows : CREATE TABLE UserMst ( UserID mediumint(8) unsigned NOT NULL AUTO_INCREMENT, UserName varchar(20) NOT NULL, CreatedOn timestamp NOT NULL DEFAULT ...
1
vote
1answer
21 views

What is InnoDB Buffer Usage and how to clear it?

In Mysql Workbench when I go in Server > Server Status there is a field called "InnoDB Buffer Usage" It is "95%" even if there is no query running. Why? It affects my performance in my new queries? ...
0
votes
1answer
91 views

A single, simple update takes 10+ seconds

We are running MySQL 5.1.71-log and have a table with 2.7 million rows. Simple updates to it are regularly taking more than 10 seconds to execute. The table structure is: CREATE TABLE `person` ( ...
0
votes
1answer
134 views

Using non-replicated read/write databases on a MySQL slave server

I have a MySQL slave server that has two databases being replicated from a master. What I'd like to do is add a third database that I will exclude from replication and use as a read/write database. ...
0
votes
0answers
11 views

Table Design for Student Attendance hourly for each day

I have created a table(attendance_details) for storing every day attendance of Students in MySQL. Need to capture attendance for hourly(1-8hrs) for every day. I have the student details in separate ...
1
vote
2answers
653 views

Mysql Deadlock on update query on same row

My server is experiencing occasional deadlocks on two update queries on same row both using index (primary key). The conflicting queries are: UPDATE auth_user SET rep_score=185 WHERE ...
3
votes
0answers
18 views

How to get the best write throughput during a MySQL import with Amazon RDS?

tl;dr - How can we get the maximum EBS throughput for a db.r3.2xlarge instance? We're only seeing about half of what it can supposedly do. Apologies for the long length of this question. We've had ...
1
vote
1answer
28 views

Add multiples of rows till they satisfy an arbitrary sum then return the rows that do

Am not sure if this is possible. Let's say I have an arbitrary figure like 200, I have a table with two columns (both ints). Lets call the columns col_a and col_b. I need to perform one query such ...
1
vote
1answer
15 views

Delete Duplicates Leaving One of Two Identical Records

Delete Duplicates Leaving One of Two Identical Records One of my MySQL database tables contains four BLObs in each row. When it was first set up the table had no keys. However when I now try to add a ...
2
votes
1answer
13 views

How should a numeric value that is an external key be stored?

This is something I was pondering recently. trying to determine how to store certain values. The best wide spread example I can think of is Federal Employee Identification Number - more commonly knows ...
1
vote
2answers
10 views

MYSQL to other DB migration using MYSQL workbench

is it possible to migrate MYSQL to other database platform like MS SQL 2012 using the MYSQL workbench migration wizard?. Everywhere the only destination DB is MYSQL, the reverse process is not ...
1
vote
1answer
201 views

Innodb buffer pool indexes versus data usage

The server has 128GB of RAM. The innodb_buffer_pool_size is set to 106GB. mysql> show global variables like 'innodb_buffer_pool_size'; +-------------------------+--------------+ | Variable_name ...
-1
votes
2answers
101 views

JOIN on three tables is not working

I have these three tables, user, categories and files In users, table I have a column user_id And I have this query that shows all Categories. SELECT `categories`.`category_id`, ...
-1
votes
0answers
21 views

I have a plan to make a database synchronizer using JOOMLA! and I don't how to start it. Can you help me with this? I am stocked in this part [on hold]

This is the interface I made for my synchronizer. It will first query source databases and their tables and the attributes of the table and then select the target database.
1
vote
1answer
11 views

pt-table-checksum says there is a difference but pt-table-sync with --print says nothing

I am using pt-table-checksum to ensure master-slave is synchronized. When I run pt-table-checksum, I see some differences on employee_profile.trigger_log but when I use pt-table-sync with --print, I ...
0
votes
1answer
344 views

Cross tab query with concat field

I want to create a cross tab query for my table.but when i am using concat i am unable to get ouput succesfully.can any one help me with my query. SET @sql = NULL; SELECT ...
0
votes
1answer
17 views

How to select only the first distinct value in a column, but have blanks in that column in the other rows otherwise

Is there any way that I can turn a table like this with a query like SELECT name,child from People |----------------------| | name | child | | John | Jack | | John | Jerry ...
0
votes
0answers
5 views

Replication broken after copying .frm .myd .myi files

I copied .frm .myd .myi files for MyISAM tables from old_prod server to new_prod. Then I did a mysqlcheck on DB and repair tables whenever necessary. All is good for MyISAM tables in the new_prod. ...
0
votes
1answer
91 views

MYSQL: How to improve performance of inserting over 1M rows to a table with over 100M indexed rows

I have this mysql table: CREATE TABLE `codes` ( `code` bigint(11) unsigned NOT NULL, `allocation` int(11) NOT NULL DEFAULT '0', `used` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`code`), ...
0
votes
1answer
596 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
3answers
202 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
1answer
105 views

List all mysql reocrds in a master table and last record in a related child table

I want to list all records in a mysql master table and last record in the child table that is related to the master. Here is my code $strsql="SELECT contact.id, contact.title, contact.init, ...
0
votes
1answer
20 views

Help converting a MySQL query to postgres

Is it possible to do this query in postgresql? SET @p:= 0; UPDATE t SET x=CEIL((@p:= @p + 1) / $n) ORDER BY id DESC; I'm struggling with the variable syntax. I tried: \set p 0 update t set ...
2
votes
0answers
27 views

Is it a waste to set innodb_buffer_pool_instances greater than the # of CPUs?

I set the innodb_buffer_pool_size to 20GB on a server with 12 CPU cores. My full database is 11gb, however most of it is archived tables that are almost never used. The total queried data is around 3 ...
0
votes
0answers
15 views

Multiple Selects Into Multiple Columns Encrypted [on hold]

I am trying to do two select statements on the same table. However I get errors. The reason for this query is one of the columns is 256 bit CRC encrypted. Here is my query: SELECT (select COLUMNA ...
4
votes
3answers
121 views

How to detect inactive tables?

I started working on a project whose former developer ragequited, and it left a complete mess both in the code and database. The server is using MySQL 5.5 There are a lot of tables created for backup ...
0
votes
1answer
122 views

MySql: Slow join and group by on indexed columns

The following query takes 15-20s to run SELECT "Trade_US"."ID" AS "Trade_US.ID", "Server_US"."Name" AS "Server_US.Name", "Trade_US"."TradeTypeID" AS "Trade_US.TradeTypeID", "Users"."ID" AS ...
1
vote
3answers
7k views

MySQL 5.6 Failure

I have the following in RPM_UPGRADE_HISTORY in /var/lib/mysql MySQL RPM upgrade to version 5.6.10-1.linux_glibc2.5 'pre' step running at Fri Mar 15 15:41:26 EDT 2013 ERR file(s): -rw-r----- 1 mysql ...
1
vote
1answer
23 views

Need help rectifying IN/NOT IN clause

Hi there everyone! I have built a link/content sharing site. The way it works is that each link can have multiple categories assigned to it. A video of a motorcycle race might have motorcycle, ...
0
votes
2answers
40 views

Recommendation for the mysqldump?

I have approx 90 GB of database, I need to schedule a mysqldump backup , thats why I Required a recommendation how much time it takes for the full hot backup? Because I didn't taken that much size of ...
1
vote
1answer
404 views

creating a report on the mysql jasperReport server

I have mysql local database, in it I have sample data mart. I have installed jasperReport server to my computer. Just using this tool, I want connect to the local mysql database and just create a pdf ...
0
votes
1answer
12 views

Filter mysql group by negative having contains

How can I get all of the values from 1 column, which do not (or do) have a specific value in one of their rows? For instance, consider example table: | ColA | ColB | --------------- | aaa1 | zzz1 | ...
0
votes
0answers
6 views

InnoDB problem when upgrading to Joomla 3.X

we have different Joomla and WordPress sites on 1 server. We want to upgrade a Joomla 2.5 site to Joomla 3.X. The upgrade only works when we remove the dbdata1 file in MySQL(it is automatically ...
1
vote
3answers
41 views

Automate CSV files to MySQL Database?

I'm a little mind struck at the second as I am looking to automate the following sequence: Extract Data from Microsoft Access Save files as CSV Connect toMySQL server Run one SQL file that exports ...
0
votes
1answer
106 views

Tuning Mysql and Monitoring

I would like to know if anybody can point me towards a tool/way for MySQL database tuning ... Actualy the tunning of my.cnf is like below My database is in MyISAM | Mysql 5.1.73 key_buffer = 500M ...
0
votes
0answers
9 views

Auto created accounts mysql

i have a small question about accounts auto-created after a fresh mysql install. there is an account with Host = localhost, user = 'No user name specified', password = 'No password specified' if i ...
0
votes
0answers
15 views

Concurrency Problem in Inserting data

In a PHP-MySQL app we have a memeber table that we use to store our members data. Before inserting a new member we check member existence based on mobile number and if there is any record we update ...
0
votes
0answers
15 views

Manage duplicate records and other entries based on duplicate records in MySQL

I have following list of tables for my PHP Application. wi_individual_g(ind_id,ind_name,ind_father_name,ind_spouse_name,is_employee,org_id,ind_dob,...) wi_individual_p(ind_id,prg_id,...) ...
0
votes
1answer
18 views

set global variable parameter?

My prod_server version is 5.1.47 and want to set the slow query log dynamically for certain period of time. I refered one site(http://toastergremlin.com/?p=276) there is mentioned below 5.1.6 we need ...
0
votes
0answers
16 views

show remaining products

I want to show remaining stock after selling. I can use view table to show the remaining stock but its not a good practice.Is there any other possible way to work on??
2
votes
1answer
291 views

Best Practices for Creating and Deleting Records in MYSQL

I'm fairly new to working with databases, but I've currently have a webCrawler that I've written in C# using MYSQL database. The crawler is frequently writing and deleting records from the database ...
0
votes
1answer
275 views

How to organize the tables of a MySQL database with several statistics data of several years?

I am developing an app that must provide statistics information about the distribution of the money that a person spends each year in taxes in the city where he lives. The user should select the ...
1
vote
1answer
410 views

Connecting to MySQL 5.1 using IBM Data Studio

I am trying to connect to MySQL database version 5.1 hosted on a web server using IBM Data Studio 4.1. I am getting an error "The JDBC driver class was not found in the specified JAR or .zip file. ...
0
votes
1answer
83 views

MySQL: summary query for date activity by last 7 days, week and all year totals

I have a customer table with records like ID Name Date Status 1 Joe 2014.02.01 New 2 Bob 2014.02.02 Requested 3 Rob 2014.02.02 New 3 Dale 2014.02.02 New 4 Jane 2014.02.03 New ... assume ...
0
votes
1answer
17 views

performance of handling many inserts into an Amazon RDS instance; are there other options? [on hold]

I am looking at using Amazon RDS for MySQL. However, the performance has not been acceptable. Using SQLite, I get about 1000 inserts per second whereas on Amazon RDS, I'm getting about 5 inserts per ...
1
vote
2answers
23 views

MySQL, RAM disk for tmpdir - how to figure out what size it should be?

We regularly run a few large SQL queries to generate reports. It seems that the bottleneck is the amount of time that is spent writing to the tmpdir, which is currently on disk. We are upgrading to a ...
9
votes
4answers
3k views

Benefits of using backtick (`) in MySQL queries?

In MySQL we can create queries with or without the backtick (`) symbol. Example: SELECT * FROM TEST; SELECT * FROM `TEST`; Both works fine in mysql-console. Is there any technical difference ...
0
votes
1answer
15 views

Is there any way to perform atomic updates of stored procedures in MySQL?

I know that MySQL does not support atomic updates of stored procedures yet and it is bug which was reported on 2005 but not yet resolved.(http://bugs.mysql.com/bug.php?id=9588). In our project ...
1
vote
1answer
438 views

How to removing or reduce the size of redo logs in MySQL cluster

Can anybody tell me that how can we reduce the redo log file size or can we remove the redo log file size in MySQL Cluster and also what will be the impact of it on performance of cluster.Also can we ...
0
votes
1answer
44 views

GROUP BY on two large joined tables

I have two tables: Person ------ PersonID (PK) GroupID DeletedDate PersonData ---------- PersonID (PK) FieldID (PK) Value The fields are custom-created; hence the schema. I am trying to run a ...