"No records matched your request" non-empty tables

[dfondente]

I've configured my first xataface application, and am seeing some strange behavior. The database has over 30 tables. Most of these work as expected, but 4 of the tables display the "No records matched your request" error when the default action is issued (when I click the table name in the left column). All 4 of these tables contain data. I have set up fields.ini, relationships.ini, and valuelists.ini files for 2 of the tables, but the other 2 have no configuration.

Checked the Apache error log and there's no error. Even more strange to me is that one of the tables displays records correctly when it is part of a relationship (i.e. I can see records in the table if I access it through a parent table).

Anybody seen this behavior before? Any suggestions about how I can determine the source of the problem?

[shannah]

There are a few possibilities that come to mind:

1. No primary key defined on these tables.
2. The URL you are entering contains query parameters that are filtering the results (i.e. what is the URL to the list view of the problematic tables?)
3. You have defined a security filter on these tables and forgot about it.
4. You have defined a custom __sql__ query for these tables in the fields.ini file that is incorrect and returns an empty set.
5. You have set up permissions that don't allow you to see any of the records in this table.

[dfondente]

Appreciate the response and the ideas. I don't think any of these apply in my situation, however.

I inspected one of the tables with the mystery behavior in more detail. This happens to be a join table. So let's say I have a table "courses" and another table "students". The misbehaving table is "coursesToStudents".

1. I have verified that "coursesToStudents" has a primary key. I even deleted the primary key, and when I tried to access it in my application was shown a white screen (error log indicated the primary key is missing). Added the primary key in again, and same behavior as before with the "no records matched" message.

2. The URL has no additional parameters. I am accessing the table through the default view with the tables listed in a left sidebar. The URL looks like:
index.php?-table=coursesToStudents

3. I have not configured any security filters.

4. I completely removed the fields.ini file when I noticed the mystery behavior. So in my application, the directory for this table (APP_DIR/tables/coursesToStudents/) is completely empty. I have other tables that have no ini files, and they all work fine. I assume an ini file in the directory for a related table cannot be the culprit?

5. I have not set up any special permissions on any of the tables.

What seems particularly odd to me is that I can see records from "coursesToStudents" when I look through a related record. For instance, if I go into an individual record in the "courses" table, I can see the associated "coursesToStudents" records in the Students tab I have configured for that table. Xataface can clearly access the data in "coursesToStudents", but just doesn't display it when I want to list all records.

Any other suggestions?

[shannah]

Can you post the SQL table schema for this table? Do you have an application delegate class defined?

[shannah]

Also... what versions of PHP, MYSQL, Web server, MySQL, OS, Xataface are you running?

[dfondente]

Table schema:

Code: Select all

CREATE TABLE `coursesToStudents` (
  `courseId` int(11) NOT NULL,
  `studentId` int(11) NOT NULL,
  `region` enum('A','B','C') NOT NULL default 'A',
  PRIMARY KEY  (`courseId`,`region`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


(Yes, the primary key is on courseId and region, not courseId and studentId.)

I have not defined any delegate classes (this is a very simple app, customized only with ini files in various table directories).

CentOS 5, Linux 2.6.18-028stab062.3 x86_64
Apache 2.2.3
mysql Ver 14.12 Distrib 5.0.45
PHP 5.2.8
Dataface 1.1.4 971

[shannah]

Hmmm... just created a table with this definition and dropped it in one of my xataface apps and it seemed to work fine. Is your app online so I can check it out myself?

-Steve

[dfondente]

The app is not publicly accessible.

I'm new to xataface and not familiar with the code, so I'm not sure where to start placing debugging statements to figure out what might be going on. I'm curious to find out exactly what SQL command is being run when I access the table via that default URL, and also to determine where the results of the query are processed to see if I can determine why it thinks the table is empty.

[shannah]

First step in debugging would be to upgrade Xataface to the latest 1.2.1.
Next make sure you're working with a minimal application (e.g. only index.php and conf.ini

[dfondente]

I just created a clean app with xataface 1.2.1. Have not added any ini files to the tables.

The same tables display the "no records" message even in the clean app.

[shannah]

For debugging purposes, you can go into the Dataface/DB.php file, find where it calls mysql_query, and just echo the SQL query.

[dfondente]

I should have posted the true table schema instead of renaming the fields in a prior message. The tables that return the "no records" message all contain an enum field named "locale". This field is part of the primary key for each table. When xataface creates a SELECT COUNT(*) on the field, it somehow has values that are not part of the enum. It is trying to count the number of records

Code: Select all

WHERE `locale` LIKE CONCAT('%','en-US','%')

And that is what returns 0 records, since 'en-US' is not one of the enum values in any of my tables.

I don't know where this is happening yet, but that appears to be the problem.

[shannah]

Oh.. you know what might be happening. PHP sometimes adds some GET parameters to the request that you don't know about.

At the beginning of your index.php file, try doing:
print_r($_REQUEST);

This will tell you what request variables there are. I suspect that locale is one of them. So xataface treats this as a search parameter if the table contains a column of the same name.

You could unset these values, but then you wouldn't know if the value was actually a true search parameter. Easiest thing is probably to change the name of the locale column to something else.

[dfondente]

Can't change the field names in the database; it's used by a legacy application.

[shannah]

Then you're going to have to filter the data to make sure that it is a valid query parameter.

E.g. at the beginning of your index.php file you'll need to do something like this:

Code: Select all

if ( isset($_REQUEST['locale']) and !in_array($_REQUEST['locale'], $arrayOfValidLocaleValues) ){
    if ( $_POST ) unset($_POST['locale']);
    if ( $_GET ) unset($_GET['locale']);
}