>Is what I did safe / advisable? yes >Are there non-string primary keys in other tables? no
>Is 254 the right max everywhere? yes >b) or MSSQL users should edit the assp_db_import.cfg file like I did That's why the file assp_db_import.cfg is there. I don't recommend to change any line - because the lines in the 'vanilla' file are the most generic. If you need to change anything for a special DB version, add a new line for it - don't forget to specify the version number/string! like : Microsoft SQL Server|version here|$sql_sm=.... Thomas Von: K Post <nntp.p...@gmail.com> An: ASSP development mailing list <assp-test@lists.sourceforge.net> Datum: 01.03.2015 19:56 Betreff: Re: [Assp-test] whitelist info in new database I don't completely follow all the steps that the import process uses. In trying to figure out assp_db_import.cfg I see there's CONVERT functions there. This: https://msdn.microsoft.com/en-us/library/ms187928.aspx says: length: Is an optional integer that specifies the length of the target data type. The default value is *30.* assp_Db_import.cfg has:Microsoft SQL Server|*|$sql_sm="ALTER TABLE $mysqlTable DROP CONSTRAINT $PK_NAME"|$sql_sm="ALTER TABLE $mysqlTable ADD CONSTRAINT $PK_NAME PRIMARY KEY (pkey)"|$sql_sm="INSERT INTO $mysqlTable SELECT "|$sql_sm="CONVERT($pkey_TYPE_NAME,$k),CONVERT($pvalue_TYPE_NAME,$v),CONVERT($pfrozen_TYPE_NAME,\'$f\')"|$sql_sm="UNION ALL SELECT "|100 I changed this toMicrosoft SQL Server|*|$sql_sm="ALTER TABLE $mysqlTable DROP CONSTRAINT $PK_NAME"|$sql_sm="ALTER TABLE $mysqlTable ADD CONSTRAINT $PK_NAME PRIMARY KEY (pkey)"|$sql_sm="INSERT INTO $mysqlTable SELECT "|$sql_sm="CONVERT($pkey_TYPE_NAME*(254)*,$k),CONVERT($pvalue_TYPE_NAME,$v),CONVERT($pfrozen_TYPE_NAME,\'$f\')"|$sql_sm="UNION ALL SELECT "|100 (I added (254) after $pkey_TYPE_NAME Upon restarting assp with a new whitelist.rpl, I'm now getting the full entries!!! So more questions and notes:1) Is what I did safe / advisable? Are there non-string primary keys in other tables? Is 254 the right max everywhere? 2) It seems like either: a) ASSP should be fixed that when it looks at the pkey length from the database structure, it includes the length for strings b) or MSSQL users should edit the assp_db_import.cfg file like I did - *IF that is advisable* - 3) Does it make sense to have strings longer than 254 defined in the database? I suppose it's possible to have a really long email whitelisted on a personal whitelist that also has a long email address, netting a string longer than 254. Thoughts? Thanks! On Sun, Mar 1, 2015 at 1:31 PM, K Post <nntp.p...@gmail.com> wrote: > I don't know what do to - I can't seem to get any flat file to import more > than 30 characters per record. > > I copied the whitelist file from a working 2.x installation to the db > import location. Added .rpl as the extension. Upgraded the lab version of > assp to the one released yesterday, re-ran the newest assp.mod installer, > and updated via ppm too just to make sure all was updated. > > Still getting the exact same thing. All entries are limited to 30 > characters. I checked the newest whitelist file from the running > installation, and sample entries that I see truncated in the mssql database > are complete in the flat file. > > Any ideas? Is there anything that I can run or code that I can modify to > produce more useful debugging output? I was able to do a simple insert > statement using SQL Server Management Studio and was able to insert a 40 > character string no problem. > > I've got assp_db_import.cfg v 2.2.2 unmodified. Am I supposed to have > changed this somehow? > > I see this at startup which looks normal to me: > > Mar-01-15 12:27:10 Try using > ADO:database=asspdb;server=127.0.0.1;Provider=SQLNCLI11; database <> for > selected tables > Mar-01-15 12:27:10 Database import started for table whitelist > Mar-01-15 12:27:10 Backup: starting backup database table Whitelist to > file C:/ASSP/mssql/dbbackup/whitelist > Mar-01-15 12:27:13 Info: starting RebuildSpamdb Scheduler with '15 3 * * > *' - next RebuildSpamdb is scheduled for Mar-02-15 03:15:00 > Mar-01-15 12:27:15 Backup: 17,597 records of database table Whitelist to > file C:/ASSP/mssql/dbbackup/whitelist > Mar-01-15 12:27:15 Replacing records in table whitelist with records in > file C:/ASSP/mssql/dbimport/whitelist.rpl > Mar-01-15 12:27:16 20837 valid records of 20838 records found in > C:/ASSP/mssql/dbimport/whitelist.rpl > Mar-01-15 12:27:16 Trying Bulkimport for table whitelist > Mar-01-15 12:27:16 Database: Microsoft SQL Server 11.00.5058 > Mar-01-15 12:27:16 Removing PRIMARY KEY PK__whitelis__40A62DB95E8DA012 > from table whitelist > Mar-01-15 12:27:17 Added 2000 of 20837 records for table whitelist - > finished in 9 sec > Mar-01-15 12:27:17 Added 3000 of 20837 records for table whitelist - > finished in 5 sec > Mar-01-15 12:27:18 Added 4000 of 20837 records for table whitelist - > finished in 8 sec > Mar-01-15 12:27:18 Added 5000 of 20837 records for table whitelist - > finished in 6 sec > Mar-01-15 12:27:18 Added 6000 of 20837 records for table whitelist - > finished in 4 sec > Mar-01-15 12:27:19 Added 7000 of 20837 records for table whitelist - > finished in 5 sec > Mar-01-15 12:27:19 Added 8000 of 20837 records for table whitelist - > finished in 4 sec > Mar-01-15 12:27:19 Added 9000 of 20837 records for table whitelist - > finished in 3 sec > Mar-01-15 12:27:20 Added 10000 of 20837 records for table whitelist - > finished in 4 sec > Mar-01-15 12:27:20 Added 11000 of 20837 records for table whitelist - > finished in 3 sec > Mar-01-15 12:27:21 Added 14000 of 20837 records for table whitelist - > finished in 2 sec > Mar-01-15 12:27:22 Added 16000 of 20837 records for table whitelist - > finished in 1 sec > Mar-01-15 12:27:23 Added 19000 of 20837 records for table whitelist - > finished in 0 sec > Mar-01-15 12:27:24 Bulkimport for table whitelist finished > Mar-01-15 12:27:24 Removing duplicate keys from table whitelist > Mar-01-15 12:28:15 Removed 984 duplicate keys from table whitelist > Mar-01-15 12:28:15 Adding pimary key PK__whitelis__40A62DB95E8DA012 to > table whitelist <-- FYI note type-o "pimary" instead of "primary" > Mar-01-15 12:28:15 Successfully added 19853 records in to table whitelist > Mar-01-15 12:28:15 Using table <whitelist> in > ADO:database=asspdb;server=127.0.0.1;Provider=SQLNCLI11; Database <> > instead of file C:/ASSP/whitelist > > On Sat, Feb 28, 2015 at 1:44 AM, Thomas Eckardt < > thomas.ecka...@thockar.com> wrote: > >> >Have you ever seen >> >this before? >> >> never. >> >> Thomas >> >> >> >> Von: K Post <nntp.p...@gmail.com> >> An: ASSP development mailing list <assp-test@lists.sourceforge.net> >> Datum: 28.02.2015 04:03 >> Betreff: Re: [Assp-test] whitelist info in new database >> >> >> >> My table defs looks good: >> >> CREATE TABLE [dbo].[whitelist]( >> [pkey] [varchar](254) NOT NULL, >> [pvalue] [varchar](255) NULL, >> [pfrozen] [int] NOT NULL, >> CONSTRAINT.... >> >> I copied the import file and opened it in an editor just as a way to check >> that the data wasn't truncated there. It seems like the 30 character >> truncation happens upon import, but I can't tell why. Have you ever >> seen >> this before? I haven't found the spot in the code that the import gets >> made to look at the code. >> >> Strange right? I obviously can't test the other areas of the system until >> this critical bit is working. >> >> On Fri, Feb 27, 2015 at 7:05 AM, Thomas Eckardt >> <thomas.ecka...@thockar.com> >> wrote: >> >> > Check the table structure - assp checks it at every startup >> > >> > .... >> > CREATE TABLE [dbo].[PBBlack]( >> > [pkey] [varchar](254) NOT NULL, >> > [pvalue] [varchar](255) NULL, >> > [pfrozen] [int] NOT NULL, >> > PRIMARY KEY CLUSTERED >> > (.... >> > >> > NEVER EVER open an import file in an editor and store it! These files >> are >> > NOT plain text - even they look like. >> > >> > Thomas >> > >> > >> > >> > Von: K Post <nntp.p...@gmail.com> >> > An: ASSP development mailing list <assp-test@lists.sourceforge.net> >> > Datum: 27.02.2015 02:58 >> > Betreff: Re: [Assp-test] whitelist info in new database >> > >> > >> > >> > Looks like each table's keys are truncated to 30 chars. I can't imagine >> > that's right. >> > I looked at the import files, and the length of the values is fine. >> > >> > Any idea where I (or the code) could be going wrong? >> > >> > thanks >> > >> > >> > On Thu, Feb 26, 2015 at 6:35 PM, K Post <nntp.p...@gmail.com> wrote: >> > >> > > I just was able to import a flat whitelist into a test MSSQL 2012 >> > database >> > > (thrilled) >> > > >> > > 2 questions: >> > > 1) I see a persblack (which I assume to be personal blacklist), but no >> > > perswhite. Is that correct? >> > > >> > > 2) In looking at the whitelist table, it appears that the maximum >> length >> > > of addresses in 30 characters. I see addresses like: >> > > u...@thedomain.com (fine) >> > > u...@thedomain.com,o...@us.org (fine I assume pers white) >> > > long-username@longdomainname.o (truncated @ 30 chars) >> > > u...@thedomain.com,ouruser@our (truncated @ 30 chars) >> > > >> > > Is this the way it's supposed to work? I checked the table def and >> its >> > > set at 255 chars (why not have that be even longer?) >> > > >> > > >> > > >> > > >> > >> > >> >> ------------------------------------------------------------------------------ >> > Dive into the World of Parallel Programming The Go Parallel Website, >> > sponsored >> > by Intel and developed in partnership with Slashdot Media, is your hub >> for >> > all >> > things parallel software development, from weekly thought leadership >> blogs >> > to >> > news, videos, case studies, tutorials and more. Take a look and join the >> > conversation now. http://goparallel.sourceforge.net/ >> > _______________________________________________ >> > Assp-test mailing list >> > Assp-test@lists.sourceforge.net >> > https://lists.sourceforge.net/lists/listinfo/assp-test >> > >> > >> > >> > >> > >> > >> > DISCLAIMER: >> > ******************************************************* >> > This email and any files transmitted with it may be confidential, >> legally >> > privileged and protected in law and are intended solely for the use of >> the >> > >> > individual to whom it is addressed. >> > This email was multiple times scanned for viruses. There should be no >> > known virus in this email! >> > ******************************************************* >> > >> > >> > >> >> ------------------------------------------------------------------------------ >> > Dive into the World of Parallel Programming The Go Parallel Website, >> > sponsored >> > by Intel and developed in partnership with Slashdot Media, is your hub >> for >> > all >> > things parallel software development, from weekly thought leadership >> blogs >> > to >> > news, videos, case studies, tutorials and more. Take a look and join the >> > conversation now. http://goparallel.sourceforge.net/ >> > _______________________________________________ >> > Assp-test mailing list >> > Assp-test@lists.sourceforge.net >> > https://lists.sourceforge.net/lists/listinfo/assp-test >> > >> >> ------------------------------------------------------------------------------ >> Dive into the World of Parallel Programming The Go Parallel Website, >> sponsored >> by Intel and developed in partnership with Slashdot Media, is your hub for >> all >> things parallel software development, from weekly thought leadership blogs >> to >> news, videos, case studies, tutorials and more. Take a look and join the >> conversation now. http://goparallel.sourceforge.net/ >> _______________________________________________ >> Assp-test mailing list >> Assp-test@lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/assp-test >> >> >> >> >> >> >> DISCLAIMER: >> ******************************************************* >> This email and any files transmitted with it may be confidential, legally >> privileged and protected in law and are intended solely for the use of the >> >> individual to whom it is addressed. >> This email was multiple times scanned for viruses. There should be no >> known virus in this email! >> ******************************************************* >> >> >> ------------------------------------------------------------------------------ >> Dive into the World of Parallel Programming The Go Parallel Website, >> sponsored >> by Intel and developed in partnership with Slashdot Media, is your hub >> for all >> things parallel software development, from weekly thought leadership >> blogs to >> news, videos, case studies, tutorials and more. Take a look and join the >> conversation now. http://goparallel.sourceforge.net/ >> _______________________________________________ >> Assp-test mailing list >> Assp-test@lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/assp-test >> > > ------------------------------------------------------------------------------ Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ _______________________________________________ Assp-test mailing list Assp-test@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/assp-test DISCLAIMER: ******************************************************* This email and any files transmitted with it may be confidential, legally privileged and protected in law and are intended solely for the use of the individual to whom it is addressed. This email was multiple times scanned for viruses. There should be no known virus in this email! ******************************************************* ------------------------------------------------------------------------------ Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ _______________________________________________ Assp-test mailing list Assp-test@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/assp-test
