Archive for the ‘Mysql’ category

How to insert data in MySQL Table

September 28th, 2011

Let’s start inserting data in the table created in our post, table name was employee, we also discussed how to check the table structure in one of our post

Step 1) Login to MySQL

we will be using user : demo and password as demouser

mysql -u demo -p demouser

Step 2) use database in which we have created table

mysql> use demo;

Step 3) check the structure of the database

mysql> desc employee;
+————–+————–+——+—–+———+——-+
| Field        | Type         | Null | Key | Default | Extra |
+————–+————–+——+—–+———+——-+
| id           | int(11)      | YES  |     | NULL    |       |
| employeename | varchar(252) | YES  |     | NULL    |       |
| address      | varchar(256) | YES  |     | NULL    |       |
+————–+————–+——+—–+———+——-+
3 rows in set (0.07 sec)

so this tells us that the table has 3 columns id , employeename  and address , lets start by writing the insert statement  syntax used will be

Insert into employee(id ,employeename , address) values ( 1, ‘DemoEmployee’, ‘US’) ;

mysql> Insert into employee(id ,employeename , address) values ( 1, ‘DemoEmployee’, ‘US’) ;

Query OK, 1 row affected (0.02 sec)

Lets check if the record is been inserted properly.

mysql> select * from employee;

+——+————–+———+

| id   | employeename | address |

+——+————–+———+

|    1 | DemoEmployee | US      |

+——+————–+———+

1 row in set (0.00 sec)

Excellent we have successfully inserted out first record.

How to get list of tables in a MySQL database

September 28th, 2011

lets discuss now  how we can check  how many tables exist in a MySQL database .

Step 1) Login to MySQL

we will be using user : demo and password as demouser

mysql -u demo -p demouser

Step 2) use database in which we need to check table , as in our previous post we created database demo we will be using same database

mysql> use demo;

Step 3) we will use command show tables ;

+—————-+
| Tables_in_demo |
+—————-+
| employee       |
+—————-+
1 row in set (0.00 sec)

this will list all the tables which are under demo database.

In our next post we will discuss how to insert data in a MySQL Table.

How to Check table structure in MySQL

September 28th, 2011

in our previous  post we discussed how to  add more columns in MySQL table , lets see how we can check table structure in MySQL.

Step 1) Login to MySQL

we will be using user : demo and password as demouser

mysql -u demo -p demouser

Step 2) use database in which we need to check table , as in our previous post we created database demo we will be using same database

mysql> use demo;

Step 3) we have 2 methods of checking the table structure.

a) Using Desc , we will now check the table structure of table employee

mysql> desc employee;
+————–+————–+——+—–+———+——-+
| Field        | Type         | Null | Key | Default | Extra |
+————–+————–+——+—–+———+——-+
| id           | int(11)      | YES  |     | NULL    |       |
| employeename | varchar(252) | YES  |     | NULL    |       |
| address      | varchar(256) | YES  |     | NULL    |       |
+————–+————–+——+—–+———+——-+
3 rows in set (0.07 sec)

This command lists all the columns and data type used for each column.

b) Using show create table command

syntax : show create table tablename

mysql> show create table employee;

+———-+———————————————————————————————————————————————————————————+

| Table    | Create Table                                                                                                                                                                    |

+———-+———————————————————————————————————————————————————————————+

| employee | CREATE TABLE `employee` (

`id` int(11) default NULL,

`employeename` varchar(252) default NULL,

`address` varchar(256) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

+———-+———————————————————————————————————————————————————————————+

1 row in set (0.00 sec)

This command gives us create table structure for table employee.

In next post we will discuss how to check how many tables exists in a database.

How to Create a Database in MySQL

September 27th, 2011

Once MySQL Server setup is done , lets start by creating a database , in our example we will see how to create a MySQL Database

Step 1) Login to MySQL

we will be using user : demo and password as demouser

mysql -u demo -p demouser

Step 2) Now we will be creating a database demo
mysql> create database demo;
Query OK, 1 row affected (0.00 sec)

Step 3) Checking if the database is created ;

mysql>show databases;

+——————–+
| Database           |
+——————–+
| information_schema |
| demo                                |
| test                                    |
+——————–+
3 rows in set (0.00 sec)

we can see that the database demo is there in the list . in next post we will see how to create tables in a MySQL.

How to create table in MySQL

September 27th, 2011

In Our previous post we discussed how to create a database in MySQL , lets see how to create tables in MySQL database.

Step 1) Login to MySQL

we will be using user : demo and password as demouser

mysql -u demo -p demouser

Step 2) use database in which we need to create table , as in our previous post we created database demo we will be using same database

mysql> use demo;

Step 3)  Create table  lets assume our table will be called employee and it will have 2 columns  id and employeename we are starting with very basic table structure and then will be adding more columns to it.

mysql> create table employee (  id int , employeename varchar(252) );
Query OK, 0 rows affected (0.01 sec)

step 4) Lets check that if table is created.

mysql> show tables;
+—————-+
| Tables_in_demo |
+—————-+
| employee       |
+—————-+
1 row in set (0.00 sec)

so gr8 we are able to create our first table. in our next post we will discuss how to check the table structure and alter the table structure.

How to add more columns in exsiting MySQL table

September 27th, 2011

in our previous  post we discussed how to create table in MySQL Database , lets check now how we can add more columns to existing table .

Step 1) Login to MySQL

we will be using user : demo and password as demouser

mysql -u demo -p demouser

Step 2) use database in which we need to create table , as in our previous post we created database demo we will be using same database

mysql> use demo;

Step 3)  we created table employee , now we will be looking how to add more columns in existing table .lets add one more column address having data type varchar and length as 256

mysql> alter table employee add column address varchar(256) ;

Query OK, 0 rows affected (0.07 sec)

Records: 0  Duplicates: 0  Warnings: 0

here we go we can see that the table is being altered . in our next post we will be discussing how to check the  existing table structure.

Creating a UTF-8 database

September 20th, 2011

1. You can create a UTF-8 database with binary UTF-8 collation.
CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_bin;

2. You will also need to set the Server Characterset to utf8. This can be done by adding the following in my.ini for Windows or my.cnf for other OS like linux. It has to be declared in the Server section, which is the section after [mysqld]:

[mysqld]
default-character-set=utf8

3. Select the database using below command
use database name

4. Use the status command to verify database character encoding information.

mysql> status;
————–
mysql  Ver 14.12 Distrib 5.0.85, for pc-linux-gnu (i686) using readline 5.1

Connection id:          2464150
Current database:       databasename
Current user:           manoj@192.168.200.83
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ”
Using delimiter:        ;
Server version:         5.5.8-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             192.168.2.85 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 15 days 1 hour 10 min 31 sec

Threads: 103  Questions: 539733512  Slow queries: 32331  Opens: 354  Flush tables: 1  Open tables: 237  Queries per second avg: 415.105

5. In some cases, the individual tables collation and character encoding may differ from the one that the database as a whole has been configured to use. Please use the command below to ensure all tables within your manoj database are correctly configured to use UTF-8 character encoding and binary UTF-8 collation:

use manoj;
show table status;

Check for the value listed under the Collation column, to ensure it has been set to utf8_bin (that is, case-sensitive) collation for all tables.
If not, then this can be changed by the following command, executed for each table in the manoj database:

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

Please substitute the <tablename> above, with each table within the manoj database.

6. How to create database for utf8_bin
CREATE DATABASE Manoj CHARACTER SET utf8 COLLATE utf8_bin;

Thanks
Manoj

Not able to start Mysql after enabling innodb

June 3rd, 2011

I upgraded Mysql 5.0 to Mysql 5.5.8 and start mysql. It was working fine but when i enable innodb and try to start Mysql i was not able to start it.

I checked the Mysql error logs, saw below errors

[root@manoj mysql]# tail /var/log/mysqld.log
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 1073741824 bytes!
110603  7:34:15 [ERROR] Plugin ‘InnoDB’ init function returned error.
110603  7:34:15 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
110603  7:34:15 [ERROR] Unknown/unsupported storage engine: InnoDB
110603  7:34:15 [ERROR] Aborting

10603  7:34:15 [Note] /usr/sbin/mysqld: Shutdown complete

I moved the ib_logfile0 and ib_logfile01 to bak and start Mysql again. Now this time, it is working fine

[root@Manoj mysql]# mv ib_logfile0 ib_logfile0-bak
[root@Manoj mysql]# mv ib_logfile1 ib_logfile1-bak

Thanks
Manoj

SMTP authentication using Mysql

March 10th, 2011

There are times when you need to have users authenticate their SMTP sessions. Perhaps you have roaming users and you don’t want to be an open relay, but you cannot predict where these users are. You need a way for them to say to your SMTP server “hey I belong here, let me send email”.

One way to do is is using SMTP Authentication. The user’s username and password are sent to the SMTP server. The server then checks the pair is correct and lets the user then send mail (or not if they are incorrect). SMTP Authentication is defined in RFC 2554.

Postfix has a method of authentication, but it is tied up with SASL. For file-based authentication you just create a special password database. However for other types you cannot simply make a LDAP or MySQL table and be done with it. You can either use SASL natively or do it the way I have implemented it here where Postfix uses SASL which uses PAM which uses MySQL; around-about way but it does work. There is some sporadic documentation about this around The Internet, but I wrote this up in the hope you find it useful and so I don’t have to remember it or relearn it all over again.

You might also be able to adapt this method to use other sorts of PAM authentication. For example I’m pretty sure this method with a little adaption would also work for LDAP authentication. Obviously you could use other databases other than MySQL, its just what I was using here.
Required Packages

The following Debian packages are required to get this all working. I’m using debian Sarge here but for the most part it should work for other versions and dists with some small changes. Some other packages will be needed, but will be pulled in as dependencies.

postfix-tls 2.1.5-9
The main postfix server with TLS and SASL support.
libsasl2-modules 2.1.19-1.5
Modules that provide the LOGIN,PLAIN, ANONYMOUS, OTP, CRAM-MD5, and DIGEST-MD5 (with DES support) authentication methods.
libpam-mysql 0.4.7-1
PAM module to query a MySQL database – only for MySQL authentication.
metamail
Useful for base64 encoding and decoding using mimencode.

You have to make sure that either one or both of the authentication modules packages are installed. If you don’t and you setup Postfix to use SASL (see below) then the stupid process will be throttled. For older distributions you may need the libsasl (no 2) packages.
Postfix and MySQL socket problem

Postfix runs the smtpd daemon in a chrooted environment, usually something like /var/spool/postfix. That means that as far as the smtpd process is concerned you have nothing above that point. MySQL has a socket sitting in another directory, something like /var/run/mysql/mysqld.sock. The problem is that the socket sits in an area that smtpd believes doesn’t exist and cannot get to anyway because of the chroot.

To get around this problem, you have 3 options: 1. Stop smtpd from running into a chroot. 2. Move the mysql socket into the chroot. 3. Don’t use the mysql socket, use a TCP socket instead.

The last two are reasonably simple, possibly the third is the best option (you can make mysqld listen only to the loopback interface). Look at the MySQL documentation about how to move sockets or make it listen on its TCP port.
Stopping smtpd from being in a chroot

This had me going for a long, long time. To change this, edit /etc/postfix/master.cf and change the following line:

smtp inet n – n – – smtpd

The second ‘n’ means it is not chrooted. There may be a way of running smtpd in a chroot with the SASL and MySQL authentication but I’m not sure how.
Postfix Changes

The following lines are added to /etc/postfix/main.cf

smtpd_sasl_auth_enable = yes
smtpd_sasl_local_domain = myserver
broken_sasl_auth_clients = yes
smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject

SASL Files Setup

So far the postfix server knows it has to use SASL if it gets an authentication request. The default way for SASL to work out if you are authenticated is for it to examine a Berkley DB file called /etc/sasldb2. You can add and change users using the saslpasswd2 program.

The problem here is if you run smtpd in a chroot environment then it will not find the sasldb file. If you try to authenticate postfix will give an error “warning: SASL authentication problem: unable to open Berkeley db /etc/sasldb2: No such file or directory”. The problem here is that you have a /etc/sasldb2 file, but postfix is looking for a /var/spool/postfix/etc/sasldb2 file.

The two solutions for this problem are to either not run postfix in a chroot environment (see a previous section on how to stop it) or get that sasldb2 file into the correct directory. You can put it right by copying it. You will also need to make sure the user that smtpd runs as can read the file.

Debian users can automatically get this file updated by editing /etc/init.d/postfix. Around line 43 there is a list of files that are copied from their real directories into the chroot. Change the line so it looks like:

FILES=”etc/localtime etc/services etc/resolv.conf etc/hosts \
etc/nsswitch.conf etc/sasldb2″

Now when postfix is restarted you have the new sasldb2 ready to go.

If you are doing file-based authentication then you are done, drop down to the Testing section.
MySQL SASL Setup

For MySQL authentication, the next step is to get SASL to ask PAM to authenticate the user. There’s some confusion because the location of this file has moved around. On my system with the versions of the packages given above, it is found at /etc/postfix/sasl/smtpd.conf but it also has been found in /usr/local/lib/sasl/smtpd.conf and /usr/lib/sasl/smtp.conf. The file is real simple one-liner:

pwcheck_method: pam

That’s it for SASL, it will then use standard PAM as we all know and love for authenticating.
PAM Setup

The PAM setup is pretty standard. All you need to know is the PAM service is called smtp, so you need to create a file /etc/pam.d/smtp. SASL only uses the authentication management group.

It might be useful to test how things are going so far. To do this, and only for testing, you can use the pam_permit module. This module permits anything you send, so its useful for testing or for some strange circumstances, but shouldn’t be used in a production environment. The file /etc/pam.d/smtp would then look like:

auth required pam_permit.so

If you are going to run it with MySQL, use a configuration similar to that shown below. The configuration is similar to a user doing the following:

server$ mysql -u postfix -psecret postfixdb
mysql> SELECT id FROM users WHERE id=’givenusername’ AND password=’givenpassword’;

auth required pam_mysql.so user=postfix passwd=secret db=postfixdb table=users usercolumn=id passwdcolumn=password crypt=0

The table users has two columns. The first is called id and has the username, the second is password it has the unencrypted password in it. A select is made checking both username and password. If there is a single row returned, authentication is successful.
Testing

I use the plain authentication method for testing. To do this you need to convert the username and password into a base64 encoded string. For example, if you have username user and password pass, you would type:

server$ printf ‘useruserpass’ | mimencode
dXNlcgB1c2VyAHBhc3M=

So the string is the username and password joined together with between them. The username is needed twice. To test it, telnet to the SMTP port of your server and type the auth commands.

server$ telnet mail.my.server 25
Trying 10.1.2.3
Connected to 10.1.2.3.
Escape character is ‘^]’.
220 mail.my.server ESMTP Postfix
EHLO blah
250-mail.my.server
250-PIPELINING
250-SIZE 10240000
250-VRFY
250-ETRN
250-AUTH LOGIN PLAIN CRAM-MD5 DIGEST-MD5
250-AUTH=LOGIN PLAIN CRAM-MD5 DIGEST-MD5
250-XVERP
250 8BITMIME
auth plain dXNlcgB1c2VyAHBhc3M=
235 Authentication successful

I’ve used a EHLO instead of the normal HELO as this is an extended hello, so the server gives you a list of things it can do. Notice that there are two AUTH lines, this is due to the broken_sasl_auth_clients line in /etc/postfix/main.cf.

You may have different authentication modules, it depends on what packages you have installed.

The important thing is the server’s response to your commands is 235 Authentication successful. This means that it recognizes the username and password. If it doesn’t, it returns a 535 Error: authentication failed. If you get a failed message, check the mail logs. The logs should tell you why the authentication failed.

Instead of using the plain authentication, you might want to use the LOGIN method. Once again mimencode is used to get the base64 encoding:

server$ printf ‘user’ | mimencode
dXNlcg==
server$ printf ‘pass’ | mimencode
cGFzcw==

You now have the two base64 encoded strings, to test this method is very similar to the PLAIN method.

server$ telnet 10.1.2.3 25
Trying 10.1.2.3…
Connected to 10.1.2.3.
Escape character is ‘^]’.
220 my.mail.server ESMTP Postfix
EHLO blah
250-my.mail.server
250-PIPELINING
250-SIZE 10240000
250-VRFY
250-ETRN
250-AUTH LOGIN PLAIN CRAM-MD5 DIGEST-MD5
250-AUTH=LOGIN PLAIN CRAM-MD5 DIGEST-MD5
250-XVERP
250 8BITMIME
auth login
334 VXNlcm5hbWU6
dXNlcg==
334 UGFzc3dvcmQ6
cGFzcw==
235 Authentication successful

You might wonder what that strange text is after the 334 numbers. Once again mimencode can help. It’s a base64 encoding of the response from the mail server.

server$ printf ‘VXNlcm5hbWU6′ | mimencode -u ; echo
Username:
server$ printf ‘UGFzc3dvcmQ6′ | mimencode -u ; echo
Password:

So the mail server is asking for a username and password, in base64. I don’t know why they bother to do this as it doesn’t make it that much more secure but at least you now know what it is.
Client Configuration

OK, so you have you server setup that can do authentication, but now you want your laptop that is running Postfix to relay all email through your server. This section describes the client setup.
Postfix Setup

Setting up Postfix is pretty simple. Tell Postfix to send all email to your mail server and enable SASL. The file /etc/postfix/main.cf requires the following lines:

relayhost = mail.example.net
smtp_sasl_auth_enable = yes
smtp_sasl_password_maps = hash:/etc/postfix/sasl/passwd
smtp_sasl_security_options =

The configuration is telling postfix to send all email to mail.example.net, use SASL authentication and that the passwords are found in a particular file. Remember for outgoing mail Postfix uses smtp while incoming uses smtpd. As the client sends email the configuration lines have the “d less” smtp_ keywords.
Client Password file

The format of the client password file is simple, especially if you have written hash tables for Postfix before. The key is the remote server and the value is the username and password to use for that server separated by a colon.

mail.example.net myuser:secpasswd

How to Export A MySQL Multiples Databases Structures Only

March 10th, 2011

If you no longer need the data inside the database’s tables (unlikely), simply add –no-data switch to export only the tables’ structures.
mysqldump –no-data -u root -p –databases db1 db2 db3 db3 db4 > /tmp/mysql_backup.txt

Install MySQL 5.5 on Ubuntu

March 8th, 2011

I followed the steps on this page to install MySQL 5.5 on Ubuntu.
To install and use a MySQL binary distribution, the basic command sequence looks like this:

shell> groupadd mysql
shell> useradd -r -g mysql mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db –user=mysql
shell> chown -R root .
shell> chown -R mysql data
# Next command is optional
shell> cp support-files/my-medium.cnf /etc/my.cnf
shell> bin/mysqld_safe –user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server

Here are some problems I had during the installation.

1. mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
I Fixed after installing libaio1: sudo apt-get install libaio1

2. When I was trying to run /usr/local/mysql/scripts/mysql_install_db –user=mysql to reate the data dictionary

This command gives the following error:
FATAL ERROR: Could not find mysqld

The following directories were searched:

/usr/libexec
/usr/sbin
/usr/bin

Fix: Find and remove the old my.cnf file.
sudo rm /etc/mysql/my.cnf, because it was getting the details from the /etc/my.cnf

Don’t forget to set your root password.
./bin/mysqladmin -u root password ‘new-password’

mysqld_safe A mysqld process already exists

March 5th, 2011

Today I faced   issue again with MySQL running of NFS .I was trying to shutwon mysql

using /etc/rc.d/init.d/mysql   stop

MySQL shutdown was successful .

after making changes in my.cnf file I tried to start MySQL using

/usr/bin/mysqld_safe –defaults-file=/etc/my.cnf &

I got error

mysqld_safe A mysqld process already exists

I checked that no other instance is running using

ps aux| grep MySQL

I got no MySQL process running, as I have faced so many errors because of the NFS

locking the files mentioned in my previous posts

http://onaxer.com/blog/blog/2011/02/26/error-1033-hy000-incorrect-information-in-file/

http://onaxer.com/blog/blog/2011/02/23/innodb-unable-to-lock-ib_logfile0-error-11/

http://onaxer.com/blog/blog/2010/06/01/innodb-unable-to-lock-ibdata1-error-37/

I tried the same solution again

1) /etc/init.d/portmap restart
2) /etc/init.d/nfslock restart

And it worked. I was able to start MySQL after that successfully.

MySQL Replication variable Seconds_Behind_Master explained

February 27th, 2011

Seconds_Behind_Master  variable in the MySQL Replication is often misused and misunderstood variables . Many DBA’s and Sysadmin use this variable to check the MySQL Replication latency between master and  slave in different replication topologies Master -> Slave , Master-> Master etc..

So what is this variable and how it works and what it shows as per the MySQL Documentation?

this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread and the value greatly depends on the  network between master and slave , this value can be realistic only on the fast network. this means that Seconds_Behind_Master  can not always  give the correct latency between the master and slave.this is the reason if you are monitoring this variables to know the how much the slave  behind master you must be getting some weird values and immediately coming to 0 .

In my   article I have recommend some methods on how MySQL replication should be   monitored.

ERROR 1033 (HY000): Incorrect information in file

February 26th, 2011

I got this error while restoring data from Netapp snapshot backup , I restored the last days backup

on a new directory on the NFS , after the restoring the MySQL data files from Netapp I tried to start

the mysql , MySQL Started , I noticed that queries are failing , on checking the error log

I got the error ERROR 1033 (HY000): Incorrect information in file for an Innodb table

I realised that I had not created the tmp file as per given in the my.cnf , I created the /tmp

file made mysql owner of the /tmp file , restarted the MySQL and error was gone.

the other possible reasons are
1) actual corruption of the table
2) Innodb plugin regsitration failed. reasons may be various

How to quickly Restore a single table from MySQLDump Backup

February 24th, 2011

Recently I had to recover a table with less then 200 records from backup file take from mysqldump of 50 GB. Normally there are different approaches which we can use to get the data from mysqldump for single table or set of tables.

1) Restore the mysqldump file on another server or same server with different name , then take the mysqldump of the tables using –tables option of mysqldump , then restore this dump file on the database we want. In mycase time was very important and I could not afford waiting for 50 Gb dump file to restore and then take backup and then restore , so this option was out of question.

2) using awk the logic here is that mysqldump has the table structure in the dump and then the actual insert commands , so we need to know the sequence of the tables in the mysqldump which will be same as of the tables listing in any tool in case you do show tables so say the table sequence in the show tables is

table1
table2
table3

and we need to restore the table2 , structure of table can be figure out using

show create table tablename

awk can be used like this

$ awk ‘/ structure for table .table2./,/structure for table .table3./{print}’ mydumpfile.sql > /tmp/recovered_table.sql
this will give all the insert commands for that table that can be restore in mysql.

This approach also could not work for me as it will take some time to parse the 50GB mysqldump.

3) Using mysql privileges .
what I did was used the mysql privileges , I created a table with the same name and structure on another Database say RecoverDB
I created a new user say recovery and in the RecoverDB database I gave all permission on the specific table only , I used the –force option of mysql which  keeps the restore process continue even if there is an error in the restoration process .now from the mysqldump file I did the restoration on the  RecoverDB

mysql -urecovery -ppassword RecoverDB </path of mysqldump file

as I have given write permission to only the table which I want to restore , restoration fails for all other tables except the one where
I have given priviledge I was able to restore the table in 2 mins, it was quick isn’t it . then I took the mysqldump of this table and restored on the actual DB where I wanted to restore. There might be other approaches as well and depending upon the platform (Windows, linux,solaris) the  (perl,awk) might  not be available (windows) but 3rd approach works irrespective of the platform.