MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
3 views

How to ensure that another schema in available from the schema the webapp is connected to?

In an existing webapp and database architecture, the application connects to a MySQL instance where several schemas reside, beside the application schema; let's call it app_db. During certain ...
0
votes
1answer
10 views

show table name + number of records for each table in a mysql innodb database

How to list all tables in the current database, together the number of rows of the table. In other words, can you think of a query to come up with something like this in mysql? ...
1
vote
0answers
16 views

MySQL instance stalling - due to InnoDB log flushes?

An instance of MySQL 5.6.20 running (mostly just) a database with InnoDB tables is exhibiting occasional stalls for all update operations for the duration of 1-4 minutes with all INSERT, UPDATE and ...
0
votes
1answer
15 views

InnoDB compression: memory usage

InnoDB offers table compression for Barracuda-type tables and the recommendation seems to be to "adjust" buffer_pool_size. The documentation is rather vague about the definite impact of compression on ...
0
votes
0answers
16 views

Calculating a running balance to store with transactions

I need to put a read-only copy of balance and transaction data from a legacy system, which will only be used to view/print statements. Balance | id | member_id | product_id | balance | | 1 | ...
0
votes
1answer
35 views

How to group uninterrupted squences and get min max?

I have table where I am storing some sequential data that indicate occurrences of some event given day. id from_id to_id date 1 2 1 2014-08-01 2 2 ...
0
votes
2answers
30 views

#1093 - You can't specify target table 'R' for update in FROM clause

As an attempt to answer Difference data between adjacent rows UPDATE Visits R SET Duration = TIMEDIFF( ( SELECT ReqTime FROM Visits N WHERE N.ID > R.ID AND N.Session=R.Session ORDER BY ID ...
1
vote
1answer
14 views

Using file .log.3.gz

I have a file which I need to look at for errors, I got it from someone its extension is mysql.log.3.gz and when I uncompressed it, it has mysql.log.3, It is a log file? And if I open it in text ...
1
vote
2answers
61 views

Online Hostel Management System ER Diagram

I have created an ER diagram for an online hostel management system. Please advise me if this diagram makes sense or needs improvement.
2
votes
2answers
62 views

How to generate auto increment values for my duplicate column in my sql?

How to display when i was run to find duplicate values from my table at this time i want to add new column in select statement with auto increment values SELECT * FROM tutorials_tbl WHERE Lscno IN ...
2
votes
2answers
98 views

Difference data between adjacent rows

I have a table (called Visits) with ReqTime DATETIME NOT NULL column AND Duration INT UNSIGNED column. Duration should be set to the difference of ReqTime for two adjacent rows. But Duration is not ...
1
vote
0answers
45 views

Query does not use index (according to EXPLAIN SELECT)

Can anyone give me an indication, why this query does not use the Key on the table 'gtg'): ...
0
votes
1answer
25 views

How does subpartitioning actually work and what physical files are created?

I can't understand how the subpartitioning works in mysql. I am reading the following example: create table invoices ( customer_id int not null, product_id int not null, amount decimal ...
1
vote
1answer
44 views

join from different tables gives same result, but it shouldn't

This is the code I'm using and I'm having a problem with it. SELECT `disenos`.*, `votos`.`valoracion`, `usuario_contacto`.`nombres`, `usuario_contacto`.`apellidos`, `users`.`username`, ...
2
votes
0answers
34 views

Emulating WITH clause in MySQL/query perfomance review

I have never really worked with MySQL queries till this last week. I was tasked with making a simple tool for my department that tracks contacts. Here are the 2 tables and the type of data they ...
0
votes
0answers
14 views

replication error Error Duplicate entry… for unique key

I have two database that one of them is replicated of another one,my tables have unique key on some column ( NOT primary key ),so if in replication process an Error Duplicate entry..... occur,these ...
1
vote
1answer
64 views

Pivot table with MySQL

I'm trying to create a pivot table like output (cross table? Not entirely sure about the difference). The idea is to have rows, columns and summary values (specifically: distinct count). The data ...
2
votes
1answer
38 views

MySQL CPU & Memory Spikes

Every couple of days, my server's CPU and memory usage spikes up to 100%, making our vBulletin forum almost unusable for 5-10 minutes. CPU is typically at 1-2, Memory at 3GB. During the spikes, CPU ...
3
votes
1answer
57 views

Row size error with MySQL

I'm running a MySQL server on my Macbook (for testing). Version is 5.6.20 from Homebrew. I started running into "Row size too large" errors, and I've been able to reduce it down to this test case. ...
-1
votes
2answers
21 views

How to convert time to minutes in Mysql

I want to change the time to minutes and update the table and insert the value in the field minutes Id time minutes 1 01:00 ? 2 00:44 ? 3 01:13 ? ...
0
votes
2answers
38 views

How to store user's language knowledge (150+ languages) in mysql DB

Users in my website must be able to select languages they know (English, German, etc), so I have a table languages with 150 languages (id, name) and table users (id and a lot of other fields). ...
0
votes
0answers
34 views

Slow mysql UPDATE with IF() conditions

not sure why I can't even EXPLAIN this query. Mysql refuses to EXPLAIN it. Table contains a million meaningless test entries. Full schema and data dump of the table available here: ...
2
votes
1answer
45 views

Is data warehouse monitoring same as database monitoring? [on hold]

I have a requirement, where data warehouse population is to be monitored. I am in confusion, and needed a clear picture. Is data warehouse different than database? eg. For monitoring database we ...
0
votes
0answers
14 views

Optimal Database Configuration

We have developed a Multilingual website based on Drupal platform. I want to know the optimal configuration for MYSQL database (an possibly server hardware) if we were to expect 50,000 hits per hour ...
1
vote
0answers
18 views

Mysql syntax error when i add on completion preserve

I have written this event in mysql DELIMITER $$ CREATE EVENT le_drop_database ON SCHEDULE EVERY 1 WEEK ON COMPLETION PRESERVE STARTS CURRENT_DATE + INTERVAL 4 - ...
1
vote
1answer
14 views

Import multiple CSV fields into one MySQL field

I'm trying to import our CSV data log into our MySQL. The first two fields of the CSV are Date and Time, and I would like to combine that into one date time (which can be searched) onto MySQL. CSV: ...
2
votes
3answers
127 views

Can I use PHP function strtotime() in SQL queries?

I have this table, and I need to use a PHP function in the query. id time lastTime 1 08:02 2 08:04 3 08:30 I am using this query with the strtotime(), and it's not working: ...
0
votes
1answer
35 views

Translating MySQL query to PostgreSQL

I have a query which works fine in MySQL, however when I try to recreate the same query in PostgreSQL (same DB structure) I just get a syntax error. MySQL: SELECT ticket.queue_id, queue.name, ...
0
votes
0answers
26 views

What is the difference between Row and Table Expressions in MySQL

Can anybody explain with example what is a Row and Table expression and what is the difference between them. Any example for clarification will be appreciated.
0
votes
1answer
55 views

Is normalization advisable on transactional data?

I have read a lot of articles about normalized vs denormalized schemas, but I find very little mention of the cost of row insertion. I am considering the impact of converting a 100% denormalized ...
0
votes
1answer
41 views

JOIN on 3 tables being slow

everyone. Can't figure out what indices are missing or how to rephrase my query. I created about a milllion of test entries in each table to see performance. Query takes about 1.5 seconds which is ...
1
vote
1answer
25 views

Adding time to an event that runs on a weekly basis

I have this event that runs every Thursday CREATE EVENT le_drop_database ON SCHEDULE EVERY 1 WEEK STARTS CURRENT_DATE + INTERVAL 3 - WEEKDAY(CURRENT_DATE) DAY DO BEGIN drop database carbon; ...
0
votes
0answers
11 views

RAID configuration for mysql [on hold]

I attached 4 10G disks to mysql Server and I used RAID 0 but still same mysql performance the same, Do I have to change configuration in my.cnf to let mysql know about RAID 0
1
vote
2answers
118 views

RAID configuration for mysql

I attached 4 10G disks to mysql Server and I used RAID 0 but still same mysql performance the same, Do I have to change configuration in my.cnf to let mysql know about RAID 0. this is my mount ...
0
votes
1answer
40 views

What's faster: two UPDATEs with WHERE (in / not in) or one UPDATE with CASE?

What's best performance wise, two separate UPDATEs: UPDATE table1 SET enabled = 0 WHERE code NOT IN ( SELECT id FROM table2 ); UPDATE table1 SET enabled = 1 WHERE ...
0
votes
0answers
10 views

MySQL - Moving binary logs to Network Server On Windows

Is it possible to move the Binary logs for MySQL (on Windows), to a different physical server? My MySQL Databases are MySQL 5.5. (Bearing in mind that ultimately this will be a Master DB, and there ...
1
vote
1answer
26 views

Table is dropped but ibd file still exists

I was dropping some unwanted tables I have in the MySQL. When I run a drop table, its showing table doesnot exists. But when a check in the data directory, the .ibd file exists and its almost 1GB. How ...
0
votes
2answers
26 views

Rank variable value in MySQL is always being set to null

In a sample procedure I am trying to find the rank the of the cities according to the number of offices. I have a Location Table and an Office Table and a location can have multiple offices ...
1
vote
1answer
36 views

Should I drop this column or is there a query that can achieve a results table without duplicate columns?

I use a column named active in several of my database tables to adjust whether a particular entry/row/user/etc should be included in future query result tables instead of deleting the information. ...
0
votes
0answers
10 views

long mysql insert and update script user variables appear to be lost while running script

I have a mysql database table called 'dash_something' dash_something ------------- date, DATE *this is the date of the summary type, CHAR(10) entity, CHAR(25) number_of_emails_1, INT ...
0
votes
1answer
35 views

MySQL JDBC does not behaves as the MySQL Workbench

I'm running the following from the MySQL Workbench, and from my local tomcat using jdbc. START TRANSACTION; CREATE SCHEMA IF NOT EXISTS `ROOT`; CREATE TABLE IF NOT EXISTS `ROOT`.`all_tables` ( ...
1
vote
0answers
35 views

Many small queries or one single query

This is not exactly a question, more of a survey. Here at BlackBean we have a time series aggregation and analysis platform built on top of MySQL, where each client have your own database, and each ...
1
vote
1answer
25 views

Indexing the difference between two columns in MySQL

Is there a way to optimize/index a query that sorts the difference between two columns. I have a modest sized table of 5 million rows that I'm trying to do the following: SELECT pk_column, column_x, ...
0
votes
3answers
41 views

PostgreSQL equivalent of MySQL query variables?

Is there a straightforward way to adapt these types of MySQL queries to PostgreSQL: setting variables in MySQL like set @aintconst = -333 set @arealconst = -9.999 It seems not. Assigning ...
0
votes
1answer
60 views

queries for hierarchical data

This table is a questions and answers CMS and i want to know how data is fetched from these tables. +------+----------+-------+ | id | parentid | type | +------+----------+-------+ | 1 | 0 ...
1
vote
1answer
49 views

Adding foreign keys constraint on databases used

We have a server with about 20 databases, all duplicates of the same original db, and we just noticed the original is missing all the foreign keys constraint for some unknown reason. So of course all ...
8
votes
2answers
201 views

Why are simple SELECTs on InnoDB 100x slower than on MyISAM?

I have quite an annoying problem. I want to use INNODB as my main database engine and give up on MyISAM as I need the former for using galera-cluster for redundancy. I copied (description follows) ...
0
votes
1answer
64 views

Speed up a MySQL BETWEEN query

SELECT dateToFind FROM table1 INNER JOIN table2 ON dateToFind BETWEEN dateFrom AND dateTo I need to improve the performance of this kind of query. I added BTREE indexes on dateToFind, dateFrom, ...
0
votes
1answer
39 views

MySQL Replication: Seconds Behind Master super high

I've set up a slave db server for my production database, but when I checked the show slave status, I noticed a super big number in seconds behind master. This is the output: ...
1
vote
1answer
23 views

InnoDB deadlock with INSERT .. SELECT

I am trying to understand why a deadlock occurs when two workers in parallel are running INSERT .. SELECT queries on a table which has both 1) auto incremented primary key, 2) a unique index on ...