Jump to content
Oliver

Updating field values fails (MySQL related)

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?

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

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. :)

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
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.

Share this post


Link to post
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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
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>

Share this post


Link to post
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...?

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By jds43
      Hello,
      Does anyone have experience with migrating content from Django to Processwire? Or are there any suggestions for achieving this?
    • By Brawlz
      Hi,
      I hope this is the correct section for my problem.
      All I need is a connection to an external Database and a query gettings some data. I do this in a processwire Page-Template. I am honestly not sure if it is a problem with processwire or my code:
      $host = ‚XXXXX’; $user = ‚XXXXX‘; $pass = ‚XXXXX‘; $db = ‚XXXXX‘; $port = ‚3306‘; $mydb = new Database($host, $user, $pass, $db , $port);  $result = $mydb->query("SELECT * FROM char“);  while($row = $result->fetch_assoc()) {  print_r($row);  }  
      Produces the following error:
      Error: Exception: DB connect error 2002 - Connection timed out (in /customers/9/4/e/XXXX.de/httpd.www/wire/core/Database.php line 79)
       
      I also tried connecting without the $port variable but got the same error.
    • By Mobiletrooper
      Hey Ryan, hey friends,
      we, Mobile Trooper a digital agency based in Germany, use ProcessWire for an Enterprise-grade Intranet publishing portal which is under heavy development for over 3 years now. Over the years not only the user base grew but also the platform in general. We introduced lots and lots of features thanks to ProcessWire's absurd flexibility. We came along many CMS (or CMFs for that matter) that don't even come close to ProcessWire. Closest we came across was Locomotive (Rails-based) and Pimcore (PHP based).
      So this is not your typical ProcessWire installation in terms of size.
      Currently we count:
      140 Templates (Some have 1 page, some have >6000 pages)
      313 Fields
      ~ 15k Users (For an intranet portal? That's heavy.)
      ~ 195 431 Pages (At least that's the current AUTOINCREMENT)
       
      I think we came to a point where ProcessWire isn't as scalable anymore as it used to be. Our latest research measured over 20 seconds of load time (the time PHP spent scambling the HTML together). That's unacceptable unfortunately. We've implemented common performance strategies like:
      We're running on fat machines (DB server has 32 gigs RAM, Prod Web server has 32gigs as well. Both are running on quadcores (xeons) hosted by Azure.
      We have load balancing in place, but still, a single server needs up to 20 sec to respond to a single request averaging at around about 12 sec.
      In our research we came across pages that sent over 1000 SQL queries with lots of JOINs. This is obviously needed because of PWs architecture (a field a table) but does this slow mySQL down much? For the start page we need to get somewhere around 60-80 pages, each page needs to be queried for ~12 fields to be displayed correctly, is this too much? There are many different fields involved like multiple Page-fields which hold tags, categories etc.
      We installed Profiler Pro but it does not seem to show us the real bottleneck, it just says that everything is kinda slow and sums up to the grand total we mentioned above.
      ProCache does not help us because every user is seeing something different, so we can cache some fragments but they usually measure at around 10ms. We can't spend time optimising if we can't expect an affordable benefit. Therefore we opted against ProCache and used our own module which generates these cache fragments lazily. 
      That speeds up the whole page rendering to ~7 sec, this is acceptable compared to 20sec but still ridiculously long.
      Our page consists of mainly dynamic parts changing every 2-5 minutes. It's different across multiple users based on their location, language and other preferences.
      We also have about 120 people working on the processwire backend the whole day concurrently.
       
      What do you guys think?
      Here are my questions, hopefully we can collect these in a wiki or something because I'm sure more and more people will hit that break sooner than they hoped they would:
       
      - Should we opt for optimising the database? Since >2k per request is a lot even for a mysql server, webserver cpu is basically idling at that time.
      - Do you think at this point it makes sense to use ProcessWire as a simple REST API?
      - In your experience, what fieldtypes are expensive? Page? RepeaterMatrix?
      - Ryan, what do you consider as the primary bottleneck of processwire?
      - Is the amount of fields too much? Would it be better if we would try to reuse fields as much as possible?
      - Is there an option to hook onto ProcessWires SQL builder? So we can write custom SQL for some selectors?
       
      Thanks and lots of wishes,
      Pascal from Mobile Trooper
       
       
    • By Sergio
      All of a sudden, with nothing changed on the database or server, a website was getting error when doing a search:
      Error: Exception: SQLSTATE[HY000]: General error: 23 Out of resources when opening file './your-database-name/pages_parents.MYD' (Errcode: 24 - Too many open files) (in /home/forge/example.com/public/wire/core/PageFinder.php line 413) #0 /home/forge/example.com/public/wire/core/Wire.php(386): ProcessWire\PageFinder->___find(Object(ProcessWire\Selectors), Array) #1 /home/forge/example.com/public/wire/core/WireHooks.php(723): ProcessWire\Wire->_callMethod('___find', Array) #2 /home/forge/example.com/public/wire/core/Wire.php(442): ProcessWire\WireHooks->runHooks(Object(ProcessWire\PageFinder), 'find', Array) #3 /home/forge/example.com/public/wire/core/PagesLoader.php(248): ProcessWire\Wire->__call('find', Array) #4 /home/forge/example.com/public/wire/core/Pages.php(232): ProcessWire\PagesLoader->find('title~=EAP, lim...', Array) #5 /home/forge/example.com/public/wire/core/Wire.php(383): ProcessWire\Pages->___find('title~=EAP, lim...') #6 /home/forge/example.com/public/wire This error message was shown because: you are logged in as a Superuser. Error has been logged.  
      I tried several things, listed in this thread: https://serverfault.com/questions/791729/ubuntu-16-04-server-mysql-open-file-limit-wont-go-higher-than-65536
      But for some reason, MySQL was not getting its limit increased, but in the end, the one that did the trick was this:
      This worked for me on Ubuntu Xenial 16.04:
      Create the dir /etc/systemd/system/mysql.service.d
      Put in /etc/systemd/system/mysql.service.d/override.conf:
      [Service] LimitNOFILE=1024000 Now execute
      systemctl daemon-reload systemctl restart mysql.service Yes indeed, LimitNOFILE=infinity actually seems to set it to 65536.
      You can validate the above after starting MySQL by doing:
      cat /proc/$(pgrep mysql)/limits | grep files
    • By ethanbeyer
      Hello,
      I recently posted in this topic, but I decided to start my own thread because while I believe my issue is related to the one in that thread, they are not exactly the same:
       
      I have created a custom User Template in the method outlined in the docs. I am creating a directory, so it made sense that every page in the directory was a Directory Member, so they could log in and edit their own information while also keeping the entire directory protected behind a login wall.
      So the new user type is created: "directory-member".
      I then created two new roles: "member" and "directory-admin":
      The "member" only has the ability to View directory-member pages, and "profile-edit", which allows them to manage their own information. The "directory-admin" has the ability to edit any directory-member pages, and administer users. Some Directory Members are both, but all have at least the "member" role.
      The first hint that something was wrong was when I was testing a "member" user and I could not add a new item to a repeater on that profile. The url for the profile edit (this will be important shortly) is site.dev/admin/profile. The repeater is set up to load new items through AJAX. If this option is turned off, the rest of this issue is no longer completely valid. But as I have found what I believe to be a pretty large issue in the Processwire codebase, I thought it worth bringing up.
      See, every page (even a user) has a $page->editUrl() method, and it returns a URL like this: site.dev/admin/access/users/edit/?id=2096. That's all good and fine for users that have page-edit permissions, but if they don't, that link will resolve to the admin's equivalent of a 404.
      So the way that Processwire currently gets around this is by creating a specific editing area for a user to interact with only their profile: /admin/profile. And that works pretty nicely, except for the fact that nowhere is editUrl() ever made aware of the difference. editUrl() is not hookable, and whether or not a page is editable is based on the PagePermissions module.
      On top of that, there are several core modules that hardcode a search-and-replace (see InputfieldRepeater.module:627) where the editing screen is for Users. This doesn't allow for a huge degree of flexibility that is offered in other places throughout Processwire. If line 627 of InputfieldRepeater is changed from this:
      $editorUrl = str_replace('/access/users/edit/', '/page/edit/', $editorUrl); to this:
      $editorUrl = str_replace('/access/users/edit/', '/profile/', $editorUrl); ...the AJAX repeaters work. It's maddening!
      As is brought up in the thread I attached above, a lot of the features of page editing are missing within /admin/profile/, and it just makes for an altogether strange editing experience. A user who has "page-edit" permissions for templates other than directory-member, but does have "profile-edit" permissions, will see their user in the Page List, but cannot edit their Page unless they hover over the wrench and click the "Profile" link. It just seems - off.
      I think what this hinges on for me is that the editUrl() of the user should be "/admin/profile/" if that user is logged in (and their page should be editable from the Page List), or the "/admin/access/users/edit/" url; regardless of the URL, both links should resolve to the Page Edit screen, as the Profile Edit screen seems to be a unnecessarily neutered version of Page Edit.
×
×
  • Create New...