Skip to content

Symfony & Doctrine Mapping problems with MySQL Bigint columns, and how to fix this

In the process of upgrading a Symfony project from version 2 to version 5, I came across an issue with a MySQL table that has a number of BIGINT columns. There had been no issue previously, but after updating to the newer symfony (and doctrine components) mySQL bigint columns were empty/null in my doctrine entities.

After some investigation I came upon this section of the Doctrine manual:

For compatibility reasons this type is not converted to an integer as PHP can only represent big integer values as real integers on systems with a 64-bit architecture and would fall back to approximated float values otherwise which could lead to false assumptions in applications.


To protect symfony apps running on 32 bit systems, Doctrine maps Bigint columns to the string type, and this broke the entity definition I was using, even though my application will only be deployed on 64 bit systems. I think that for most people this is the norm.

There are a few different ways to get around this issue, including type casting from string to integer/integer to string in your getters and setters, but if you have a lot of bigint columns across tables, that probably isn't a great solution. In this article, I present the solution I implemented, which utilizes a Custom Doctrine type to override the built in Doctrine behavior.
Continue reading "Symfony & Doctrine Mapping problems with MySQL Bigint columns, and how to fix this"

docker4lamp - A LAMP Docker development environment

I have made this project (Docker For LAMP) publically available on Github, under the MIT license.

The target user group is php developers who want or need a simple, minimal (yet very current) LAMP development environment. I want this to be friendly to new developers, and a useful alternative to localhost environments like xamp, mamp or wamp.

Ideally it offers the type of convenience and isolation of a vagrant lamp environment except with orchestrated containers. Continue reading "docker4lamp - A LAMP Docker development environment"

PHP Mysql support: mysql or mysqlnd?

So you are installing a modern version of php using one of the alternative repositories, and suddenly you are confronted with a confusing choice. You want support for mysql (mysqli or PDO-mysql) in your php program. But which one to choose?

First off, you should probably be using PDO. It's just a cleaner database interface when compared to mysqli, and also tends to be the supported option if you're using an ORM like Doctrine2.

But you probably have found that installing the PDO package doesn't get you support for MySQL.

So what are these 2 packages? Well let's see what yum under Centos shows us, once we've setup webtatic as a repo:


 * webtatic: us-east.repo.webtatic.com
==============================================
php56w-mysql.x86_64 : A module for PHP applications that use MySQL databases
php56w-mysqlnd.x86_64 : A module for PHP applications that use MySQL databases
 


In short, the mysql extension aka the mysql library is to quote Oracle/mysql
... a general-purpose client library
named libmysql.

This was the original php approach to supporting mysql. MySQL provided a client api library, and using that c library, a php extension was created that depends upon libmysql implementing the famous mysql_ functions that allowed php to talk to mysql.

The mysqlnd package (where nd stands for "native driver") is the fruit of a project to make mysql work optimally in the php language. Again to quote the mysql site:

The mysqlnd library is highly optimized for and tightly integrated into PHP. The MySQL Client Library cannot offer the same optimizations because it is a general-purpose client library.

The mysqlnd library is using PHP internal C infrastructure for seamless integration into PHP. In addition, it is using PHP memory management, PHP Streams (I/O abstraction) and PHP string handling routines. The use of PHP memory management by mysqlnd allows, for example, memory savings by using read-only variables (copy on write) and makes mysqlnd apply to PHP memory limits.


On top of these benefits are a number of interesting enhancements and support for plugins that might be of specific interest to you as a developer or sysadmin.

In general nothing should break in your code as the api should work the same under mysqlnd as it did with the old mysql library.

Conclusion

In summary, you want to use mysqlnd now and in the future.



Defined tags for this entry: , , , , ,

Calculate a person's age in a MySQL query -- continued

The serendipity cache blew up on me for this article so I had to split it into 2 parts.

DATE_ADD to the rescue


MySQL knows how to work with the Gregorian calender, and I believe it is easier to let MySQL do the work, rather than trying to figure out how to implement the different cases. I chose to implement the "last day of the month" technique. In this case, we'll do this by creating a mysql date based on March 1st of the current year, and subtract one day from it to get to the last day of February.

If you read my article on "Finding next monday" using mysql date functions, you would have seen how DATE_ADD() can be used to help solve a lot of problems, even though the name can be misleading when you are actually using it to subtract. Using it with the following test dates proves that mysql has fully implemented the leap year logic accurately:

Continue reading "Calculate a person's age in a MySQL query -- continued"

Calculate a person's age in a MySQL query

Recently a question was posed on the Phpfreaks.com forums, as to the best way for someone to store a user's birth date using the MySQL database. One person suggested using a varchar as they were most familiar with string functions. My answer was to use the MySQL date type.

I provided a quick "advantages of using date" comparison list:
Storing as a DATE
•A MySQL Date requires 3 Bytes of storage
•You can do Date arithmetic inside mysql (search for dates within ranges) and use mysql functions to calculate values directly in a query
•It intrinsically will only store valid dates
•You can format it in numerous ways

Storing as a Varchar
•A string will require minimum 8 bytes, or 10 with separators
•can't do any form of efficient native range queries
•can't reformat it easily in SQL
•will allow completely invalid dates

I thought a great proof of the benefit of this approach was to show that you could have MySQL calculate the person's current age in a query, using their birthday. Certainly with a string, using PHP for example, you would typically query the database to pull out the string and for each row, turn it into a PHP date and do some calculations in your code, and you'd arrive at the same place, but I wanted to show just how capable SQL -- and in this case MySQL can be especially when you use the native data types and some functions.
Continue reading "Calculate a person's age in a MySQL query"

SQL UPDATE for strings in MongoDB

MongoDB currently does not offer the equivalent of a SQL Update statement when you want to change the value of a string to something that is based on the current value. In my case I needed to find spaces and change them to underscores -- something done easily in php with str_replace(), and in MySQL with REPLACE.

If you had a table named foo with a column named "mystr" you could do this sort of update like this:


UPDATE foo SET mystr = REPLACE(mystr, ' ', '_');
 


MongoDB does have collection.update() that works with a series of "modifier" operations, but none of them are useful for doing modifications to the current value of a document field when that field is a string.

MongoDB does however support javascript, and the javascript replace() function implements a regular expression search and replace. I was able to accomplish the update using the forEach() method to process the resulting documents one at a time. While not as efficient as having mongo do this internally with a specially built update modifier, at least this method only needs to operate on documents that are known to require modifications, and could be run in the mongo interactive client easily.

Again, assuming the collection is named "foo" and the document field is named "mystr"


db.foo.find({ mystr: /[ ]+/ }).forEach( function(u) { u.mystr = u.mystr.replace(/[ ]/g, "_"); db.foo.save(u); } );
 


An existing patch promises to add a new update() modifier but until such a time as it exists, this approach can be used when you need to update strings in a collection, referencing the existing values in the transformation.
Defined tags for this entry: , , , , , ,