Jump to content

SystemUpdater #12 Error, PW incompatible with mysql 5.7.x ?


felic
 Share

Recommended Posts

Hi,

since i updated my local mysql to v5.7.9, the Processwire SystemUpdater fails (on all local PW installations) with the following message:

SystemUpdater: ERROR: Update #12 ERROR: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'created'

I suppose PW is not prepared for this mysql version.

Sources: https://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html#upgrade-system-table-changeshttps://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_mode

As my database knowledge is limited,- can someone confirm this? 

regards

Olaf

Link to comment
Share on other sites

Fresh install 2.7.1 and upgraded to 2.7.2

2015-11-28 19:04:38Update #14: Initializing update
2015-11-28 19:04:38Detected core version change 2.7.1 => 2.7.2
2015-11-28 19:04:39Update #14: Completed!

Edit: I just upgraded an "old" PW 2.6.20 installation without any problems:

2015-12-01 14:39:13Update #14: Initializing update
2015-12-01 14:39:13Detected core version change 2.6.20 => 2.7.2
2015-12-01 14:39:14Update #14: Completed!

MySQL

❯ mysql --version
mysql  Ver 14.14 Distrib 5.7.9, for osx10.11 (x86_64) using  EditLine wrapper 

Have a look at your database. What is the default for column created (table pages)?

+-------------------+------------------+------+-----+---------------------+----------------+
| Field             | Type             | Null | Key | Default             | Extra          |
+-------------------+------------------+------+-----+---------------------+----------------+
| created           | timestamp        | NO   | MUL | 0000-00-00 00:00:00 |                |
+-------------------+------------------+------+-----+---------------------+----------------+
Link to comment
Share on other sites

+-------------------+------------------+------+-----+---------------------+----------------+
| Field             | Type             | Null | Key | Default             | Extra          |
+-------------------+------------------+------+-----+---------------------+----------------+
| created           | timestamp        | NO   | MUL | 0000-00-00 00:00:00 |                |
+-------------------+------------------+------+-----+---------------------+----------------+

literally the same. Hm...

Link to comment
Share on other sites

Have a look if your mysql configuration has strict_trans_tables or strict_all_tables set. This prevents zero dates (since 5.7.4) from working.

See http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes for details.

That's a good point. I remember that I changed something like that a long time ago. I found my snippet  :P  

sql errors - check (mysql 5.6.15):
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
remove STRICT_TRANS_TABLES
  • Like 10
Link to comment
Share on other sites

Ah, that's it. Got to remove some... Thanks to both of you, @justb3a, @BitPoet!

mysql> select @@GLOBAL.sql_mode;
+-----------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                         
+-----------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY
| _ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
+-----------------------------------------------------------------------------------------+
Link to comment
Share on other sites

  • 3 months later...
  • 10 months later...

@justb3a

 Did you change the settings in the my.cnf file or through phpmyadmin? 

I read that in order to make the changes persist across DB server restarts, we have to change my.cnf.

What for shared hosting where we only have access through phpmyadmin?

 

Link to comment
Share on other sites

@felic Yes, it is possible on the console and through phpadmin. But the changes can only be made by a user with root privileges and they will not persist. So after a restart of  mysql they will be lost.

In most shared hosting environments we are not allowed to do these changes ourselves...

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

  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Similar Content

    • By Aswincs
      Here are the step by step to install and setup ProcessWire with the help of a server management tool - https://cloudstick.io/
      1. Create your Vultr compute.

      2. Select the Operating system Ubuntu 16.04/18.04/20.04 LTS >> Enter your server root password then click on Deploy now!

      3. Create an account in CloudStick and connect your server:
       
      Click on connect server >> Enter your server login details >> Add this server.

      4. Your server setup will be done in couple of minutes - The setup will finish less than 8 minutes. Then select your server:

      5. Create an account to host/upload ProcessWire: Click on Accounts >> Create an account >> Create Custom Web application:

      6. Enter the web application details, such as the email address which you would like to receive the SFTP login details >> web application name >> Domain name >> username then >> select the web application stack >> nginx + apache >> then create web application. 

      7. Now select the web application then install SSL: 
      8. Open your email account, and find the login details to connect the server over SFTP >> then upload the source code of ProcessWire:

      9. Let us open the domain in browser once the upload finish.

      10. Select the profile and click next:

      11. Click next to proceed further: Now, you will see an incompatibility issue with PDO-Mysql which can be install in 2 clicks.

       
      12. Go back to the summary page >> Click on easy PHP >> Select the PHP version of your web account:

      13. Then it is time to enable PDO_Mysql, scroll down and enable it:

      14. Go back to to the ProcessWire installation URL and click on check again >> You can see no incompatibility issue after enabling PDO_Mysql:

      15. Click on Next and now it is time to enter the database credentials:

      16. let us create the database, db user and grant privilege's to the db user - it is just matter of few clicks and very easy! 
      Click on the menu Accounts >> Select your web account of ProcessWire >> Click on App Database then create the a database:

      17: Click on create database and enter the database name:

      18. Create the database user:

      19: Go back to the database page and click on Grant user then grant permission:

      20. Go back to the ProcessWire installation URL and enter the database credentials you have created in CloudStick dashboard.
      Now, it is time to setup your admin user credentials and setup admin area URL:

      21. Then you are done:


    • 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
×
×
  • Create New...