Posts Tagged ‘MySQL’

MySQL may use temporary tables during query execution. Ideally you would want to avoid this, since its an expensive and slow operation. It can be avoided by optimizing queries. Sometimes it can’t be completely avoided – in that case you want to make sure the temporary table is created as a “memory” storage engine table, since its very fast, as it is never written to disk and remains, as the name states, in memory. But, as the manual explains, there are some conditions, such as TEXT/BLOB columns, or a combination of GROUP BY/ORDER BY clauses that makes MySQL write the temporary table to disk as a MyISAM table. One can spot these queries by the EXPLAIN output:
[...] Using where; Using temporary; Using filesort
In that case performance depends on disk I/O speed. If there are multiple similar queries running simultaneously, they try to read/write a lot of information to the disk, and will become extremely slow.

Solution? TMPFS!

tmpfs is a filesystem, that resides in RAM/Swap, so if your server has enough available RAM, files written there will bypass disk I/O completely, and will perform significantly faster.

Now, “High Performance MySQL, Second Edition” claims that this solution is still not as good as a MEMORY table, since it requires MySQL to use some expensive OS calls to write & read the temporary table, but it is still faster than the disk based temporary table.

To set it up, just mount a tmpfs system on an empty directory (you should also add this to fstab):
mount tmpfs /tmpfs -t tmpfs
and edit my.cnf to make MySQL use that directory as a temporary directory:
tmpdir = /tmpfs
Be careful though, there is a bug in some versions that prevents this from working properly.

For more information, see this blog.

Tags: MySQL
Posted in Programming | 9 Comments »

TYPO3 has a table for backend users. Those users need to occasionally upload files to the fileadmin directory via FTP. Wouldn’t it be nice to configure the FTP server to pull from the be_users table and authenticate them, so that two separate locations for userdata don’t have to be maintained?

It’s possible. This guide documents how I was able to set up FTP on a Debian Linux server running TYPO3 4.2. Our TYPO3 installation uses MySQL 5.0 DB.

My FTP server of choice was pure-ftpd. Fortunately this server has MySQL authentication capabilities, they just had to be set up.

apt-get install pure-ftpd-common pure-ftpd-mysql

This will remove plain old pure-ftpd package if you have it, and install the MySQL enabled version, that can use a MySQL DB to authenticate users against.

Next step is to set up the configuration, so edit /etc/pure-ftpd/db/mysq.conf. You need to set some configuration variables:

• MYSQLServer: Point it to your MySQL server IP, or localhost
• MYSQLUser: DB username
• MYSQLPassword: DB password
• MYSQLCrypt: TYPO3 encodes be_users passwords with md5
• MYSQLGetPW: This query authenticates the user. Here is how I set it up:
  SELECT password FROM be_users WHERE username="\L" AND LEFT(username, 1) != '_' AND deleted=0
  In the first where clause, \L is replaced by the username of a user who is trying to login. The second clause is for security reasons – backend processes running as scheduled tasks require users with a prefix “_” (usually “_cli”). This clause prevents them from being awarded FTP access automatically. Third clause, of course, prevents deleted users from logging in.
• MYSQLDefaultUID: since each TYPO3 user might not necessarily have a user account on the server, its better to set the default UID of the file owner here.
• MYSQLDefaultGID: same as above, set the user group.
• MYSQLGetDir: This is a fun one. As you may know, TYPO3 has file mounts for users and groups, which restrict the user to a certain folder. We would want to restrict the user to the same folder, right?
  The disadvantage of the code below is that 1) it requires relative filemounts 2) it ignores multiple filemounts and 3) it only allows one usergroup per user. If a user has several filemounts selected this query will fail, and the user will be denied FTP access. This is a problem I was not able to resolve yet.

SELECT CONCAT('/srv/ftp',file.path) AS Dir
FROM be_users as user
JOIN be_groups as ugroup ON user.usergroup=ugroup.uid
JOIN sys_filemounts as file ON user.file_mountpoints=file.uid OR ugroup.file_mountpoints=file.uid
WHERE user.username="\L" LIMIT 1

The manual documents other options quite well.

Next I tried to connect to FTP, but got an error: ‘Can’t exec /usr/sbin/pure-ftpd’. I had to modify /usr/sbin/pure-ftpd-wrapper to call pure-ftpd-mysql instead of pure-ftpd. This may be different depending on which package you install.

You would also want to secure the FTP by limiting the users to their respective directory. This can be done by creating a file /etc/pure-ftpd/conf/ChrootEveryone with content ‘yes’. Now when they login, they will have access only to their file-mounted folder.

So at this point everything should work. Nothing ever works as planned though, but pure-ftpd writes log messages to syslog, so go there to pinpoint the problem in your installation.

Tags: DAM, ftp, MySQL, TYPO3
Posted in TYPO3 | No Comments »