Heroku：从MySQL迁移数据库后Postgres输入运算符错误 [英] Heroku: Postgres type operator error after migrating DB from MySQL

问题描述

这是后面提到的一个问题，我之前问过这个问题，认为这不仅仅是一个数据库问题，而是更多的编程问题。

在Heroku上与Sinatra / Haml / DataMapper导致错误

我认为在运行 db：push 之后，Heroku的Postgres数据库中的ID列存储问题已被隔离。

总之，我的应用程序在我的原始MySQL数据库上正常运行，但在ID列上执行任何查询时，Heroku会抛出Postgres错误，这似乎已经作为TEXT存储在Postgres中尽管它在MySQL中作为INT存储。我的问题是为什么在向Heroku传输数据时Postgres中的ID列被创建为INT，以及是否有任何方法可以阻止这种情况。

以下是输出从 heroku控制台会话中显示问题：

  myapp.heroku.com 
>> Post.first.title 
 => 欢迎来到第一！ 
>> Post.first.title.class 
 =>字串
>> Post.first.id 
 => 1 
>> Post.first.id.class 
 => Fixnum 
>> Post [1] 
 PostgresError：错误：运算符不存在：text = integer 
 LINE 1：...，title，created_atFROMpostsWHERE（id= 1 ）ORDER ... 
 ^ 
提示：没有操作符与给定的名称和参数类型匹配您可能需要添加显式类型转换
查询：SELECTid，名称，email，url，title，created_atFROMpostsWHERE（id= 1）ORDER BYidLIMIT 1 
  pre> 
 
 
谢谢！
解决方案
并且他们的人能够很快地为我解决这个问题，在他们发送我的MySQL表格模式之后，他们建议我从ID列中删除 UNSIGNED ：
 
 
你可以删除UNSIGNED位，看看它是否有效吗？我不认为续集支持这个。如果这样，我会为续集写一个补丁。  
 
一旦我这样做了，我就能够o使用 db：push 以与之前相同的方式迁移数据库，并且该应用程序功能完全正常。
 
 
 
持续对Heroku有更深的印象，无论是平台还是支持。 
This is a follow-up to a question I'd asked earlier which phrased this as more of a programming problem than a database problem.

Postgres error with Sinatra/Haml/DataMapper on Heroku

I believe the problem has been isolated to the storage of the ID column in Heroku's Postgres database after running db:push.

In short, my app runs properly on my original MySQL database, but throws Postgres errors on Heroku when executing any query on the ID column, which seems to have been stored in Postgres as TEXT even though it is stored as INT in MySQL. My question is why the ID column is being created as INT in Postgres on the data transfer to Heroku, and whether there's any way for me to prevent this.

Here's the output from a heroku console session which demonstrates the issue:
Ruby console for myapp.heroku.com
>> Post.first.title
=> "Welcome to First!"
>> Post.first.title.class
=> String
>> Post.first.id
=> 1
>> Post.first.id.class
=> Fixnum
>> Post[1]
PostgresError: ERROR:  operator does not exist: text = integer
LINE 1: ...", "title", "created_at" FROM "posts" WHERE ("id" = 1) ORDER...
                                                         ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
Query: SELECT "id", "name", "email", "url", "title", "created_at" FROM "posts" WHERE ("id" = 1) ORDER BY "id" LIMIT 1
Thanks!
解决方案
I opened a support request at Heroku and their guys were able to quickly resolve this for me. After sending them my MySQL table schema, they suggested that I remove UNSIGNED from my ID columns:

  
Can you remove the UNSIGNED bit and see if that works? I don't think sequel supports that. If that works, I'll write a patch to sequel.
Once I did that, I was able to migrate the database the same way as before using db:push, and the app was fully functional.

Continually more impressed w/ Heroku, both for their platform and support.

                        
这篇关于Heroku：从MySQL迁移数据库后Postgres输入运算符错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！