MySQL : Open-Source, Relational Database Management System
0
votes
0answers
81 views
Try to find a match using SUBSTRING(SHA1(value)), 0, 6)
I have a tbl where all entries for companies that participate in a online project are saved. This table is named "business". Those companies can use their employess to access the online platform i 've ...
0
votes
0answers
57 views
How to efficiently model this simple data structure?
catId(INT) | userId(INT) | catName(VARCHAR)
0 1 Cat1
1 1 Cat2
2 2 U2Cat1
3 2 ...
0
votes
0answers
53 views
Tomcat lost connection with mysql server
I have an application running in 2 servers, in one server I have the tomcat7 installed and in the other I have MySQL server installed.
The application works correctly until I go home, the next day I ...
0
votes
0answers
207 views
Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)! in Fedora16
I installed MariaDB server in linux using tar file(mariadb-5.5.28a-linux-i686.tar.gz) followed by the instructions from this link : https://kb.askmonty.org/en/installing-mariadb-binary-tarballs/.
...
0
votes
0answers
44 views
Partitioning for queue tables
I have a queue table where entries are added by a service with the WAITING status and a due date, which may be seconds to weeks from now. Those due entries are consumed and updated with the PENDING ...
0
votes
0answers
222 views
How to integrate sqlite extension in XAMPP?
There's an extension on the http://www.sqlite.org/contrib?orderby=date, extension-functions.c, which integrates the SQRT command in sqlite.
How can I integrate this and use it in my Windows-XAMPP ...
0
votes
0answers
27 views
How Best to Parition Temporal Data for Access By Date
The latest edition of the High Performance MySQL book has this to say about paritioning a large table where all the hot data is clustered temporally:
Suppose you have a table with an ...
0
votes
0answers
42 views
Which mysql server 5.5 variables would be involved in queries locking in sending data state?
A large portion of my queries (~58%) be it for queries with previously quick return rates or other, are spending in my monitor as sending data. I made some configuration changes but it was very broad ...
0
votes
0answers
46 views
Best way to merge a Sharded MySQL
I have a MySQL database that is shared across 16 different database servers, and i also have 1 replica for each machine. To save money as product becomes less popular I am wanting to shard down into a ...
0
votes
0answers
185 views
What to do to reduce first byte time after removing slow queries?
I've set long_query_time to 1 sec and found only 2 query which took 1 sec to execute at 2 different points of time since yesterday. So I don't suppose such 2 queries cause my first byte time to be 3 ...
0
votes
0answers
52 views
Configure phpmyadmin to run on different hosts
Just created locally a new host pointing to a new Virtual Host.
And added through command line a new user at the new host.
So like 'user1'@'localhost' I now have 'user2'@'local2' .
The problem is I ...
0
votes
0answers
36 views
search in mysql with like statement
imagine i have this table named "test_table" in the "test_db" in MySQL :
field1 field2
google http://google.com
...
0
votes
0answers
58 views
Mysql Stuck On “Copying to tmp table”
I have a query which is running on mysql 5.5, and i stuck at the temprory table problem.
Query is working in small amount of time but when i am sending concurrently it's stucking in status of ...
0
votes
0answers
70 views
What is faster - multiple access of table or using temporary table?
I have created two tables
create table one(ref_no int, name varchar(100), design varchar(100));
create table two(SNo int auto_increment primary key, use_date Date, ref_no int references one(ref_no), ...
0
votes
0answers
303 views
Leave management database
I am creating a database for a Leave management system.
But I don't understand how to arrange table and where.
My goal is:
There are four types of leave allowed for an employee (Leave_Types
were ...
0
votes
0answers
31 views
Performance While merging two mysql DBs
I have two similar schema DBs which I need to merge. I was thinking about the performance issues which I need to keep in mind while creating the script. These are the ones which I can think of.
...
0
votes
0answers
219 views
InnoDB row level locks without SELECTs?
I have several (>30) MySQL clients inserting into InnoDB tables concurrently. I keep encountering lock wait timeouts and can't figure out why. Here is the situation:
Each client runs the following ...
0
votes
0answers
89 views
To scale horizontally, is MySQL with sharding, memcache and master-slave replication better than MongoDB
I am building an api, to share discounts between users. Each discount can be linked with several users, using joins and many to many relationships.
Currently I am using MySQL and I am looking to ...
0
votes
0answers
29 views
“Disk leak” with xtrabackup intrementals/innodb
I have several Percona Mysql databases being backed up using Xtraback. Aside from the mysqldb these are all innodb databases. These are setup on a weekly full backup schedule with multiple daily ...
0
votes
0answers
41 views
How to check on my partitions?
I am a newbie into partition. First I have a table with auto increment with its primary key and many other fields. I wanted to partition based on dataInsertDateTime column. It have me error so I had ...
0
votes
0answers
454 views
Dynamically Converted rows into columns (Pivoting)
Query
SELECT
Performparam.performparam_Name,studfeedback.studfeedback_Rating, SUM(studfeedback.studfeedback_Rating) AS total,
COUNT(studfeedback.studfeedback_Rating) AS totastudents, ...
0
votes
0answers
89 views
Moving database table data from MySQL to Oracle
I've developed a website with Drupal 7 running on standard PHP/MySQL server. Now there's a need to move it to server with Oracle database. I managed to set up testing environment with a fresh Drupal 7 ...
0
votes
0answers
38 views
Partitioning and Sub-partitioning in MySQL
I want to partition a table by month and sub-partition by day.
When new data is added for a new month, I don't want to manually update the database.
When I initially create my database, I have data ...
0
votes
0answers
61 views
Unable to set table-specific privileges
I want to give a user access to only a few tables. I execute this query:
GRANT SELECT (credits_features_csgo, steam_code, credits_csgo, userid,
credits_forum, credits_features_tf2, credits_tf2, ...
0
votes
0answers
112 views
Which update is faster using join or sequential?
My this question is in sequence of my previous question I asked.
I could write two solutions using Stored Procedure instead of trigger or nested-query .
Both use a helper function ...
0
votes
0answers
66 views
Custom doctrine default character set
In my.ini mysql file, i have this :
[mysqld]
collation-server = utf8_general_ci
character-set-server = utf8
When i create my database with "doctrine:database:create", my collation db is in ...
0
votes
0answers
87 views
innodb deadlock on different tables
I am using mysql 5.5.28.
I would like to ask a question about the nature of deadlocks in innodb engine.
I monitor deadlock with pt-deadlock-logger.
Recently I found deadlock of two transactions to ...
0
votes
0answers
166 views
how to control aborted_clients?
Hi,
my aborted clients are keep on increasing. How to control it?
We are executing alter table pt_online to add index.
table size is 150gb.
(1) Restart the pt-online-schema-change online tool or ...
0
votes
0answers
207 views
MySQL: Get info from SHOW ERRORS
How can I get the info (code and message fields) from SHOW ERRORS? Or even, how can I insert this information into a table to keep tracking the errors?
Something like
SELECT CONCAT(Code,' - ', ...
0
votes
0answers
110 views
how to improve performance of my query it was taking more than one hour in my environment
How can I improve performance of below query? It was running more than one hour to execute in my production environment. Query plan:
mysql> explain
SELECT transaction_detail.trans_id,
...
0
votes
0answers
83 views
can not insert big data to mysql database
I am trying to save big data to database I extend the max_allow_packet and post_size but nothing happened.
the code and database released to production server and to stage server on production server ...
0
votes
0answers
34 views
Group by collection_item_id and order by products.weight
SELECT
`products`.*
FROM `products`
INNER JOIN collection_items ON products.collection_item_id = collection_items.id
WHERE `products`.`product_type_id` = 1
AND (products.status = ...
0
votes
0answers
153 views
Slow INSERT INTO SELECT statement – not repeatable!
Short Version:
INSERT INTO SELECT into a table with fulltext index with the same data takes sometimes 25 seconds and sometimes 2500 seconds. We have no idea where this huge gap is coming from.
Long ...
0
votes
0answers
142 views
Optimizing MySQL for a shared hosting setup
This is my current setup:
[mysqld]
default-storage-engine=MyISAM
local-infile=0
symbolic-links=0
max_connections = 500
max_user_connections = 25
key_buffer = 64M
myisam_sort_buffer_size = 32M
...
0
votes
0answers
24 views
MySQL search_data table
I've got a search_data table taking up 2.5GB of space.
I presume it's something to do with MySQL search queries but I'd like to reclaim some space - can I safely delete it?
Edit By @RolandoMySQLDBA
...
0
votes
0answers
67 views
Should I use compression in my InnoDB tables for this project?
I'm building a web portal that has a lot of tables and data is mostly id's (integers) in range 0 -> max, and strings of chars (VARCHARs) and fixed char(1) like 'y' and 'n' s mostly.
I switched (still ...
0
votes
0answers
163 views
Error connecting to DB
In regards to:
http://serverfault.com/questions/442218/remounting-mysql-under-a-new-mount-point
Everything is running. MySQL started but for websites they can not connect to the mysql database at ...
0
votes
0answers
198 views
MySQL Amazon RDS DIY Sharding Strategy (plug-n-play)
I'm creating my own sharding solution with MySQL. I develop my application in ASP.NET 4.5.
My idea is to create a document wit the available shards for every table:
components_servers[
{shard_id: ...
0
votes
0answers
100 views
GROUP_CONCAT with ORDER BY , but results are not orderd
SELECT
*,
(SELECT
GROUP_CONCAT(url SEPARATOR '$$' )
FROM project_photos
WHERE project_id = projects.id
ORDER BY priority) AS images
FROM projects
WHERE catID = 2
LIMIT 0,5
above query works fine but ...
0
votes
0answers
279 views
Inserting query result to another table hangs on “Copying to temp table on disk” on MySQL
I started the process of inserting returned results to another table. The query groups the rows in respect of indexed IDs. This causes 149,000,000 rows to be decreased to 460,000 rows.
The query ...
0
votes
0answers
129 views
Migrating from MySQL to Cassandra without DataStax
Currently have a MySQL DB used by a Java app. As we are seeing growth, looking at feasibility of moving to Cassandra and how expensive the move would be.
I understand that we will have to heavily ...
0
votes
0answers
20 views
A qq reg SBL with current_timestamp()
A quick check reg binary log. I would like to know since which version onwards we have current_timestamp() value recording correctly on binlogs? Like, when we have current_timestamp() as system ...
0
votes
0answers
37 views
Give privilege to new created databases in MySQL
I have user "U" who I want to give him full access to all the existing databases except "db1". I want him to only have select privilege for that db. What I did is I gave "U" all permissions for each ...
0
votes
0answers
52 views
MySQL Partioning: Is there a performance tradeoff between number of partitions and size of each partition?
I have a large table (several 100 million rows) that I would like to efficiently partition. My question is whether there is a tradeoff between partition size and number of partitions. As far as I ...
0
votes
0answers
201 views
mysql high load
as you can see from the screen print below mysql is using a lot of cpu, when there isnt much going on. i have had to restore to a default my.conf so i might need some settings changing. could be wrong ...
0
votes
0answers
54 views
MySQL database design help
EDIT: also posted on stackoverflow: http://stackoverflow.com/questions/12148715/mysql-database-design-check-in-system
I am attempting to build a check-in system for an office building.
What happens ...
0
votes
0answers
58 views
How to perform several operations at DB level
I need to insert 25 000 or 50 000 test rows for make some performance tests in several tables in my DB. I'm using PHP but the script takes to long to run so in shared hosting is a pain! I'm asking if ...
0
votes
0answers
261 views
Potential cause for memory leak
I have a replication setup where one of those nodes started experiences a slow but steady memory leak.
The setup is kind of like this
M---->S
|
\/
FilteringSlave
| | | |
R1 R2 R3 ...
0
votes
0answers
132 views
Access denied in bash script
I'm using a modified version of automysqlsump.sh
I don't want to have a single dump but every single table for itself, so I can write the tables back into the db per table and choose whether I want ...
0
votes
0answers
59 views
Charset issues when migrating from mysql4.0 to mysql5.1
I'm having some trouble when moving a SQL database from an old mysql4.0 db to a newer mysql5 db. We're moving the db by copying the datafiles from the /var/db directories.
Problem is, the characters ...
