phpMyAdmin

Logged In: YES
user_id=210714

fixed in cvs for 2.6.0, thanks.

Logged In: YES
user_id=210714

OK, tried 4.0.18 and 4.0.20 and they report
`human_resources`.`employee` (`EMPLOYEE_ID`).
I will code a general fix for this.

Logged In: YES
user_id=218271

CREATE TABLE `test` (
`EMPLOYEE_ID` int(11) NOT NULL default '0',
KEY `EMPLOYEE_ID` (`EMPLOYEE_ID`),
CONSTRAINT `test_ibfk_1` FOREIGN KEY
(`EMPLOYEE_ID`) REFERENCES
`human_resources`.`employee` (`EMPLOYEE_ID`)
) TYPE=InnoDB

Logged In: YES
user_id=210714

Ok, I am not running 4.0.20 for the moment. On my 4.0.16
system, it reports only one identifier
`human_resources.employee`
enclosed by backticks:
CREATE TABLE `test` (
`EMPLOYEE_ID` int(11) NOT NULL default '0',
KEY `EMPLOYEE_ID` (`EMPLOYEE_ID`),
CONSTRAINT `0_982` FOREIGN KEY (`EMPLOYEE_ID`) REFERENCES
`human_resources.employee` (`EMPLOYEE_ID`)
) TYPE=InnoDB

Just to confirm, can you show me the output of SHOW CREATE
TABLE test; I guess we will have to support both possibilities.

Logged In: YES
user_id=218271

explode wold fail because function PMA_SQP_parse
splits 'human_resources.employee' to three arrays:
[31] => Array ( [type] => quote_backtick [data] =>
`human_resources` )
[32] => Array ( [type] => punct_qualifier
[data] => . )
[33] => Array ( [type] => quote_backtick [data]
=> `employee` )

but explode is applied to thirst one, second one, and third
separately.
explode('.',$identifier)
where $identifier = $arr[$i]['data']

Logged In: YES
user_id=218271

PHP Version 4.3.7

Logged In: YES
user_id=218271

trace (human_resources)
trace2 Array ( [0] => human_resources ) trace (employee)
trace2 Array ( [0] => employee )

Logged In: YES
user_id=210714

I cannot understand why current code (explode) would fail,
please trace this in sqlparser.lib.php (also, which PHP
version are you running?)
if ($seen_references) {
$identifier =
str_replace('`','',$arr[$i]['data']);
if ($in_bracket) {

$foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
} else {
// identifier can be table or db.table
echo "trace (" . $identifier . ")<br>";
$db_table = explode('.',$identifier);
echo "trace2 ";
print_r($db_table);
if (isset($db_table[1])) {

$foreign[$foreign_key_number]['ref_db_name'] = $db_table[0];

$foreign[$foreign_key_number]['ref_table_name'] = $db_table[1];
} else {

$foreign[$foreign_key_number]['ref_table_name'] = $db_table[0];
}

Logged In: YES
user_id=218271

i fix this bug :)
in file 'sqlparser.lib.php' at line 1497:
if ($seen_references) {
$identifier = str_replace('`','',$arr[$i]['data']);
if ($in_bracket) {
$foreign[$foreign_key_number]['ref_index_list'][] =
$identifier;
} else {
// identifier can be table or db.table
if (isset($arr[$i+1]['data']) && $arr[$i+1]['data'] == ".") {
$foreign[$foreign_key_number]['ref_db_name'] = $identifier;
} else {
$foreign[$foreign_key_number]['ref_table_name'] =
$identifier;
}
}
}

Logged In: YES
user_id=218271

My further researches have shown that the information on
base human_resources is eaten with function
PMA_SQP_analyze as after performance PMA_SQP_parse
($show_create_table) the information still remains, however
after performance PMA_SQP_analyze she vanishes:

after PMA_SQP_parse:
Array ( [raw] => CREATE TABLE `test` ( `EMPLOYEE_ID` int
(11) NOT NULL default '0', KEY `EMPLOYEE_ID`
(`EMPLOYEE_ID`), CONSTRAINT `test_ibfk_1` FOREIGN
KEY (`EMPLOYEE_ID`) REFERENCES
`human_resources`.`employee` (`EMPLOYEE_ID`) )
TYPE=InnoDB [0] => Array ( [type] => alpha_reservedWord
[data] => CREATE ) [1] => Array ( [type] =>
alpha_reservedWord [data] => TABLE ) [2] => Array ( [type]
=> quote_backtick [data] => `test` ) [3] => Array ( [type] =>
punct_bracket_open_round [data] => ( ) [4] => Array ( [type]
=> white_newline [data] => ) [5] => Array ( [type] =>
quote_backtick [data] => `EMPLOYEE_ID` ) [6] => Array (
[type] => alpha_columnType [data] => int ) [7] => Array (
[type] => punct_bracket_open_round [data] => ( ) [8] =>
Array ( [type] => digit_integer [data] => 11 ) [9] => Array (
[type] => punct_bracket_close_round [data] => ) ) [10] =>
Array ( [type] => alpha_reservedWord [data] => NOT ) [11]
=> Array ( [type] => alpha_reservedWord [data] => NULL )
[12] => Array ( [type] => alpha_columnAttrib [data] =>
default ) [13] => Array ( [type] => quote_single [data] => '0' )
[14] => Array ( [type] => punct_listsep [data] => , ) [15] =>
Array ( [type] => white_newline [data] => ) [16] => Array (
[type] => alpha_reservedWord [data] => KEY ) [17] => Array
( [type] => quote_backtick [data] => `EMPLOYEE_ID` ) [18]
=> Array ( [type] => punct_bracket_open_round [data] => ( )
[19] => Array ( [type] => quote_backtick [data] =>
`EMPLOYEE_ID` ) [20] => Array ( [type] =>
punct_bracket_close_round [data] => ) ) [21] => Array (
[type] => punct_listsep [data] => , ) [22] => Array ( [type] =>
white_newline [data] => ) [23] => Array ( [type] =>
alpha_reservedWord [data] => CONSTRAINT ) [24] => Array
( [type] => quote_backtick [data] => `test_ibfk_1` ) [25] =>
Array ( [type] => alpha_reservedWord [data] => FOREIGN )
[26] => Array ( [type] => alpha_reservedWord [data] =>
KEY ) [27] => Array ( [type] => punct_bracket_open_round
[data] => ( ) [28] => Array ( [type] => quote_backtick [data]
=> `EMPLOYEE_ID` ) [29] => Array ( [type] =>
punct_bracket_close_round [data] => ) ) [30] => Array (
[type] => alpha_reservedWord [data] => REFERENCES )
[31] => Array ( [type] => quote_backtick [data] =>
`human_resources` ) [32] => Array ( [type] => punct_qualifier
[data] => . ) [33] => Array ( [type] => quote_backtick [data]
=> `employee` ) [34] => Array ( [type] =>
punct_bracket_open_round [data] => ( ) [35] => Array (
[type] => quote_backtick [data] => `EMPLOYEE_ID` ) [36]
=> Array ( [type] => punct_bracket_close_round [data] => ) )
[37] => Array ( [type] => white_newline [data] => ) [38] =>
Array ( [type] => punct_bracket_close_round [data] => ) )
[39] => Array ( [type] => alpha_reservedWord [data] =>
TYPE ) [40] => Array ( [type] => punct [data] => = ) [41] =>
Array ( [type] => alpha_columnAttrib [data] => InnoDB ) [len]
=> 42 )

after PMA_SQP_analyze:
Array ( [0] => Array ( [querytype] => CREATE
[select_expr_clause] => [position_of_first_select] =>
[from_clause] => [group_by_clause] => [order_by_clause]
=> [having_clause] => [where_clause] =>
[where_clause_identifiers] => Array ( ) [queryflags] => Array
( ) [select_expr] => Array ( ) [table_ref] => Array ( )
[foreign_keys] => Array ( [0] => Array ( [constraint] =>
test_ibfk_1 [index_list] => Array ( [0] => EMPLOYEE_ID )
[ref_table_name] => employee [ref_index_list] => Array ( [0]
=> EMPLOYEE_ID ) ) ) ) )

Logged In: YES
user_id=218271

Array ( [constraint] => test_ibfk_1 [index_list] => Array ( [0]
=> EMPLOYEE_ID ) [ref_table_name] => employee
[ref_index_list] => Array ( [0] => EMPLOYEE_ID ) )
Array ( [EMPLOYEE_ID] => Array ( [constraint] =>
test_ibfk_1 [foreign_db] => test [foreign_table] => employee
[foreign_field] => EMPLOYEE_ID ) )

Logged In: YES
user_id=210714

Ok, now in libraries/relation.lib.php, line 339, add a print_r:

if (isset($foreign) && is_array($foreign)) {
print_r($foreign);
return $foreign;
} else {
return FALSE;
}

and line 311:
foreach($analyzed_sql[0]['foreign_keys'] AS $one_key) {
print_r($one_key);
echo "<br>";

Logged In: YES
user_id=218271

Similarly the mistake in function PMA_getForeigners. As she
generates a wrong name of the database (test insteed
human_resources).
I insert command 'printf($foreigners);' after line 90 in
file 'tbl_select.php':

Array ( [EMPLOYEE_ID] => Array ( [constraint] =>
test_ibfk_1 [foreign_db] => test [foreign_table] => employee
[foreign_field] => EMPLOYEE_ID ) )

Logged In: YES
user_id=218271

trace test employee
Error

SQL-query :

SHOW TABLE STATUS FROM `test` LIKE 'employee'

MySQL said:


#1146 - Table 'test.employee' doesn't exist

[Documentation] · [Back]

Logged In: YES
user_id=210714

Tried to reproduce this bug, could not. Please do some
tracing for me. In libraries/get_foreign.lib.php, add 2
lines around line 28:

echo "trace $foreign_db $foreign_table ";
$the_total = PMA_countRecords($foreign_db,
$foreign_table, TRUE);
echo "trace after";