MySQL 5.5 brought many performance enhancements and expanded the feature set of the popular open-source RDBMS.

learn more… | top users | synonyms

0
votes
0answers
37 views

Use join to aggregate data from various normalized tables

I have a database as follows: tbl_clients (Primary Key: client_id, Foreign Keys:place_id,district_id,zone_id,country_id) tbl_places (Primary Key: place_id) tbl_districts (Primary key: district_id) ...
1
vote
1answer
43 views

Purge mysql log bins older than two weeks automatically

I have to manually write purge binary logs bin to "mysql-bin.somenumbers" ; Once in a while, I need to reduce the spaces that binlogs are taking on my web server. Is it possible to purge binary ...
0
votes
1answer
108 views

Add linked server from SQL-Server to MySQL: “Data source name not found” (Error:7303)

I'm trying to link my SQL-Server 2005 on Windows Server 2008 R2 with MySQL 5.5.40 on a different computer. I have followed this guide: ...
2
votes
2answers
371 views

MySQL user without SUPER privilege

This may be a simple question and may not be the right place to ask but I couldn't find any useful information from Google. Here is my question. When creating users in mysql using grant all ...
1
vote
1answer
52 views

MySQL Data Files Missing?

We are upgrading our website and I was checking on the size of the new database compared to the old one and was really surprised to find that in the /var/lib/mysql/ directory, the only files there ...
1
vote
1answer
79 views

Mysql Master-Slave replication catchup and bin_logs

I have set up MYSql Replication using 2x 128gig ram 24 core cloud servers. we have a massive live database that cannot experience down time due to the high volume of data input. I have found while ...
3
votes
0answers
4k views

Why does CAST(1 AS SIGNED INTEGER) return a BIGINT on MySQL?

If I do CAST(1 AS SIGNED INTEGER) I always end up getting a BIGINT returned, for example: $ mysql -u root -p --column-type-info Enter password: --- Copyright and help message snipped for brevity --- ...
0
votes
1answer
42 views

how to overcome these type of issues where null value inserted into primary key not null column in table

how to overcome these type of issues where null value inserted into primary key not null column in table
2
votes
2answers
206 views

How to batch multiple insert statements for database dump with MySQL?

I have a MySQL database with some large tables. In particular, the largest table (by far) currently has ~11,000,000 rows. According to the a query I ran, the table clocks in at around 1.7GB, but it's ...
1
vote
1answer
130 views

Why does this ALTER TABLE ADD FOREIGN KEY query create two foreign key constraints

I am attempting to add a foreign key constraint to existing tables via an ALTER TABLE statement: CREATE TABLE `blog` ( `blogid` int(11) NOT NULL AUTO_INCREMENT, `deptid` int(11) NOT NULL DEFAULT ...
0
votes
0answers
90 views

MySQL Dynamic columns duplicate value workaround

As the title suggests i have the following code which transposes certain row values into field names, however the problem im having is that the values "closed" & "reopened" may appear in several ...
2
votes
4answers
976 views

Getting 0KB of size file while backup mysql database using mysql dump [closed]

I am using below code to backup my database using php script but I am getting 0KB size file. How can I backup my full database with all routines and functions? $dbhost = 'localhost'; $dbuser = ...
10
votes
2answers
165 views

Find total duration of each consecutive series of rows

MySQL Version The code will run in MySQL 5.5 Background I have a table like the following one CREATE TABLE t ( id INT NOT NULL AUTO_INCREMENT , patient_id INT NOT NULL , bed_id INT NOT NULL , ...
0
votes
0answers
184 views

Are there any caveats to installing MySQL 5.5 on CentOS 5?

I need to install MySQL 5.5 in CentOS 5. As CentOS 5 is a little bit old is there any odd behavior when you want to install MySQL 5.5 on CentOS 5?
2
votes
1answer
77 views

How can I determine which Innodb table is being written?

I am running Mysql 5.5. I have about 100 Innodb tables spread over 10 DB's (mostly wordpress sites) and I am recently seeing a lot more disk IO than I have been, without any increase in traffic. The ...
-1
votes
2answers
102 views

MYSQL: Join with many to many

I'm trying to create a view and trying to see what can be done, I have a main table a table I want to join to and also a junction table. Event table (it's very big so it's just dummy data): ...
5
votes
2answers
149 views

Is there a (non-binary) MySQL collation that doesn't treat different mathematical symbols as the same character?

I've run into a real headache with MySQL's collations and non-BMP characters (ones with Unicode codepoints above U+FFFF). Basically, given a table and data like: CREATE TABLE `math` ( `id` int(11) ...
0
votes
0answers
77 views

How to use a subquery that returns multiple rows?

I am getting Error 1242 : Subquery returns more than 1 row. Basically, I have to do this : select data from my database based on a time criterion (between 2 dates) and then apply a filter on the ...
0
votes
1answer
47 views

Unique constraint for new values only

I have a large table with a column that contains duplicates. I want to add a unique constraint on this column for future entries only (not the old data). Is this possible?
2
votes
1answer
187 views

MySQL - Get Minimum Value, Data From Multiple Tables

Good evening all, first post on the DBA forums. I am having real issues working out how to accomplish a seemingly simple task within MySQL as a model for PHP code later on. I am not exactly a MySQL ...
0
votes
0answers
674 views

Combine and display multiple row data with same column data in MySQL

I have a menu, from where every item ordered is stored in a row. For a single order, there can be multiple rows.What I want is that when I display it, for same order_number ( number) all rows data is ...
0
votes
1answer
139 views

MySQL Workbench - “deploying” schema into a real database

a noob here, finding my way around. So, how can I "transfer, deploy or whatever the right term is" the database tables, fields, etc. that I have created with Workbench into a real database? The dB I ...
0
votes
1answer
79 views

import text file getting command is not allowed with this MySQL version

here is my table CREATE TABLE cities ( country CHAR(2), city_ascii VARCHAR(100), city VARCHAR(255), region CHAR(2), population INT UNSIGNED, ...
0
votes
2answers
258 views

Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases

We are planning to build a web based custom business analytics tool using PHP and MySQL/PostgreSQL. In our application, we are planning to upload customer’s data using a Excel sheet and these data ...
0
votes
1answer
117 views

Is it possble to replicate MySQL server on Azure virtual machines

I have two Windows Server 2012 R2 virtual machines on Azure. I need to replicate the database on each of the windows servers. I have followed this link but I am not getting the connection between the ...
1
vote
0answers
395 views

Server version (5.5.40-MariaDB) does not match with the version of the server (10.0.15-MariaDB) with which this program was built/distributed

I just upgrade MariaDB (Mysql) 5.5.40 to MariaDB 10.0.15 by following this step by step. I have failed to enable the mysql and also mysql_upgrade with the following errors mysql_upgrade -u root -p ...
0
votes
0answers
49 views

Access Denied to users after upgrading mysql to 5.5.40

I recently upgraded mysql from 5.1.0 to 5.5.40 but suddenly some users seem to have lost the previlages. All the projects are live and those with issues are showing database connection error. How can ...
0
votes
1answer
54 views

Hierarcy Query Mysql

I have a table like this... ========================================================================== UID || PARENT ID || SPONSOR ID ...
1
vote
1answer
177 views

When try to do Mysqldump Backup from console then getting such as error [closed]

mysqldump: Got error: 2003: Can't connect to MySQL server on 'localhost' (10061) when trying to connect when i am tring to do MySQLDump backup from console then getting such as above error. I am ...
2
votes
1answer
146 views

View last several innodb deadlocks

I see that I can view the latest deadlock in mysql/innodb, but is there a way to view past deadlocks? We have two deadlock issues, one being important and the other not. The less important deadlock ...
0
votes
0answers
46 views

MySQL JDBC Driver puts transaction in read only. Why?

Using the MySQL JDBC connector with a MySQL 5.6.22 data source defaults the transaction mode to read only. Anyone know why?
1
vote
1answer
58 views

Finding out the exact size of the database after inserting records?

I have this query: SELECT table_schema "db.name" , Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema; Does this ...
1
vote
0answers
33 views

Can MySQL Index Merge an Composite index with a single column index?

I am tuning a query and currently have an index as following : Index 1 : (col1, col2) An example query would be : SELECT * FROM t1 WHERE col1 = '1' AND col3 = '2'; I have created an index on ...
0
votes
0answers
70 views

Slow Query Performance - Can't Debug

I have a MySQL query that I am trying to debug the performance issue but cannot. I know that the majority of the query is reading/processing rows (Sending Data state). However, in the explain output, ...
0
votes
0answers
29 views

Oddness with MySQL with InnoDB - CPU capped at 50%

I have MySQL 5.5.22 with InnoDB, running on multiple c3.8xlarge EC2 instances. Which is to say, the VMs are set up with 32 CPUs & 59 GB RAM There are no CPU controls in MySQL, right? Then why ...
1
vote
1answer
35 views

Queries that ran, and the number of times they ran

I want to profile a running web application in dev on a mysql 5.5 database. The web application uses hibernate and I am able to see that a rather large number of queries are run against the database. ...
0
votes
0answers
33 views

unable to add data source on idashboard admin

I am in the process of adding data source on iDashboard , Data Source section. So, for that I downloaded the driver from here which is mysql-connector-java-5.0.8-bin-jar and currently trying to enter ...
1
vote
2answers
165 views

One to many join, Sum from both table

I have two tables: orders and ordersdetail...(partial table) orders- id(primary key), amount, date( index) orderdeails- id(primary key), skunitid, quantity, order_id Each order has multiple order ...
1
vote
1answer
106 views

MySQL Latest Deadlock Explanation

Can someone explain this deadlock to me? It seems as if there are 2 inserts happening within milliseconds of each other with the same Primary Key. I'm not sure how this can cause a deadlock. At most a ...
0
votes
1answer
71 views

SQL INSERT and UPDATE queries run far to slow

I have two queries that run far too slow and I have no idea why. Both tables contain about 500000 rows. Each call needs ~20 minutes and the they have a really high CPU usage (~90% on Mac). I have ...
1
vote
1answer
1k views

Why mysqldump is returning with error 1143: Couldn't execute 'show table status like

I ran the following command to create a mysqldump mysqldump -ubackup -psomething --single-transaction --quick somedatabase | gzip > 4_19.gz I got following error after 60% (estimating checking ...
1
vote
4answers
133 views

How to backup live mysql database?

I have 12 GB MySQL InnoDB Database with around 800 queries per second. No sooner I start 'mysqldump' over it to make backup I start getting '503' on my website. How do I make backup of my live ...
-2
votes
1answer
15k views

How to get MAX and MIN in GROUP BY query?

In a simple query SELECT col1, col2, col3 FROM table1 GROUP BY col1 each row includes the first value of col2 and col3 for each unique value of col. How to select MAX and MIN for each value of ...
0
votes
2answers
72 views

Mysql replication from existing slave to new slave

I'm hoping you experts can help advise me, I've got this topology: Live DB(Master) ----> Backup Server (Master and slave) ----> Test DB Backup server is a slave of Live DB, and Test DB is a slave of ...
0
votes
0answers
435 views

How to solve MySql Slave Error 1452 Foreign key constraint fails?

I am site administrator. We use to have a mysql 5.5.40 master(db01) - slave(db02) setup. One day our master server (db01) got down due to hardware failure. So we switched our site to slave (db02) ...
3
votes
2answers
139 views

Why wouldnt Mysql use index on the Primary query in a nested query

I have two tables: search_criteria and pricing. There is an index on search_id column in search_criteria table and on pricing_id column in pricing table. But running this nested query does not use ...
0
votes
0answers
22 views

Trying to create a trigger to fill two columns (MySQL)

I am trying to create a trigger to update columns on a table when a new record is added to another table. This is the code I am using CREATE TRIGGER `hire` AFTER INSERT ON `hire` FOR EACH ROW BEGIN ...
0
votes
1answer
135 views

Adding a primary key to an existing table in mysql is taking time

22 hours ago I started a script that executes an alter table to add a primary key to a table which has 225 millions of records. MSISDN is the only column. In showproceslist its process status is ...
1
vote
2answers
307 views

avoiding unique key collisions across tables

This might be related to the idea of data warehousing, but i'm not sure, though it is extreme theory. We have a table where records get written to first, let's call it tbl_recording which looks like: ...
2
votes
3answers
1k views

how to install mysql on mac?

My Mac runs Mac OS X, version 10.7.5., so it's a 64 bit system. First, I downloaded mysql-5.5.28-osx10.6-x86_64.dmg. Then I double clicked mysql-5.5.28-osx10.6-x86_64.dmg, it's mounted, and shows ...