HelloMySQL.pl - Sample Perl Program to Use MySQL

This section provides a sample Perl program, HelloMySQL.pl, which connects the MySQL server on the local machine, creates a table and inserts a record into the table.

Now we are ready to write a Perl program using the DBI module to access MySQL server. Here my first example, HelloMySQL.pl:

#- HelloMySQL.pl
#  Copyright (c) 2005 HerongYang.com. All Rights Reserved.
#
   use DBI;
#   $dbh = DBI->connect("DBI:mysql:test","root","TopSecret");
   $dbh = DBI->connect("DBI:mysql:test","herong","TopSecret");
   $dbh->do("create table hello (message varchar(80))");
   $dbh->do("insert into hello (message) values ('Hello world!')");
   $sth = $dbh->prepare("select * from hello");
   $sth->execute();
   while ((@row) = $sth->fetchrow_array()) {
      print "$row[0]\n";
   }
   $sth->finish();
   $dbh->do("drop table hello");
   $dbh->disconnect();
   exit;

If you are running the above script with the default "root" user created on MySQL Server 8.0, you will get the following error:

BI connect('test','root',...) failed: Client does not support
authentication protocol requested by server; consider upgrading
MySQL client at HelloMySQL.pl line 5.

Here is why. By default, all user accounts on MySQL Server 8.0 are created with the new authentication method called: caching_sha2_password. Unfortunately, caching_sha2_password is not supported by the "mysqli" module yet.

One solution is to change the user's authentication method back to "mysql_native_password", or create a new user with "mysql_native_password".

Here is how to create a new user "herong" with the old authentication method, "mysql_native_password" with ALL privileges on all databases and tables:

herong> %mysql%\bin\mysql.exe --user=root --password
Enter password: TopSecret

mysql> CREATE USER herong IDENTIFIED
mysql> WITH mysql_native_password BY 'TopSecret';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL ON *.* TO herong;
Query OK, 0 rows affected (0.01 sec)

Run the script again with user "herong", you will see the "Hello world!" message.

herong> \local\perl\bin\perl -version
This is perl 5, version 18, subversion 1 (v5.18.1) built
for MSWin32-x86-multi-thread-64int
(with 1 registered patch, see perl -V for more detail)

herong> \local\perl\bin\perl HelloMySQL.pl
Hello world!

Congratulations, the sample Perl program is working. This proves that all required software and modules were installed correctly.

Table of Contents

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

 Introduction of MySQL Programs

 PHP Programs and MySQL Server

Perl Programs and MySQL Servers

 Accessing MySQL Servers from Perl Programs

HelloMySQL.pl - Sample Perl Program to Use MySQL

 Java Programs and MySQL Servers

 Datatypes and Data Literals

 Operations and Expressions

 Character Strings and Bit Strings

 Commonly Used Functions

 Table Column Types for Different Types of Values

 Using DDL to Create Tables and Indexes

 Using DML to Insert, Update and Delete Records

 Using SELECT to Query Database

 Window Functions for Statistical Analysis

 Use Index for Better Performance

 Transaction Management and Isolation Levels

 Locks Used in MySQL

 Defining and Calling Stored Procedures

 Variables, Loops and Cursors Used in Stored Procedures

 System, User-Defined and Stored Procedure Variables

 MySQL Server Administration

 Storage Engines in MySQL Server

 InnoDB Storage Engine - Primary and Secondary Indexes

 Performance Tuning and Optimization

 Bulk Changes on Large Tables

 MySQL Server on macOS

 Installing MySQL Server on Linux

 Connection, Performance and Second Instance on Linux

 Archived Tutorials

 References

 Full Version in PDF/EPUB