Tutorials

11). How to setup admin user for MYSQL ?

Login as super user 'root' in mysql and execute the following commands.

mysql> use mysql;

mysql> create user 'tom'@'%' identified by 'tom';

mysql> grant all on *.* to 'tom'@'%' with grant option;

mysql> flush privileges;

12). What types of privileges are there in MySQL ?

There are 4 types of privileges.

i .Global privileges like *.* (all hosts connecting to Mysql db server)

Ex: GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';

ii. Database privileges like .*

Ex: GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';

iii. Table privileges like SELECT, INSERT, UPDATE, DELETE

Ex: GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

iv. Column privileges like

Ex: GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';

13). How to find the version of MySQL ?

mysql> select version();

14). How do I limit the number of rows I get out of my database?

SELECT name FROM table LIMIT [, ] ;

if you want to get the rows between 10 and 20 do the following:
SELECT name FROM table LIMIT 10, 20 ;

15). Is it possible to insert multiple rows using single command in MySQL ?

Yes. Please see below example.

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9) ;

16). I am getting the following error while logging into “sras” database.

ERROR 1044 (42000): Access denied for user 'srini'@'localhost' to database 'sras'.

Please refer the error to DBA asking for granting the privilege to “sras” database.

mysql > grant all on sras.* to 'user_name' @ 'host_name' ;

17). What is null value in MySQL ?

In MySQL NULL is only equal to NULL, but NULL is not equal to ' ' ( blank value ) or 0(zero)

18). How can I check if a table in MySQL database already exists?

Command : SHOW TABLES LIKE '%';

Mysql > SHOW TABLES LIKE 'mytab%';

19). I am getting below error when application tries to execute any procedure say sras_purge etc.,

Caused by: java.sql.SQLException: User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types.

Solution : Remove the grants to particular user using following command:

delete from mysql.user where host like '%';

grant ALL on .* to ''@'' identified by '';

grant ALL on mysql.proc to ''@'' identified by '';

Eg:

delete from mysql.user where host like 'sustain-73%';

grant ALL on sras25_qa01.* to 'sras_app'@'sustain-73.central.sun.com' identified by 'sras_app';

grant ALL on mysql.proc to 'sras_app'@'sustain-73.central.sun.com' identified by 'sras_app';

20). Convert datetime from MST (db servers timezone) into GMT returns NULL value, how to solve it?

Solution: DB should be updated with timezone value from os otherwise Mysql will return null.

To load the timezone value from os into db

Command : mysql_tzinfo_to_sql /usr/share/lib/zoneinfo
mysql -u root mysql -p

eg. mysql_tzinfo_to_sql /usr/share/lib/zoneinfo
mysql -u root mysql -pmysql

21). Column alias not displaying for tables column but with function it is displaying for driver mysql-connector-java-5.1.6-bin.jar, how to solve it?

Solution: This is issue with this driver, so please use mysql-connector-java-5.0.7-bin.jar since SRAS DB version is Mysql 5.0.68 enterprise version
22). How to make MySQL database & table names are case in-sensitive?

Solution: Change lower_case_table_names=1 in my.cnf and restart the instance

23). How to make MySQL character data are case in-sensitive?

Solution: Recreated db with COLLATION=utf8_bin option instead of utf8_general_ci

Eg: CREATE DATABASE IF NOT EXISTS

CHARACTER SET utf8 COLLATE utf8_general_ci;

1 comments