MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
0answers
23 views

Randomizing the Auto Increment in Mysql

We have a database, where we insert data into table whenever we receive an order from our services. We use auto_increment to generate the ID and assign the order with the same. This is usually same ...
0
votes
2answers
49 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`, ...
0
votes
1answer
30 views

Indexes involved in intervals collision detection

Let's assume an hotel booking application wants to display all customers being present between two dates. Starting with a venue table : CREATE TABLE `venue` ( `id` INT NOT NULL AUTO_INCREMENT ...
1
vote
2answers
87 views

Altering table Foreign Keys produces an error (errno:150) (MySQL)

This is the first database I set up so I apologize if my problem is something obvious. I'm playing around with my table design and tried to create some link tables with FKs to avoid redundant data. ...
0
votes
0answers
29 views

DBD::mysql complains “Character set 'latin1' is not a compiled character set”

I think this boils down to "Am I doomed to upgrading mysql hoping the problem goes away?". I've seen a few of the same issues around the internet but with no guidance on a fix other than "Did you turn ...
0
votes
1answer
28 views

MySQL: update field with “x hours xx minutes”using int from another field

We have two fields that hold time data totalTime (int): number of minutes (eg 243) totalTimeDesc (string): normal english representation (eg '4 Hours 3 Minutes') We have a lot of different formats ...
3
votes
2answers
73 views

Find problematic MySQL users and databases

We have a MySQL 5.1 server that is an "open" service: anyone on campus can request a database. As such, we have several thousand databases on this one server. As you might imagine, sometimes one ...
0
votes
1answer
39 views

Last row from a query without Order by

I have something like SELECT var1,var2 FROM table where var3=X AND var4=Y order by var5 desc limit 1; Running this query takes roughly 5 seconds on my database which is far to slow. I was ...
1
vote
1answer
19 views

Can triggers be used to implement automated polymorphic association?

(Apologies for any bad formatting or ignorance of common practices within this community; this is my first question here.) I'm attempting to (re)design a MySQL database for a personal social ...
0
votes
1answer
109 views

slow writes on percona / mariadb server only when replication is running

Hi I am trying to migrate to percona server from mysql 5.6 on windows. In my development machine for percona is running of centos 6.5 which is installed on vmware 5.5 sphere. Our business ...
0
votes
1answer
19 views

MySQL/PHP Returning Blank Error Message [closed]

I am using the following code to delete a record. It works fine, but MySQL always returns a blank error message. PHP returns "Error: " instead of "1 Record Dropped". $eventid = ...
0
votes
1answer
17 views

Where mysql writes its log seqeunce numbers and last checkpoint at?

I'm working on developing mysql log collecting system. To do that, I have to collect log sequence numbers from mysql LSN and last checkpoint location. But I have no idea where should Which log file ...
0
votes
2answers
36 views

How do I allow remote mysql access to all users?

I have setup a mysql server on unbuntu. The server hosts websites for my various clients. Hence, I want all of them(and any new clients) to have remote access to the mysql server. How do I configure ...
0
votes
1answer
260 views

Table is marked as crashed and should be repaired

I have MySQL and Nagios installed Linux PC (Centos5.5) I have wrote some script to fetch the data from Nagios to Mysql DB (Exactly fetch data in Table) The data fetching working properly (confirmed ...
1
vote
1answer
64 views

MHA configuration problem with MariaDB10

I am using MHA (MySQL Master High Availability) over a MySQL/MariaDB master-slaves pool. Here is my setup : master1(10.0.0.1:3306) (current master) MySQL 5.1 (log-bin:enabled) ...
2
votes
1answer
2k views

MariaDB and XtraDB/InnoDB plugins

In my vps, I thought of switching from MySQL to MariaDB (using Monty's repository for Debian Squeeze). Copying over my.cnf from MySQL to MariaDB, I saw that the new config file (the one from the ...
0
votes
0answers
18 views

Why does Amazon RDS memory utilization drop and disk space utilization rises?

I encountered a problem with my Amazon RDS(db.m2.2xlarge) where DB connections spiked up and my website started responding slowly. On looking up the metrics I was not sure why my freeable memory shot ...
-1
votes
0answers
35 views

“Bad SubDomain SQL query” [closed]

My website shows "Bad Sub Domain SOL query" when i open the site.It's an E-commerce site, platform is PrestaShop(Version: 1.5.5.0) and it worked well in the last 2 years. Now it shows "Bad Sub Domain ...
1
vote
1answer
26 views

mysqldump drop create use case sensitivity

Dumped sql from MySQL is showing inconsistencies in the name of the database. Using the following bog standard method to dump a database: mysqldump --add-drop-database -u someuser --databases ...
1
vote
2answers
260 views

Import CSV file into Mysql with Multiple Delimiters/Field Separators

I'm trying to import a large csv file into Mysql. Unfortunately, the data within the file is separated both by spaces and tabs. As a result, whenever I load the data into my table, I end up with ...
0
votes
2answers
35 views

query execution time in mysql

I have run the query in mysql which consists of approximately 33million records when i have executed the query select population1 < 200000 from city; it shows like this after execution ...
0
votes
1answer
264 views

Mysql compatibility between client and server of different versions

How to know what version of mysql client is compatible with what version of the server? Is there backward or forward compatibility tables from the client and/or server point of view? For a given ...
0
votes
2answers
81 views

why does mysql query running slower on server?

select glpi_tracking.id AS gi, glpi_tracking.date AS gdate, glpi_tracking.closedate AS cdate, glpi_tracking.contents AS gc, glpi_followups.tracking AS gft, glpi_followups.date ...
0
votes
2answers
46 views

MySQL Code Works in PHPMyAdmin but not in PHP Page

When I run this query in PHPMyAdmin it works fine, but when I run it from the PHP page using the $sql command, I get the error: "You have an error in your SQL syntax.....near ')' at line 2" $sql = ...
0
votes
0answers
14 views

What are the advantages and disadvantages of Sequel Pro over writing on my terminal? [closed]

I am starting to use MySQL (I am actually learning). What are the advantages and disadvantages of Sequel Pro over writing on my terminal? Where can I find a good quick tutorial to use those advantages ...
0
votes
2answers
1k views

Replication on MySQL server

I had to stop the slave server to test something. After I started the server again there is a problem with replication on MySQL server On the problematic server mysql> SHOW SLAVE STATUS\G ...
2
votes
1answer
39 views

Auto-increment ids with multiple inserts in mysql/mariadb

Suppose I have a table with an auto_increment column, and then insert multiple values in a single statement: insert into foo (bar) values ('a'), ('b'), ('c'); From the documentation, ...
3
votes
2answers
5k views

How do I fix the definer problem The user specified as a definer ('fred'@'192.168.0.%') does not exist

I dumped an existing database from my web site into a new MySQL setup on my laptop. Everything works except pages that use a view, when the error message above appears. There is only one view on the ...
0
votes
1answer
33 views

Gets records by max date and hour

I have this query: "SELECT * FROM `" . $table . "` WHERE STR_TO_DATE(data, '%d/%m/%Y')=(SELECT max(STR_TO_DATE(data, '%d/%m/%Y')) FROM " . $table . ") AND hour=(SELECT MAX(hour)-".$delta_hour." ...
0
votes
0answers
17 views

Grouping together values in different columns in MySQL

I have hierarchical data and I'm using the adjacency model. For example my table looks like CREATE TABLE books( book_id INT AUTO_INCREMENT PRIMARY KEY, book_category VARCHAR(100), parent INT ); ...
5
votes
1answer
997 views

How can I get status messages during a large re-indexing operation with MySQL 5.5?

I get large data transfers periodically that I used to completely replace the contents of a MySQL table. Each time I do this, I truncate the table, disable keys, add several million rows of data, and ...
1
vote
1answer
190 views

Syncronize mysql databases between local and hosted servers automatically

We have many website with Development , Staging and Production Server. we have many developers for many projects, we need a solution to synchronize the database with developer database with staging ...
1
vote
0answers
73 views

Issue with MySql upgrade from 5.1 to 5.6 for Jboss SOA - p v 4.3

We are using JBoss SOA P v- 4.3, we have recently upgraded Mysql version to 5.6 from 5.1 and are facing some issue with the jboss start up. We are using MySQL instead of Hypersonic database for Jboss ...
4
votes
1answer
731 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 ...
0
votes
3answers
53 views

Master-master Replication with 3 Nodes

I need to configure mysql master/master replication with 3 nodes. Currently I have master/slave setup and I need to move it to master/master with 3 nodes. I have gone through some posts about ...
2
votes
2answers
47 views

Copy from one MySQL table to another MySQL table of same database

I have around 40 million rows in a MySQL table and I want to copy this table to another table in same database. What is the most efficient way of doing this? How much time will it take (approx.)?
1
vote
1answer
17 views

add column without metadata lock in mysql 5.6

I am using MySQL Server version: 5.6.15 Homebrew. I need to add new null column. alter table requires no metadata lock. In order to avoid this MySQL 5.6 introduced algorithm=inplace. Here is the ...
3
votes
2answers
219 views

mysql second slave not syncing while first slave works fine

I have a master (m) - slave (s1) setup using mysql 5.1.45 When I try to add a second slave (s2) the slave lags behind and never catches up on the sync. Even after having synced the s2 with the whole ...
1
vote
1answer
78 views

MySQL hangs in killed state

I have a strange situation where a mysql OPTIMIZE query will never end. After killinh it, it remains in the "killed" state. Scenario to reproduce: OS: CentOS 6.5 MySQL: 5.6 Engine: MyISAM (with at ...
0
votes
1answer
26 views

Is it possible to uninsall MySQL-5.6 and install MySQL-5.1 on RHEL 5

I have installed MySQL-5.6.x community edition on RHEL5. It was running fine. Since, it is my testing server. To find the differences in execution timing, I have to install the MySQL-5.1 community ...
0
votes
1answer
34 views

MySQL crashing randomly

We have a sever running a website Joomla 2.5. We are facing frequent mysql crashes that occur when website has medium visitor load like a few thousands. It appears that mysql consumes more memory and ...
0
votes
1answer
233 views

Getting the top count of records from a table for each month and user

I have a table where translations are stored along with the user_id and when the translation was added. I want to create a query that shows the 'Translator of the month' (the person who did the most ...
0
votes
1answer
37 views

Pulling from Two Tables with Different Number of Records

I have three tables, one for members with 8 records, one for tasks with 1 record, and one for events with 3 records. The tasks table associates the events with members using one-to-many primary to ...
1
vote
1answer
239 views

How to rebuild entire MySQL data after server re install?

I have a MySQL server running with multiple databases in a 32 bit environment. I need to rebuild my system to a 64 bit environment. Is it possible to replicate the entire MySQL databases, users, ...
0
votes
1answer
42 views

How to prevent crashes connected with dropped tables during mysql import?

I have made a mysql dump with mysqldump, tables are dropped and re-created with DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( However for a read-intensive database I sometimes get crashes ...
0
votes
1answer
20 views

Adding a constant IF not in an other table

So I have this query. SELECT * FROM `Comment` WHERE `Target` = '".$_GET['pp']."' AND `Id` NOT IN (SELECT `Target` FROM `Delete` WHERE `Type` = 'comment') ORDER ...
1
vote
2answers
289 views

Mysql cluster fast inserts but slow selects

I have a mysql cluster with 3 Data Nodes and one SQL node. Curently I have 1million 1kb records. The inserts are very fast, takes only a few ms. But the selects are very slow >20s. All machines are ...
0
votes
3answers
97 views

How to solve multidimensional data relationship

I'm having a problem, that I could solve programmatically, but for my personal (test) project I'm trying to see if I can fix it in the relational model. Imagine a complex fashion product Pants with ...
0
votes
1answer
16 views

Mysql Select using Alias

I Need to filter the query that have some conditional column as below: SELECT `c`.`fullname` AS `fullname`, IFNULL(`c`.`new_ic`,`c`.`old_ic`) AS `Ic`, `bla`.`loan_no` AS `Loan_No`, ...
0
votes
2answers
68 views

How to select the latest record of each hour in a day

I want to select the latest record of each hour in a given date, based on the datetime column 'reading_on' in my table , what is the best way to do it. I already tried group by HOUR(reading_on) with ...