Notes

So, during a routine totals recalculation from the Configurator, we suddenly got the error: "the year in the Date literal exceeds 3999". Which means there's a date somewhere in the database — or trying to get there — that is beyond the maximum 1C can handle: 31.12.3999 23:59:59.

Alright. What do we do with that?

The main thing is to identify the exact table that contains the bad dates. Since the error showed up during totals recalculation, the obvious place to look is the registers. Open the standard totals management tool, start recalculation, and you'll get the name of the register the platform trips over.

Another approach — more proper, methodologically speaking — is to enable tech log collection (SDBL, EXCP, and EXCPCNTX) and get a log that looks roughly like this. In that log, find the EXCP event, and right before it the SDBL event. That SDBL entry contains the SQL query that caused the crash. In its text, you'll see the table name you need (AccumRgTn11530). The corresponding configuration object name can be pulled out with pretty much any utility built around GetDBStorageStructureInfo().

One way or another, you end up with the register name. Open its form, sort by period, and you'll quickly get to the bad entries. Then the job is to inspect the documents that created those records, fix the root cause, and repost the documents. If totals recalculation is still broken after that, then either this is not the only table with broken dates, or those dates have already spread somewhere beyond the records table.

For example, I once had a case where fixing the records was not enough, because records with invalid dates were still sitting in the turnover table. The platform itself could not remove them, but the database was small enough that I could get away with some restructuring tricks. I pulled the following stunt: turned off the "Use in totals" flag for all register dimensions and applied the changes; then turned it back on and recalculated totals again. As a result, the register's turnover table was physically dropped, then recreated and filled from scratch — this time without dates from the distant future.

As a last resort, you could delete the bad records with direct SQL queries (DELETE or even TRUNCATE). But first, that violates the platform vendor's license agreement, and second, it's risky: it's very easy to remove something important and not notice until later. So I would, uh, not recommend trying that at home.

The platform has a few different ways to decide whether a user can see a metadata object. One is functional options. If you haven’t used them before, here’s the short version: an object can be included in an option that is either active in a particular infobase, making it visible to users, or inactive, hiding it. An object can belong to several options, in which case it appears if at least one is active, or to none, in which case users always see it.

I remember looking, about five years ago, for a way to tell from code whether a user could see an object, just as far as functional options are concerned. For some reason, I didn’t find a solution then, although it’s ridiculously simple. Here’s a function for exactly that. The logic:

1. Iterate through the options and look for the object in each one.
2. If the object belongs to an option, check whether that option is active. If so, the user can see the object.
3. If the object belongs to no options, the user can see it too.

You’d want to cache the result, of course, if only because there’s a query inside a loop. Apart from that, it does the job. I still can’t understand why I didn’t write something similar back then. Man, I hope that five years from now, the things driving me nuts today will look just as easy to fix.

Some time ago, I tried finding code vulnerabilities with Bash scripts. Sounds intimidating, but it’s just recursive text searching with regular expressions. I wrote the scripts, but as I realized today, I did it in a rather… ass-backwards way, ahem. All you need is egrep! Out of find, xargs, and egrep, we can throw away two of the three.

For example, today our configuration stopped building in the latest EDT release. We suspected broken GUIDs: references to metadata objects removed from the configuration. The platform doesn’t always clean these up after deleting the objects themselves. I’ve written about this a couple of times, for example here and here.

We decided to search the configuration export for GUIDs and check whether they all belonged where they were. All it takes is one command:

egrep -rn '.{8}-.{4}-.{4}-.{4}-.{12}' dump

The r flag enables recursive search; n adds not only the filename but also the line number to each match. The final parameter, dump, is the directory to search.

You could make the regex more precise, but this is plenty. False positives, GUIDs that aren’t broken references, are easy to filter through a pipe. For example, the following script excludes GUID-containing lines that include “uuid”:

egrep -rn '.{8}-.{4}-.{4}-.{4}-.{12}' dump | grep -v 'uuid'

I did some reading online about date types in MS SQL Server, and I no longer wonder why 1C still clings to its date offset. People describe a whole bundle of DATETIME2 problems:

1. You can’t do basic date arithmetic. Want to subtract two dates or add a day? You’ll need a workaround.
2. Standard functions still return good old DATETIME, DATEADD for example. If you store your data as DATETIME2, you have to convert it.
3. Fields of this type don’t index well, since each DATETIME2 value is stored backwards, time first, date second. So the DBMS gets its row count estimate wrong and comes up with a bad execution plan.

You can read more on Towards Data Science or, for example, SQL Server Central.

MS SQL Server DATETIME fields cannot store dates before 1753. Try writing January 1, 0001 to the database, for example, and you’ll get an out-of-range value error. I thought this was a funny old quirk for such a well-established DBMS until I stumbled across the reason.

Briefly, Britain adopted the Gregorian calendar in 1752, losing eleven days in the process. That created a problem: suppose a user wants the difference in days between 1653 and 1753. What should happen? Account for the missing days? Ignore them? Add hints or settings?

Apparently, the developers decided not to get into all that and just cut off the date range. If you need earlier dates, DATETIME2 doesn’t have that restriction.

As for 1C, the platform originally used DATETIME and devised a special workaround to avoid trouble storing dates before 1753. In short, when writing dates to the database, it quietly adds two thousand years to each one, then subtracts them when reading. A user sees January 1, 2000 in 1C, while the database actually stores January 1, 4000.

I wonder why 1C hasn’t thrown this out yet. The platform uses DATETIME2 now, so it shouldn’t need the offset trick anymore. Maybe there’s a catch, or maybe they’re just being careful, but the DBMS developers themselves seem quite confident:

Your great great great great great great great grandfather should upgrade to SQL Server 2008 and use the DateTime2 data type, which supports dates in the range: 0001-01-01 through 9999-12-31.

Joe Stefanelli (SQL Server developer)

Perhaps it’s simply at the bottom of the priority list. Adding and subtracting two thousand years for every date increases hardware load, of course, but it gets lost among everything else.

On a PostgreSQL course, I learned a funny detail: in version 10, the DBMS developers renamed pg_xlog, containing write-ahead logs, to pg_wal, and pg_clog, containing transaction statuses, to pg_xact.

Know why? Because of admins with more confidence than experience, who saw “log” in a folder name and went for it. I need to free some disk space, they’d think, and this DBMS has filled it all with stupid logs. No time to investigate: rm -rf the lot, problem solved!

So out went both the protection for data operations in the buffer cache and multiversion concurrency control, in one fell swoop. The cluster then died in agony. There was plenty of free disk space, but I’m afraid the joy didn’t last long :-)

How do you know when to take a break from work? Put it this way: if your script suddenly starts winking at you, it’s definitely time for some fresh air.

So, Designer reports an error, and we need to fix it or find a workaround. This could get awkward: we can’t look at the application’s code. Still, to fix it, we need to work out what Designer was doing before it fell over, and why it couldn’t finish.

What can help?

First, the error message itself. Often, that alone is enough to get your thoughts moving in the right direction. If a query caused the failure, the error will also include a message from the DBMS.

Second, the technological log, capturing EXCP, SDBL, and DBMSSQL (DBPOSTGRS?) events for t:applicationName=Designer. That shows us the exceptions inside Designer and the queries it runs, which are often what went wrong.

A database query trace can also help. With MS SQL, you can capture it using Extended Events; specifically, we’re interested in error_reported, rpc_completed, and sql_batch_completed. The principle is the same: capture query execution errors and the queries themselves.

Here’s an example. Maybe not the best one, but it’s fresh.

We’re working with a configuration that uses data separation. Its database is divided into pieces, or data areas, each of which knows nothing about its neighbors: how many there are, their sizes, and so forth. The database also contains shared data, such as catalogs accessible from any area. These usually hold technical information, for example a list of metadata objects.

We need to take the FileStorageVolumes catalog out of the main separator. The catalog is currently separated, meaning its data differs from one area to another. We need its contents to become the same across all areas.

This should be straightforward, since the catalog table is empty: no area stores anything in it. We apply the setting, and:

The exception text contains two messages: one from the platform, the other from the DBMS. The first one makes me scratch my head: what does it mean, the data isn’t unique? The catalog is empty; there’s no data. Maybe something’s wrong with one of the supporting structures, rather than the catalog data itself.

The DBMS message is clearer: MS SQL Server tried to create a unique index on _DataHistorySettingsNG, but couldn’t because combinations of indexed fields weren’t unique. It even gives the specific value that prevented index creation: NULL.

Conclusions?

1. The problem clearly arose during restructuring. First, that’s exactly what we were doing. Second, both CREATE UNIQUE INDEX, since creating indexes is part of restructuring, and the problematic table’s name point to it. The NG suffix is assigned to table copies created during restructuring. If restructuring succeeds, the platform drops the original table and renames the copy.
2. The problem involves the data history mechanism’s settings, _DataHistorySettings. This table stores each metadata object’s status: whether to maintain data history for the object, its fields, and its tabular section fields, if any.

That explains how an empty catalog can have a uniqueness problem: an object’s data history settings exist independently of whether the object contains data. The settings table has just three fields: _MetadataId (the metadata object’s ID), _Content (setting values), and _Fld626 (the area separator).

Before restructuring, the data looks roughly like this:

But then the picture changed. When we excluded the catalog from the common attribute, Designer started restructuring: it created _DataHistorySettingsNG, copied the data from _DataHistorySettings, and set _Fld626 to NULL for all records belonging to FileStorageVolumes.

The result? A whole pile of settings for FileStorageVolumes that belong to no data area. That sounds unhealthy in itself, but the real trouble started when Designer tried to create the table’s clustered index. It is built on _MetadataId and _Fld626 and is unique, so it cannot be created: many records differ only in _Content, while _MetadataId and _Fld626 are guaranteed to be identical.

Just to be sure, let’s check the technological log. I’ve stripped out the unrelated events and other clutter. Sure enough: Designer creates and populates _DataHistorySettingsNG, tries to index it, receives an error, and drops it. The DBMS trace shows roughly the same picture.

That’s where the interesting part ends, since the solution is fairly obvious: delete the catalog’s data history settings in every data area and repeat restructuring. This doesn’t fix the bug, of course. Really, Designer should catch this and give us a sensible error message, as it does when deleting an information register dimension would violate the uniqueness of its records. But it does get the task done.

And we’re done. Look at us go!

Another script. It counts exceptions per minute and ranks the results, so you can quickly see when the applications were having a particularly bad time.

Along the way, two odd little problems threw me off. First, for some reason I was convinced that uniq -c groups lines regardless of where they occur in the data stream. Take this:

банан
банан
груша
банан

I thought that feeding this data to uniq -c would group identical lines, count repetitions, and produce something like this:

3 банан
1 груша

But what I actually got was:

2 банан
1 груша
1 банан

Turns out uniq expects repeated lines to sit next to each other. If a line differs from the previous one, it starts a new count for that line. To get the result I expected, you have to sort the data first, then pass it to uniq.

The second problem involved sed. I was trying to use it to remove everything from the data stream except hours and minutes; the attempt is on line 12 of the script. But some events stubbornly failed to match the regular expression despite looking exactly the same. I wasted a lot of time and got thoroughly annoyed before remembering BOM. Once I removed the markers, everything went smoothly.

Every technological log file uses a BOM, which you can confirm with this script, for example. Each log begins with special characters invisible to the naked eye, and these can prevent the first line from being processed because they don’t match the regular expression.

My takeaway is to strip BOMs out by default, rather than wonder every time whether they’ll cause trouble. Sometimes this will be unnecessary; a BOM doesn’t interfere with solving the task above using grep, for instance. But I don’t like surprises. Besides, a few processor cycles spent on a simple replacement are a much better deal than the equivalent in fried nerves and wasted time.

On the screenshot above my little one is just letting me know where she is: walking down the street, at the school, at a playground. It's not her fault that I have a sort of nerd view: when I look at this chat I see nothing but code on Gherkin, which is just a bit broken. A little patch here and there and it will be fine :-)

And I exit home
Then I enter school
And I exit school
Then I gonna swing

We use Gherkin to write our autotests for Vanessa Automation. I can't say I've made very many of them, especially in comparison with some of my colleagues… Well, it seems like I've made them enough to twist the reality a few.

I’ve put together two more technological log analysis scripts: the first ranks heavy MS SQL queries, and the second ranks long lock waits.

I pick out heavy queries by how long their DBMSSQL events lasted. The longer a query takes, the more likely it is to have eaten a lot of resources. That usually holds up, though you’d need a trace to diagnose it properly.

Lock waits are also measured by duration. The script also checks that the TLOCK event has something in WaitConnections. That tells us the platform really was waiting to get a lock, rather than just taking a while to set one.

I rewrote the Bash script that ranks exceptions in a collected technological log: I wanted a simpler way to do it.

Out went all the fiddling with replacing event beginnings with a gawk record separator: you can just give gawk a regex for that. I also moved more of the logic into gawk itself. It’s easier to follow, especially if I want to add to it later.

It’s clearly better than before, or at least the logic is easier to understand. Originally, though, I wanted to make the script smaller and get something like this:

grep -hoP ",EXCP,.*\KDescr=.*" */*.log | uniq -c | sort -rn

Filter only lines with EXCP events, cut everything before the error description, and group with uniq. Very elegant, I think.

Except EXCP descriptions can run over several lines. So sometimes we’d lose information we need to figure out what happened: everything after the first line break. I haven’t yet worked out how to solve that without tripling the script’s size :-)

Last month, I attended an Infostart meetup about security in 1C-based solutions. I learned a lot! Among other things, Oleg Tymko talked about things we do in code that can leave a product vulnerable: hard-coded IP addresses, links, email addresses, passwords, that sort of thing.

Yesterday, I put together a simple Bash script that searches a configuration export for such issues. Essentially, it just runs regular expressions over the configuration. I didn’t put too much thought into it, just tried a couple of approaches for the fun of it.

My takeaway? If you’re going to keep developing this seriously, start with something like SonarQube. Brute-force approaches produce too many false positives; you need filters, exceptions, history, and supporting infrastructure.

For a quick look, though, this does the job: no infrastructure, no fussing with configuration or hardware. Export the configuration, kick Bash into action, and that’s it: results in a few seconds.

Whatever else you make of this clash of two yokozuna the Apple–Epic Games fight, it’s a great reminder that you shouldn’t use Single Sign-On on the internet: anywhere, ever, on any service. Who knows which giants will suddenly come to blows next, or which website will ban you for no clear reason? Because screw you, dude, that’s why.

The users in that tweet might get off lightly. But if, say, Google blocks your account, you could be in real trouble: a pile of services you accessed through Google will suddenly turn into pumpkins.

There are plenty of solutions just as convenient as Single Sign-On. Password managers, browser extensions, hardware keys: almost anything is better than proprietary, closed-source services you have no say in.

At the end of August, I passed the 1C Professional test on technological issues in large implementations. It’s basically your ticket to the main exam, a way to thin the crowd and get people to brush up on the theory.

The test has fourteen questions randomly chosen from around five hundred possibilities. You need at least twelve correct answers. You get half an hour, though five to ten minutes is enough in practice: you can work out most answers or just memorize them. There are inaccuracies and vague wording, but not many. Decent questions, in short.

The selection of topics felt like quite a jumble, a little bit of everything. Still, covering all that ground is probably a good thing: you look at another unfamiliar question, answer it, get it wrong, get over being annoyed, and go find out why. You become a little smarter, though no guarantees.

Here’s one way to solve unresolved references left after deleting a functional option. It starts with a configuration check giving me a couple of dozen messages like this:

CommonForm.PersonalSettings.Form Unresolved metadata object references (2)
Catalog.BankAccounts.Form.GLAccountsEditForm.Form Unresolved metadata object references (1)
Catalog.CashRegisters.Form.GLAccountsEditForm.Form Unresolved metadata object references (1)

I export the configuration to files and open Form.xml for one of the affected forms. I pick a form with fewer controls, so there’s less stuff to sift through.

A quick scan for anything unusual doesn’t take long:

A normal functional option reference is its name, as with DepreciationOfAssets. If there’s a GUID instead, that functional option is missing from the configuration. The reference is unresolved.

I search the rest of the export for that GUID, and there are almost all the problems the platform complained about. Fixing them is easy: remove the broken reference from the XML and load the file back into the configuration. There’s an even simpler approach: open the form control’s functional options list and immediately save it. That also removes the broken reference.

Why is looking for these strays in Designer itself a job for incurable optimists? Because the problem is almost impossible to notice. At best, you’ll see something like this:

Designer knows there are two options in the field, but can’t get a name for the second one. It’s gone. And most often there’s only one functional option! Then it looks like this:

A couple of weeks ago, I encountered an annoying bug. The task couldn’t be simpler: find a document by number. Do it this way, and the document is found:

SELECT Ref FROM Document.Invoice WHERE Number = &Number

But this way? No luck:

Documents.Invoice.FindByNumber(Number)

At first I froze for a moment, then checked the documentation and, of course, found the answer. FindByNumber() has a second parameter, IntervalDate, for finding documents with periodic numbering. It narrows the search to a particular period. If numbering resets annually and you pass May 1, 2020, for example, the search covers January 1 through December 31, 2020. And Invoice does indeed use annual numbering.

So what’s the problem? The vague phrase “the parameter is used for documents with periodic numbering” actually hides an iron rule: you cannot omit the parameter for such documents.

To check, let’s look at the SQL the DBMS actually runs. An ordinary query brings no surprises. P1 is the separator, P2 the document number:

SELECT
    T1._IDRRef 
FROM
    dbo._Document283 T1
WHERE
    ((T1._Fld704 = @P1))
    AND ((T1._Number = @P2))

But calling FindByNumber() adds a third parameter with the value 2001-12-31 23:59:59. Yes, “the end of the first year since the beginning of time”:

SELECT
    T1._IDRRef
FROM
    dbo._Document283 T1
WHERE
    ((T1._Fld704 = @P1))
    AND
    (
        T1._Number = @P2
        AND T1._Date_Time <= @P3
    )

Naturally, this query doesn’t find the required document. It won’t find anything in a real infobase, ever, under any circumstances.

The solution is clear: pass IntervalDate or, if we don’t have a date, use the Standard Subsystems Library’s ObjectAttributeValue() or an equivalent. But honestly, the platform should throw an exception when FindByNumber() is called without IntervalDate on a document with periodic numbering, rather than quietly execute a query guaranteed to be pointless.

For posterity, I’ve uploaded one of the inefficient queries I worked on a few months ago to GitHub Gist. It powered a dynamic list on a catalog item form, and whenever the user opened that tab, the platform went off to meditate, even with a relatively small database.

At first glance, it looks straightforward: a bunch of queries against document tables, joined with UNION ALL. Each table is filtered by roughly the same conditions: reference type, date, and whether the reference appears in a subquery’s result.

But there are several potential problems. First, at least 14 tables are involved, one per combined query. That alone raises the risk that the optimizer won’t have time to choose even a reasonably decent plan. Most likely, it will just take a wild guess and hope for the best.

Second, much of that work is probably wasted. Each query has something like this:

WHERE VALUETYPE(AdditionalExpenses.Ref) IN (&DocumentsListSelectedTypes)

The dynamic list receives a list of document types to display. But that filter kicks in after the data has been read: even if the user only wants invoices, the DBMS still reads all 14 tables, then discards 13 of them.

Still, none of that is especially critical. If that were the full list of problems, we might never have investigated. The main issue is the second condition in the WHERE clauses: each query checks whether the document reference appears in a subquery’s result.

Using subqueries in conditions is almost taboo unless temporary tables are involved: the DBMS often can’t work out how much data it’ll get back, or how best to handle it. And the data source makes things even worse here. A filter criterion isn’t a database table you can read, even by scanning; it’s a collection of tables. DocumentsByProject contains thirty!

That sounds bad enough, but there’s more: each of the fourteen combined queries selects documents of a particular type from those thirty tables.

And… scene :-)

I love JavaScript! Whenever some bizarre nonsense in my beloved platform pisses me off, I open any thread about web developers’ daily lives and calm down quickly, very quickly.

Similar tricks are possible in 1C, though. JavaScript understands window.window.window.location, for example, while the platform understands this :-)

I’ve improved Service Launcher. If you run the script without parameters, specifying neither -start nor -stop, it now decides for itself whether to start or stop the services.

It checks the status of the first service in the list. If it’s running, the script stops everything on the list. If it’s stopped, the script tries to start them all.

Why? If, like me, you run the script from a programmable keyboard, you now need only one key. Previously, you needed two: one to start the services and another to stop them.