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 中文
Showing entries 1 to 30 of 103 Next 30 Older Entries

Displaying posts with tag: coding (reset)

MySQL Workbench 6.0: Help is on the way…
Employee_Team +2 Vote Up -0Vote Down

Do you know this scenario: you are writing down  a stored procedure but you can’t for the life of you remember the exact syntax of that CASE statement? Has it to end with CASE or not? Can I use more than one WHEN part and how should that be written? Usually you end up opening a web page and read through the excellent MySQL online docs. However, this might cost too much time if you quickly need different statements and other detail info. Here’s where MySQL Workbench’s context help jumps in.

The server can help

It’s probably only known to the die-hard terminal operators who write most of their SQL queries in a MySQL console window: the MySQL server already has a stripped down set of help topics produced by the Docs team. That means you can always get at least the syntax but often

  [Read more...]
Building MySQL Database Applications with Go
+1 Vote Up -0Vote Down

Last night at the Golang-DC meetup I spoke about building (MySQL) database applications with Go. The meetup was well attended and people were very enthusiastic about Go. I spent a few minutes talking about Go in general, how VividCortex uses Go (we’ve built our agents, API servers, and all backend processes with Go), why we like it, some of the nice things it enables like making it easy to build very resilient programs, and then I gave the presentation, which I’ve embedded below.

Afterwards the discussion ranged to a lot of related topics. This was the best part of the evening for me. There were really great questions on a variety of topics, and insightful answers from everyone.

MySQL Workbench: Script for adding columns to all tables in a model
Employee_Team +0 Vote Up -0Vote Down

Here’s a quick Python script for adding columns for all tables in your EER model. This will create a create_time and update_time columns in all tables, but you can modify it to your needs. To execute:

  • go to Scripting -> Scripting Shell…
  • click the New Script toolbar icon at the top left corner
  • select Python Script and specify some name for the script file
  • click Create
  • copy/paste the script there
  • click the Execute toolbar button
  • Make sure to backup your model before running this!

    The code

    # get a reference to the schema in the model. This will get the 1st schema in it.
    schema = grt.root.wb.doc.physicalModels[0].catalog.schemata[0]
    # iterate through all tables
    for table in schema.tables:
        # create a new column object and set its name
        column = grt.classes.db_mysql_Column()
        column.name = "create_time"
        # add it
      [Read more...]
    mysqlnd_qc and Symfony2
    Employee +1 Vote Up -0Vote Down

    Previously I was writing about combining Symfony2 and mysqlnd to get more statistics on what is going on below the surface in the database communication when using a Symfony2 application via the Symfony2 profiler. Now that's not all that can be done and I gave some ideas for extending this. One idea was adding mysqlnd_qc support. mysqlnd_qc is the client side query cache plugin for mysqlnd. This provides a client-side cache for query results transparently without changing the application.

    <span id="XinhaEditingPostion"></span><span

      [Read more...]
    Symfony 2 and mysqlnd
    Employee +0 Vote Up -0Vote Down

    In a previous blog posting I was mentioning that I'm working on a small hobby PHP project. As I'm using this project to update myself to current frameworks I've decided to use Symfony2. Symfony provides a nice feature, which is the Symfony Profilier, an extensive logging and reporting system for Symfony2 developers to understand what's going on. A part of it is the Doctrine query logger which lists all database queries executed by Doctrine and their execution time.

    This is nice but when we're using mysqlnd in our PHP build we have more information available. "So why not use that information," I thought and built

      [Read more...]
    Direct MySQL Stream Access - Revised
    Employee +2 Vote Up -0Vote Down

    Roughly three years ago I was writing about Direct MySQL Stream Access - a way to access the low-level stream PHP's mysqlnd library is using. Back then this had been a patch against PHP's mysqli extension. As such a feature is quite dangerous (you can easily mess with the connection state which confuses mysqlnd and/or the MySQL server) we didn't push it into the main PHP tree. Now three years later it's time to look at this again as we don't need to patch PHP anymore.

    Since the mentioned patch was written mysqlnd got a plugin interface about which I was talking before. This plugin-interface, especially in the version of PHP 5.4, makes it trivial to implement this feature.

      [Read more...]
    mysqlnd plugins and json
    Employee +3 Vote Up -0Vote Down

    Some time ago I was already writing about the power included with mysqlnd plugins and how they can they can be used transparently to help you with your requirements without changing your code. But well, as mysqlnd plugins in fact are regular PHP extensions they can export functions to the PHP userland and providing complete new functionality.

    In my spare time I'm currently writing a shiny Web 2.0 application where I'm heavily using AJAX-like things, so what I do quite often in this application is, basically this: Check some pre-conditions (permissions etc.) then select some data from the database, do a fetch_all to get the complete result set as an array and run it through json_encode; or to have it in code:

    $m = new MySQLi(/*...*/);
      [Read more...]
    Improvements for PHP application portability in PHP.next
    Employee +0 Vote Up -0Vote Down

    I was writing about PHP.next before, many things improved there meanwhile. Most notably we have a committed version number: The next PHP release will be called PHP 5.4. The topic I want to talk about today is "Improved application portability" which covers multiple small changes which aim at making it simpler for developers to write applications working on any PHP setup.

    Separating <?= from short_open_tags

    PHP knows quite a few ways to separate PHP code from surrounding text (usually HTML), most applications use <?php as that works on every system. There is a short form of this, <?, which can be disabled using php.ini's short_open_tags setting. Being able to disable this is important when embedding PHP

      [Read more...]
    Escaping from the statement mess
    Employee +2 Vote Up -0Vote Down

    One of the issues Web Developers face is making their application robust to prevent SQL injection attacks. Different approaches exist which help. Sometimes people use large abstraction layers (which, sometimes, don't make anything safe ...) and sometimes people use prepared statements as a way to secure queries. Now prepared statements were a nice invention some 30 years ago abut they weren't meant for making things secure and so they do have some shortcomings: One issue is that preparing and executing a query adds a round-trip to the server where it then requires resources. In a classic application this is no issue. The users starts the application up early in the morning and processes data multiple times so the prepared statement handle is re-used quite some time. The system benefits from early optimisations.  In a typical PHP Web application this isn't the

      [Read more...]
    What’s wrong with MMM?
    +5 Vote Up -0Vote Down

    I am not a fan of the MMM tool for managing MySQL replication. This is a topic of vigorous debate among different people, and even within Percona not everyone feels the same way, which is why I’m posting it here instead of on an official Percona blog. There is room for legitimate differences of opinion, and my opinion is just my opinion. Nonetheless, I think it’s important to share, because a lot of people think of MMM as a high availability tool, and that’s not a decision to take lightly. At some point I just have to step off the treadmill and write a blog post to create awareness of what I see as a really bad situation that needs to be stopped.

    I like software that is well documented and formally tested. A lot of software is usable even if it isn’t created by perfectionists. But there are two major things in the MySQL world for which I think we can

      [Read more...]
    MySQL Workbench: Introducing Utilities
    Employee_Team +8 Vote Up -0Vote Down

    MySQL has the well earned reputation for ease-of-use and “15-minutes-to-success”, since we continually focus making the server easy to use. MySQL Workbench provides the visual tools for database design, development, and administration. However, many DBAs prefer using the command-line, and there are many tasks that require the creation scripts for doing the job.

    To make it easier to work with the server, the latest release of the MySQL Workbench—version 5.2.31—contain a set of Python scripts intended to make the life easier for DBAs by providing easy-to-use utilities for common tasks, which were introduced in the blog MySQL Workbench: Utilities. The set currently consists of just a few utilities, but will expand over time.

    The utilities available in the Workbench are:

      [Read more...]
    mysqlnd plugins for PHP in practice
    Employee +1 Vote Up -0Vote Down

    If you follow my blog or twitter stream you might know I've recently been at Barcelona to attend the PHP Barcelona conference. Conferences are great for exchanging ideas one of the ideas I discussed with Combell's Thijs Feryn: They are a hosting company providing managed MySQL instances to their customers, as such they run multiple MySQL servers and each server serves a few of their customers. Now they have to provide every customer with database credentials, including a host name to connect to. The issue there is that a fixed hostname takes flexibility out of the setup. Say you have db1.example.com and db2.example.com over time you figure out that there are two high load customers

      [Read more...]
    MySQL Workbench Plugin: Auto-Create Foreign Keys
    Employee_Team +6 Vote Up -0Vote Down

    To automatically create Foreign Key relationships is a typical use case for developers working with the MyISAM storage engine. This has been a popular topic on the Blog and forums so we’re revisiting it here – with a new and improved plugin written in Python.

    While the InnoDB storage engine supports foreign keys, MyISAM doesn’t, but developers often pick it for various reasons and leave the application to handle relationships itself. MySQL Workbench can be used to reverse engineer a database to a model to better visualize or maintain it. But since MyISAM doesn’t have foreign keys, databases that use it will be lacking a very important part of it’s structure in the diagrams. You can link the columns manually, using the relationship tool to link columns, but you can also automate that. Databases are usually created so that columns that represent relationships have names that follow some

      [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...]
    SQL: good comments conventions
    +1 Vote Up -0Vote Down

    I happened upon a customer who left me in awe and admiration. The reason: excellent comments for their SQL code.

    I list four major places where SQL comments are helpful. I’ll use the sakila database. It is originally scarcely commented; I’ll present it now enhanced with comments, to illustrate.

    Table definitions

    The CREATE TABLE statement allows for a comment, intended to describe the nature of the table:

    CREATE TABLE `film_text` (
     `film_id` smallint(6) NOT NULL,
     `title` varchar(255) NOT NULL,
     `description` text,
     PRIMARY KEY (`film_id`),
     FULLTEXT KEY `idx_title_description` (`title`,`description`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Reflection of `film`, used for FULLTEXT search.'

    It’s too

      [Read more...]
    15 years of PHP
    Employee +3 Vote Up -1Vote Down

    Wow, 15 years ago, on June 8th 1995, Rasmus announced PHP 1.0. Time for a personal look back:

    I can't remember when I first went online. Out from the local BBS systems into the wide open net. It must have been around the same time. But soon I figured out that I needed my own homepage, so I created one, using black fonts on a green (#00ff00) background. I was proud. The only issue: I had no idea how to make it accessible to others, I had the HTML file local on the PC, but well, I still was proud. I was proud since the effect of this HTML with a little bit of JavaScript was way stronger than most of my BASIC stuff I did before.

    A bit later, in 1998, a cousin brought a magazine about writing Perl CGI applications which caught my attention so I

      [Read more...]
    Berkeley DB 5 and PHP
    Employee +2 Vote Up -0Vote Down

    Everybody working on Unix or in the database world stumbles over Oracle Berkeley DB every now and then. DB is an Open Source embedded database used by applications like OpenLDAP or Postfix. Traditionally it followed mostly a key-value access pattern. Now what caught my attention was the fact that the recently released DB 5.0 provides an SQLite-like C API with the promise of providing better concurrency and performance than regular SQLite. Time to give it a shot.

    So I grabbed the source distribution, checked the documentation and saw that I shall use the

      [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...]
    mk-query-digest now supports Postgres logs
    +3 Vote Up -0Vote Down

    Maatkit does more than just MySQL. I’ve just committed a new version of mk-query-digest, a powerful log analysis tool, with support for Posgtres logs, in both syslog and stderr format. I’m hoping that people will give this a spin in the real world. I have lots of test cases, but that’s never enough; I’m looking for people to crunch their logs and let me know if anything breaks.

    A brief tutorial:

    # Get it
    $ wget http://www.maatkit.org/trunk/mk-query-digest
    # Run it
    $ perl mk-query-digest --type pglog /path/to/log/file
    # Learn about it (search for the string "pglog")
    $ perldoc mk-query-digest

    I’m going to close comments on this blog post so I don’t get bug reports in the comments. If you have feedback, please

      [Read more...]
    Production scripts: sign me up for obfuscation
    +0 Vote Up -0Vote Down
    If there’s one thing that will always make me angry, it’s people that should not be editing my code going and editing my code. If you want to change something on the server and you have sudo privileges please let the real admin know beforehand. I don’t mind people improving processes or scripts but if [...]
    MySQL Workbench 5.2 – Code Statistics
    Employee_Team +0 Vote Up -0Vote Down

    A community member recently noted, that it takes quite a long time to compile MySQL Workbench. So he started wondering about how big the project actually is and asked for the Lines Of Code we have in our MySQL Workbench 5.2 repository.

    We did not have this information at hand and therefore Alfredo ran some scripts during the weekend and generated this nice breakdown.

    As you can see, we almost have 700k lines of code to maintain. Given that the MySQL Server itself has about 900k lines of code this is a pretty decent number I think, especially for a small team of 7.

    How to round to the nearest whole multiple or fraction in SQL
    +2 Vote Up -0Vote Down

    Every once in a while, I find myself needing to round a number up to the nearest even power of 10, or round a time interval to the nearest quarter of an hour, or something like that. This is actually quite simple, but for some reason I always find myself confused about how to do it. I have to reason it out all over again, instead of just remembering how to do it. Perhaps writing this blog post will help me remember next time.

    The basic idea for rounding to whole multiples is to divide the number, losing precision. Then round, floor, or ceiling the resulting number, and multiply to get back to the original magnitude. For rounding to fractions, reverse the process: multiply, round and divide again.

    This actually works for any programming language, not just SQL. But I find myself doing it in SQL most often.

      [Read more...]
    Auto-create relationships for MyISAM tables in MySQL Workbench
    Employee_Team +2 Vote Up -0Vote Down

    Over a chat on the #workbench IRC channel, Collin Cusce has written a handy little Lua script to automatically create relationships (through foreign keys) for his reverse engineered database.

    Reverse engineering the DB to import tables into a diagram was easy, but their database used no “hard” foreign keys and an ER diagram without relationships wouldn’t be of much use. So one option would be to individually connect each foreign key column pair by hand, using the relationship picking tool . But doing that for the thirty-something tables in the database would be too much work and something could be overlooked and left out. The other option would be to automate that, since all such foreign keys followed a naming convention like

      [Read more...]
    A tweak to column alignment for the mext script
    +0 Vote Up -0Vote Down

    I tweaked the mext script so it auto-detects the necessary column widths for each sample.

    Get mext here.

    kill, exit(), _exit() and issues getting gcov results
    Employee +0 Vote Up -0Vote Down

    We are currently running code coverage for MySQL Cluster testing and have hit a few bumps along the road when it cam to collecting the "block" coverage for the NDBD.

    I wanted to share them here for others that might run into similar frustrations when doing testing code coverage.

    Gcov accumulates during each run information on which functions and lines of code are called and a total for the times called.

    The problem comes from when gcov dumps this information out. The actual data is dumped on the "exit" of the program.

    After much time tracking this issue down, it turns out that the NDBD code had been changed to use _exit() instead of just exit()

    What is the difference?

    Terminate the process after cleanup.

    Terminate process immediately.

    So by

      [Read more...]
    Give and Take
    Employee_Team +0 Vote Up -0Vote Down

    Have you ever looked at the source code of MySQL Workbench? There are quite some hidden gems there, e.g. the OpenGL canvas and our latest addtion: a new UI port of the famous open source edit control Scintilla. This port allows us to use this fantastic editor control natively on Mac OS X Leopard as a Cocoa framework.

    In fact, we are using Scintilla for a long time already, mainly on Windows. It allows us to give you a powerful editor interface for SQL code. This includes features like syntax highlighting, text markers (e.g. for marking syntax errors), code folding, code completion and many more. However, so far we could not use it on OS X. There is a Carbon port

      [Read more...]
    Formatting mysqladmin extended-status nicely
    +0 Vote Up -0Vote Down

    I always say that the ultimate MySQL tuning script is an expert human. To that end, I generally try to build tools that help a human be more productive with the raw information from MySQL. One of the things we look at during a performance audit is the MySQL status counters. It’s useful to look at a) absolute values and b) several incremental snapshots. I’ve written a small shell script called “mext” that can make this a little easier.

    It looks like this:

    baron@kanga:~$ mext -- mysqladmin ext -ri1 -c3
    Aborted_clients                               1      0      0
    Aborted_connects                              0      0      0
    Binlog_cache_disk_use                         0      0      0
      [Read more...]
    Join the MySQL Workbench Team!
    Employee_Team +0 Vote Up -0Vote Down

    If you are a Mac developer, interested in a new professional challenge and like what we have done with MySQL Workbench so far, please consider applying for our open position in the Workbench team.We are looking for an Objective-C / UI coding wunderkind who is also not easily scared by C++.

    You can be located anywhere around the world, only hard requirements are that you are comfortable with working from home, have a decent internet connection and are not too far from an airport.We would be happy to have you on the team.

    More details about the job below. To speed up the hire process please send your CV to workbench@sun.com. We are preferring people with open source background so please do not forget to include links to your open source work.

    Job Summary:

    Sun Microsystems is looking for a strong Mac UI developer with in-depth knowledge of

      [Read more...]
    A review of SQL and Relational Theory by C. J. Date
    +0 Vote Up -0Vote Down

    SQL and Relational Theory

    SQL and Relational Theory How to Write Accurate SQL Code by C. J. Date, O’Reilly 2009. Page count: 266 pages of “real” text, plus hefty appendixes. (Here’s a link to the publisher’s site: SQL and Relational Theory How to Write Accurate SQL Code).

    This is a very important book for anyone involved with databases. Before I say why, I need to apologize to Mr. Date. I tech-reviewed part of the book and did not care for

      [Read more...]
    Google Summer of Code 2009
    Employee +0 Vote Up -0Vote Down
    The time is just running and it's already time, again, for students to apply for this year's Google Summer of Code program. PHP and MySQL are among the list of 150 participating organizations. So if you're a student and are interested to learn how OpenSource works, do some networking with some famous people or just want a Google T-Shirt it's your time to take a look at the different idea pages (PHP, MySQL, others) or come up with an own idea and apply. Oh if accepted you even can earn some money as part of the program ...
    Showing entries 1 to 30 of 103 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.