The LinkedIn MySQL DB Development group posed a questions on how to handle images. Naturally, the argument always goes: Should images be deployed in the database or the file system? I believe they should be stored in the database because the cost and time associated is too high with regard to managing files, a file naming schema, and backing up the file system discretely from the database.

Since there’s a significant difference between the backup of transactional data and image data, they should be placed in different databases. The imagedb database is where you would place the images and large text descriptions, as shown in the MySQL Workbench ERD:

The imagedb ERD splits the foreign key references back to the system_user table, which contains the individual user credentials. The …

Join 7500 others and follow Sean Hull on twitter @hullsean.

There’s a lot of talk on the web about scalability. Making web applications scale is not easy. The modern web architecture has so many moving parts. How can we grapple with the underlying problem?

Also: Why Are MySQL DBAs So Hard to Find?

The LAMP stack scales well

The truth that is half right. True there are a lot of moving parts, and a lot to setup. The internet stack made up of Linux, Apache, MySQL & PHP. LAMP as it’s called, was built to be resilient, dynamic, and scalable. It’s essentially why Amazon works. Why what they’re doing is possible. Windows …

One of my old students and lab assistants stopped by to show his fiancée the BYU-Idaho campus. It was a long trip since he lives in Macao, China.

He kindly brought me a copy of my Oracle Database 11g and MySQL 5.6 Developer Handbook in simplified Chinese. He’s holding it in the photo.

That makes three books translated into Chinese, which made my day. It’ll be interesting to see if the new MySQL Workbench: Data Modeling & Development book …

Just went through all my PHP testing against a fresh instance of Oracle with Zend Server Community Edition 6, and found these warnings, guess that’s pretty clean for the Oracle part of the installation. I didn’t notice it before because generally I do most of my PHP development against a MySQL database. I should have been configuring the php.ini file routinely, as qualified in this PHP forum discussion.

      Warning: oci_set_client_info(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in C:\Program Files …

As we continue to see MySQL being implemented in bigger and more challenging environments, we are working to ensure Zmanda Recovery Manager for MySQL (ZRM) matches this growth and provides a comprehensive, scalable backup management solution for MySQL that can easily integrate into any network backup infrastructure.

The latest release of ZRM for MySQL is a significant next step, bringing disk space and network usage optimization and enhanced backup reporting, along with simplified management to help configure backups quickly and intelligently.   Additionally, ZRM for MySQL 3.5 now supports backup of MySQL Enterprise Edition, MySQL Community Edition, SkySQL, MariaDB, and MySQL databases running on latest versions of Red Hat Enterprise Linux, CentOS, Debian, Ubuntu and Windows – giving you an open choice for your MySQL infrastructure, now and in future, …

Percona’s Kenny Gryp leads a recent MySQL workshop

Percona will be in Chicago and London the week of April 8th delivering two 2-day MySQL workshops. For our MPB readers, we are offering a 15% discount. Just use MPB15A when purchasing your tickets to one or both MySQL workshops.

• Scaling and Optimization for MySQL on Monday & Tuesday, April 8 & 9
  • This MySQL workshop will cover:
    • Apply architecture scaling strategies that make the most sense, including …

Last October I posted an example and description of a common_lookup table. It was intended to show how common_lookup tables support drop down selections in web forms. However, it wasn’t adequate to show how they work with existing data, and the function only supported fresh queries.

This post goes to the next level, and shows how to use foreign keys to preselect values for display in web forms. It also rewrites the prior function so that it supports querying existing data and inserting new data.

Let’s start with data stored in join between two tables – the member and contact tables. The internal lookup uses the customers name from the contact table to find the membership account information in the member table.

1
2 …

It’s always interesting when somebody asks why they got an error message, and especially sweet when you’re working on something related that lets you answer the question. They were using MySQL Workbench and wanted to know why they couldn’t open a SQL script file by clicking on the Scripting menu option.

As I explained to the individual who asked, you should always click the Edit SQL Script link in the SQL Development section of the MySQL Workbench home page to work on SQL scripts. The Scripting menu option supports Python and Lua plug-ins development and scripts.

They did the following initially, which led down the rabbit warren and left them stumped because they don’t know anything about Python or Lua. This is provided to those who choose to experiment with this advanced feature of MySQL Workbench.

…

Somebody wanted an example of how to handle column values using PHP to query a MySQL database. While I thought there were enough examples out there, they couldn’t find one that was code complete.

Well, here’s one that works using a static query. If you want to use a prepared statement, check this earlier post.

<html>
<header>
<title>Static Query Object Sample</title>
<style type="text/css">
  /* HTML element styles. */
  table {background:white;border-style:solid;border-width:3px;border-color:black;border-collapse:collapse;}
  th {text-align:center;font-style:bold;background:lightgray;border:solid 1px gray;}
  td {border:solid 1px gray;}
 
  /* Class tag element styles. */
  .ID {min-width:50px;text-align:right;}
  .Label {min-width:200px;text-align:left;}
</style>
</header>
<body>
<?php
  // …

Somebody asked about the possibility of putting DML statements inside MySQL stored functions. DML statements like the INSERT, UPDATE, and DELETE. When I said, “Yes, you can put DML statements inside functions.” They showed me the error they encountered, which is only raised at compilation when you put an explicit COMMIT statement or a Data Definition Language (DDL) statement (CREATE, ALTER, DROP, or RENAME) inside a MySQL function. The actual error message displayed is:

ERROR 1422 (HY000): Explicit OR implicit commit IS NOT allowed IN stored FUNCTION OR TRIGGER.

While an explicit COMMIT is obvious when placed inside a function, the implicit COMMIT statement isn’t obvious unless you know a DDL statement generates one. This means you can’t include any DDL statement inside a stored …