Fatal MySQL error 1364: Field 'id' doesn't have a default value when attempting to run prune database

Princeps_of_Nihils · 28 August 2018 14:53

Attempted to run a Prune Database program and got the following fatal error.

Anyone seen this before and know a fix. My workflow automates fine.

Tue Aug 28 14:50:37 2018 [6897][1][FATAL] Query Failed: INSERT INTO tracker (monitor_id,user_id,module_name,item_id,item_summary,date_modified,action,session_id,visible ) VALUES ( ‘16e199f0-c025-4f13-2101-5b8561255b8b’,‘1’,‘Schedulers’,‘64732703-42d2-d2fb-c6ab-5b855b1588c0’,‘Prune Tracker Tables 2’,‘2018-08-28 14:50:37’,‘detailview’,‘2b1f63de5a250bafdd9797c92f23547d’,‘1’): MySQL error 1364: Field ‘id’ doesn’t have a default value

Princeps.

pgr · 28 August 2018 19:26

This is the error you would see if your “trackers” table on the database didn’t have the “id” column set as AUTO_INCREMENT.

But it should have. The installer does that, and I bet you didn’t change it.

Can you check with phpMyAdmin?

Princeps_of_Nihils · 4 September 2018 09:37

Hi PGR

Sorry for the late reply I was off sick for a bit. You are right the table is not set to Auto Increment.

I have to use MySQL WorkBench to define it as Auto Increment (by using the spanner icon next to the table and selected AI.

When I set it I got error “ERROR 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key”

I then defined it as Primary Key and Auto Increment and it did not throw up an error.

I did a quick Repair and Rebuild from the Admin Centre.

After that I checked by running the a test Prune Tracker Tables (I did this by duplicated the Prune Tracker Tables and setting the date) and it still shows no successful runs.

Checked my error logs just MqSQL Error 1146 but did not match the times I was attempting to Prune.

Currently Unresolved.

Question PGR do I need to change all the tables ID’s to have Primary Key and Auto Increment, as in Surveys, Opportuntities Audit etc?

Thank you.

Princeps

pgr · 4 September 2018 11:55

You shouldn’t change anything else, it is even strange that this table was not set correctly - as I said, the installer takes care of all of that.

You can simply change the Schedule of the Prune Tables job to run more frequently, for tests.

If the error doesn’t come up when the job runs, then this particular issue seems to be fixed. If you have another issue keeping the job from finishing successfully, you should have a different error there.

Princeps_of_Nihils · 4 September 2018 15:55

Hi PGR

Prune Database scheduler still does not run at all. Looks like my workflows only work on Save as well. Maybe I have a cron error?

No error codes coming up in the log at all.

Princeps

pgr · 4 September 2018 17:19

That sounds like none of your jobs are running, so yes, that would mean a faulty cron configuration.

See Admin/Schedulers, bottom of screen, for instructions specific to your installation.

Then tell me exactly how you set up cron.

Princeps_of_Nihils · 5 September 2018 08:20

Hi PGR

My CRON Instructions at the bottom of Schedulers is

In order to run SuiteCRM Schedulers, edit your web server user’s crontab file with this command:
sudo crontab -e -u apache
… and add the following line to the crontab file:

cd /var/www/html; php -f cron.php > /dev/null 2>&1

I am already in as root so I do not use sudo but when I type in the command it looks like it is working. I have taken a screenshot of the current command.

Here is the screenshot

Princeps

pgr · 5 September 2018 09:33

Do you see any errors in your logs about Scheduler jobs or cron? Check both

suitecrm.log
php_errors.log

Also give this command

php -i | grep "'_'"

Use copy-paste to put that in, otherwise it’s hard not to mess up. That should be double-quote, single-quote, underscore, single_quote, double_quote.

Princeps_of_Nihils · 5 September 2018 10:07

Hi PGR

Copy and pasted the command in but it stated

[root@server ~]# php -i | grep “’_’”
-bash: php: command not found

It does that all the time with commands, I then resheduled a job.

SuiteCRM.log - has no errors at the time the job failed, nothing at all for an hour before.

php_errors.log - I do not think I have one set up. My php.ini file states

; Log errors to specified file. PHP’s default behavior is to leave this value
; empty.
; http://php.net/error-log
; Example:
;error_log = php_errors.log
; Log errors to syslog (Event Log on Windows).
;error_log = syslog

Prior to above.
I used whereis PHP and changed all the permissions on the stated folders to allow write access.

http://tinypic.com/r/2my11mu/9

I redid the cronjob and checked with crontab -e and got below.

http://tinypic.com/r/311me4m/9

Will setting up the php_errors.log make a difference?

Princeps

pgr · 5 September 2018 10:38

Yes, definitely set up php_errors.log. I usually put it on my SuiteCRM root directory so I can check it on the same place as suitecrn.log. Restart your web server.

You don’t have the “path” set up so you can call PHP from the command-line. That is why crontab doesn’t run PHP correctly.

So, check that your PHP is where I think it is by doing:

/usr/bin/php -i | grep php.ini

/usr/share/php -i | grep php.ini

One of these should work, and it will also tell you where your php.ini file is.

(you shouldn’t have changed permissions on those folders, by the way)

Tell me how this goes.

Princeps_of_Nihils · 5 September 2018 11:02

Hi PGR

Thank you so much for your help.

[root@server ~]# /usr/bin/php -i | grep php.ini
-bash: /usr/bin/php: No such file or directory
[root@server ~]# /usr/share/php -i | grep php.ini
-bash: /usr/share/php: Is a directory

The php.ini is located in the second share document not bin. Does that change anything?

Regarding the php_errors.log do you know of any step by step instructions to create it?

Princeps

Princeps_of_Nihils · 5 September 2018 14:21

I got into my cron log and looks like Cron jobs are working

Sep 5 14:58:01 server CROND[21164]: (apache) CMD (cd /var/www/html; php -f cron.php > /dev/null 2>&1)
Sep 5 14:59:01 server CROND[21231]: (root) CMD (/usr/share/php /var/www/html/cron.php >/dev/null 2>&1)
Sep 5 14:59:01 server CROND[21232]: (apache) CMD (cd /var/www/html; php -f cron.php > /dev/null 2>&1)
Sep 5 15:00:01 server CROND[21293]: (root) CMD (/usr/share/php /var/www/html/cron.php >/dev/null 2>&1)
Sep 5 15:00:01 server CROND[21294]: (root) CMD (/usr/lib64/sa/sa1 1 1)
Sep 5 15:00:01 server CROND[21295]: (apache) CMD (cd /var/www/html; php -f cron.php > /dev/null 2>&1)
Sep 5 15:01:01 server CROND[21363]: (root) CMD (/usr/share/php /var/www/html/cron.php >/dev/null 2>&1)
Sep 5 15:01:01 server CROND[21364]: (apache) CMD (cd /var/www/html; php -f cron.php > /dev/null 2>&1)
Sep 5 15:01:01 server CROND[21365]: (root) CMD (run-parts /etc/cron.hourly)
Sep 5 15:01:01 server run-parts(/etc/cron.hourly)[21365]: starting 0anacron
Sep 5 15:01:01 server run-parts(/etc/cron.hourly)[21379]: finished 0anacron
Sep 5 15:02:01 server CROND[21434]: (root) CMD (/usr/share/php /var/www/html/cron.php >/dev/null 2>&1)
Sep 5 15:02:01 server CROND[21435]: (apache) CMD (cd /var/www/html; php -f cron.php > /dev/null 2>&1)
Sep 5 15:03:01 server CROND[21495]: (apache) CMD (cd /var/www/html; php -f cron.php > /dev/null 2>&1)
Sep 5 15:03:01 server CROND[21496]: (root) CMD (/usr/share/php /var/www/html/cron.php >/dev/null 2>&1)
Sep 5 15:04:01 server CROND[21560]: (root) CMD (/usr/share/php /var/www/html/cron.php >/dev/null 2>&1)
Sep 5 15:04:01 server CROND[21561]: (apache) CMD (cd /var/www/html; php -f cron.php > /dev/null 2>&1)
Sep 5 15:05:01 server CROND[21626]: (root) CMD (/usr/share/php /var/www/html/cron.php >/dev/null 2>&1)
Sep 5 15:05:01 server CROND[21627]: (apache) CMD (cd /var/www/html; php -f cron.php > /dev/null 2>&1)
Sep 5 15:06:01 server CROND[21689]: (root) CMD (/usr/share/php /var/www/html/cron.php >/dev/null 2>&1)
Sep 5 15:06:01 server CROND[21690]: (apache) CMD (cd /var/www/html; php -f cron.php > /dev/null 2>&1)
Sep 5 15:07:01 server CROND[21749]: (root) CMD (/usr/share/php /var/www/html/cron.php >/dev/null 2>&1)
Sep 5 15:07:01 server CROND[21750]: (apache) CMD (cd /var/www/html; php -f cron.php > /dev/null 2>&1)
Sep 5 15:08:01 server CROND[21812]: (root) CMD (/usr/share/php /var/www/html/cron.php >/dev/null 2>&1)
Sep 5 15:08:01 server CROND[21813]: (apache) CMD (cd /var/www/html; php -f cron.php > /dev/null 2>&1)
Sep 5 15:09:01 server CROND[21878]: (root) CMD (/usr/share/php /var/www/html/cron.php >/dev/null 2>&1)
Sep 5 15:09:01 server CROND[21879]: (apache) CMD (cd /var/www/html; php -f cron.php > /dev/null 2>&1)
Sep 5 15:10:01 server CROND[21942]: (root) CMD (/usr/lib64/sa/sa1 1 1)
Sep 5 15:10:01 server CROND[21943]: (apache) CMD (cd /var/www/html; php -f cron.php > /dev/null 2>&1)
Sep 5 15:10:01 server CROND[21944]: (root) CMD (/usr/share/php /var/www/html/cron.php >/dev/null 2>&1)
Sep 5 15:11:01 server CROND[22008]: (root) CMD (/usr/share/php /var/www/html/cron.php >/dev/null 2>&1)
Sep 5 15:11:01 server CROND[22009]: (apache) CMD (cd /var/www/html; php -f cron.php > /dev/null 2>&1)
Sep 5 15:12:01 server CROND[22072]: (root) CMD (/usr/share/php /var/www/html/cron.php >

I am just not getting anything in SuiteCRM this is really frustrating.

Princeps

Princeps_of_Nihils · 5 September 2018 15:15

I also changed my array to below as user and group are root on my server.

  array (
    'dir_mode' => 1527,
    'file_mode' => 420,
    'user' => 'root',
    'group' => 'root',
  ),

No changes found so far.

pgr · 5 September 2018 15:16

That’s not a good way to tell, you just know that the system is issuing the command, you don’t know if it is failing (it is!)

To set up the php_errors.log all you have to do is uncomment that line in php.ini. To uncomment a line just remove the ; from the beginning. So it will look like

error_log = /var/www/html/php_errors.log

Then restart your web server.

We really need to find your php executable. How about any of these places, can you execute it?

/usr/share/php/bin/php -i | grep php.ini

Now, if that works, I want to know which php.ini path it reports. It doesn’t matter that you found a php.ini somewhere, there is more than one.

If that doesn’t work, then go inside SuiteCRM, Admin / diagnostics, select only “phpinfo”, download, unzip, open it, and get the executable path from there.

pgr · 5 September 2018 15:27

I also changed my array to below as user and group are root on my server.
  array (
    'dir_mode' => 1527,
    'file_mode' => 420,
    'user' => 'root',
    'group' => 'root',
  ),
No changes found so far.

Wrong! Please reverse this. You will also have to reset all ownerships and permissions again:

  sudo chown -R www-data:www-data .
  sudo chmod -R 755 .
  sudo chmod -R 775 cache custom modules themes data upload
  sudo chmod 775 config_override.php 2>/dev/null

The user you need to be concerned about is not the user you’re using to login; it is your web server user. By giving files’ ownership to root you are making them invisible to SuiteCRM. So stay calm, follow instructions, let’s do one thing at a time… :ohmy:

Princeps_of_Nihils · 5 September 2018 16:10

Hi Thank you so much for your help

I am getting really lost.The php.ini file is /etc/php.ini. The command didn’t I am having massive problems having my SSH respond to commands, it appears to use a different language! (Something simple takes forever)

Configuration File (php.ini) Path	/etc
Loaded Configuration File	/etc/php.ini
Scan this dir for additional .ini files	/etc/php.d

When I attempt to set file permissions I get invalid user for www-data

[root@server ~]# sudo chown -R www-data:www-data .
chown: invalid user: ‘www-data:www-data’

.

I want to fix the permissions I am really concerned about how this is moving forward.

Princeps

pgr · 5 September 2018 17:35

Which command provided the valid output, this one?

/usr/share/php/bin/php -i | grep php.ini

If so, we now know where your php executable is, it is in /usr/share/php/bin
And it is configured from /etc/php.ini

Now we just need to find out your web server user name. Is it Apache you’re running? If so, this should give you that answer:

ps -ef | egrep '(httpd|apache2|apache)' | grep -v `whoami` | grep -v root | head -n1 | awk '{print $1}'

That’s what you need to use instead of www-data in the chown command.

Princeps_of_Nihils · 6 September 2018 09:48

Hi PGR

I had to look up via the diagnostic to get the output. The command did not work for me comes up with the following.

[root@server ~]# /usr/share/php/bin/php -i | grep php.ini
-bash: /usr/share/php/bin/php: No such file or directory

I logged in as root so I used

I find / -name php.ini 2>/dev/null

The webserver name is apache

I am at a real loss why this keeps going wrong!

Princeps

pgr · 6 September 2018 13:40

One of the reasons this seems complicated is because our communication is a bit chaotic; I would prefer if you would always pair up which command you gave, and what it answered. Even for the failed commands. The messages might mean something to me, even if they don’t mean much to you.

Another thing: don’t lose hope, this is actually a simple problem, only you (apparently) don’t have much experience as a sysadmin, and I have to make guesses and work from a distance, which makes things complicated.

But you simply have a bash shell that doesn’t have a properly set “path”, so it doesn’t find commands easily; and you need to set SuiteCRM permissions but first you have to find out some details about your system.

If “apache” is the user name your Web server is running under (and not just the brand of the server like you wrote: “The webserver name is apache”), then issue all these commands from the root of your SuiteCRM installation:


  sudo chown -R apache:apache .
  sudo chmod -R 755 .
  sudo chmod -R 775 cache custom modules themes data upload
  sudo chmod 775 config_override.php 2>/dev/null

Now, I still don’t know the path of your php executable. Can you send me the entire phpinfo that you retrieve from Admin / Diagnostic?

Princeps_of_Nihils · 6 September 2018 13:54

Hi PGR

Myself not being a sysadmin has meant I hit a steep learning curve and that has impacted our communication I am sorry.

When I typed in the code below, I got no reponse.

 sudo chown -R apache:apache .
  sudo chmod -R 755 .
  sudo chmod -R 775 cache custom modules themes data upload
  sudo chmod 775 config_override.php 2>/dev/null

The company which is hosting my package kindly stepped in when I asked why the command above was not working. You had rightly pointed out it was a permissions issue. They fixed it and Cron so it now runs my scheduled jobs. (The web company is really awesome)

The issue has been resolved and I do not know what they did to fix it. I have asked them for the details so I can list them here in the forum, but they may not come back to me with the nitty gritty stuff. (for which I am sorry to anyone who reads this in future)

I will keep chasing them as I really want to know how to fix it.

Thank you for your help PGR do you have a email I can send the PHP info to?

Princeps