Tag: mysql

Pubblicato il

LibreBase problems with native mysql connector

1. mysql connector not working

From LO 6.4 native mysql connector didn’t work any more. But a possible solution is to comment the line skip-networking in my.conf.

I mean now the line is #skip-networking, before it was skip-networking.

2. subforms not working

In new releases, to avoid this issue, you have to cut a small portion of code in content.xml, as in the following instructions:

have a look at the content.xml in the *.odb-file (could be opened by a zip-program)

Not working code:
<db:driver-settings db:system-driver-settings=”” db:base-dn=”” db:parameter-name-substitution=”false”/>

Working code
<db:driver-settings db:system-driver-settings=”” db:base-dn=””/> ยป

It works!

3. tables or queries not working

You should check the table/query structure and avoid fields (which should have a length) with no length.

Pubblicato il

mysql convert a column to utf-8

You can use this mysql query code:

ALTER TABLE yourtable MODIFY
    yourcol VARCHAR(255)
      CHARACTER SET utf8
      COLLATE utf8_general_ci;

There are more problems with ENUM and SET type fields.

For ENUM you have to specify all the field values, no matter how many they are

ALTER TABLE yourtable MODIFY
    yourcol ENUM('val1','val2',etc)
      CHARACTER SET utf8
      COLLATE utf8_general_ci;

For SET the problems rise from the name SET, so you have to do this command

ALTER TABLE yourtable MODIFY
    yourcol CHAR(255)
      CHARACTER SET utf8
      COLLATE utf8_general_ci;

You will be able afterwards to modify CHAR to SET (with your previous values) without losing your data.

Pubblicato il

convert an InnoDB database to MyIsam

The problem of Innodb is that you can’t copy the database tables from a PC to another or from localhost to an online website, because of the file “ibdata1”, which is out of the tables folder.

So I think that the best solution, if you need to sync your database between different locations, is to convert Innodb tables into MyIsam tables, which can be copied without problems.

Unfortunately I didn’t find a way to convert a whole database with a single mysql command, but I had to convert each table, with this code:

ALTER TABLE table_name ENGINE=MyISAM.

But at the end the result was any way good.

Pubblicato il

calculate age from year of birth

There are two ways:

via php

## 2. in php
You can also use php code, as in the following way
(inspired from this page (https://stackoverflow.com/questions/3776682/php-calculate-age), and _slighty modified_: 

    $dob =”$row[anno_nascita]”;
    $age = date_diff(date_create($dob), date_create(‘now’))->y;
    echo “age: $age years
“;

via mysql in phpmyadmin

1.do this sql query:
     select *,year(CURDATE())-year(anno_nascita) age from studenti__classi; 
2. save the result as view
3. and so you will have, in the view, the age (of your students, i.g.) that can work in LibreBase (to connect with that view).