Category: Databases

ManageEngine ServiceDeskPlus MSP: Change Starting Request ID (Postgres)

The procedure to change the request ID for ServiceDeskPlus MSP product (v10.5 and later) is outlined below.

Navigate to the location of ServiceDeskPlus MSP installation and change into the pgsql\bin directory in a command prompt.

C:\ManageEngine\ServiceDeskPlus-MSP\pgsql\bin> psql -U sdpadmin -p 65432 -d servicedesk -h 127.0.0.1

Enter the password sdp@123

Queries for the respective modules:

REQUEST

insert into workorder(workorderid,requesterid,createdtime,respondedtime,duebytime,completedtime,timespentonreq,isparent,is_catalog_template,fr_duetime,haschange,hascausedbychange,hasproblem,surveystatus,hasdraft,resolvedtime) values (3000,1,0,0,0,0,0,true,false,0,false,false,false,0,false,0);

PROBLEM

insert into problem (problemid,title,reportedtime,updatedtime,duebytime,closedtime,isread,notespresent) values(3000,’title’,0,0,0,0,false,false);

CHANGE

insert into changedetails (changeid,title,createdtime,scheduledstarttime,scheduledendtime,completedtime,notespresent) values(3000,’title’,0,0,0,0,false);

Use \q to exit postgresql commandline and restart the ServiceDeskPlus MSP service. On Windows this is servicedeskmsp.

Try creating a new ticket to check the ID series.

ServiceDesk Plus MSP 10.5 Postgre SQL password

The latest release of ManageEngine ServiceDesk Plus MSP changes the default user and password of the Postgres SQL database.

Previously, the command to connect to the database was:

psql -U postgres -p 65432 -d servicedesk -h 127.0.0.1

The new change shows that there is a change in username and password as follows:

psql -U sdpadmin -p 65432 -d servicedesk -h 127.0.0.1

Username: sdpadmin

Password: sdp@123

ServiceDesk Plus MSP build 14620 and above

The default PostgreSQL database password will now be auto-generated for sdpadmin.

The auto-generated password can be retrieved  by invoking the following script:

  1. Windows: decryptPostgresPassword.bat
  2. Linux: decryptPostgresPassword.sh

When and how will the password be auto-generated?

  1. For existing setups, the password will be auto-generated during migration only if the default password was not updated previously.
  2. For fresh setups, the default password will be auto-generated when the server is started for the first time.
  3. For setups that switch to PostgreSQL database after migrating to 14620 or later, the database password will be auto-generated while executing changeDBServer.bat/.sh script.

The PostgreSQL password for sdpadmin cannot be retrieved after the database configuration is updated from bundled Postgres to external Postgres/MSSQL. Therefore, ensure the password is retrieved and stored in a secure location for future use. If the sdpadmin password is reset to the default password, the application will fail to start.

WordPress SQLite to MySQL Migration Complete

Just finished migrating my website from SQLite to MySQL. What a rush. (lol)

It was actually not as bad as I thought. A lot of sed, grep and other sorcery involved; especially in transforming of SQLite statements to MySQL.

Some quick commands I used:

sqlite techish.db .dump > production_2018-08-23.dump.sql

I found that it used quotes for tables and column names, so I had to remove those first and foremost.

sed -i '/INSERT INTO/,/VALUES (/s/"//g' production_2018-08-23.dump.sql

Next I found that there was an error using mysql -ufoo -p mynewdatabase < production_2018-08-23.dump.sql because the table creations were failing still. So I did a quick fresh install of a vanilla WordPress install, did a dump of the database and just grabbed the table creation parts out:

Dump fresh database:

mysqldump -ufoo -p wordpres > wordpress.sql

Next, I just want table creations…

awk '/CREATE TABLE/, /) ENGINE/' wordpress.sql > create_tables.sql

Next, run create_tables.sql on my new database and then import data.

mysql -ufoo -p mynewdatabase < create_tables.sql

Sweet, that worked and I have a baseline of tables now.

Now importing the data…

400 Million Records in MySQL

So I’m trying to figure out a way to make searching a VARCHAR in MySQL fast when there are 400 million rows.

I tried the UNIQUE approach using alter table names add unique(name(15)); in the table but I have some duplicates apparently, so now I’m trying a different method.

I’m going to create multiple tables;  a-z, 0-9.

Based on the input query such as SELECT * FROM name WHERE name='rich' I’ll split that out and re-write the query such as SELECT * FROM r_name WHERE name='rich'.  r_name table is considerably smaller than the entire name table of 400 million rows.  I’ll just say 30 million.

Here’s my bash script to process the main plain text file that I’ll then create LOAD for in MySQL for each split file.

#!/bin/bash
# Split names.txt into a.txt, b.txt, c.txt etc.
for x in {a..z}
do
 echo Scanning $x
 grep -i ^$x names.txt >$x.txt
done
for x in {0..9}
do
 echo Scanning $x
 grep -i ^$x names.txt >$x.txt
done
echo.
echo Done

This is currently processing, so I’ll update when this is finished and see how much more optimized this will be.

Update

Well, crap.  I think I screwed up when I initially parsed out the main names.txt and tried to only grab unique lines.  I should have passed a case insensitive uniq command using uniq -i.

So now I’ll try doing that again and reloading the database.

Update 2

I reloaded the names database from text file.  Doing a create index idx_name on names(name(767)); changed a query execution time of select * from names where name='rich' from 12 minutes to 0.03 seconds.  I’m happy now. And really happy I don’t need to create a bunch of tables and add logic to some PHP/MySQL. Just goes to show I’m green behind the ears when it comes to database technology.

Note: Not quite 400 million after removing duplicates and invalid names from the file 😉

MariaDB [data001]> select max(id) from names;
+-----------+
| max(id)   |
+-----------+
| 321995408 |
+-----------+
1 row in set (0.00 sec)

MariaDB [data001]> select * from names where name='rich';
+----------+-------------+
| id       | name        |
+----------+-------------+
| 86382207 | rich        |
+----------+-------------+
1 row in set (0.00 sec)