SQL Joins

Codeproject has this image which explains the simple basics of SQL joins, taken from: http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

MySQL 5.6 added comprehensive password security mechanism how password and internally handled and encrypted. One major feature is auto encrypted password generation when mysql first time installed on your system. you can find the encrypted password in /root/.mysql_secret. Once you are into mysql using encrypted password you will get this error until unless root password is not set :

SET PASSWORD = PASSWORD('new_password');

Here is how the error looks like:

-bash-4.1$ mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.6.19-enterprise-commercial-advanced

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

Just reset the password and you should be able login on next attempt:

-bash-4.1$ mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.6.19-enterprise-commercial-advanced

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

mysql> SET PASSWORD = PASSWORD('newpassword');

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql>

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

-bash-4.1$ mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 9

Server version: 5.6.19-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

+--------------------+

4 rows in set (0.00 sec)

“The program can’t start because MSVCR100.dll is missing from your computer.” error on Windows

I was installing a software on windows 2008R2 64 bit edition and ran into this error.

"The program can't start because MSVCR100.dll is missing from your computer. Try reinstalling the program to fix this problem"

After doing some online research I figured the Microsoft Visual C++ Redistributable package requires installed prior installing my desired installation.

This Redistribution package is available on microsoft website. Here's the link for both x86 or x64 edition.

32 bit: http://www.microsoft.com/download/en/details.aspx?id=5555
64 bit: http://www.microsoft.com/download/en/details.aspx?id=14632

What's Your Favorite Feature of SQL Server 2012?

MySQL Import Error 1153 - Got a packet bigger than 'max_allowed_packet' bytes

Today I was importing a MySQL database and ran into this Error “Error 1153 - Got a packet bigger than 'max_allowed_packet' bytes”.

Basically , When you dump table data from MySQL, you may end up pulling a large chunk of data and it may exceed the MySQL client’s max_allowed_packet variable. If that happens, you might catch an error like this:

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `tablename` at row: 1627

The default max_allowed_packet size is 25M, and you can adjust it for good within your my.cnf by setting the variable in a section for mysqldump:

OR

The fix is to increase the MySQL daemon’s max_allowed_packet. You can do this to a running daemon by logging in as Super and running the following commands. Keeping the session open create a 2nd session in which to run the import.

mysql> set global net_buffer_length=1000000;
Query OK, 0 rows affected (0.00 sec)

mysql> set global max_allowed_packet=1000000000;
Query OK, 0 rows affected (0.00 sec)

Keep the mysql prompt open, and run your command-line SQL execution on a second terminal..

MySQL : Monitor live MySQL queries

You can run the mysql command "show processlist" to see what queries are being processed at any given time, but that probably won't achieve what you're hoping for as this won’t show the whole SQL text.

Here are the steps to dump sql into a file for analysis.

mysql> SHOW VARIABLES LIKE "general_log%";

+------------------+----------------------------+

| Variable_name    | Value                      |

+------------------+----------------------------+

| general_log      | OFF                        |

| general_log_file | /var/run/mysqld/mysqld.log |

+------------------+----------------------------+

Turn on the SQL Logging and go to the general_log_file location.

mysql> SET GLOBAL general_log = 'ON';

Do your queries (on any db). Grep or otherwise examine /var/run/mysqld/mysqld.log

Then don't forget to disable the general_log parameter to OFF.

mysql> SET GLOBAL general_log = 'OFF';

or the performance will plummet and your disk will fill!

MSSQL : Bring database back to normal from Restoring State

If you are restoring a database using multiple backup files, you would use the WITH NORECOVERY option for each restore except the last. If your database is still in the restoring state and you want to recover it without restoring additional backups you can issue a RESTORE DATABASE .. WITH RECOVERY to bring the database online for users to use.

Here is the syntax.

RESTORE DATABASE <DATABASE_NAME>

WITH RECOVERY

SQL Server: Linked Server is not configured for RPC error:

Workaround for calling table-valued function remotely in SQL Server over Linked Server needs some fancy in the coding. Something like this


EXEC SERVER_LINK.DATABASE_NAME..sp_executesql N'SELECT value FROM fn_testexecute(''value1'', value2, value3, value3, value4);'

Now what if the SQL fails due to below error:

Msg 7411, Level 16, State 1, Line 2
Server 'TEST_LINK' is not configured for RPC.

This is a configuration thing with the Linked Server option called "RPC OUT". This is necessary to execute a procedure calls to go out to the linked Server. Where as, "RPC" option allows stored procedure call from the linked Server.

For this case, I had to enable "RPC Out". I ran sp_helpserver and it reported RPC OUT isn’t enabled.

That's it. Problem solved.

MSSQL Server to MSSQL Server Instance connectivity with Linked Server

In order to create LINKED Server the user needs to have SYSADMIN privileges. Here in this demonstration I’m going to show the preferred LINKED SERVER option called “Be made using the login’s current security context”. Condition is, LOGIN needs to stay on both the servers. This is the preferred and recommended method in terms of security as other users can’t use this link except for the Login I’ll be using.


Here are the steps:

1. I’m going to use a user called TEST for this demonstration, and going to provide SYSADMIN privilege to this user. Once the link created and tested, I’ll revoke SYSADMIN privilege from the TEST user.

2. Login as TEST user

3. Open SSMS, Go to Server Objects-->Right Click Linked Servers--> New Linked Server. The below form pops up

4. Give a name for “Linked Server” , choose the provider as shown on form. Product name here I mentioned ‘*’ to specify any SQL Server product. On the Data source I’m mentioning the instance name from where I’d be pulling data. It’s “MSSQLTEST” for my case.

5. On the security tab select “Be made using the login’s current security context”. This would use my used Loging ie, “TEST” for this case.

6. Click OK, now you can browse through the objects using SSMS-->Linked Servers--> TEST_LINK-->Catalogs or you could use below syntax to fetch any table data

select * from [TEST_LINK].[TEST_DB].[dbo].[TEST_TABLE];
  7. Revoke “Sysadmin” privilege from “TEST” user

My “Linked Servers” has been created!

SQL Server - Maintenace plan integrity checks fail with "Alter Failed for server xxx"

My maintenance plan integrity check task was failing with "Alter failed for server XXX". 

After tracing i found the maintenance plan executes sp_configure 'USER OPTIONS',xxxx followed by RECONFIGURE. The reconfigure statement then generates an error causing the plan to fail. This was because the server configuration option "Allow Updates" was set to 1. Changed the setting back to 0 and the reconfigure statement started to work again!

Following command changes "Allow Updates" parameter to "0"

SP_CONFIGURE 'ALLOW UPDATES',0
GO
RECONFIGURE
GO 

SQL Server Performance Dashboard Reports in SSMS

The step by step procedure has been published on Microsoft MSDN blogs both for 2005 & 2008.
Couple of tweaks required for 2008, but everything is documented.
Here's the link.

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/sql-server-performance-dashboard-reports-in-ssms-introduction-install-datediff-error-amp-modified-for-sql-2008.aspx

This is how it looks like:

 

When did a user changed his password in Oracle?

The table sys.user$ contains the field ptime, which keeps the time when the password was changed the last time over. Do not confound it with ctime, which is the "creation time", nor with ltime, which is the time the account has been locked (if any).

SELECT NAME, ptime AS "LAST TIME CHANGED", ctime "CREATION TIME", ltime "LOCKED"

FROM USER$

WHERE ptime IS NOT NULL

ORDER BY ptime DESC;

Recover MySQL Root Password

If you already have the root password, but just want to change it. Then it's simple and can be 

done when MySQL Database is up & running.

/usr/bin/mysqladmin -u root password 'new-password’

But If ROOT password is lost then following steps needs to be performed and requires reboot of 

MySQL Database:

STOP MySQL Service:

/etc/init.d/mysql stop

START MySQL Without grants:

mysqld_safe --skip-grant-tables &

Connect to mysql server using mysql client:

mysql -u root

Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Stop MySQL Server:

# /etc/init.d/mysql stop

Start MySQL server and test it

# /etc/init.d/mysql start

# mysql -u root -p