Database import [message #95] |
Wed, 21 June 2006 16:27 |
David Lee
Messages: 44 Registered: June 2006
|
Member |
|
|
The database import routine appears to attempt to import any/all databases on the mysql server. It also appears to have problems with MiXeD case database names.
(MiXeD case is on of Tony's pet hates - see his article on it - and I realise he has a point.)
I was trying to import a database called "KeyControl", but the system first found another database called "KCbackupMar06" and tried to import it in the lower case, giving the error
Fatal Error: MySQL error: 1049 : Unknown database 'kcbackupmar06'
Error in line 1286 of file '...../includes/dml.mysql.class.inc'.
However, it had created an entry on the List Database screen with a description "KCbackupMar06", subsystem "?", and databaseid of "kcbackupmar06".
Trying again, it found my "KeyControl" database, but changed it to "keycontrol".
I am now out of time tonight to check
1) What does the documentation say?
2) Can I manually alter the imported "keycontrol" to "KeyControl" and bypass the problem?
3) Do I have to change the database name, which may then affect my access of the database (for reports) via Open Office database
4) Can I have some databases on my mysql server accessed only by Open Office, and unknown to RADICORE?
I hope this post gives pointers that help others.
David Lee
|
|
|
Re: Database import [message #100 is a reply to message #95] |
Wed, 21 June 2006 17:24 |
AJM
Messages: 2363 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
I have tried creating database names in mixed case with phpMyAdmin, MySQL-Front, MySQL Administrator and MySQL Query Browser without success - the names are always converted to all lower case.
The only way I can create database and table names with mixed case (on my Windows PC at least) is to create the files outside of any MySQL utility.
When the dict_database record is created the value which appears in database_id is downshifted and the subsys_id is set to '?' - this is normal behaviour. Querying the MySQL server to get any table names and column details works regardless of which case is used - sql queries are insensitive to case. The framework will use lower case for all database, table and column names without any problem.
In answer to your question - no, it does not matter if you have any databases in the dictionary which are not used by any scripts which are run under the Radicore framework.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
Re: Database import [message #104 is a reply to message #100] |
Thu, 22 June 2006 13:30 |
David Lee
Messages: 44 Registered: June 2006
|
Member |
|
|
Using phpmyadmin, I have no difficulty creating a database with both UPPER and lower case characters in the name.
However, my server is running LINUX, so mysql and apache are both running under that operating system, which probably explains the difference. My workstation is currently running windows 2000, but is dual boot, with a Linux option.
If you need any more details, or want me to do any more tests, then let me know.
Using phpmyadmin, I corrected the case in dict_database. Importing tables then worked ok.
However, when I then tried to import the columns of the first table, RADICORE tried to access the lower case version of KeyControl and crashed, as shown below. I will investigate further.
Fatal Error: MySQL error: 1049 : Unknown database 'keycontrol' (# 256).
SQL query: SELECT SQL_CALC_FOUND_ROWS dict_column.*, dict_table.table_desc, mnu_task.task_desc FROM dict_column LEFT JOIN dict_table ON (dict_table.database_id=dict_column.database_id AND dict_table.table_id=dict_column.table_id) LEFT JOIN menu.mnu_task ON (mnu_task.task_id=dict_column.task_id) WHERE dict_column.database_id='keycontrol' AND dict_column.table_id='cylinder' ORDER BY column_seq asc
Error in line 1082 of file '/projects/includes/dml.mysql.class.inc'.
Script: /radicore/dict/column_import.php
|
|
|
|
Re: Database import [message #106 is a reply to message #104] |
Thu, 22 June 2006 15:00 |
AJM
Messages: 2363 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
David Lee wrote on Thu, 22 June 2006 18:30 | Using phpmyadmin, I have no difficulty creating a database with both UPPER and lower case characters in the name.
|
I think I must have the 'lower_case_table_names' option set in MySQL.
Even when I managed to create a database and table names with upper case characters I had no trouble with importing the details as the SQL queries which are generated will work whatever mixture of case is used.
That's another reason why I ABSOLUTELY HATE CASE SENSITIVE SOFTWARE!!!
UPDATE: MySQL is case-insensitive by default, but some non-Windows operating systems can reverse this behaviour. In order to avoid any potential problems you should therefore ensure that the lower_case_table_names variable is set to 1. Please refer to http://dev.mysql.com/doc/refman/4.1/en/name-case-sensitivity .html for details.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
[Updated on: Thu, 22 June 2006 17:22] Report message to a moderator
|
|
|