Category Archives: 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

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)

SQL Server 2016 SP1 Edition Limit Changes

width=724
Microsoft made the following changes in their documentation (see screenshot above) to accurately reflect the memory limits on lower editions of SQL Server.

Key Points

  • The limits for In-Memory OLTP data is per database.
  • The limits for Columnstore segment cache is per SQL Server instance across all the databases in the instance.

Example Scenario

A Standard Edition of SQL Server has buffer pool memory limited to 128GB, so the data and index pages cached in buffer pool is limited by 128GB. Starting with SQL Server 2016 SP1, you can have an additional 32GB of memory for Columnstore segment cache per instance and an additional 32GB of memory quota for In-Memory OLTP per database. In addition, there can be memory consumed by other memory consumers in SQL Server which will be limited by “max server memory” or total memory on the server if max server memory is uncapped.

Change WordPress Password using MySQL

If the Forgot password option doesn’t work for you (which it really should), then you can change the password of your self-hosted WordPress login.

Check the contents of wp-config.php for the database login. Use that login information for MySQL login.

define('DB_NAME', 'wpdatabase');
define('DB_USER', 'wpdbuser');
define('DB_PASSWORD', 'wpdbpassword');

Replacing DB_USER and DB_NAME from that command with the values from wp-config.php, enter the following command to connect to the database on your server.

mysql -uDB_USER -p DB_NAME

Get a list of users in the WordPress database.

select user_login from wp_users;

This might return multiple entries, but it was just one in my case: admin
To change the password, issue the following. Be sure to change ‘admin’ to the value of your user_login.

update wp_users set user_pass=md5('NEW_PASSWORD_HERE') where user_login='admin';

All done. Now you can log into your WordPress administration panel with the new password you specified.

Calculate Date Differences in MySQL

There is a function in MySQL that will calculate the number of days between two given dates:  datediff

Example

mysql> select datediff('2014-06-09','2014-07-07');
+----------------------------------------------+
| datediff('2014-06-09','2014-07-07') |
+----------------------------------------------+
| -28 |
+----------------------------------------------+
1 row in set (0.00 sec)

Calculate Days Between Now and Specific Date

mysql> select datediff(now(),'2014-07-01');
+------------------------------+
| datediff(now(),'2014-07-01') |
+------------------------------+
|                            6 |
+------------------------------+
1 row in set (0.00 sec)

MSSQL Find Last Date & Time Database Table Accessed

This is a T-SQL that will show all the last access date and time for Select, tables in a database.

select
t.name
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
,last_user_update
from
sys.dm_db_index_usage_stats i JOIN
sys.tables t ON (t.object_id = i.object_id)
where
database_id = db_id()

Source: SQL Authority
Additional Information/Links:  SQL Blog
 

MSSQL Server Uptime Using T-SQL

A sample T-SQL for determining uptime of a Microsoft SQL Server.

USE master
SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'
IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
PRINT 'SQL Server is running but SQL Server Agent < > running'
END
ELSE BEGIN
PRINT 'SQL Server and SQL Server Agent both are running'
END

Example Output:

SQL Server "MSSQL01" is Online for the past 1438 hours & 7 minutes
SQL Server and SQL Server Agent both are running

Source: MSDN Social