texts: Is there a way to get the contnents of an RPG program using SQL?

texts:
So, I've got myself a query to list our RPG programs:

SELECT OBJNAME
FROM
    (SELECT OBJNAME AS SCHEMA FROM TABLE (QSYS2.OBJECT_STATISTICS('*ALLSIMPLE', 'LIB')) S) AS SCHEMAS
    ,LATERAL (SELECT * FROM TABLE(QSYS2.OBJECT_STATISTICS(SCHEMAS.SCHEMA, 'PGM')) X) AS PROGRAMS
    LEFT JOIN QSYS2.PROCEDURES
        ON QSYS2.PROCEDURES.PROCNAME = PROGRAMS.OBJNAME
        AND QSYS2.PROCEDURES.PROCSCHEMA = PROGRAMS.OBJLONGSCHEMA
WHERE
    PROCNAME IS NULL
    AND SCHEMAS.SCHEMA = 'MYLIB'
;

Now, is there any way, given the name of an RPG program, to get the contents of it via SQL?

(Or, failing SQL, some other way to get the contents from within a C# application?)


texts: How do I access Postgres when I get an error about "/var/run/postgresql/.s.PGSQL.5432"?

texts:
I am running Ubuntu 16.  I have installed Postgresql.  Postgresql used to work, but then I rebooted.

nmap commands show port 5432 is open.  Postgres seems to be working correctly:

service postgresql status

  postgresql.service - PostgreSQL RDBMS    Loaded: loaded
  (/lib/systemd/system/postgresql.service; enabled; vendor preset:
  enabled)    Active: active (exited) since Sat 2017-07-29 18:42:59 EDT;
  1min 4s ago   Process: 201 ExecStart=/bin/true (code=exited,
  status=0/SUCCESS)  Main PID: 201 (code=exited, status=0/SUCCESS)
  Memory: 0B    CGroup: /system.slice/postgresql.service

I ran this: psql

But I got this:

  psql: could not connect to server: No such file or directory
          Is the server running locally and accepting
          connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

The file listed above does not seem to exist.

How do I get into Postgresql?  Normally I'd run psql or sudo -i -u postgres then psql.  But these commands are not working.  I keep getting an error about "could not connect to server."  Several reboots have not helped.

Update:

I ran this command: dpkg -l | grep postgres

rc postgresql-9.5                9.5.6-0ubuntu0.16.04                   amd64        object-relational SQL database, version 9.5 server
ii postgresql-client             9.5+173                                all          front-end programs for PostgreSQL (supported version)
ii postgresql-client-9.5         9.5.7-0ubuntu0.16.04                   amd64        front-end programs for PostgreSQL 9.5
ii postgresql-client-common      173                                    all          manager for multiple PostgreSQL client versions
ii postgresql-common             173                                    all          PostgreSQL database-cluster manager


texts:
What do you do when you have a database that is marked as Suspect?

Restore from the last backup?

Please advise.


texts: Implementating user notifications list using Aerospike

texts:
I need to choose the right DB for a notifications system that needs to handle billions of notifications. The record structure is as follows:
[user_id, item_type, item_id, created_at, other_data]

The inserts are going to be in bulks up to hundreds of thousands at spikes.
And it needs to support thousands of selects per minute of this kind:
select * from user_notifications where user_id=12345 order by created_at limit 10
select * from user_notifications where user_id=12345 and item_type='comment' order by created_at limit 10
--- and for the pagination next page:
select * from user_notifications where user_id=12345 and item_type='comment' and created_at>'2020-11-01 10:50' order by created_at limit 10

It should also allow quick updates and deletes and ideally have TTL on each record.
Right now it's implemented using MySQL, we only have 400M rows and it's already slow as hell. And bulk cleanup is just impossible.
Initially, I thought ScyllaDB/Cassandra is ideal for that. If I set the primary key to be [user_id, item_type, item_id] (user_id being the partition key) for inserts/updates/deletes and [user_id, item_type, created_at] as secondary index. CQL seems straightforward in this case and it should work fast (correct me if I'm wrong). The problem is that we are Ruby-On-Rails based and there's no good ruby client library for that. The one listed in the ScyllaDB clients' list (https://github.com/datastax/ruby-driver) is in maintenance mode and I'm not sure it will be updated with new Ruby versions, etc.
Recently, I heard about Aerospike and their benchmarks look really cool, but I couldn't figure out how to implement the above requirement using Aerospike's architecture. Especially as their secondary index seems to be always in memory which makes it impossible to index billions of rows.
This notifications schema seems to me like something very common, but still, I couldn't find a good article describing all the ideal ways to implement it.
Any suggestions are welcome.
Thanks


texts: Recovering mysql innodb table from table.ibd backup file

texts:
Just practising DB disaster recovery. As per our back up policy, we do complete disk image back ups every half an hour (on our slave server).

Now for a scenario, where some how a table has been accidentally dropped, I thought recovery would be as simple as -

copy .ibd and .frm file from backup to mysql data dir.
alter table tbl_name import tablespace;

On issuing a select, DB throws an error - table does not exist. I understand this might have something to do with ib_logfile?

Any ideas how to go about restoring table in this situation?


texts:
I created a trigger in table tbl_user_order.

and I want to  insert data in retailer_deliver_customer table.
CODE:-

create or replace trigger insert_record
after insert on tbl_user_order
for each row
BEGIN
insert into retailer_deliver_customer
(
prod_name,product_quantity,message,delivery_date,delivery_address,pin_code
)
select
  :new.prod_name,
  :new.prod_quantity,
  :new.meessage,
  :new.delivery_date,
  :new.delivery_address,
  :new.pin_id
 from tbl_user_order where pin_id=:new.pin_id;
commit;
end insert_record;
/

I got mutating error once.

If I pass any value in :new.pin_id then it inserted  and if we declare variable for it then get error.


texts:
I am trying to figure out pt-table-checksum, as I am using it for the first time.
It look likes the documentation is complex and not easy to understand stuff.

I have a complex replication topology setup: I have master-master active topology setup geographically separated and both syncing each other. Further a number of slaves under each master. Also, some of the slaves are using filter table replication under both masters.

The database I am using is around 200GB in size, and slaves are balancing reads. I need to make sure pt-table-checksum will not break replication and will not cause any loads on server which hurts app usage.

My requirements are

Connect on master1 and check master2 and all slaves under both masters and inform for any deficiences in between master slaves data.
Is it possible to check only specific tables' specific slaves ? e.g. check from master1 and all underlying slaves of master1 but do not touch master2 and its underlying slaves?
How to take care of replication filters ? e.g. master1 has slaves slave1 slave2 where slave2 replicating only some of the database tables while slave1 replicates the entire database. I need pt-table-checksum to check for differences on slave1 for entire database and check only tables that are replicating on slave2.

I am running pt-table-checksum (2.0.1) on test machines on an employees database. I inserted some records in employees table with sql_log_bin off to prevent them from replicating so I can test pt-table-checksum to report differences and sync on slave.

Created user on master and on slave with SUPER, REPLICATION SLAVE privilege. The user is testuser with testpass being the password

master$ ./pt-table-checksum --replicate=test.checksum --create-replicate-table --nocheck-replication-filters --databases=employees localhost
Cannot connect to h=slave-ip

            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
07-09T13:48:38      0      0        9       1       0   0.082 employees.departments
07-09T13:48:39      0      0   331603       7       0   1.001 employees.dept_emp
07-09T13:48:39      0      0       24       1       0   0.040 employees.dept_manager
07-09T13:48:39      0      0   300028       1       0   0.577 employees.employees
07-09T13:48:48      0      0  2844047      13       0   8.006 employees.salaries
07-09T13:48:49      0      0   443308       1       0   0.989 employees.titles

You can see employees table has some extra records. Secondly I executed the following command.

master$ ./pt-table-checksum --replicate=test.checksum --replicate-check-only --databases=employees --host=slave-ip --user=testuser  --password=testpass

Command executed without any slave. Nothing displayed. When I checked on slave employees count is still old and not updated as master.


texts: SQL Server 2012 Standard Edition using more than 64GB of memory, why and how?

texts:
I put SQL Server 2012 Standard on a virtual machine (VMWare) which has roughly 98GB of Memory.

Now I checked the query below how much memory is taken by SQL Server.

Microsoft SQL Server 2012 - 11.0.5058.0 (X64)

And see that he ate already 81GB of the memory and the max Memory is set to ~90GB.

Using:

select (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 ) VAS from sys. dm_os_process_memory

SQL Server 2012 Standard should support only 64GB of memory.

Why does my SQL Server eat more than he can? Or is the info incorrect?

Any idea? Is that related to a virtual machine using virtual memory?

I don't know if balloon driver is activated on VM Host...

If you need any more info I will add it.


texts: My sql select query during long updates on the same table. Junk or old data?

texts: What happens when select query happens simultaneously during long updates. Will I be seeing a junk data or the old data before update.


texts:
I am currently using

  SELECT FORMAT (getdate(), 'MM/dd/yyyy, hh:mm:ss tt') as date

The output is in the format I need, but is off by 6 hours.

  05/30/2019, 06:26:01 PM

I just need to add 6 hours to it. been using dateadd, but I keep loosing the format I need it to be on.

  SELECT DATEADD(Hours,6,GETDATE()) outputs in wrong format


texts: MYSQL REPLACE string content with field value

texts:
How can I REPLACE a string value with a other field content?

SELECT REPLACE(html_input,'!value',field_value) FROM table

Value of html_input is <input type='text' value='!value'>

Value of field_value is test

Expected result:

<input type='text' value='test'>


texts:
Our databases files  (data and log)  of (a lot of) SQL Servers are hosted on flash storage (SAN), what would you use as auto-grow property for databases having size round 500GB (data) and 150Gb (log)?
Knowing in the background that you cannot size 5 years ahead because of storage price.
Subsidiary question:
Is it still accurate to take care of file fragmentation on SSD SAN volumes?


texts: Why is there no data loss with mysql innodb_flush_log_at_trx_commit set to 0?

texts:
Here is the MySQL info:

mysql> show variables like 'innodb%flush%log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout    | 2700  |
| innodb_flush_log_at_trx_commit | 0     |
+--------------------------------+-------+
2 rows in set (0.00 sec)

mysql> select version()
    -> ;
+-----------+
| version() |
+-----------+
| 5.6.14    |
+-----------+
1 row in set (0.00 sec)

Then I insert something:

mysql> insert into x select now();
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from x;
+---------------------+
| now()               |
+---------------------+
| 2014-08-20 18:59:09 |
+---------------------+
1 row in set (0.00 sec)

mysql>

I immediately kill mysqld to make it crash:

:/data/mysql_dbf # killall -9 mysqld
:/data/mysql_dbf # /usr/local/bin/mysqld_safe: line 166: 28859 Killed                  nohup /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql_dbf --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql_dbf/.err --pid-file=/data/mysql_dbf/.pid < /dev/null >> /data/mysql_dbf/.err 2>&1 >> /data/mysql_dbf/.err 2>&1 >> /data/mysql_dbf/.err 2>&1 >> /data/mysql_dbf/.err 2>&1 >> /data/mysql_dbf/.err 2>&1 >> /data/mysql_dbf/.err 2>&1 >> /data/mysql_dbf/.err 2>&1
140820 18:59:22 mysqld_safe Number of processes running now: 0
140820 18:59:22 mysqld_safe mysqld restarted

Then I check it again:

mysql> select * from x;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select * from x;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

+---------------------+
| now()               |
+---------------------+
| 2014-08-20 18:59:09 |
+---------------------+
1 row in set (0.02 sec)

No data was lost. Can someone explain why?

Edit 1

With the help of @jynus, we are going to test for sufficient data.

i=0;
while true;
do ./bin/mysql --defaults-file=my.cnf -umysql -pmysql test -e "insert into x select now()";
    if [ $i -eq 100 ];
    then killall -9 mysqld;
        sleep 3;
    fi;
    ./bin/mysql --defaults-file=my.cnf -umysql -pmysql test -e "select count(*) from x" >> trace.log;
    if [ $i -gt 110 ];
    then break;
    fi;
    ((++i));
done

And the output shows we are facing data loss

......
count(*)
99
count(*)
100
count(*)
101
count(*)
102
count(*)
103
count(*)
3
count(*)
4
count(*)
5
count(*)
6
......


texts: Simultaneous READ COMMITTED transactions updates a select for update columns

texts:
I have a READ COMMITTED transaction in which I am trying to update a row and increment a column value till it reaches a bucket value contained in another column. Please see the code below

CREATE PROCEDURE `testproc`()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    ROLLBACK;
    SIGNAL SQLSTATE 'ERR0R' SET MESSAGE_TEXT = 'An error has occurred, operation rollbacked and the stored procedure was terminated' ;
    END;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

--- SOME LOGIC HERE ---
START TRANSACTION;
IF(SELECT IF(CurrentSize<BucketSize,TRUE,FALSE) FROM TestTable WHERE id = someId)
THEN
--- SOME INSERTIONS HERE ---
IF(SELECT IF(CurrentSize=BucketSize-1,TRUE,FALSE) FROM TestTable WHERE id = someId FOR UPDATE)
THEN
UPDATE TestTable SET CurrentSize = CurrentSize+1 WHERE id = someId;
--- SOME LOGIC HERE ---
ELSE
UPDATE TestTable SET CurrentSize = CurrentSize+1 WHERE id = someId;
END IF;

--- BELOW IS ADDED AS A EXTRA PRECAUTION ---
IF((SELECT COUNT(1) FROM TestTable WHERE CurrentSize > BucketSize AND id = someId) > 0)
THEN
ROLLBACK;
END IF;
END IF;
END

This works fine under normal load. But when we reach a high traffic I don't know for what reason the above logic fails and The CurrentSize column reaches a greater value than BucketSize.

It would be great if someone could help me with the same.


texts: Non-sequential Primary Key performance in PostgreSQL 9.3

texts:
Due to requirement to support distributed environment, each of my clients has been assigned unique node id, it appends the node id with running number as primary key and stores at local database. E.g.

  Client A node id = 200, first row in a table will have primary key
  200,000

  Client B node id = 100, first row in a table will have primary key
  100,000

These records then replicate to centralized database. Since primary key at centralized database is not in sequence, will it cause any serious performance issue when data size getting bigger?

Possible sequence of inserting new data at centralized database:

200,000
100,000
100,001
200,001
300,000
100,002

This may cause a big performance in SQL Server with table is clustered along the PK. However, will this happens in PostgreSQL 9.3?

Notes:

I can't use composite keys as it does not play well at my presentation layers.
The 3 digits running number is just a simplified example, real
running number will be much bigger and sufficient.


texts: Does MySQL support replicating all databases?

texts:
I’ve been doing some investigation into properly configuring MySQL replication.

From what I understand after reading the documentation, you can only replicate specific databases, and each database name must be specified: In the master configuration, in order for the relevant database writes to be written to the binary logs, and in the slave configuration, in order to replay those logs onto the slave.

Is this correct, or is it possible to configure MySQL to replicate any and all databases?


texts:
We recently separated out our tempdb files to a new SSD and have started seeing:

  5348 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [T:\tempdb\tempdb4.ndf] .

We have multiple occurrences of this error. We did not see the errors when tempdb was back on its original RAID 5 home. I followed a tutorial on SQLIO and I think the SSD should be much faster, when doing 8kb random read/writes, than the previous RAID 5 disks.  So why are we seeing these errors?

Also, by way of more proof that not all is well, the batch file we run overnight (which is when these errors occur) is taking 7 hours.  It took 6.25 hours on the old disks.

The disks sit in a directly attached array. The RAID5 for data, RAID 10 for logs and a spare slot that we used for the SSD. The RAID 5 and SSD are formatted for 64kb block size. The log is incorrectly set to 4KB block size (I know – will fix when I get a chance).

These are the results of SQLIO:

T drive (ssd)
Ios= 8KB random writing,IOs/sec=31847.48,MBs/sec=248.8
Ios= 8KB random reading,IOs/sec=76391.66,MBs/sec=596.8

S drive (RAID 5)
Ios= 8KB random writing,IOs/sec=2601.3,MBs/sec=20.32
Ios= 8KB random reading,IOs/sec=3138.45,MBs/sec=24.51

For 64K sequential read/writes they were around the same.

Tempdb is split into 4 1.5Gb files (this is the same before and after the move).

SQL server 2012 is patched to SP3.

Do you have any idea what could be causing all these I/O errors getting reported by SQL Server?

Is it possibly an Array or HBA driver issue? Does a single disk added into a spare slot on an directly attached array needs careful configuration in terms of cache?


texts: MYSQL MyISAM data recovery implementation

texts:
I am creating integration into eshop script, shop is using MyISAM tables and I need to implement something like transactions in InnoDB, what I actually need is full recovery of data after modifying or adding some rows.

Scenario would be like: some PHP script fills several tables with information, there will be about 10 tables affected.  I'm thinking about creating temporary tables and filling them. After whole fill process succeeded, it will put actual rows to permanent tables and if not it will clean temporary tables.

I am wondering if there is easier way to have full recovery on fail? I cannot edit existing table's structure, or change table's engine. Thanks in advance for your time.

Edit: I have made very basic table of reference for simple understading of what I am dealing with. http://creately.com/diagram/h7yqs7qg2/UP3fno4Rzw3DTW3IdDa5uVDZQHE%3D

Real case scenario would be: I am creating order for user, but I cannot be sure that user  creation didn't failed on behalf of creation(let's say created row only on main user table) then when I'll check if user exists for order it will return true. So when I'll start order creation an I'll need it's address but it won't exist. Thus order creation will also fail. That's why there has to be rollback if failed any of data. Data cannot be simply deleted by row id because row could be modified(Invoicing order or change of order status).

Sorry for my bad Englist.


texts:
We have a purestorage san, a couple of VMhosts each serving up several SQL Server VM's. Each VMhost is given a slice of the SAN and each SQL Server VM is presented a single data VMDK and a single log VMDK.

Some SQL Servers have like 50+ databases. Each SQL Server VM is presented 1 single vSCSI controller.

Can adding a second vSCSI and creating another drive improve performance by increasing Queue Depth?


texts: Can MySql cause a Windows server to shutdown unexpectedly?

texts:
For the 3rd time, my server where my MySQL instance is running simply shutdown unexpectedly, this time, causing my tables to be corrupt as I had ALTER statements running.

While in contact with my hosting company, they deny that it is a hardware failure (they have done test that showed no hardware failure) and says that MySQL uses up all resources which causes the server to just shutdown. In my mind, the MySQL instance would shutdown unexpectedly, not the server itself but maybe I'm wrong.

My question is simple. Could MySQL cause the server to shutdown unexpectedly?

There are nothing in the error logs of either the server, or MySQL. Just that the server had shutdown unexpectedly.

Has anyone had this problem where MySQL causes a unexpected server shutdown?

I found that a kernel-power error with event id 41 has been generated all 3 times the error has occured.

EventData

  BugcheckCode 10
  BugcheckParameter1 0xffffffff49bd9dc9
  BugcheckParameter2 0x2
  BugcheckParameter3 0x1
  BugcheckParameter4 0xfffff80005ed80d8
  SleepInProgress false
  PowerButtonTimestamp 0

Luckily, I've managed to find a minidump file, (only for the first time it happened thought) and I've decoded it to this.

  IRQL_NOT_LESS_OR_EQUAL (a) An attempt was made to access a pageable
  (or completely invalid) address at an interrupt request level (IRQL)
  that is too high.  This is usually caused by drivers using improper
  addresses.

Could this be that I've simply used up all available memory or is there maybe something wrong with the memory itself? I've just sent this details to the hosting company and I am awaiting answer but figured I would get some more opinions.


texts: What is the meaning of "sga_max_size= # internally adjusted"

texts:
I used following command to get backup of my spfile.

CREATE PFILE='c:/test_init.ora' FROM MEMORY;

it has following lines related to memory.

memory_max_target=12G
memory_target=8G
__java_pool_size=16M
log_buffer=4743168 # log buffer update
__pga_aggregate_target=6G
_pga_max_size=1258280K
processes=400
sga_max_size=2G # internally adjusted
__sga_target=2G
__shared_io_pool_size=0
__shared_pool_size=704M
_smm_max_size=629140
_smm_min_size=1024
_smm_px_max_size=3145728

I wonder about "# internally adjusted" comment. What does it mean?


texts: What are the possible 'types' available in SQL Server as mentioned in the 'type' column in the sys.master_files view?

texts:
I'm relatively new to SQL Server, and having a problem with an expanding tempdb database. As such, I'm starting to look just more generally into how database files work.

Looking at this query:

USE master;
SELECT *
FROM sys.master_files mf

I understand looking at the Microsoft documentation that the sys.master_files view keeps track of files related to databases. On my system the 'type' column is only either 0 or 1 - i.e. I only have Log and Row files. But the docs mention types 2 (FILESTREAM), 3 (...), and ,4 (Full text).

Questions:

Would .mdf files always be type 0? It seems this is the file that represents a database; I didn't realize that all database objects would be represented by 'Row'-type files
What is the type FILESTREAM?
How are tables differentiated in files of type Rows?

===== EDIT

Question 1 is a little misleading; the answer mentions that .mdf and other SQL Server file extensions are just SQL Server's default file extensions and that these can be changed. Testing this, it looks like data is written to the files in exactly the same way regardless of file extension.


texts: When you select from a table on an indexed column and order by that column in the opposite direction of the index, does the index help

texts: Let's say I have a large table of transactions with a date field and an index on that field in ascending order. If I select that field from the table and order by in reverse order is it able to somehow use that index in reverse, or is it going to have to do a table scan or some other less efficient method of accessing those records and sorting them?


texts: Why am I unable to create this Availability Group? (error 35220)

texts:
I was attempting to create an Availability Group on a SQL Server 2017 instance and was running into error 35220:

  Could not process the operation. Always On Availability Groups replica
  manager is waiting for the host computer to start a Windows Server
  Failover Clustering (WSFC) cluster and join it. Either the local
  computer is not a cluster node, or the local cluster node is not
  online. If the computer is a cluster node, wait for it to join the
  cluster. If the computer is not a cluster node, add the computer to a
  WSFC cluster. Then, retry the operation.

I was stumped, because the host was most definitely part of a WSFC, and the node was online.  I eventually found the answer in a blog that was focused on Multi-Subnet Availability Groups, which I hadn't looked at earlier because mine was not Multi-Subnet.

Thus, I'm posting this here to give better visibility to the solution in case some other poor DBA gets stumped the same way I did.  I will post the solution below.


texts: changing Virtual IP interface names on RAC nodes - how to reconfigure

texts:
We are adding an extra interface on our nodes and we are going to bond these so the VIP interface will change from eth5 to bond3.

Can someone point on documentation on what to do to reconfigure the interface name in a RAC?

Or simply give a procedure to do so.


texts: Clever way to order a json item by key in postgresql (two examples)

texts:
I need to sort a json item in a record, so here are two examples:
First case
create table jsontable (
    jsonitem json
);

Next I insert a json item:
insert into jsontable(jsonitem) values ('{ "3" : "foo", "2" : "bar", "1" : "qux" }');

Then I query the values:
select t.jsonitem from jsontable t;
                 jsonitem
-------------------------------------------
 { "3" : "foo", "2" : "bar", "1" : "qux" }
(1 row)

Let's suppose the key is unique and is an integer > 0;
Question 1: ¿ How to sort the json item values by the key value, and get the following ?
{ "1" : "qux", "2" : "bar", "3" : "foo" }

Second case
This case use named keys and named values.
create table jsontable_arr(
   jsonitem json
   );

insert into jsontable_arr(jsonitem) values ('[  { "key" : "3" , "value": "foo"}, { "key" : "2" , "value": "bar"}, { "key" : "1" , "value": "qux"} ]');

select t.* from jsontable_arr t;
                                                jsonitem
--------------------------------------------------------------------------------------------------------
 [  { "key" : "3" , "value": "foo"}, { "key" : "2" , "value": "bar"}, { "key" : "1" , "value": "qux"} ]
(1 row)

Question 2: ¿ How to sort the json array item by the key value, and get the following?
[  { "key" : "1" , "value": "qux"}, { "key" : "2" , "value": "bar"}, {"key" : "3" , "value": "foo"} ]

Thanks for your suggestions.


texts: Find records size in kb/mb/gb for one client from multiple table in one database

texts: I have multiple client using same database, i need to find client wise size utilized.


texts: How to set SQL Server index pages per fragment?

texts:
I have SQL Server 2008, and a number of databases. I have discovered that one of my table's indexes is extremely fragmented (How I know: http://msdn.microsoft.com/en-us/library/ms189858.aspx)

The avg_fragmentation_in_percent is a whopping 97% or more and the fragment count is in the thousands. Very interestingly, the avg_fragment_size_in_pages is just slightly more than 1. I can defrag it, which helps, but I'd like to prevent this from happening in the first place.

Why is the pages per fragment so low? I have FILEGROWTH = 128MB for the DB as a whole, but this particular table is the most active - so is there a way to tell SQL Server to allocate more growth to this table or index so that the pages-per-fragment is higher?


texts: How to design a partitioning strategy for a VLDB OLTP solution (SQL Server 2016, >100TB init. size)

texts:
I am currently working to design a MSSQL 2016 based platform to handle a dataset (OLTP based) that will grow above the PetaByte level. It will be used for specific types of analysis that will require trends to be discovered using various methodes and tool (incl. R). There will be various sources feeding the database(s) on a 'live' basis as well as batches of data that will be ingested on a batch basis. Due to the high volumes of transactions, that number of concurrent users projected (>250) and the way data will be consumed by the users (later more), we need this solution to be high performant and scalable. It is obvious the data needs to be partitioned on a few levels to support the data consumers.

The users will be running trend analysis type workloads on daily, weekly, monthly and multi year ranges. Most data will be supplied with date fields, but customer name, account numbers and transaction types, are also in scope for doing trend analysis.

My question to you all is as follows, what would your strategy be for designing a proper partitioning solution? What questions would you ask and what would you look for in the answers? How would you handle maintenance on indexes and such.... What would you factor into the design?

Oowww and dropping everything into a datalake (read: swamp) or going for a different platform is not an option. Also, I am not at liberty to discuss the particulars of the project or the data involved so please don't ask. Just know it is highly confidential financial and personal data and we will be doing forensic analysis (using R, PowerBI and/or other BI tooling) in compliance with lawful requirements that have been imposed on us. I will not share any other details beyond this, sorry.


texts: In PostgreSQL, can you read from an index during a ALTER INDEX ... SET TABLESPACE operation?

texts:
I have a PostgreSQL 9.3 server, and there's a large index that I want to move to another tablespace on another disk, because I want to free up space on the original disk. However this is a production system, and the index is useful for making read performance acceptable, so I want the index to be usable as an index while it is being moved from one tablespace to another. Data writes are not a problem, they can be halted, turning the database into essentially a "read-only database".

But I can't find information in the postgres docs about this.

The PostgreSQL 9.3 docs for ALTER INDEX don't mention locks:

  This form changes the index's tablespace to the specified tablespace and moves the data file(s) associated with the index to the new tablespace. See also CREATE TABLESPACE.

The PostgreSQL 9.4 docs for ALTER INDEX mention locks but doesn't say which locks, and only when refering to ALL IN TABLESPACE form.

  This form changes the index's tablespace to the specified tablespace and moves the data file(s) associated with the index to the new tablespace. To change the tablespace of an index, you must own the index and have CREATE privilege on the new tablespace. All indexes in the current database in a tablespace can be moved by using the ALL IN TABLESPACE form, which will lock all indexes to be moved and then move each one. This form also supports OWNED BY, which will only move indexes owned by the roles specified. If the NOWAIT option is specified then the command will fail if it is unable to acquire all of the locks required immediately. Note that system catalogs will not be moved by this command, use ALTER DATABASE or explicit ALTER INDEX invocations instead if desired. See also CREATE TABLESPACE.

So if I run an ALTER INDEX ... SET TABLESPACE ... will I still be able to read from the index while it is being moved?


texts: Multi Select Statement With inner join multiple table

texts:
user table

id   u_id   name   phone       email
1    912    abcd   23424    abcd@email.com
2    913    xyz    45342     xyz@email.com
3    916    pqrt   23239    wdiw@email.com

subject table

s_id   name
    1     eng
    2     math
    3     phy

city table

c_id   name
    1     city1
    2     city2
    3     city3

location table

l_id   name
    1     loc1
    2     loc2
    3     loc3

user_to_subject table

u_id    s_id
913      2
913      3

user_to_city table

u_id    c_id
913      1
913      3

user_to_location table

u_id    l_id
913      2
913      3

What I am trying to do is to select one statement with complete details. This is what I have tried.

SELECT U.*, GROUP_CONCAT(S.`name`) AS subject, GROUP_CONCAT(C.`name`) AS class,
GROUP_CONCAT(L.`name`) AS location
FROM `user` U
  INNER JOIN `user_to_subject` UTS ON U.`u_id`=UTS.`u_id`
  INNER JOIN `subjects` S ON S.`s_id`=UTS.`s_id`
  INNER JOIN `user_to_class` UTC ON U.`u_id`=UTC.`u_id`
  INNER JOIN `city` C ON C.`c_id`=UTC.`c_id`
  INNER JOIN `user_to_location` UTL ON U.`u_id`=UTL.`u_id`
  INNER JOIN `location` L ON L.`l_id`=UTL.`l_id`
WHERE U.`u_id`=913

I got duplicate subjects, cities and location. Not sure what I am doing wrong here.


texts: PostgreSQL determine the optimum number of partial indexes

texts:
I have a table with 5 columns of interest:

geo_x    integer    -- quantized longitude
geo_y    integer    -- quantized latitude
context  integer    -- the type of record
hash     integer    -- a hash of the above 3 columns, modulo divided by n
tags     integer[]  -- GIN indexed

I defined n as 16 and therefore created 16 indexes.

I also created an index that contained all rows (ignoring n).

In my tests, I get an almost 13 times speed-up by having 16 indexes vs 1. Great!

However, this is test data (1 million random entries), which is somewhat meaningless. Nonetheless, it proves smaller indexes provide tremendous speed-up. Close to linear in fact.

My question is, is there a way to calculate an optimum number of partial indexes without going through a tedious trial-and-error process?

I have searched, but can't find any best practices regarding the number of partial indexes.

If there is no "formula" to determine the answer, can anyone share their experiences with multiple partial indexes?


texts:
I am new to PostgreSQL. I am looking for a solution wherein 2 mirror images of a database sit on a local and remote server. All the work is done on the local server and at periodically synced with the remote server. Is there any solution for this issue.

I am using Debian.


texts: SQL Server Service has stopped, but process still appears in TaskMgr

texts:
A customer has the strangest situation.  Windows Server 2016 with SQL Server 2016. There is only one SQL instance on the machine (the default). Service name: MSSQLSERVER.

The service take a very long time to stop (about a minute), but once it is shown as Stopped, the SQLSERVER.EXE process still appears in the TaskMgr for up to another minute. If I try terminating the process from TaskMgr I get error: Access Denied.

This causes problems when restarting SQL Server. This is what happens:

Stop SQL Server and wait for service to Stop (net stop mssqlserver)
Process remains in TaskMgr
Try to start SQL Server again (net start mssqlserver)
SQL Server fails to start because the Master database is currently in use

Has anyone seen anything like this? Why would the process still be running after the Service has stopped?


texts:
I am responsible for backups of normal DBs, but SharePoint is handled by others. Bit worried that we miss something here.

Is SharePoint backup handled completely through some tool in SharePoint, or should I also make regular SQL Backups of the databases outside SharePoint?


texts:
How could I add a Check-costraint for Date type in PostgreSQL?

For example,

 CHECK 'DateXYZ>01/02/98'

A typecast is needed?


texts: mongs failed to findone when collection is under sharding migration

texts:
I have a collection which enabled sharding on 2 replica sets, rs01 and rs02.
I added a new replica set rs03, it started to migrating chunks to rs03 slowly(it only migrated about 300 chunks in 4 days...)

It is weird that I do a find().limit(1) or findOne() on the collection on mongos, it hung there without response.(I need ctrl-c to escape the mongo shell on mongos).

How to check anything wrong that makes the query stuck?
Because the mongo.log on the rs01 is filled with migration actions that I cannot find useful message that make the findOne() query stuck.

Moreover, I tried findOne() on the sharded collection on rs01, rs02, rs03 ,all can return document. Only hung on mongos.

Below is message from mongos by db.locks.find( { _id : "balancer" } ).pretty()

mongos> db.locks.find( { _id : "balancer" } ).pretty()
{
    "_id" : "balancer",
    "state" : 2,
    "who" : "mongos30-02:27017:1459106195:1804289383:Balancer:846930886",
    "ts" : ObjectId("5784b2b42e0cc50683dc622e"),
    "process" : "mongos30-02:27017:1459106195:1804289383",
    "when" : ISODate("2016-07-12T09:04:52.713Z"),
    "why" : "doing balance round"
}

Update: I tried adding a balancer schedule at midnight. the balancer stopped now. Even the migration thread stopped, It still failed to findOne() on mongos. but worked on each primary node.

I got log from mongos when I issue findOne() command as below:

2016-07-13T14:15:06.118+0800 I SHARDING [LockPinger] cluster mongocfg30-01:27019,mongocfg30-02:27019,mongocfg30-03:27019 pinged successfully at Wed Jul 13 14:15:05 2016 by distributed lock pinger 'mongocfg30-01:27019,mongocfg30-02:27019,mongocfg30-03:27019/mongos30-01:27017:1459049395:1804289383', sleeping for 30000ms

The cluster map is as below(some mongo version are not the same)
All node OS is ubuntu 14.04 kvm, where all mongod have 20G RAM.


texts: Unable to Create new Maintenance plan in SSMS 2017

texts:
Hi I have reinstalled SQL Server 2016 in my database server last day.
I am able to connect to SQL Instance(SQL-02) using the SSMS 2017 i had earlier in the same server before reinstall.

But when i try to create the maintenance plans,i get the below error.

Exception has been thrown by the target of an invocation. (mscorlib)

Retrieving the COM class factory for component with CLSID {8357986F-76E9-4E0A-A780-FB0541E91ECC} failed due to the following error: 8007007e The specified module could not be found. (Exception from HRESULT: 0x8007007E). (Microsoft.SqlServer.ManagedDTS)

I have done enough research on this and learned from several posts from web that i have to run the below command

REGSVR32.EXE dts.dll

and then disconnect from SSMS and reconnect.
For most of the people ,this solved the issue and for me i still the issue.

I have my sql server in d: drive and i ran the above command as administrator in the below location.

D:\Program Files\Microsoft SQL Server\130\DTS\Binn

I get the message that the .dll is loaded successfuly.
But when i try to create a new maintenance plan i still get the same error.

I didn't find any other solution for this issue.

Any help is appreciated.

Additional Info:

I have another database sever in the same domain.
I can connect the same instance(SQL-02) in that server's SSMS 2017 and can create new maintenance plan for the instance.
I am using the same version of SSMS in both servers.


texts: Connect to Azure KeyVault via SQL Connect failed with Error code 3113

texts:
I have a task to enable SQL Server database encryption with an Azure Key Vault. I follow the steps described in Set up SQL Server TDE Extensible Key Management by using Azure Key Vault.
More details as following

In my Azure portal, I created a Key Vault, named DB-Encryption-POC and added a Key management Access policy with all permissions.
In the Key vault I created a RSA 2048 Key named DBEncryptionKey1
In Active Directory I registered an App named test-keyvault
In Key Vault DB-Encryption-POC, I added a Key management Access policy with all permissions and set the service principal as test-keyvault. I hope this will hook my app test-keyvault with the access permissions
In the App test-keyvault, I created a secret


texts: changed password for postgres login in pgAdmin, now can't connect to server

texts:
I was trying to use pg_dump to script out a database and in the process I used pgAdmin to change the password for the postgres user. I realize (now) that changing that password was a bad idea. But now I can't get back in to the server to undo it. Postgres is running:

$ ps aux | grep postgr
postgres 18888  0.0  0.1 206480 13984 ?        S    16:39   0:00 /opt/PostgreSQL/9.3/bin/postgres -D /home/dev/postgres_data
postgres 18889  0.0  0.0  60060  1180 ?        Ss   16:39   0:00 postgres: logger process
postgres 18891  0.0  0.0 206480  1376 ?        Ss   16:39   0:00 postgres: checkpointer process

But when I connect from the command line, I get an error I've never seen before:

$ psql
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

/var/run/postgresql/ doesn't exist. When I search for that error I only get hits for Mac OS X but I'm on Ubuntu 12.04. The closest I can get is:

$ psql -h localhost -U postgres
psql: FATAL:  password authentication failed for user "postgres"
password retrieved from file "/home/dev/.pgpass"

I've tried adding passwords to /home/dev/.pgpass (there's really only 4 that I use on this system) and none of them are allowing me access. I've tried connecting with pgAdmin with the same result. I've restarted the server with sudo /etc/init.d/postgresql-9.3 restart and then logging in, didn't help.

I'm at a loss for what to do next.

Postgres 9.3, Ubuntu 12.


texts:
We have been encountering lately frequent hanging of our database server. Initial thoughts is its running out of memory. Based on logs, just before this happens, the stmm would reallocate memory.

The application currently accessing the database is running on jboss eap. Checking the logs, the error returned is its unable to open connections.

Initially, we are trying to figure out why memory runs out. We noticed that jboss opens a lot of active connections, and would not flush then

  Application     Cpu%     IO%    Mem% Application                         Application              Delta          Delta
   Handle(Stat)   Total   Total   Total Status                              Name                RowsRead/s  RowsWritten/s
   ------------ ------- ------- ------- ----------------------------------- --------------- -------------- --------------
       17819(c)   0.00%   0.00%   3.59% Connected                           db2jcc_applicat              0              0
       21065(i)   0.00%   0.00%   8.38% UOW Waiting in the application      db2jcc_applicat              0              0
       21069(i)   0.00%   0.00%   7.78% UOW Waiting in the application      db2jcc_applicat              0              0
       21080(i)   0.00%   0.00%   9.58% UOW Waiting in the application      db2jcc_applicat              0              0
       21138(i)   0.00%   0.00%   8.38% UOW Waiting in the application      db2jcc_applicat              0              0
       21156(i)   0.00%   0.00%   8.98% UOW Waiting in the application      db2jcc_applicat              0              0
       21169(i)   0.00%   0.00%   3.59% UOW Waiting in the application      db2jcc_applicat              0              0
       21175(i)   0.00%   0.00%   7.19% UOW Waiting in the application      db2jcc_applicat              0              0
       21179(i)   0.00%   0.00%   7.19% UOW Waiting in the application      db2jcc_applicat              0              0
       21182(i)   0.00%   0.00%   8.38% UOW Waiting in the application      db2jcc_applicat              0              0

I was wondering wheter the Mem%total would mean that memory is already being reserved for this process despite it not being in active use? Could this be the possible reason why we always run out of memory?


texts: Is it poor practice to aggregate data from different tables into one?

texts:
Background

I write a lot of large reports for and generally maintain a large health records DB (write SPs, functions, jobs, etc.). The original schema, and software that uses it, is from a different vendor, so I can't change much about it structurally.  There are many records that require tracking such as labs, procedures, vaccines, etc. and they are scattered across dozens of tables, many of which are bloated and poorly indexed (I have been able to fix this somewhat).

The Problem

The issue is that because we have little control over the DB, and since it can change from any given update or patch, it makes writing and maintaining these reports difficult and tedious - especially when there's a large amount of overlap.  All it takes is one patch and I'm stuck rewriting large portions of a dozen reports.  Additionally, queries quickly become obfuscated and slow as the joins, nested selects, and applies pile up.

My "Solution"

My plan was to write all of these records to one "catch-all" table, and write triggers on the original tables to maintain the records in this aggregate table.  Of course I'd need to ensure my triggers were intact after updates, but this would be much easier from a maintainability standpoint, and just referencing the data.

The table would be thin and long, storing only the data required, something like this:

CREATE TABLE dbo.HCM_Event_Log (
    id INT IDENTITY,
    type_id INT NULL,
    orig_id VARCHAR(36) NULL,
    patient_id UNIQUEIDENTIFIER NOT NULL,
    visit_id UNIQUEIDENTIFIER NULL,
    lookup_id VARCHAR(50) NULL,
    status VARCHAR(15) NULL,
    ordered_datetime DATETIME NULL,
    completed_datetime DATETIME NULL,
    CONSTRAINT PK_HCM_Event_Log PRIMARY KEY CLUSTERED (id)
)

Then I'd have various relational tables for things like the type_id and item groupings.

I'm starting to second guess this idea as several of these tables are written to quite a bit, the SPs and reports I'd be writing would reference the data a lot as well.  So I'm concerned that this table would become a record locking and performance nightmare with so much I/O.

My Question

Is a bad or a good idea?  I realize every situation is different in SQL Server (2008 r2 Standard Edition BTW), and the "sometimes" rule, but I'm really just looking for general advice.

I started to consider using a service broker, but I'd only be performing simple updates/inserts (See the alternative to the accepted answer). The data in many cases needs to be realtime, so using a backup DB wouldn't really work.  Performance is already somewhat of an issue for us, but most of that is hardware related that'll be resolved soon.


texts: Running each SQL Server service under different Windows accounts

texts:
Besides the Database Engine, SQL Server has some additional Services, namely:

SQL Server Integration Services
SQL Server Analysis Services
SQL Server Reporting Services
SQL Server Agent

Should each one of them have separate Windows accounts?

If so, what are the benefits of using this more finegrained account setup? Is it practical?

If not, what would be a good, recommended account setup for the SQL Server and its additional services?


texts: psycopg2 connect error: cannot pass more than 100 arguments to a function?

texts:
I am trying to pass json object as a parameter to my stored procedure. I tried many things and solved couple of error atlast I am stuck with the error :
Error :
psycopg2 connect error: cannot pass more than 100 arguments to a function
LINE 1: SELECT * FROM insert_usr_details_in_all_tables('{','"','N','...
i tried json.dumps, json.loads etc but unfortunately nothing is working for me if i am executing the postgresql from scripts with same input it works fine. below is my stored procedure..

CREATE OR REPLACE PROCEDURE public.insert_usr_details_in_all_tables(
    jsonobject text)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    skill_s  Text[] := '{}';
    selectedSkill TEXT;
    edu TEXT;
    email TEXT;
    phone TEXT;
    usr_name TEXT;
    clg TEXT;
    lstEmployer TEXT;
    gendr TEXT;
    skill_row_count  INTEGER;
    usr_id INTEGER;
    skl_id INTEGER;
    intEmailExist INTEGER;
    expen DECIMAL;
BEGIN

    -- Getting name
    SELECT jsonobject->'Name' into usr_name;

    -- Getting education
    SELECT jsonobject->'Education' into edu;

    -- Getting phone number
    SELECT jsonobject->'phone_number' into phone;

    -- Getting experience
    SELECT (jsonobject->>'Exp')::DECIMAL INTO expen;

    --Getting college
    SELECT jsonobject->'College' into clg;

    -- Getting last employer
    SELECT jsonobject->'CurrentEmployer' into lstEmployer;

    --Getting gender
    SELECT jsonobject->'Gender' into gendr;

    -- Getting Email
    SELECT json_array_length(jsonobject->'Email') into intEmailExist;
    IF intEmailExist > 0 THEN
        email:=array(SELECT json_array_elements_text(jsonobject->'Email'));
    END IF;

    -- Insert user details in 'Extractor_usr_details' table.
    INSERT INTO public."Extractor_usr_details"(name, phone_number, email, exp, education, college, "currentEmployer", gender)VALUES ( usr_name, phone, email, expen, edu, clg, lstEmployer, gendr) returning "id" into usr_id;

    skill_s := array(SELECT json_array_elements_text(jsonObject->'Skills'));
    FOREACH selectedSkill IN ARRAY skill_s LOOP
        INSERT INTO public."Extractor_skills" (skill) SELECT selectedSkill WHERE NOT EXISTS (SELECT id FROM public."Extractor_skills" WHERE skill = selectedSkill)RETURNING id into skl_id;
        INSERT INTO public."Extractor_usr_skills" (skill_id, user_id) SELECT skl_id, usr_id WHERE NOT EXISTS (SELECT id FROM public."Extractor_usr_skills" WHERE skill_id = skl_id AND user_id = usr_id);
    END LOOP;
    COMMIT;
END;
$BODY$;

and here is my python code to call the procedure..
   try:
   usrdetails = {'Name': 'Prashant Chandel', 'Education': 'B. Tech', 'Exp': 3.6, 'phone_number': '', 'College': '', 'CurrentEmployer': ['Tata Tele Services'], 'Gender': '', 'Skills': ['Spring boot', 'springioc', 'Css']}
        abc = json.dumps(usrdetails)
        print(type(abc))
        ps_connection = psycopg2.connect(user="postgres",
                                         password="demo",
                                         host="127.0.0.1",
                                         port="5432",
                                         database="postgres")

        cursor = ps_connection.cursor()
        cursor.execute("call insert_usr_details_in_all_tables(?)" [abc])

    except (Exception) as err:
        print("\npsycopg2 connect error:", err)
        conn = None
        cur = None


texts:
I'm trying to find a scheduler_count for my Azure SQL DB, so that I can get sp_Blitz to run. On all other platforms, scheduler_count can be read from sys.dm_os_sys_info, but this dmv is not available currently.

SELECT scheduler_count, * FROM sys.dm_os_sys_info;

Do you know of a way to find it?

I also need a (estimate for) cpu_count.


texts: AWS Aurora MySQL Serverless CPU utilisation is above 10 even it is not processing any requests

texts: We have Aurora MySQL serverless DB with 2 capacity units. I didn't understand why the CPU utilisation is always above 10 even when it not processing any queries. As a result we are paying more bills without any use. Can someone help me to understand how the CPU utilisation is calculated and why serveless is charging even when it is in ideal state(not serving any requests).


texts: Multiple transactional publication: pros and cons

texts:
I have two database servers:

 1. DB1
 2. DB2

Both are SQL Server 2012. DB1 - is a production server. DB2 - is a server which is used only for reporting. There is a transactional replication, which replicate data between DB1 (publisher) and DB2 (subscriber and distributor).

Recently database size has grown and if I need to add or delete any object it takes too much time to make a snapshot, send it to Distributor and apply all changes to Subscriber. Now it takes 3+ hours which is not reasonable for me.

So I wonder if I could configure multiple publications - one for the biggest tables which are not changed to often and one for all other objects.

Does it make sense?
What do you think about any cons of this solution?
Maybe you could provide any other solutions for this case?

Thank you.


texts:
I have a log table. It logs visitor data (time of visit, browser, window size ...).

I want to create reports. I use a query like this (MySQL):

SELECT
COUNT(*) as raw_views ...
FROM logs
WHERE timestamp >= CURDATE()
GROUP BY HOUR(timestamp)

The problem is HOUR(timestamp). I am using a function on this field, so the index won't work. But the table is huge. Index are very important. But how else can I create hourly (or daily, yearly, monthly) reports?

I think I have this problem: http://en.wikipedia.org/wiki/Sargable

But there must be a way somehow, or how else are other people doing it?


texts: Oracle Table Design, Storing Small Values

texts:
In Oracle if you want to store a small value, such as a one digit code (e.g. 0, 1, 2, 3 4, or Null; or Null, or 1; or 1, or 2), what is the space optimal way to store such a value. Currently I'm looking at a system where it was decided to store these values as VARCHAR2(11 CHAR), but it seems like the wrong option for a column type. From the online Oracle documentation and the almighty Google it seems like the more space optimal storage option with be CHAR(1) as it seems to be the smallest on disk type.

Am I completely wrong in my thinking or doesn’t it not matter either way?


texts:
I have MS SQL Server with different size of RAM.
So I would like to control the values which are set for the RAM.
Somewhere I read a 'rule' to calculate the maximum of RAM.

These SQL Server are only for DB's. At the server itself is nothing else running.
Only two remote session are possible.

How many RAM does the OS and the two remote session require?


texts: How to know if a table has a non-unique clustered index?

texts:
Give a user table ‘MyTable’. How to know whether the table contains a non-unique clustered index by using SQL query?

Thanks


texts: What is the best way to get a random ordering?

texts:
I have a query where I want the resulting records to be ordered randomly. It uses a clustered index, so if I do not include an order by it will likely return records in the order of that index. How can I ensure a random row order?

I understand that it will likely not be "truly" random, pseudo-random is good enough for my needs.


texts:
I have a virtualized 32bits Server for Mysql (RAM : 115 Go).

I am trying to optimize with mysqltuner and I get this :

>>  MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.41-0+wheezy1-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 6G (Tables: 293)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 3

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 23m 4s (55K q [39.949 qps], 8K conn, TX: 72M, RX: 9M)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 2.7G global + 35.6M per thread (600 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 23.6G (785% of installed RAM)
[OK] Slow queries: 0% (35/55K)
[OK] Highest usage of available connections: 8% (53/600)
[OK] Key buffer size / total MyISAM indexes: 1.5G/1.1G
[OK] Key buffer hit rate: 99.6% (85M cached / 327K reads)
[!!] Query cache efficiency: 10.5% (2K cached / 22K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (43 temp sorts / 8K sorts)
[!!] Joins performed without indexes: 68
[OK] Temporary tables created on disk: 19% (645 on disk / 3K total)
[OK] Thread cache hit rate: 97% (202 created / 8K connections)
[OK] Table cache hit rate: 34% (542 open / 1K opened)
[OK] Open file limit used: 28% (843/3K)
[OK] Table locks acquired immediately: 97% (47K immediate / 48K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_limit (> 5M, or use smaller result sets)
    join_buffer_size (> 20.0M, or always use indexes with joins)

my.cnf :

key_buffer              = 1500M
key_buffer_size         = 1500M
tmp_table_size          = 800M
max_heap_table_size     = 800M
sort_buffer_size        = 15M
join_buffer_size        = 20M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
max_connections        = 600
table_cache            = 1200
open_files_limit       = 2400
#thread_concurrency     = 10
query_cache_limit       = 5M
query_cache_size        = 500M

Does it mean that I can use only 23,6 Go of the RAM ? Do you think it would be important to migrate to a 64bits container ? The Mysql performance are not so good that I could imagine with a such server...


texts: Multiple tables entry with current timestamp

texts:
I have several different tables that the user can perform an action on, for example:

Vote
Comment
Chat

Each of the above tables will have a timestamp column, to hold the date of creation (it is set to the current time).

I am using long polling to get real-time updates for other users that require these updates.

I assume it would heavier on the database to constantly search multiple tables for rows where time values are greater than some value. The number of these kinds of tables can increase if features are added.

So, I want to create a dummy table called actions, which will contain all the actions that a user performed. There will be another column in that table that will contain what the type of that action is, for example vote, comment, chat etc.

It will also contain a timestamp value, so that my long polling can just search in this actions table for any actions.

If there are actions present that are newer than a certain timestamp, then go to those action type tables (vote, comment, chat), and get rows that are newer than that same value, and return them to the user.

My question is: As I am inserting data to two different tables (and I want both of the timestamp values to be exactly equal), what should I do to maintain this integrity?


texts: In SQLCMD is there any way of shelling out to run arbitrary programs?

texts:
In the past I have dropped json into a table in a nvarchar(Max) column.

This is structured data but it needed serving back to the client as json and came in from the client as json so it seemed a bit of a waste of time to map it out to a table and then recombine it again.

However doing this made me a little uneasy as you can't manipulate nvarchar(Max) columns in sql and lets face it sql is really poor at manipulating strings anyway.

Our database versioning process is entirely based around creating scripts to be run by sqlcmd.  This would make it impossible to refactor the data structure (using sql) in the future, which isn't a good state to be in.

So the question is this:  Is there anyway of shelling out of sqlcmd to run arbitrary programs?  This would want to be something synchronous, so the normal flow of patches could resume after the other program was called.  This would allow you write programs in languages more suited to manipulating strings.


texts:
I am building my first SQL database for work. I want to be able to run queries that show what stocks a particular client owns. I have a table for clients which has an identifier for each portfolio each client manages. Each client may manage multiple portfolios, so I have a column for portfolio_1, portfolio_2, etc. Multiple clients may also be associated with the same portfolio.

In a separate table, I have the list of portfolio identifiers with details about each portfolio. I then have a holdings table to which I plan to upload every holding for each portfolio by ticker. Each holding will have the portfolio identifier and the date of record for the holding. The same holdings will appear multiple times, each with the associated portfolio identifier.

Is this the best way to structure the database? My concern is that it seems simple if each client only managed one portfolio. However, how would I query the database to make sure it found all the holdings in each portfolio the client manages?

Thank you very much for your time.


texts: Which will be faster for Millions of records? Procedure or Insert statement with multiple values?

texts: I have written a perl script that reads row from a tab separated txt file, do some processing on it, and inserts into the MySQL table.Currently, I am making an insert query with 10,000 records (from the script) and finally execute the statement. insert into tablename values (1, 'kamal'), (2, 'nayan'), ......, (10000, 'kumar');  Another approach that came in my mind is stored procedure. Can anyone tell me how can I utilise stored procedure to make my queries faster? Right now, I have tested stored procedure to insert one row at a time, but that would need 10000 procedure calls, while insert statement is asking for just one call.Any help would be appreciated.Engine: InnoDBVersion: 5.5.45


texts:
We normally think about normalisation as a way of avoiding data duplication, and for speed we tend to denormalise. However I have a performance problem with a database I maintain and I wonder if I could normalise some of the data to increase the speed of select queries.

We have a legacy MS SQL Server 2005 database that runs our helpdesk. There is a Tasks table that contains a task that some engineer has to do. Over the years this table has had more and more columns added to it. It now has 29 columns of various types including text (BLOB) columns.

The problem is that if I do a simple query (using ADO from VBScript) like select created_date, title from Tasks where blah blah then this is getting rather slow. If I look in detail I find that the query returns in a second or two but stepping through the result set and reading back the (few hundred) results takes 10-20 seconds. When I look at the server performance I see a big spike in the disk i/o and disk queue length.

This query doesn't have any joins, so it should be about as fast as it could be. The columns in the where clause are indexed, and reindexing doesn't affect the speed so I assume the indicies are in good shape.

I wonder if the speed problems are due to SQL Server having to pick out just the few columns I'm querying from the 29 columns currently in each row. So my question is whether I can improve performance by splitting the table into a table with just the most commonly queried columns and a second table with everything else?

I guess the obvious thing is to try the experiment, but setting up a test system with SQL 2005 is not an insignificant effort. It would be very helpful if someone has run into this sort of problem and can tell me whether it's likely to be worth trying or not.


texts: Does the order of columns in a columnstore index matter?

texts:
I have a table with ~200 million rows and ~15 columns in it. I am planning to create a COLUMNSTORE index on my table.

Will there be any change in performance based on the order of columns that I use in the columnstore index? If yes, what is the logic behind it?


texts: MEMORY_OPTIMIZED table as replacement for temporary tables

texts:
I use temporary tables for complex processing which is done in several "child" procedures. These procedures are working in the same session, so the temp tables are accessible for them.

Is there a similar option for memory-optimized tables?


texts: Why max_allowed_packet is larger in mysqldump than mysqld in my.cnf?

texts:
If you look at the sample config such as /usr/share/mysql/my-large.cnf, you will find..

[mysqld]
max_allowed_packet = 1M

[mysqldump]
max_allowed_packet = 16M

What is the point?

Because when you export a dump using mysqldump with max_allowed_packet that might not able import successfully to the current mysql? E.g. https://stackoverflow.com/questions/93128/mysql-error-1153-got-a-packet-bigger-than-max-allowed-packet-bytes


texts: Centralized master data shared among other several DBs

texts:
I'm researching and investigating about all possible ways to create a master data database for my portfolio of applications.
To give you a quick overview, an example will come in handy:

I want to create a Users database that several different applications will use. Instead of creating user tables in each application I need to centralize in one point all these users data and their permissions, for each application.

All this applications have MS SQL server as rdbms, so the Replication engine with a single publisher and several subscribers is an option. However, I would like to know if there are other alternatives, and what's more important, if any of these alternatives could be platform independent (for example, a master DB designed in MySQL with two slaves which could be Oracle and MS SQLServer).

Thanks for your help!!


texts: Querying MySQL InnoDB to find if the Record is in its Buffer Pool

texts:
For us, it is very important to get high response time for our website php script which queries a large database. It is essentially getting user information for the user who visited the page. There may be millions of users in the db.

We would like to not bother using the user data at all if the record is not already in the InnoDB Buffer Pool of the MySQL database, and use information only if present in the above cache.

Is there a way to directly ask Mysql/InnoDB whether the record is in cache?

We thought this might have been addressed by many others when developing high performance web apps.  Is it not common to get data only if can be retrieved in super quick time (for some scenarios at least where response time is very critical) and not otherwise?


texts: How to copy bytea from Postgres to Oracle

texts:
I have a db on Postgres 9.1. The db collected some binary data that are stored in bytea file.
I need to copy the data to Oracle 10g. How to do it?

The details

I have a table plines in Postgres9.1. plines contains a bytea  field shape.
The number of records is about 500.000.

I have a table olines in Oracle10g. olines contains a blob  field shape.

I need to copy data from  plines.shape to  olines.shape.


texts: Join to get most specific record for tenant without PIVOT

texts:
I have a Settings table and a Tenant table.
There is a hierarchy for that an Account can have 1 or many Companies and a Company can have 1 or many `Facilities'.
Account 1
   ---> Company 1
          ---> Facility 1
          ---> Facility 2
   ---> Company 2
          ---> Facility 3
          ---> Facility 4

They may have a default setting that applies to their entire account....
| FacilityId | CompanyId | AccountId | SettingValue |
|------------|-----------|-----------|--------------|
|     (null) |    (null) |         1 |            5 |

...except they have one override for Facility 3 that only applies to Facility 3, all other facilities will use the default setting value at the account level.
| FacilityId | CompanyId | AccountId | SettingValue |
|------------|-----------|-----------|--------------|
|          3 |    (null) |         1 |            6 |

I want to create a join between them to get the most specific setting for each tenant. Most specific is defined as the Setting record that matches the Tenant's FacilityId is more specific than a match on CompanyId which is more specific than a match on AccountId and finally, if no match is found, use the setting that has NULL for all 3 values.
I do not want to use the PIVOT feature as the code uses Entity Framework and LINQ and there is no LINQ to SQL for PIVOT. Basically need simple SQL that you could create a view for...so no temp tables, etc. Not looking for a stored proc solution if at all possible.
SQLFiddle
Table: Settings
| FacilityId | CompanyId | AccountId | SettingValue |
|------------|-----------|-----------|--------------|
|          1 |         1 |         1 |            5 |
|     (null) |         2 |         2 |            7 |
|     (null) |         1 |         1 |            4 |
|     (null) |    (null) |         2 |            6 |
|     (null) |    (null) |         1 |            3 |
|     (null) |    (null) |    (null) |            2 |

Table: Tenants
| FacilityId | CompanyId | AccountId |
|------------|-----------|-----------|
|          1 |         1 |         1 |
|          2 |         2 |         2 |
|          3 |         3 |         3 |

So join on these would have this desired output:
| FacilityId | CompanyId | AccountId | SettingValue |
|------------|-----------|-----------|--------------|
|          1 |         1 |         1 |            5 |
|          2 |         2 |         2 |            7 | --> this account would match to a setting value of 6 or 7, but the 7 value matches more specifically
|          3 |         3 |         3 |            2 | --> there is no match on Facility, Company, or Account so match to all nulls.

In code, I am doing the following to get the most specific Setting for a given Tenant, but I now need to do this for a large set of Tenant data and hence want to do it by a SQL Join. For those unfamiliar with LINQ the double pipe (||) is equivalent to OR.
private SettingViewModel GetSettingBy(string strKey)
{
    var allSettings = GetAllSettings();
    var settingQuery = allSettings.Where(x => x.SettingKey == strKey);

    if (_accountCompanyFacilityViewModel.AccountId.HasValue)
    {
        settingQuery = settingQuery.Where(x => (x.AccountId == _accountCompanyFacilityViewModel.AccountId || x.AccountId == null));
    }

    if (_accountCompanyFacilityViewModel.CompanyId.HasValue)
    {
        settingQuery = settingQuery.Where(x => (x.CompanyId == _accountCompanyFacilityViewModel.CompanyId || x.CompanyId == null));
    }

    if (_accountCompanyFacilityViewModel.FacilityId.HasValue)
    {
        settingQuery = settingQuery.Where(x => (x.FacilityId == _accountCompanyFacilityViewModel.FacilityId || x.FacilityId == null));
    }

    var setting = settingQuery
            .OrderByDescending(x => x.FacilityId)
            .ThenByDescending(x => x.CompanyId)
            .ThenByDescending(x => x.AccountId)
            .FirstOrDefault();

    return setting;
}

Here's the SQL Fiddle for the Answer


texts: Calculations with group-wise sum and row count

texts:
Is it possible to sort a table by a value, divided by the sum of values within each year, again divided by number of rows for each year? I've tried to illustrate it by this faulty pseudo-query:

select *, value / sum(value group by year) / count(value group by year) result from bar order by result desc;

A very simple table to show what the result should be.

+-------+-------+-------+---------------+
| title |  year | value | result        |
+-------+-------+-------+---------------+
| Hello |  2017 |  100  | 100 / 250 / 2 |
| Hello |  2017 |  150  | 150 / 250 / 2 |
| Hello |  2018 |  300  | 300 / 903 / 3 |
| Hello |  2018 |  301  | 301 / 903 / 3 |
| Hello |  2018 |  302  | 302 / 903 / 3 |
+-------+-------+-------+---------------+

I thought I could get there by creating dummy columns, but that just got an error message.

update bar set groupsum = sum(value) group by year;
update bar set groupcount = count(value) group by year;

Does anyone have a solution, preferably a short and sweet one? (I'm using MySQL if it matters.)


texts:
I encountered a problem while testing recovering backups (Veritas BackUp Exec 2015, just for you to know).

Main Problem is: I couldnt restore the databases - looking on the target Server where I want to restore the DBs, SSMS tells me, 'msdb' database is being recovered.

I've already tried:

RESTORE DATABASE MSDB WITH NORECOVERY | ...WITH RECOVERY

The command was executed (no Errors), but nothing happened (even after restarting SQL Services & a Server restart).

I tried to set it offline, but running

ALTER DATABASE MSDB SET OFFLINE

tells me, that the msdb-Database can't be set as offline.

I need a way to get the msdb database out of recovery. Unless it will be recovered anyway, the current data is useless for me. Is there a way to just force it out of recovery (even if its damaged after executing the command)?

SELECT [state_desc] FROM [sys].[databases] WHERE [name] = N'msdb';

returns "RESTORING".


texts:
I'm trying to install mongodb as service on a machine running Windows Server 2008R2. I'm getting this message in the command prompt:

  The service is not responding to the control function. More help is available by typing NET HELPMSG 2186.

I have tried either removing --install option from config file/adding this option to the execution script, using script like

  mongod --config mongod.config --service --reinstall

There is unresolved issue at https://jira.mongodb.org/browse/SERVER-5115 describing the same problem.

Is it possible to make this work?


texts:
I am considering moving my existing script which is downloaded to be a hosted script.  One of the things it does is to keep track of some stats.  My concern is with heavy hitters (100,000 pageviews/month on up), it might tax my mysql process.

I'm currently using a VPS, which would be just to start with.  My question is...

How do I best plan ahead for an easy/smooth transition to another mysql server if load gets too high?

Thanks for any advice, it's appreciated.


texts: How to query Last inserted row for each group in oracle 10

texts:
I have two tables about users where I want to join two tables and show the latest remarks of each user

bellow is table

I want to query the latest remarks for each user (1 remark per user)
bellow query I have written but it returns multiple remarks per user if date and time is the same for remarks

SELECT a.cust_id,b.remarks,b.contact_date,b.contact_time FROM customers a,(select * from customer_review
where (cust_id,contact_time,contact_date) in (select cust_id, max(contact_time),max(contact_date) from customer_review group by cust_id)
) b WHERE  a.cust_id=b.cust_id;

Please give any solution to query 1 remark per user
(Note: sorry I cannot alter column details if any)
Live link Oracle Live


texts:
I have a slow query from a table with 7000 rows.
After a long time trying to optimize the MySQL conf nothing has changed.
The query:

| 38 | root | localhost | nrj | Query | 28 | Sending data |

SELECT node.nid AS nid, workflow_node_current.stamp AS workflow_node_current_stamp,
    'node' AS field_data_field_rh_username_node_entity_type,
    'node' AS field_data_field_text_no1_node_entity_type,
    'node' AS field_data_field_membership_number_node_entity_type,
    'node' AS field_data_field_text_1_255_n12_node_entity_type
FROM  node node
LEFT JOIN  workflow_node_history workflow_node_current ON
    (SELECT max(hid)
     FROM workflow_node_history
     WHERE nid = node.nid AND sid != old_sid
    ) = workflow_node_current.hid
WHERE (((node.status = '1') AND (node.type IN ('membership_request'))
      AND (workflow_node_current.sid = '85')))
ORDER BY workflow_node_current_stamp DESC
LIMIT 10 OFFSET 0


texts:
I have SQL Server 2012 SP4, ten databases and log shipping.

One database 2.7TB

Ten data files and two log files

Log shipping configuration:

Transaction log backup job : 3 Min
Copy job : 1 Min
Restore job : 1 Min
Standby Mode, clicked Disconnect users...

A 50MB trn file take at 2 hours restored. The database is being 100% restored and remains. How do i solve this?


texts: Checking space availability before a VACUUM FULL (postgres)

texts:
Is it possible to check how much re-usable space is available in a tablespace before running a VACUUM FULL on a large table?

I have a large postgres table (about 20G) that gets an occasional VACUUM FULL. The available freespace on that drive varies between 15-25 Gbytes. Before each vacuum is attempted I log the table size (using postgres queries) and the available diskspace (using OS tools).

I know that VACUUM FULL requires a full copy to be made of the table. So if the table is 20G then 20G of freespace is required.

Sometimes the table will be 20G, there will only be 15G OS space available and the vacuum will work. I guess the extra 5G required is recovered internally from the tablespace.

Other times the vacuum will fail due to lack of space, I guess on these occasions the extra 5G required wasn't found in the tablespace.

I'd like to be able to check that I've got enough space for a VACUUM FULL beforehand, how can I do this? I know how big the table is, I know how much space is available to the OS, but what I don't know is how much re-recyclable space is available in the tablespace.


texts: Does dropping index on column A affect queries on column B?

texts:
I have a table like below.

Column |         Type
-------------------------------
id     | integer
foo_1  | character varying(255)
foo_2  | text
foo_3  | character varying(255)
foo_4  | character varying(255)
foo_5  | character varying(255)
foo_6  | character varying(255)
foo_7  | character varying(255)
foo_8  | character varying(255)
foo_9  | timestamp without time zone
foo_10 | timestamp without time zone
foo_11 | timestamp without time zone
foo_12 | integer
foo_13 | boolean
foo_14 | integer
foo_15 | integer
foo_16 | integer
foo_17 | character varying
foo_18 | character varying
foo_19 | character varying
foo_20 | character varying
foo_21 | character varying
foo_22 | boolean
foo_23 | character varying
foo_24 | integer
foo_25 | integer
foo_26 | boolean

Indexes:
"items_pkey" PRIMARY KEY, btree (id)
"index_items_on_foo_12_and_foo_25" UNIQUE, btree (foo_12, foo_25)
"index_items_on_foo_25" btree (foo_25)
"index_items_on_foo_12_and_foo_11" btree (foo_12, (foo_11 IS NULL), foo_11) WHERE foo_11 IS NULL
"index_items_on_foo_12_and_foo_18_and_foo_19" btree (foo_12, foo_18, foo_19)

Referenced by:
TABLE "bar_1" CONSTRAINT "fk_rails_0b050ce365" FOREIGN KEY (item_id) REFERENCES items(id)
TABLE "bar_2" CONSTRAINT "fk_rails_3a20ccae66" FOREIGN KEY (item_id) REFERENCES items(id)
TABLE "bar_3" CONSTRAINT "fk_rails_6a4a43dcc1" FOREIGN KEY (item_id) REFERENCES items(id)
TABLE "bar_4" CONSTRAINT "fk_rails_92770ef494" FOREIGN KEY (item_id) REFERENCES items(id)
TABLE "bar_5" CONSTRAINT "fk_rails_f9da65bc10" FOREIGN KEY (item_id) REFERENCES items(id)

I want to do an UPDATE:

UPDATE items
SET foo_1 = 'foo' || items.id || 'bar',
    foo_8 = NULL,
    foo_17 = NULL,
    foo_20 = NULL,
    foo_21 = NULL,
    foo_23 = NULL;

Does dropping any of the indexes of foreign keys might improve the time of the above update? None of the updated columns are in the indexes or constraints.


texts: Oracle Data Pump imported table, some columns not selectable

texts:
Imported a table into Oracle 12g using data pump. Some of the columns, though present in the table as shown by describe, cannot be selected but they show up in select *.

Cannot select them individually  or apply where clause on only these columns.


texts:
I have pgbouncer running locally with config:

[databases]
* = host=myremoteIP port=5432 user=postgres
listen_addr = *
listen_port = 6432

When I connect using pgsql using:

psql -h 127.0.0.1 -p 6432 -U postgres -W

I receive an error:

  psql: ERROR:  pgbouncer cannot connect to server

/var/log/pgbouncer/pgbouncer.log contains the following:

  WARNING Cannot listen on unix:/var/run/postgresql/.s.PGSQL.6432:
  bind(): Permission denied

Disabling the firewall on the local and remote machines still results in the same error.

What configuration am I missing?


texts: MySQL can take more than an hour to start

texts:
I have a mysql (Percona) 5.7 instance with over 1Million tables. When I start the database, it can take more than an hour to start. Errorlog doesn't show anything, but when I trace mysqld_safe, I found out that MySQL is getting a stat on every file in the DB.

Any idea why this may happen? Also, please no suggestion to fix my schema, this is a blackbox. Also keep in mind that I've heard it all regarding how bad the design is....

Thanks


texts:
I have written a query utilizing Oracle's with clause. I have identified one particular with clause in a larger query to be really, really slow.  Here are three versions of the same query, they all have the same execution plan!

Version 1 (takes 30 seconds):

with abc as
(
  select count(distinct id) as my_count, some_column
  from  large_view v where some_column in (...constant values here...)
  group by some_column
) select my_count from abc;

Version 2 (takes 50ms):

  select count(distinct id) as my_count, some_column
  from  large_view v where some_column in (...constant values here...)
  group by some_column;

Version 3 (takes 50ms);

select my_count from (
  select count(distinct id) as my_count, some_column
  from  large_view v where some_column in (...constant values here...)
  group by some_column
);

I'd really like to use the with clause to keep the large query tidy. However, the difference in execution time is dramatic. Version 2 and 3 only take a few ms and Version 1 runs for about 30 seconds.

What is it that oracle does so that the statement with the with clause is that much slower and how can I get rid of it? Any explanation appreciated. I suspect that Version 1 is materializing every time but don't know for sure and how I can test it. Also, if that's the case, can I get rid of it (with a hint or a setting)? However, I could be wrong on this because with the materialize hint explicitly set the query runs even slower.

Oracle version:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production PL/SQL Release 11.2.0.1.0 - Production
"CORE    11.2.0.1.0  Production"
TNS for Linux: Version 11.2.0.1.0 -
Production NLSRTL Version 11.2.0.1.0 - Production


texts: os_username vs username in Oracle audits

texts:
I have recently been given the task of building reports from the audits of our Oracle 11g database. While not being the DBA and him being on vacation for a few weeks I have troubles understanding how some informations are recorded.

My main concern is about the os_username and username field. When reading Oracle documentation I see that:

  os_username: Operating system login username of the user whose actions were audited

  username: Name (not ID number) of the user whose actions were audited.

In the audit_trail I found that os_username contained mostly login username which sure sounds like the definition but also contained entries by "oracle". While the obviously correct os_username had username of what seems to be our systems or things like OEM, the records with a os_username "oracle" all had username entries of login username making this confusing.

In the end, what is the real difference between those 2 columns and on which should I base my query to know how many times a user was recorded?

Any other references I could read on the subject would also be appreciated


texts: In BEFORE UPDATE TRIGGER, how do I prevent changing nothing but timestamp?

texts:
I'm working on a trigger, and part of its functionality is to prevent UPDATE if the only field being updated is the timestamp field.

My current solution is like this:

CREATE OR REPLACE FUNCTION verify() RETURNS TRIGGER AS $verify$
    BEGIN
        IF (TG_OP = 'UPDATE') THEN
            IF ((OLD.first, OLD.middle, OLD.last, OLD.address, OLD.phone, OLD.notes)
                IS DISTINCT FROM
                (NEW.first, NEW.middle, NEW.last, NEW.address, NEW.phone, NEW.notes)) THEN
                    RETURN NEW;
            ELSE
                    RAISE NOTICE 'Must change something besides timestamp.';
                    RETURN NULL;
            END IF;
        END IF;
        RETURN NEW;
    END;
$verify$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS verify ON test;
CREATE TRIGGER verify BEFORE INSERT OR UPDATE ON test
    FOR EACH ROW EXECUTE PROCEDURE verify();

It works, but - if I add a field to the table, I would have to update the trigger, and that seems convoluted.

Is it possible to simplify the condition?


texts: MySQL replication process parallel with user process?

texts:
I have process which executes query like below on Mysql(InnoDB)  slave

INSERT ignore INTO   user_profile_temp (some_fields)
select some_fields from user_profile

user_profile is bigtable  of 8GB  which will take 1 to 2 hour with HDD.

Because of this query , replication(master to slave) is waiting in queue to get above query getting complete. There can be lag of 1 to 2 hours.

Lag is happening because of select query.As select query will acquire shared lock only at row level which mean replication records not impacting current read in process , should be able to update the record parallely.

My question is does replication process waits if any user process running at that instant or it can run in parallel if it is not impacting same rows/tables?


texts:
For example I have a column id and column email. I would like to find all values from id and column that share similar pattern.

let's say:
in id 'column' I have Alexander and in 'email' column - walex@sample.com.
if I set similarity to 4 - means match 4 character in row

script should find alexander and walex@sample.com cause they have 4 charachters match (alex)

What's the best way to accomplish something like this?


texts: Is it possible to drop inheritance from the parent table (top down)?

texts:
Our system uses inheritance on two levels:

master

child_level_1

child_level_2
child_level_2
child_level_2

child_level_1

child_level_2
child_level_2

child_level_1

child_level_2

The idea was to aggregate data by using the parent tables. But we realized that accessing the parent tables was way too slow to be useful.

We have around 500.000 tables right now and it would be nice if we could drop the parent tables to reduce that number a bit.

Is it possible to drop the inheritance from the master table? Or do we have to do it for every child?

The ideal solution would be one query that leaves us with only child_level_2 tables. They would still have all the columns that they inherited before.

Is that possible?


texts: Controlling the order in which merge articles are applied by snapshot at initialization?

texts:
I have a replication scheme that was created several months ago.  Since that time the schema has had a variety of changes.  All existing subscriptions were updated as these changes were made as the publication is set to push schema changes as well.

Recently however when trying to add a new subscription the initial snapshot fails to apply.  The cause of the error is that two different views fail to apply as tables they rely on have not been created by the snapshot yet.

I know that I can specify processing order of articles via the sp_changemergearticle stored procedure.  But as I understand... the processing order setting here is for specifying the order in which data changes are applied to subscribers during sync.  Is there a way to specify the order in which articles are applied when a new subscription is initialized?  Does it use the same setting?

I figured that generating a new snapshot would cause the engine to figure out the proper order but in the case of my current publication it did not.  The new subscription fails to apply the snapshot even after I have generated a new snapshot.  It tells me the exact script that fails and when applying by hand it's obvious that it's failing because a table needed by the view does not yet exist.

What is the best way to fix this?  Does setting the processing order of an article affect the schema order as well as the data order or do I need to look somewhere else to set the order that articles are built on new subscriptions.

From what I can tell the processing order setting has no effect whatsoever on the order in which the snapshot applies its articles during initialization.  Mostly because currently all my articles have a value of 0 for their processing order.  So, is this something I can control or am I at the mercy of the snapshot generation process?


texts: Proper resizing of Log File after shrinking - SQL

texts:
Recently I've discovered our 2012 SQL Server database contains over 100,000 Virtual Log files by running the DBCC LOGINFO command. After doing a good deal of research
I am still uneasy as to what exact size I should set the Log after shrinking.

With using the code snippet below and based on my details, what suggested size should I set my Log file to and would you recommend that I change my autogrowth?

USE AdventureWorks
GO
BACKUP LOG AdventureWorks TO DISK='d:\adtlog.bak'
GO
-- Get Logical file name of the log file
sp_helpfile
GO
DBCC SHRINKFILE(AdventureWorks_Log,TRUNCATEONLY)
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(NAME = AdventureWorks_Log,SIZE = 1GB)
GO
DBCC LOGINFO
GO

Current Database Size: 39,482,368 KB
Initial Database Size: 38,557,000 KB
AutoGrowth: 128 MB
Current Log File Size: 34,964,480 KB
Initial Log File Size: 34,145,000 KB
AutoGrowth: 128 MB

A Log backup is taken 8 times a day 6 days a week and Autogrowth is happening pretty much once a week. The Log file is also on a separate storage device from the Database

Also: Many of the VLF's are probably left over from the last DBA not shrinking the file. It has been within a year or two since we updated the autogrowth to 128 MB from the default and set this initial size.


texts: Adding PRIMARY KEY and IDENTITY attributes to SQL Server 2008 tables

texts:
An application is using an SQL Server 2008 database that has ID columns, but these columns are not primary keys and have no IDENTITY attribute.

What I need to do now is adding the PKs and IDENTITY attributes. However, old IDs must be preserved.

Also, I need to do this with SQL commands (no GUI).

Problems

I tried all the following solutions, with no success.

I can't directly INSERT/UPDATE an IDENTITY value
I can't disable IDENTITY constraint for more than one table simultaneously
I can't copy data with ALTER TABLE ... SWITCH TO (it doesn't work if origin table has not PK and target table has a PK)
I can't use sequences instead of IDENTITYs for primary keys, because 2008 doesn't support sequences
The following doesn't work either:

SET IDENTITY_INSERT mydb.dbo.AZIENDE_NEW ON;

INSERT INTO mydb.dbo.AZIENDE_NEW
    SELECT *
    FROM mydb.dbo.AZIENDE_OLD;

I get error

  Msg 8101: An explicit value for the identity column in table
  'mydb.dbo.AZIENDE_NEW' can only be specified when a column
  list is used and IDENTITY_INSERT is ON

This seems to me illogical. IDENTITY_INSERT should be ON for that table...

I'm open to other solutions, but I have no more ideas.


texts: Converting character sets and foreign key constaints

texts:
I'm trying to convert the character set and collation of my MySQL database and all containing tables from utf8/utf8_unicode_ci to utf8mb4/utf8mb4_unicode_ci.

I will refer to my database name as: MyDB
I will refer to my table(s) as: MyTable1 [, MyTable2, MyTable3, etc]

I have set the database character set and collation using:

ALTER DATABASE MyDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Then I used this SQL script to generate all of my table commands:

SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ",
    "ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ")
    AS alter_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'MyDB';

This SQL generated a table with these contents:

ALTER TABLE MyDB.MyTable1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable2 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
... etc

Now my table has a lot of foreign key constraints and if I directly execute this query I get foreign key constraint errors because of incompatible character sets half way through the modification process:

#1025 - Error on rename of './MyDB/#sql-bba_3975' to './MyDB/MyTable1' (errno: 150)

So I figured I could just set FOREIGN_KEY_CHECKS to 0 before I run my query. I remember doing this before when I had to convert my database from latin1 to utf8.

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE MyDB.MyTable1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable2 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
... etc

I still get the same error for the same table.

SHOW ENGINE INNODB STATUS;

...
LATEST FOREIGN KEY ERROR
------------------------
161103  9:59:06 Error in foreign key constraint of table MyDB/MyTable1:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
 CONSTRAINT "MyTable1_ibfk_4" FOREIGN KEY ("MyTable1_Column1") REFERENCES "MyTable2" ("MyTable2_PK") ON UPDATE CASCADE
...

How could I still be getting foreign key errors if I have disabled foreign key checks?

I have tried some strange experiments...

SELECT @@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS=0;
SELECT @@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS=1;
SELECT @@FOREIGN_KEY_CHECKS;

All the selects return 1 when it should return 1 0 1.

But if I execute this:

SELECT @@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS=0;
SELECT @@FOREIGN_KEY_CHECKS;
#SET FOREIGN_KEY_CHECKS=1;
SELECT @@FOREIGN_KEY_CHECKS;

All of the selects return 0 when it should return 1 0 0.

This causes me to think that something is fundamentally wrong with my understanding of how the server session variables work.

Note:

I know I could remove all of the foreign keys in my database before I do the conversion but I'd rather not do that if I don't have to. My database has a lot of foreign keys and I don't want to miss one during the conversion.


texts: Format value to configured number of decimal places

texts:
I want to format a number to display decimal places according to a configuration value specified in another table. If the configured value is NULL, the number of decimal places should default to 2 (hardcoded here) The following statement works fine except that for values like 3.50000 which returns 3.5 even when the configured value for 'NumberOfDecimalPlaces' is 2 or 3. I get that it is the desired behavior for ROUND()

ROUND([ActualValue], COALESCE([ConfigurationTable].[NumberOfDecimalPlaces], 2)) [FormattedActualValue]

As an alternative, I tried CONVERT AND CAST.

SELECT CAST ([ActualValue] AS NUMERIC(16, COALESCE([ConfigurationTable].[NumberOfDecimalPlaces], 2))

SELECT CONVERT(DECIMAL (16, COALESCE([ConfigurationTable].[NumberOfDecimalPlaces], 2)), [ActualValue])

Both of which err to Incorrect syntax near the keyword 'COALESCE'. I understand that the second parameter to the datatype definition is not nullable and hence the error. What are my options? How can I achieve this with minimum performance overhead?


texts:
I am working on an application to store financial data and need to model the tables where the data for several financial statements will be stored. I came up with the following two designs but I can't decide on which one to use. Appreciate your input.

Diagram 1

Diagram 2

Update:

A 3rd diagram based on Daniel's answer below

Diagram 3


texts:
I know that transactions are meant to group several operations as one.
But if for example in the same table one transaction does
 DELETE FROM table where state = 'done'
and another does either
SELECT FROM table where state='new_row'
OR INSERT INTO TABLE VALUES (123, 'new_row')
 do I need any transactions or locks for the DELETE vs INSERT/SELECT? It seems not but I was thinking I may be misunderstanding something

Note:
My question is not about transactions and their benefits.My question is if 2 statements from different processes on the same table SELECT/DELETE need for any reason to be synchronized


texts: ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

texts:
We have a SQL 2012 AlwaysOn Availibility Group with 2 nodes.  We have synchronous commit between the 2 replicas. They are both set to accept ReadOnly connections when acting as secondaries and to accept All Connections when acting as Primary.

We have ReadOnly routing setup for readonly traffic via the listener and with ApplicationIntent=ReadOnly in the connection string.  It all works fine.

A couple of days ago we rebooted the readable secondary and during the time it was down, the readonly traffic failed to connect to a readable secondary (as expected), but wasn't redirected to the Primary as I thought it would. Instead the following error was received

  Unable to access the SalesDb database because no online secondary replicas are enabled for read=only access.

My question is, if no readable secondary replicas are available for some reason, does the readonly traffic not get routed to the Allow All Connections Primary replica by the listener, rather than getting a connection error?

Any answers gratefully received.

AcombLil


texts: PostgreSQL ignoring (unique) index on TEXT field

texts:
I have a users table similar to

CREATE TABLE users (
  id bigserial NOT NULL,
  msisdn text NOT NULL
);

and a UNIQUE constraint defined on msisdn:

ALTER TABLE users ADD CONSTRAINT UNIQUE (msisdn);

Now, I tried running

EXPLAIN SELECT * FROM users WHERE msisdn = '000000000001';

and PostgreSQL is returning

Seq Scan on users  (cost=0.00..7.80 rows=1 width=400)
  Filter: (msisdn = '000000000001'::text)

The table has 256 rows at the moment. Could it be that the table is so small that PostgreSQL thinks it's better to do a full-table scan instead of scanning the index? How can I make sure that PostgreSQL actually uses the index instead of doing a full-table scan?