[TYPO3-dev] Typo3 and Oracle 11g
bart.dubelaar at logica.com
Thu Oct 13 14:31:12 CEST 2011
Éric Thibault wrote:
> As our company's system administrators want to migrate our Typo3 from MySQL to Oracle 11g database, is there tips / tricks / cons to watch for? Any insight would be appreciated!
> Thanks to all.
Below are some findings I reported after trying to move an existing TYPO3 installation from MySQL to Oracle.
I know Patrick Broens has once given a presentation on running TYPO3 with Oracle on a Dutch Usergroup event, but I don't know his findings.
I would like to hear your experiences in case you decide to move to Oracle.
Setting up environment
- An Oracle Client needs to be installed on the machine running TYPO3
- PHP needs to be extended with the OCI8 module for Oracle access
- NLS_LANG env of apache has to be set to utf8 for Oracle to handle utf8
Creating a database mapping
- Several table names of TYPO3 core extensions (caching framework & extbase) exceed the maximum table name length of 30 characters in Oracle. A mapping needs to be made for these tables before the database is set up. (This will be fixed in 4.6).
- When adding new extensions one will need to check if there are no table names, field names, or index names that exceed the maximum length.
Setting up a fresh database structure
- Setting up a clean database structure without additional extensions can be done in the 1-2-3 install tool mode. Using the regular install tool is not recommended.
- The compare function in the install tool cannot be used to check if all tables and fields are created correctly, it can’t see which tables and fields are already present.
- Using the install tool to set up a databasestructure when extensions are already present does not always work as expected. Some fields are not created. It is best to install these extensions later on.
- When installing a new extension, tables and fields will be created but as whith the compare function in the install tool the extension manager is not aware of which tables and fields already exist.
- Extensions that provide static info (staticinfotables) do not insert their data properly. The data is best inserted in the database directly via a CSV file exported from another running system.
Moving from an existing MySQL database
- Oracle recommends to use SQL Developer for migrating databases. SQL Developer is however not suitable due to the lack of control of the process. For instance all uid fields are changed in uid_ which is not necessary and unwanted. After much trial
and error no successful migration could be achieved.
- The single best method is to migrate the structure and data separately. First make a dump of the structure with mysqldump, then carefully remove backticks and comments. Then place this file in the typo3conf dir of TYPO3. After this the 123
install tool should be used to import the structure. Data cannot be moved in this way!
- Data is best moved with the use of the import export module. Make a t3d export of the root page and siblings. The import this file. Other methods to move data all failed, for instance NULL values where inserted as actual NULL strings.
- Again static info is not imported correctly, this should be imported by hand. For instance from a CVS dump from another system.
- First of all, running with a database absctraction layer will have an impact on performance. However, what counts is the net performance as the Oracle DB might be faster depending on the setup. So perfomance test should be done for a good
- The bare core and basic functions of TYPO3 run without big problems. However the OCI8 driver is more strict than the default MySQL driver. PHP warnings will be thrown when inserting data that exceeds field lengths, etc. As a result error
reporting has to be set up less strict.
- Many extensions that run fine on MySQL are not tested (or badly tested) on Oracle. Table definitions might be invalid or queries might be invalid on Oracle. Mostly these are simple things like the wrong kind of quotes in a query, but things
cannot be trusted to run out of the box.
- Tools to check if the database is still set up properly do not work (install tool).
- The built in search engine does not work, the produced query is incompatible with Oracle.
- Parts of TYPO3 that are used less frequently in the TYPO3 community cannot be trusted to work flawlessly out of the box, for instance problems might occur in workspaces.
- Updates of the core and extensions cannot be trusted to work out of the box as chances are high that they have not been tested with Oracle.
More information about the TYPO3-dev