Skip to content

MySQL and a known bug since 2003 about the auto_increment value

We detected a bug that is known since 2003.

The value of an auto increment column is set back to zero when you are having an empty table and restarting the MySQL DBMS. We run into this issue by using the auto increment value as a history id into a second table.

How can you work around this issue?

The easiest way is to order by id descending on the second table or to setup a "start up" shell script that calculates and sets the auto increment value.

Translate to de es fr it pt ja

"ERROR 1148 (42000) at line 1: The used command is not allowed with this MySQL version" after upgrade percona version 5.5.35-33.0 to 5.5.36-34.2

So the setup is the following, debian squeeze and percona version 5.5.35-33.0.
I had done an upgrade from 5.5.35-33.0 to 5.5.36-34.2-648.squeeze and the following error created an red monitor on my jenkins.

ERROR 1148 (42000) at line 1: The used command is not allowed with this MySQL version

After searching on the web, i found this entry on the web.

sudo vim /etc/mysql/my.cnf
# add follwing entry to fitting locations
local-infile=1 # /etc/init.d/mysql restart

Get your current used version?

# mysql --user=root --password=

Check if changes are working?

mysql --user=root --password=


Translate to de es fr it pt ja

FrOSCon - Database DevOps With Flyway, Git, Maven, MySQL And Jenkins

By Michael Hüttermann.
Thanks for the book again, i will try to write a review about it this year :-).

What Is This Talk About

  • what is a DevOp
  • the goal
  • the problem
  • the solution
  • recipes and pitfalls
  • examples - not in this documentation
  • demo (MySQL, Flyway, Jenkins, Git, Maven, Gradle, Sonar, Vagrant) - not in this documentation

How Is Part Of The Team

  • everybody how is envolved in the process of creating the software

What Is Not A DevOp

  • is already a buzz word
  • it is not a new project role
  • its also not a new tool-suite
  • devOps is not a new department / business unit
  • *

What Is A DevOp

  • development and operations
  • better communication
  • improved collaboration

The Goal

  • shared incentives
  • holistic metrics
  • common processes (like kanban)
  • shared tools (same deployment tools for development and production for example)
  • high automation degree
  • improve and accelerate delivery - downsize the batch size
    • small releases
    • downsize the changesize to keep up the frequency
    • improve the cycle time (from bug finding/feature request to release the patch/feature)

The Problem

  • a lot of completed functions/features facing a slow release cycle
    • release small, release often
  • problem between development (want feature online) against operation (want stable and "relaiable" software)

The Solution

  • value stream map
  • continuous integration
  • continuous delivery (each commit moves system into stable and releaseable version)
  • continuous deployment
  • development and operation is merged together to create a infinie loop of release and feedback (both directions)
  • share knowledge and experience

Define clearly what is a release (new feature, bugfix, and so one). Again DevOps is not a new layer between the two areas. Responsibilites is still clear to reproduce scenarios (automate as much). It is about people, not about tools. Implement culture of communication. Try to find conceptual deficits (also software design problems) as early as possible. Add a version number to your database layout (most likly same release number than code) - flyway, liquibase, self-made

The Area Matrix

  • area 1 - extend development to operations
    • practice
      • use tools like puppet/chef/cfengine to provision environments from versioned code
    • goal
      • fast feedback through automations
      • reuse of code and tools
      • reliability of delivery process and provisioning
  • area 2 - extend operations to development
    • practice
      • provide monitoring and log files to development (or automated database snapshots to reproduce bug on development machine)
    • goal
      • share information about state in production
      • enable development to improve
      • enable development to trace production incidents
  • area 3 - embed development into operations (end user experience is goal to reach)
    • practice
      • set stability and capacity as development goals
    • goal
      • align goals, share incentives
  • area 4 - embed operations into development
    • practice
      • operations gives feedback about the design of the application that is under development, early and often
    • goal
      • avoid not releaseable design

Recipes And Pitfalls

  • automate to ensure repeatability and foster collaboration (build once on a build server and release)
  • consider kanban (scrum is more for development but bad for organisation)
  • consider thorough change and config management - content management
  • track to ensure traceability (which build/release contains which tickets/bugs - artifactory)
  • monitor to support accountability (nagios and everything)
  • dashboard what you are doing (talk to each other what you are doing or what you have done)
  • use version control to ensure reproducibility (no manual tweaking after checkout)
  • consider pipelines/process from "put in feature/bugfix to release"
  • create (executable) documentation to, well, document (try to define goals and what it needs to reach it)
  • align goals and incentives of devs and ops (small cycle time)
  • ask and help, open culture of communication
  • respect your colleagues (stability against features, both leads to great user experience)
Translate to de es fr it pt ja

FrOSCon - How To Make Money From Open Source Today

Gosh, one of the fastes talks i joined in the past. Nevertheless, the talk was full of informations. Mr. Widenius is an incredible likeable person since you feel the force he is up to presenting open source as a workable business concept.

Based on the speed of the slide switches and the enormous number of slides, there are a lot of gabs in my blog entry (aka talk record).

By Michael "Monty" Widenius, one of the creator of mysql and now part of the mariaDB team.

What Is The Talk About

  • how to make a good open source project
  • how to create money of that
  • understand how open source works
  • create an open source project
  • participate, drive or fork an open source project
  • try to create a company around an open source code

Good And Bad About Open Source


  • more developers
  • more spread
  • much better code
  • works well for a lot of projects


  • not enough money to pay developers (most at the beginning)
  • hard to get money and investors for projects (you have to make consulting and so on to get investors)
  • investors, in general, don't belive that a open source company can make enough money

Things To Clear Up

Top Reasons For Creating An Open Source Project

  • solve one of ones personal problems (mysql/php)
  • relicense and existing closed source porject to open source to get the benefits of open source (netscape)
  • get more developers on an internal company tool/project
  • wanting to earn money and at the same time do something good
  • wanting to give something back to the comunity
  • ...

Developing Software Open Source Is In Human Nature

  • you solve your own problems and get free help and development efforts from others while doing it
  • you participate to increase your reputation
  • you use open source because its less expensive
  • ...

Before Starting A New Open Source Project

  • check if there is already an existing actively developed project (it is always better to participate then to do new project or fork it)
  • do some reseach why old and dead projects are failed
  • find a company or a group of users that wants to work with you to define the scope of the project

Its Not Just Software

  • most importent part is to create a communiy
  • you need to interact with that community
    • web pages
    • forum or knowledgebase, email lists, bug system
    • documentation and localization
    • packages, build systems
    • respository

You Need A Good Team And Active Community

  • mark a designated active leader (linux) or leaders (MariaDB/PostgreSQL) that have the respect from the community
  • ...

Transparency Is Critical For Long Time Success

  • a open development model
  • clear guidelines
  • clear license and business model
  • extensive documentation
  • be clear about your roadmap and release schdule
  • good open review process of patches that enforces quality
  • be transparent with your plans and let users influence them
  • be open about your bugs

Communicate With Your Community

  • attend conferences and talk about your product
  • listen to your users and either do it or help them to help themselves
  • make it easy for people to communicate with you and find informations about your product
  • ensure that your faq is up to date
  • ...

Be Good Open Source Citizens

  • building a supportive community later in the game is extreamly hard - you have to do it at day 1
  • ...

You Need To Get The Product Out There And Used

  • release early, release often
  • aim that each release should be bug free enought to be usable in production
  • work with the distributions and cloud providers to get your product in there
  • use a good open toolset ...

Doing Business With Open Source

Open source is a philosophy and a development model.

Open source does not gurantee that you will make enough money.

Different Kind Of Open Source Products

  • products developed by the community
  • products that are tools for the company and released as open source to get more community development
  • small team or company that is developing and driving ...

Questions To Consider When Creating A Company

  • do you plan for a virtual company (no offices)
  • are you creating a company of equals (hacker companies where company is owned by employes)
  • do you want to concentrate on services or development
  • do you plan th have a big community or work with a few big companies
  • do you plan to take in investors (if yes, you need an exit plan)

What Business Model To Choose

  • service company
    • man powered services (support, training, consulting)
    • valuation 2 times revenue
  • software company
    • licensing, software as a service (SAAS), subscription
    • valuation 10 times revenue plus x number of users

What Is The Final Goal With The Company

  • sold on the market (quick and high profit for owners, unpredictable future for employees)
  • go public on the stock market
  • owned by founders, employees
  • create an open source foundation

Why Go Open Source

  • spread the product more quickly
  • get some part of the development done elsewhere
  • get things more tested and more bug reports
  • possible to get development done in "not business critical" directions
  • easier to find good developers, partners and customers

In general, more market recognition, feedbacks ...

Reasons For Users To Trust A Open Source Vendor

  • more trustworthy as they depend on trust to survive
  • no vendor lock in (can fork it or publish patches each time)
  • little risk for trap doors

Benefits For Developers Using Open Source Vendor

  • easy to get access ...

Benefits For Big Business / Countries Using Open Source

  • no license costs
  • not depending on vendors (from different countries)
  • ...

When Go Open Source

  • ...

How To Choose An OS License

  • what is your business idea ...

Business Models To Use With Open Source

  • open core model (SugarCRM)
  • dual licensing model
  • service models
  • subscriptions

Open Core

  • its a closed source business model (like oracle and mysql)
  • ...
  • for cummunity developers, the worst possible offer

Dual Licensing

  • used first by ghostscript and mysql
  • can only be used if you have full rights to all the code
  • give same code under two licenses
  • companies that can not use the gpl have to buy the closed source version from you
  • only works well for infrastructure, easily embeddable produts like libraries or databases

Business Source (Delayed Open Source)

  • not an open source license
  • source code is available from start but using it commercially you have to pay
  • after x years, the code automaticly converts to some open source/free license
  • better than open core
  • investor friendly (early adaptors have to pay)
  • forces good behaviour
  • you to do frequently releases
  • you to innovate and fix bugs to ensure users wants it
  • ensures that company is bought by "bad entity" that just wants to close down the project
  • project can be take over if something happens to the company

Recommendatins For Business Source

  • 3 to 5 years before it becomes open source
  • target that 1/100 or 1/1000 users have to pay
  • free version and commercial should be identical

The Importance Of Selling Licensing

  • mysql was only possibly with the licenses
  • very hard to get companies to pay
  • ...

Business Differnces Between MySQL and MariaDB

  • ...
Translate to de es fr it pt ja

Propel ORM, schema.xml, the missing autoIncrement value and a not working "$propelObject->reload()" with mysql greater 5.1

So i had to evaluate a migration of an existing application from mysql 5.1 to mysql 5.5. Yes i know, the gap between 5.1 and 5.5 is tremendous but the system was working without any problems for a long time of periode.

After switching a test environment from 5.1 to 5.5 there was one unittest failing. What was so special in that given unittest? For a reason, we needed to execute the native propel method "reload" right after a "save". The test was failing because of an empty value for the "id". The representing database table has a column id with an autoincrement flag. With mysql 5.1, everything is working as expected. The id was available after the reload so everything was fine.

After switching to 5.5, no id was available after calling "save". This leads to the fact that propel was failing by executing the "reload" method. Switching back to mysql 5.1 and the error was gone. After search in usergroups, issuepages i still could not find any matching issue or entry. Finally i presentend my problem on the #propel channel in freenode and got some feedback. One guy had experienced the same error in the past. He could remember that this problem occures while switching to mysql 5.3 or 5.4. Niceguy exptom wrote that goosed him with my report and he will give the problem a debug session try.

On the next day, he quickly responded and presented his results. He (if he is a he and not a she) told me he could takle it down to a missleading schema.xml. The column where the error occures had a missing autoIncrement="true" value. I took a look into my current schema.xml and bam same flaw.

The rest is quite easy. Update schema.xml, rerun propel generation and testing. Everything is now working fine. Thanks to exptom again. I guess the schema.xml would have been one of the last areas i had debugged.

Translate to de es fr it pt ja

PHPUGHH130611 - composer introduction and MySql Database Clustering


Mindworks Jarrestraße 42A Hamburg

InnoGames Game Jam - By Frank Sons

  • 48 hours to develop a game
  • Can work alone or in pair
  • Free food and drinks
  • From 21 June to 23

Game Jam

So Coded - A Web Conference In Hamburg - By Ole Michaelis

  • A lot of cities currently have conference
  • Hamburg also should have one
  • A lot of speakers from hamburg (and germany also ;-))
  • Its not only for java script or php guys
  • 19 - 20 of September 2013

so coded

Introduction To Composer - By Till Klampäckel

Composer - What is it?

  • Dependency management
  • Helps to keep components as components (with a maintainer)
  • Breaking up dependencies for development (and what is needed for production system)
  • Deployment tool
  • Update your dependencies
  • Generates autoloading

Composoring Your Code

Simple Example For composer.json

    "name": "net_bazzline/example-library",
    "licenses": "GPLv3"


git tag -a 1.0.0 -m "My first release"

Consuming - Using A Library (By Using Packagist And Git)

    "name": "artodeto/my-application",
            "type": "vcs",
            "url": ""
            "type": "package",
            "package": {
                "name": "company/legacy-code",
                "version": "1.2.3",
                "dist": {
                    "url": "https://dev.mycompa.ny/code.tar",
                    "type": "tar"
    "require": {
        "net_bazzline/example-library": "*"


sudo su
mkdir -p /usr/local/bin/composer/
cd /usr/local/bin/composer/
curl -sS | php

Deployment - Things That Can Go Wrong

  • Github is down
  • Minimum stability or prefer stable (use dev or oder stability)
  • Versioning is hard (require a special version like 1.2.*)



  • Something similar to composer exists for java script
  • semantic versioning

MySql Database Clustering - by Ulf Wendel ([email protected])

  • great speaker -> try to get a free presentation :-) Ulf Wendel


  • Availability
  • Scalability
  • DistributionTransparency

What Kind Of Clusers

  • Transactions (where)
  • Synchronization (when)

Important Keywords

  • Concurrency control
    • Atomic commit
    • Atomic broadcast (virtual synchrony offering total-order delivery)
  • Certification: detect conflict
  • Quorum (better than ROWA - read from one, write to all?)
  • Fault Tolerance
  • Virtual Synchrony
  • Reliable, delivered vs receieved (for example, update transactions should receive the application layer at the same ordering)
  • MySQL DBMS (with a Reflector and Replicator connected via Group Communication System (GCS) - not available so far)
  • MySQL Cluster Replication (hybrid, extends MySQL with a Reflector Plugin (Database engine: NDB Storage Engine and) and Replicator (NDB Data Node and Replicator (NDB Data Node)) with "fire and forget" api
  • Partitioning (auto sharding)
  • Tune your partitions
Translate to de es fr it pt ja

mysql set variables, convert timestamp to date and substracting/adding time to a timestamp

SET @expiredtimestamp = DATEFORMAT(NOW() - INTERVAL 23 MINUTE, '%Y-%m-%d %H:%i:%s');
SET @validdatetime = DATEFORMAT((NOW() - INTERVAL 42 DAY), '%Y-%m-%d');

SELECT column_with_datetime, @expiredtimestamp AS 'expiredtimestamp', @validdatetime as 'validdatetime' FROM my_table WHERE column_with_datetime >= @valid_datetime;


Translate to de es fr it pt ja

howto - speed up mysql constantantly with increasing tablesize

The startingpoint is the following. You have a table with increasing content. Due to this fact, somebody added a timestamp or a created_at column. Now a requirement pops up telling you "we have to get a quick overview of entries per day and we want to paginate over the results".

First idea is add a index on the created_at column. But your table is growing and another index means also more workload for the db to write an entry. To implement the pagination, you implement the usage of a limit. But the limit doesn't really speeds up your pagination when you try to get results for the last pages. The problem for the limit is, that mysql needs also to go over the results you want to leave of (because of the offset you have to provide).

So what to do? Assuming you have an unique key that is an autoincrement value, you can use this to reach your goals (and don't need another createdat column and write intensive index). But for the per day pagination you also need a little helper. One idea is to create a table that holds the start unique id for per day or you create a table per day or month. Since the second idea has too much drawbacks for me, i will explain the first one a bit more in detail. The idea is to create a table with columns id, date, first_id_for_the_day. Now you can easily deal with the question "what id's are entered per day". Now you just have to add to you query a "BETWEEN idofsearchdate AND (idofsearchnextdate - 1)". Of course, you still need the limit but the database now is using a smaller scope (limited by the between clause), so it shouldn't be that big deal for it :-).

Translate to de es fr it pt ja

howto - mysql count with unique entries only

Ever tried to count for a number of entries and figured out that the opposite/control query does not provide a fitting number? Maybe you forgot about the fact, that you have selected multiple entries per id. You can easily adapt your query to count only unique entries.

SELECT COUNT(DISTINCT `my_table`.`my_column`) FROM `my_table` AS `t` [ LEFT JOIN `my_second_table` AS `t2` ON (`t`.`id` = `t2`.`my_table_id` AND `t2`.`is_online` = 1) ] [ WHERE `t`.`my_column` = 'my_value` ]

I have also added this to my github howtos, fork for free ;-).

sql count function with examples

Translate to de es fr it pt ja

howto - mysql delete from table as t

You try something like

DELETE FROM my_table AS t WHERE ...
and you get back an error. The problem is, that you are using an alias for your table and mysql can not figure out where to delete from.

The solution/fix is simple

DELETE t FROM my_table AS t WHERE ...

I found the answere here

Translate to de es fr it pt ja

howto - add one second / add time to a date column

Nothing much to write. You want to add a second or some time to an available column.

-- add a second to current time ADDTIME(`my_table`.`my_date_field`, '00:00:01')

Want to know more? Check the available man page about time and date at

Translate to de es fr it pt ja

mysql delete with subquery using a limit

So, you are want to delete entries by using a subquery and the limit and all you get back is something like the following:

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

The solution is a little bit tricky and costs a bit performance but it is working, try the following:

DELETE FROM my_table WHERE IN ( SELECT * FROM ( SELECT FROM my_other_table WHERE my_other_table.condition = 'My condition' LIMIT 0, 100 ) as deleteTable);

Translate to de es fr it pt ja

MySQL - While do procedure example

Just a small example of how write a procedure / function in mysql (5.x) by using a simple while loop for inserting (example testdata) into a database.

DROP PROCEDURE IF EXISTS doWhileArtodeto; DELIMITER // CREATE PROCEDURE doWhileArtodeto() BEGIN DECLARE int1 INT DEFAULT 1; DECLARE int2 INT DEFAULT 50; int1loop: WHILE int1 <= int2 DO INSERT INTO `my_db`.`my_table` (`name`,`value`) VALUES('artodeto',int1); SET v1 = int1 + 1; END WHILE int1loop; END// DELIMITER ; CALL doWhileArtodeto(); DROP PROCEDURE doWhileArtodeto;

Feel free to use. It should be self explanatory, if not, do not use it (remember, you should always know what code you copy and what you do).

If you want to know more, try the following links. Procedure tutorial Manual about while on

Translate to de es fr it pt ja

Web - Owncloud 2 veröffentlicht

Owncloud wurde im Januar 2010 von KDE-Entwickler Frank Karlitschek beim Camp KDE 2010 initiiert. Er stellte fest, dass die meisten Anwender einen einfachen Zugang zu Anwendungen, eine Anbindung an soziale Netze und einen standortunabhängigen Zugriff auf Daten wünschen. Die Tradition, in der ein Anwender an lokale Daten gebunden ist, unterliegt mittlerweile einem Wandel, denn die Zahl der Rechner, die ein Anwender einsetzt, wird immer größer, auch durch Tablets, Netbooks und Mobiltelefone. [...] Kalender und Kontakte kann man im Web verwalten und mit Anwendungen wie Thunderbird, Kontact und Evolution nutzen. Außerdem kann Owncloud Speicher für andere Webanwendungen bereitstellen, wofür das Protokoll von verwendet wird. Mehrere Anwendungen können dieses Protokoll bereits nutzen, damit kann man auf proprietäre Anbieter von Web-Speicherplatz verzichten oder problemlos von diesen zu Owncloud wechseln.


Nach einem kurzen Test wird ein erweiterter Test folgen. Wenn man auch nicht alles benötigt hat es einige grundlegende Funktionen, die interessant sind. Daneben scheint die PlugIn-Entwicklung einfach zu sein.

Translate to de es fr it pt ja

Change session lifetime for phpmyadmin

Be aware that this can be security problem if you increase the session lifetime on a productive machine. Just do it on your local development machine where the database does not handle any sensitive data or informations. So far for the service announcements ;-).

Are you tired of logging in every 30 minutes to the phpmyadmin (currently it is unimportant that there is a mysql cli and tools like tora outside)?

Just edit your configuration file. This file should be in the path "/etc/phpmyadmin" (on debian) and is called "". In this file, you just have to add the following line to increase the session time to four hours.

$cfg['LoginCookieValidity'] = 14400;

You want to know more? Take a look at the phpmyadmin wiki

Translate to de es fr it pt ja

Building a cms with zend framework

Just by searching for a new technical book i stumbled over Pro Zend Framework Techniques: Build a Full CMS Project.

Well, the price in my local bookdealer was very cheap so i gave him a try. After finishing reading, i must admit that the book touches nearly all sticking points i was falling by learning using the zend framework. The book also shows me more ;-). I like the way the book is written. You begin with easy tasks and refactor the code if needed.

So what you get for your money? 230 Pages full of information. The number of pages guarantees that there is no place for boring stuff. This book presents you straight forward informations about:

  • application.ini
  • Template and Layout
  • Form, Form_Element
  • Working with Zend_Db_Table
  • Zend_Navigation
  • Zend_Auth
  • Zend_Acl
  • Zend_Search
  • Setup Modules
  • The use of DB_Profile
  • Zend_Cache
  • Zend_Translate

If the price fits to your pocket, give him a try :-).

Translate to de es fr it pt ja

Sourceforge - projectlist 110823

After browsing trough the, my tab group with "software" to look at counts 15 tabs. Hopefully i can/will check this software. Otherwise, if you checked it, write a comment.

  • webmail program like roundcube

    IlohaMail is a PHP based lightweight full featured multilingual webmail program with IMAP and POP3 support. IlohaMail also includes a full-featured contacts manager, bookmarks manager, and scheduler.


  • web file management

    AjaXplorer is a PHP rich-client browser for managing files on a web server without FTP. Implements usual file actions, online zip browsing, text files edition and images preview. Users management system and multi-languages.

    ajax filemanager

  • web instant messenger

    AJAX Chat is a fully customizable web chat implemented in JavaScript, PHP and MySQL which integrates nicely with common forum systems like phpBB, MyBB, PunBB, SMF and vBulletin. A Flash and Ruby based socket connection can be used to boost performance.


  • phpFreeChat is a free, simple to install, fast and customizable chat that uses by default files for message and nickname storage. It uses AJAX to smoothly refresh and display the chat zone and the nickname zone.
  • web proxy

    Lightweight, PHP-based Web Proxy that can utilize whatever remote connecting ablities your server has to offer. It should work out of the box. No configuration needed.


  • web desktop/os

    eyeos is an open source web desktop following the cloud computing concept, written in mainly PHP and XML. It acts as a platform for web applications written using the eyeOS Toolkit. It includes a Desktop environment with 67 applications and sys utils.


  • website heatmap

    ClickHeat is a visual heatmap of clicks on a HTML page, showing hot and cold click zones. Requires Javascript on the client to track clicks, PHP and GD on the server to log clicks and generate the heatmap


  • web human resource management

    OrangeHRM is an Open Source Human Resource Management System that covers Personnel Information Management, Employee Self Service, Leave, Time & Attendance, Benefits, and Recruitment. Tags: HRM, HRMS, HCM, HRIS, EHRMS, Human Capital Management


  • open source nas system

    Openfiler is a browser-based network storage management utility. Linux-powered, Openfiler delivers file-based Network Attached Storage (NAS) and block-based SAN in a single framework. It supports CIFS, NFS, HTTP/DAV, FTP, and iSCSI.


  • application security

    The Open Web Application Security Project (OWASP) software and documentation repository.


  • running native linux code on windows with andLinux

    andLinux is a complete Ubuntu Linux system running seamlessly in Windows 2000 based systems (2000, XP, 2003, Vista, 7; 32-bit versions only). This project was started for Dynamism for the GP2X community, but its userbase far exceeds its original design. andLinux is free and will remain so, but donations are greatly needed.


Translate to de es fr it pt ja

mysql import big *.sql files using the cli

If you need to import big *.sql files into your mysql dbms, just log in using the cli support on your shell.

mysql - u -p

After that, change to your database ("show databases;" and "use mydatabase") and use the following command to insert the *.sql file.

>mysql source /path/to/source.sql

Translate to de es fr it pt ja