MySQL : Open-Source, Relational Database Management System
0
votes
0answers
9 views
Problem with close connection [migrated]
I'm using myBatis on JBoss, transaction was managed by JBoss (MANAGED in myBatis config).
I'm using EJB too. Every time, when is EJB used is new database connection created and after while database ...
0
votes
0answers
11 views
Using Workbench to export. Views no longer work
I'm using Workbench 5.2 from my production DB machine (Ubuntu Linux). I've been trying to export. First try.. I had the entire schema selected including all tables and views in the right hand ...
0
votes
0answers
9 views
MySQL+Web-App Performance Issues using different servers
We are having a performance issue with our MySQL servers that does not make any sense. I have read countless articles from many people (mostly Percona) and have made my.cnf tweaks. We have even manage ...
0
votes
1answer
24 views
bit column vs multiple join tables
Hi all I asked a question earlier about how I could define a many to many relationship between users and FOSes (fields of study). My original question was how could we know whether the user was the ...
0
votes
0answers
13 views
How to debug a db memory-leak causing mysql to go before it's own limits?
We are having a problem with one of the database server of one application, possibly caused by some code that is creating a problem in the way Mysql manage it's memory.
Until the second week of ...
2
votes
1answer
31 views
MySQL Subquery hangs / terrible perfomance
I am using MySQL 5.5.16 @ CentOS.
Please have a look at this query:
SELECT
inventory_ID,
(
SELECT
price_brutto
FROM
_inventory_prices
...
0
votes
0answers
13 views
MySQL GIS - point in polygon storedproc too slow
My server is on a shared host which is running MySql version 5.5.30-30.2 - Percona Server (GPL), Release rel30.2, Revision 509 (taken from phpmyadmin).
I have a table containing 10,023 records ...
0
votes
0answers
17 views
updation of a column after expire of specific time in mysql
i have a table where i have a column named 'state'(int(2)) and 'modify_time' (time_stamp on update current timestamp).
i have an update trigger which changes the value of state column to 0 based on ...
0
votes
2answers
39 views
Two nullable columns one required to have value
No-Explanation Question:
Is there anyway to have a constrain of 2 null values that always requires 1 to have value ? For example two date columns both null but having at least 1 that requires to have ...
0
votes
1answer
14 views
Executing local scripts against remote server?
I am in a situation where I would like to execute a number of sql scripts that are on my local machine against a remote MySQL instance. I found this question which seems to point me in the right ...
0
votes
0answers
17 views
What's the best database for analysis/research applications, i.e. involving complex queries but few transactions? [closed]
I'd like to start a discussion on what people think what the best database environment (MySQL, MS SQL, PostgreSQL, ...) is for data analysis and research applications like encountered in empirical ...
0
votes
0answers
8 views
MySQL - Faster after reload?
I'm running MySQL 5.1.41 (innoDB) on Ubuntu 12.10.
I noticed that the MySQL performance will be better immediately after I restart MySQL.
This smells like something bad, so I'm wondering what could ...
-1
votes
0answers
14 views
Accessing a NAS using MySQL/Etc [closed]
Probably an easy question, but I want to make a webapp to sort of View/Sort/Etc... Movies/Music/Files on a NAS (It's a Synology Station on a Home Network). There shouldn't be any reason a database ran ...
2
votes
1answer
24 views
Is there any way to get the optimization time in MySQL?
I'd like to see the time optimizer takes to optimize a query. Is there any way to get that time?
0
votes
1answer
37 views
Changing primary key from varchar to int
I am having user table where i am using varchar as primary key.
Now from different place I got the performance of int is better over varchar for priary key.
So i would like to change my primary key ...
0
votes
0answers
24 views
MySQL asynchronous multi master replication conflict resolution
I have setup Multi Master replication, following this blog thread:
http://0wned.it/geek-bits/guides/mysql-multi-master-replication/
And now it synchronizes perfectly, up until the moment the ...
1
vote
1answer
25 views
Like with the results of a subquery (multiple values)
What I'm trying to do is the following concept:
SELECT field1,field2,field3
FROM table1
where field3 like '%'+(SELECT distinct field from table2)+'%'
But this is not allowed because like does not ...
1
vote
0answers
31 views
Can't get rid of filesort on joined query sorting on second table
For some reason, this query:
EXPLAIN SELECT * FROM biz as b INNER JOIN listings as l ON b.id = l.biz_id
WHERE l.pub_id = 14 AND b.cat_id=310 ORDER BY l.level DESC, l.random DESC LIMIT 5;
says it ...
0
votes
1answer
10 views
UTF8 vs ASCII or custom binary format: high-performance optimization on very big tables
The summary of my question is whether there are advantages, even if minimal, to use ASCII, or even a smaller format designed yet created specifically for the situation, instead of UTF-8 for strings.
...
2
votes
0answers
60 views
+50
Relation to original tables or to existing linking table
In my database I have a table with different settings for my app. Each setting is in relation to a guest (table guests) and an event (table events). So basically each guest has specific settings for ...
0
votes
1answer
21 views
MySQL cannot create foreign key, errors 1005, 150
I'm trying to add a foreign key to a MySQL InnoDB table, linking a BIGINT with a table within the same database. I'm using MySQL Workbench to do this. It generates a script:
ALTER TABLE ...
2
votes
1answer
22 views
MySQL error 2006 MySQL server has gone away after upgrade to 5.6.10 using homebrew on Max OS X 10.8.3
I upgraded my installation of MySQL on my Mac OS X 10.8.3 using homebrew
brew install mysql
Everything seemed to go smoothly, but...not really, as it turns out.
I cannot add a new user (neither ...
0
votes
1answer
32 views
Multiple junction tables?
I am working on a system where you have users, and you have Fields of Study or 'fos' for short. Each user can have many fos and each fos can have many users. So we are dealing with a many to many ...
0
votes
1answer
16 views
MySql - set my.cnf location at server start
I want to have two MySql servers running on the same system (for master-slave replication tests purposes).
To be able to do this I need both servers read different my.cnf files.
As I see at startup ...
0
votes
0answers
25 views
MySql Database Design for Bollywood Movies (India) [closed]
I'm trying to get my head round this mind boggling stuff they call Database Design without much success, so I'll try to illustrate my problem with an example.
I am using MySQL and here is my ...
0
votes
0answers
25 views
Find the partition where data lives in MySQL
Is there a way to select the partition name where a specified record actually lives in? The idea is something like:
SELECT <PARTITION_NAME> FROM mytable WHERE <where_condition>
I found ...
0
votes
0answers
26 views
Why does mysql show Primary key constraint on a View?
As far as I know views cannot have a primary key.
However the result of
SELECT FROM KEY_COLUMN_USAGE
says differ.
PRIMARY | S_MOBILE_ITEM_VIEW | ID |
...
0
votes
0answers
24 views
What is the easiest way to convert the Wordnet prolog .pl file into the Mysql table?
is Anyone used to convert the Prolog database format WordNet files into Mysql table?
There is a website http://www.androidtech.com/html/wordnet-mysql-20.php which did the similar thing. However, it ...
0
votes
0answers
22 views
How to use Python and XML for distributed DBMS [closed]
I have a project where 3 VMs (say VM1 VM2 and VM3) are given to me. Each with 4 GB ram, and 50 GB space. They have known static IPs.
All 3 VMs are installed with ubuntu 10.04 and MySQL. VM2 and VM3 ...
2
votes
1answer
18 views
Table optimize command crashed MySQL server
I have a large 5GB table on one of my MySQL databases that I wanted to optimize.
I went into the console and ran the optimize command and since it's an InnoDB database it cloned the table to a ...
0
votes
0answers
11 views
exported .SQL file won't import completely in mySQL
I am running MySQL on an Apple server OS X 10.6 (and am a complete newbie to SQL). I am trying to import a .SQL export file that was generated by an application called MailSteward Pro. The .SQL file ...
0
votes
1answer
34 views
Is it possible to change the MySQL server-id during a SQL session?
I have a MySQL 5.5 server with a server-id of 20. I want to execute a few queries on that server, but have them write to the binlog as a different server-id than 20. Is this possible?
I am aware set ...
0
votes
1answer
41 views
Is it possible to force drop of mysql tables with FK?
For dropping a table in which a Foreign Key is used in other tables, it is necessary to drop all other tables having FK connection with the corresponding table.
Is there a short way to drop a table, ...
1
vote
1answer
36 views
Setting mysql slow query to less than 1 second
I have a heavily loaded LAMP system serving about 40 requests per second and doing about 300 mysql queries per second. Here is the output of mysqladmin status
Uptime: 5051 Threads: 1 Questions: ...
1
vote
2answers
33 views
Label data according to percentile
I have a table name temp with column customer_id , sum and I need to add extra column customer_label (may be on view) which is 1 if customer lies in top 10% according to value of sum, 2 if lies within ...
1
vote
1answer
10 views
Mysql query grouping by two columns for max date takes too long
I have table that looks like this:
l_p l_a l_timestamp l_n l_act l_name
123 321 2011-1-1T01:00:00Z B q das
21 23 2012-1-1T01:00:00Z C q sd
123 ...
1
vote
0answers
28 views
MySQL Partitioning Performance
I have a MyISAM table with billions of rows that is still causing me problems because the indexes, even after I shortened them as much as possible, do not fit in my 64GB of RAM.
I am unable to index ...
0
votes
0answers
37 views
MySQL CSV LOAD DATA INFILE … doesn't want to load more than 72 lines
I'm trying to load a .csv file into my database with many lines of input from a website I'm working on. For some reason I can load files multiple times (over and over) into the database but they have ...
3
votes
2answers
19 views
How to design indexes for columns with NULL values in MySQL?
I have a database with 40 million entries and want to run queries with the following WHERE clause
...
WHERE
`POP1` IS NOT NULL
&& `VT`='ABC'
&& (`SOURCE`='HOME')
&& ...
1
vote
1answer
36 views
How often should MySQL (stock, Percona, etc.) be upgraded?
My organization runs several data-driven web applications backed by various flavors of MySQL on RHEL (i.e. we're just like everyone else!) Some of our web apps use the standard build of MySQL, and we ...
-1
votes
0answers
19 views
Unable to restore cpanel database backup [closed]
I recently created a complete backup of my cpanel to move it into another server. Now the insert queries generated by cpanel for wp-posts table (corresponding to WordPress posts) are too big since ...
1
vote
1answer
23 views
Trigger not Working
I created trigger on table PENDING.
Pending table has 3 columns
uniqueId
duration
maxDuration
I have another table COUNT with 2 columns
req_id
total
Here is my trigger--
CREATE TRIGGER ...
0
votes
1answer
23 views
Mysql settings for query_cache_min_res_unit
What is the best setting for query_cache_min_res_unit for these results:
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| ...
1
vote
2answers
24 views
Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating
Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating and it happens for the similar queries for the same table and the queries got ...
0
votes
1answer
17 views
MySQL Err 1032 on only 1 Slave
I'm getting a very strange error on one of my MySQL servers and I'd appreciate some help.
The Setup
I have a separate environments.
* Environment 1 consists of the Master Server and 1 Slave.
* ...
0
votes
2answers
24 views
How can I add a column to multiple tables at once in MySQL Workbench?
I have a MySQL Workbench model (diagram) that belongs to a Ruby on Rails application. I now need to add the created_at and updated_at timestamp columns to each of the tables. What is a quick and easy ...
2
votes
2answers
34 views
Trigger for updating a count of rows from a table into another table
I have these two tables:
mysql> select * from orders;
+-------------+---------+
| Customer_ID | Item |
+-------------+---------+
| A01 | CPU |
| A01 | Monitor |
| A02 ...
0
votes
1answer
8 views
Check if bit is set in mysql
I have a field which stores the days of the week in binary representation. For example, Mon-Wed-Fri would be represented as b'0101010' with the 1st digit as Sunday and the last as Saturday.
What I ...
2
votes
2answers
40 views
MySql is not optimizing the query properly
I have a table structure as follows :
CREATE TABLE `sale_product_inventories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sale_id` int(11) NOT NULL,
`product_id` int(11) NOT NULL,
`size` ...
2
votes
4answers
66 views
Drop tables where the last update is over 1 week ago
I can see in PMA that it stores information for every table.
I was wondering if there was an SQL query to drop any table in the database that have a last update time of more than 1 week ago?
