Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE development » Bug Reports » Database import
Database import [message #95] Wed, 21 June 2006 16:27 Go to next message
David Lee is currently offline  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 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
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.


Re: Database import [message #104 is a reply to message #100] Thu, 22 June 2006 13:30 Go to previous messageGo to next message
David Lee is currently offline  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 #105 is a reply to message #95] Thu, 22 June 2006 14:13 Go to previous messageGo to next message
David Lee is currently offline  David Lee
Messages: 44
Registered: June 2006
Member
Applying the same trick of using phpmyadmin to update database_id in dict_table allows columns to be imported.

All my column names are lower case.
I have started setting up relationships, but am now out of time. No problems so far.
Re: Database import [message #106 is a reply to message #104] Thu, 22 June 2006 15:00 Go to previous message
AJM is currently offline  AJM
Messages: 2347
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.


[Updated on: Thu, 22 June 2006 17:22]

Report message to a moderator

Previous Topic: Audit_trn task_id field too small
Next Topic: Possible linux pathname bug
Goto Forum:
  


Current Time: Thu Mar 28 14:48:29 EDT 2024

Total time taken to generate the page: 0.01483 seconds