search

rails-sqlserver / activerecord-sqlserver-adapter

SQL Server Adapter For Rails
MIT License
968 stars 558 forks source link

TinyTds::Error: Error converting data type varchar to numeric. #1112

Closed no1-knows closed 8 months ago

no1-knows commented 8 months ago

Issue

ActiveRecord query get error but raw SQL is fine

Expected behavior

WbMss.all.count
=> 5

Actual behavior

WbMss.all.count
/usr/local/bundle/gems/activerecord-sqlserver-adapter-7.0.4.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:454:in `each': TinyTds::Error: Error converting data type varchar to numeric. (ActiveRecord::StatementInvalid)
/usr/local/bundle/gems/activerecord-sqlserver-adapter-7.0.4.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:454:in `each': Error converting data type varchar to numeric. (TinyTds::Error)

Big question

raw query is fine but active record doesn't

sql = WbMss.all.to_sql
=> "SELECT [WB_MSS].* FROM [WB_MSS]"
result = ActiveRecord::Base.connection.exec_query(sql)
=> #<ActiveRecord::Result:0x00007fd782affe18
result.count
=> 5

Additional Info

I could access the other tables correctly but only this table have error....

Details

Compile-time settings (established with the "configure" script)
                            Version: freetds v1.2.3
             freetds.conf directory: /etc/freetds
     MS db-lib source compatibility: no
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: auto
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: yes
                            OpenSSL: no
                             GnuTLS: yes
                               MARS: yes

UPDATED

I tried schema:dump because not enough table info in schema.rb

rails db:schema:dump
/usr/local/bundle/gems/activerecord-sqlserver-adapter-7.0.4.0/lib/active_record/connection_adapters/sqlserver_adapter.rb:111: warning: undefining the allocator of T_DATA class TinyTds::Result
rails aborted!
ActiveRecord::StatementInvalid: TinyTds::Error: Error converting data type varchar to numeric.

Caused by:
TinyTds::Error: Error converting data type varchar to numeric.

Tasks: TOP => db:schema:dump
(See full trace by running task with --trace)
aidanharan commented 8 months ago

@no1-knows, Did the issue only start with v7.0.4.0? Does v7.0.3.0 have the same issue? Would you be able to create a script so that I can reproduce the issue? Thanks

no1-knows commented 8 months ago

@aidanharan v7.0.2.0 had same issue. not tried v7.0.3.0. I was developing sqlserver in docker which is no problem. Now switch to production server which is not docker and have problem only this model. I'm not sure if I can create a script because we're short on time before the deadline...

aidanharan commented 8 months ago

You need to find the SQL that is actually being sent to your database as it looks like the issue is with the generated SQL.

If you open a Rails console and run WbMss.all.count then you should see the SQL. You might need to change your logging level to see it. If that doesn't work then run following from your Rails console.

ActiveSupport::Notifications.subscribe('sql.active_record') do |_name, _start, _finish, _id, payload|
   puts payload[:sql]
end
WbMss.all.count
no1-knows commented 8 months ago

Thanks for your info! I'm newbie but I understand a bit.

The flow to execute SQL is like follows:

  1. Rails access DB server and get table and columns info
  2. Rails create sql based on 1st info
  3. Error occur at WbMss

This means 1st info might be not correct. That's why u said this.

it looks like the issue is with the generated SQL.

So I have 2 approaches

  1. Use to_sql and exec_query

sql = "SELECT [WB_MSS].* FROM [WB_MSS]" result = ActiveRecord::Base.connection.exec_query(sql)

  1. Fix SQL Server setting

Is this correct? 

ActiveSupport::Notifications.subscribe('sql.active_record') do |_name, _start, _finish, _id, payload|
   puts payload[:sql]
end
WbMss.all.count

DBCC USEROPTIONS WITH NO_INFOMSGS
USE [SKWEB]
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WITH (NOLOCK) WHERE TABLE_CATALOG = DB_NAME() AND TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'WB_MSS' AND TABLE_TYPE = N'VIEW' ORDER BY TABLE_NAME
EXEC sp_executesql N'SELECT o.name AS [table_name], c.name AS [name], t.name AS [type], d.definition AS [default_value], CASE WHEN t.name IN (''decimal'', ''bigint'', ''int'', ''money'', ''numeric'', ''smallint'', ''smallmoney'', ''tinyint'') THEN c.scale END AS [numeric_scale], CASE WHEN t.name IN (''decimal'', ''bigint'', ''int'', ''money'', ''numeric'', ''smallint'', ''smallmoney'', ''tinyint'', ''real'', ''float'') THEN c.precision END AS [numeric_precision], CASE WHEN t.name IN (''date'', ''datetime'', ''datetime2'', ''datetimeoffset'', ''smalldatetime'', ''time'') THEN c.scale END AS [datetime_precision], c.collation_name AS [collation], ROW_NUMBER() OVER (ORDER BY c.column_id) AS [ordinal_position], CASE WHEN t.name IN (''nchar'', ''nvarchar'') AND c.max_length > 0 THEN c.max_length / 2 ELSE c.max_length END AS [length], CASE c.is_nullable WHEN 1 THEN 1 END AS [is_nullable], CASE WHEN ic.object_id IS NOT NULL THEN 1 END AS [is_primary], c.is_identity AS [is_identity] FROM .sys.columns c INNER JOIN .sys.objects o ON c.object_id = o.object_id INNER JOIN .sys.schemas s ON o.schema_id = s.schema_id INNER JOIN .sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id LEFT OUTER JOIN .sys.default_constraints d ON c.object_id = d.parent_object_id AND c.default_object_id = d.object_id LEFT OUTER JOIN .sys.key_constraints k ON c.object_id = k.parent_object_id AND k.type = ''PK'' LEFT OUTER JOIN .sys.index_columns ic ON k.parent_object_id = ic.object_id AND k.unique_index_id = ic.index_id AND c.column_id = ic.column_id WHERE o.name = @0 AND s.name = schema_name() ORDER BY c.column_id', N'@0 nvarchar(128)', @0 = N'WB_MSS'
SELECT CAST('' AS char(2)) AS value
SELECT CAST('' AS char(1)) AS value
SELECT CAST('' AS varchar(10)) AS value
SELECT CAST('' AS varchar(6)) AS value
SELECT CAST('' AS char(7)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS char(7)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS char(7)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(24)) AS value
SELECT CAST('' AS char(8)) AS value
SELECT CAST('' AS char(9)) AS value
SELECT CAST('' AS char(12)) AS value
SELECT CAST('' AS numeric(18,0)) AS value
/usr/local/bundle/gems/activerecord-sqlserver-adapter-7.0.4.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:454:in `each': TinyTds::Error: Error converting data type varchar to numeric. (ActiveRecord::StatementInvalid)
/usr/local/bundle/gems/activerecord-sqlserver-adapter-7.0.4.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:454:in `each': Error converting data type varchar to numeric. (TinyTds::Error)

ActiveSupport::Notifications.subscribe('sql.active_record') do |_name, _start, _finish, _id, payload|
   puts payload[:sql]
end
WbFzi.all.count

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WITH (NOLOCK) WHERE TABLE_CATALOG = DB_NAME() AND TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'WB_FZI' AND TABLE_TYPE = N'VIEW' ORDER BY TABLE_NAME
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WITH (NOLOCK) WHERE TABLE_CATALOG = DB_NAME() AND TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'WB_FZI' AND TABLE_TYPE = N'VIEW' ORDER BY TABLE_NAME
EXEC sp_executesql N'SELECT o.name AS [table_name], c.name AS [name], t.name AS [type], d.definition AS [default_value], CASE WHEN t.name IN (''decimal'', ''bigint'', ''int'', ''money'', ''numeric'', ''smallint'', ''smallmoney'', ''tinyint'') THEN c.scale END AS [numeric_scale], CASE WHEN t.name IN (''decimal'', ''bigint'', ''int'', ''money'', ''numeric'', ''smallint'', ''smallmoney'', ''tinyint'', ''real'', ''float'') THEN c.precision END AS [numeric_precision], CASE WHEN t.name IN (''date'', ''datetime'', ''datetime2'', ''datetimeoffset'', ''smalldatetime'', ''time'') THEN c.scale END AS [datetime_precision], c.collation_name AS [collation], ROW_NUMBER() OVER (ORDER BY c.column_id) AS [ordinal_position], CASE WHEN t.name IN (''nchar'', ''nvarchar'') AND c.max_length > 0 THEN c.max_length / 2 ELSE c.max_length END AS [length], CASE c.is_nullable WHEN 1 THEN 1 END AS [is_nullable], CASE WHEN ic.object_id IS NOT NULL THEN 1 END AS [is_primary], c.is_identity AS [is_identity] FROM .sys.columns c INNER JOIN .sys.objects o ON c.object_id = o.object_id INNER JOIN .sys.schemas s ON o.schema_id = s.schema_id INNER JOIN .sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id LEFT OUTER JOIN .sys.default_constraints d ON c.object_id = d.parent_object_id AND c.default_object_id = d.object_id LEFT OUTER JOIN .sys.key_constraints k ON c.object_id = k.parent_object_id AND k.type = ''PK'' LEFT OUTER JOIN .sys.index_columns ic ON k.parent_object_id = ic.object_id AND k.unique_index_id = ic.index_id AND c.column_id = ic.column_id WHERE o.name = @0 AND s.name = schema_name() ORDER BY c.column_id', N'@0 nvarchar(128)', @0 = N'WB_FZI'
EXEC sp_executesql N'SELECT o.name AS [table_name], c.name AS [name], t.name AS [type], d.definition AS [default_value], CASE WHEN t.name IN (''decimal'', ''bigint'', ''int'', ''money'', ''numeric'', ''smallint'', ''smallmoney'', ''tinyint'') THEN c.scale END AS [numeric_scale], CASE WHEN t.name IN (''decimal'', ''bigint'', ''int'', ''money'', ''numeric'', ''smallint'', ''smallmoney'', ''tinyint'', ''real'', ''float'') THEN c.precision END AS [numeric_precision], CASE WHEN t.name IN (''date'', ''datetime'', ''datetime2'', ''datetimeoffset'', ''smalldatetime'', ''time'') THEN c.scale END AS [datetime_precision], c.collation_name AS [collation], ROW_NUMBER() OVER (ORDER BY c.column_id) AS [ordinal_position], CASE WHEN t.name IN (''nchar'', ''nvarchar'') AND c.max_length > 0 THEN c.max_length / 2 ELSE c.max_length END AS [length], CASE c.is_nullable WHEN 1 THEN 1 END AS [is_nullable], CASE WHEN ic.object_id IS NOT NULL THEN 1 END AS [is_primary], c.is_identity AS [is_identity] FROM .sys.columns c INNER JOIN .sys.objects o ON c.object_id = o.object_id INNER JOIN .sys.schemas s ON o.schema_id = s.schema_id INNER JOIN .sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id LEFT OUTER JOIN .sys.default_constraints d ON c.object_id = d.parent_object_id AND c.default_object_id = d.object_id LEFT OUTER JOIN .sys.key_constraints k ON c.object_id = k.parent_object_id AND k.type = ''PK'' LEFT OUTER JOIN .sys.index_columns ic ON k.parent_object_id = ic.object_id AND k.unique_index_id = ic.index_id AND c.column_id = ic.column_id WHERE o.name = @0 AND s.name = schema_name() ORDER BY c.column_id', N'@0 nvarchar(128)', @0 = N'WB_FZI'
SELECT CAST('' AS char(2)) AS value
SELECT CAST('' AS char(2)) AS value
SELECT CAST('' AS char(1)) AS value
SELECT CAST('' AS char(1)) AS value
SELECT CAST('' AS varchar(10)) AS value
SELECT CAST('' AS varchar(10)) AS value
SELECT CAST('' AS varchar(6)) AS value
SELECT CAST('' AS varchar(6)) AS value
SELECT CAST('' AS char(12)) AS value
SELECT CAST('' AS char(12)) AS value
SELECT CAST('' AS char(3)) AS value
SELECT CAST('' AS char(3)) AS value
SELECT CAST((0) AS smallint) AS value
SELECT CAST((0) AS smallint) AS value
SELECT CAST((0) AS numeric(9,2)) AS value
SELECT CAST((0) AS numeric(9,2)) AS value
SELECT CAST('' AS char(12)) AS value
SELECT CAST('' AS char(12)) AS value
SELECT CAST('' AS char(8)) AS value
SELECT CAST('' AS char(8)) AS value
SELECT CAST('' AS char(2)) AS value
SELECT CAST('' AS char(2)) AS value
SELECT CAST('' AS varchar(12)) AS value
SELECT CAST('' AS varchar(12)) AS value
SELECT CAST('' AS varchar(8)) AS value
SELECT CAST('' AS varchar(8)) AS value
SELECT CAST('' AS varchar(10)) AS value
SELECT CAST('' AS varchar(10)) AS value
SELECT CAST('' AS varchar(4)) AS value
SELECT CAST('' AS varchar(4)) AS value
SELECT CAST('' AS char(2)) AS value
SELECT CAST('' AS char(2)) AS value
SELECT CAST('' AS varchar(10)) AS value
SELECT CAST('' AS varchar(10)) AS value
SELECT CAST('' AS varchar(8)) AS value
SELECT CAST('' AS varchar(8)) AS value
SELECT CAST((0) AS numeric(6,3)) AS value
SELECT CAST((0) AS numeric(6,3)) AS value
SELECT CAST((0) AS numeric(5,1)) AS value
SELECT CAST((0) AS numeric(5,1)) AS value
SELECT CAST('' AS char(5)) AS value
SELECT CAST('' AS char(5)) AS value
SELECT CAST('' AS varchar(18)) AS value
SELECT CAST('' AS varchar(18)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS char(5)) AS value
SELECT CAST('' AS char(5)) AS value
SELECT CAST('' AS varchar(18)) AS value
SELECT CAST('' AS varchar(18)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS char(3)) AS value
SELECT CAST('' AS char(3)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(24)) AS value
SELECT CAST('' AS varchar(24)) AS value
SELECT CAST('' AS varchar(14)) AS value
SELECT CAST('' AS varchar(14)) AS value
SELECT CAST('' AS varchar(14)) AS value
SELECT CAST('' AS varchar(14)) AS value
SELECT CAST('' AS varchar(14)) AS value
SELECT CAST('' AS varchar(14)) AS value
SELECT CAST('' AS char(7)) AS value
SELECT CAST('' AS char(7)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS char(7)) AS value
SELECT CAST('' AS char(7)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS char(7)) AS value
SELECT CAST('' AS char(7)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('00000' AS char(5)) AS value
SELECT CAST('00000' AS char(5)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT COUNT(*) FROM (SELECT 1 AS one FROM [WB_FZI]) subquery_for_count
SELECT COUNT(*) FROM (SELECT 1 AS one FROM [WB_FZI]) subquery_for_count
=> 2174
aidanharan commented 8 months ago

The query that is throwing the error is:

SELECT CAST('' AS numeric(18,0)) AS value

It appears the default value of a column is an empty string but the column type is a number, which doesn't make sense. If you run the following query then you might be able to work out which column is causing this issue and what the fix might be.

EXEC sp_executesql N'SELECT o.name AS [table_name], c.name AS [name], t.name AS [type], d.definition AS [default_value], CASE WHEN t.name IN (''decimal'', ''bigint'', ''int'', ''money'', ''numeric'', ''smallint'', ''smallmoney'', ''tinyint'') THEN c.scale END AS [numeric_scale], CASE WHEN t.name IN (''decimal'', ''bigint'', ''int'', ''money'', ''numeric'', ''smallint'', ''smallmoney'', ''tinyint'', ''real'', ''float'') THEN c.precision END AS [numeric_precision], CASE WHEN t.name IN (''date'', ''datetime'', ''datetime2'', ''datetimeoffset'', ''smalldatetime'', ''time'') THEN c.scale END AS [datetime_precision], c.collation_name AS [collation], ROW_NUMBER() OVER (ORDER BY c.column_id) AS [ordinal_position], CASE WHEN t.name IN (''nchar'', ''nvarchar'') AND c.max_length > 0 THEN c.max_length / 2 ELSE c.max_length END AS [length], CASE c.is_nullable WHEN 1 THEN 1 END AS [is_nullable], CASE WHEN ic.object_id IS NOT NULL THEN 1 END AS [is_primary], c.is_identity AS [is_identity] FROM .sys.columns c INNER JOIN .sys.objects o ON c.object_id = o.object_id INNER JOIN .sys.schemas s ON o.schema_id = s.schema_id INNER JOIN .sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id LEFT OUTER JOIN .sys.default_constraints d ON c.object_id = d.parent_object_id AND c.default_object_id = d.object_id LEFT OUTER JOIN .sys.key_constraints k ON c.object_id = k.parent_object_id AND k.type = ''PK'' LEFT OUTER JOIN .sys.index_columns ic ON k.parent_object_id = ic.object_id AND k.unique_index_id = ic.index_id AND c.column_id = ic.column_id WHERE o.name = @0 AND s.name = schema_name() ORDER BY c.column_id', N'@0 nvarchar(128)', @0 = N'WB_MSS'
no1-knows commented 8 months ago

DB column was wrong which rails don't create. Thanks for your support!!!