Skip to main content

Why GROUP_CONCAT returns BLOB?

Last week, when using the GROUP_CONCAT() function on a MySQL database, I got an unexpected result. :(

Indeed, instead of getting my result as VARCHAR types, I got it as BLOB types! For information, a BLOB is a binary large object that can hold a variable amount of data:
http://dev.mysql.com/doc/refman/5.0/en/blob.html
Because BLOB values are treated as binary strings, it is not easy to use. This is why we would prefer to have VARCHAR values.

So the question is how to get around this frustrating problem?

The answer is, for once, very simple! :D
You simply need to:

Open your my.ini or my.cnf file;
Change the value of the group_concat_max_len system variable to 512 (no ‘k’ suffix);
Restart the mysql service

To verify if the value has been successfully updated, execute the following command in your mysql client:
mysql> show variables like "%concat%";
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| group_concat_max_len | 512 |
+----------------------+-------+
1 row in set (0.00 sec)

Note that you cannot set the value of group_concat_max_len to less than 1Kb using the MySQL Administrator GUI. Which means that the only way to set this system variable to 512 (which is less than 1Kb) is to edit your MySQL configuration file as described above.

Comments

Popular posts from this blog

Odoo/OpenERP: one2one relational field example

one2one relational field is deprecated in OpenERP version>5 but you can achieve the same using many2one relational field. You can achieve it in following two ways : 1) using many2one field in both the objects ( http://tutorialopenerp.wordpress.com/2014/04/23/one2one/ ) 2)  using inheritance by deligation You can easily find the first solution with little search over internet so let's start with 2nd solution. Scenario :  I want to create a one2one relation between two objects of openerp hr.employee and hr.employee.medical.details What I should do  i. Add _inherits section in hr_employee class ii. Add field medical_detail_id in hr_employee class class hr_employee(osv.osv):     _name = 'hr.employee'     _inherits = {' hr.employee.medical.details ': "medical_detail_id"}     _inherit = 'hr.employee'         _columns = {              'emp_code':fields.char('Employee Code', si

Odoo: Download Binary File in v10

To download any binary file in Odoo10 following is the link: http://127.0.0.1:8069/web/content?model=<module_name>&field=<field_name>&filename_field=<field_filename>&id=<object_id> module_name    - the name of the model with the Binary field field_name         - the name of the Binary field object_id            - id of the record containing particular file. field_filename    - name of a Char field containing file's name (optional). So if you want to call a function on button click and download the file, code is as follow: file_url = "http://127.0.0.1:8069/web/content?model=<module_name>&field=<field_name>&filename_field=<field_filename>&id=<object_id>" return {     'type': 'ir.actions.act_url',     'url': file_url,     'target': 'new' } In Reports or web page, you can use it as: <t t-foreach="files&qu

Odoo/ OpenERP: Customize one2many relational field

I was working on one2many relational field to integration one functionality. Worked on python code and then wrote an xml file for the same. Restarted my server. It showed me the row with headers and 'Add an item' button. When clicked on that it opened a pop-up to provide inputs. It shows all the fields with in sequence of it's own. Q1. I need input fields in other sequence or don't need all fields. A.   In xml you would have wrote code like this : <field name="dirtyhandsphp" widget="one2many_list" colspan="4" nolabel="1"> <tree string="Details">              <field name="field1" />              <field name="field2" />                      ---------------------------------------------              <field name="designation" />         </tree>                                                     </field> Add form section inside and