MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
57 views

SQL Error while restoring big database in Mysql

I have successfully created a MYSQL dump on RHEL 6 machine. The dump file is having 40 GB. When I try to restore it in an UBUNTU machine I got this error.
3
votes
1answer
218 views

Migrate from innodb_file_per_table to off in MySQL

We have a large number (2,500+) of databases and users in MySQL 5.6 on a single virtualized server (4 cpus and 8GB of memory). All the tables are using InnoDB. The actual data size is not very large ...
0
votes
2answers
182 views

MySQL Index for searching for NULL values in text fields

I need to do the simple query SELECT id FROM table WHERE blob_field IS NULL. blob_field is a longblob. If I create an index on blob_field with prefix length 1, will this be sufficient to use for ...
0
votes
3answers
280 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
156 views

make MariaDB Auto-Increment-Increment dynamic

In a certain replication setup I have created multi-master replication in STAR topology using MariaDB. I have an unknown number of 'client-masters', so I have set the auto_increment_increment to 30 ...
0
votes
0answers
101 views

Opimizing Mysql

Below is a Snapshot of my Server info. And the results of mysqltuner.pl Some help with commands would be appreciated. I'm new to this and have gotten pretty far, but I'm having issue with Mysql ...
0
votes
3answers
2k 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
55 views

Need to find the employees who have switched jobs atleast twice

I am trying the following query on the tables which are described below: SELECT * FROM job_history INNER JOIN employees ON job_history.employee_id = employees.employee_id WHERE employee_ID IN ...
1
vote
3answers
3k 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
97 views

How do I solve this problem of a correlated subquery

We have an employee table with the following attributes : employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id Display the manager ...
0
votes
1answer
157 views

mysqldump 5.5.25 use swap instead of memory

mysql version 5.5.25, while use mysqldump to dump big tables, it eat lots of swap while there is lots of free memory. # free -m total used free shared buffers ...
2
votes
2answers
367 views

Migrating data from a boolean column to a varchar column

My table currently has a boolean column, let's call it the "important" column, reflecting whether a record is urgent or not. I want to transition to using a varchar column that reflects a record's ...
0
votes
2answers
335 views

Inserting 60 000 rows taking too long

I'm trying to insert about 60 000 rows on my database on localhost (windows) it is now taking about an hour and still inserting. And after a while it says that maximum timeout is over. Shouldn't it be ...
1
vote
0answers
99 views

Script to query MongoDB with PHP [closed]

With Code Igniter and MySQL I do: $this->database->query('select * from table'); But I don't know how to use MongoDB. Can I still use this the same way to query MongoDB? ...
0
votes
2answers
32 views

Return row in given date

I have a MySQL table named tasks that contains four columns: id (Integer) name (Varchar) task_start (DateTime) task_finish (DateTime) How can I write an SQL statement that returns tasks that are ...
1
vote
1answer
433 views

mariadb 5.5.36 with innodb plugin

I installed mariadb 5.5.36 on a win32 system. Everything runs fine except innodb requests are 2 x slower than an identical MySQL installation. So I want to switch back to original innodb plugin to ...
-1
votes
1answer
2k views

how to autoincrement varchar datatype depending upon the product details name using trigger

I have a product details table, the fields in the table are *Prod_Id, Product_Name, Quantity, Slno*. Now i have created trigger for varchar data type field and implemented auto increment for Prod_Id ...
1
vote
1answer
105 views

Is it possible to archive table rows of an RDS read replica?

I would just like to know if I can purge 60 days old rows from a replica. Would the replica just copy the rows again from the master after deleting the rows? Because what I would really like to do is ...
0
votes
1answer
142 views

Change the row position of a record without messing anything else MySQL?

Hi I would like to ask if there is a smart way to change the record position of a table in mysql. My problem is this: I have a table with statistics data and when I recover all the details in a table ...
0
votes
0answers
136 views

Why is adding a FK constraint slow in MySQL?

I'm doing some experimentation in MySQL 5.1.53 and I've run across a case that I just don't understand. CREATE TABLE A ( `pk` INT(11) NOT NULL, `fk` INT(11) NOT NULL, PRIMARY KEY `pk_A` ...
1
vote
1answer
317 views

Native checksum table says there's a difference, pt-table-checksum does not

I was setting up a slave and doing some table checksums to check things were ok before getting them live. I did a native mysql checksum tables on the slaves after syncing slaving to a stopped point ...
1
vote
2answers
275 views

mysqldump with automatically generated export name

When I do database dumps through MySQL WorkBench it picks a filename for me which is the current date (YYYMMDD). Now, I want to do this in a batch file since I want to make weekly dumps automatically ...
0
votes
1answer
90 views

fixing mySQL replication (large DB)

I have a large mySQL database and I am replicating to two slaves. All three servers are windows (i know). One of my slaves seems to have corruption, the other is fine. This is huge DB, a few ...
0
votes
2answers
2k views

How to maintain MySQL performance at high concurrency

The following has been observed using "mysqlslap". mysqlslap -u root -p --create-schema=matrix --query=monthly_kpi.sql --iterations=1 --concurrency=1 --delimiter=";" Benchmark Average number ...
3
votes
2answers
364 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 ...
1
vote
2answers
651 views

Baffled with MySQL LEFT JOIN query that I have to speed it up

I am kind of a puzzled with this query trying to optimize it but no way I am able to (due to my limited knowledge as a DBA). So, I asked the experts. In my setup I have three (primary) tables. ...
2
votes
1answer
624 views

mysql optimize group by having count

I have 2 mysql table: table t1: CREATE TABLE IF NOT EXISTS `t1` ( `id` bigint(20) NOT NULL, `url` varchar(255) DEFAULT NULL, `title` varchar(255) DEFAULT NULL, `type` varchar(255) DEFAULT ...
0
votes
1answer
28 views

what mistake i did in mysql query

I am getting error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"insert into ...
1
vote
1answer
2k views

mysqldump: Does --lock-tables=false work with MyISAM tables?

So, I have to check and come with a backup solution for a very busy mysql server (with tables over 5 GB). Due to resources constraints, they choose to set all tables with the MyISAM engine. Currently, ...
4
votes
5answers
804 views

MySQL runs out of memory when importing innodb database

I have a small VPS with 512MB RAM and no swap (can't have any). I am importing a 1.5GB innodb database with mysql -u -p < database.sql Before the operation can finish it says : Lost connection ...
0
votes
1answer
62 views

MySQL: How to find MySQL 5.1 (with built-in InnoDB) or MySQL 5.1 (with InnoDB-plugin)?

My DB server has MySQL 5.1.58-community-log and I am planning to upgrade it to MySQL 5.6 with specific purpose of improving my system performance and removing the bottleneck issue. So, I have gone ...
0
votes
1answer
390 views

How can I Automate MongoDB schema design from existing MySQL Database and Logs?

I am working on the project which will automatically generate sample MongoDB schema analyzing existing MySQL database and logs. I am looking for help regarding the below questions. Please, share your ...
0
votes
2answers
56 views

To replicate, or not to replicate

1.Intro Hello, I am running web application on 2 separate geographical locations(e.g. Europe, Hong Kong). Let's suppose Hong Kong server is an Amazon EC2 instance(paying for disk space, IO disk ...
3
votes
1answer
175 views

Is the innodb_lazy_drop_table problem solved?

I'm researching database configuration for MySQL/MariaDB. I've been reading the configuration chapter in "High Performance MySQL". In there, it suggests that innodb_lazy_drop_table can be used to ...
1
vote
2answers
3k views

Datediff() in MySQL

While calculating difference between 2 dates as number days, by using MySQL Datediff() function, I am facing some issue, the SQL code is given below. select datediff((select curdate()), (select ...
2
votes
3answers
230 views

How to update cust_id from Customer table to order table Cust_id field while inserting customer name using trigger

I have two tables 1. Customer_details and 2. Order_details. 1.Customer_details table -->fields--> Cust_Id(Primary Key) and Customer_Name, 2.Order_details table --> fields-- Ord_Id(Primary ...
1
vote
3answers
72 views

MySQL backup breakes a table every time

I've a MySQL Server 5.1.58-1ubuntu1 installed on an Ubuntu 12.04 LTS. Every night we do a backup of all the databases with this script: rm -rf /var/backup/mysql/ * for i in $(echo 'SHOW DATABASES;' ...
0
votes
2answers
49 views

Backup and restore procedure

I am having only MYI files is it possible to restore my database with this files.? If yes means tell me the procedure please
1
vote
1answer
47 views

Is it possible to have mysql treat row based updates as “upserts”?

When processing row based replication events, mysql replication will break if it's unable to find a row matching the "before image". Is there any setting to tell mysql, if you don't find that row, ...
0
votes
1answer
162 views

CentOS and Ubuntu differences with information_schema

I have a Vagrant VM running CentOS 6.5. My localhost is Ubuntu 12.04, both running MySQL. I have the exact same database on both (structured exactly the same, different testing seed data in both). ...
0
votes
0answers
35 views

mysql id sequencial problem

I create 3 records with the id 1,2,3 I delete record 3, then in the future add another record, it then goes 1,2,4. how to id get sequential order when it is deleted the record like 1,2,3
0
votes
0answers
45 views

Multi language where this feature never existed

I need to implement multi language system in an existing system that was not developed for this. The number of entries in the tables that will hold data that has to be translated will not exceed 5 ...
2
votes
1answer
405 views

Is there any way MySQL can list relationship cardinality between two entities?

I am working on the project where I have to analyse the MySQL schema. As a part of schema information, I want to list down relationship cardinality between different relations. Does MySQL stores ...
1
vote
0answers
968 views

MySQL: Upgraded from 5.1 to 5.6 - no problem?

We're using Yum to maintain MySQL and our MySQL server only hosts a largish Drupal database. We recently upgraded via Yum from 5.1 to 5.6. The process went like so: Ran a mysqldump --all-databases ...
1
vote
1answer
3k views

How do I tune MySQL with results from mysqltuner?

I ran mysqltuner and got this: -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL ...
2
votes
2answers
2k views

How to Recover truncate table in MySQL?

I am getting the "wrong input table name" when use TRUNCATE as following: TRUNCATE `stats` The table should bans. How to recover stats table? I am using CentOS 6, and MySQL-Server.
1
vote
2answers
39 views

SQL Booleans vs ManyToMany

As I understand it, one way to 'abstract' a parent/child relationship in SQL would be to have a table set up with a joining foreign key table. Ie in this case, the swimsets are children of different ...
0
votes
2answers
417 views

Recovering mysql innodb table from table.ibd backup file

Just practising DB disaster recovery. As per our back up policy, we do complete disk image back ups every half an hour (on our slave server). Now for a scenario, where some how a table has been ...
3
votes
3answers
2k views

Verify mysqldump backup is corruption free

We've got a cron task backing up our Database using mysqldump - my main concern is corruption, short of manually importing and checking the backups each time whats the best way to check / verify a ...
2
votes
2answers
1k views

Dynamically change schema to accommodate custom fields!

I am working on building a sort of a cms for a particular project. Nothing that will have a large volume of records in any table. Within 6 digits max. I was thinking to create the default schema for ...