0

I have the following table:

CREATE TEMPORARY TABLE `new_aps` (
  `MinuteStart` datetime NOT NULL,
  `ProxyListID` bigint(20) NOT NULL,
  `SuccessCount` double(22,0) NOT NULL,
  `UnknownCount` double(22,0) NOT NULL,
  `FailureCount` double(22,0) NOT NULL,
  PRIMARY KEY (`ProxyListID`,`MinuteStart`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

When I try to run the following query in HeidiSQL:

INSERT INTO new_aps (MinuteStart, ProxyListID, SuccessCount, UnknownCount, FailureCount)
SELECT NOW(), 1, 0.5, 1.5, 2.5

I get the following entry in new_aps table:

SELECT * FROM new_aps
"2020-06-11 11:58:19"   "1" "0" "1" "2"

What am I doing wrong? I need double-precision floating-point values stored in the table.

MariaDB version is 10.4.13 on Windows 10 64-bit.

3
  • "Counts", to me mean integral values. Use an integer type?
    – Rick James
    Jun 11 '20 at 16:43
  • @RickJames, no, they are not integers because they are divided by the number of lists a proxy belongs to. You can rather suggest a better name for such amounts :) . Jun 11 '20 at 17:26
  • 1
    "Rate", "Ratio", "PerList". Maybe "Percent" or "Pct", but that might mean throwing a factor of 100.
    – Rick James
    Jun 11 '20 at 17:29
1

Apparently HeidiSQL corrupts the table creation code. The proper table creation code is (without bounds after double):

CREATE TEMPORARY TABLE `new_aps` (
  `MinuteStart` datetime NOT NULL,
  `ProxyListID` bigint(20) NOT NULL,
  `SuccessCount` double NOT NULL,
  `UnknownCount` double NOT NULL,
  `FailureCount` double NOT NULL,
  PRIMARY KEY (`ProxyListID`,`MinuteStart`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

And the proper way to get it is from the database server, rather than HeidiSQL client:

SHOW CREATE TABLE new_aps

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.