Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 31 to 60 of 255 Next 30 Older Entries

Displaying posts with tag: perl (reset)

How To Sort Columns of MySQL Data on a Web Page With Perl
+0 Vote Up -0Vote Down

A friend of mine was building a web site so his customers could view his current inventory of transportation containers, and he asked me for help on how to sort the rows of information that appeared on his site. So, in this post, I will give you a quick example on how to sort columns on a web page.

First, let’s start with an inventory database that we will build in MySQL (http://mysql.com):

CREATE TABLE `inventory` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`item_name` varchar(30) NOT NULL,
`item_SKU` varchar(20) NOT NULL,
`item_cost` decimal(4,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100000 DEFAULT CHARSET=latin1

Next, here are some SQL statements to populate the MySQL database with some sample data.

use inventory;





  [Read more...]
Automatically Download MySQL Enterprise Monitor Graphs as PNG Files Using Perl
+0 Vote Up -0Vote Down

I was giving a presentation of the MySQL’s Enterprise Monitor (http://www.mysql.com/products/enterprise/monitor.html)* application to a client recently. I was demonstrating the “graphs” section of MEM, where you can monitor MySQL sessions, connections, replication latency and more with 60+ graphs. Usually, you view the graphs from within the MEM Enterprise Dashboard (via a web browser). But the client asked if there was a way to automatically download graphs. I wasn’t sure why he wanted to download the graphs (I didn’t ask), but I knew it wasn’t possible by using MEM alone. However, in the past I have written Perl scripts to automatically download files from web sites, so I thought I would see if it was possible with MEM.

 
*The MySQL Enterprise Monitor (MEM)
  [Read more...]
Using MySQL and Perl to Create, Edit and Delete Information Via a Web Page
+0 Vote Up -0Vote Down

A friend of mine was asking me for my recommendation of a good desktop database program to use to keep track of his inventory of cargo containers. I suggested to him that he should use MySQL and write a web page interface to do everything that he needed. He then reminded me that he is a lawyer by trade, and that he doesn’t have any computer programming experience. Then I remembered that he has almost zero computer skills. And his Texas Hold-Em skills are even worse, but I don’t mind taking his money. In his case, he should just use a notepad and a pencil. (As for the question – what is a lawyer doing with cargo containers? – that is a different story.)

If he did decide to broaden his horizons a bit, he could easily write his own software web application for creating and storing almost any

  [Read more...]
Checking on the Progress of Large DML Commands in MySQL Using Perl – Part Two
+0 Vote Up -0Vote Down

Part Two of Two: Checking on database activity when running a large DML (Data Manipulation Language) statement – such as INSERT, DELETE, UPDATE or SELECT.

Part Two: Monitoring the activity via Perl and SHOW ENGINE INNODB STATUS. (part of the InnoDB Monitors)

In part one, I showed you how to use a Perl script to insert a million rows of dummy data into a table. I needed a large database in order to test a Perl script that I would use to monitor the activity when running a large DML statement.

The original reason for creating both of these scripts was to find a quick way to see if a large

  [Read more...]
Checking on the Progress of Large DML Commands in MySQL Using Perl – Part One
+1 Vote Up -0Vote Down

Part One of Two: Checking on database activity when running a large DML (Data Manipulation Language) statement – such as INSERT, DELETE, UPDATE or SELECT.

Part One: Inserting a million rows into a database.

A friend of mine had asked a question – “Is there any way you can track how far you have advanced in a slow-moving ALTER or OPTIMIZE statement?”. A customer was performing some modifications on a database with tens of millions of rows, and they wanted to be able to see if the command was making any progress.

Since the customer was using the InnoDB storage engine, I thought of a way that you could check on the progress – but only given

  [Read more...]
Using MySQL, Perl and jQuery to Auto-Populate a Form Field on a Web Page
+0 Vote Up -0Vote Down

If you have ever built a form on a web page, you might have used a drop-down menu to display the choices available for a particular field. With a drop-down menu, you restrict the choices a user may select so that the user doesn’t enter invalid data (among other reasons). If a user misspells an entry, then a subsequent search for that value would not produce a found result.

A friend of mine who runs an online forum sent me an email about a problem he was having. He was trying to modify an existing registration web page using jQuery to auto-populate the state names, and then pass the state abbreviation back to his MySQL database. Believe it or not, he was actually having problems with people knowing their own state abbreviation. He had searched and found an example of what he wanted to do, but he couldn’t get

  [Read more...]
Setting up Perl on Windows for MySQL Scripts (mysqldumpslow, mysql_explain_log, etc.)
+3 Vote Up -0Vote Down

This article is just a how-to for setting up Perl on Windows in order to use the perl scripts provided with MySQL, such as mysqldumpslow.pl or mysql_explain_log.pl.

Now, you might say there is a section on this topic in the MySQL manual, to which I’d agree. But, this was for ActiveState Perl 5.6. The latest ActiveState Perl is 5.12, so thoese instructions are a bit out-of-date. Also, there are some helpful “User Comments” on that same page, but again, those are slightly out-of-date in the ActiveState 5.12. So, I thought I’d just post the steps I took in order to set this up.

First, download and install ActivePerl. Click the “Download ActivePerl 5.12.4 for Windows” button (choose either 32-bit or 64-bit). You’ll be

  [Read more...]
Using MySQL to Import and Retrieve Blobs and Display as Image Files in HTML
+3 Vote Up -1Vote Down

I received a phone call from a friend of mine who had some questions about storing image files as blobs in MySQL. He wanted to design a web site that would allow someone to upload an image, and then that image would be stored in the MySQL database for viewing later. He stated that he was going to be storing thousands of images. I mentioned that it might be better to store the images on the file system, and then to just store the location of the image in the database. But, he still wanted to know how to do it and would decide which solution he would incorporate later.

I already had a Perl script that allowed me to upload files to a web site, as I would give out this URL to people that wanted to send me large files. And, I know that you can store images (and other files) in MySQL as a blob, but I wasn’t sure

  [Read more...]
Splitting a MySQL Dump File Into Smaller Files Via Perl
+1 Vote Up -1Vote Down

I was trolling the MySQL forums web site and noticed a post regarding someone who was trying to load a 50-gigabyte MySQL dump file. The author of the post had stated that the loading of the file had been running for days, and was wondering if there was a better way to import the file. They did not have access to anything else (i.e. – original database) but this file.

I have had to restore several databases in the past from a single large MySQL dump file – which led me to start backing up each database individually. These databases are for my own personal use and are not updated that often, so I don’t need to have point-in-time recovery – and so a MySQL dump works just fine. If I had a production system, I would invest in the MySQL Enterprise Backup and the MySQL

  [Read more...]
Perl TCP Listener for Detecting Available Ports for MySQL Enterprise Monitor
+2 Vote Up -0Vote Down

I recently visited a client for the purpose of installing and demonstrating MySQL Enterprise Monitor (http://www.mysql.com/products/enterprise/monitor.html).

If you are unfamiliar with the MySQL Enterprise Monitor – from the MySQL web site: The MySQL Enterprise Monitor continuously monitors your MySQL servers and alerts you to potential problems before they impact your system. Its like having a “Virtual DBA Assistant” at your side to recommend best practices to eliminate security vulnerabilities, improve replication, optimize performance and more. As a result, the productivity of your developers, DBAs and System Administrators is improved significantly.

The MySQL Enterprise Monitor is a distributed web application that is deployed within the safety of your firewall. It is comprised of a

  [Read more...]
Convert .csv File to MySQL Database via Perl
+1 Vote Up -0Vote Down

Have you ever had a spreadsheet file or a large .csv file that you wanted to manipulate, but you want more power than a spreadsheet program could offer?

Before I started using MySQL, I would usually throw the .csv file into a desktop database program, like FileMaker. FileMaker would allow you to import the .csv file and it would automatically create the column headers for you. Recently, I was given a spreadsheet with 27,000 rows in it. I still use FileMaker for some databases, but I wanted the power of MySQL to manipulate the information contained in this file. So, I could have easily just typed out the database column names manually into a MySQL “create table” statement, guessed at the types and sizes of the columns and then imported the .csv file. Instead, I decided to write a Perl script to do the dirty work for me. Plus, this

  [Read more...]
Want to hack Maatkit and Aspersa? We’re hiring
+0 Vote Up -0Vote Down

As announced on the Maatkit and Aspersa mailing lists, Daniel and I have created a new toolkit that represents the union of the two, and will be focusing efforts on this Percona Toolkit moving forward. The goal is to make them simpler and significantly more powerful, and to create more tools. The tools will continue to be open-source, but will be developed primarily to meet our MySQL support and consulting staff’s needs.

If you’re interested in challenging software engineering in Perl and shell, then please apply online. You can work online from anywhere, but I strongly prefer someone in the Americas timezones.

Further Reading:

  [Read more...]
Scripting Backups of MySQL with Perl via mysqldump
+0 Vote Up -0Vote Down

MySQL provides you with a nice utility for creating a backup of your databases. From the mysqldump documentation page: “The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.”

The mysqldump utility doesn’t provide you with a “hot” or live backup like MySQL Enterprise Backup (http://www.mysql.com/products/enterprise/backup.html) does, and there may be issues with mysqldump locking tables during the

  [Read more...]
Inserting Data into MySQL with Perl
+0 Vote Up -0Vote Down

In the two previous posts, we looked at simply connecting to a MySQL database via Python and Perl. In this post, we will:

- use an insert statement to input data into a MySQL table via Perl
- use a select statement to view the same data to confirm our results

For this example, we will use a table named “address”. Here is the SQL statement that we used to create the table:



SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
  `name_first` varchar(30) NOT NULL,
  `name_last` varchar(30) NOT NULL,
  `address_01` varchar(40) NOT NULL,
  `address_02` varchar(40) NOT NULL,
  `address_city` varchar(30) NOT NULL,
  `address_state` varchar(20) NOT NULL,
  `address_postal_code` varchar(12) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS = 1;


We are only going to


  [Read more...]
Connecting to MySQL with Perl
+0 Vote Up -0Vote Down

When I was designing web sites, for a long time I wrote my HTML code the “hard” way – by opening a text editor and manually typing in the code (this was before I purchased Adobe DreamWeaver).

During that time of manual HTML writing, I had a project that required forms on a web page, and I needed a place to store the information. After talking with a few tech friends, I decided to use MySQL as my web site database, and Perl as my scripting language.

I had written complex Bourne shell scripts before, but Perl was something entirely new. With a little help from a buddy of mine, after a few hours I was off and running. I was amazed at how easy it was to connect to a MySQL database with Perl.

This example will show you how to

  [Read more...]
Summary of Blog Posts for Week of July 11
+0 Vote Up -0Vote Down

I hope everyone is enjoying summertime, at least in the northern hemisphere. I’m about to head out to the pool, but before I go, here is a summary of this week’s blog posts.

1. Introduction to Perl interface for Monitis API
Monitis announces a simple way to access its API through Perl, a high-level, general-purpose, interpreted, dynamic programming language. This post demonstrates some examples for using the API with Perl and describes some of the benefits of the programming language. The source can be found on our Github page.

2. 101 Tips to


  [Read more...]
Installing Perl DBI and DBD-mysql on Windows 64 bit
+1 Vote Up -0Vote Down

I had trouble getting Perl DBI and DBD-mysql on Windows in the past. In addition, on Windows 64-bit, you sometimes see recommendations of using 32-bit Perl.

Today I got to test the latest 64-bit ActiveState Perl distro for Windows, version 5.12.3.1204. I tested it on Windows 2008 R2 64-bit. I am happy to report that it works. I am not categorically recommend FOR the installation of 64-bit Perl on Windows, though.

Here are the steps:
1. Get the ActiveState Perl 64-bit package for Windows and install it, following all the default options;
2. On command prompt, do:
cd c:\perl64\bin
ppm install DBI
ppm install DBD-mysql

I then tested against both Oracle’s MySQL 5.5 Community Server and MariaDb’s 5.2.7 on






  [Read more...]
Poor man’s mytop
+2 Vote Up -0Vote Down

I often need to watch a server that’s very minimally configured, e.g. has no Perl DBI libraries installed, and I shouldn’t install anything. The following snippet is a quick way to do that:

watch 'mysqladmin proc | grep -v Sleep | cut -b0-130'

Replace 130 by the width of your terminal, naturally.

(Of course, innotop is much more featureful than mytop, but mytop is the essential functionality we’re going for here!)

Related posts:

  • An easy way to run many tasks in parallel
  • How to read Linux’s /proc/diskstats easily
  •   [Read more...]
    Excluding databases from mysqldump
    +7 Vote Up -0Vote Down
    A question that came up during the MySQL track at the UKOUG conference in Birmingham was "Can I exclude only a few databases from mysqldump? Let's say that I have 50 databases, and I want to dump all of them, except a few."
    As many know, mysqldump has an option to ignore specific tables. SO if you have 1,000 tables in a databases, you can tell mysqldump to dump all the tables except a few ones.
    There is no corresponding option to exclude one or more databases.
    However, if you know your command line tools, the solution is easy:
    First, we get the list of all databases:
    mysql -B -N -e 'show databases'
    information_schema
    employees
    five
    four
    mysql
    one
    performance_schema
    six
    test
    three
    two
    -B forces batch mode (no dashes box around the data), while -N gets the result without the
















      [Read more...]
    innotop version 1.8.0 released
    +6 Vote Up -0Vote Down

    I’ve just uploaded the new release of innotop to Google Code. Short version of the changelog: works on MySQL 5.1 with the InnoDB plugin; more efficient; supports Percona/MariaDB USER_STATISTICS data; fixes a bunch of small annoying bugs.

    Longer version:

    2010-11-06: version 1.8.0
    
       Changes:
       * Don't re-fetch SHOW VARIABLES every iteration; it's too slow on many hosts.
       * Add a filter to remove EVENT threads in SHOW PROCESSLIST (issue 32).
       * Add a timestamp to output in -n mode, when -t is specified (issue 37).
       * Add a new U mode, for Percona/MariaDB USER_STATISTICS (issue 39).
       * Add support for millisecond query time in Percona Server (issue 39).
       * Display a summary of queries executed in
      [Read more...]
    DBD::mysql 4.018 released
    +3 Vote Up -0Vote Down

    Dear Perl and MySQL developers, admins and enthusiasts,

    I’m please to announce the release of DBD::mysql 4.018! This release includes some code cleanups, added documentation as well as new features (database handle attributes) that allow you to be able to ascertain the server and client versions (Thank you to Robert M. Jansen). An example below, from the test for this new feature, shows you how you can use these new handle attributes:

    print "$dbh->{mysql_clientinfo}\n";
    
    5.2.0-MariaDB
    
    print "$dbh->{mysql_clientversion}\n";
    
    50200
    
    print "$dbh->{mysql_serverversion}\n";
    
    50200
    

    You can obtain the latest DBD::mysql release source from Github:

    git clone git://github.com/CaptTofu/DBD-mysql.git

    Or CPAN:

    http://search.cpan.org/~capttofu/DBD-mysql-4.018/lib/DBD/mysql.pm

    The file:

    file:

      [Read more...]
    Having fun with MySQL and Python: converting MySQL character set to utf8
    +0 Vote Up -0Vote Down

    Lately I worked quite a bit with Python and Linux, writing monitoring and automation utilities. I am in a transition period, so I thought I ought to write some Python stuff interfacing with MySQL for fun, and start positioning myself for expanded career horizon, I hope.

    To get started, I thought it would be fun to rewrite a Perl utility I wrote before with Python. That script converts MySQL character sets to utf8, a very common task for wikis and blogs during an upgrade. This time, I did everything from scratch: firing up an Amazon EC2 Linux instance, hand install and configuring MySQL 5.1.50 (creating mysql user, group, wget tarball, setting directory ownership and permissions, creating symbolic to MySQL binaries, editing my.cnf,

      [Read more...]
    YAPCEU 2010 – Day Two…
    +1 Vote Up -0Vote Down

    After enjoying the excellent hospitality of our host here in Pisa (6 courses) we were ready for our second day at YAPCEU 2010 here in sunny Pisa.

    Larry’s new catch phrase “My Language is a four letter word” was the ‘Buzz word’ for today. We settled down to some very interesting talks, the highlight for me being Tim Bunce’s talk on using Devel::NYTProf to Optimize your code. Tim first gave us a quick and dirty overview of optimization which covered the basics of where to start and what to look for he followed up with real examples of Optimizer output and than wrapped up with a few before and after results on an optimization effort.

    The rest of the day was dedicated in my opinion, to the future of DBs in with Nelson Ferraz giving an excellent presentation of his concepts for using Perl as to glue for a Data Warehouse application. Next on my agenda,

      [Read more...]
    As of late...
    +1 Vote Up -0Vote Down
    What I'm up to lately (giving love to some projects):

    * Fixing bugs in DBD::mysql, just released 4.015, 4.016, and next 4.017. I had a patch sent yesterday from a user/developer that I want to get out there
    * Memcached::libmemcached - 0.4201 version - now using latest libmemcached 0.42. This is the only Perl client that supports binary protocol!

    patg@patg-desktop:~/code_dev/perl-libmemcached$ PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/12-set-get-binary.t




      [Read more...]
    Four short links: 1 July 2010
    +0 Vote Up -0Vote Down

  • Conflict Minerals and Blood Tech (Joey Devilla) -- electronic components have a human and environmental cost. I remember Saul Griffith asking me, "do you want to kill gorillas or dolphins?" for one component. Now we can add child militias and horrific rape to the list. (via Simon Willison)
  • Meteor -- an open source HTTP server that serves streaming data feeds (for apps that need Comet-style persistent connections). (via gianouts on Delicious)
  • Hobby King RC Store -- online
  •   [Read more...]
    Keeping Up
    +2 Vote Up -0Vote Down

    I found I never published this post as it was sitting in my drafts few months now — it was written in 13th February, 2010. I’m publishing it without any changes.

    I learn therefore I am!

    I’ve just wrote few bits about learning a new technology and after skimming through my Google Reader, I noticed a great post by Chen Shapira — Deliberate Practice. That’s reminded me about another aspect of learning that I didn’t mention — learning is a continuous process.

    There are two aspects…

    • No matter how good I am and how much I know, my knowledge and expertize become outdated relatively quickly these days unless I keep up with the
      [Read more...]
    MySQL Sandbox meets plugins
    +5 Vote Up -0Vote Down
    I saw it coming.
    In the past year I have been working more and more with plugins. The InnoDB plugins, the semi-synchronous replication plugins, the Gearman UDFs, the Spider plugins, and every time I found myself doing a long installation process, with inevitable mistakes.
    So I told myself "I wish I had a tool that installed plugins instantly and painlessly, the way MySQL Sandbox installs a server.
    There you go. So yesterday I had enough of suffering and have put together an installation script that does a good job of installing several plugins with little or no effort.

    Overview

    How does it work? For now, it's a separate script, but it will soon end into SBtool, the Sandbox tool.
    Plugins need




      [Read more...]
    World map, shaped by MySQL downloads
    +5 Vote Up -0Vote Down

    A few years ago, during the MySQL Conference opening keynote, two world maps of MySQL downloads were displayed. With the lights down, they made an impression.
    Oddly enough, to the best of my knowledge, the downloads map has not been drawn again since then. I asked my friend and colleague Markus Popp, and he provided the data from the downloads logs, leaving the implementation to me.
    A first attempt with Google Maps API produced a chart that is nice to see for a single country or town, but hardly pleasant for the entire world.




      [Read more...]
    New Maatkit tool to compute index usage
    +1 Vote Up -0Vote Down

    In a couple of recent consulting cases, I needed a tool to analyze how a log of queries accesses indexes and tables in the database, specifically, to find out which indexes are not used. I initially hacked together something similar to Daniel Nichter’s mysqlidxchk, but using the framework provided by Maatkit, which gave me a pretty good start right out of the box. This was useful in the very tight time constraints I was under, but was not a complete solution. Alas, I could not use anything like Percona’s enhancements for finding unused indexes.

    So, in response to another consultant’s customer request (and sponsorship — thank you!) I spent more time actually writing a real tool in the Maatkit style, with

      [Read more...]
    Maatkit learns how to map-reduce
    +2 Vote Up -3Vote Down

    The May release of Maatkit included a new feature in mk-query-digest. This allows you to process queries in many pieces, write out intermediate results, and then combine the pieces in a separate step. Maybe it’s not exactly map-reduce, but it makes a good headline.

    The purpose is to enable query analysis across an arbitrarily large group of servers. Process queries on all of them, ship the results to a central place, and then combine them together. Pre-processing the results has some nice benefits, such as reduced bandwidth requirements, speeding up processing by doing it in parallel, and reducing the workload on the central aggregator. One Percona customer with many MySQL instances is trying this out.

    The --save-results option on mk-query-digest saves the digested results to a file, stopping just before the final stages of the query

      [Read more...]
    Previous 30 Newer Entries Showing entries 31 to 60 of 255 Next 30 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.