MySQL : Open-Source, Relational Database Management System
0
votes
1answer
10 views
'mysql' execute statment returns 'NULL'
Could you please advise why this query returns 'NULL' on BASH shell, basically I need to script it so could you please also advise what's the best practices to execute queries in BASH script?
MySQL ...
4
votes
1answer
14 views
ORDER BY optimization for a large table
I am currently aggregating posts from different social networks. Currently I have 50-100 million posts from Facebook, Twitter, Youtube, Instagram , Pinterest.
Consider a table posts
posts
{
id ...
0
votes
2answers
32 views
MySQL using too much CPU
I'm studying to be a DBA right now because I have a massive live database that is very sensitive that I manage. Here's the current system stats of my dedicated database server:
CentOS 5.9
24gb Ram
8 ...
0
votes
0answers
5 views
MySql Replication in two directions
I am successfully able to set up master-slave replication of database with MySQL. So, whenever I am updating my database on master, It will automatically replicated on the slave. Now, I have situation ...
1
vote
2answers
20 views
Rearranging the output of mysql query
When i run the following Query:
SELECT ticket.id, th.ticket_id , th.id, th.name, th.create_time
FROM ticket_history th
JOIN ticket ON th.ticket_id = ticket.id
WHERE (
th.name LIKE ...
2
votes
1answer
42 views
Is it ok to let users add new columns and tables to your database?
I'm basically making a database of databases. An administrative User can go in and hit "Create Database" (which is basically a table) and set up the column types and how many columns. Other users can ...
1
vote
1answer
30 views
How to select specific rows if a column exists or all rows if a column doesn't
I'm writing a script that gets a count of rows for a few tables, however for some tables I want to only get a count of rows where a flag is set (in this case active=1). Is there a way I can do this ...
1
vote
1answer
22 views
Creating schema for a shop that sells items from multiple tables
I am trying to create a table in mySQl that contains a list of all the items that a shops. For example:
shops (shop_ID (PK), shop_name)
shops_and_items (shop_ID (PK), item_ID (PK), price)
The issue ...
-1
votes
0answers
30 views
How much time is considered 'Ideal' for a SELECT query? [closed]
This is my first experience in developing for the masses, and I wanna know how much time is considered ideal for running a select query and returning the data to the client?
I've seen simple queries ...
2
votes
1answer
17 views
NEWB Getting to KNow New Database and Understand Where Relationships Are
I'm teaching myself SQL using MYSQL and a textbook.
While working through the textbook I'm using a database I'm kinda familiar with - an exported copy of my CMS driven websites database.
My website ...
0
votes
1answer
19 views
The InnoDB log sequence number is in the future
Our site was getting intermittent database errors and I asked my webhost to check what's happening. After some inspection they found that the database has few issues and tried repairing it. Finally I ...
0
votes
1answer
19 views
What is wrong with table_cache hit rate?
In my.cnf I have:
table_cache = 524288
open_files_limit = 65535
Both are at max allowed value for mysql config. Both are less than max open file limit:
# cat ...
0
votes
0answers
22 views
Query take a long time! Is that server issue?
When Query runs on Server (shared-server) it takes (2.5332 sec)
But when I run the same Query on another Server (dedicated-server), it takes only (0.0205 sec).
I don't know where is the issue! Is ...
1
vote
2answers
20 views
Why are there different levels of MySQL collation/charsets?
Looking at the MySQL server variables the server and database collation are set to latin1_swedish_ci but the collaction_connection is utf8_general_ci. Additionally, the collation/charset is set at ...
-1
votes
2answers
26 views
How to use GROUP BY after the Having clause Mysql
Here is the query which gets the accounts for a specific user the products are related to accounts in one-to-many relation so for each product there are four permissions
All the accounts which is not ...
0
votes
2answers
57 views
Which is better: more rows or more columns in a student attendance table?
I want to create an attendance table for student attendance. Which structure should I go for?
I have two options.
attendance(student_id,date,is_absent,reason)
...
0
votes
2answers
34 views
Commands like CREATE, GRANT, DROP always return “0 rows affected”
I was considering why some special commands in mysql like create user, grant privileges, drop user when correct passed return "0 rows affected", which is not true! (rows in mysql db are affected ...
0
votes
1answer
30 views
MySQL multiple rows with same id meeting two different conditions at same time
I am using OTRS helpdesk ticket management system. When i execute following Query:
SELECT `ticket`.`id`,
`ticket_history`.`ticket_id`,
`ticket_history`.`id`,
`ticket_history`.`name`,
...
0
votes
0answers
31 views
CSV File Into MYSQL Merging - LOAD DATA on Duplicate Key?
Hey Dba Stack Exchange,
My question to you is with regards to mysql. Right now I have a medium to small sized csv with 25k lines I need imported into mysql for a client. Problem is, their is only ...
1
vote
1answer
20 views
How to change a column type from SET to an ENUM
I have a table with a SET column type that need to be changed to ENUM type.
Can I do it with a simple ALTER or will this mess up my data?
NB: there are no multiple values (a,b,c) in the table.
0
votes
0answers
28 views
How to add column/alter structure to big table in MySQL [duplicate]
'm php developer so dont be strict. I have a big table ~5.5gb dump. Our PM decided to make new column in it to perform new feature. Table is InnoDB So what i tried:
Alter table in screen with table ...
0
votes
2answers
21 views
How to add column to big table in MySQL
I'm php developer so dont be strict. I have a big table ~5.5gb dump. Our PM decided to make new column in it to perform new feature. Table is InnoDB So what i tried:
Alter table in screen with table ...
0
votes
2answers
32 views
Create a trigger to update table data on another Server's database
I am creating a trigger in MySQL and I need a little help.
I have 2 websites, 2 databases (same name) on 2 different web servers, S1 & S2.
These databases have the same tables names.
I want ...
0
votes
1answer
19 views
How big can innodb_doublewrite_file grow?
When I use the enable innodb_doublewrite_file ...
How big can this file grow ?
How can I calculate the optimal disk size, if it would be used only for this file?
2
votes
2answers
31 views
How to change “tmpdir” on a running MySQL instance
Is there a way to change the tmpdir on a MySQL server without restarting it?
The issue is that I use a tmpfs mounted at /var/lib/mysqltmp but sometimes it's overflowing and causes issues. I don't want ...
-1
votes
1answer
29 views
trigger is not working properly [closed]
I have create trigger for some condition but its through error.
Here is trigger code.
DELIMITER //
CREATE TRIGGER `getcrm`.`update_round_date` AFTER UPDATE ON `getcrm`.`vtiger_stockcheckcf`
FOR ...
1
vote
2answers
44 views
Which MySQL engine should i use where most operations are read operations
I want to develop an application for which i will need a database to store data. I am using MySQL as backend. My question is Which MySQL engine should i use?
Well, i have mentioned some important ...
0
votes
2answers
23 views
Error while restoring a Database from an SQL dump
I am extremely new to MySQL and am running it on Windows. I am trying to restore a Database from a dumpfile in MySQL, but I get the following error:
$ >mysql -u root -p -h localhost -D database ...
2
votes
1answer
27 views
MySQL, optimize and replication
I have two MySQL (MariaDB actually) servers, a master and a slave with standard replication.
Two times already in less than a month I've had my slave become unusable.
This is what I experienced:
...
1
vote
1answer
22 views
MyISAM key buffer
Good evening,
I want to know how can I use informations about MyISAM to calculate :
size of buffer,
size of buffer used,
Write ratio,
Read ratio,
and MyISAM key cache hit rate
and thank you.
1
vote
1answer
18 views
Optimizing disk space for MyISAM tables: what is the benefit of ALTER and ANALYZE if you already do OPTIMIZE?
Per this post, in order to optimize the storage of a MyISAM table, you need to:
OPTIMIZE TABLE ;
ALTER TABLE ENGINE=MyISAM ;
ANALYZE TABLE ;
It's obvious what OPTIMIZE does for disk space but what ...
1
vote
1answer
21 views
Can I remove #sql_ .MYD files?
I found some old files in the mysql datadir:
-rw-rw---- 1 mysql mysql 0 2012-10-09 09:39 #sql_263c_0.MYD
-rw-rw---- 1 mysql mysql 1.0K 2012-10-09 09:39 #sql_263c_0.MYI
-rw-rw---- 1 mysql ...
0
votes
1answer
22 views
Is there a system wide way to change MariaDB to create tables using ROW_FORMAT = DYNAMIC
I've got a 700MB dump file from a mysql database that I was trying to undump into a new install of MariaDB and I got this error
ERROR 1118 (42000) at line 7481: Row size too large (> 8126). ...
0
votes
0answers
24 views
Federate engine to help overcome distributed transaction?
Currently we have been using pure innodb. The issue is that we have architecture where we have multiple outlet each have its own local db and a copy of it in the hq outlet. The issue is that we ...
0
votes
1answer
55 views
How to create a table in MySQL with automatic numeration of columns?
Example. My data is from Table name:BIKE
Bike_No Repair_Date Repair_Cost
-------- ----------- ------------
ABC1234 2013-01-05 50.00
BMX5678 2013-02-04 75.00
ABC1234 ...
1
vote
3answers
24 views
About changing data path by database
Is there any way to change physical path by database?
For example :
I have 2 databases in server.
database1 will be located in /data/database1/
database2 will be located in /data/database2/
Is ...
1
vote
1answer
16 views
Using a mysqldump export to create data with a different database name
I am using the following to import database using a mysqldump export:
mysql -u root -p example_db < path/to/export.sql
I would like to import the database to a database with a different name to ...
0
votes
2answers
32 views
how important are mysql's innodb logs?
I'm basically concerned about the following two settings, these are my default settings :
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
I haven't changed them for 2 reasons : one, ...
0
votes
2answers
21 views
Mysql slow.log user specific
I use a slow.log logging on my MySQL server, to catch bottlenecks in my script, but at the same time, i use phpmyadmin on this server. My script and pma has different MySQL user accounts and now, when ...
0
votes
1answer
24 views
MYSQL Timezone support
We are having a shared hosting plan and they are saying that do provide MYSQL Timezone support in a shared hosting plan. I can create timezone related tables in our database and populate them with ...
3
votes
2answers
71 views
Which design to choose in this case?
I am designing a database for a video game. I have some data structures like {x, y, z, dimension} used across multiple tables.
For instance :
Table player_death
id: int
player_id: int
death_x: int
...
0
votes
2answers
34 views
reset mysql root password on ubuntu
In short: my goal is to reset mysql root password on ubuntu.
Background: I set up a ubuntu desktop and installed LAMP stack last week. I then went to do something else, and just got back to carry on ...
0
votes
0answers
12 views
Mysql slow.log with noindex, but without tmp_tables
Is it possible to change anything in config to logging in slow log queries without index use, but not in the temporary tables. I have a lot enough subquerys, so it creating anytime temporary table, ...
-1
votes
0answers
21 views
Why is simple timestamp comparison performing slow
I have a table with 12,582,912 rows and can't figure it out why is a simple comparison ad_hot_expire > 1371399048 so slow (SHOW PROFILES: http://i.imgur.com/6L5HQyW.png)? The subquery is faster, ...
0
votes
2answers
21 views
What is the best way to transport database fields from one database to another?
I have two databases. The table name and fields name are different and field numbers are unequal. I need to transport all fields from one database to another. I can import the database as CSV format.
...
0
votes
1answer
26 views
Is it possible to avoid filesort?
Is it possible to avoid 'Using temporary' and 'Using filesort' for the following SELECT query? I can't figure out a way to do it.
I tried adding indexes, for both top_expire and program, but didn't ...
0
votes
0answers
16 views
To what extent is MariaDB Java client a perfect replacement for MySQL JDBC (Connector/J)?
Premise: I ship a Java application which will make a JDBC connection to a MySQL database. (At the time of this writing it could be 5.1 or 5.5, but hopefully the question and answer don't rely heavily ...
4
votes
4answers
46 views
Fast way of delete MySQL record
I have a 1.2 million record in a database table( it use index column also)
I want to delete old 1 million record. I try to do it using PhpMyadmin but it take more time.
DELETE FROM `table` WHERE ...
-1
votes
0answers
19 views
Generating a continuous sequence of keys in MySQL
I am looking for sequential logic in MySQL - if there is no data in the table the invoice number should start with DDXBL1 and the query should give the highest sequential number whenever I run it, ...
0
votes
1answer
21 views
MySQL Cluster Setup for WAN on Windows
I would like to know whether replication or clustering for MySQL is it possible over a WAN environment?
For example
Site A and Site B are both at different remote place.
Both have MySQL installed.
...
