two method compared 1. use test; create table t (a int); 2. create table test.t1(a int); my result is the first one can log the binlog but the second one can't not use test;create table t(a int); mysqlbinlog -v mysql-bin.000005|grep create use test/*!*/; SET TIMESTAMP=1490090893/*!*/; create table t(a int) /*!*/; DELIMITER ; but when I do not use "use", DML cannot log in binlog,can tell me why? create table test.t(a int); mysqlbinlog -v mysql-bin.000005|grep create test.t

I need to delete rows from a table based on what is present in a temporary table. For me, both of these statements work : DELETE from main_table where id in (select deletable_id from temporary_table); and DELETE main_table from main_table join temporary_table on main_table.id = temporary_table.deletable_id; Which of the two is advisable to use given the fact that main_table will be having a billion of rows and the temporary will have a few thousands.

I am upgrading mysql 5.1 to mysql 5.5 in centos 7 server. As time limit,I only mysqldump and source several small database to mysql 5.5,and then mysql_upgrade --verbose -u root -p,everything is ok. Several days later, I import a big database db3 to mysql 5.5. [root@localhost ~]# mysql -u root -p < db3.sql Enter password: [root@localhost ~]# mysql_upgrade --verbose -u root -p Enter password: Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck This installation of MySQL is already upgraded to 5.5.60-MariaDB, use --force if you still need to run mysql_upgrade [root@localhost ~]# mysql_upgrade --verbose --force -u root -p Enter password: Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Phase 1/4: Fixing views Pls notice This installation of MySQL is already upgraded to 5.5.60-MariaDB, use --force if you still need to run mysql_upgrade. Do I need to run mysql_upgrade --verbose -u root -p again?

I have a cloud-based MySQL instance (slave) and am planning a solution where a remote instance (master) will be based on a ship. The ship will go in and out of international waters and therefore will have an intermittent terrestrial connection. When there is no connection, data will be stored locally. When the vessel arrives in an area with a terrestrial connection, I want the vessel database to synchronize with the cloud-based instance. This scenario will be duplicated with multiple vessels, with their own instances, and each vessel will need to send local changes to the cloud-based instance. We have looked at 3'rd party open-source tools like symmetric DS and wanted to 'ask-the-experts' the preferred approach. Is there a best-in-breed 3'rd party tool, or is the out-of-the-box MySQL solution sufficient? Thank you in advance.

**MySQL 5.5.32, MyISAM** A LOAD DATA INFILE statement usually executes and returns normally, succeeding at loading the data. On rare occasion it fails, returning a generic syntax error, always indicating the same character as the problem spot. ERROR 1064 (42000) at line 36: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ull, @field_name)' **I have no reason to believe there is a syntax error** because the large majority of the time it succeeds, and it always succeeds on MySQL 5.5.19 and other versions. So all of these details might be red herrings. But, for what it's worth, that line in the statement is field_name = if(@field_name='None', null, @field_name); The question is whether this is a known bug, or a theoretically explainable behavior, and **not** what the syntax error is. Are there conditions in a database, or perhaps a file or filesystem, that could generate the same generic syntax error inconsistently but persistently like this?

Am executing one query which is the same as below. Now here the problem is as limit increasing its increasing execution of time. I am adding limit using a PHP script. So anyone likes to suggest to stop increasing the time of execution by mysql? There is total records are 12042993 which I have to copy from one table to another table. But when am executing the single query without limit that time it's not working. So will you please suggest a better way to avoid this increasing time? For now, I have added one index which is below. I know its wrong. But for me, I just have to concentrate only and only on this query. There is no any other query is running on the entire server/database. alter table cdrs add index cdrs_data (callstart,uniqueid,accountid, type,callerid,sip_from_uri,callednum,billseconds,trunk_id,disposition, debit,cost,provider_id,pricelist_id, package_id,pattern,notes,reseller_id, provider_call_cost, call_direction,calltype,progress_media_stamp, progress_mediamsec, is_recording,call_reward_amount); insert into cdrs_data(uniqueid,accountid,type,callerid,sip_from_uri,callednum, billseconds,trunk_id,disposition,callstart,debit,cost,provider_id,pricelist_id, package_id,pattern,notes,reseller_id,provider_call_cost,call_direction, calltype,progress_media_stamp,progress_mediamsec,is_recording,call_reward_amount) select uniqueid,accountid,type,callerid, sip_from_uri, callednum,billseconds, trunk_id,disposition, callstart,debit,cost, provider_id,pricelist_id,package_id, pattern,notes,reseller_id,provider_call_cost, call_direction,calltype, progress_media_stamp,progress_mediamsec, is_recording,call_reward_amount from callinfo where callstart >= '2017-01-30 00:00:00' and callstart < '2019-05-06 00:00:00' order by callstart asc limit 24000,3000 All suggestions are appriciated.

Any assitance would be greatly welcomed. Pardon my ignorance however I am wondering if anyone else has had to encounter bad EAV performance issues. I have a particular query which takes a long time. What is the best way to increase the speed on this?

SELECT * FROM
(
    SELECT f.*, p.timestamp
    FROM tObjectValues ov
    INNER JOIN tObjectProperties op ON ov.propertyId = op.id
    INNER JOIN tObjects o ON op.objectId = o.id
    INNER JOIN files f ON SUBSTRING(ov.value, CHAR_LENGTH('cache://content//')) = f.filename
    LEFT JOIN publishFiles pf ON f.id = pf.fileId
    LEFT JOIN publishes p ON p.id = pf.publishId AND o.appId = p.appId AND p.type = 'l'
    WHERE o.status = 1 AND op.status = 1 AND ov.status = 1 AND ov.value LIKE 'cache://content/%' AND o.appId = 2
    ORDER BY p.timestamp DESC
    LIMIT 0, 10000000
) s
GROUP BY s.id

I have run the explain function to delve further into the issue however i was wondering if anyone who has had similar problem could help in the optimisation.

I have noticed 'commit' statement missing for a DML statement in binary log. Does it mean that this statement is rolled back in server? I am using mysql 5.5.40. sample binlog ------------- #### at 159527792 #####160229 23:25:22 server id 104 end_log_pos 159528127 Query thread_id=45195 exec_time=0 error_code=0(in comments) SET TIMESTAMP=1456809922/*!*/; update table_name set col1=4 where col2=4 /*!*/; ##### at 159528275 #####160229 23:25:22 server id 104 end_log_pos 159528484 Query thread_id=45195 exec_time=0 error_code=0(in comments) SET TIMESTAMP=1456809922/*!*/; insert into table_name values (1) /*!*/; ##### at 159528632 #####160229 23:25:22 server id 104 end_log_pos 159528511 Xid = 22993533(in comments) COMMIT/*!*/; As per mysql documentation **Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.** http://dev.mysql.com/doc/refman/5.5/en/binary-log.html please let me know whether my undersatnding is correct or not. if any statement getting rolled back, server write rollback to binlog?

Is it possible to capture the messages that can be listed with SHOW ERRORS and store them in a log table? Like, is there a table somewhere, that I can select from?

I have a table named 'mrtg' that has four columns: mysql> select * from mrtg; +----------+------+------+------+ | city | day1 | day2 | day3 | +----------+------+------+------+ | Delhi | 2 | 6 | 9 | | Mumbai | 1 | 3 | 8 | | Banglore | 4 | 1 | 6 | +----------+------+------+------+ 3 rows in set (0.00 sec) If current date = '2019-09-01' then I want to select as SELECT CITY, DAY1 FROM MRTG; If current date = '2019-09-02' then I want to select as SELECT CITY, DAY1, DAY2 FROM MRTG; If current date = '2019-09-02' then I want to select as SELECT CITY, DAY1, DAY2, DAY3 FROM MRTG; and so on. How can I write a query for this? The values represent day-wise data for a whole month. I do wonder about this type of table structure, but it's the client requirement. I need to find a solution to fetch only the columns from day 1 to the current date.

How to take MySQL Master-Master and Master-Slave replication backup?. I have setup Master-Master replication with a separate slave for each master on Ubuntu. What happens if, 1. I issue or schedule mysqldump on master servers. 2. I allow developers to make changes directly connecting to master servers with the GUI tools(workbench). Any help is appreciated. Thank you!

I am trying to create a stored procedure in MySQL 5.5.8. using connector 8.0.13. Once this procedure is debugged, it will have to be embedded into a c#.net application, to be created on the fly every time the program is set up. For now, I am doing it manually. For someone like me, with MS SQL, Oracle, and DB2 background, this is challenging. I honestly read MySQL dev docs and googled to get a hang of MySQL SQL syntax. What I gather is that: * I have to change delimiter to something like // * I should use if not exists just in case * Parameters do not start with @, and they have to be parenthesized * I end the block with the temporary delimiter // and restore it to ; afterwards This is what I arrived at: delimiter // create procedure if not exists logging.logEntry( in hostName varchar(512) ,in entryDateTime datetime ,in entryText varchar(1024) ,out return_value int ) begin insert into logging.log (hostName, entryDateTime, entryText) select hostName, entryDateTime, entryText; set return_value = last_insert_id(); select return_value; end// delimiter ; When I run the above code in SquirrelSQL, the server throws the following error: > Error: You have an error in your SQL syntax; check the manual that > corresponds to your MySQL server version for the right syntax to use > near 'if not exists loging.logEntry`( in hostName varchar(512) ,in > entryDat' at line 1 SQLState: 42000 ErrorCode: 1064 Here is what I tried: * Ran without DELIMITER * Removed if not exists * Removed backquotes around entity names * Inserted values() instead of select even though the standalone insert works I know that this is something totally obvious for a seasoned MySQL developer, but it escapes someone like me with a different background. Am I using any functionality that does not exist in 5.5.8 or are there bugs in my code?

I'm interested in this for InnoDB mostly, but also for MyISAM. I looked around in information_schema and did not see this data anywhere. I'm using MySQL 5.5.16.

I have just recently learned about user variables (ie, @myvar) and am trying to create a complex sorting query that takes 2 tables where the second is missing a column which then gets filled in by a subquery. In this subquery I need to generate a number by doing a lookup on the first table and modifying the result. More specifically, I have a table A (id, date), and table B (id, weekday) and I need to return a date for each row of B where it is the *next date occurring on the same weekday where such a date does not occur in A*, all starting from a specific minimum date, where entries in table A are all higher (ie, later) than that date. Table B is sorted by staggered weekday (First Monday, First Tuesday... Second Monday, Second Thursday..) and then these get "slotted in" (by assigning "date") where that date in A does not occur. So if the two tables look something like: A id | date a-a 2014-11-11 a-b 2014-11-13 a-c 2014-11-18 B id | weekday b-a Tuesday b-b Tuesday b-c Wednesday Then the resulting sort (if start date is 2014-11-10, a Monday) would be: sorted id | date a-a 2014-11-11 b-c 2014-11-12 (Wednesday) a-b 2014-11-13 a-c 2014-11-18 b-a 2014-11-25 (Tuesday) b-b 2014-12-02 (Tuesday) I have been working on this query for a while now, and almost had it done, but then I couldn't find a way to pass the "start date" to the subquery that allocates dates to table B. The docs say that > Subqueries in the FROM clause cannot be correlated subqueries. They are materialized in whole (evaluated to produce a result set) before evaluating the outer query, so they cannot be evaluated per row of the outer query. So I reimplemented the subquery to move the variable to a WHERE clause, using JOINs, but this still didn't seem to help, since I couldn't figure out how to return just the single row needed without an outer query to do that. So here is what I have, in the hopes someone can help me figure this out: SELECT @next_date as next_date, @tmp_i := CASE w.weekday + 0 WHEN 0 THEN @idxw_U:=@idxw_U+1 WHEN 1 THEN @idxw_M:=@idxw_M+1 WHEN 2 THEN @idxw_T:=@idxw_T+1 WHEN 3 THEN @idxw_W:=@idxw_W+1 WHEN 4 THEN @idxw_R:=@idxw_R+1 WHEN 5 THEN @idxw_F:=@idxw_F+1 WHEN 6 THEN @idxw_S:=@idxw_S+1 END as idxW, @idxw_offset := (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxw_offset, @idxw_corr := (@tmp_i *7)-7 + (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxW_corr, w.weekday as weekday, @dayofweek:= w.weekday+0 as dweekday, @TMP_date:= DATE_ADD(@next_date, INTERVAL (@idxw_corr) DAY) as tmp_date, @clash_offset:= IF((SELECT 1 FROM t_date WHERE date = @TMP_date), IFNULL( ( SELECT next_slot FROM ( SELECT -- DAYOFWEEK(d.date) as weekdaynum, -- dayname(d.date) as weekday, -- d.date, -- MIN(d2.date) as min_d2, DATE_ADD(MIN(d2.date), INTERVAL 1 WEEK) as next_slot, -- DATEDIFF(d2.date, d.date) as datediff, -- DATEDIFF(d2.date, d.date) DIV 7 as weeksdiff, -- DATEDIFF(MIN(d3.date), d2.date) DIV 7 as nextdiff FROM t_date as d JOIN t_date as d2 JOIN t_date as d3 WHERE d.date >= @TMP_date and DAYOFWEEK(@TMP_date) = DAYOFWEEK(d.date) = DAYOFWEEK(d2.date) = DAYOFWEEK(d3.date) and d2.date > d.date and d3.date > d2.date and DATEDIFF(d2.date, d.date) = 7 GROUP BY d.date ORDER BY d.date ASC ) as t -- these 3 lines below are the ones I need to figure out how to accomplish without an outer query -- ie, find the first (lowest date) result that has a nextdiff greater than 1 (a gap of more than 1 week between results) WHERE nextdiff >1 ORDER BY date ASC LIMIT 1 ) , CONCAT('dow: ',@dayofweek,' ',@TMP_date) ) -- , 1) -- commented this out while debugging, debug output is line above; in final, if return is null, that means offset is one week , 0 ) as clash_offset, @NEW_date:= (SELECT DATE_ADD((SELECT @TMP_date), INTERVAL @clash_offset WEEK)) as date, w.extra FROM t_weekday as w JOIN (SELECT @idxw := 0, @idxw_U := 0, @idxw_M := 0, @idxw_T := 0, @idxw_W := 0, @idxw_R := 0, @idxw_F := 0, @idxw_S := 0, @next_date := DATE_ADD((SELECT t.date FROM t as t ORDER BY t.date DESC LIMIT 1), INTERVAL 1 DAY) ) as varrs ORDER BY date DESC, weekday, id ASC @TMP_date is the starting (minimum) date. The subquery is the one inside the IFNULL, and tested outside of this, as its own query, it works perfectly, when specifying a literal value (or setting the variable in a JOIN()). As a test, I did: SELECT @next_date as next_date, @tmp_i := CASE w.weekday + 0 WHEN 0 THEN @idxw_U:=@idxw_U+1 WHEN 1 THEN @idxw_M:=@idxw_M+1 WHEN 2 THEN @idxw_T:=@idxw_T+1 WHEN 3 THEN @idxw_W:=@idxw_W+1 WHEN 4 THEN @idxw_R:=@idxw_R+1 WHEN 5 THEN @idxw_F:=@idxw_F+1 WHEN 6 THEN @idxw_S:=@idxw_S+1 END as idxW, @idxw_offset := (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxw_offset, @idxw_corr := (@tmp_i *7)-7 + (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxW_corr, w.weekday as weekday, @dayofweek:= w.weekday+0 as dweekday, @TMP_date:= DATE_ADD(@next_date, INTERVAL (@idxw_corr) DAY) as tmp_date, @clash_offset:= IF((SELECT 1 FROM t_date WHERE date = @TMP_date), IFNULL(:= IF((SELECT 1 FROM t_date WHERE show_after = @TMP_show_after), IFNULL( ( -- SELECT f FROM ( -- uncommenting this line and the ftmp one results in NULL, with them commented out, @TMP_date is picked up no problem SELECT @TMP_date as f LIMIT 1 -- ) as ftmp ) , CONCAT('dow: ',@dayofweek,' ',@TMP_show_after) ) -- , 1) , 0 ) as clash_offset, @NEW_date:= (SELECT DATE_ADD((SELECT @TMP_date), INTERVAL @clash_offset WEEK)) as date, w.extra FROM t_weekday as w JOIN (SELECT @idxw := 0, @idxw_U := 0, @idxw_M := 0, @idxw_T := 0, @idxw_W := 0, @idxw_R := 0, @idxw_F := 0, @idxw_S := 0, @next_date := DATE_ADD((SELECT t.date FROM t as t ORDER BY t.date DESC LIMIT 1), INTERVAL 1 DAY) ) as varrs ORDER BY date DESC, weekday, id ASC Which confirms what the docs say. However, I don't know how to modify my query (the first one) to get the result I want. ---------- Edit: I found an error in the logic, I need to return the highest result (date) in a series of *nextdiff*s if all return 1, or the first higher-than-1 result. It currently only would return the latter. So that needs fixing too.

I have an application which produces log files. These log files include a Timestamp field in the format 2015-08-25T09:35:01 UTC. (there will be approximately 60 logs produced at 0.25 GB per day). I need to import these log files into MySQL for analysis. But I have a problem converting the Timestamp to DateTime. example: CREATE TABLE test1 ( TIMESTAMP varchar(25) DEFAULT NULL, EVENT_TIME datetime DEFAULT NULL ); INSERT INTO test1 (TIMESTAMP) VALUES ('2015-08-25T09:35:01 UTC'), ('2015-08-25T09:36:01 UTC'), ('2015-08-25T09:37:01 UTC'), ('2015-08-25T09:38:01 UTC'), ('2015-08-25T09:39:01 UTC'); So far so good. I can now run a SELECT query to get the datetime SELECT CAST(TIMESTAMP AS datetime) FROM test1; But, if I try to update the table with the datetime format I get an error UPDATE test1 SET EVENT_TIME = CAST(TIMESTAMP AS datetime); Error Code: 1292. Truncated incorrect datetime value: '2015-08-25T09:35:01 UTC' Is there a way to do this? as I really need the datetime field in the database, so I don't have to do the CAST every time I run a query. I was also looking to partition the table by date, as there will be a lot of data produced, and so I only want to keep the minimum amount of data, and then drop the oldest partitions once I am done.

I am aware that when a connection is made to MySQL, MySQL does a reverse lookup on the IP address to determine the host it is coming from. I have a very odd situation where I have a number of users connecting from the same server and some users are showing as coming from one host (lets say for example "myhost.com") and others are from a different host (lets say "myhost.co.uk") The config of the connection in both cases is the same. The IP they connect from is the same. The server they connect to is the same. On both client and server PC the reverse lookup of the IP resolves to the same host (e.g. myhost.com). There is only one IP address on the server. This is causing us problems with authentication because when adding new users (something which is an automated script) we add them using the server IP address as the host, and about 50% of the time, this is not working because when it does the reverse lookup it finds the wrong host name! I am aware that you can turn off the host lookup in MySQL config, but at this stage I'm reluctant to do that and would rather find an answer to the problem.

I am not in the best situation. I inherited an Ubuntu 14.04 8 GB RAM, 8 CPU MySQL 5.5 database server with almost 400 GB of business-critical data (stored on external SSD) contained within several thousand different databases. My database administration skills and experience are nascent. I want to create a backup of this data to set up MySQL Replication, but I need to create the backup with minimized impact and downtime. These databases are individually backed up with mysqldump about every four hours. This unfortunately means that I have no single, point-in-time, logical or raw backup of the entire database server and to top it off, binary logging is not enabled on that server. But I do have the capability to individually restore these backups. In total, there about 250,000 tables in the database server. Of those tables, about 90,000 use the myisam engine and about 160,000 use the innodb engine. I know there will be some downtime but I would really just like to avoid having downtime of an unknown duration during which I am obliged fully backup the data and deploy replication at the same time. In testing, I've given thought to or tried various approaches: - using Percona Xtrabackup - using mysqldump with a single transaction (for innodb) and no locks for the myisam tables - rsync'ing the mysql data directory, then gracefully shutting down the MySQL server, and rsync'ing the flushed out changes - converting the myisam tables to innodb, then doing a mysqldump or using xtrabackup - using my existing backups to start replication, then letting the slave catch up - restoring my existing backups, then syncing the changes with pt-table-checksum and pt-table sync - and the list can go on... Without me providing excessive detail about my testing methods and results, I would like to know how you would approach this situation. EDIT: In essence, my question is: With the goal of minimal downtime and given my scenario, how would you create a backup of the database server in anticipation of setting up MySQL Replication? I would appreciate any advice, opinions, services, or resources you may have. Thank you.

I have multiple queries using index_merge but for put an example, I show you the following query

EXPLAIN select count(*) From Clientes  Where id_campana='1849' and id_empresa='55' and (id_estado_cliente='1' or id_estado_cliente='5' or id_estado_cliente='9'  or id_estado_cliente='10' or id_estado_cliente='13' or id_estado_cliente='7');

Running Explain Statement, this was the result

+----+-------------+----------+-------------+-------------------------------------------------------------------------------------+-----------------------+---------+------+-------+-----------------------------------------------------+
| id | select_type | table    | type        | possible_keys                                                                       | key                   | key_len | ref  | rows  | Extra                                               |
+----+-------------+----------+-------------+-------------------------------------------------------------------------------------+-----------------------+---------+------+-------+-----------------------------------------------------+
|  1 | SIMPLE      | Clientes | index_merge | id_empresa,id_campana,id_estado_cliente,id_empresa_campana,idx_mejora_perf_clientes | id_empresa,id_campana | 2,4     | NULL | 35564 | Using intersect(id_empresa,id_campana); Using where |
+----+-------------+----------+-------------+-------------------------------------------------------------------------------------+-----------------------+---------+------+-------+-----------------------------------------------------+

The only thing I have done to optimize query is create a compound query and use index_hint this are the indexes which exist on this table

+----------+------------+--------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name                 | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+--------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Clientes |          0 | PRIMARY                  |            1 | id                  | A         |    59405824 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          0 | id_id_empresa            |            1 | id                  | A         |    59405824 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          0 | id_id_empresa            |            2 | id_empresa          | A         |    59405824 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          1 | id                       |            1 | id                  | A         |    59405824 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          1 | id_empresa               |            1 | id_empresa          | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          1 | id_campana               |            1 | id_campana          | A         |       61752 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          1 | id_estado_cliente        |            1 | id_estado_cliente   | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          1 | id_empresa_campana       |            1 | id_empresa          | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          1 | id_empresa_campana       |            2 | id_campana          | A         |       27982 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          1 | idx_mejora_perf_clientes |            1 | id_empresa          | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          1 | idx_mejora_perf_clientes |            2 | id_campana          | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          1 | idx_mejora_perf_clientes |            3 | id_estado_cliente   | A         |       18363 |     NULL | NULL   |      | BTREE      |         |               |
| Clientes |          1 | idx_mejora_perf_clientes |            4 | id_resultado_agente | A         |      110214 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+--------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

but the problem if the developers will modify this statement to solve this condition but my doubt is exist something method to make it transparent.

I would like to query the integer part and fractional part of all decimal(8,4) values in a column. By that I mean, I want to know the actual given values, and not the max allowed by the data type, which are integer = 4, fractional = 4. For example, 33.99 would return (2,2) and 1.375 would return (1,3). I have started trying to parse the numbers as strings using char_length() like this: SELECT max(char_length(SUBSTRING_INDEX(col,'.',1))), max(char_length(SUBSTRING_INDEX(col,'.',-1))); A similar, more intuitive, and uglier way in Oracle is SELECT max(length(regexp_substr(col,'^.*[.]',1))), max(length(regexp_substr(col,'[.].*$',1))) But is there a better way that exploits MySQL's knowledge that these are **actually numbers**? This page implies that the true lengths of numbers - e.g. without leading 0s - is known and retrievable, so I was hoping there was a function that would just do this. Does this have something to do with the assertion that "MySQL returns all data as strings and expects you to convert it yourself" or is that specific to that Python module?

Currently, we have MySQL master-slave replication. Both master and slave servers have Mysql 5.5 Currently I need to add one more Master server having version 5.7. **Is it possible to add Multiple masters to a single slave server?** >One master and slave is having Mysql 5.5 and new Mysql server is 5.7 **Is it possible to configure Master-slave replication with different MySQL version?**