MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
2answers
26 views

MySQL - Creating an associative table

I have the following table structures: Cases: (PK) case_id | case_notes | time 1 | blah | timestamp 2 | blah | timestamp 3 | blah | timestamp . | . ...
-1
votes
2answers
70 views

Why is “select join insert” so much faster than “loop insert”?

MySQL docs state that the time required for inserts has these approximate proportions: Connecting: (3) Sending query to server: (2) Parsing query: (2) Inserting row: (1 × size of row) ...
0
votes
1answer
19 views

replace in String from subquery

I'm currently (trying) to optimize veery weird code. So please don't teach me about bad DB design ;-). Assuming there are two tables messages and options In the messages table theres a string like ...
0
votes
1answer
15 views

MySQL table doesn't exist

What I did before the disaster was: vi /etc/my.cfg I added innodb_buffer_pool_size=4G innodb_buffer_pool_instance=2 innodb_read_io_threads=12 innodb_write_io_threads=12 innodb_io_capacity=300 ...
2
votes
1answer
34 views

Will large inserts crash a 3-node MySQL database cluster?

We have a 3 node MySQL database cluster than runs on Percona. I have a stored procedure that inserts a lot of rows in one hit, potentially over a million rows. I've been warned (by our DBA) this ...
1
vote
1answer
33 views

Reverse auto_increment id existing column in phpMyAdmin

I have imported csv file through phpMyAdmin into one table. I have set auto-increment field, as shown below, but of course it ordered it ascending: +----+------------+------+ | id | date | name ...
0
votes
1answer
23 views

Add '%' sign to MySQL results

I am using a query to calculate total percentage but I want a '%' symbol to appear next to it. Any ideas that could help me My query so far is SELECT student.StudentID, student.Name, ROUND ...
1
vote
1answer
54 views

MySQL Summary with Union

The following query does what I want, but I need to extend it, but i'm just not enough of an SQL expert to figure out what I'm doing wrong. SELECT sd.StockNumber AS StkNm, sd.Description As Descr, ...
1
vote
1answer
40 views

Database design import massive csv files is there a more efficient way?

I need to let users import huge csv files varied between 200k - 1.5m lines. For this I use LOAD DATA INFILE which is the fastest way to import those files. Thing is however that I can't check the user ...
0
votes
1answer
29 views

Big lists in a relational DB

I need to store a list of items and that list belongs to a single user. The lists are going to be huge, so this has to be done as efficiently as possible. The lists also have to be modifiable to a ...
0
votes
1answer
28 views

MySQL query INSERT INTO … failed - The Table “x” is full

I apologize if my question is incomplete or noobish, I have extremely limited knowledge of SQL but the boss wants me to figure this one out. Environment: Webmin/Virtualmin | MySQL version 5.1.73 on ...
0
votes
0answers
20 views

mysql store search result as new table in memory

I am a novice, and I am wanting to know how to achieve this: 1million plus row in a table. user runs a search, gets some results. I want to store this result in memory in a way, so that user can ...
0
votes
2answers
18 views

Logging the Execution times of dynamically constructed mysql stored procedures

I have a MySQL Server. The MySQL server contains a huge network data collection. The client API's will connect to the MySQL server and execute some dynamically created stored procedures and delete ...
0
votes
2answers
19 views

Vague replication error code 2005

2015-04-07 13:49:53 23758 [ERROR] Slave I/O: error connecting to master 'replication'@master-host:3306' - retry-time: 60 retries: 1, Error_code: 2005 This was after attempting to do a change master ...
2
votes
2answers
56 views

Squirel SQL uses wrong command for MySQL when renaming a column

I installed Squirel SQL on Ubuntu and a downloaded and put a MySQL driver into the lib folder of the Squirel folder. Then I chose this driver in the "extra class" tab when configuring Squirel MySQL ...
0
votes
2answers
28 views

Get rows where lastLoginTimestamp is in last 7 days

I'm trying to get the rows from a table where lastLoginTimestamp is in last 7 days and I'm doing as follow: SELECT * FROM representative WHERE DATE_SUB(lastLoginTimestamp, INTERVAL 7 DAY) ...
2
votes
2answers
80 views

How do I rank the total score in order of positions

How do I rank the total_score such that the highest total score has position 1, etc. If 2 or more have the same total score, then they should have the same position CREATE TABLE `scores` ( `id` ...
0
votes
0answers
10 views

Import database with views and routines into Amazon RDS

I need to import a collection of MySQL DB's into Amazon RDS without downtime. One can do this by initially setting up the RDS instance as a slave. There is one problem, you're not a super user on RDS ...
1
vote
1answer
62 views

MySQL Count rows from another table for each record in table

SELECT student.StudentID, student.`Name`, COUNT(attendance.AttendanceID) AS Total FROM student LEFT JOIN attendance ON student.StudentID = attendance.StudentID I am trying to count the last ...
2
votes
1answer
23 views

Is using MySQL views a good translation mechanism?

We want to use a foreign vendor MySQL database, but the whole schema is barely understandable by our development team. We could alter the schema to rename tables and columns once for all, but the ...
0
votes
2answers
53 views

mysql connection timeout on small traffic server

How can I set up MySQL so that connections never time out? And how do I even check to see what the current settings for variables such as wait_timeout and interactive_timeout, etc. are? Given that ...
0
votes
3answers
35 views

MySQL Exporting a query to text file via Cron Job

I'm in the beginning stages of hacking together code that syncs two completely different pieces of software/webware. I'm looking for a way to run a SQL query and output the results to a text file. I'd ...
0
votes
3answers
37 views

MySQL - How do I Calculate an Average Value Over a Period of Time When Only Value Changes are Recorded

I am tracking the sales price of various items, and I would like to calculate the average price of each item over a period of time. My simplified tables look like this: Items ------------------ |key ...
3
votes
2answers
50 views

Copying MySQL Raw Data Files for Master-Slave Replication and GTIDs

I have been developing and running a mysql-based web application (intranet-based) for some time now. I wish to establish a step-by-step sane sequence for creating a slave from the running MySQL ...
-2
votes
1answer
13 views

what else to enable to make LOAD DATA INFILE work?

Anyone got an idea what to enable to make the LOAD DATA INFILE working? I have the global variable 'local_infile' set to ON, the database is answering queries just normaly, but LOAD DATA INFILE ...
4
votes
2answers
130 views

search misses indexed column

I have a table like this CREATE TABLE `agentpositions` ( `idPosition` INT(11) NOT NULL AUTO_INCREMENT, `idAgent` INT(11) NULL DEFAULT NULL, `hardwareSerial` VARCHAR(128) NOT NULL, ...
3
votes
2answers
96 views

Inner Join and Right Join same query

I'm using MySQL and InnoDB, I have a table reserver with 2 columns : idReservation and idChambre I have a table reservation and a table chambre Now I want my query to return every "chambre.id" who is ...
1
vote
1answer
28 views

How to avoid MySQL slave to have errors after crash

I have a MySQL master/slave configuration (MariaDB 10.0.17). Sometimes, the slave or the master server crashes. After the crash and restart, I get some errors of duplicated keys on slave. The server ...
0
votes
0answers
25 views

mysql on delete cascade error

I have the following tables : as you can see there is a FOREIGN KEY from projects to files and another FOREIGN KEY from files to projects the sec one is set to on delete cascade (the idea is to ...
0
votes
1answer
39 views

GRANT permission to access to respective creator

Does the following command: GRANT SELECT ON EVENT TO STUDENT; grant student access to whole EVENT table? If yes, how to only give STUDENT access to EVENT(s) record created by respective STUDENT?
1
vote
2answers
28 views

MySQL unknown column 'password_last_changed'

When creating a user using this command create user 'foo'@localhost'; this error is shown ERROR 1054 (42S22): Unknown column 'password_last_changed' in 'mysql.user' Using MySQL server version: ...
2
votes
1answer
18 views

Attribute-Attribute Dependency

So as part of our college project, I'm working on CENSUS Database. I stumbled upon a problem wherein if an individual is below the age of 18, his/her marital status should only allow 'NA' and anyone ...
1
vote
1answer
2 views

Made changes to my.cnf and mysqld wont restart - no backup

I thought I'd be clever and update my.cnf file to add ft_min_word_len=2 and didn't back it up first. I will never make that mistake again. Luckily, our project is still in production and no harm done. ...
0
votes
1answer
21 views

Tmpfs for mysql tmp dir questions

I have a 768M tmpfs dir that I use for mysql temp directory. It occasional fills up when running reports on large date ranges. When this happen the report fails and I get an error 28. This actually ...
0
votes
1answer
46 views

Performance of MySQL BLOB fields processing

Suppose, I have large (about 1 GB) strings in ASCII encoding. I have to choose whether to store them as separate blob fields or split each one of them into smaller pieces and then store them as ...
0
votes
2answers
30 views

What is an easy query to fix up historical columns valid_from and valid_to?

I have a table with typical history rows using valid_from and valid_to dates. My table might look like this: | id | object_id | name | valid_from | valid_to ...
0
votes
1answer
13 views

SQL CHANGE DATE FORMAT IN BULK

I have a database with several hundred fields but my data structure is wrong. It is currently in uk format as follows: d/m/y 01/01/85 01/01/96 23/12/87 What would be the most efficient way to change ...
0
votes
1answer
52 views

Database : what design to point on two possibilities?

I am designing a datamodel for a new project. One of the requirements specifies that some objects can point either a person or a company. What is the smartest way to achieve that? I have thought ...
0
votes
0answers
40 views

Temporary table not supported in MySQL Cluster

I am new to MySQL Cluster and as I don't have opportunity to try it now I am interested in one thing. In the official pages of MySQL Cluster, there is a statement about temporary tables for NDB ...
1
vote
1answer
30 views

How can I remove references to tables after removing .frm files?

So I had a bunch of csv files in my /var/lib/mysql/imports directory for importing into mysql tables I had created. To clean up the folder I deleted all the files in the directory (incorrectly ...
0
votes
4answers
30 views

Aggregate data from two tables with different field names

I have two tables that each have date and amount fields. This is for a donation system so one is pledges and the other is donations. Here are the structure of relevant tables and fields: pledges ...
0
votes
1answer
25 views

Usage of non standardized queries

So this is more of a theory question than practical but I'd appreciate any help. My friend and I are doing a project to learn new stuff and have fun. He's a coder and I like to think that I'm good ...
0
votes
1answer
23 views

MySQL tmp and tmpfs disk

I have mysql and i use a tmpfs disk that is 768 M. Mysql uses about 70% of the memory. There is about 1170 M available. If mysql uses ram to the point there is less than 768; will my RAM disk be ...
1
vote
1answer
37 views

How to edit data to 2 tables using MySQL Workbench

How to edit data in 2 tables using MySQL Workbench (or can I)? I use the below query to get the data but unable to edit it from the table (or Form Editor). SELECT * FROM TABLE1 ...
0
votes
0answers
20 views

Add Row To table on update Trigger

okay trying again. I have a trigger that I want to fire off whenever my order status changes to 'wc-processing'. So that My trigger doesnt copy ALL data onto this new table (vr_key) I want to use a ...
-1
votes
1answer
21 views

How to data transpose in sql view?

Table1 Name CITY RANK Manish New delhi A Akash New delhi A Akash Aligarh B Vinay Barh C I have to transpose like Name New Delhi Aligarh Barh Manish A ...
0
votes
0answers
15 views

How to calculate complaint close time in hours and skip non working hours

**complain_id date_created date_closed** 1005 03/15/15 15:00 PM 03/16/15 11:00 AM I have to calculate complaint close time in hours and skip non working ...
1
vote
1answer
18 views

extract all unique chars from all texts across the table

I have a huge mysql table (20 columms by 320.000 rows) that contains a lot of text from a lot of languages and the problem is that I need to identify all the unique characters in that table. For ...
0
votes
0answers
18 views

How to get records in group when records are duplicated?

I have following table structure. Here id is primary key and auto incremented.Here Records are duplicated and id with 1 is parent records and id with 2 & 3 are its child records. same with id ...
1
vote
2answers
29 views

MySQL databases on their own separate Logical Volume

I'm looking for a way to keep MySQL database sizes within certain limits. That's why I was thinking about creating a logical volume for each database. So for example: db1 => lv_db1 (5 GB) db2 => ...