Hey Josh and Julian,Thanks for your input regarding this. I also spent hours and hours over the last few days trying to get to the bottom of this, and it honestly wasn't the best use of my time either. I am sure being in different timezones also makes it much harder to this problem resolved quicker.
Because protobufs is a binary protocol, it can be somewhat difficult to debug. I tried installing a no-longer-maintained wireshark dissector to but I wasn't able to get it to compile. I also tried a few Fiddler2 plugins and the Charles debugger, but they also ended up being dead ends.
While writing a script to demonstrate the problem might be helpful for a lot of cases, I don't think it would be helpful here. For example, we could mandate that a reproduction be written in Java, but the problem could actually be how the golang protobuf library (https://github.com/golang/protobuf) is encoding the messages and the test script would not be able to catch it. At the same time, having people submitting scripts written in all sorts of different languages isn't going to help either.
Go has a concept of using "golden files", which I think would be extremely useful here. This is basically running some test input and capturing the output as a file (if the test and other checks passes). In future runs, the tests runs and is compared against the golden files. Also, if the tests are updated, the golden files can be automatically updated by just running the tests. This video gives a pretty good overview of how they work:
In the case of driver implementers, I think it would be extremely useful to have a bunch of "golden files" for us to test against. In our case, this might just be dumps of encoded protobuf messages for a given input. These golden files would be automatically generated from the avatica remote driver test suite. After a successful build, it can save the protobufs encoded by the remote driver somewhere. Driver implementers can then just take the input used, generate the output and do a byte by byte comparison against the golden files. If the driver does not encode the message correctly, it should be pretty evident.
I would love to hear what ideas you guys have regarding presenting the input in a language agnostic way. In a lot of cases, it's not simply having a SQL statement. For example, parameters provided to a statement might be an external file. Also, meta data like the connection id and the statement id also needs to be dealt with too.
Maybe something like toml-test (https://github.com/BurntSushi/toml-test) would be useful, but it lacks a java encoder/decoder.
Anyway, those are just some of my thoughts and I would love to hear what you guys think would be the best way to implement the test framework. Hopefully, we can get the issue of inserting binary data resolved soon :)
Cheers, Francis On 19/04/2016 3:24 AM, Julian Hyde wrote:
+1 on some kind of test framework I can tell that Josh has put in a lot of effort trying to reproduce this problem based on descriptions in emails. For the sake of the sanity of our committers, these kind of problems should be accompanied by a pull request that contains a script that reproduces the issue. What language that script is written in is up for debate. JulianOn Apr 18, 2016, at 7:20 AM, Josh Elser <[email protected]> wrote: Ok, I will try to circle around to this. Meanwhile, let's start thinking about ways that you and I can better collaborate :) We want to make building drivers for Avatica in other languages as painless as possible, but it seems like we have a big impedance mismatch already -- not having a common language/test framework we can both use. What kind of tooling would help you in testing your driver and give us a common-base to analyze failures that you run into? * Dumping of protobufs to stdout by the Avatica server(PQS)? * Known protobuf requests/responses for executing some SQL? * ... something else entirely? F21 wrote:I have now created a repo on github containing all the binary protobuf requests and responses to insert binary data into a VARBINARY column. The repo is available here: https://github.com/F21/avatica-binary-protobufs The file 8-request is of most interest because that's the one where we are sending the ExecuteRequest to do the actual insertion of the image. I am using the protoc tool to decode the messages, e.g.: protoc --decode_raw < 8-request Let me know if this helps :) On 18/04/2016 3:31 AM, Josh Elser wrote:Sorry, yes. I just didn't want to have to post the JSON elsewhere -- it would've been gross to include in an email. For JSON, it is base64 encoded. This is not necessary for protobuf (which can natively handle raw bytes). For #2, did you verify that the data made it into the database correctly? HBase/Phoenix still, right? Also, maybe this is a SquirrelSQL issue? Can you verify the record is present via Phoenix's sqlline-thin.py? For #3, very interesting. I'm not sure why base64 encoding it by hand makes any difference for you. Avatica isn't going to be making any differentiation in the types of bytes that you send. Bytes are bytes are bytes as far as we care. F21 wrote:Just reporting back my experiments: 1. Using JSON: I set the serialization of the query server to JSON and replayed your requests using CURL. For the binary file, I first base64 encode it into a string and then sent it. This worked properly and I can see data inserted into the table using SquirrelSQL. 2. Using Protobufs: I inserted the binary data using Rep_BYTE_STRING and set BytesValues to the byte array read in from the file. It inserts (upserts) correctly, but if I query the table using SquirrelSQL, the binary column's cell is shown as <null>. 3. Using Protobufs with Base64 encoding: I first encode the binary data as base64. I then upsert the parameter as Rep_STRING and set StringValue to the base64 encoded string. This upserts correctly and I can see the data in SquirrelSQL. I then SELECT the data and base64 decode it and write it to a file and generate a hash. The file is written correctly and the hash also matches. So, approach 3 works for me, but it doesn't seem to be the correct way to do it. On 17/04/2016 8:17 AM, Josh Elser wrote:I wrote a simple test case for this with the gopher image. Here's the JSON data (but hopefully this is enough to help out). I'd have to write more code to dump out the actual protobufs. Let me know if this is insufficient to help you figure out what's wrong. My test worked fine. CREATE TABLE binaryData(id int, data varbinary(262144)); [{"request":"prepare","connectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","sql":"INSERT INTO binaryData values(?,?)","maxRowCount":-1}, {"response":"prepare","statement":{"connectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","id":1,"signature":{"columns":[],"sql":"INSERT INTO binaryData values(?,?)","parameters":[{"signed":true,"precision":10,"scale":0,"parameterType":4,"typeName":"INTEGER","className":"java.lang.Integer","name":"?1"},{"signed":false,"precision":262144,"scale":0,"parameterType":-3,"typeName":"VARBINARY","className":"[B","name":"?2"}],"cursorFactory":{"style":"LIST","clazz":null,"fieldNames":null},"statementType":"SELECT"}},"rpcMetadata":null}] [{"request":"execute","statementHandle":{"connectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","id":1,"signature":null},"parameterValues":[{"type":"INTEGER","value":1},{"type":"BYTE_STRING","value":"<REDACTED>"}],"maxRowCount":100}, {"response":"executeResults","missingStatement":false,"rpcMetadata":null,"results":[{"response":"resultSet","connectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","statementId":1,"ownStatement":true,"signature":null,"firstFrame":null,"updateCount":1,"rpcMetadata":null}]}] [{"request":"closeStatement","connectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","statementId":1}, {"response":"closeStatement","rpcMetadata":null}] [{"request":"prepareAndExecute","connectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","statementId":0,"sql":"SELECT * FROM binaryData","maxRowCount":-1}, {"response":"executeResults","missingStatement":false,"rpcMetadata":null,"results":[{"response":"resultSet","oonnectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","statementId":0,"ownStatement":true,"signature":{"columns":[{"ordinal":0,"autoIncrement":false,"caseSensitive":false,"searchable":true,"currency":false,"nullable":1,"signed":true,"displaySize":11,"label":"ID","columnName":"ID","schemaName":"SCOTT","precision":32,"scale":0,"tableName":"BINARYDATA","catalogName":"PUBLIC","type":{"type":"scalar","id":4,"name":"INTEGER","rep":"PRIMITIVE_INT"},"readOnly":false,"writable":false,"definitelyWritable":false,"columnClassName":"java.lang.Integer"},{"ordinal":1,"autoIncrement":false,"caseSensitive":false,"searchable":true,"currency":false,"nullable":1,"signed":false,"displaySize":262144,"label":"DATA","columnName":"DATA","schemaName":"SCOTT","precision":262144,"scale":0,"tableName":"BINARYDATA","catalogName":"PUBLIC","type":{"type":"scalar","id":-3,"name":"VARBINARY","rep":"BYTE_STRING"},"readOnly":false,"writable":false,"definitelyWritable":false,"columnClassName":"[B"}],"sql":null,"parameters":[],"cursorFactory":{"style":"LIST","clazz":null,"fieldNames":null},"statementType":"SELECT"},"firstFrame":{"offset":0,"done":true,"rows":[[1,"<REDACTED>"]]},"updateCount":-1,"rpcMetadata":null}]}] Josh Elser wrote:Super helpful! Thanks. I'll try to take a look at this tonight. If not then, over the weekend, likely. Will report back. F21 wrote:Hey Josh, Here's what I am doing: Create the table: CREATE TABLE test ( int INTEGER PRIMARY KEY, bin VARBINARY) TRANSACTIONAL=false Download the binary file we want to insert from here: https://raw.githubusercontent.com/golang-samples/gopher-vector/master/gopher.png Prepare the statement: UPSERT INTO test VALUES (?, ?) Here's the code I am using to execute the statement: // read the file into a byte array []byte file, _ := ioutil.ReadFile(filePath) // create array of typed values containing the parameters parameters := []*message.TypedValue{ &message.TypedValue{ Type: message.Rep_LONG, NumberValue: 1, }, &message.TypedValue{ Type: message.Rep_BYTE_STRING, BytesValues: file, }, } // create message: msg := &message.ExecuteRequest{ StatementHandle: &s.handle, // this is just the statement handle ParameterValues: parameters, MaxRowCount: 100, HasParameterValues: true, } // encode the message: wrapped, _ := proto.Marshal(message) // wrap it in the wire message: wire := &message.WireMessage{ Name: " org.apache.calcite.avatica.proto.Requests$ ExecuteRequest", WrappedMessage: wrapped, } // encode wire message and send over http: body, _ := proto.Marshal(wire) response, _ := ctxhttp.Post(context.Background(), httpClient, avaticaServer, "application/x-google-protobuf", bytes.NewReader(body)) Let me know if there's more information I can provide or if something's unclear :) On 15/04/2016 2:46 AM, Josh Elser wrote:Yeah, that sounds right to me too. I think we have a test for random bytes. Maybe there's something weird happening under the hood in the Avatica JDBC driver that isn't obvious to you in the Go driver. Any chance you can share some example code you're running? I can try to convert it to a Java test case, maybe help track down your issue. F21 wrote:I also tried casting the data to a string and setting it to StringValue and the Rep type to STRING. This works when I store and retrieve strings from the binary column, but doesn't work correctly if I try to store something like a small image. On 14/04/2016 5:03 PM, Julian Hyde wrote:BytesValue sounds right. I’m not sure why it isn’t working for you.On Apr 14, 2016, at 6:34 AM, F21 <[email protected]> wrote: As mentioned on the list, I am currently working on a golang client/driver for avatica using protobufs for serialization. I've got all datatypes working, except for BINARY and VARBINARY. For my test table looks like this: CREATE TABLE test (int INTEGER PRIMARY KEY, bin BINARY(20), varbin VARBINARY) TRANSACTIONAL=false In go, we have a datatype called a slice of bytes ([]byte) which is essentially an array of bytes (8-bits each). When I generated the golang protobufs using the .proto files, this is the definition of TypedValue: type TypedValue struct { Type Rep `protobuf:"varint,1,opt,name=type,enum=Rep" json:"type,omitempty"` BoolValue bool `protobuf:"varint,2,opt,name=bool_value,json=boolValue" json:"bool_value,omitempty"` StringValue string `protobuf:"bytes,3,opt,name=string_value,json=stringValue" json:"string_value,omitempty"` NumberValue int64 `protobuf:"zigzag64,4,opt,name=number_value,json=numberValue" json:"number_value,omitempty"` BytesValues []byte `protobuf:"bytes,5,opt,name=bytes_values,json=bytesValues,proto3" json:"bytes_values,omitempty"` DoubleValue float64 `protobuf:"fixed64,6,opt,name=double_value,json=doubleValue" json:"double_value,omitempty"` Null bool `protobuf:"varint,7,opt,name=null" json:"null,omitempty"` } I am currently creating a TypedValue that looks like this when sending binary data: {BYTE_STRING false 0 [116 101 115 116] 0 false} So, the Rep is set to BYTE_STRING and ByteValues is populated with the string "test" in bytes (it's shown here as decimal because I printed it). The problem is that even though the insert executes properly, if I look at the row using SquirrelSQL, both the BINARY and VARBINARY columns are <null>. Is BYTE_STRING the correct rep type for sending binary data? Do I also have to encode my bytes in a special format? Thanks!
