I-Learnings

Monday, January 15, 2018

ORA-12519, TNS:no appropriate service handler found

Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

SQL> set lines 200

SQL> select * from v$resource_limit where resource_name = 'processes';

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE CON_ID

------------------------------ ------------------- --------------- ---------------------------------------- ---------------------------------------- ----------

processes 988 1000 1000 1000 0

SQL> select count(1) FROM v$session;

COUNT(1)

----------

969

SQL> select status,count(1) FROM v$session group by status;

STATUS COUNT(1)

-------- ----------

ACTIVE 46

INACTIVE 923

We have seen lots if INACTIVE sessions which we need to kill. After killing sessions that were INACTIVE for a very long time. We don't see the errors anymore after re-executing the deployment.

SQL> select status,count(1) FROM v$session group by status;

STATUS COUNT(1)

-------- ----------

ACTIVE 46

INACTIVE 310

SQL> select count(1) FROM v$session;

COUNT(1)

----------

356

SQL>

Vagrant was unable to mount VirtualBox shared folders

I got this error below while starting vagrant. I am dependent on using CLI in my work so i need to find a solution.

C:\>vagrant up
Bringing machine 'default' up with 'virtualbox' provider...
==> default: Checking if box 'bento/centos-7.3' is up to date...
==> default: Clearing any previously set forwarded ports...
==> default: Clearing any previously set network interfaces...
==> default: Preparing network interfaces based on configuration...
default: Adapter 1: nat
==> default: Forwarding ports...
default: 22 (guest) => 2222 (host) (adapter 1)
==> default: Booting VM...
==> default: Waiting for machine to boot. This may take a few minutes...
default: SSH address: 127.0.0.1:2222
default: SSH username: vagrant
default: SSH auth method: private key
default: Warning: Connection reset. Retrying...
default: Warning: Connection aborted. Retrying...
default: Warning: Connection reset. Retrying...
default: Warning: Connection aborted. Retrying...
default: Warning: Connection reset. Retrying...
default: Warning: Connection aborted. Retrying...
default: Warning: Connection reset. Retrying...
default: Warning: Connection aborted. Retrying...
default: Warning: Connection reset. Retrying...
default: Warning: Remote connection disconnect. Retrying...
default: Warning: Connection aborted. Retrying...
default: Warning: Connection reset. Retrying...
==> default: Machine booted and ready!
==> default: Checking for guest additions in VM...
default: No guest additions were detected on the base box for this VM! Gues

default: additions are required for forwarded ports, shared folders, host o
ly
default: networking, and more. If SSH fails on this machine, please install
default: the guest additions and repackage the box to continue.
default:
default: This is not an error message; everything may continue to work prop
rly,
default: in which case you may ignore this message.
==> default: Mounting shared folders...
default: /vagrant => C:/
Vagrant was unable to mount VirtualBox shared folders. This is usually
because the filesystem "vboxsf" is not available. This filesystem is
made available via the VirtualBox Guest Additions and kernel module.
Please verify that these guest additions are properly installed in the
guest. This is not a bug in Vagrant and is usually caused by a faulty
Vagrant box. For context, the command attempted was:

mount -t vboxsf -o uid=1000,gid=1000 vagrant /vagrant

The error output from the command was:

/sbin/mount.vboxsf: mounting failed with the error: No such device

Found some discussions in the stackoverflow. And i tried 1 solution below.

C:\>vagrant plugin install vagrant-vbguest
Installing the 'vagrant-vbguest' plugin. This can take a few minutes...
Fetching: vagrant-share-1.1.9.gem (100%)
Fetching: micromachine-2.0.0.gem (100%)
Fetching: vagrant-vbguest-0.15.1.gem (100%)
Installed the plugin 'vagrant-vbguest (0.15.1)'!

I tried to reinstall it again since i forgot to shut it down first before i install the vagrant-vbgust plugin.

C:\>vagrant halt
==> default: Attempting graceful shutdown of VM...

Since i was already installed in the first run it does not do anything.

C:\>vagrant plugin install vagrant-vbguest
Installing the 'vagrant-vbguest' plugin. This can take a few minutes...
Installed the plugin 'vagrant-vbguest (0.15.1)'!

Then i started the vagrant. After starting it the vagrant reinstalled some libraries.

C:\>vagrant up
Bringing machine 'default' up with 'virtualbox' provider...
==> default: Checking if box 'bento/centos-7.3' is up to date...
==> default: Clearing any previously set forwarded ports...
==> default: Clearing any previously set network interfaces...
==> default: Preparing network interfaces based on configuration...
default: Adapter 1: nat
==> default: Forwarding ports...
default: 22 (guest) => 2222 (host) (adapter 1)
==> default: Booting VM...
==> default: Waiting for machine to boot. This may take a few minutes...
default: SSH address: 127.0.0.1:2222
default: SSH username: vagrant
default: SSH auth method: private key
default: Warning: Connection reset. Retrying...
default: Warning: Connection aborted. Retrying...
default: Warning: Connection reset. Retrying...
default: Warning: Connection aborted. Retrying...
default: Warning: Connection reset. Retrying...
default: Warning: Connection aborted. Retrying...
default: Warning: Remote connection disconnect. Retrying...
default: Warning: Connection reset. Retrying...
default: Warning: Connection aborted. Retrying...
default: Warning: Connection reset. Retrying...
==> default: Machine booted and ready!
[default] No installation found.
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: ftp.tsukuba.wide.ad.jp
* extras: ftp.tsukuba.wide.ad.jp
* updates: ftp.tsukuba.wide.ad.jp
Package binutils-2.25.1-32.base.el7_4.1.x86_64 already installed and latest vers
ion
Package 1:make-3.82-23.el7.x86_64 already installed and latest version
Package 4:perl-5.16.3-292.el7.x86_64 already installed and latest version
Package bzip2-1.0.6-13.el7.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package gcc.x86_64 0:4.8.5-16.el7_4.1 will be installed
--> Processing Dependency: cpp = 4.8.5-16.el7_4.1 for package: gcc-4.8.5-16.el7_
4.1.x86_64
--> Processing Dependency: glibc-devel >= 2.2.90-12 for package: gcc-4.8.5-16.el
7_4.1.x86_64
---> Package kernel-devel.x86_64 0:3.10.0-693.11.6.el7 will be installed
--> Running transaction check
---> Package cpp.x86_64 0:4.8.5-16.el7_4.1 will be installed
---> Package glibc-devel.x86_64 0:2.17-196.el7_4.2 will be installed
--> Processing Dependency: glibc-headers = 2.17-196.el7_4.2 for package: glibc-d
evel-2.17-196.el7_4.2.x86_64
--> Processing Dependency: glibc-headers for package: glibc-devel-2.17-196.el7_4
.2.x86_64
--> Running transaction check
---> Package glibc-headers.x86_64 0:2.17-196.el7_4.2 will be installed
--> Processing Dependency: kernel-headers >= 2.2.1 for package: glibc-headers-2.
17-196.el7_4.2.x86_64
--> Processing Dependency: kernel-headers for package: glibc-headers-2.17-196.el
7_4.2.x86_64
--> Running transaction check
---> Package kernel-headers.x86_64 0:3.10.0-693.11.6.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
gcc x86_64 4.8.5-16.el7_4.1 updates 16 M
kernel-devel x86_64 3.10.0-693.11.6.el7 updates 14 M
Installing for dependencies:
cpp x86_64 4.8.5-16.el7_4.1 updates 5.9 M
glibc-devel x86_64 2.17-196.el7_4.2 updates 1.1 M
glibc-headers x86_64 2.17-196.el7_4.2 updates 676 k
kernel-headers x86_64 3.10.0-693.11.6.el7 updates 6.0 M
Transaction Summary
================================================================================
Install 2 Packages (+4 Dependent packages)
Total download size: 44 M
Installed size: 95 M
Downloading packages:
Delta RPMs disabled because /usr/bin/applydeltarpm not installed.
--------------------------------------------------------------------------------
Total 2.8 MB/s | 44 MB 00:15
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : kernel-headers-3.10.0-693.11.6.el7.x86_64 1/6
Installing : glibc-headers-2.17-196.el7_4.2.x86_64 2/6
Installing : glibc-devel-2.17-196.el7_4.2.x86_64 3/6
Installing : cpp-4.8.5-16.el7_4.1.x86_64 4/6
Installing : gcc-4.8.5-16.el7_4.1.x86_64 5/6
Installing : kernel-devel-3.10.0-693.11.6.el7.x86_64 6/6
Verifying : cpp-4.8.5-16.el7_4.1.x86_64 1/6
Verifying : glibc-devel-2.17-196.el7_4.2.x86_64 2/6
Verifying : kernel-devel-3.10.0-693.11.6.el7.x86_64 3/6
Verifying : glibc-headers-2.17-196.el7_4.2.x86_64 4/6
Verifying : gcc-4.8.5-16.el7_4.1.x86_64 5/6
Verifying : kernel-headers-3.10.0-693.11.6.el7.x86_64 6/6

Installed:
gcc.x86_64 0:4.8.5-16.el7_4.1 kernel-devel.x86_64 0:3.10.0-693.11.6.el7
Dependency Installed:
cpp.x86_64 0:4.8.5-16.el7_4.1
glibc-devel.x86_64 0:2.17-196.el7_4.2
glibc-headers.x86_64 0:2.17-196.el7_4.2
kernel-headers.x86_64 0:3.10.0-693.11.6.el7

Complete!
Copy iso file C:\Program Files\Oracle\VirtualBox\VBoxGuestAdditions.iso into the
box /tmp/VBoxGuestAdditions.iso
Mounting Virtualbox Guest Additions ISO to: /mnt
mount: /dev/loop0 is write-protected, mounting read-only
Installing Virtualbox Guest Additions 5.1.28 - guest version is unknown
Verifying archive integrity... All good.
Uncompressing VirtualBox 5.1.28 Guest Additions for Linux...........
VirtualBox Guest Additions installer
Removing installed version 5.1.26 of VirtualBox Guest Additions...
Copying additional installer modules ...
Installing additional modules ...
vboxadd.sh: Starting the VirtualBox Guest Additions.
Could not find the X.Org or XFree86 Window System, skipping.
Redirecting to /bin/systemctl start vboxadd.service
Redirecting to /bin/systemctl start vboxadd-service.service
Unmounting Virtualbox Guest Additions ISO from: /mnt
==> default: Checking for guest additions in VM...
==> default: Mounting shared folders...
default: /vagrant => C:/
==> default: Machine already provisioned. Run `vagrant provision` or use the `--
provision`
==> default: flag to force provisioning. Provisioners marked to run always will
still run.

After everything was done tried to access vagrant via PUTTY and now the my local drive is now accessible via vagrant. How cool is that!!!!!

Just another issue while browsing my local directory via vagrant. I throws an error while browsing some Windows default directory. I am not sure why but the important thing is i am able to access my Work directory.

[root@localhost vagrant]# ls -ltr

ls: cannot read symbolic link Documents and Settings: Protocol error

total 14211895

-rwxrwxrwx. 1 vagrant vagrant 5686 Nov 7 2007 vcredist.bmp

Friday, February 14, 2014

Innotop on Centos 6.5 MySQL version 5.5.34

innotop - MySQL and InnoDB transaction/status monitor.

INSTALLATION

wget https://innotop.googlecode.com/files/innotop-1.9.1-1.el6.noarch.rpm
rpm -ivh innotop-1.9.1-1.el6.noarch.rpm

Posible errros:

error: Failed dependencies:
perl(Term::ReadKey) is needed by innotop-1.9.1-1.el6.noarch
perl(Time::HiRes) is needed by innotop-1.9.1-1.el6.noarch
perl-TermReadKey is needed by innotop-1.9.1-1.el6.noarch
perl-Time-HiRes is needed by innotop-1.9.1-1.el6.noarch

more info about innotop: http://innotop.googlecode.com/svn/html/manual.html

[root@mercury tmp]# yum install perl-TermReadKey
Loaded plugins: fastestmirror, refresh-packagekit, security
Loading mirror speeds from cached hostfile
* base: centos.mirrors.hoobly.com
* extras: mirrors.xmission.com
* updates: centos.eecs.wsu.edu
* webtatic: us-east.repo.webtatic.com
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package perl-TermReadKey.x86_64 0:2.30-13.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================
Package Arch Version Repository Size
==============================================================================================================================================
Installing:
perl-TermReadKey x86_64 2.30-13.el6 base 31 k

Transaction Summary
==============================================================================================================================================
Install 1 Package(s)

Total download size: 31 k
Installed size: 62 k
Is this ok [y/N]: y
Downloading Packages:
perl-TermReadKey-2.30-13.el6.x86_64.rpm | 31 kB 00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : perl-TermReadKey-2.30-13.el6.x86_64 1/1
Verifying : perl-TermReadKey-2.30-13.el6.x86_64 1/1

Installed:
perl-TermReadKey.x86_64 0:2.30-13.el6

Complete!
[root@mercury tmp]# yum install perl-Time-HiRes
Loaded plugins: fastestmirror, refresh-packagekit, security
Loading mirror speeds from cached hostfile
* base: centos.mirrors.hoobly.com
* extras: mirrors.xmission.com
* updates: centos.eecs.wsu.edu
* webtatic: us-east.repo.webtatic.com
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package perl-Time-HiRes.x86_64 4:1.9721-136.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================
Package Arch Version Repository Size
==============================================================================================================================================
Installing:
perl-Time-HiRes x86_64 4:1.9721-136.el6 base 48 k

Transaction Summary
==============================================================================================================================================
Install 1 Package(s)

Total download size: 48 k
Installed size: 56 k
Is this ok [y/N]: y
Downloading Packages:
perl-Time-HiRes-1.9721-136.el6.x86_64.rpm | 48 kB 00:01
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : 4:perl-Time-HiRes-1.9721-136.el6.x86_64 1/1
Verifying : 4:perl-Time-HiRes-1.9721-136.el6.x86_64 1/1

Installed:
perl-Time-HiRes.x86_64 4:1.9721-136.el6

Complete!

[root@mercury tmp]# rpm -ivh innotop-1.9.1-1.el6.noarch.rpm
Preparing... ########################################### [100%]
1:innotop ########################################### [100%]

Mytop on Centos 6.5 Mysql Version : 5.5.34

Mytop

mytop is a console-based (non-gui) tool for monitoring the threads
and overall performance of a MySQL server.
It runs on most Unix systems which have Perl,
DBI, and Term::ReadKey installed. And with Term::ANSIColor installed
you even get color.

If you install Time::HiRes, you’ll get good real-time queries/second stats.

As of version 0.7, it even run

INSTALLATIONS

Download Package: http://jeremy.zawodny.com/mysql/mytop/
tar -zxvf mytop-.tar.gz
cd mytop-
perl Makefile.PL
make
make install

Possible Errors during installation:

Warning: prerequisite DBD::mysql 1 not found.

Warning: prerequisite DBI 1.13 not found.

Warning: prerequisite Term::ReadKey 2.1 not found.

Solution:

perl-Class-Data-Inheritable-0.08-1.el5.rf.noarch.rpm

perl-DBD-MySQL-4.014-1.el5.rfx.x86_64.rpm

perl-DBI-1.616-1.el5.rfx.x86_64.rpm

perl-DBIx-ContextualFetch-1.03-1.el5.rf.noarch.rpm

perl-Ima-DBI-0.35-1.el5.noarch.rpm

perl-Ima-DBI-0.35-1.el5.src.rpm

perl-TermReadKey-2.30-4.el5.

Error after a successful installation:

Use of uninitialized value $host in substitution (s///) at /usr/local/bin/mytop line 958.

Solution:

Remove this line

$host =~ s/^([^.]+).*/$1/;

$thread->{Host} = $host;

Replace with this

if ($host)

{

$host =~ s/^([^.]+).*/$1/;

$thread->{Host} = $host;

}

Error in option spec: "long|!"

Solution:

The fix for this is to edit the mytop script and comment out the line containing long|!

vi /usr/local/bin/mytop

Run a search for long|! and comment out the following line:

"long|!" => \$config{long_nums},

Start mytop and you should be fine.

Sources:

http://www.eth0.us/node/204

http://www.ashishnepal.com/mytop-centos-5-6-mysql-version-5-5-16/

Thursday, February 13, 2014

MySQL 5.5 Installation on Centos/RHEL 6.5 via Yum

I've been using RPM every time i will install MySQL server in my Linux boxes. But today i will show you on how to install MySQL 5.5 on Centos 6.5 via Yum.

First, to set up the yum repository, install the webtatic-release RPM based on your CentOS/RHEL release.

[root@localhost ~]# rpm -Uvh http://mirror.webtatic.com/yum/el6/latest.rpm
Retrieving http://mirror.webtatic.com/yum/el6/latest.rpm
warning: /var/tmp/rpm-tmp.q3Y9pR: Header V4 DSA/SHA1 Signature, key ID cf4c4ff9: NOKEY
Preparing... ########################################### [100%]

1:webtatic-release ########################################### [100%]

Remove the pre-installed mysql-libs with no dependecy.

[root@localhost ~]# rpm -e mysql-libs

error: Failed dependencies:

libmysqlclient.so.16()(64bit) is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64

libmysqlclient.so.16(libmysqlclient_16)(64bit) is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64

mysql-libs is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64

[root@localhost ~]# rpm -e --nodeps mysql-libs

After removing the previous version of mysql-libs you can now start the installation of MYSQL via Yum

[root@localhost ~]# yum install mysql55 mysql55-server

Loaded plugins: fastestmirror, refresh-packagekit, security

Loading mirror speeds from cached hostfile

 * base: centos.corenetworks.net

 * extras: centos.mirror.freedomvoice.com

 * updates: mirrors.usinternet.com

 * webtatic: sp.repo.webtatic.com

Setting up Install Process

Resolving Dependencies

--> Running transaction check

---> Package mysql55.x86_64 0:5.5.34-1.w6 will be installed

--> Processing Dependency: mysql55-libs = 5.5.34-1.w6 for package: mysql55-5.5.34-1.w6.x86_64

---> Package mysql55-server.x86_64 0:5.5.34-1.w6 will be installed

--> Processing Dependency: perl-DBI for package: mysql55-server-5.5.34-1.w6.x86_64

--> Processing Dependency: perl-DBD-MySQL for package: mysql55-server-5.5.34-1.w6.x86_64

--> Processing Dependency: perl(DBI) for package: mysql55-server-5.5.34-1.w6.x86_64

--> Running transaction check

---> Package mysql55-libs.x86_64 0:5.5.34-1.w6 will be installed

--> Processing Dependency: libmysqlclient16 for package: mysql55-libs-5.5.34-1.w6.x86_64

---> Package perl-DBD-MySQL.x86_64 0:4.013-3.el6 will be installed

---> Package perl-DBI.x86_64 0:1.609-4.el6 will be installed

--> Running transaction check

---> Package libmysqlclient16.x86_64 0:5.1.69-1.w6 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

==================================================================================================================================================

 Package                                 Arch                          Version                              Repository                       Size

==================================================================================================================================================

Installing:

 mysql55                                 x86_64                        5.5.34-1.w6                          webtatic                        5.5 M

 mysql55-server                          x86_64                        5.5.34-1.w6                          webtatic                         10 M

Installing for dependencies:

 libmysqlclient16                        x86_64                        5.1.69-1.w6                          webtatic                        1.0 M

 mysql55-libs                            x86_64                        5.5.34-1.w6                          webtatic                        778 k

 perl-DBD-MySQL                          x86_64                        4.013-3.el6                          base                            134 k

 perl-DBI                                x86_64                        1.609-4.el6                          base                            705 k

Transaction Summary

==================================================================================================================================================

Install       6 Package(s)

Total download size: 18 M

Installed size: 82 M

Is this ok [y/N]: y

Downloading Packages:

(1/6): libmysqlclient16-5.1.69-1.w6.x86_64.rpm                                                                             | 1.0 MB     00:06

(2/6): mysql55-5.5.34-1.w6.x86_64.rpm                                                                                      | 5.5 MB     00:45

(3/6): mysql55-libs-5.5.34-1.w6.x86_64.rpm                                                                                 | 778 kB     00:05

(4/6): mysql55-server-5.5.34-1.w6.x86_64.rpm                                                                               |  10 MB     01:13

(5/6): perl-DBD-MySQL-4.013-3.el6.x86_64.rpm                                                                               | 134 kB     00:02

(6/6): perl-DBI-1.609-4.el6.x86_64.rpm                                                                                     | 705 kB     00:06

--------------------------------------------------------------------------------------------------------------------------------------------------

Total                                                                                                             130 kB/s |  18 MB     02:22

warning: rpmts_HdrFromFdno: Header V4 DSA/SHA1 Signature, key ID cf4c4ff9: NOKEY

Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-webtatic-andy

Importing GPG key 0xCF4C4FF9:

 Userid : Andy Thompson 

 Package: webtatic-release-6-4.noarch (installed)

 From   : /etc/pki/rpm-gpg/RPM-GPG-KEY-webtatic-andy

Is this ok [y/N]: y

warning: rpmts_HdrFromFdno: Header V3 RSA/SHA256 Signature, key ID c105b9de: NOKEY

Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6

Importing GPG key 0xC105B9DE:

 Userid : CentOS-6 Key (CentOS 6 Official Signing Key) 

 Package: centos-release-6-5.el6.centos.11.1.x86_64 (@anaconda-CentOS-201311272149.x86_64/6.5)

 From   : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6

Is this ok [y/N]: y

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

  Installing : perl-DBI-1.609-4.el6.x86_64                                                                                                    1/6

  Installing : libmysqlclient16-5.1.69-1.w6.x86_64                                                                                            2/6

  Installing : mysql55-libs-5.5.34-1.w6.x86_64                                                                                                3/6

  Installing : mysql55-5.5.34-1.w6.x86_64                                                                                                     4/6

  Installing : perl-DBD-MySQL-4.013-3.el6.x86_64                                                                                              5/6

  Installing : mysql55-server-5.5.34-1.w6.x86_64                                                                                              6/6

  Verifying  : libmysqlclient16-5.1.69-1.w6.x86_64                                                                                            1/6

  Verifying  : perl-DBD-MySQL-4.013-3.el6.x86_64                                                                                              2/6

  Verifying  : mysql55-libs-5.5.34-1.w6.x86_64                                                                                                3/6

  Verifying  : perl-DBI-1.609-4.el6.x86_64                                                                                                    4/6

  Verifying  : mysql55-5.5.34-1.w6.x86_64                                                                                                     5/6

  Verifying  : mysql55-server-5.5.34-1.w6.x86_64                                                                                              6/6

Installed:

  mysql55.x86_64 0:5.5.34-1.w6                                         mysql55-server.x86_64 0:5.5.34-1.w6

Dependency Installed:

  libmysqlclient16.x86_64 0:5.1.69-1.w6  mysql55-libs.x86_64 0:5.5.34-1.w6  perl-DBD-MySQL.x86_64 0:4.013-3.el6  perl-DBI.x86_64 0:1.609-4.el6

Complete!

After installing, the commands needed to activate it to run at system startup depend on your operating system. On Linux, you can use chkconfig:

[root@localhost ~]# chkconfig --add mysqld

On some Linux systems, the following command also seems to be necessary to fully enable the mysql script:

[root@localhost ~]# chkconfig --level 345 mysqld on

You can now start MySQL as a service.

[root@localhost bin]# service mysqld start
Starting mysqld: [ OK ]
[root@localhost bin]# ps ax | grep mysql
26597 pts/2 S 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
26804 pts/2 Sl 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
26836 pts/2 S+ 0:00 grep mysql
[root@localhost bin]#

Source: http://webtatic.com/packages/mysql55/

Tuesday, December 11, 2012

MySQL: Localhost vs 127.0.0.1

Yesterday i was trying to connect to MySQL localhost with another port aside from 3306:

mysql -uroot -p -h localhost --port=3390

to my suprise i was login to the default port. My collegue told me that i need to used 127.0.0.1 instead of localhost so i tried it then i search everywhere to find the answer on why oh why its like this until i found a blog that explained the difference between localhost and 127.0.0.1.

source: http://blogs.sakienvirotech.com/index.php/random/2011/08/30/mysql-101-connecting-to-a

With network connections, there are two special addresses that always refer to the machine you are currently using. This is either called 'localhost' or has the IP address of 127.0.0.1. In general using these is equivalent and interchangeable, and uses a special path through the networking subsystem called the local loopback interface. However MySQL has reserved 'localhost' for the special purpose of indicating the use of a UNIX socket and not use networking. This can cause some confusion to new users, but if you understand what is going on it is pretty clear.

'localhost' in MySQL uses the UNIX socket and ignores any port you may supply.

127.0.0.1 in MySQL uses networking and requires a port, using the default if you don't supply it.

Tuesday, November 20, 2012

ERROR 1018 (HY000): Can't read dir of '.' (errno: 24)

We encountered an error in MySQl below:

ERROR 1018 (HY000): Can't read dir of '.' (errno: 24)

Upon searching for some solutions at google we found out that the error means 'Too many open files'. Everything you do in linux is a file (or rather a file descriptor). Every mysql connection, every apache connection etc.

To see how many files a user has open try this

sudo lsof -u mysql

edit the file in /etc/security/limits.conf and add these two mysql lines in at the bottom



#@student        -       maxlogins       4
mysql   soft    nofile  4096
mysql   hard    nofile  8192
# End of file









There is some debate as to whether you need to reboot for this to 
take effect (i say debate, i rebooted, and didnt google for it as i was 
in a rush ymmv).


Once this is set you need to make some changes in the mysql file as 
well.  Assuming yours lives in /etc/mysql/my.cnf - edit or add these 
values





open-files-limit = 2048
table_cache            = 512



The table_cache should be researched (start here: http://dev.mysql.com/doc/refman/5.0/en/table-cache.html) and changes monitored.  The default value is 64, so 512 should be a safe bet to begin with.