0

I have a console app written with C# on the top of .Net Core 2.2 framework with EntityFrameworkCore as ORM. My application runs various tasks that run every 15 minutes. Each task calls an external API server and updates my internal MySQL database (MySQL v8.16.) If the job fails for any reason, I get an email with the error.

I got the error email with the following error

Error Message: Out of range value for column 'Value' at row 1

I am trying to figure out what Value column is causing this error and trying to figure out what query has caused this issue.

From the look of it it, MySQL does not log the invalid queries anywhere which sends me on a wild search for the problem column.

I looked up all columns that are named 'Value` in my database using the following query

SELECT table_name, Is_nullable, data_type, column_type 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE Table_Schema = 'my_table_name' and Column_Name = 'Value'

The above query gave me the following results

table_name    Is_nullable  data_type    column_type 
field_values        NO     varchar      varchar(128)
boolean_fields      YES    tinyint      tinyint(1)
lookup_fields       YES    text         text
numeric_fields      YES    decimal      decimal(30,6)
text_fields         YES    longtext     longtext
time_fields         YES    datetime     datetime(6)
user_tokens         YES    longtext     longtext

I think if the error was caused by a column with the type of varchar/longtext I would get "Text truncated" error not "Out of range value". With that in mind, I am left with the following columns as a suspect

table_name    Is_nullable  data_type    column_type 
boolean_fields      YES    tinyint      tinyint(1)
numeric_fields      YES    decimal      decimal(30,6)
time_fields         YES    datetime     datetime(6)

Furthermore, since my application is written in C# and EntityFrameworkCore is used as my ORM to interact with the database, I feel confident that both boolean_fields.Value and time_fields.Value are not causing the issue. My conclusion is because the property type is DataTime? in the TimeField entity-model where the property type is bool for my BooleanField entity-model.

With the above assumption in mind, I am down to one field that could be causing my problem.

table_name    Is_nullable  data_type    column_type 
numeric_fields      YES    decimal      decimal(30,6)

I store all numeric values in to numeric_fields.Value column (ie, int, decimal and long)

The API is expected to return numeric data using the following

data_type      MinValue              MaxValue
'Int',         '-2147483648.0000',   '2147483647.0000'
'Decimal',     '-999999999999.0000,  '999999999999.0000'
'Long',        NULL,                 '9223372036854775807.0000'

I should be able to store all the above numeric in decimal(30,6) column with no problem.

How can I identify what could be causing this problem?

  • If the error is from an insert or update are you outputting the values that are getting set before the statement is executed? – Aaron May 18 at 19:55
  • @Aaron, unfortunately, that information isn't available in the email that I get. This issue does not happen all the time, so I am unable to reproduce it which is why I am hoping I can get some sort of lead to trace. – Junior May 18 at 20:06
  • Could you modify the app to log the data to a file so you know when you get the email to go look at the file? – Aaron May 18 at 20:29
  • Use SHOW CREATE TABLE my_table_name. DECIMAL(30,6) tops out at 999999999999999999999999.999999, not the value shown. Without further clues of which "value" is out of range, I can't help. – Rick James May 19 at 14:17
  • @RickJames I am still having this issue. I tried to modify my jobs to throw a more meaningful exception but I can't seem to figure out the value that is causing this error. Is there a way to configure MySQL server to log the query along with the value when a query fails so I can see what is causing this issue? – Junior Jun 27 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.