Details
-
Bug
-
Resolution: Fixed
-
Medium
-
2011.10, 4.4.0, 4.5.0, 4.6.0beta1, 4.6.0
-
None
-
eZ Publish 4.4.0 with eZ Flow 2.2.0
Description
On a large scale site using eZDFSFileHandlerMySQLBackend especially delete operations are painfully slow.
After some monitoring and testing, queries not escaping SQL-wildcards where identified as the cause of this problem.
Here is an example query without escaping:
SELECT mtime FROM ezdfsfile WHERE name like 'var/ezflow_site/storage/images/media/gallery/test/206_test/1867222-1-ger-DE/206_test_image_660.jpg%'
Showing rows 0 - 0 (1 total, Query took 5.1395 sec)
and now with escaped underscores ...
SELECT mtime FROM ezdfsfile WHERE name like 'var/ezflow\_site/storage/images/media/gallery/test/206\_test/1867222-1-ger-DE/206\_test\_image_660.jpg%'
Showing rows 0 - 0 (1 total, Query took 0.0006 sec)
So the query with escaping is 8500 times faster.
Number of rows in "ezdfsfile" was ~ 1.400.000
A simple workaround is to add $value = str_replace('_', '_', $value);
to method _quote() in class eZDFSFileHandlerMySQLBackend.
diff --git a/kernel/private/classes/clusterfilehandlers/dfsbackends/mysql.php b/kernel/private/classes/clusterfilehandlers/dfsbackends/mysql.php index df30775..a954c73 100644 --- a/kernel/private/classes/clusterfilehandlers/dfsbackends/mysql.php +++ b/kernel/private/classes/clusterfilehandlers/dfsbackends/mysql.php @@ -1280,12 +1280,14 @@ class eZDFSFileHandlerMySQLBackend **/ protected function _quote( $value ) { - if ( $value === null ) + if ( $value === null ){ return 'NULL'; - elseif ( is_integer( $value ) ) + }elseif ( is_integer( $value ) ){ return (string)$value; - else + }else{ + $value = str_replace('_', '\_', $value); return "'" . mysql_real_escape_string( $value ) . "'"; + } } /** lines 1-22/22 (END)
I'm sure the very same bug is widespread in many ez sql handlers.
Would be great if someone with broad ez-knowledge could check this.
Steps to reproduce
delete a file in cluster setup
Attachments
Issue Links
- duplicates
-
-
- Closed
-
