MySql falling over

[ZOR]

I have reinstalled things, but MySql still giving me headaches. I am trying just to edit 1 record, which in browse looks okay, to add into a field. But all I get is a page of gobbledegook data with no suggestion of what to do, with a message:

MySQL returned an empty result set (i.e. zero rows).

Any ideas before I throw it out of the window. Thanks

 

[lgarner]

I've never seen MySQL return "gobbledegook", unless that was in the returned record.

What does the update statement look like? Since you're getting an empty result set, there's probably a select query. What does it look like?

 

[ZOR]

I have just gone through an exercise of removing the extra field I had added to the table. I then added it back again. I went into browse, ticked in all the visible records for editing, filled in the data in the added field and all okay, untill I selected the remaining records which were not listed (shows 30 records) and tried ticking in those for editing, and again I get what looks like some query or something with Edit/Explain SQL/and something else. Maybe I am doing something wrong, just following a logical path I thought. Regards

 

[ZOR]

This is what I see when I select the records not shown on page 2 for editing:

MySQL returned an empty result set (i.e. zero rows).SQL query:SELECT *
FROM `template`
WHERE `ID` =37
AND CONVERT( `Location`
USING utf8 ) = ''
AND CONVERT( `OrderNumber`
USING utf8 ) = ''
AND CONVERT( `PWRD`
USING utf8 ) = ''
AND `OrderDate` IS NULL
AND `Group` =5
AND CONVERT( `Description`
USING utf8 ) = 'SX321 LARGE'
AND CONVERT( `FujiPartNumber`
USING utf8 ) = 'P10WSXL124A'
AND CONVERT( `Duration`
USING utf8 ) = '124 mins'
AND CONCAT( `UnitPrice` ) = 7.94
AND `Qty` =0
AND CONCAT( `OrdSubTotal` ) IS NULL
AND CONCAT( `RunningTotal` ) IS NULL
AND CONVERT( `Title`
USING utf8 ) = '';

All I am trying to do is put something in the Title field. Getting concerned about the reliability of MySql, just hope its me not doing things properly. Thanks

 

[johnwm]

I wouldn't worry about the reliability of MySQL. It's used by many of the biggest websites around. What tool are you accessing the database with? It looks from the resultset that you're showing that you've got some rare query set accidentally.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[ZOR]

Thanks. I am not running queries that I know of, I.m using PhpMyAdmin to edit the DB. It is an XAMPP install, where PHP,MySql,Apache all get loaded as a bundle. It seems to be only on 1 record thats on page 2 of the browse that opens up in this way when trying to edit the record, yet the record looks normal. ??

 

[johnwm]

When you use phpMyAdmin you are using queries, although some of them are pre-written for you. The original default query for browsing a table looks like:

SELECT *
FROM `template`
LIMIT 0 , 30

In phpMyAdmin select the table you want to work with, then click the Browse tab. You will see the SQL that you're using in the grey 'SQL query' box. At the bottom edge of the box is an [edit] link. When selected the link brings up the present query in an edit box. To see the records whose title is NULL add this to the end of the query.

WHERE title is NULL

Note you don't need the backticks round the fieldnames unless the field names are reserved words, or include spaces.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[ZOR]

Thanks John. I followed your instructions but all I get is the sql getting updated, and no record to edit. There must be something very wrong why I cannot pick a particular record from page two of the browse list without the problems I am getting. I even tried deleting the offending record, but can't even do that. I tried repair, etc, but it just comes back everything is okay. I imported the table using a script file generated by AccessToMySql, maybe its causing a glitch. Am I correct in thinking to edit a record shown after record 30, I simply click page two, and click on the checkbox/pencil icon and then expect the record to show for editing. Thanks again.

 

[lgarner]

I'd suggest first looking into the database without phpMyAdmin or any other third-party program. Like johnwm, I highly doubt that MySQL is failing. This all sounds much more like an application problem unrelated to MySQL.

 

[ZOR]

Thanks. I just tried removing the packaged application XAMPP, and installed Wamp5. Imported the same table from script, and it fails in the same way when trying to edit a selected record 37. From selecting table, selecting Browse, Selecting Page 2, clicking the checkbox/pencil. So I then dumped the table, looked at the import script, and removed record 37. I imported the table, and all records on page 1 and 2 are editable with no errors appearing??. I cannot see a difference in the script that stands out.

INSERT INTO `Template`VALUES(37, "", "", "", NULL, 5, 'SX321 LARGE', 'P10WSXL124A', '124 mins', 7.94, 0, NULL, NULL);

Any clues? Thanks again.

 

[johnwm]

We don't know the exact datatype and order of your database fields, so we can't tell from that type of Insert statement what the problem may be. Show us the whole list of fields with their datatypes so we can cross-check with your data from above

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[KarveR]

Its characterset issues, table is probably latin_swedish_c1(beerfuzzy), and phpMyAdmin is reading everything as utf8 which will cause major confusion.

Not entirely sure how one would correct this tho.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[ZOR]

Thanks again. Here is the script being imported into MySql. Hope it throws up something.

# File name: F:\FujiOrdering\Five\FujiOrders.sql
# Creation date: 07/31/2006
# Created by Access to MySQL Pro 3.3
# --------------------------------------------------
# More conversion tools at

http://www.convert-in.com

DROP DATABASE IF EXISTS `FujiOrders`;
CREATE DATABASE `FujiOrders`;
USE `FujiOrders`;

#
# Table structure for table 'Template'
#

DROP TABLE IF EXISTS `Template`;
CREATE TABLE `Template` (
`ID` INT NOT NULL AUTO_INCREMENT,
`Location` VARCHAR(50),
`OrderNumber` VARCHAR(50),
`Caption` VARCHAR(30),
`OrderDate` CHAR(19),
`Group` INT,
`Description` VARCHAR(255),
`FujiPartNumber` VARCHAR(255),
`Duration` VARCHAR(50),
`UnitPrice` DECIMAL(19,4),
`Qty` INT,
`OrdSubTotal` DECIMAL(19,4),
`RunningTotal` DECIMAL(19,4),
INDEX `ID` (`ID`)
) TYPE=InnoDB;

#
# Dumping data for table 'Template'
#

LOCK TABLES `Template` WRITE;
INSERT INTO `Template` VALUES(1, "", "", "", NULL, 1, 'DIGITAL BETACAM', 'P10W000028A', '6 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(2, "", "", "", NULL, 1, 'DIGITAL BETACAM', 'P10W000018A', '12 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(3, "", "", "", NULL, 1, 'DIGITAL BETACAM', 'P10W000022A', '22 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(4, "", "", "", NULL, 1, 'DIGITAL BETACAM', 'P10W000024A', '32 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(5, "", "", "", NULL, 1, 'DIGITAL BETACAM', 'P10W000027A', '40 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(6, "", "", "", NULL, 1, 'DIGITAL BETACAM', 'P10W000026A', '34 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(7, "", "", "", NULL, 1, 'DIGITAL BETACAM', 'P10W000030A', '64 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(8, "", "", "", NULL, 1, 'DIGITAL BETACAM', 'P10W000032A', '94 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(9, "", "", "", NULL, 1, 'DIGITAL BETACAM', 'P10W000020A', '124 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(10, "", "", "", NULL, 2, 'BETACAM SP', 'P10W000378A', '5 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(11, "", "", "", NULL, 2, 'BETACAM SP', 'P10W000367A', '10 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(12, "", "", "", NULL, 2, 'BETACAM SP', 'P10W000370A', '20 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(13, "", "", "", NULL, 2, 'BETACAM SP', 'P10W000374A', '30 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(14, "", "", "", NULL, 2, 'BETACAM SP', 'P10W000383A', '60 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(15, "", "", "", NULL, 2, 'BETACAM SP', 'P10W000387A', '90 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(16, "", "", "", NULL, 3, 'DVCPRO', 'P10W000046A', '33 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(17, "", "", "", NULL, 3, 'DVCPRO', 'P10W000052A', '66 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(18, "", "", "", NULL, 3, 'DVCPRO', 'P10W000051A', '66 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(19, "", "", "", NULL, 3, 'DVCPRO', 'P10W000053A', '94 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(20, "", "", "", NULL, 3, 'DVCPRO', 'P10W000043A', '126 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(21, "", "", "", NULL, 4, 'HDCAM', 'P10W000007A', '6 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(22, "", "", "", NULL, 4, 'HDCAM', 'P10W000006A', '12 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(23, "", "", "", NULL, 4, 'HDCAM', 'P10W000189A', '22 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(24, "", "", "", NULL, 4, 'HDCAM', 'P10W000190A', '32 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(25, "", "", "", NULL, 4, 'HDCAM', 'P10W000191A', '40 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(26, "", "", "", NULL, 4, 'HDCAM', 'P10W000005A', '34 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(27, "", "", "", NULL, 4, 'HDCAM', 'P10W000192A', '64 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(28, "", "", "", NULL, 4, 'HDCAM', 'P10W000193A', '94 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(29, "", "", "", NULL, 4, 'HDCAM', 'P10W000188A', '124 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(30, "", "", "", NULL, 5, 'SX321 SMALL', 'P10WSXS006A', '6 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(31, "", "", "", NULL, 5, 'SX321 SMALL', 'P10WSXS012A', '12 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(32, "", "", "", NULL, 5, 'SX321 SMALL', 'P10WSXS022A', '22 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(33, "", "", "", NULL, 5, 'SX321 SMALL', 'P10WSXS032A', '32 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(34, "", "", "", NULL, 5, 'SX321 SMALL', 'P10WSXS062A', '62 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(35, "", "", "", NULL, 5, 'SX321 LARGE', 'P10WSXL064A', '64 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(36, "", "", "", NULL, 5, 'SX321 LARGE', 'P10WSXL094A', '94 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(37, "", "", "", NULL, 5, 'SX321 LARGE', 'P10WSXL124A', '124 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(38, "", "", "", NULL, 5, 'SX321 LARGE', 'P10WSXL184A', '184 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(39, "", "", "", NULL, 6, 'Mini DV', 'P10VDCDA00A', '60 mins', 0, 0, NULL, NULL);
INSERT INTO `Template` VALUES(40, "", "", "", NULL, 6, 'Mini DV', 'P10VDCFA00A', '80 mins', 0, 0, NULL, NULL);
UNLOCK TABLES;
ALTER TABLE `Template` CHANGE `OrderDate` `OrderDate` DATETIME;

#
# Table structure for table 'USR'
#

DROP TABLE IF EXISTS `USR`;
CREATE TABLE `USR` (
`ID1` INT NOT NULL AUTO_INCREMENT,
`USN` VARCHAR(12),
`USP` VARCHAR(12),
INDEX `ID1` (`ID1`),
PRIMARY KEY (`ID1`)
) TYPE=InnoDB;

#
# Dumping data for table 'USR'
#

LOCK TABLES `USR` WRITE;
UNLOCK TABLES;

Thanks again

 

[ZOR]

Thanks KarveR, only reference I have looking at the table is the collation - latin1_swedish_c1 , don't know if thats a clue. Thanks

 

[ZOR]

This is making me quite sick. I have found how to set my database collation to utf8, but it makes no difference. I still cannot get into certain records to edit them. When I try, I get some SQL to see what is trying to be done to get that record by pressing edit. It shows:

SELECT * FROM `template` WHERE `ID` = 37 AND CONVERT(`Location` USING utf8) = '' AND CONVERT(`OrderNumber` USING utf8) = '' AND CONVERT(`Caption` USING utf8) = '' AND `OrderDate` IS NULL AND `Group` = 5 AND CONVERT(`Description` USING utf8) = 'SX321 LARGE' AND CONVERT(`FujiPartNumber` USING utf8) = 'P10WSXL124A' AND CONVERT(`Duration` USING utf8) = '124 mins' AND `UnitPrice` = '7.9400' AND `Qty` = 0 AND `OrdSubTotal` IS NULL AND `RunningTotal` IS NULL;

If I change the SQL window to:

SELECT *
FROM `template`
WHERE `ID` =37
LIMIT 0 , 30

I then get my record okay. Why are there conversions being done. If anybody knows where to get answers would be very appreciated. Thanks.

 

[ZOR]

Just to say jpadie from the PHP forum managed to sort my problems out. The reasons I was having so much problem was the dumpfile which was imported into MySql had an Index Autonumber field instead of a primary. Changing that took all my headaches away. Thanks all for trying to help. Regards