Author Topic: SQL Query not working after using it for 3 years without issues, [solved]  (Read 308 times)

mailblade

  • Jr. Member
  • **
  • Posts: 109
  • A beginner
I've been running the below piece of code in an event scheduler for the past 3 years without any issue.

Code: [Select]
UPDATE `jos_virtuemart_product_customfields` SET `customfield_value`= `customfield_value` - 1 WHERE `virtuemart_custom_id` = 22 AND `customfield_value` !="0"
However, since yesterday, I have been getting the following error:

Quote
#1292 - Truncated incorrect DOUBLE value: ''

I haven't changed anything in my Virtuemart DB or done anything remotely different. Has anyone experienced this before ?

AH

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3165
  • VirtueMart Version: 3.6.11
Re: SQL Query not working after using it for 3 years without issues
« Reply #1 on: May 05, 2020, 09:35:22 am »
Tested here and seems to work fine
regards
A

Joomla 3.9.18
php 7.3

Jörgen

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 2750
    • Kreativ Fotografi
  • VirtueMart Version: 3.4.x
Re: SQL Query not working after using it for 3 years without issues
« Reply #2 on: May 05, 2020, 09:58:05 am »
Some googling

Quote
This message means you're trying to compare a number and a string in a WHERE clause; either make sure they have similar declarations, or use an explicit CAST to convert the number to a string. If you turn off strict mode, the error should turn into a warning.Jun 30, 2016

Jörgen @ Kreativ Fotografi
Joomla 3.9.18
Virtuemart 3.4.x
Olympiantheme Hera (customized)

mailblade

  • Jr. Member
  • **
  • Posts: 109
  • A beginner
Re: SQL Query not working after using it for 3 years without issues
« Reply #3 on: May 06, 2020, 08:22:32 am »
@AH thank you, glad it is still working. Must be something wrong on my end then.

@Jorgen: I also found the above page, thanks for providing the quotes. I will try fixing it.


Jörgen

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 2750
    • Kreativ Fotografi
  • VirtueMart Version: 3.4.x
Re: SQL Query not working after using it for 3 years without issues
« Reply #4 on: May 06, 2020, 08:42:28 am »
Tell us how it goes.

Jörgen @ Kreativ Fotografi
Joomla 3.9.18
Virtuemart 3.4.x
Olympiantheme Hera (customized)

mailblade

  • Jr. Member
  • **
  • Posts: 109
  • A beginner
Re: SQL Query not working after using it for 3 years without issues
« Reply #5 on: May 06, 2020, 08:47:33 am »
@Jurgen: I just ran it again and all seems back to normal. That was pretty weird  :D

I did re-upload all the customfield_values where virtuemart_custom_id = 22 yesterday afternoon, so maybe that helped to sort it out.

Jörgen

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 2750
    • Kreativ Fotografi
  • VirtueMart Version: 3.4.x
Re: SQL Query not working after using it for 3 years without issues
« Reply #6 on: May 06, 2020, 09:55:47 am »
Nice to hear that it works again, pity that the cause remains some what unknown.

Jörgen @ Kreativ Fotografi
Joomla 3.9.18
Virtuemart 3.4.x
Olympiantheme Hera (customized)

mailblade

  • Jr. Member
  • **
  • Posts: 109
  • A beginner
Re: SQL Query not working after using it for 3 years without issues
« Reply #7 on: May 09, 2020, 08:26:13 am »
I found the reason why it was not working!

There was one null value in the customfield_values where virtuemart_custom_id = 22, so it was impossible to minus this value by "1".  This is why I got the error!

AH

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3165
  • VirtueMart Version: 3.6.11
Re: SQL Query not working after using it for 3 years without issues
« Reply #8 on: May 09, 2020, 11:28:16 am »
Great - thanks for the update
regards
A

Joomla 3.9.18
php 7.3