exocad database problem - multiple licenses

tehnik

tehnik

Active Member
Full Member
Messages
520
Reaction score
12
Hi

I have used Qnap NAS server for storing exocad database and sharing it between 4 computers. As it got slower I recently upgraded the NAS to another QNAP with 2.5gb speed and tried to use SSD cache acceleration, but this did not work as it just eventually locked up the sqlite database file. After disabling the caching I got the server to work but recently I keep having problems (now daily) where nobody can access the database. I can open the folder etc. but exocad is unable to open database. I have to restart the server in order to get it to work. Has anybody any idea how can I solve this issue?
 
J

juldan

Member
Messages
3
Reaction score
0
Usaully issue is on CAD files, when they grow up and need to be pushed to computers.
If you notice the problem was sqlite - try to change it to MSSQL Express Edition.
Is quick and shared untill 15 users @same time
 
tehnik

tehnik

Active Member
Full Member
Messages
520
Reaction score
12
How is it possible to change the sqlite file? Because it seems the database file is the problem.
 
J

Jussi Roivanen

Member
Full Member
Messages
63
Reaction score
9
"SQLite Is Embedded, Not Client-Server. There is no separate server process that manages the database. An application interacts with the database engine using function calls, not by sending messages to a separate process or thread".

SQL server is used to handle databases/table, to ensure integrity, in multi-user environment - each instance communicates with server, and server is responsible of modifying database files. If there is no server that handles databases, it means that each instance using database files (on NAS) will __directly__ read/alter database and files. The biggest issue with sqlite over network is that database/table file is locked when one instance is searching or is performing something: other instances cannot access that file. Lock will be released when once instance finishes: when lock is released, another instance locks file and does its thing. And file locking is something that CANNOT be avoided using sqlite. And it may cause situation where database is constantly locked.

A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network

I think you are using sqlite in a way which it is not desiged for, and that causes your problems.

Way to migrate to sql express (or other sql server) is to export SQL formatted data from sqlite database using sqlite3 command line tool (command is something like: sqlite3 databasename.db ".dump" > databasename.sql),then import that .sql into new server and configure exo to use that server. Disclaimer: im not completely familiar with details of SQL syntaxes on various servers - but basically SQL is generic: this works when exportin/importing websites from sqlite to mysql (or maria db),but sqlite to sql express is not something that im familiar with.

I would also advice do not try fiddling with gdrive/onedrive/dobx to sync that database files to multiple computers.. it actually opens a lot more worse "can of worms" than simple file locking.
 
tehnik

tehnik

Active Member
Full Member
Messages
520
Reaction score
12
Ok, I asked the solution from my reseller, so far there is silence... I don't think that I can change the database format to sql. The server part seems to be built in the exocad.
 
Last edited:
S

sirmorty

Active Member
Full Member
Messages
616
Reaction score
37
Have you talked to an IT company?

I think they know more about getting software to work the way you want than any reseller of exocad.
 
J

Jussi Roivanen

Member
Full Member
Messages
63
Reaction score
9
Exocads DentalDB is not a database software as such. As far as i know DentalDB is set of functionalities, which include saving files to given location in harddrive (or network drive),but it also formats and passes SQL queries (or in common term, instructions) - these queries are passed to external piece of software, like sqlite (which has its limitations) or sql server which then in turn does things with a database. DentalDB does not manage database, sqlite (or sql server software) does it.

In exocad (like in 3shape) scanned files are not actually part of a database: database contains various bits of information, like patient name etc.. and it contains reference to location of saved files - or reference to file itself. This means that database (sqlite3 or sql server) and files can be on separate machines. This means that Exocad can be changed (migrated) to run on sql server instead of sqlite, but it requires expertese. IT company knows how to do it. Good starting point is: https://wiki.exocad.com/wiki/index.php/DentalDB_Migrating_SQLite

I must point out, that migrating Exocad to use sql server (weather that is mariadb/mysql/mssql server) might mean a scenario where an sql server is operating on a computer (windows or linux) and scanned files are stored in NAS if your NAS does not have compatible sql server option available (edit: as tehnik pointed out, mariadb is available on some nas's) . Other option is that files are saved on same computer that sql server is running on and other computers access that one computer - NAS then becomes just a backup storage.
 
Last edited:
J

juldan

Member
Messages
3
Reaction score
0
If you want to use MSSQL is not a rocket science :)

1 - Download Express ( free )

2 - Install it in computer
3 - Download SQL Manager Editor to track Database ( also free from Microsoft )

4 - when was in installation - use mixed mode and put a SQL User sa
4.1 - then update youre DB config with SQL username and password
 
J

Jussi Roivanen

Member
Full Member
Messages
63
Reaction score
9
Qnap has something as I see.
Dang! my memory served me badly. At one point MariaDB was not fully supported on ARM cpus (which most nas's use),but.. apparently it is!

Just make sure that MariaDB version is what Exo migrating instructions mention (10.6.x series) and give it a go :)
 
J

Jussi Roivanen

Member
Full Member
Messages
63
Reaction score
9
yep.

NAS might provide some functionality to control MariaDB databases, like phpmyadmin. that usually allows you to create "database schema", create database users and change privelages. Usually it can be used to import data as well.

if there is no database tools in NAS, there are windows software to do necessery tricks - like DBeaver (dbeaver.io). DBeaver can also access sqlite databases.
 
tehnik

tehnik

Active Member
Full Member
Messages
520
Reaction score
12
As I thought, exocad reseller does not provide the database migration although exocad pointed that it is resellers job.

I don't see any database tools on the NAS. Do I understand correctly that the DBeaver has to be installed on every computer, that I want to connect?
 
J

Jussi Roivanen

Member
Full Member
Messages
63
Reaction score
9
As I thought, exocad reseller does not provide the database migration although exocad pointed that it is resellers job.

I don't see any database tools on the NAS. Do I understand correctly that the DBeaver has to be installed on every computer, that I want to connect?

Generally explaining how to do migration forum is possible, but explanations are just guidelines. Explaining how to use DBeaver to create database, user and setting privileges is something you have to learn, because giving detailed instructions are seriously time consiming.. so its up to you to learn how to use dbeaver and how to actually do things - which i know is also time consuming.

Now, if you have time and will to learn things, its possible - but since migration is done usually once, i would say it is not really worthwhile to learn to do this. In this thread we have suggested that you find a local IT guy, who knows what to do - this might cost few usd/eur/other currency but it is best way to do this - it saves a lot gray hairs and time 😅

However, if you want to try it yourself, here goes.

mariadb is server, to which dbeaver - or exocad - connects to. dbeaver is needed to do few phases in instruction, and it can be used to migrate data from sqilte to mariadb (as an alternative to tools mentioned in exocads migration instructions introduction segment). DBeaver can be installed on one computer: it is not needed once everything is up and running.

- install dbeaver on one computer. connect dbeaver to your mariadb server (server name is nas’ ip-address). login as "root" (without quotes, password might be empty or then pw was set when you enabled mariadb in nas).

- once connected, use dbbeaver to create database schema and user as instructed in exocad migration guide (phase no. 2). remember to give new user all privileges to database schema you created - and this username/pw combination is what all exocad instances must be configured to use once everything is done. It is good practice to reconnect dbeaver to mariadb with created username/pw to verify that database schema and user is creater correctly. Note: root user has all access all the time, so in theory that can be used as login which is used in exocad - however i dont not recommend this.

- configure exocad on one computer (for starters) as instructed in migration instructions, phases 3 and 4: server name can be ip-address of your nas, database name and user name (and pw) are which you created with dbeaver

- run /createdatabase as in exocads instructions: this will create database structures to which enables exocad to store information to maridb database instead of sqlite.

at this moment exocad should work one computer, but there will be no orders because database (in mariadb) is empty. scans are still safe, but patient information etc is not there. Next thing is to copy data from sqlite to mariadb.. you can either use tools mentioned in instruction (introduction) or then you can use dbeaver - like this

- use dbeaver to connect to sqlite database (dbeaver supports multiple connections so mariadb connection can remain open): export data rows (not table structures) on all tables and save it to a file - export is basically set of sql statements that insert data into databse (a text file)

- open mariadb connection and use username/pw you created. then open saved file or open in notepad and copypaste contents to dbeaver window that is labeled something like "sql query".. and execute those queries.

if all goes well, exocad can be started and it SHOULD find all orders (and scans as well). when all is good and working, configure exocad/dentaldb on other computers to use mariadb as well.
 
Last edited:
Car 54

Car 54

Well-Known Member
Donator
Full Member
Messages
8,017
Reaction score
1,122
This has been an interesting thread, the titans of PC/Exocad knowledge going back and forth :)

My probable ignorant thought is, with all that has to be done, what if one of the chains in the link breaks, how much of a headache would it be to restore files, file paths, 3rd party software, etc. that in the future could possibly be compromised or corrupted?

Is there an easier solution like getting a different brand of a NAS with differing caching, and handling abilities? Even if you reply to this, I probably wouldn't understand or follow what you're saying, but there has to be an easier way than from where this thread is going, no?
 
Last edited:
J

Jussi Roivanen

Member
Full Member
Messages
63
Reaction score
9
Making backup copy of dentaldb files (entire dentaldb folder perhaps, but basically sqlite database files are sufficent) before doing anything is "i hope i did not have to mention this" -level thing. Backup, Backup, Backup. In migrating instructions there is location and filename which to back up!

Like always, these are rather generic ideas - so basically "a theory". I know migration is possible, because these things are done with webservices that use databases to store data: database can be changed (eg. from sqlite to mariadb or to microsoft sql server) without loss/changing data - then its all about configuring webservice (or specific part of it) to fetch data from new database. exocad in this sense does not differ. So, File paths should not be issue, since files (associated to order) that include paths or other information should not change: data in database remains same, exocad just use different "broker" to get that data

There can always be "hickups". This is why IT Tech is more than recommended. a person just using exocad cannot be expected to know (or think) how to deal if something unexpcted happens.

but..

One mistake might be is to run "dentaldb /createdatabase" when exo is configured to use its default database instead of mariadb. i dont know about exos design philosophy, but i would assume that there is some failsafe on that command so that Exo does should not overwrite existing database (before confirming action at least). but i dont know really, i have never tried such thing. And if this thing would happen, full backup can be restored.

There is also a small thing with that "dentaldb /createdatabase" command, and what it actually does. Since it is part of "migration" instructions and used in context of transferring database (export data from one, then import data to another) so my assumption is, that it only creates tables to database (database consists of tables - like excel file has multiple worksheets: worksheet is like a table, and excel file is like a database) and it does not add/modify any data. It just creates some tables (and sets types what is stored in each table). But then, i could be wrong: this is why IT guy would earn his pay, he could set up test environment and try it out - and if something gets fkd, its not going to affect database that is in use. And if full backup of exocad and DentalDB exists, one should be able to restore old setting if things happen go south.

Its also to possible to make a mistake and import data to that sqlite database (same place from where data is exported) instead mariadb database.. and that will mess up patient data etc, but not scan files. And If backup exists, then backup can just be copied over a mess.

3rd party applications are 3rd party applications: no one knows, usually they are good but sometimes they can cut corners - one scenario would be that 3rd party app would try to use default database, neglecting dentaldb settings and its functionality - it would render 3rd party app unusable. 3rd party program could have its of configuration, which is set by default so that it causes conflicts between dentaldb configuration, thus creating possible troubles when patient data or designs are saved..

As for backing up: mariadb files are essentially like any files, they can be copied but... it does not always work like "charm". Better way to backup sql server (mariadb, mysql, ms sql, sql express) database is to connect to database with external program (like dbeaver) and export database (with table sturctures and data, which can be selected when exporting) - it creates a text file with instructions (sql queries) which create tables (would do what dentaldb/createdatabase presumably does) and insert data. Or then one can schedule mariadb server to make something called "dump", which is basically a file which contains same information that can be exported with external program like dbeaver. "dump" is done without dbeaver or external programs and that "dump" can then be restored with external program.

Caching is different animal: file is send to NAS, which stores it to its memory temporarily and then it is written to harddisc as is, as soon as writing becomes possible, it still locks files.
 
Last edited:
tehnik

tehnik

Active Member
Full Member
Messages
520
Reaction score
12
I was told by another exocad reseller to get synology NAS, as it is simpler to configure it. I am now giving a try with my QNAP by using MariaDB and phpMyAdmin.
 
J

Jussi Roivanen

Member
Full Member
Messages
63
Reaction score
9
i dont think new NAS is needed. QNAP (operating system is called QTS) and Synology (operating system is called DSM) are actually both linuxes, it is the control tools that look different: usually one is good at some thing, other good at other. I think its just about finding out how/what to do things with QNAP.

Setting up MariaDB requires same things weather its pc-linux, pc-windows, mac-mac, mac-linux, nas-linux, nas-windows (yes, these exist too and they use some seriously stripped down windows server edition and im not sure how configurable those actually are): enable/install, make initial setup to set root password etc and use root access to create databases, users and set privileges. then you can export data from existing source (exocad and its sqlite database) and import it to mariadb. One can set up and create database/users can even with NAS's command line (command prompt/terminal) but it requires a bit more knowledge.

And apparently QNAP does not, by default, configure MariaDB so that it would allow root access from remote computers!

https://www.qnap.com/it-it/how-to/faq/article/how-to-access-mariadb-remotely and section MariaDB 10 does describe how to access mariadb remotely. Document also gives instructions how to grant user root to have full access from remote computers: this has to be done so that root can login from remote computer using external program.

Your way of using phpmyadmin will allow you to create database and users, since phpmyadmin is installed on NAS and it connects mariadb locally (hence, notion root not having access from remote computer does not apply). Then its just matter of creating database and give new user privelages (to access that database remotely) Instructions how to give user privileges to access database remotely (with phpmyadmin) can be found at https://www.ovipanel.in/tutorials/d...-connection-to-a-mysql-database-on-phpmyadmin. NOTE: in these instructions they use MySQL as database server: MariaDB is derived from it, and instructions how to change privelages is applied in same way.
 
M

MizzouMade

New Member
Messages
2
Reaction score
0
Pulling my hair out over this. Cannot get exocad to connect to mysql or mariadb. Just keep getting connection must be valid and open error. I can connect to the database in DBeaver no problem and I have full privliges, but exocad refuses to connect. Any advice?
 
J

Jussi Roivanen

Member
Full Member
Messages
63
Reaction score
9
Pulling my hair out over this. Cannot get exocad to connect to mysql or mariadb. Just keep getting connection must be valid and open error. I can connect to the database in DBeaver no problem and I have full privliges, but exocad refuses to connect. Any advice?

- Check Exocad settings, make sure you have settings-db.xml in DentalDB\Config folder and it has required lines. Naturally you must replace Server = something here with IP address of or name of your server (id use ip address),change Database = to match database schema you have created with dbeaver, and use creditentials assigned to that database (if you are using different user than root/admin).

<SQLSetting>
<ConnectionString>Server=localhost;Database=dentaldb;User ID=test;Password=somepass</ConnectionString> <SQLDialect>NHibernate.Dialect.MySQLDialect</SQLDialect> <DriverClass>NHibernate.Driver.MySqlDataDriver</DriverClass> </SQLSettings>

- Check firewall settings: Computer that runs Exocad might have a rules which prevents (blocks) Exocad from accessing SQL server. For exocad allow outbound TCP traffic to port 3306 (default for mariadb/mysql).

- if you have created database and a new user that uses that database, check that User has privilages to connect using network: in mysql/mariadb Accounts are specified as "user@access_from" format. eg user@localhost will only allow user to access service from localhost. "[email protected]" would only allow user to access from specific ip. user@% will allow user to access service from anywhere.
 
Last edited:

Similar threads

Top Bottom