Updating field values fails (MySQL related)

[Oliver]

A problem has come up, when I uploaded PW2.2 to a client’s webspace. Everything seems to work fine but one thing: When editing for example the value of a page’s text area, the value which was initially - and successfully - entered and saved, can’t be overwritten by a new value. As I know it worked on my local web server and on other webspaces, I tried to get to the problem’s root.

The SQL-query generated to update the field is

INSERT INTO `field_team_text` (pages_id, data) VALUES('1074', '11112asdsadasdasa') ON DUPLICATE KEY UPDATE data=VALUES(data)

I tested it in phpMyAdmin directly on the database used for the PW2.2 setup. The result is, that the query doesn’t cause any error but also doesn’t update the `data` column for the already existing `pages_id`. As `pages_id` is correctly defined as primary key, I can’t see any reason why this shouldn’t work.

The server version is 5.0.37-community-nt, the os may possibly be Windows, as the web server (which isn’t running on the same machine like the database server) definitely is running it.

Any ideas?

[ryan]

That does seem very strange. I'm impressed you went to the effort of trying out the query independently. If the query is failing (not updating) even when trying it directly in a MySQL client (like PhpMyAdmin) then it seems like there is a MySQL error or bug occurring here? Can you confirm that following up with this query shows the non-updated value?

SELECT data FROM field_team_text WHERE pages_id=1074;

If it's showing the incorrect value, you might want to try to run a database repair on the table (in PhpMyAdmin, check the box next to the table and select 'Repair' from the select box). If it is showing the correct value from PhpMyAdmin, then let me know as we'll need to look at some other things.

[Oliver]

Yeah, I should run a database repair! Having value a saved in the db, inserting/updating to value b and after that running the SELECT query results in getting a much older value c, while browsing the table still gives me value a. WTF?

Update: Ok, it’s totally corrupted, the whole thing! Repairing fixes the issue for the moment (so it at least shows the values I updated to before), but the db keeps behaving the same way after that, showing different results for different queries getting the same field value. As there is just one line in the table, I’m pretty sure, it’s the same field.

Seems I’ve to contact the hoster to get this fixed.

Update 2: As it seems, the problem just appears when using INSERT INTO ON DUPLICATE KEY UPDATE queries. An UPDATE query updates successfully and the field shows the right result.

[ryan]

Oliver, there might be some bug in the version of MySQL there. But I would suggest exporting the whole database with PhpMyAdmin to an SQL dump, then create a new database, and import the dump. Then switch your site to use the new database (by editing the bottom of /site/config.php). Just in case there's a problem specific to that DB, that would at least put it in a fresh state. Please let me know if you find anything else.

[Oliver]

Ryan, just to give you an update: It’s actually a bug in this particular mysql server version which is about 5 years old. I found a bug report that describes my problem and it seems there is a problem with primary keys using a varchar field and caching. I asked the hosting provider to update the server.

[ryan]

Thanks for reporting back on this. Glad to hear you found the source of it. And of course, glad to hear it's their problem rather than ours.

[AnotherAndrew]

Oliver,

I think I am having a similar problem. What version of mysql were you using? My host is using version 5.1, and I see the latest version is 5.6. Also, do you have a link to that bug report?

[Oliver]

The bug report I found was this one: http://bugs.mysql.com/bug.php?id=28638

But the guys replying say the problem was fixed in version 5.0.41. My host runs 5.0.37-community-nt.

[AnotherAndrew]

Ok thanks.

It now appears that I am not able to save any data in basic text fields. When I save a page, I get thrown to a 404 page. I can only make edits if I do it in the database. I'm not sure what is the problem.

[ryan]

AnotherAndrew, when did the problem start? After migrating the site to another server? The fact that you are getting a 404 makes me wonder if this is a different issue than the one Oliver was running into. If the issue just started without there being some big server change, I'd suggest running a repair on your database, just in case. If you have PhpMyAdmin, this is very easy and fast to do.

[AnotherAndrew]

Ryan,

Thanks I did run a repair on the database and I even made a new database and that didn't work. It is strange because it is only specific to a certain template.

What I did was create two new textarea fields, dropped in my code. Then in the editor, if I create a new line or change the styling, and then hit save, it goes to a 404 page.

Perhaps I should delete the textarea field and create a new one? Or do you have any other thoughts?

[apeisa]

If you edit only some other field than textarea, like title field, and hit save? Does it work?

If it works, it feels like tinyMCE/other js is messing with save button or something like that...

[AnotherAndrew]

Apesia, if I try editing another field, like the title field, and hit save, then again it throws me to a 404 page. Any ideas?

[apeisa]

Nope, no ideas. What is in your address bar when you see that 404 page?

[AnotherAndrew]

Yep, its odd.

When the 404 shows, I am getting in the address bar foo.com/admin/page/edit/?id=1033#. Where the page I am editing is just foo.com/admin/page/edit/?id=1033

[apeisa]

That url is ok, it shows that # also right after hitting save, and removes it when redirect happens.

[AnotherAndrew]

Could this be a cache problem?

[formmailer]

Could this be a cache problem?

If you empty the whole cache directory, you can rule out all caching problems. There is no risk in emptying it, just give it a try.

/Jasper

[AnotherAndrew]

Jasper,

I tried that and it still doesn't work. I have two pages using the same template, whenever I edit anything, it throws a 404. I don't know what else to try!

[apeisa]

Does other pages work fine? Do you have any custom modules installed? Did you migrate from another server or developed? Localhost or server? Do you run other PW-sites without a problem on your server?

[formmailer]

You could try to install Fiddler (http://fiddler2.com/...er2/version.asp) to monitor all http requests and replies. This could tell you if it's the page you are trying to edit that throws the 404 or if it's something else...

Do your fields have "strange" names? What happens if you remove your custom fields from your template (I assume you haven't been able to save any pages with these fields, so it wouldn't be a problem to remove these from your template. If you are unsure, make a db backup before doing this)

/Jasper

[AnotherAndrew]

Apeisa,

All other pages are ok. This is the first PW site that I have done on any host. I migrated from localhost to the developing host a week ago. Everything was fine until I created two new text fields. They have unique names that should not interfere with PW.

I do have a few custom modules installed, but they are not being used on the template page.

[AnotherAndrew]

Do your fields have "strange" names? What happens if you remove your custom fields from your template (I assume you haven't been able to save any pages with these fields, so it wouldn't be a problem to remove these from your template.

The pages were saved and had data. This problem seemed to appear after I added the following code to the template.

<div id="top-image-caption"><?php echo $page->topimagecaption; ?></div>
<div id="quotation"><?php echo $page->quotation; ?></div>

[apeisa]

Andrew, I don't think that what you have on your template file has anything to do with problems on saving the page on admin. But custom modules might always be a reason. If they are autoload modules (like many are) they run on every page load.

How did you migrate? With profile exporter? If that is the case, then those two new fields are first ones created on your current host. Might be something related to mysql user permissions...?

[AnotherAndrew]

I didn't use "profile exporter". Is that a module? I just copied the files, exported and imported the database.

I think I kept the same usernames for the mysql users, but I will check.

Actually, the admin username is different in the mysql database. Is that a problem?