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;
service mysql start
If you use ansible, you can just have this task
- name: mysql custom config
where files/mysqld-custom.cnf contains the following
# limits /var/lib/mysql/ibtmp1 to 100Mb-1GB
innodb_temp_data_file_path = ibtmp1:100M:autoextend:max:1G
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.
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.
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.
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.
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.
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.
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
Mydump and gzip of database dbuser:pass@localhost/dbname into remote machine “destination.com”, file ~/dbname.sql.gz
mysqldump -h localhost -u root -ppass dbname | gzip
| ssh firstname.lastname@example.org "cat > ~/dbname.sql.gz"
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)
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
Depending on the Zend version, the Zend_Db_Statement class might have a getter for it, so you can debug with
You can also – of course – enable the MySQL general log and monitor the log file
general_log_file = /path/to/logfile
general_log = 1