[PATCH ulogd2] doc: mysql: declare MAC protocol columns unsigned

[PATCH ulogd2] doc: mysql: declare MAC protocol columns unsigned

[Jeremy Sowden]

By default, MySQL smallints are signed.  This causes problems inserting packets
for ethertypes above 0x7fff, such as IPv6 (0x86dd):

  MariaDB [ulogd]> SELECT INSERT_PACKET_FULL(...,'f4:7b:09:41:7a:71','f0:2f:74:4e:b2:f3',34525,0,NULL,NULL,NULL);
                                                                                         ^^^^^

which fails as follows:

  ERROR 1264 (22003): Out of range value for column 'mac_protocol' at row 1

Signed-off-by: Jeremy Sowden <jeremy@azazel.net>
---
 doc/mysql-ulogd2-flat.sql | 2 +-
 doc/mysql-ulogd2.sql      | 6 +++---
 2 files changed, 4 insertions(+), 4 deletions(-)

diff --git a/doc/mysql-ulogd2-flat.sql b/doc/mysql-ulogd2-flat.sql
index d71608c65f7f..6d663f6e035d 100644
--- a/doc/mysql-ulogd2-flat.sql
+++ b/doc/mysql-ulogd2-flat.sql
@@ -51,7 +51,7 @@ CREATE TABLE `ulog2` (
   `raw_label` tinyint(3) unsigned default NULL,
   `mac_saddr_str` varchar(32) default NULL,
   `mac_daddr_str` varchar(32) default NULL,
-  `oob_protocol` smallint(5) default NULL,
+  `oob_protocol` smallint(5) unsigned default NULL,
   `raw_type` int(10) unsigned default NULL,
   `mac_str` varchar(255) default NULL,
   `tcp_sport` int(5) unsigned default NULL,
diff --git a/doc/mysql-ulogd2.sql b/doc/mysql-ulogd2.sql
index c44f9a9db1e4..782f3689dc0b 100644
--- a/doc/mysql-ulogd2.sql
+++ b/doc/mysql-ulogd2.sql
@@ -84,7 +84,7 @@ CREATE TABLE `mac` (
   `_mac_id` bigint unsigned NOT NULL auto_increment,
   `mac_saddr` varchar(32) default NULL,
   `mac_daddr` varchar(32) default NULL,
-  `mac_protocol` smallint(5) default NULL,
+  `mac_protocol` smallint(5) unsigned default NULL,
   UNIQUE KEY `key_id` (`_mac_id`)
 ) ENGINE=INNODB;
 
@@ -681,7 +681,7 @@ delimiter $$
 CREATE FUNCTION INSERT_OR_SELECT_MAC(
 		`_saddr` varchar(32),
 		`_daddr` varchar(32),
-		`_protocol` smallint(5)
+		`_protocol` smallint(5) unsigned
 		) RETURNS bigint unsigned
 NOT DETERMINISTIC
 READS SQL DATA
@@ -764,7 +764,7 @@ CREATE FUNCTION INSERT_PACKET_FULL(
                 raw_header varchar(256),
 		mac_saddr varchar(32),
 		mac_daddr varchar(32),
-		mac_protocol smallint(5),
+		mac_protocol smallint(5) unsigned,
 		_label tinyint(4) unsigned,
 		sctp_sport smallint(5) unsigned,
 		sctp_dport smallint(5) unsigned,
-- 
2.35.1

Re: [PATCH ulogd2] doc: mysql: declare MAC protocol columns unsigned

[Pablo Neira Ayuso]

On Sat, Nov 05, 2022 at 04:54:02PM +0000, Jeremy Sowden wrote:
> By default, MySQL smallints are signed.  This causes problems inserting packets
> for ethertypes above 0x7fff, such as IPv6 (0x86dd):
> 
>   MariaDB [ulogd]> SELECT INSERT_PACKET_FULL(...,'f4:7b:09:41:7a:71','f0:2f:74:4e:b2:f3',34525,0,NULL,NULL,NULL);
>                                                                                          ^^^^^
> 
> which fails as follows:
> 
>   ERROR 1264 (22003): Out of range value for column 'mac_protocol' at row 1

Applied, thanks