MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
11 views

MySQL insert position value

I have trouble while inserting the values corresponding to a column: position. I have multiple inserts like this: INSERT INTO category_product(id_category,id_product,position) VALUES(2,1,1); INSERT ...
0
votes
0answers
13 views

Can I use xtrabackup to import a database without altering others?

Context: Each time a demo branch is built, I want to download a copy of the prod database and setup it onto the demo server. This way under the MySQL I have X database corresponding to X demos. I ...
1
vote
0answers
11 views

Multilanguage category structure - standard vs. normalization?

I'm building a multilingual application and I need some advice on how to structure the table. Basically I have a category table based on nested set. There are 4 languages and I plan to enlarge this ...
0
votes
0answers
9 views

MySQL Query lock acquisition order

I have a query that joins across many tables. In one of our workflows another query deadlocks with that query. Iv'e read that one step towards prevention of deadlock is to acquire locks in the same ...
0
votes
3answers
42 views

Duplicate values

Here is my table : PlayerId NoOfTeams 1 3 2 2 3 2 4 NULL 5 NULL I want to display PlayerID having same No.OfTeams including NULL values. Could any one please ...
0
votes
0answers
18 views

Signal in MYSQL procedure with parameters?

Following is from my MySQL dump DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `test`() SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Mobile Number Exists'$$ CREATE ...
1
vote
0answers
17 views

Strange MySQL replication error 1146 (Table doesn't exist)

I'm getting a very weird MySQL replication error No. 1146 for a REPLACE INTO query on a slave host replicating all tables in all databases from a master, and I'm having a bit of a hard time ...
0
votes
0answers
12 views

New To SQL and looking for some advice [on hold]

I have just finished my first SQL server class and would like to continue learning how to develop databases. The problem that I am having is that I am a noob at this, and I cannot think of a simple ...
0
votes
0answers
7 views

In MySQL 5.6, schema changes in master blocks the slave?

I have a question with a MySQL 5.6 master-slave setup. If you were to modify the master's schema with an ALTER TABLE SQL query, when the slave tries to execute that ALTER TABLE statement too, would it ...
0
votes
2answers
38 views

Is it ok to have 40+ columns in a table

I am currently re-designing some of DB's for a project. There is one DB in particular that is giving me a little of a headache. The idea is that for every client_id and date (client_id + date would be ...
0
votes
1answer
70 views

Increase performance WITHOUT adding indexes

I'm having a rather large table (350MB, 1.3M rows) and there are dozens of queries arriving at this table every second. Most queries are super-fast thanks to indexes - but some others aren't. ...
1
vote
0answers
11 views

Sysbench insert data between a given range

I was trying to insert 1000000 records of data into 1000 tables in sysbench. But after installing about 278, sybench just crashed with the following error: FATAL: failed to execute function ...
0
votes
0answers
9 views

Are ZRM (Zmanda Recovery Manager) incremental backups reliable?

I had an issue while restoring incremental backups and I'm wondering if they are reliable. I'm making weekly full backups and daily incremental backups and I needed to restore a single database after ...
2
votes
1answer
20 views

What is the Best way to set Disaster recovery setup for percona Xtra DB Cluster?

We do have a Percona XtraDB Cluster(Master-Master-Master) setup in one country. Now we want to setup a disaster recovery setup in some other country. Please provide us the best possible ways to do ...
0
votes
0answers
34 views

Select these if this, or select these…

Here is my problem: organization_category.id can be either 1,2. With organization_category.id = 1, then the organization_type.id can be 0,1,2, or 3. With organization_category.id = 2, then the ...
0
votes
1answer
14 views

Select all results from two independent tables

I have two tables: Comments -------- id comment_content time_stamp Activities ----------- id activity_content time_stamp And I would like to combine the results from both, ordered by their ...
1
vote
1answer
16 views

How to Paginate a MySql Ranking Query

I have the following ranking query working fine with a limit of 25 records per page, it shows 1st - 25th for the first 25 records, but when i go to the next page, the rank stays the same 1st - 25th ...
1
vote
3answers
33 views

MySQL select with between optimization

I got issues with some geo IP query. So basically here is create code of table: CREATE TABLE `geo_ip_city` ( `id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT, `begin` BIGINT(20) NOT NULL DEFAULT ...
0
votes
0answers
12 views

upload updated details in excel to mysql database

I have a batch file which updates the excel file by adding the latest values to it. The details are already there in the MySQL as a table. Once the details are updated. For eg after the end of day ...
0
votes
1answer
22 views

How to update Large table in MYSQL

I want to update a large number of rows (more than 80k) from an external data source (from file) into an existing mysql table. This database is InnoDB. Database structure: CREATE TABLE IF NOT EXISTS ...
0
votes
1answer
19 views

Newly created user has rights on MySQL database

I use the root to create a new user, like this: I have no more operations on it. Then I use this user to log in: I try to do some operations like this: Here "test" is a database which I created ...
0
votes
0answers
15 views

Unexpected slow queries at random times

I have a moderate service, serving at most 100~ users at the same time. Mariadb slow log is open, and I occasionally see reports of slow running queries such as: # Time: 141123 19:48:38 # User@Host: ...
0
votes
0answers
20 views

Fast select = slow update, slow select = fast update

A query is super-fast when executed as a SELECT (#1), but when I try to UPDATE using the retrieved data, the performance starts to be unpredictable for me. For example, an UPDATE is faster with much ...
0
votes
1answer
17 views

MySQL rapid Data Replication from multiple Databases

I am a Freelance Web Developer but not a Database Administrator at all except basic Database Operations like DML etc. Today I got a client asking me to develop a Web based application but there is a ...
0
votes
1answer
30 views

Unknown column in 'where clause'

I've a query in my php script $result = mysql_query("SELECT State_Id FROM state_master WHERE State_Name='$statename'") or die(mysql_error()); $row=mysql_fetch_assoc($result); return $row['State_Id']; ...
1
vote
1answer
30 views

Is a query inside mysql IF clause allowed?

DELIMITER $$ CREATE PROCEDURE `InsertUser`(IN passwd VARCHAR(100), IN userType VARCHAR(20), IN mobileNo VARCHAR(20), IN refId BIGINT(20), IN companyName VARCHAR(100), IN companyMail VARCHAR(200), IN ...
2
votes
1answer
28 views

How much space is needed to reclaim space from a table in INNODB

I inherited a MySQL InnoDB table that has approximately 200 GB worth of data, as well as several unneeded indices that amount to approximately 500 GB. I've archived approximately 75 percent of the ...
-2
votes
2answers
51 views

What's wrong with my SQL statement? [on hold]

I'm trying to execute: create table `certificates`( `id` INT NOT NULL AUTO_INCREMENT, `common_name` VARCHAR(100) NOT NULL, `csr` LONGTEXT NOT NULL, `type` VARCHAR(100) NOT NULL, `certificate` ...
1
vote
1answer
22 views

Does a database update force index rebuild regardless of updated field?

With MySQL (InnoDB), I understand that having indexes slow down UPDATE queries. I would like to know if this happens regardless of what fields are updated. For example, if I have a table with 20 ...
1
vote
1answer
51 views

How to get the SUM between dates in a WHERE clause?

I'm trying to get the SUM between each date in the WHERE clause of the following query, not the cummulative sum, but the sum for the period in between the dates. For example: Query: SELECT ...
2
votes
2answers
27 views

Joining two Mysql VIEWs takes a very long time

I have a textbox (for which I used jQuery auto-completion) in my PHP form where user can insert an actor/actress name and then by clicking "Search" button, a new window is opened showing list of ...
1
vote
1answer
58 views

How to combine two queries? [on hold]

I can't explain this very well except by spelling it out. I start a query on ID, and from there I can get TITLE, DATE, and REGNUMBER. The problem is I also want to get 'where,' which is also stored ...
2
votes
1answer
18 views

MySQL table indexes change location

Is it possible to move the MySQL Table indexes to another location rather than "datadir" ? for example a ssd drive or memory partition ?
2
votes
2answers
46 views

How can I simplify this UNION?

Here's the case - I have the following query: SELECT DISTINCT post_id FROM ( ( SELECT DISTINCT rp.post_id, pm.meta_value AS premiere_date FROM wp_related_people AS rp JOIN ...
1
vote
1answer
33 views

MySQL no query cache hits

My database have been up and running for a while with query cache enabled, but there are 0 hits. I have tried to check the settings but I am not sure what the problem is. Using InnoDB. mysql> SHOW ...
1
vote
2answers
26 views

Outer Joins to find records with no matching associated entries

I'm trying to find all foo records which do not have any bar records that match a given criteria. create table foo ( id integer ); create table bar ( foo_id integer, name varchar(255) ); ...
0
votes
1answer
19 views

WordPress MySQL writes during “Flush Tables With Read Lock” are rejected instead of being queued

I'm running into an odd issue when setting MySQL into a brief read-only mode for backup. Reads continue fine during that "read only" minute, but writes are just rejected instead of being queued to ...
1
vote
0answers
20 views

Migration error: “Invalid date literal detected” when reading decimal

I'm attempting a migration from MSSQL to MySQL and keep coming across a "Invalid date literal detected" error when reading certain decimal columns. I'm running the following (all up-to-date as of ...
1
vote
1answer
37 views

Taking the difference in timestamps between successive entries

This is my first foray into DBA.stackexchange, so I'll say up front: this is a query optimization question. If this is not an appropriate question for DBA, let me know and I'll skulk away quietly... ...
5
votes
2answers
132 views
+50

Is this update, select combination query thread safe?

I have a table called seats, which has a schema like this id,taken For each user, I take a random untaken id and assign to that user, here for simplicity say, I make taken =1. The query I am ...
2
votes
3answers
55 views

Running two DELETE statements causes a syntax error

DELETE `member` FROM `member` WHERE `member`.`type_id` = 1; DELETE `subscriptions` FROM `subscriptions` WHERE `subscriptions`.`type_id` = 1; Table: subscriptions ...
0
votes
0answers
13 views

MySQL Remote Database Failover: Need Advice

I run a network of sites all on 1 server. To improve performance, I'm (finally) moving the MySQL database to a remote machine. I'd love anyone's advice on how to appropriately approach failover. ...
1
vote
0answers
22 views

Migrate from SQLite to MySQL (then back)

For various reasons, I must use a SQLite database with a local application. However, I wrote a Flask app that enables users to more easily classify items in the database, and it runs very slow on ...
0
votes
0answers
25 views

MySQL 5.6.19a InnoDB Concurrent Insert

I've looked through most of the threads on this topic but still had question I was hoping someone would be gracious enough to help with. I am seeing low CPU usage and write latency but transaction ...
1
vote
0answers
21 views

Need advice on optimizing database on a recurring basis

I have a search engine with approx 3.5 million rows in the searchable table. There are many suppliers that feed into the table each day, so primary keys are constantly being removed and added and the ...
3
votes
2answers
410 views

Can anybody tell me what's wrong with this query?

CREATE PROCEDURE Click( in ipaddress varchar(45)) BEGIN DECLARE ex int; SELECT COUNT(*) INTO ex FROM clickactivity WHERE IP = ipaddress; IF ex = 0 THEN INSERT INTO clickactivity (IP, ...
0
votes
1answer
51 views

MySQL-How to find that value in a column is subset with all other values in same column multiple time

I have a table named as Team where table contain 3 column (ID,Team,Task). Its datatypes are (INT,VARCHAR(255),INT). Below you will find a sample table where I would like to perform the query on column ...
1
vote
1answer
44 views

Similar hardware, Same DBconfiguration and Database but different status variables result between Windows and Linux server

I have two database servers - one is Windows - 2008 and another is RHEL5 with similar hardware configuration. Both OS are 64 bit. MySQL 5.1.58 community edition is installed on Windows servers while ...
0
votes
2answers
48 views

Companies and addresses MySQL

I've got a database with 2 tables. companies addresses These are my tables. Adresses adress_id adress_adress adress_adressnumber adress_zipcode adress_city adress_postbus adress_country ...
0
votes
1answer
28 views

schema for mysql database with 30million rows

We am building a SaaS backend for restaurants. We integrate directly with POS, so each POS keeps sending customer orders that we store in a following table in mysql. CREATE TABLE name ( `id` ...