Category Archives: DB and MySql

Mysql ibtmp1 taking too much space: solutions

Posted on by Elvis Ciotti

I recently had one of my AWS instances running out of disk space. Mysql server (version 14.14, running a few hundred Mb single databae) created a temporary file of over 11Gb at the path /var/lib/mysql/ibtmp1 and saturated the 16GB disk.

I solved that with this setting

innodb_temp_data_file_path = ibtmp1:100M:autoextend:max:1G

And also the following command, that sets fast shutdown, stops mysql, deletes that temp file, and start mysql again

mysql -u root -e "SET GLOBAL innodb_fast_shutdown = 0;"; 
service mysql stop; 
rm /var/lib/mysql/ibtmp1; 
service mysql start

If you use ansible, you can just have this task

- name: mysql custom config
  copy:
    src: files/mysqld-custom.cnf
    dest: /etc/mysql/mysql.conf.d/mysqld-custom.cnf
    mode: "744"

where files/mysqld-custom.cnf contains the following

[mysqld]

# limits /var/lib/mysql/ibtmp1 to 100Mb-1GB
innodb_temp_data_file_path = ibtmp1:100M:autoextend:max:1G

script to modify DB values filtering by column and rows

Posted on by Elvis Ciotti

One of my clients recently needed to implement an application-level encryption for some existing applications.

Unfortunately, the design pattern used for the database layer obliged me to write an independent script to browse the database and encrypt the data for each user using a user-specific two-way encryption key.

So, I ended up writing a simple utility class that applies a user-defined function (modifies) to the defined columns and records (via a filter query) of each database table.

It can be used for any relational database server supported by PDO (MySQL, SQL Server, MySQL, Oracle). I’ve only used it with MySQL but it should work smoothly with all the others.

Repository:
Db modifier https://github.com/elvisciotti/DbModifier

An example of usage is in the README, hope it’s clear enough.

You can basically use this to mass editing Db records with a function that is NOT easily implementable with a simple MySQL Query, e.g. encrypt the data with a custom PHP function.

Doctrine 2 + zf2 working with both SQL server and MySQL

Posted on by Elvis Ciotti

The web application I’m working on (PHP 5.3, zf2, Doctrine2, on MySQL/Apache) has recently raised interest from the clients and it now needs to be installed on their premises, often a Windows server( arghhh!). That meant we had to make the application compatible with both MySQL and SQL-Server.
Here I summarize the changes and the solutions we adopted.
Continue reading →

MongoDB + MySQL with Doctrine 2. Example of listener to maintain redundant data (alternative to joins)

Posted on by Elvis Ciotti

If you are developing an application with MongoDb, sooner or later you will face the need of solving a query that involves more than one collections (or a MongoDB collection with a MySQL table). In this case, an SQL join operation won’t be available.

If you have a small amount of data, you can perform two MongoDB/MySQL queries and then join the results at the application level, but – depending on your needs – that could be not feasible or turn out to be inconvenient. A possible better solution for that could be redundancy.

Example
Continue reading →

97 things every programmer should know: personal notes

Posted on by Elvis Ciotti

Last month I saw a book flying about the office, containing useful tips from developers. 97 suggestions from experienced developers. Amazon is still selling it, so I suggest you read it if work as a software developer or you occasionally dip into coding: 97 Things Every Programmer Should Know: Collective Wisdom from the Experts

I’ve finished reading it, and I took some notes while reading. Some concepts and suggestions were obvious, as I hear them many times from the community (blogs, conferences, colleagues) or I’ve already personally experienced them. I’ll paste those my notes here, with the hope that you can find something valuable from looking through them.

Continue reading →

NoSQL and MySQL notes

Posted on by Elvis Ciotti

NoSQL: If Only It Was That Easy at Marked As Pertinent

What am I going to build my next app on? Probably Postgres. Will I use NoSQL? Maybe. I might also use Hadoop and Hive. I might keep everything in flat files. Maybe I’ll start hacking on Maglev. I’ll use whatever is best for the job. If I need reporting, I won’t be using any NoSQL. If I need caching, I’ll probably use Tokyo Tyrant. If I need ACIDity, I won’t use NoSQL. If I need a ton of counters, I’ll use Redis. If I need transactions, I’ll use Postgres. If I have a ton of a single type of documents, I’ll probably use Mongo. If I need to write 1 billion objects a day, I’d probably use Voldemort. If I need full text search, I’d probably use Solr. If I need full text search of volatile data, I’d probably use Sphinx.

Which is faster: MySQL or MongoDB? Does it depend on the use case? – Quora

To me, the Mysql vs MongoDB benchmarks are revealing as they show that with beefy hardware, a NoSQL engine does not have to be faster than a well indexed Mysql Table, which might seem the case.

 

MySQL became the most popular relational database on the planet by turning its weaknesses into strengths and successfully leveraging its ubiquity.

 

It would be foolish to predict the same success that MySQL enjoyed for MongoDB, because the underlying market context has changed.

idbdata problem with MySQL

Posted on by Elvis Ciotti

How to shrink/purge ibdata1 file in MySQL – Stack Overflow

That ibdata1 isn’t shrinking is a particularly annoying feature of MySQL. The ibdata1 file can´t actually be shrunk unless you delete all databases, remove the files and reload a dump. But you can configure MySQL so that each table, including its indexes, is stored as a separate file. In that way ibdata1 will not grow as large. It was a while ago I did this. However, to setup your server to use separate files for each table you need to change my.cnf in order to enable this: [mysqld] innodb_file_per_table=1

MySQL REPLACE statement – When to use it

Posted on by Elvis Ciotti

MySQL implements an useful function – REPLACE INTO –  that developers should use to avoid coding logic already implemented at the DB level.

That function works exactly as an INSERT (same syntax), but it also performs a DELETE on the existing record when a duplicate-key occurs (same PRIMARY KEY or UNIQUE KEY on another column) in the same one atomic operation.
Note:  UNIQUE KEYS allows multiple NULL values. In that case there is no key violation and no replacement.
Note: DELETE operations are counted in the number of affected rows.
Note: the DELETE  triggers the innoDb “ON DELETE” .

Example: simple table with settings (containing pairs key->value)

CREATE TABLE IF NOT EXISTS `settings` (

`key` varchar(50) NOT NULL,
`value` varchar(255) NOT NULL,
PRIMARY KEY (`key`)
)

If we want to update a setting or create it when not existing, only one query is needed:

REPLACE INTO `settings` (`key`,`value`) VALUES ('version', 1)

Display full query in the Exception of Zend_Db_Statement_Pdo

Posted on by Elvis Ciotti

When working with Zend Framework 1.x and Zend_Db in particular, you might bump into an SQL problem like this

Syntax error or access violation: 
1064 You have an error in your SQL
syntax; check the manual that corresponds 
to your MySQL server version for 
the right syntax to use near ') 
ORDER BY `order` ASC' at line 1

OK, thanks a lot Zend, but what’s the query ?

the statement is kept inside $_stmt property of Zend_Db_Statement, and to display it, you need to acess the queryString property

$_stmt->queryString

Depending on the Zend version, the Zend_Db_Statement class might have a getter for it, so you can debug with

var_dump($zfDbStmt->getDriverStatement()->queryString);

You can also – of course – enable the MySQL general log and monitor the log file

[mysqld]
general_log_file = /path/to/logfile
general_log = 1