database-administration interview questions
Top database-administration frequently asked interview questions
What are the differences between the Oracle SYS and SYSTEM built in accounts?
Edit: Apart from 3 letters!
Source: (StackOverflow)
i used the following sytanx
drop database filmo;
and got the following error:
ERROR 1010 (HY000): Error dropping database (can't rmdir './filmo/', errno: 17)
any ideas..
Source: (StackOverflow)
I am working with a postgreSQL database that gets updated in batches. I need to know when the last time that the database (or a table in the database)has been updated or modified, either will do.
I saw that someone on the postgeSQL forum had suggested that to use logging and query your logs for the time. This will not work for me as that I do not have control over the clients codebase.
Source: (StackOverflow)
I use Squirrel SQL to connect to a JavaDB/Derby database on my desktop. I can run SQL queries.
But how can I list all tables in the database? And preferably all column and column types.
Source: (StackOverflow)
I need to quickly (and forcibly) kill off all external sessions connecting to my oracle database without the supervision of and administrator.
I don't want to just lock the database and let the users quit gracefully.
How would I script this?
Source: (StackOverflow)
Operating system used is linux. I have tried Navicat and SQL Power Architect. They did display relations between tables in the same schema. I have some foreign key constraints which reference tables in a different schema.
Am I missing something with respect to Navicat and PostgreSQL Maestro? Can they not display the inter schema relations?
Is there data visualisation tool for postgresql which is capable of displaying inter schema relations as well?
Source: (StackOverflow)
This seems to be an overlooked area that could really use some insight. What are your best practices for:
- making an upgrade procedure
- backing out in case of errors
- syncing code and database changes
- testing prior to deployment
- mechanics of modifying the table
etc...
Source: (StackOverflow)
I have large database of 22GB. I used to take backup with mysqldump command in a gzip format.
When i extract the gz file it produces the .sql file of 16.2GB
When I try to import the database in my local server, it takes approximately 48hrs to import.Is there a way to increase the speed of the import process?
Also i would like to know if any hardware changes need to be done to improve the performance.
Current System Config
Processor: 4th Gen i5
RAM: 8GB
#update
my.cnf is as follows
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 512M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 4M
query_cache_size = 512M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
[mysqldump]
quick
quote-names
max_allowed_packet = 512M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 512M
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
It is being uploading for 3 days and right now 9.9 GB has been imported. The Database has both MyISAM and InnoDB tables. What can i do to improve the import performance?
I have tried exporting each table separately in gz format with mysqldump and importing each table through PHP script executing the following code
$dir="./";
$files = scandir($dir, 1);
array_pop($files);
array_pop($files);
$tablecount=0;
foreach($files as $file){
$tablecount++;
echo $tablecount." ";
echo $file."\n";
$command="gunzip < ".$file." | mysql -u root -pubuntu cms";
echo exec($command);
}
Source: (StackOverflow)
I write a backup shell script to execute mysqldump.
mysqldump -u$BACKUP_USER -p$BACKUP_PASS --flush-logs --lock-tables $DB_NAME > $SQL_FILE
My db's storage ENGINE is MyISAM. So I can't use --single-transaction option.
The --lock-tables only lock one table in mysqldump progress.
There are many databases in my MySQL instance, I don't want to use --lock-all-tables, it will lock all databases run in my server.
So, How to lock all tables in ONE mysql database at sametime so I can dump it?
Source: (StackOverflow)
I have a table (SQL Sever) which references paths (UNC or otherwise), but now the path is going to change. In the path column, I have many records and I need to change just a portion of the path, but not the entire path. And I need to change the same string to the new one, in every record.
How can I do this with a simple update?
Source: (StackOverflow)
Can I (How do I) configure Sql Server 2008 to notify an operator if any step in the Job fails?
I have a Sql Server job with several steps to update data from multiple different sources, followed by one final step which performs several calculations on the data. All of the "data refresh" steps are set to "Go to next step on failure". Generally speaking, if one of the data refreshes fails, I still want the final step to run, but I still want to be notified about the intermediate failures, so if they fail consistantly, I can investigate.
Source: (StackOverflow)
I downloaded a VM image of a web application that uses MySQL.
How can I monitor its space consumption and know when additional space must be added?
Source: (StackOverflow)
I hope the title is somewhat helpful. I'm using MySQL as my database
I am building a database of products and am not sure how to handle storing prices/SKU of variations of a product. A product may have unlimited variations, and each variation combination has its own price/SKU/etc..
This is how I have my products/variations table set up at the moment:
PRODUCTS
+--------------------------+
| id | name | description |
+----+------+--------------+
| 1 | rug | a cool rug |
| 2 | cup | a coffee cup |
+----+------+--------------+
PRODUCT_VARIANTS
+----+------------+----------+-----------+
| id | product_id | variant | value |
+----+------------+----------+-----------+
| 1 | 1 | color | red |
| 2 | 1 | color | blue |
| 3 | 1 | color | green |
| 4 | 1 | material | wool |
| 5 | 1 | material | polyester |
| 6 | 2 | size | small |
| 7 | 2 | size | medium |
| 8 | 2 | size | large |
+----+------------+----------+-----------+
(`products.id` is a foreign key of `product_variants.product_id`)
I've created an SQLFiddle with this sample data: http://sqlfiddle.com/#!2/2264d/1
The user is allowed to enter any variation name (product_variants.variant) and can assign any value to it (product_variants.value). There should not be a limit the amount of variations/values a user may enter.
This is where my problem arises: storing prices/SKU for each variation without adding a new table/column every time someone adds a product with a variant that did not exist before.
Each variant may have the same price but the SKU is unique to each product.
For example Product 1 has 6 different combinations (3 colors * 2 materials) and Product 2 only has 3 different combination (3 sizes * 1).
I've thought about storing the combinations as a text, i.e:
+------------+-----------------+-------+------+
| product_id | combination | price | SKU |
+------------+-----------------+-------+------+
| 1 | red-wool | 50.00 | A121 |
| 1 | red-polyester | 50.00 | A122 |
| 1 | blue-wool | 50.00 | A123 |
| 1 | blue-polyester | 50.00 | A124 |
| 1 | green-wool | 50.00 | A125 |
| 1 | green-polyester | 50.00 | A125 |
| 2 | small | 4.00 | CD12 |
| 2 | medium | 4.00 | CD13 |
| 2 | large | 3.50 | CD14 |
+------------+-----------------+-------+------+
But there must be a better, normalized, way of representing this data. Hypothetical situation: I want to be able to search for a blue product that is less than $10. With the above database structure it is not possible to do without parsing the text and that is something I want to avoid.
Any help/suggestions are appreciated =)
Source: (StackOverflow)
I have downloaded the mysql-5.1.38-linux-x86_64-glibc23.tar.gz from here
and then i have executed it by using below command
groupadd mysql
useradd -g mysql mysql123
cp mysql-5.1.38-linux-x86_64-glibc23.tar.gz /home /mysql123/
su - mysql123
tar -zxvf mysql-5.1.38-linux-x86_64-glibc23.tar.gz
mv mysql-5.1.38-linux-x86_64-glibc23 mysql
mkdir tmp
cd mysql/
mv suppport-files/my-medium.cnf my.cnf
cp support-files/mysql.server bin/
and then i have edited the my.cnf and set the basedir and datadir to /home/mysql123/mysql and /home/mysql123/mysql/data and innodb_home_dir and logfile directory to datadir
Now edited mysql.server and set the datadir and basedir in them properly
and then initiated mysql_install_db as
[mysql123@localhost mysql]$ ./scripts/mysql_install_db
./scripts/mysql_install_db: line 244: ./bin/my_print_defaults: cannot execute binary file
Neither host '127.0.0.1' nor 'localhost' could be looked up with
./bin/resolveip
Please configure the 'hostname' command to return a correct
hostname.
If you want to solve this at a later stage, restart this script
with the --force option
on seeing the error i thought it may be confused with basedir and executed the same as below
[mysql123@localhost mysql]$ ./scripts/mysql_install_db -–user=mysql123 -–basedir=/home/mysql123/mysql
./scripts/mysql_install_db: line 244: ./bin/my_print_defaults: cannot execute binary file
Neither host '127.0.0.1' nor 'localhost' could be looked up with
./bin/resolveip
Please configure the 'hostname' command to return a correct
hostname.
If you want to solve this at a later stage, restart this script
with the --force option
i am not gettin what is going internally and showing this kind of message
and i am sure that i have enough diskspace ( df -h ) and i have proper ownership ( chown mysq123:mysql /home/mysql123/ -R) and proper permissions ( chmod 755 .)
and the lines in mysql_install_db are like below
please any help to solve this problem is very useful ( and i have to follow the same installation process)
i am using redhat 6
Source: (StackOverflow)