Symbols special character

General Discussions - English
1

Hi,

I have make a database in phpmyadmin. I have prices with dollar symbols and I also other symbols.
When I open mysql through Manticore I see the number 0 instead of the prices. This is also the case with the other symbols. I insert ‘collation_server = utf8_ci’ in my searchd and ‘sql_query_pre = SET CHARACTER_SET_RESULTS=utf8’ and ‘sql_query_pre = SET NAMES utf8’ in my index source.
In mysql when isued ‘SHOW COLLATION;’ I get the following:

mysql> SHOW COLLATION;
+-----------------+---------+------+---------+----------+---------+
| Collation       | Charset | Id   | Default | Compiled | Sortlen |
+-----------------+---------+------+---------+----------+---------+
| utf8_general_ci | utf8    |   33 | Yes     | Yes      | 1       |
+-----------------+---------+------+---------+----------+---------+
1 row in set (0,00 sec)

But it still doesn’t work.
I have a plan index.

Can someone help me with this?

2

Please provide a reproducible example:

  • mysqldump of your table, or, even better, a config like this:
source min {
    type = mysql
    sql_host = localhost
    sql_user = test
    sql_pass =
    sql_db = test
    sql_query = select 1, 'dog' f, 1 a
    sql_attr_uint = a
}

index idx {
    path = idx
    source = min
}
  • your configuration file
  • more details about “I open mysql through Manticore I see the number 0 instead of the prices”:
    • client info
    • query
3
  • My .conf:

searchd {
listen = 127.0.0.1:9312
listen = 127.0.0.1:9306:mysql
listen = 127.0.0.1:9308:http
log = /var/log/manticore/searchd.log
collation_server = utf8_ci
query_log = /var/log/manticore/query.log
pid_file = /var/run/manticore/searchd.pid
query_log_format = sphinxql
}

source demo {
type = mysql
sql_host = localhost
sql_port = 9306
sql_user =
sql_pass =
sql_db = phpmyadmin
sql_query_pre = SET CHARACTER_SET_RESULTS=utf8
sql_query_pre = SET NAMES utf8
sql_query = SELECT demo2.ID_general, demo2.price, demo2.price_range, demo2.inventory, demo2.inventory_range FROM demo2 WHERE NOT name=‘zero’
sql_attr_uint = price
sql_attr_uint = price_range
sql_attr_uint = inventory
sql_attr_uint = inventory_range
}

index demo {
type = plain
path = /var/lib/mysql/phpmyadmin
source = demo
}

  • This is what I get from MySQL:
    mysql> SELECT * FROM demo2 WHERE NOT price=‘Zero’;
    ±------±-------------±----------±----------------+
    | price | price_range | inventory | inventory_range |
    ±------±-------------±----------±----------------+
    | $25 | $25 - $49.99 | 18.000 | 30.000 |
    | $35 | $25 - $49,99 | 10.000 | 10.000-14.999 |
    | $25 | $25 - S49.99 | 25.000 | 20.000 - 30.000 |
    | $36 | $25 - $49.99 | 5.000 | 1.000 - 9.999 |
    | $45 | $25 - $49.99 | 15.000 | 15.000 - 19.999 |
    | $900 | > $100 | 50.000 | > 30.000 |
    | $58 | $50 -$100 | 35.000 | > 30.000 |
    | $32 | $25 - $49.99 | 4.000 | 1.000 - 9.999 |
    | $52 | $50 - $100 | 45.000 | > 30.000 |
    | $35 | $25 - $49.99 | 1.500 | 1.000 - 9.999 |
    ±------±-------------±----------±----------------+
    10 rows in set (0,00 sec)

This is what I get from Manticore Search:
Mysql> SELECT * FROM demo;
±------±------------±----------±----------------+
| price | price_range | inventory | inventory_range |
±------±------------±----------±----------------+
| 0 | 0 | 18 | 30 |
| 0 | 0 | 10 | 10 |
| 0 | 0 | 25 | 20 |
| 0 | 0 | 5 | 1 |
| 0 | 0 | 15 | 15 |
| 0 | 0 | 50 | 0 |
| 0 | 0 | 35 | 0 |
| 0 | 0 | 4 | 1 |
| 0 | 0 | 45 | 0 |
| 0 | 0 | 1 | 1 |
±------±------------±----------±----------------+
10 rows in set (0,01 sec)

  • Don’t know what you mean with client info. I am using my own computer and I am running Manticore Search on Ubuntu 20.04.6 LTS.

I hope that I could give you enough information to help me out.

4

When I was searching for the information for you, I finger it out. I have sql_attr_uint instead of sql_field_string.
But thanks anyway.