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