Tagged Questions

MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
2answers
52 views

Truncating and inserting in a safe transaction on MySQL

I am trying to TRUNCATE and INSERT data into a MySQL InnoDB table. I wanted to make sure that if any statement in the transaction failed, the data will be untouched. I can't have an empty or ...
0
votes
0answers
24 views

Billion records mysql performance with large result sets

I'm currently developing an application which will have several mysql tables where 3 of them have very large amounts of records. The thing is that my app will have to sync each account on a hourly ...
1
vote
0answers
24 views

MySQL not using multicolumn index on self join

I'm trying to optimise a query that performs an inner join with itself. The table size is currently 10GB ~50 million rows. /* Count all occurrences between start and end site id of a given speed ...
0
votes
1answer
31 views

MySQL - How to see the queries executing by slave io thread

We have a strange problem with MySQL replication. We have observed huge load and IO wait on server while replication is in progress. We can have information of the queries from bin-log by using ...
1
vote
1answer
48 views

How to get all distinct words in a column

I have a table with a text column that can contain a single word, more words separated by a space or a NULL value. I need to get all the distinct words in that column (words non column entries). For ...
0
votes
1answer
43 views

Steps or evaluation order of the SQL statement

How the SQL statement containing simple statement like Select x From y Where z Having, order by , group by etc. are evaluated.( In which order they are evaluated.?)
1
vote
1answer
83 views

MySQL: Why queries with subqueries are much faster than single query?

I made few queries on my db lately First query: SELECT `table`.* FROM (`table`) WHERE table.id in(27172172,[...bunch of ids...],27171770) ORDER BY field (`table`.`id`, ...
0
votes
1answer
18 views

Pros and cons in combining current data and historical data in the same table

What is the pros and cons in designing a RDB table to store both current data and historical data together? Is it scaleable? Is this approach better than having one table for current data and one ...
0
votes
1answer
34 views

COUNT() and link between tables

I have some difficulties to make a query. Basically, I have a 3 tables: member, build and up: member --+--- id|... build --+-----------+--- id|member (fk)|... up --+-----------+--------- id|member ...
1
vote
1answer
32 views

MySQL data to Postgres every X time

Our team (Uni students) have a project that we are updating. Old project team has a Live MySQL database. We want to make a archive/warehouse from it in PostgreSQL. My question is: How can we import ...
0
votes
0answers
21 views

Convert J1950 Date to Gregorian Date using a MySQL Query

I have a database table with a column named "time." The cells in the "time" column each contain a timestamp entry which was inserted in J1950 time format. I would like to convert the J1950 time to a ...
1
vote
1answer
17 views

Change entire MySQL drive location?

We cloned a VM server and on Server A MySQL is located on the F drive, but on Server B it's located on the E drive. I updated the ini file and the Windows path variables, but it's still trying to pull ...
1
vote
1answer
34 views

Mathematical or Computing Alternatives for MySQL Combination Queries operating toward their Performance Limit

We regularly run a contest that is becoming quite popular. In the contest, we need to select the combinations of contestants from 8 groups whose cost is under a threshold, and who combined have the ...
0
votes
0answers
27 views

MySQL multiple update queries vs. one insert … on duplicated key update query

I need to update hundreds to thousands rows. I wonder which of the following options is better: sending multiple (hundreds to thousands) "UPDATE" queries sending one (big) "INSERT INTO ... ON ...
0
votes
0answers
17 views

How to partition a MySQL table by Week using a timestamp column?

I have the following MySQL 5.6 table, which uses InnoDB and contains a timestamp column (I've omitted all the extra columns, in order to simplify my question): create table partition_by_week ( id ...
0
votes
1answer
34 views

How to skip OR if AND statement row count is not 0

Heey Guys, I have a question and i can't find the solution. So this is my QUERY SELECT * FROM `hwr_story` WHERE question_count = '8' AND (lower(subcategory) = lower('True') OR subcategory IS ...
0
votes
1answer
31 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 ...
0
votes
0answers
42 views

Displaying SELECT Query Results Row as Columns

I have 3 tables: scoreboards, problems, and users. A user can do several problems, and the score will be stored in scoreboards table. Every user will have same amount of row in scoreboards table ...
0
votes
0answers
20 views

High CPU Usage for Mysql after adding partitions

On an amazon m3.large instance (vCPU:2, Mem: 7.5), I run a mysql(5.5.29) server. Initially, the state of the server is OK except that the query is too slow. However, after partitioning the table ...
0
votes
0answers
9 views

MariaDB Galera cluster nod won't start

I have configured a MariaDB Galera cluster with three nodes. The master node and one of the slave-master node is up and running, but the third node just won't run. The system specs and cluster config ...
0
votes
0answers
30 views

WHERE NOT a = b vs WHERE a <> b

Do the following queries have identical effects in MySQL, and are they just a different way of writing? I am asking this because I am creating a query builder in php, ideally if one way is identical ...
0
votes
0answers
18 views

Differing AUTO_INCREMENT values between 5.1 vs 5.6 slaves

I have a MySQL 5.6.21 slave and a MySQL 5.1.73 slave that are both replicating from the same MySQL 5.1.73 master. I noticed that for several of the tables ( I said several because when I try to rerun ...
0
votes
1answer
18 views

MySQL - using ON DELETE CASCADE properly?

Trying to implement a simple user follower system in MySQL, using a junction table: CREATE TABLE users ( id int NOT NULL AUTO_INCREMENT, email varchar(255) NOT NULL, username varchar(25) NOT NULL, ...
0
votes
0answers
30 views

MySQL Inno DB Crash

This is the log of the crash. Basically every time I run a query it looses connection an crash. 2014-11-03 19:46:42 58021 [Note] Event Scheduler: Loaded 0 events 2014-11-03 19:46:42 58021 [Note] ...
5
votes
3answers
442 views

Speed of MySQL query for the most recent 800 entries in chronological order

I have a MySQL database running on an Ubuntu box that pings 250 clients once a minute (24x7). My column names are: ip_address varchar(16), status varchar(7), timestamp datetime Been running it for ...
0
votes
1answer
18 views

How to investegate MySQL crash on windows?

I have MySQL 5.6.19 running on Windows Server 2008 R2. I have 3 servers A, B, C Server A is a stand alone server. This server had data that can't be placed on SERVER B (ie. users information.) ...
1
vote
1answer
32 views

Append duplicate records with count (mysql)

We need to update a set of titles that have some duplicate entries. Because of the size of the database I would prefer to use direct mysql method, but I am open to any solutions out there. If we have ...
0
votes
1answer
57 views

NDB Cluster seems to ignore foreign_key_checks=OFF

According to MySQL documentation on the local foreign_key_checks variable Beginning with MySQL Cluster NDB 7.3.2, setting this variable has the same effect on NDB tables as it does for InnoDB ...
0
votes
0answers
12 views

SQL nodes in MySQL Cluster

I running mysql cluster in windows 8.1 on 3 machines 2 mngmd 2 sql node 3 data nodes in the first machine I tried to connect MySQL server with localhost and tried to put my old database it ...
0
votes
0answers
11 views

lot of relay log formation in mysql5.6

Reason for lot of small relay log file formation. I Have setup a slave and it relay log rotates to soon and creates thousands of relay log file.whats the reason for more relay log formation.
0
votes
0answers
10 views

Mysql table size reported, on disk and on slave

I thought I understood MySQL table sizes, but I have some confusion about it. I have two database servers, one primary and one slave (which was created recently). All DBs are replicated; no dangerous ...
0
votes
0answers
14 views

MySQL stored procedure return variable values from seconds query? [migrated]

SET @v1 = ''; SET @v2 = ''; SET @Query = CONCAT('SELECT sum(colName1), sum(colName2) INTO @v1, @v2 FROM tableName WHERE id=1 '); PREPARE stmt FROM @Query; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET ...
0
votes
0answers
29 views

How to avoid 2013 error when executing ALTER TABLE

I have a huge table (2.8 GB) and I need to add a column. After I run this query I experience a lost connection error (Error code: 2013. Lost connection to MySQL server during query): ALTER TABLE ...
0
votes
2answers
46 views

Moving MySQL to another server without updating connection strings

Currently, I have a Windows 2008 R2 Server which runs both all my websites and all my MySQL databases. I am considering moving MySQL to another seperate server which may also be Windows or may be ...
0
votes
0answers
29 views

mysql trigger Update a row after insert according to condition

I am writing a MySQL trigger. In which I would like to update branch_form_no with auto increment form number according to branch id. How should I do? I have table hcm_staffing_applicantform with ...
2
votes
2answers
40 views

How to retrieve elements from another table in a SQL query?

Sorry for my english !!! I get data from a table wp_posts and now I need to get data from the table wp_postmeta that match the ID captured in wp_posts table. The data that I need to grab in the ...
0
votes
1answer
26 views

Speeding up a simple select query on Mariadb

I have a 100k rows table with the following structure: +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
0answers
26 views

Database still slow after index added

I have a mysql database with 2 million rows. when using "order by firstname" which firstname is not an index, the database is exteremely slow. I added an index for "firstname" and I thought it will ...
0
votes
0answers
17 views

MySQL stops unexpectedly on CentOS 6.5 with WHM/Cpanel after migrating server with transfer tool [duplicate]

I recently moved my entire websites from a dedicated CentOS5 server to a CentOS 6.5 on Amazon EC2 with the same cPanel/WHM version. WHM 11.44.1 (build 19) However after angry calls from clients I ...
6
votes
2answers
321 views

Transaction speed benchmarks for mySQL v5.6 replication - seems very slow

I'm trying to select the best configuration for our new infrastructure but got a bit confused about the results. I used sysbench v0.5 for the tests: prepare data sysbench ...
1
vote
0answers
42 views

Lost Connection to mysql server at 'reading authorization packet', system error 0"

OK, this has been bugging me all afternoon now, I have a new MySQL Database '5.5.40-0+wheezy1-log' running on Debian Linux. It is to all extents and purposes a clean install. I have created two ...
0
votes
1answer
34 views

How to insert values in junction table for many to many relationships?

I have three tables in database: trips(trip_id(pk), trip_name(unique), user_id(fk)) places(place_id(pk), place_name(unique)) trips_places_asc(trip_id(fk), place_id(fk)) Since, many trips can ...
0
votes
0answers
19 views

MySQL Crash due to “InnoDB Assertion” after Query

Current setup: Ubuntu 12.04 LTS MySQL 5.5.31 Current Use: Slave - Only ETL queries 64 GB RAM Intel(R) Xeon(R) CPU X5670 @ 2.93GHz - 12 Cores - Hyperthreaded (24 Logical) MySQL Main Settings: ...
1
vote
1answer
26 views

Move a MySQL 5.5 database to a server running MySQL 5.1

I have a backup of a MySQL database (using InnoDB) from a Windows machine that was running MySQL 5.5. The Windows machine is no longer available and now I need to import the backup into an Ubuntu box ...
0
votes
2answers
37 views

Need help about giving relationships in database design

I am developing a simple blog application in which users can write posts and others can like, comment, share etc. i am facing difficulty in designing database. What I have tried is: user ...
0
votes
0answers
19 views

MySQL performance_schema not working properly?

I have MySQL 5.6.19 community server running on my Ubuntu 14.04. Lately I have been trying to monitor MySQL server using performance_schema. Previously I used to do this by running SHOW FULL ...
0
votes
2answers
20 views

Effects of monolothic databases on performance and security?

Please note: although I'm specifically calling out MySQL here, I would also appreciate/entertain generic answers that apply to all/most relational systems. As a developer I don't see the design ...
2
votes
1answer
34 views

Breaking down by minutes in between two dates

In MySQL, I've got a data model with a "visit" table that has start and end datetimes. How can I get a breakdown of the number of visits that are active per minute? e.g. | visit_id | start | end ...
0
votes
1answer
14 views

MySQL: Is there a way to update a field so that all foreign key related fields are updated as well?

I'm assuming the answer to this question is yes but it is hard to Google this type of question and not get overly ambiguous results. I am working with a database that was dumped from a Magento ...
0
votes
1answer
29 views

Execute grant for MySQL stored procedure revoked when stored procedure is altered

I have created the following stored procedure in a MySQL database (MySQL 5.5). DELIMITER $$ CREATE DEFINER=`remote_admin`@`%` PROCEDURE `usp_insert_deal_and_segment`() BEGIN "code here" END$$ ...