Friday, July 08, 2016

Wednesday, August 06, 2014

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)


Tuesday, October 22, 2013

“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

Friday, August 23, 2013

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..


Wednesday, July 31, 2013

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!

Friday, July 26, 2013

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