Database Errors while API-Import

[GoogleCodeExporter]

What steps will reproduce the problem?
First updating a POS from API shows different database-errors with "sql_mode    
            = STRICT_ALL_TABLES" in my.cnf (MySQL 5.0), Import don't work with 
them.

What is the expected output? What do you see instead?
No errors and a working API-Import ;)

What version of the product are you using? On what operating system?
svn checkout + fresh install from 31.01.2011 on php-5.2.6 with MySQL-5.0 (linux)

Please provide any additional information below.
I've change the structure of the 'pos3_tower_info'-table, so that it works for 
me:

######
-- phpMyAdmin SQL Dump
-- version 3.3.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Erstellungszeit: 01. Februar 2011 um 08:53
-- Server Version: 5.0.51
-- PHP-Version: 5.2.6-1+lenny9

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Datenbank: `voidhawk_POS`
--

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `pos3_tower_info`
--

CREATE TABLE IF NOT EXISTS `pos3_tower_info` (
  `pos_id` int(10) unsigned NOT NULL auto_increment,
  `typeID` bigint(20) default NULL,
  `evetowerID` bigint(20) default NULL,
  `outpost_id` bigint(20) default NULL,
  `corp` varchar(150) default NULL,
  `allianceid` int(30) unsigned default NULL,
  `pos_size` tinyint(3) unsigned NOT NULL default '0',
  `pos_race` tinyint(3) unsigned NOT NULL default '0',
  `isotope` int(10) unsigned NOT NULL default '0',
  `oxygen` int(10) unsigned NOT NULL default '0',
  `mechanical_parts` int(10) unsigned NOT NULL default '0',
  `coolant` int(10) unsigned NOT NULL default '0',
  `robotics` int(10) unsigned NOT NULL default '0',
  `uranium` int(10) unsigned NOT NULL default '0',
  `ozone` int(10) unsigned NOT NULL default '0',
  `heavy_water` int(10) unsigned NOT NULL default '0',
  `charters` int(10) unsigned NOT NULL default '0',
  `strontium` int(10) unsigned NOT NULL default '0',
  `towerName` varchar(150) NOT NULL default '',
  `systemID` int(10) NOT NULL default '0' COMMENT 'EVE System ID',
  `charters_needed` tinyint(1) unsigned NOT NULL default '0',
  `status` varchar(255) NOT NULL default '0',
  `owner_id` int(10) unsigned default NULL,
  `secondary_owner_id` int(11) default NULL,
  `pos_status` tinyint(10) unsigned NOT NULL default '1',
  `pos_comment` varchar(255) default NULL,
  `secret_pos` tinyint(1) NOT NULL default '0',
  `moonID` bigint(20) NOT NULL default '0',
  `onlineSince` datetime default NULL,
  `powergrid` int(10) unsigned default NULL,
  `cpu` int(10) unsigned default NULL,
  PRIMARY KEY  (`pos_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

Original issue reported on code.google.com by alex.gae...@googlemail.com on 1 Feb 2011 at 8:04

[GoogleCodeExporter]

I'm not fully sure what's going on here but there are a few things probably 
going on.

1. The POS Tracker is insanely picky on its database. You change how it 
operates and the entire thing pretty much blows up. So saying you're changing 
its structure scares me.

2. You have on "STRICT_ALL_TABLES". I am no MySQL expert in any way but knowing 
that the tracker pulls in lots of data. Probably some of it invalid/incorrect 
at various stages during imports and updates, having such a setting set like 
"STRICT_ALL_TABLES" will most likely never work with the tracker. Not unless 
someone spends the time to vigorously test out different setups and tries to 
figure out what data is invalid.

If this project was being worked on by 20+ people and some MySQL experts then 
this request can probably be fixed/figured out. However being a one man team 
very little MySQL experience with a backlog of requests, I doubt I would get to 
this before the year 2012. So I'm sorry but I don't think there is much I can 
do here for you unless you know exactly what needs to be fixed that doesn't 
break other installs.

-FG

Original comment by frozenic...@gmail.com on 2 Feb 2011 at 11:14

• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

[Theoretical prework]

If you use modes like "STRICT_ALL_TABLES", you can only insert a value like 
"123" in a tinyint(3) field. If you try to inset "1.02" or "1234" in that 
field, you will get an permanet error and there will be nothing inserted.

If you don't set such "hard-modes" like "STRICT_ALL_TABLES", MySQL will behave 
more "comfortable" and tries to "guess" what you likely try to insert. So, if 
you try to insert "1.02" in a tinyint(3) field, there will be a "1" inserted. 
If you try to insert a "1.92", there will be a "2" inserted. If you try to 
insert a "123456789" in that field, there will be a 127 in (or 255 if the field 
is flagged as UNSIGNED. See the problem?
[/Theoretical prework]

[This Problem]
Back to i've changed the database-structure only a little bit, so it works for 
me. Here is the diff for you to make the life easyer:
(a "-" Sign ahead mean, this is the Database-Structure from you and i have 
changed it, a "+" Sign ahead means this is the change. Not one of this signs 
ahead means, only context around this changes.)

--- data_orig.sql       2011-02-04 10:42:16.047999082 +0100
+++ data_new.sql        2011-02-04 10:41:04.929047897 +0100
@@ -1,10 +1,10 @@
-CREATE TABLE `%prefix%tower_info` (
+CREATE TABLE IF NOT EXISTS `pos3_tower_info` (
   `pos_id` int(10) unsigned NOT NULL auto_increment,
-  `typeID` bigint(20) NOT NULL default '0',
-  `evetowerID` bigint(20) NOT NULL default '0',
-  `outpost_id` bigint(20) NOT NULL default '0',
-  `corp` varchar(150) NOT NULL default '',
-  `allianceid` int(30) unsigned NOT NULL,
+  `typeID` bigint(20) default NULL,
+  `evetowerID` bigint(20) default NULL,
+  `outpost_id` bigint(20) default NULL,
+  `corp` varchar(150) default NULL,
+  `allianceid` int(30) unsigned default NULL,
   `pos_size` tinyint(3) unsigned NOT NULL default '0',
   `pos_race` tinyint(3) unsigned NOT NULL default '0',
   `isotope` int(10) unsigned NOT NULL default '0',
@@ -28,7 +28,7 @@
   `secret_pos` tinyint(1) NOT NULL default '0',
   `moonID` bigint(20) NOT NULL default '0',
   `onlineSince` datetime default NULL,
-  `powergrid` int(10) unsigned NOT NULL,
-  `cpu` int(10) unsigned NOT NULL,
+  `powergrid` int(10) unsigned default NULL,
+  `cpu` int(10) unsigned default NULL,
   PRIMARY KEY  (`pos_id`)
[/This Problem]

Original comment by alex.gae...@googlemail.com on 4 Feb 2011 at 9:52

• Added labels: ****
• Removed labels: ****