Jump to content

Updating field values fails (MySQL related)


Oliver
 Share

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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>
Link to comment
Share on other sites

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...?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...