Table Data Exists But Is Missign From Query Result

[minboost]

Last night I upgraded to Yog 5.01. I downloaded data as an sql dump from my live server using phpMyAdmin. Then I used Yog 5.01 to execute to import the sql dump to the MySQL 4.x on my local machine (Win XP).

When I view the data in the Table Data tab of Yog, I can see all of my records correctly. When I query MySQL from my ASP web site (also run on my local machine) I get the correct results.

However, when I type in a SELECT query in the Yog window, the results do not get returned for any of the data imported from the sql dump.

For example: prior to the sql dump tbl_product had 3 records. After, it had over 200. But when I query SELECT * FROM tbl_product I get only 3 records (the original 3 that weren't part of the sql dump). If I run the same query through my ASP web site, I get all of the records.

Thanks for any help you can give me.

-m

[peterlaursen]

that is strange … 😮

When you click on TABLE tab this SQL

Code:

select * from `db_name`.`tbl_product` limit 0, 50

is executed. You can see it in the HISTORY pane.

Try copy/paste from HISTORY to SQL-pane it and exectute. What happens?

Any difference to see? Do you use back-quote as Yog does ? Do you use full syntax (`db`.`table`). Try omitting first the one thing, then the next … Can you dump from your local? If yes try edit the table name. Does it import? We gotta try a lot of mad things to find some idea to follow here … Does remote server and local use the same charset ? phpmyadmin has implemented som non-standard 'encoding' (NOT a MySQL term!!) management.

Do you have a chance to get data from the server using SQLyog (Can you connect? ) ? Can you import them then and problem persists or not? If server is a Linux server (and you have physical access to it) you can install SQLyog in WINE and make the dump. Refer to http://www.webyog.com/faq/5_71_en.html. Or use SJA for Linux.

It remember some situation (but I don't remember details) with some problem with data imported from a phpMyAdmin dump some moths ago. So I think we will have to try with a dump NOT from phpmyadmin.

A hexeditor also can be a wonderful thing …

[peterlaursen]

and ..

what happens in MySQL command-line client ?

[minboost]

Well, I did this at another location so I am not able to check up on it.

However, today I tried it at the office. The phpMyAdmin is version 2.7.0-pl2 and it connects to MySQL 4.0.24. The MySQL version I'm running on my local machine is 4.1.x.

This time it worked fine.

Now I'm not certain about what settings I used to create the SQL dump from phpMyAdmin. I will have to try out the different options to see if I can duplicate the problem again.

Also, I originally misstated the facts in my original post. The records that were being returned from the Yog query window were NOT originally there. Here are the real facts (sorry, I had forgetten them until now):

1. I uploaded the database using an SQL dump from Yog 4.x to phpMyAdmin. The tbl_product containted 3 records.

2. Using a custom ASP backend program, a couple hundred records were added to the table.

3. I downloaded the database as an SQL dump from phpMyAdmin. I DID NOT use the “drop if exists” statements. Instead, I manually dropped all tables from the local database and just imported from the SQL dump (which only had INSERT statements).

4. The problem described in my original post occurred.

For now, it seems, that my problem is solved since I was able to get it to work at the office I am certain I can get it to work where I need it to.

However, if anyone (peterlaursen) wants me to further investigate and discover the exact source of the problem (and potential bug or incompatibility) just ask and I will.

Thanks.

[peterlaursen]

hmmm …

I have no needs of such research my own. But if you ever run across a similar thing again it would be very nice if you were able a very small 'reproducable test case' – ie. a dump with a single database table and a few rows and colums only.

I very rarely use phpmyadmin myself and I am mot familiar with the different versions.

What is the 'history' of your local mysql server. Has the 4.1 version been updated from an earlier version, and did you run the update scripts? I wonder if there are some old-format system tables there.

[Author]

Posts