Archive

Posts Tagged ‘Database’

HowTo: Install Python, PIP and cx_Oracle on MSYS2 / Windows

Here’s how to install Python, PIP package installer and cx_Oracle extension module on MSYS2:

$ pacman -S python
warning: python-3.8.3-1 is up to date — reinstalling
resolving dependencies…
looking for conflicting packages…

Packages (1) python-3.8.3-1

Total Installed Size:  108.66 MiB
Net Upgrade Size:        0.00 MiB

:: Proceed with installation? [Y/n] y

$ pacman -S cx_Oracle
error: target not found: cx_Oracle

$ pip install cx_Oracle
bash: pip: command not found

$ pacman -S python-pip
resolving dependencies…
looking for conflicting packages…

Packages (2) python-setuptools-47.1.1-1  python-pip-20.1.1-1

Total Download Size:    2.22 MiB
Total Installed Size:  10.91 MiB

:: Proceed with installation? [Y/n] y
:: Retrieving packages…

$ pip install cx_Oracle
Collecting cx_Oracle
  Downloading cx_Oracle-8.0.0.tar.gz (325 kB)
     |████████████████████████████████| 325 kB 693 kB/s
Using legacy setup.py install for cx-Oracle, since package ‘wheel’ is not installed.
Installing collected packages: cx-Oracle
    Running setup.py install for cx-Oracle … error
    ERROR: Command errored out with exit status 1:
     command: /usr/bin/python3.exe -u -c ‘import sys, setuptools, tokenize; sys.argv[0] = ‘"’"’/tmp/pip-install-wqtzv2rv/cx-Oracle/setup.py’"’"’; __file__=’"’"’/tmp/pip-install-wqtzv2rv/cx-Oracle/setup.py’"’"’;f=getattr(tokenize, ‘"’"’open’"’"’, open)(__file__);code=f.read().replace(‘"’"’\r\n’"’"’, ‘"’"’\n’"’"’);f.close();exec(compile(code, __file__, ‘"’"’exec’"’"’))’ install –record /tmp/pip-record-mui2mjs9/install-record.txt –single-version-externally-managed –compile –install-headers /usr/include/python3.8/cx-Oracle
         cwd: /tmp/pip-install-wqtzv2rv/cx-Oracle/
    Complete output (17 lines):
    running install
    running build
    running build_ext
    building ‘cx_Oracle’ extension
    creating build
    creating build/temp.msys-3.1.5-x86_64-3.8
    creating build/temp.msys-3.1.5-x86_64-3.8/src
    creating build/temp.msys-3.1.5-x86_64-3.8/odpi
    creating build/temp.msys-3.1.5-x86_64-3.8/odpi/src
    x86_64-pc-msys-gcc -Wno-unused-result -Wsign-compare -DNDEBUG -g -fwrapv -O3 -Wall -march=x86-64 -mtune=generic -O2 -pipe -march=x86-64 -mtune=generic -O2 -pipe -march=x86-64 -mtune=generic -O2 -pipe -DCXO_BUILD_VERSION=8.0.0 -Iodpi/include -Iodpi/src -I/usr/include/python3.8 -c src/cxoApiType.c -o build/temp.msys-3.1.5-x86_64-3.8/src/cxoApiType.o
    In file included from src/cxoModule.h:14,
                     from src/cxoApiType.c:11:
    /usr/include/python3.8/Python.h:44:10: fatal error: crypt.h: No such file or directory
       44 | #include <crypt.h>
          |          ^~~~~~~~~
    compilation terminated.
    error: command ‘x86_64-pc-msys-gcc’ failed with exit status 1
    —————————————-
ERROR: Command errored out with exit status 1: /usr/bin/python3.exe -u -c ‘import sys, setuptools, tokenize; sys.argv[0] = ‘"’"’/tmp/pip-install-wqtzv2rv/cx-Oracle/setup.py’"’"’; __file__=’"’"’/tmp/pip-install-wqtzv2rv/cx-Oracle/setup.py’"’"’;f=getattr(tokenize, ‘"’"’open’"’"’, open)(__file__);code=f.read().replace(‘"’"’\r\n’"’"’, ‘"’"’\n’"’"’);f.close();exec(compile(code, __file__, ‘"’"’exec’"’"’))’ install –record /tmp/pip-record-mui2mjs9/install-record.txt –single-version-externally-managed –compile –install-headers /usr/include/python3.8/cx-Oracle Check the logs for full command output.

Found a solution here for other Linuxes, mentioning

apt install python-dev clang libcrypt-dev

Via MSYS2 package manager (pacman), libcrypt and libcrypt-devel seem to be available. The second one should fetch the first one if needed, together with header files for development.

$ pacman -S libcrypt-devel
resolving dependencies…
looking for conflicting packages…

Packages (1) libcrypt-devel-2.1-2

Total Download Size:   0.04 MiB
Total Installed Size:  0.04 MiB

:: Proceed with installation? [Y/n] y

Now we can install cx_Oracle succesfully:

$ pip install cx_Oracle
Collecting cx_Oracle
  Using cached cx_Oracle-8.0.0.tar.gz (325 kB)
Using legacy setup.py install for cx-Oracle, since package ‘wheel’ is not installed.
Installing collected packages: cx-Oracle
    Running setup.py install for cx-Oracle … done
Successfully installed cx-Oracle-8.0.0

MySQL DATETIME vs TIMESTAMP and the year 2038

Judging from MySQL documentation at https://dev.mysql.com/doc/refman/8.0/en/datetime.html, it sounds best to use DATETIME rather than TIMESTAMP if you want your database to be future proof. After all 2038 is only 18+ years away.

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

Btw, another issue that sounds problematic, at least conceptually, is that one can use the CURRENT_TIMESTAMP to initialize auto-updated columns with the current datetime (aka NOW()), but since the TIMESTAMP type is supposed to be up to 2038, wonder why the CURRENT_TIMESTAMP would be a difference (aka return a value that would overflow TIMESTAMP columns, but still work fine – aka return the accurate datetime – for DATETIME columns after the year 2038).

https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html

At the very least, I’d avoid mixing those data types in the same database, for consistency reasons.

How-to: get int value via ADO.net SqlDataReader using column name

Based on Sam Holder’s answer at https://stackoverflow.com/questions/7388475/reading-int-values-from-sqldatareader/54296026, just contributed an extension method for fetching Int32 values via ADO.net’s SqlDataReader, without jumping through hoops (aka first fetch column ordinal [number] by name, then fetching the int value passing the column ordinal).

Would be nice if Microsoft was providing such things out of the box.

namespace adonet.extensions
{
  public static class AdonetExt
  {
    public static int GetInt32(this SqlDataReader reader, string columnName)
    {
      return reader.GetInt32(reader.GetOrdinal(columnName));
    }
  }
}

and use it like this

using adonet.extensions;

//…

int farmsize = reader.GetInt32("farmsize");

assuming there is no GetInt32(string) already in SqlDataReader – if there is any, just use some other method name instead

Fix: Windows Update 0x8e5e03fa, 0x800703fa errors

Sometime ago, I was receiving errors 0x8e5e03fa and 0x800703fa on several pending updates at Windows 10’s Update pane (found at Settings / Updates & Security / Windows Update from the Start menu).

Στιγμιότυπο οθόνης (692)

The updates history wasn’t showing many more details, but could see Knowldege Base article numbers (KBxx) for some pending cummulative updates.

Στιγμιότυπο οθόνης (700)

Trying to update some graphics drivers from the Device Manager (can access that by right clicking the Start menu button and selecting “Device Manager” from the popup menu shown on Windows 10), by right-clicking respective devices and selecting to update their drivers, was also failing.

Στιγμιότυπο οθόνης (688)

Στιγμιότυπο οθόνης (691)

So it did look like a systematic issue, not some issue with some specific update item.

Looked up the error code 0x8e5e03fa via Google and found this article mentioning a JET (database engine) error. That’s the same engine used in Access if I remember well, interesting that it’s getting used by Windows Update too (probably to maintain some private database).

Στιγμιότυπο οθόνης (698)

The suggested fix didn’t work since the file mentioned in that article was not existing, but at that folder (%windir%\system32\catroot2) I found a dberr.txt file that obviously was holding some error log.

Στιγμιότυπο οθόνης (694)

Printing out that file (can use TYPE dberr.txt | more to wait after each “page”), I couldn’t help but notice that it was writing JET error all over it.

Στιγμιότυπο οθόνης (693)

I renamed that file (think it was then recreated again automatically) and also renamed the two folders there (using the move command – e.g. can type move, press TAB till the name of the folder appears and then add a minus sign and press TAB again till the same folder name appears and press ENTER). Did that while having the cryptsvc service stopped (using net stop cryptsvc command) as that article suggested. Then started the service again (using net start cryptsvc). 

Στιγμιότυπο οθόνης (696)

After doing this, all failing updates (some extra driver updates had been found using DriverBooster, but were also failing to install) eventually installed fine and Windows 10 started bringing more updates:

Στιγμιότυπο οθόνης (699)

HowTo: Delete all nodes and relationships from Neo4j graph database

At a Neo4j question in http://stackoverflow.com/questions/19624414/delete-node-and-relationships-using-cypher-query-over-rest-api, a recent reply (older ones use obsolete Cypher syntax) says:

Both START and the [r?] syntax are being phased out. It’s also usually not advised to directly use internal ids. Try something like:

match (n{some_field:"some_val"})
optional match (n)-[r]-()
delete n,r

So, to delete all nodes (including disconnected ones) and their relationships you could do:
MATCH (n) OPTIONAL MATCH (n)-[r]-() DELETE n,r
(nice that it works in a single line too)

However since we delete ALL nodes and relationships, this one looks cleaner:
MATCH (n), ()-[r]-() DELETE n,r

 

Update #1

Michael Hunger kindly commented below on this last query:

In your last query you create a huge cross product.
All nodes times all relationships.

Probably cleaner then to split it into two, delete rels first then nodes

Indeed, using PROFILE before the query it seems to do more work from a quick look (hoped it would be a bit more clever to optimize this, but maybe I’m asking too much). So probably should change it to two queries:

MATCH ()-[r]-() DELETE r

and

MATCH (n) DELETE n

 

Update #2

For deleting really big graphs, checkout an answer by Stefan Armbruster on how to delete in an iterative way at

http://stackoverflow.com/questions/29711757/best-way-to-delete-all-nodes-and-relationships-in-cypher/29715865

…the most easy way is to stop Neo4j, drop the data/graph.db folder and restart it.

Deleting a large graph via Cypher will be always slower but still doable if you use a proper transaction size to prevent memory issues (remember transaction are built up in memory first before they get committed). Typically 50-100k atomic operations is a good idea. You can add a limit to your deletion statement to control tx sizes and report back how many nodes have been deleted. Rerun this statement until a value of 0 is returned back:

MATCH (n)
OPTIONAL MATCH (n)-[r]-()
WITH n,r LIMIT 50000
DELETE n,r
RETURN count(n) as deletedNodesCount
%d bloggers like this: