Error converting Blob data from MySql DB to google cloud storage

Question

I am trying to do the data migration. We used to store image as mediumblob data in MySQL DB, I want to read this image(blob data) and upload to the google cloud storage bucket. The upload works but the image are corrupted in cloud storage.

code to upload file

const id: string = uuidv4();
const buffer: Buffer = image.ImageBin;   //image.ImageBin contains blob data from DB

let file = {
    originalname: imageId + ".JPG",
    buffer,
};

return await upload(file, id);

upload function

public async upload(file, imageId): Promise<string> {
    try {
      const bucket = this.storage.bucket(process.env.IMAGE_BUCKET);

      return new Promise((resolve, reject) => {
        let { originalname, buffer } = file;
        originalname = imageId + ":" + originalname;
        originalname = originalname.replace(/ /g, "_");
        const blob = bucket.file(originalname);

        const blobStream = blob.createWriteStream({
          resumable: false,
        });

        blobStream
          .on("finish", () => {
            const publicUrl = format(
              `https://storage.googleapis.com/${bucket.name}/${blob.name}`
            );
            resolve(originalname);
          })
          .on("error", (error) => {
            reject(error);
          })
          .end(buffer);
      });

    } catch (error) {
      this.logger.log(`error uploading file error:${error}`);
    }
  }

earlier Java code that used to get image from mysql and this code is working fine.

public Response getImage(Integer imageid, HttpServletResponse resp)
    {
        Response response = null;
        UserPhotoUpload UserPhotoUpload = new UserPhotoUpload();
        Blob imageBlob;
        OutputStream out = null;

        try
        {
            // get the image to upload
            UserPhotoUpload = userPhotoUploadDAO.getImage(imageid);
            if (UserPhotoUpload != null)
            {
                imageBlob = UserPhotoUpload.getImageBin();
                if (imageBlob != null)
                {
                    // get the output stream
                    out = resp.getOutputStream();

                    // add header info
                    resp.setHeader("Content-Disposition", "inline;filename=test.JPG");
                    resp.setContentType("Content-type: image/JPG");

                    // copy the image to the output stream
                    IOUtils.copy(imageBlob.getBinaryStream(), out);

                    out.flush();
                    out.close();
                }
                else
                    throw new ScottsAppException(CommonConstants.STATUS_MSG_ERROR_IMAGE_NOT_EXISTS);
            }
            else
                throw new ScottsAppException(CommonConstants.STATUS_MSG_ERROR_IMAGE_NOT_EXISTS);
        }
        catch (ScottsAppException e)
        {
            response = new Response();
            CommonUtil.responseErrorUpdate(response, e.getMessage(), CommonConstants.STATUS_CODE_APPLICATION_FAILURE);
        }
        catch (IOException e)
        {
            response = new Response();
            log.error("Error", e);
            CommonUtil.responseErrorUpdate(response, CommonConstants.STATUS_MSG_MISC_APPLICATION_FAILURE, CommonConstants.STATUS_CODE_APPLICATION_FAILURE);
        }
        catch (SQLException e)
        {
            response = new Response();
            log.error("Error", e);
            CommonUtil.responseErrorUpdate(response, CommonConstants.STATUS_MSG_MISC_APPLICATION_FAILURE, CommonConstants.STATUS_CODE_APPLICATION_FAILURE);
        }
        catch (Exception e)
        {
            response = new Response();
            log.error("Error", e);
            CommonUtil.responseErrorUpdate(response, CommonConstants.STATUS_MSG_MISC_APPLICATION_FAILURE, CommonConstants.STATUS_CODE_APPLICATION_FAILURE);
        }
        finally
        {
            try
            {
                out.flush();
            }
            catch (Exception e)
            {

            }

            try
            {
                out.close();
            }
            catch (Exception e)
            {

            }
        }

        // return
        return response;
    }

if someone wan't to look at blob data https://justpaste.it/862mk

I am a bit confused by your question, you mentioned you have an earlier version of a Java code that was able to get the image from the DB, so that means that the first block of code is where the issue is happening? Did the upload Java function worked before? — Rafael Lemos
@RafaelLemos yes the java code is working fine, it is able to send blob data that is in the table. The Nodejs code Is not working, sure why. — Deepak Poojari
ok but you have a very small snippet of node.js code, where is this value coming from const buffer: Buffer = image.ImageBin;? I guess my bigger question is what is the order of execution here? I mean, are running all 3 snippets in the getImage() -> node.js code -> upload() order or are you trying to replace the "old" java function by this new node.js function? If you have more relevant node.js code, please share it as well. — Rafael Lemos
image.ImageBin is the blob data coming from mysql database where column name is ImageBin. The old java code used to serve blob data directly to UI using API.for data migration I need to create an image and upload to google cloud storage using this blob data. — Deepak Poojari
I think the problem is that you are not converting the blob properly to a buffer in the node code, try replacing that line for this: const buffer = new Buffer( image.ImageBin, 'binary' ); Let me know if this works. — Rafael Lemos

Deepak Poojari Deepak Poojari · Accepted Answer · 2020-10-21T13:01:33

For someone coming here, The issue was in copy-paste of blob data from Database. The above functions are working fine.

Error converting Blob data from MySql DB to google cloud storage

1 Answers