21
SQL Server does not store UTF-8 under any circumstances. You get either UTF-16 Little Endian (LE) via NVARCHAR (including NCHAR and NTEXT, but don't ever use NTEXT) and XML, or some 8-bit encoding, based on a Code Page, via VARCHAR (including CHAR and TEXT, but don't ever use TEXT).
The problem here is that your code is mistranslating that 0x82 character, ...
18
The "Cannot assign requested address" part in the error message comes from the kernel TCP stack. When encountered intermittently, this typically means that the space of available sockets is exhausted because of too much sockets in wait state (TIME_WAIT, or less probably FIN_WAIT_1 or FIN_WAIT_2)
The range of socket ports can be output by cat /proc/sys/net/...
14
It's to do with Python's object model - there's always a way to get a reference to objects that could be unsafe. See the rexec module documentation and the restricted execution chapter of the docs for some info on the problems, as well as:
https://www.researchgate.net/publication/228612669_Controlling_access_to_resources_within_the_python_interpreter
http://...
13
You can use the EXCLUDED keyword to access the values passed to INSERT. No need to pass them twice:
insert_sql = '''
INSERT INTO {t} (id,col1, col2, col3)
VALUES (%s, %s, NULLIF(%s, 'nan'), NULLIF(%s, 'nan'))
ON CONFLICT (id)
DO UPDATE SET
(col1, col2, col3)
= (EXCLUDED.col1, EXCLUDED.col2, EXCLUDED.col3) ;
...
12
Don't make your user transaction wait for the (hopefully!) successful completion of the Python script. Your entire transaction sits there and waits for this external process to run, try to send mail, etc. I doubt the e-mail really has to go out that instant - especially given you can't control any delays it has as it gets routed to the recipient's inbox ...
12
There are two typical ways to express this.
With LIKE infix search:
SELECT title FROM snippets WHERE description LIKE '%evil%';
or with position:
SELECT title FROM snippets WHERE position('evil' in description) > 0;
Note that neither are indexable by default. Search for "infix search index" for more info on that, and look into pg_trgm if you need it.
10
with PGBouncer, you'd probably want to just stick with NullPool. In that case you may be able to share a single Engine across subprocesses since no socket connections will be carried over the subprocess boundary. But you can't share anything referring to a Connection object, like a Session with an active transaction, over this boundary. You definitely ...
10
I find these multiple closings with age=0 worrying
No, this whole log looks fine and tends to show that pooling is working.
These entries with age=0 being tagged as LOG C, they concern communication between a client and pgBouncer. age=0 just means that the client used the connection for less than 1 second, which is consistent with the dates with ...
10
You can reverse the sort direction to get the minimum instead of the maximum value:
# Sort by myfield (ascending value) and return first document
collection.find_one(sort=[("myfield", 1)])["myfield"]
This example assumes that:
myfield is a numeric value (so the sort order makes sense to determine a minimum or maximum)
myfield exists in the matching ...
10
I performed the same steps and ran into the same problem. I found that the problem was resolved by removing the ~/.pgadmin directory (created during installation) and then re-running "python pgAdmin4.py"
9
Yes, it is possible to work with multiple databases at the same time but you're looking in the wrong place. psycopg2 is just a library that simplifies accessing and manipulating data coming out of PostgreSQL but it doesn't go far beyond what you can do with psql. What you're looking to do you can solve on the database level by using Foreign Data Wrappers.
...
9
psycopg2 is a wrapper around libpq, written in C, to expose a Python DB-API compatible API to Python programs. It implements Python objects in C that call libpq functions. It has a thin Python module wrapper around it to load it and provide some of the interface functionality that's easier to write in pure Python.
Anything that implements the DB-API to ...
7
This is covered in the developer FAQ, some relevant excerpts:
MongoDB stores all field names in every document. For most documents,
this represents a small fraction of the space used by a document;
however, for small documents the field names may represent a
proportionally large amount of space
And, just a note on indexes:
Shorter field names do ...
7
sudo apt autoremove pgadmin4
works for ubuntu version 18.04
6
You might be able to use the service broker, though that is probably overkill, as you state.
Alternately, if you don't want to install/manage an extra service for this one need, you could use xp_cmdshell or a CLR-based trigger to make an external call when needed, to a program that starts the desired process asynchronously (so, outside the transaction that ...
5
The "geospatial feature" of dynamodb isn't really anything else then calculating a geohash from the coordinates, storing the hash in the same row, create an index on the hash, and when querying based on a location, calculate the hash of that location and compare to the indexed hashes in the table. The algorithm of geohash is really simple and with a ...
5
Duplication isn't ideal, but in this case is probably the best choice. Set the table permissions so that the table owner is not the operational day to day database user your app runs as, and only GRANT your app the ability to write to the certificate data column, not the "cache" columns with expiry etc. Have a SECURITY DEFINER trigger function intercept ...
5
I cannot see any possible way for this to work given that the script is being submitted through a back-end service where there is no user-context (i.e. display, etc). SSMS is not executing anything. It is merely submitting those statements to SQL Server which handles the execution (even if SQL Server then passes it off to something else).
The only potential ...
5
Pyodbc won't progress past info messages automatically, and RESTORE generates a lot of them. So you must process them with cursor.nextset() or else you're actually aborting the RESTORE by running cursor.close() before it actually completes.
Eg
cursor.execute(sql)
while cursor.nextset():
pass
cursor.close()
answered Aug 21 '20 at 19:26
David Browne - Microsoft
32.8k3 gold badges33 silver badges73 bronze badges
5
I am able to get identical results between python and T-SQL code with the MD5 algorithm. For example, the NO COLLUSION string hashes to 0x5CA1A58C070F24EF1D4D2900E5727F37 on both platforms.
Example T-SQL code:
SELECT HASHBYTES('MD5', 'NO COLLUSION');
Example Python code:
import hashlib
result = hashlib.md5(b'NO COLLUSION')
print(result.hexdigest())
I'm not ...
5
Joe has correctly pointed out that Python's hashlib.md5 and SQL Server's HASHBYTES('MD5', ...) functions have the same output.
As an additional clarification, the built-in hash() function in Python is not intended to be used in the same way. It's implementation is platform specific, varies depending on the type of object being used, and is (as you mentioned)...
5
my other colleague categorically stated that using CLR here would be a huge risk to security and stability
Well, your colleague is categorically wrong (unless they can offer up actual proof of such claims).
Security
Ever since SQLCLR was introduced in SQL Server 2005 people have been saying that it is "unsafe". However, I have yet to see anyone ...
4
The package to install is postgresql-plpython3.
By way of dependencies, this will install postgresql-plpython3-9.1, which provides these files:
$ dpkg -L postgresql-plpython3-9.1
/.
/usr
/usr/share
/usr/share/doc
/usr/share/doc/postgresql-plpython3-9.1
/usr/share/doc/postgresql-plpython3-9.1/copyright
/usr/share/postgresql
/usr/share/postgresql/9.1
/usr/...
4
Set the Application Name
If you expect to be running many processes you need to know where they are
connecting from. PGBouncer will make this invisible to pg_stat_activity. Solve
this by carefully setting the application_name with the information you'll
need:
# Sets the application name for this connection in the form of
# application-name:user@host
prog ...
4
I did some experimentation in console trying to figure out why this was happening for me as well. In the end, I searched right into the file folder for the package looking to ensure that the pooling file was present in the mysql.connector package version I had.
Sure enough there it was.
In the end, I simply changed my import statement. I had been using
...
4
I have some theories as to why the performance is so bad.
Lets see.
The first is that I simply have too many rows.
Probably not. There is no limit on the number of rows in a (SQL) DBMS. 80 million rows is not that much these days.
The second is that a database is not suited for this task.
Unlikely. We don't know which DBMS you use (Postgres? ...
4
Use the following parameter to set the number of maximum connections from your pymongo app.
client = MongoClient(host, port, maxPoolSize=10)
Here is documentation with more details.
4
I tried to fix this by submitting a patch #234 (approved but not yet committed). You'll have to edit the file ~/.local/bin/mssql-cli and replace
python -m mssqlcli.main "$@"
With this line
( command -v python3 && python3 -m mssqlcli.main "$@" ) || python -m mssqlcli.main "$@"
3
The fact that MySQL locks behave this way by default seems perverse to me, but that's not the point.
Actually, that's totally the point, because locks from SELECT statements is something MySQL doesn't ordinarily do... so by some as-yet-unknown mechanism, you've asked it to do that.
The most likely explanation is that you (or whatever you are using as a ...
3
A few patterns are evident in your data:
The primary activity is updates.
Updates come for several seconds after query/inserts.
The criteria fields in finds is indexed.
Even so, the 'index miss' rate is very high.
Background flush is very low (as it should be with SSD) so you're not getting I/O contention.
Lock-average is extremely high.
Based on this, I'm ...
Only top voted, non community-wiki answers of a minimum length are eligible
