MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
37 views

How to use a subquery that returns multiple rows?

I am getting Error 1242 : Subquery returns more than 1 row. Basically, I have to do this : select data from my database based on a time criterion (between 2 dates) and then apply a filter on the ...
0
votes
0answers
14 views

How to execute a mysql prepared statement on different database?

Is there an option to execute a prepared statement on a different database? ex: USE @database PREPARE pstmt FROM @stmt; EXECUTE pstmt; DEALLOCATE PREPARE pstmt; I need to set the database ...
0
votes
0answers
23 views

Need a High available for Database : HA clustering vs failover with mysql utilities

I intend to setup a high available for database system, I have 2 solutions here : Set up a HA clustering with heartbeat and share storage for 2 nodes as master(to storage the configuration and data ...
0
votes
0answers
37 views

How to insert into MySQL table SUM of two cells from different tables using insert function

I want to make sum of two cells from different tables in MySQL using INSERT function. The code that I used is: $panoue="INSERT INTO usi2(latime_paneli, inaltime_paneli, latime_panele, ...
0
votes
0answers
31 views

mysql memory usage increasing

We have recently been facing consistent issues with mysql memory on almost all of our machines. Through the course of the week, we see the memory rise gradually until the machine starts swapping and ...
0
votes
1answer
40 views

problem with query two different tables with foreign key relations

I am having a problem with querying two tables with different foreign key relations... My first query works fine but when adding another table with a different set of relations I am having a ...
0
votes
0answers
21 views

Use an API key for MySQL login instead of password

Basically I am using a MySQL account in an application to send data back to me so I can use it in various ways, but this of course degrades my security. Anybody can login to the account and cause ...
0
votes
1answer
29 views

select statement for subtype

Okay, so all I'm trying to do here is list the managers who supervised the employee that charged a customer $100 for a rental on November 1st, 1998. My issue is that I don't know how to select the ...
0
votes
1answer
79 views

Not able to solve issue: Incorrect string value: '\xC5\xABt\xC4\x97'

I am facing database error Incorrect string value: '\xC5\xABt\xC4\x97'. I did a lot search and tries a few ways, include move from utf8 to utf8mb4. But still not able to solve it. Following are the ...
1
vote
4answers
72 views

How to make a remote mysqldump?

I know that's it's possible to do a remote mysqldump if I specify --host but is it possible to create the file in the remote server as well?
1
vote
2answers
49 views

How to solve intermittent MySQL performance issues on simple queries?

We are currently experiencing an intermittent issue with UPDATE/DELETE/INSERT performance. Simple queries like the following take a long time: DELETE FROM ip_filter WHERE filter_id = '348'; ...
0
votes
0answers
21 views

Best configuration for high speed insert in Mysql using innodb engine

I am using apache storm to process csv files and insert the output to mysql. Apache storm process the data and produce output at a very high speed. I find Mysql inserts comparatively very slow. How ...
1
vote
2answers
50 views

Creating index on large MySQL table taking forever

My table has 981 million rows and is about 30GB large. It is six columns: ID, Ticker, Date, Time, Price, Volume. The data was in text files and using LOAD DATA INFILE was actually quite painless and ...
3
votes
1answer
111 views

Stored Procedure to update an adjacency Model to Nested Sets Model

If you don't already know, these two models are the most common ways to store a tree in a relational DB. Adjacency Model: +-------------+----------------------+--------+ | category_id | name ...
0
votes
0answers
41 views

Design a database with test table with different answer format

I am designing a database with tables: For each test,there should be a template and one test has many product code and each product code has or has no attribute. tb_template(id,name) ...
0
votes
0answers
19 views

tmp_table_size and max_heap_table_size setting

Does anyone know whether or not setting global MYSQL variables(tmp_table_size and max_heap_table_size) to the same value will prevent Converting HEAP to MyISAM (since there should never be a temporary ...
0
votes
0answers
15 views

Search column, get row of result, check if another column in the found row has a certain value [migrated]

id user profile profile id 1 mark data 12 2 john time 13 3 sinc time 13 4 alex data 12 I have the above table called users in a database called rd. Is there a ...
0
votes
2answers
39 views

How to get sum of the rows of 3 tables

I have 3 tables and I want to know with one query sum of row's number in 3 tables I tried 2 different queries but it they give only the number of rows each table and I c'ant read with, return ...
1
vote
1answer
46 views

Clarification for database creation and relations

I've trying to make database with few tables and relations between them. As I'm not very good in databases at all I need some clarifications. The idea is to connect table with table_1, table_2 and ...
0
votes
2answers
44 views

Hierarchical data in MongoDB?

I have a question regarding Hierarchical data. Our app shows posts, that are structured inside parents. Here's how it goes: prof->sprof->subject->post (Each prof can contain multiple ...
0
votes
1answer
39 views

selecting table1 and table2 with single data row and table 3 with multiple data rows

I want to retrieve data from table1(payments), table2(properties), and table3 (property.assessments). See my visual representation below: tables screenshot Snippets: SELECT a.payment_id, ...
0
votes
0answers
44 views

Is there any way to improve this code?

SELECT tblstud.studid, tblsched.ip FROM tblstud LEFT JOIN tblstudsched ON (tblstud.studid=tblstudsched.studid) LEFT JOIN tblsched ON (tblstudsched.schedid=tblsched.schedid) ...
0
votes
0answers
15 views

Local Server Slow When Joining

I have two cira 600MB tables that I am querying as such: SELECT id, name, url FROM names, urls WHERE metaphone = 'ALX;FLX' AND urlId = urlRef; This runs fine on my live server ( ~ 0.02 sec,) but on ...
0
votes
1answer
29 views

GET_LOCK([name], 0) behaviour and multiple GET_LOCK() order

I read from the documentation that the behavior of distributing a lock requested by many connections after RELEASE_LOCK is called from the connection holding the lock is undefined. I was curious: say ...
0
votes
0answers
17 views

Mysql native driver or just use pdo

I am now exploring on prepared statement. I was using the codes below. $link = new mysqli(dbHost, dbUser, dbPassword, dbDatabase); if($link->connect_error) { die('bind_param() failed: ' . ...
0
votes
1answer
45 views

MySQL over total memory allocated usage? memory leak?

i'm using 5.6.21-70.0 for performance test. when i run mysqlslap -a --concurrency=40 --number-of-queries 1000 --iterations=500 --engine=innodb --debug-info -utest -p do some performance test ...
0
votes
1answer
39 views

Big table with simple queries, but with a lot of text, is it bad for performance?

I am making cache table for website. It will have no problem with indexes (one unique index, simple quires), but it will have a lot of utf text. It will be 10-15gb (2 mln rows). Caching will help a ...
0
votes
0answers
22 views

MySQL blob removes last bites when selecting in some strings

When I retrieve blob it get cut from the end a little. Most strings are ok, but some got cropped for several last charecters. echo $data, '<br />'; db_execute("UPDATE table SET blobcolumn = ...
0
votes
1answer
47 views

How do I report progress of a long running transaction in MySQL?

I have this transaction in a stored procedure: ... START TRANSACTION; WHILE @Codes > 0 DO EXECUTE qry_rand_stmt; EXECUTE qry_insert_stmt; SET @Codes = @Codes - ROW_COUNT(); END ...
0
votes
1answer
32 views

DELIMITER issue in MySQL 5.6

Drop procedure if EXISTS rotatepjp; DELIMITER // CREATE PROCEDURE rotatepjp() BEGIN DECLARE u_id, settingval, pjp_userid, pjp_beatid, u_frequency INT; DECLARE u_pjpstartdate, pjp_fordate, ...
0
votes
1answer
38 views

Mysql Get Column Name after Case Max [closed]

i have this scenario with this query that trasform multiple rows key/values in a columns. SELECT t1.*, t2.*, u.name, u.email, u.username, MAX(CASE WHEN t2.datakey = 'latitude' THEN t2.data END) ...
1
vote
1answer
49 views

MySQL Shutdown on Windows

Folks, I am running MySQL on Windows8 for my personal (casual) use. I am trying to determine if there is a way to automatically and cleanly shutdown MySQL whenever I shutdown my Windows8 computer. ...
-1
votes
1answer
53 views

Database size after importing the .sql file [closed]

I was about to import a .sql file into a database. The size of the sql file is 35GB, and the available size in our server is 10gb. At this point, can I import the sql file into the database? I'm not ...
0
votes
0answers
37 views

Mysql cluster edition uses more RAM than configured in configuration file for data nodes. Both SQL and DATA nodes show full RAM usage

I have a MySQL cluster 7.3.6 setup with 4GB RAM for SQL nodes and data nodes on Ubuntu. RAM usage is shown using ndb_mgm's all report memory command is less and even if I have 500 MB data, both SQL ...
0
votes
0answers
40 views

MySQL: Load data with FK constraint into existing tables with same ids values already used

I have these tables: table1_main table2_main table1_test table2_test table1_main and table1_test have identical structure and table2_main and table2_test also have the same structure. table2_main ...
0
votes
0answers
27 views

mysql high CPU usage

i have a problem with mysql database. My system is a VM with 2 cores and 2GB of RAM with Ubuntu 12.04 and Mysql 5.5.40 installed. After i make a query Mysql process is using 100% of CPU and a query ...
0
votes
0answers
31 views

How to use ID field AUTO_INCREMENT sorted by Date field?

Any ideas how I could use ID field AUTO_INCREMENT sorted by Date field? Date field consist of dates and I would like to have to ID (INT NOT NULL AUTO_INCREMENT) field ascending values from oldest to ...
0
votes
0answers
22 views

Very slow IO on mysql percona server

Our site goes down because the IO on our database servers is too slow. Troubleshooting this we found that mysql is really really slow. It's constantly reading from disk and I think not using enough ...
0
votes
0answers
56 views

I can not connect mysql server via ssh

I installed SSH secure shell client on my windows machine.I am trying to connect mysql server installed in linux machine, throw java code using JDBC. Here is my JDBC code: package com.abc.db; ...
0
votes
1answer
23 views

import text file getting command is not allowed with this MySQL version

here is my table CREATE TABLE cities ( country CHAR(2), city_ascii VARCHAR(100), city VARCHAR(255), region CHAR(2), population INT UNSIGNED, ...
0
votes
1answer
45 views

ERROR 1226 (42000): User 'root' has exceeded the 'max_user_connections' resource (current value: -1)

I'm using mariadb and have error ERROR 1226 (42000): User 'root' has exceeded the 'max_user_connections' resource (current value: -1) I'm set max_connection to 1000 and not set max_user_connections ...
0
votes
1answer
3 views

MySQL recovery procedure

After the server die, I have "old" HD (it is external now) with sock file and data files in mysql directory. Where I should find information how to restore the data in fresh installed MySQL? What ...
0
votes
1answer
24 views

Should I use additional id values as unique values that only applies in junction tables in MySQL?

I have created three tables called person, metadata_image and person_image. person table contains info about people. person_id person_name person_bio metadata_image table stores the images. image_id ...
0
votes
1answer
30 views

Is it possble to replicate MySQL server on Azure virtual machines

I have two Windows Server 2012 R2 virtual machines on Azure. I need to replicate the database on each of the windows servers. I have followed this link but I am not getting the connection between the ...
2
votes
1answer
50 views

Efficient manual cleanup of foreign keys in MySQL [closed]

I have an all-InnoDB database in MySQL (Percona) 5.6 that was previously using FK constraints. I had to give up on them as I kept running into locking issues, so I removed the constraints and added ...
0
votes
1answer
35 views

Local MySQL instance running extremely slow compared to remote version

I'm working on a local project and it have a remote version running normally and I did imported all data from external DB to local instance. When I run a query with a specific FUNCTION, it run too ...
0
votes
1answer
44 views

custom product variant upload

I'm building a custom eCommerce solution for a client. I came up with this database design: +---------------+ +---------------+ | PRODUCTS |-----< SKUS | +---------------+ ...
0
votes
0answers
15 views

custom product variant design [duplicate]

I'm building a custom eCommerce solution for a client. I came up with this database design: +---------------+ +---------------+ | PRODUCTS |-----< SKUS | +---------------+ ...
0
votes
1answer
23 views

Any recommendation on my.cnf on aws.large instance?

Any best recommendation variables for my.cnf on percona server on a high 256GB RAM and 32 CPU with 4000 IOPS? How about below ones? > innodb_buffer_pool_size=180G > ...
0
votes
1answer
18 views

Is this normal binary logging behaviour - maintaining one ever growing file despite expire_logs_days?

I have a Percona MySQL DB running on a dedicated server. I have in my.cnf # REPLICATION # log_bin=/var/lib/mysql/binlogs/mysql-bin expire_logs_days=7 My issue is that the log file mysql-bin.000001 ...