You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/04/24 14:09:37 UTC
[GitHub] [shardingsphere] ppourali opened a new issue #5308: XML Types in PostgreSQL is not listed in ShardingProxy
ppourali opened a new issue #5308:
URL: https://github.com/apache/shardingsphere/issues/5308
## Potential bug Report
Hello, during using the Sharding Proxy, I found that it throws error on the columns with xml type in postgresql.
### Which version of ShardingSphere did you use?
Version 5.0.0-RC1
### Which project did you use? Sharding-JDBC or Sharding-Proxy?
Sharding-Proxy
### Expected behavior
Returning the column's content
### Actual behavior
Throwing error for unknown column type
### Reason analyze (If you can)
The reason is that the XML type (2009) is not defined in the PostgreSQLColumnType enum class.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
1- Run sharding proxy
2- Connect to Sharding Proxy
3- Create table1 with an xml typed column
4- SELECT * FROM table1
### Example codes for reproduce this issue (such as a github link).
1) I was working on the AdventureWorks Database (postgres version). Here is the script to create the table:
```
-- Table: person.person
-- DROP TABLE person.person;
CREATE TABLE person.person
(
businessentityid integer NOT NULL,
persontype character(2) COLLATE pg_catalog."default" NOT NULL,
namestyle "NameStyle" NOT NULL DEFAULT false,
title character varying(8) COLLATE pg_catalog."default",
firstname "Name" COLLATE pg_catalog."default" NOT NULL,
middlename "Name" COLLATE pg_catalog."default",
lastname "Name" COLLATE pg_catalog."default" NOT NULL,
suffix character varying(10) COLLATE pg_catalog."default",
emailpromotion integer NOT NULL DEFAULT 0,
additionalcontactinfo xml,
demographics xml,
rowguid uuid NOT NULL DEFAULT uuid_generate_v1(),
modifieddate timestamp without time zone NOT NULL DEFAULT now(),
CONSTRAINT "PK_Person_BusinessEntityID" PRIMARY KEY (businessentityid),
CONSTRAINT "FK_Person_BusinessEntity_BusinessEntityID" FOREIGN KEY (businessentityid)
REFERENCES person.businessentity (businessentityid) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT "CK_Person_EmailPromotion" CHECK (emailpromotion >= 0 AND emailpromotion <= 2),
CONSTRAINT "CK_Person_PersonType" CHECK (persontype IS NULL OR (upper(persontype::text) = ANY (ARRAY['SC'::text, 'VC'::text, 'IN'::text, 'EM'::text, 'SP'::text, 'GC'::text])))
)
TABLESPACE pg_default;
ALTER TABLE person.person
OWNER to postgres;
COMMENT ON TABLE person.person
IS 'Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.';
COMMENT ON COLUMN person.person.businessentityid
IS 'Primary key for Person records.';
COMMENT ON COLUMN person.person.persontype
IS 'Primary type of person: SC = Store Contact, IN = Individual (retail) customer, SP = Sales person, EM = Employee (non-sales), VC = Vendor contact, GC = General contact';
COMMENT ON COLUMN person.person.namestyle
IS '0 = The data in FirstName and LastName are stored in western style (first name, last name) order. 1 = Eastern style (last name, first name) order.';
COMMENT ON COLUMN person.person.title
IS 'A courtesy title. For example, Mr. or Ms.';
COMMENT ON COLUMN person.person.firstname
IS 'First name of the person.';
COMMENT ON COLUMN person.person.middlename
IS 'Middle name or middle initial of the person.';
COMMENT ON COLUMN person.person.lastname
IS 'Last name of the person.';
COMMENT ON COLUMN person.person.suffix
IS 'Surname suffix. For example, Sr. or Jr.';
COMMENT ON COLUMN person.person.emailpromotion
IS '0 = Contact does not wish to receive e-mail promotions, 1 = Contact does wish to receive e-mail promotions from AdventureWorks, 2 = Contact does wish to receive e-mail promotions from AdventureWorks and selected partners.';
COMMENT ON COLUMN person.person.additionalcontactinfo
IS 'Additional contact information about the person stored in xml format.';
COMMENT ON COLUMN person.person.demographics
IS 'Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.';
```
2) Use the sample following java code to select from the table, or use psql.
```
package example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;
public class PostgreCon {
public static void main(String args[]) {
try {
Class.forName("org.postgresql.Driver");
Connection con = DriverManager.getConnection("jdbc:postgresql://localhost:3307/adventureworks", "username",
"12345678");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * from person.person");
con.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}
```
3) My Sharding Proxy config (transparent mode):
```
schemaName: adventureworks
dataSource:
url: jdbc:postgresql://127.0.0.1:5432/Adventureworks?serverTimezone=UTC&useSSL=false&useUnicode=true&allowEncodingChanges=true
username: username
password: 12345678
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
```
Thanks,
Best Regards,
Parsa
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] dongzl commented on issue #5308: Bug: XML Types in PostgreSQL is not listed in ShardingProxy
Posted by GitBox <gi...@apache.org>.
dongzl commented on issue #5308:
URL: https://github.com/apache/shardingsphere/issues/5308#issuecomment-630094266
I will try fix it.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] terrymanu commented on issue #5308: Bug: XML Types in PostgreSQL is not listed in ShardingProxy
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #5308:
URL: https://github.com/apache/shardingsphere/issues/5308#issuecomment-630081660
Does anybody familiar PostgreSQL protocol?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 closed issue #5308: Bug: XML Types in PostgreSQL is not listed in ShardingProxy
Posted by GitBox <gi...@apache.org>.
tuohai666 closed issue #5308:
URL: https://github.com/apache/shardingsphere/issues/5308
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] ppourali commented on issue #5308: XML Types in PostgreSQL is not listed in ShardingProxy
Posted by GitBox <gi...@apache.org>.
ppourali commented on issue #5308:
URL: https://github.com/apache/shardingsphere/issues/5308#issuecomment-621377024
Does anybody know about this?
Parsa
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org