texts:
I have setup a Master-Master-Slave replication where say A B and C, I want to replicate data from A to B (which is working) and B to C (also working) and from A to C which is not working with two masters since one slave is configured to accept only one master.My query is how to configure slave to accept multiple masters.

I want to achieve the following structure :

Tables in Master 1
+--------------+
| Master1-DB   |
+--------------+
| Table1       |
| Table2       |
| Table5       |
+--------------+

Tables in Master 2 where table 2 is replicated form master 1
+---------------------------+
| Master2-DB                |
+---------------------------+
| Table1 From Master1       |
| Table3                    |
| Table4                    |
+---------------------------+

Tables in master 3 where table 1 is from Master 1 and table 2 is from Master 2 must be replicated.
+--------------------------+
| Slave-DB                 |
+--------------------------+
| Table1 From Master1      |
| Table3 From Master2      |
| Table4 From Master2      |
| Table5 From Master1      |
+--------------------------+


texts: Equivalent of PGAdmin III Server Status Activity Window in PGAdmin 4 v5

texts:
I'm in the process of upgrading to Postgres 13.2 from a much older version.  This entails an upgrade from PGAdmin III to PGAdmin 4.
PGAdmin III has a brilliantly designed feature called Server Status Activity Window which lists all open connections on a server.  If you sort the connections by the "Query Start" column (descending) you can see the connections that have the longest-running queries.  The rows are color coded: active connections are initially green, then after a query is running for 10-15 seconds it turns brown.  Idle queries are blue.  The autovacuum queries are usually sitting in brown color because they tend to run for a while.
So with PGAdmin III Server Status Activity Window, a dba can quickly spot queries that have been running for a long time.
PGAdmin 4 v5 has something similar called "Server Activity" but it falls short.  Only the connection start timestamp is available as a column that can be sorted by.  You have drill into the connection details to see the "Query Start" timestamp and you can't sort by it.
What work-arounds are available in PGAdmin 4 v5 to get this view that was available in PGAdmin III?
A related question appears here: PGAdmin 4 - server status / view log file


texts: Sqllogship.exe: replace with powershell?

texts: To get more control, and to enable another transport. Is there a direct replacement in powershell?


texts: Why query optimizer doesn't use negative filter index

texts:
I have a table that has following structure, as you can see, there are one clustered index and two non-clustered indexes, one non-clustered index is IX_ParentId_Include, and another one is FLIX_ParentId_Include

they are same apart from the second index is filtered.

CREATE TABLE [dbo].[PhotoRepo] (
    [PhotoRepoId]              INT            IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL,
    [TypeId]                   TINYINT        NULL,
    [ParentId]                 INT            NULL,
    [RemoteLocation]           VARCHAR (4000) NOT NULL,
    [UploadedAt]               DATETIME       NOT NULL,
    [UploadedBy]               VARCHAR (255)  NULL,
    [FileSize]                 INT            NOT NULL,
    [DefaultChild]             BIT            NOT NULL,
    [RemoteLocationUploadedAt] DATETIME       NULL,
    CONSTRAINT [PK_FileList] PRIMARY KEY NONCLUSTERED ([PhotoRepoId] ASC)
);

GO
CREATE CLUSTERED INDEX [IX_PhotoRepoId]
    ON [dbo].[PhotoRepo]([PhotoRepoId] ASC);

GO

CREATE NONCLUSTERED INDEX [IX_ParentId_Include]
    ON [dbo].[PhotoRepo]([ParentId] ASC)
    INCLUDE([RemoteLocation], [DefaultChild], [TypeId]);
GO

CREATE NONCLUSTERED INDEX [FLIX_ParentId_Include]
    ON [dbo].[PhotoRepo]([ParentId] ASC)
    INCLUDE([RemoteLocation], [DefaultChild], [TypeId])
    WHERE TypeId <> 7;

GO

Now when I run this query with execution plan enabled, the query optimizer doesn't use filter index, which I expect it should be more efficient one, it uses IX_ParentId_Include. It uses filter index only when i either force it to, or delete IX_ParentId_Include

Anyone knows why?

SELECT
    *
FROM   PhotoRepo
WHERE
    typeid <> 7 and
    parentid = 123

PS: there is 30% rows that have typeid = 7


texts:
I have a variable which is a VARCHAR and will contain a list of folders (1 or more).
It may looks like that :

@Folder = '/folder1/folder2/'
or
@Folder = 'folder1/folder2/'
or
@Folder = '/Folder1/Folder2'

And if the first and the last characater of my string are '/', i want to remove them and my variable should be always

@Folder = 'Folder1/Folder2'

Thanks for your help


texts: I have a production server and a development server. The schema is equal on both. I need a tool that, if I do any schema change in the development server, it will create a sync script that I can run on production. The servers are not connected, and I can't get from one server to another.


texts: How to go about extending volumes on SQL Server boxes?

texts:
Here is a question for the sys ops out there (or those who have gone through this experience before).

My company wants to allocate additional disk space on existing SQL Server boxes in production. We are wondering whether extending a volume ("Extend Volume..." in Disk Management) can be done ONLINE or whether the SQL Server Service needs to be shut down.

Any reason why SQL Server Service should be stopped to do this or is it OK to just go an perform this operation during the day?

Just in case the system specs are relevant. This is a real box (no virtualization) running Windows 2012 R2 and SQL Server 2008 R2.

Thanks!


texts:
I have created a table with 650 Numeric(19,4) columns.
When I switch on Page Compression, by running

ALTER TABLE fct.MyTable REBUILD  WITH (DATA_COMPRESSION = PAGE);

I get

  Msg 1975, Level 16, State 1Index 'PK_Mytable' row length exceeds the maximum permissible length of '8060' bytes.

but 650 times 9 bytes is only 5850 bytes, which is quite far from the stated limit of 8060 bytes.

The server is running Windows 2012 r2 with SQL Server 2016 SP1 CU2

What is the row overhead when using Page Compression?

Here is some code to show what I mean:

/* test script to demo MSG 1975 */
DECLARE @sql NVARCHAR(max)='', @i INT =0
drop table if exists dbo.mytable;

SET @sql = 'Create table dbo.Mytable (MyTableID bigint not null
  identity(1,1) primary key clustered, '

WHILE @i < 593 BEGIN
    SET @sql += ' Column' + LTRIM(@i) + ' numeric(19,4) null, '
    SET @i +=1
END

SET @sql += ' LastColumn int) '
--SET @sql += ' with (DATA_COMPRESSION = ROW) '
SET @sql += ' with (DATA_COMPRESSION = PAGE) '

SELECT @sql
EXEC sys.sp_executesql @sql

SELECT top 10000 * FROM dbo.MyTable MT

Row compression also fails, but at a different row count.


texts: MySQL always writes simple temporary/memory tables to disk (both tmp_table_size and max_heap_table_size are set to 8GB)

texts:
I'm struggling with this issue and just can't figure out why this is happening.

CREATE TABLE sitecache._test ENGINE=MEMORY SELECT * FROM website.gallery_likes WHERE 1=0;

Every time I execute this query, the Created tmp disk tables server variable increases immediately by 5. The same thing happens for CREATE TEMPORARY TABLE ... and also for creating a table with a structure definition instead of copying the structure from another table.

Structure of website.gallery_likes:

CREATE TABLE gallery_likes (
  photoid mediumint(8) unsigned NOT NULL,
  userid int(10) unsigned NOT NULL,
  timestamp int(10) unsigned NOT NULL,
  KEY photoid (photoid,userid)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

As you can see there's no TEXT and no BLOB fields, it's a fairly simple table. If I then dump a large set of data into that temporary/memory table, it takes several minutes to complete and I can see how mysqld is writing a lot of data to a temporary file in the C:\Windows\Temp directory.

tmp_table_size and max_heap_table_size are both set to 8GB (and confirmed via SHOW VARIABLES).

MySQL 5.6.17 x64 on Windows Server 2012 R2 x64 with 64GB RAM.

Config:

[client]
no-beep
port=3306

[mysql]
default-character-set=latin1

[mysqld_safe]
open-files-limit=16384

[mysqld]
log-bin="D:/MySQL Blackhole/master_replication.log"
sync_binlog=0
binlog-do-db=...
binlog-ignore-db=...
...edited...
binlog-format=STATEMENT
expire_logs_days=1
server-id=1
ft_min_word_len=3
innodb_ft_min_token_size=3
port=3306
datadir="D:/MySQL/data/"
character-set-server=latin1
default-storage-engine=MYISAM
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=NONE
general-log=0
general_log_file="MYSQL_SERVER.log"
slow-query-log=0
slow_query_log_file="MYSQL_SERVER-slow.log"
long_query_time=10
log-error="MYSQL_SERVER.err"
max_connections=1024
query_cache_size=0
table_open_cache=4096
tmp_table_size=8G
max_heap_table_size=8G
thread_cache_size=64
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=256M
key_buffer_size=8192M
read_buffer_size=2M
read_rnd_buffer_size=128M
sort_buffer_size=8M
innodb_flush_log_at_trx_commit=0
innodb_flush_log_at_timeout=1
innodb_log_buffer_size=32M
innodb_buffer_pool_size=32G
innodb_log_file_size=8G
innodb_thread_concurrency=0
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=0
innodb_open_files=4096
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=strict_crc32
innodb_adaptive_hash_index=ON
back_log=80
flush_time=0
join_buffer_size=8M
max_allowed_packet=16M
max_connect_errors=10
open_files_limit=16384
query_cache_type=0
table_definition_cache=2500
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000

(query_cache_size=0 and default-storage-engine=MYISAM on purpose)

This happens on the production system. I have MySQL 5.6.16 x64 on my development machine (Windows 8.1 x64) running with the exact same databases and almost the same config (smaller innodb buffer pool, tmp_table_size at 512M, no replication) and this doesn't happen here. Something environmental must be different.

I've now read the documentation multiple times but I just don't see any connection as to why this happens. The table is fairly simple with no columns that are more than 512 bytes long.
Full disclosure: I already posted it on StackOverflow but nobody responded.


texts:
I have sql table from which I am trying to get results based on some criteria as:

From above table I want to create xml which has to return result as:

Here is the query I have written for the

DECLARE
    @ResourceName VARCHAR(50)
, @XMLResult XML = NULL
, @xMSG XML = NULL

SET @ResourceName = 'Property'
SET @xmsg = '<Results ReplyCode="0" ReplyText="Operation successful"><Result></Result></Results>'

SET @xMSG =
(
    SELECT 0 AS [@ReplyCode]
    , 'Operation Successfull' AS [@ReplyText]
    , @ResourceName AS ResourceName
    , ( SELECT *
        FROM (
                SELECT SystemName
                        , DBName
                        , ShortName
                        , ClassSortOrder
                        , Pcode
                        , TabNumber
                        , ColumnNumber
                        , RowNumber
                FROM ScreenOrder
            ) SearchFieldsList
        ORDER BY ClassSortOrder
        FOR XML PATH('Field'), TYPE
     )
    FOR
    XML PATH('Results')
)

SET @XMLResult = @xMSG
SELECT   @XMLResult

But this query not returning expected result. This query returning result as:

I trying to put all that Fields as class wise in xml. Where do I need to modify query?


texts: Slow postgresql query with pagination and filtering

texts:
For the DB in question, I have followed the advice of Pagination Done the
PostgreSQL Way

That is, results are being paginated on a composite index with a definite sort order.

Now, my problem is that when this is combined with a where-clause filter on a join table, the query becomes very slow.

How can I make my query faster in the filtering case?

Note: pagination_index is a unique index on sort_key_1, sort_key_2, sort_key_3.

Query without filter

EXPLAIN (ANALYZE, BUFFERS) SELECT *
FROM big_table

join small_table on small_table_id = small_table.id
--WHERE small_table.filter_column = 9173

ORDER BY sort_key_1, sort_key_2, sort_key_3
LIMIT 20

Limit  (cost=0.85..124.26 rows=20 width=293) (actual time=49.947..50.254 rows=20 loops=1)
  Buffers: shared hit=64 read=1
  ->  Nested Loop  (cost=0.85..318749153.46 rows=51655908 width=293) (actual time=49.942..50.189 rows=20 loops=1)
        Buffers: shared hit=64 read=1
        ->  Index Scan using pagination_index on big_table  (cost=0.56..106181666.21 rows=51655908 width=113) (actual time=0.019..0.054 rows=20 loops=1)
              Buffers: shared hit=5
        ->  Index Scan using small_table_pkey on small_table  (cost=0.28..4.11 rows=1 width=180) (actual time=2.498..2.500 rows=1 loops=20)
              Index Cond: (id = big_table.small_table_id)
              Buffers: shared hit=59 read=1
Total runtime: 92.107 ms

Query with filter

EXPLAIN (ANALYZE, BUFFERS) SELECT *
FROM big_table

join small_table on small_table_id = small_table.id
WHERE small_table.filter_column = 9173

ORDER BY sort_key_1, sort_key_2, sort_key_3
LIMIT 20

Limit  (cost=0.85..79443.10 rows=20 width=293) (actual time=1331003.261..2376282.943 rows=16 loops=1)
  Buffers: shared hit=45840126 read=1148265
  ->  Nested Loop  (cost=0.85..108506196.66 rows=27317 width=293) (actual time=1331003.257..2376282.873 rows=16 loops=1)
        Join Filter: (big_table.small_table_id = small_table.id)
        Rows Removed by Join Filter: 154967708
        Buffers: shared hit=45840126 read=1148265
        ->  Index Scan using pagination_index on big_table  (cost=0.56..106181666.21 rows=51655908 width=113) (actual time=0.010..1544834.357 rows=51655908 loops=1)
              Buffers: shared hit=45840121 read=1148265
        ->  Materialize  (cost=0.28..14.60 rows=3 width=180) (actual time=0.002..0.007 rows=3 loops=51655908)
              Buffers: shared hit=5
              ->  Index Scan using small_table_id_index on small_table  (cost=0.28..14.59 rows=3 width=180) (actual time=0.008..0.017 rows=3 loops=1)
                    Index Cond: (filter_column = 9173)
                    Buffers: shared hit=5
Total runtime: 2376283.041 ms

Query with filter but no order by

This is relatively fast also.

EXPLAIN (ANALYZE, BUFFERS) SELECT *
FROM big_table

join small_table on small_table_id = small_table.id
WHERE small_table.filter_column = 9173

--ORDER BY sort_key_1, sort_key_2, sort_key_3
LIMIT 20

Limit  (cost=0.56..78.57 rows=20 width=293) (actual time=0.035..100.824 rows=16 loops=1)
  Buffers: shared hit=18 read=132
  ->  Nested Loop  (cost=0.56..106540.40 rows=27317 width=293) (actual time=0.031..100.759 rows=16 loops=1)
        Buffers: shared hit=18 read=132
        ->  Seq Scan on small_table  (cost=0.00..200.91 rows=3 width=180) (actual time=0.014..10.124 rows=3 loops=1)
              Filter: (filter_column = 9173)
              Rows Removed by Filter: 5670
              Buffers: shared hit=2 read=128
        ->  Index Scan using big_table_small_table_index on big_table  (cost=0.56..35179.54 rows=26696 width=113) (actual time=30.168..30.183 rows=5 loops=3)
              Index Cond: (small_table_id = small_table.id)
              Buffers: shared hit=16 read=4
Total runtime: 100.901 ms


texts:
Everyone probably seen 100% CPU while slave recovers a WAL with lots of deletes on instance with big shared_buffers. To avoid it I usually chase sync with small shared_buffers (it reads a day of WALs in ~ one minute with 2GB and 5minutes of WAL in ~1 minute with 64GB for example) and then restart Postgres with shared_buffers = 64GB (ready for propagating a master and serving with no downtime). But what I see - it starts recovering hour old WAL! And yet changes made on master a minute ago were on the slave before taking it down. I check the state:

13138 postgres  20   0 65.8g 686m 684m R 99.7  0.6   8:07.41 postgres: startup process   recovering 00000003000009DE0000009F

and then I check the master:

postgres@master:~$ ll 9.3/main/pg_xlog/00000003000009DE0000009E
-rw------- 1 postgres postgres 16777216 Mar 14 07:57 9.3/main/pg_xlog/00000003000009DE0000009E

So it is processing WAL he was supposed to recover before restart (the WAL is hour old)! I'm missing something here - please explain?

from master settings:

checkpoint_completion_target is 0.9
checkpoint_segments 500
it has two other slaves reading from it with hot_standby_feedback on


texts: Unable to SELECT data that is definitely there

texts:
This is truly strange.  I'm connecting to an Oracle Docker container (epiclabs/docker-oracle-xe-11g) and performing the following operations:

alter database default tablespace users
create user foo identified by foo
alter user foo quota unlimited on users
create table foo.bar
insert into foo.bar some data 5 times.
Verify that select count(*) from foo.bar returns 5.  So far so good.
Open a second connection to the same database.
select * from foo.bar order by baz asc

This query returns 0 rows.
Things I've ruled out:

The container isn't being reset, because the table foo.bar still exists.  (I tried saying select * from foo.GARBAGE just to make sure it wasn't erroring out on a missing table and swallowing the error.  It errored as expected.  So the table is there.)
The second connection isn't a different user with no permissions.  Verified by select USER from DUAL.  Same username both times, and if it was a permissions issue I'd expect to see an error.

So... what could be the reason why I'm getting no rows returned from a table I've verified is populated with data, on a query with no WHERE clause?


texts: Change SSIS package location (REPOST from SO)

texts:
My SSIS Package are located in shared drive with a long path name. To make the packages easier and not to remember long path name, I use "Map my network drive" option and use X: drive as an alias to access packages. Next thing I know that all my packages locations have been updated as "X:\Package5.dtsx". Now if the pseudo network drive is disconnect, I cannot access the packages (outside the server).

Please find the snapshot that will justify what I actually meant.

How can I change the package name from X:\ to actual share drive name (where my package are stored)? I don't want to do some changes that could effect how this package run since they are very critical to the business continuity


texts: How to regenerate execution plans after clearing the plan cache?

texts:
After clearing the plan cache on SQL Server, how do I regenerate execution plans for stored procedures without executing the stored procedures themselves?

When I updated statistics this morning, one of my stored procedures was running really sluggishly.   Per some google-fu, I found that clearing the plan cache can fix the issue - and it did.  However, if I have to do this again, I'd like to avoid the performance hit from regenerating the plans while a user is waiting for a response.

Version/edition of SQL Server is 2016 Enterprise.


texts: how to get rank number that group by a column

texts:
I spend hours trying to solve this, but I can't.

Well, I simply have a table call invoices

I have data like this

I want to know each customer rank by their total sum

How can I archive this?


texts:
I have a query

SELECT leads.id, sfav.id is_favorite
FROM leads
  INNER JOIN teams tst
    ON  tst.team_set_id = leads.team_set_id
  INNER JOIN members members
    ON  tst.team_id = members.team_id
    AND members.user_id = 'a14447cf-a997-bdad-a0f8-51111616c23c'
    AND members.deleted=0
  LEFT JOIN  favourite sfav
    ON sfav.record_id=leads.id
    AND sfav.module ='Leads'
    AND sfav.created_by='a14447cf-a997-bdad-a0f8-51111616c23c'
    AND sfav.deleted=0
  LEFT JOIN cldetails bidders
    ON  bidders.lead_id = leads.id
    and bidders.deleted = 0
  LEFT JOIN accounts
    ON accounts.id = bidders.account_id
    AND accounts.deleted = 0
WHERE leads.deleted = 0
  AND
  ( ( accounts.billing_address_state IN  ( "AL","AK","AZ","AR","CA","CO","CT","DE","DC","FL","GA","HI","ID","IL","IN","IA","KS","KY","LA","ME","MD","MA","MI","MN","MS","MO","MT","NE","NV","NH","NJ","NM","NY","NC","ND","OH","OK","OR","PA","RI","SC","SD","TN","TX","UT","VT","VA","WA","WV","WI","WY") )
    OR leads.assigned_user_id = "a14447cf-a997-bdad-a0f8-51111616c23c"
  )
  AND leads.parent_lead_id IS NULL
GROUP BY COALESCE(leads.parent_lead_id, leads.id)

Which is taking around 4 seconds the Explain plan is as

id   select_type     table               type        possible_keys                                       key                 key_len     ref                                             rows    Extra
1    SIMPLE          leads               ref         idx_leads_tmst_id,idx_del_user                      idx_del_user       2    const                                  16097    Using where
1    SIMPLE          sfav                ref         idx_abdul_test                                      idx_abdul_test     375 db.leads.id,const,const,const   1
1    SIMPLE          bidders             ref         idx_abdul_test                                      idx_abdul_test     111  db.leads.id,const              5
1    SIMPLE          tst                 ref         idx_ud_set_id,idx_ud_team_id,idx_ud_team_set_id     idx_ud_set_id      109  db.leads.team_set_id           3    Using where; Using index
1    SIMPLE          members             index       idx_teammemb_team_user,idx_abdul_test               idx_abdul_test     220  NULL                                   8    Using where; Using index; Using join buffer
1    SIMPLE          accounts            eq_ref      PRIMARY,idx_accnt_id_del,idx_accnt_assigned_del     PRIMARY            108  db.bidders.account_id          1

Indexes are as

teams
  PRIMARY KEY (`id`),
  KEY `idx_ud_set_id` (`team_set_id`,`team_id`),
  KEY `idx_ud_team_id` (`team_id`),

members
  PRIMARY KEY (`id`),
  KEY `idx_abdul_test` (`team_id`,`user_id`,`deleted`)

favourite
  PRIMARY KEY (`id`),
  KEY `idx_abdul_test` (`record_id`,`module`,`created_by`,`deleted`)

cldetails
  PRIMARY KEY (`id`),
  KEY `idx_abdul_test` (`lead_id`,`deleted`)

leads
  PRIMARY KEY (`id`),
  KEY `idx_del_user` (`deleted`,`assigned_user_id`),

What can i do minimize execution time of query.

UPDATE : If i run this query with LIMIT clause it will run in 0.00 sec,But this is a part of some other query so i can't use LIMIT clause over there.


texts: Count where any 3 columns have values (not null)

texts:
I have a table by name ItemProperties with 15 columns. Columns names are like Feature1, Feature2, Color, Length, Width, Height etc... It has 10K+ rows. I need counts from this table where any 3 columns are filled (Not Null). Can we do this through a query?

In the example shown, the query should return count as 4 rows.


texts: postgresql rename a foreignkey after renaming a table

texts:
I renamed a table, and apparently a foreignkey in it doesn't get changed, and causing problems,
Although when I \d error I get:
"error_test_id_fkey" FOREIGN KEY (test_id) REFERENCES test(id) ON DELETE CASCADE

Which the only relation to error name, is the name (weird)

Anyway - I have no problem deleting the foreignkey and recreating it, but I can't figure out how to do it


texts: Force exact match from postgres ts_query

texts:
I've constructed a column of stem words called journal_tsv from the column journal_name. I've then created a GIN index on journal_tsv. Here's and example of the type of query I'm running:

SELECT DISTINCT(journal_name) FROM my_table WHERE journal_tsv @@ to_tsquery('SCIENTOMETRICS');

This returns a list of 15 journal names

                            journal_name
--------------------------------------------------------------------------------------------------------------------------------------------
 ISSI 2005: Proceedings of the 10th International Conference of the International Society for Scientometrics and Informetrics, Vols 1 and 2
 SCIENTOMETRICS
 PROCEEDINGS OF ISSI 2009 - 12TH INTERNATIONAL CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, VOL 1
 EVALUATION OF RESEARCH BY SCIENTOMETRIC INDICATORS
 8TH INTERNATIONAL CONFERENCE ON SCIENTOMETRICS AND INFORMETRICS, VOLS 1 AND 2 - ISSI-2001, PROCEEDINGS
 ISSI 2005: PROCEEDINGS OF THE 10TH INTERNATIONAL CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, VOLS 1 AND 2
 JOURNAL OF SCIENTOMETRIC RESEARCH
 PROCEEDINGS OF ISSI 2009 - 12TH INTERNATIONAL CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, VOL 2
 PROCEEDINGS OF ISSI 2011: THE 13TH CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, VOLS 1 AND 2
 PROCEEDINGS OF ISSI 2015 ISTANBUL: 15TH INTERNATIONAL SOCIETY OF SCIENTOMETRICS AND INFORMETRICS CONFERENCE
 COLLNET JOURNAL OF SCIENTOMETRICS AND INFORMATION MANAGEMENT
 SEVENTH CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, PROCEEDINGS - 1999
 Proceedings of ISSI 2007: 11th International Conference of the International Society for Scientometrics and Informetrics, Vols I and II
 PROCEEDINGS OF ISSI 2007: 11TH INTERNATIONAL CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, VOLS I AND II
 14TH INTERNATIONAL SOCIETY OF SCIENTOMETRICS AND INFORMETRICS CONFERENCE (ISSI)
(15 rows)

In most circumstances this works really well but what I can't figure out is how to restrict the results to exact matches? Most of the time fuzzy searches are exactly what I need but how can I use the same type of query to only return exact matches?

I'm trying to get the results I would get from this query:

SELECT DISTINCT(journal_name) FROM my_table WHERE journal_name = 'SCIENTOMETRICS';

But with the performance gains from using tsvector.

                                version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)


texts: Should SQL Server Always-On Availability groups have storage deduplication enabled?

texts:
We are using EMC and storage engineers want to save on storage and provide one big storage array for implementing synchronous AG replicas. I am not a storage expert but isn't it best practice to have two separate storage arrays when implementing AG replicas?
Otherwise, I will be introducing single point of failure by having one big de-duped storage. Am I right?


texts: Join mysql tables to find siblings and get their notifications

texts:
i have two mysql tabes one for company and one for notifications.
Company

+-----------------------+
| ID |parentID|vaue     |
|1   |0       |22020    |
|2   |1       |23423    |
|3   |2       |22222    |
|4   |2       |23423    |
+-----------------------+

company_notes

+---------------------------------+
|ID   |FromID |  ToID   | NotesID |
|1    |1      |  2      | 33
+---------------------------------+

so far i am able to join the tables to get NotesID of the company who has posted the note. How do i get NoteID of all sibings using company_notes.FromID.

I tried:

Select c1.ID as id,
       ci.Parent as Parent,
       n1.NotesID as noteid
    FROM `company` as c1
    LEFT JOIN comapany as c        on c2.parent=c1.ID
    LEFT JOIN company_notes as n1  on n1.FromID=c1.ID
    WHERE c1.ID=2

but the above code gives me only child notifications and alot of dupilcate values.

how do i find out notifications of all siblings, everyone related to id 2

Sorry if my question was not clear, i have now removed unrequired fields to avoid confusion.

Below is the result that i am expecting:

+-----------------+
|  ID   | NotesID |
|  3    | 33      |
|  4    | 33      |
+-----------------+

Cheers! and Happy New Year's Eve!


texts: In SQL Server 2008, is there some simple logging of how often a particular table is selected (in an hour, a day, a week, etc.)? I have a large table that is accessed by dozens of applications and scores of web pages. The data provider is threatening to make major changes to what is available and I need to start working on a demonstration of how much the data is accessed.


texts: getting mysql error when running the query alognwith ssh

texts:
I am getting " You have an error in your SQL syntax; check the manual that corresponds to your MySQL server" error when running alongwith ssh command :

ssh user@localhost echo "delete from partner_company_association where colId = \"default.col \";" | /usr/bin/mysql -h localhost dbname

But the same works fine without doing ssh and running directly in the box:

 echo "delete from partner_company_association where colId = \"default.col \";" | /usr/bin/mysql -h localhost dbname

Kindly help me in figuring out the mistake here.
thanks in advance.


texts: Multiple aggregate operations via a pivot table

texts:
My goal is to obtain for each user a total lifetime achievement points as well as points obtained in ranges, i.e. for the last three months.
I have this working but I'm curious if my solution will scale or if theirs a better way of thinking about the problem, in particular, I'm using Postgres so particular features in that DB might be a good solution for this.
Table Structure
Users:
------
id: int

AchievementsUser
----------------
id: int
user_id: int
achievement_id: int
inserted_at: DateTime

Achievement
----------------
id: int
value: int

Solution description

Left join on the pivot table to get the achievements per user. This is built as a CTE (base in the SQL)
Produce a further CTE for each date range I'm interested in. These provide the filters, and sum operations (i.e. later, I could do averages if I wanted)

SQL
WITH base AS (
  SELECT
    users.id AS user_id,
    achievements.value AS value,
    achievements_users.inserted_at AS inserted_at
  FROM
    users,
    achievements_users,
    achievements
  WHERE
    users.id = achievements_users.user_id
    AND achievements.id = achievements_users.achievement_id
),
all_time AS (
  SELECT
    base.user_id,
    SUM(base.value)
  FROM
    base
  GROUP BY
    base.user_id
),
last_three_months AS (
  SELECT
    base.user_id,
    SUM(base.value)
  FROM
    base
  WHERE
    base.inserted_at > CURRENT_DATE - INTERVAL '3 months'
  GROUP BY
    base.user_id
)
SELECT
  all_time.user_id,
  all_time.sum AS all_time,
  last_three_months.sum AS last_three_months
FROM
  all_time,
  last_three_months
WHERE
  all_time.user_id = last_three_months.user_id;


texts: Why isn't it possible to cast to a timestamp in an index in PostgreSQL?

texts:
I am working with a JSON document which I receive from an external source. In this document, there is a timestamp property which corresponds to the textual format of a timestamp PostgreSQL data type. So in other words, it can be casted to a timestamp type: (data->>'timestamp')::timestamp.

I want to use that timestamp property in an index. I tried the following:

create table t
(
    data    jsonb
);

create index on t(((data->>'timestamp')::timestamp));

I am getting:

ERROR:  functions in index expression must be marked IMMUTABLE

Why is this? I understood that casting to a timestamptz is not immutable, because of the localization configuration settings which are not static, but I can't apply that logic to a cast to timestamp.

Is there still a way to cast my timestamp property in the index here?

I am working with PostgreSQL 12, as released a few days ago.


texts:
I have a new install with push replication (20+ replications).  When it was set up the SQL account 'distributor_admin' was created with sysadmin.  I did not do the install but I am responsible for ongoing security of the databases. The install team says the account was auto created which is supported by the msdn article Secure the Distributor
According to the msdn article Replication Agent Security Model it looks like after install we can back the permission up to db_owner with a few other considerations.

The Windows account under which the agent runs is used when it makes connections to the Distributor. This account must:
-At minimum be a member of the db_owner fixed database role in the distribution database.
-Be a member of the PAL.
-Have read permissions on the snapshot share.
-Have read permissions on the installation directory of the OLE DB provider for the Subscriber if the subscription is for a non-SQL Server Subscriber.
-When replicating LOB data, the distribution agent must have write permissions on the replication C:\Program Files\Microsoft SQL Server\XX\COMfolder where XX represents the instanceID.
Note that the account that is used to connect to the Subscriber must at minimum be a member of the db_owner fixed database role in the subscription database, or have equivalent permissions if the subscription is for a non-SQL Server Subscriber.
Also note that when using -subscriptionstreams >= 2 on the distribution agent you must also grant the View Server State permission on the subscribers to detect deadlocks.

Per security rules, if the account keeps sysadmin the password needs to be changed regularly.
In this environment we expect additional replications to be added periodically.
Will dialing back the sysadmin for distributor_admin as described by msdn lead to issues?


texts: (Mysql) Using Memory engine for 4.3 million row table?

texts:
My new company stores an intermediate data for our loading process in a table using the memory engine. However a quite simple update (set a.id=b.id) is taking something like six hours to run.

I've checked how big the tables are and in this import they have a about 4.3 million rows of data in them. I'm certain the problem is the memory engine and the lack of index on id (there is a PK but its across 5 other attributes) based on HASH.

I'm 95% certain I need to change the table engine and index type but I want to make sure before I push for the change.

EDIT: Here is the statement that is is so slow:

select  @Extraction_id := id from extraction;

update items as a
set a.extraction_id=@Extraction_id;

Edit 2: create statement of table

CREATE TABLE `items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `extraction_id` char(12) NOT NULL,
  `a` char(6) NOT NULL,
  `b` char(10) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `c` date NOT NULL,
  `d` varchar(100) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `e` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `f` decimal(17,6) DEFAULT NULL,
  `g` decimal(17,6) DEFAULT NULL,
  `h` varchar(150) DEFAULT NULL,
  `i` varchar(150) DEFAULT NULL,
  PRIMARY KEY     (`id`,`extraction_id`,`a`,`b`,`c,`d`),
  KEY `fk_extraction` (`extraction_id`),
) ENGINE=MEMORY AUTO_INCREMENT=2343054016 DEFAULT CHARSET=latin1;


texts: What is the fastest way to export a table to a text file

texts:
I will have a SQL Server 2012 database and a table with 3 million rows and maybe 50 columns. What will be the fastest way for an unattended background .net process (maybe issues some SQL or Powershell command) to export it to a text file, one line for each row of data? The .net process should know when the export have been finished or if there was any error. The datatype will be all int or nvarchar.

I am assuming that a pure C# code using ado.net to execute a select * command and looping over the datareader and writing to a file for each record will be slow and there is no way I can parallelize this.

Ideally the export will be to a remote shared network folder and not a local folder on the SQL Server machine. The SQL Server will be a HA cluster. Is SSIS better suited for this, no data transformation required?

The .Net process would run on Machine A, SQL Server on Machine B and ultimate file destination is a network share. One option is SQL server writes the file directly to the network share. The other option is SQL Server writes to machine A and then when the file is written the .net process copies it to network share. I don't have formal SLA but expecting 30 mins - 1 hr for the file write.


texts: MongoDB - Mongoose query findOneAndUpdate() doesn't update/duplicates the DB

texts:
I am trying to save and update (upsert: true - creates the object if it doesn't exist) the result of the Web Api which contains multiple arrays of data to populate the stock chart. Each time I enter a symbol and click the "Get Quote" button it should fetch data from the web api, save/update it under the Child Schema in the database. For some reason it adds duplicate values. Is there a way to fix avoid duplicate values? Here is the code that I tried....

let curValueSchema = new parentSchemaSymbol()

      curValueSchema.symbol = curValue
      highLow.map(item => {
        curValueSchema.data.push(item)
      })
      const query = { symbol: `${curValue.toUpperCase()}` }
      const update = curValueSchema
      const options = { upsert: true, new: true }
      parentSchemaSymbol.findOneAndUpdate(query, update, options).then(doc => {
        console.log('Saved the symbol', doc)
        return res.send(highLow)
      }).catch(e => {
        console.log(e)
      })

SOLUTION

Because Mongoose by default creates a new MongoDB ObjectId ( this hidden _id field) every time you pass it a Javascript Object to update the field of a document.

To go around you can tell Mongoose to not create a new ObjectId, by making sure your mongoose schema is as followed:

Folder - Models - Stock.js

  "_id": false - Has to be added to the Schema

const mongoose = require('mongoose')
mongoose.Promise = global.Promise
mongoose.connect('mongodb://localhost:27017/myapp', { useNewUrlParser: true })
const slug = require('slug')

const childSchemaData = new mongoose.Schema({
  "_id": false,
  date: mongoose.Decimal128,
  open: mongoose.Decimal128,
  high: mongoose.Decimal128,
  low: mongoose.Decimal128,
  close: mongoose.Decimal128,
  volume: mongoose.Decimal128
})

const parentSchemaSymbol = new mongoose.Schema({
  "_id": false,
  symbol: {
    type: String,
    trim: true,
    minlength: 2,
    maxlength: 4,
    required: 'Plese enter a valid symbol, min 2 characters and max 4'
  },
  // Array of subdocuments
  data: [childSchemaData],
  slug: String

});

//we have to PRE-save slug before save the parentSchemaSymbol into DB
parentSchemaSymbol.pre('save', function (next) {
  if (!this.isModified('symbol')) {
    next()//skip it
    return//stop this function from running
  }
  this.slug = slug(this.symbol)
  next()
  //TODO make more resiliant soslug are unique
})

module.exports = mongoose.model('Stock', parentSchemaSymbol)


texts: The good method for full text search on one column may contain multi languages?

texts:
when I only have english. I use such way.

add a new english_column to store tsvector.
create gin index on the english_column.
create trigger on insert or update.

However. When I want to support other language. This method need create 3 new column.
And query the pattern for 3 different language like
select * from where
   english_column @@ to_tsquery('english', pattern) or
   german_column  @@ to_tsquery('german', pattern)

Can I keep only one new index column to support multi language?
something change like this
CREATE TEXT SEARCH DICTIONARY multi_stem_nostop (
    Template = snowball
  , Language = english, german, spanish
);

CREATE TEXT SEARCH CONFIGURATION public.multi_nostop ( COPY = pg_catalog.english, pg_catalog.german );

Or I got misunderstanding for the dictionary?


texts:
I'm trying to decide on how to set up the tables to get this use working without over-complicating it or not collecting enough data.

For the sake of an example, I've got a ticketing system with two users, a manager and a worker.

A ticket comes in, and the manager assigns it to a worker.
The manager also can set when they expect the the ticket to meet any particular milestones.
The worker updates ticket status whenever the status changes.
If the worker does not update the ticket to the status that the manager defined by the set date, the manager is notified (and the worker gets a mild electric shock).

The goal is to generate notifications when a status update does not happen by the set deadline. The second part is to be able to gauge performance, so if the manager keeps updating the date expected update (for a set status), the data is there to generate such a report.

data I'm trying to track

Ticket
User
Current Status
Previous Statuses
Expected update
Previous Expected updates

the list of statuses is likely to change, so using multiple columns won't work

I could store all of these in one table, but I could lose history of status updates.

I'm looking at a setup where I have a table for a ticket, ticket_status and ticket_expected with 1:m relationships (1 being ticket).

I've also got the latest ticket and next expected update on the ticket to make lookups more straight forward.

Is there a better way to do this?


texts: How to fetch records with a number of consecutive associated records?


texts: Functional dependencies and candidate key example

texts:
Hi there I'm still learning for my examination and I got this exercise:

A table is given:

F-Name,           F-Nation,    Team-Name,   Team-Nation
-------------------------------------------------------
Lance Armstrong,     USA,   Discovery Channel,   USA
Jose Luis Rubiera,   ESP,   Discovery Channel,   USA
Jan Ullrich,         GER,   Team Telekom,        GER
Andreas Klöden,      GER,   Team Telekom,        GER
Davide Rebellin,     IT,    Team Gerolsteiner,   GER

a) Which are the candidate keys?

b) Which non-trivial functional dependencies existing?

c) Is the table in 2NF (second normal form)? If not modify the table to be in 2NF.

d) Is the table in 3NF (third normal form)? If not modify the table to be in 3NF.

a) I would say that the candidate keys are (F-Name, Team-Name) or (F-Name, Team-Nation)

b) The dependencies are:

Team-Name -> Team-Nation
F-Name -> F-Nation

c) no it is not in 2NF because Team-Nation is not dependant from the F-Name which is part of the key candidate.

Table after normalization to 2NF:

Table driver

F-Name, F-Nation, Team-Name

Table teams

Team-Name, Team-Nation

d) I couldn't find any transitive relations so I guess the solution of c) would be in 3NF also am I right?


texts: I want to display welcome message when ever a oracle schema try to login database using TOAD or PLSQL developer only


texts: SQL Server 2008 R2 (Suspect) mode - how to repair?

texts:
I have SQL Server 2008 R2 database in Suspect mode. I tried to fix it running this query:

EXEC sp_resetstatus ‘yourDBname’;
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb(’yourDBname’)
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (’yourDBname’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER

But the repair output was this message:

Warning: You must recover this database prior to access.
Msg 8921, Level 16, State 1, Line 5
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Warning: The log for database 'ServeDB' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer
has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has
been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
Msg 8921, Level 16, State 1, Line 9
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

How to repair the database fully? This repair which i made, works only for some days, then database again goes to Suspect mode...


texts: How to know max specified memory in Oracle XE11g release 2?

texts: What is the max specified memory in Oracle XE11g release 2? I have read in Oracle documentation that the allowed max memory for database is 1GB RAM. Is that per database or per session? And if it is per database, then how can I modify it per session?


texts:
I'm learning about Execution plans and am trying out different queries and comparing their performance and stumbled upon this:

SELECT StatisticID
FROM (
    SELECT StatisticID, ROW_NUMBER() OVER (ORDER BY StatisticID) AS rn
    FROM FTCatalog.Statistic
    ) AS T
WHERE T.rn <= 1000
ORDER BY rn

SELECT TOP 1000 StatisticID
FROM FTCatalog.Statistic
ORDER BY StatisticID

They both return same result set - however first one executes faster and is less resource hungry (at least SSMS tells me that)
Here are execution plans:

Comparison from SQL Query Plan Explorer:

Could anyone give me some insight on what's actually happening behind the scenes and why results differ? If there's anything else you need - just let me know.

Thanks,
Evaldas.


texts: SQL 2016: OPENDATASOURCE Msg 7357, Level 16, State 2, Line 1

texts:
SELECT MAX(VersionNumber)
FROM OPENDATASOURCE(
    'SQLNCLI',
    'Data Source = I.P.V.4;User ID = sa;Password = pswd;catalog=MyDB'
    ).MyDB.dbo.tbl_Version

results in a "speak to the red-hand" error that does not occur on over 20 other remote SQL instances inquired-of from the same source SQL instance:

  Msg 7357, Level 16, State 2, Line 1 Cannot process the object ""MyDB"."dbo"."tbl_Version"". The OLE DB provider "SQLNCLI11" for
  linked server "(null)" indicates that either the object has no columns
  or the current user does not have permissions on that object.

Source is SQL 2016, targets vary from 2008R2, 2012, 2014 up to 2016 SP1 CU2, Enterprise and Standard Editions.

I used Idera's SQL Admin Toolset to get the server configurations from a working baseline instance, and compare it to 2 working instances, and 3 non-working instances.

All 3 of the latter reside in a Hong Kong Rackspace datacenter.  The working instances are in either a US or a UK Rackspace datacenter.

Aside from the collation of the non-working instances being different, there are no differences of merit (MAXDOP and MAXMEM don't count).

I also used that toolset's SQL Database Configuration tool and set the baseline as a working 2016 DB, then compared it to another working instance's 2014 DB, and the not-working 2016 DB - the only difference is the SQL Compatibility Level.

Ad Hoc Distributed Queries is running "ON", on all servers.

All 3 client protocols are enabled.  All TCP/IP configurations in SQL Client configs are identical.

I can connect via SSMS from my laptop using the I.P. of the not-working instance, using the same SQL login and password, and run a SELECT * from the same table, and get all rows.  I can see everything via SSMS.

I opened Server Objects/Linked Servers/Providers/SQLNCLI11 and looked at the properties on working and non-working instances - identical - the DisallowAdhocAccess setting is "OFF".

Although BOL doesn't state that after the RECONFIGURE to set the Ad Hoc Distributed Queries config to "ON", one must restart SQL, is this an omission/something that is really "required"?

Having Googled for the Msg line, plus most of the error text and found just 10 hits, none of which are useful, I took out the text message and still found nothing relevant that includes the "Msg 7357" portion.

Does anyone suspect a firewall issue? I doubt that because I can get through with SSMS, as I said above...

What (else) am I missing?

Thanks for any insights.


texts: Why are my SQL Server backup ending as a file that is twice the size of my database?

texts:
I have a SQL Server 2008 r2 database that is currently 56 GBs. When I run the following code I get a bak file that is 93 GBs. What could be causing this?

    BACKUP DATABASE [CommerceCenter]
TO DISK = 'N:\MSSQL\DATA\CC\CC_backup_tues.bak'
WITH COMPRESSION

If I use SSMS to run the backup to the same location with compression the bak file is only 8 GBs.


texts: How do you move a SQL Server 7 database to SQL Server 2012?

texts: I would like to move a database from SQL Server 7.0 to SQL Server 2012. They are not compatible for a backup and restore. Is there a way to export from SQL Server 7.0 and import it to 2012? I only need the tables -- no views or stored procedures.


texts:
Table names:

g_number  name
123       John
123       Sara
123       Michael

I want to get one RANDOM name and quantity of all names with the same g_number in one query:

SELECT name, count(*) cnt
FROM names
WHERE g_number='123'
ORDER BY rand()

But result is always the same

name    cnt
John    3

Looks like count() ignores ORDER BY rand() line.
So that's the question: is it possible to solve this in ONE query?


texts:
I have the need to find top resource consuming queries in my Percona 5.6 server so that those queries can be fine tuned to improve the performance of the database.

Also, is it possible to get the history of highly resource intensive queries for the past 3 days? Is it possible to fetch the info from the logs?

Thanks.!


texts: MySQL optimizing - use ram instead of CPU?

texts:
I am on a dedicated server with Intel Xeon D D-1520 4/8t 2,2 / 2,6 GHz, 32 GB of RAM DDR4 ECC 2133 MHz, and 2 x480GB of SSD SOFT. The problem I am encountering is that MySQL eats up all of my CPU power and slows down everything else. My server pilot says my total CPU usage is 92% right now, and these are the active processes.

As you can see, I have a lot of RAM left to be used, but nearly any CPU. This is my MySQL config my.cnf

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]

user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

bind-address        = 127.0.0.1

key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M

log_error = /var/log/mysql/error.log

expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 16M

!includedir /etc/mysql/conf.d/

my mysql_innodb.cnf only has this entry

[mysqld]
innodb_file_per_table = 1

Now, is there anything I can do to optimize my situation?

I am completely new to managing my own server, but I figured I have to start sometime somewhere somehow. So I would sincerely appreciate any recommendation or advice.

Thanks!


texts: Alter table executes despite Lost connection to Mysql server

texts:
mysql  Ver 8.0.19 for Linux on x86_64 (MySQL Community Server - GPL)
InnoDB engine

I executed 'ALTER table ... drop column' on a mysql table.

Despite the client losing connection to the server, I can still see the process running on server.
I ran 'show processlist' and it shows the 'ALTER TABLE' running.
How is connection management being done here to achieve this? Shouldn't the query on the TCP connection break once the connection itself breaks?


texts:
I'm using a database monitoring system that, amongst other data, shows the following values for SELECT queries:

number of joins that use ranges on the first table
number of joins that do a full scan of the first table
number of joins without keys that check for key usage after each row
number of joins that perform table scans because they do not use indexes
number of joins that use a range search on a reference table

Of these queries, which ones are the least performing and hence should be optimized first?  I'd say #2 and #4 in order of importance, but I'd like to hear comments about the other types too.

This monitoring system shows also the total number of SELECT queries as the sum of these five values. Are these really all possible, mutually-exclusive  cases for a SELECT?

I'm not talking about a specific query (which I can find in the slow query log); I'm more interested in the type of query amongst these 5 listed above. For instance, full table scans are usually a bad thing (unless we're working on small tables, in which case we won't care about optimization).

My other question was if these types may be overlapping, because the monitoring system shows a 6th value which is the sum of these five, and this makes no sense if the types are not mutually exclusive (as we suspect).


texts:
I work for a doctor's office, and we are in the process of switching EMR systems.  The previous system has provided a physical 'dump' of the database.  I however, don't know how to open this database and view the contents therein.  The file is 1.2 gb with less then 1000 lines of SQL commands so I assume that all of the data is there.  Here's what I've tried thus far:

Opened in a text reader:  All it shows is a bunch of SQL commands to create the database tables
Installed MySQL Workbench and tried to create a "EER Model From SQL Script": This just creates the database structure.
Using MySQL Workbench/Setup a 'localhost' server: Tried a 'import' of the .sql file onto the newly created 'server' but it doesn't work and spits out an error

What are some steps to properly opening and obtaining the data found within a MySQL data dump?

I'm VERY new to the whole database management so if I've missed any details feel free to prompt.


texts: SSAS Tabular Cube partitioning - number of partitions

texts: We ha a Fact table with 500 milions of rows (data from 10 years - 60GB). I've partitioned this table on database level and SSAS Tabular Cube level as well - one month per partition to improve the overall browsing performance and SSAS Cube processing time. But last time I've heard that configuring more than 2 partitions for SSAS Tabular cube is not good. There should be max 2 partitions per tabular cube. Have you heard about something like that ? Is that true ?


texts: How do I count the number of different child id's belonging to a parent record?

texts:
For instance I have two tables in a music database.

Table album and table track.

Each track record has (amongst other things), an album_id and an artist_id

Each album record has a unique album_id

In order to spot which albums are by 'various artists' I want to count the number of distinct artist_id's which belong to each album.

Such that two or more track records with the same album_id but with differing artist_id's should signal that I can flag the album as 'various artists'

I can do it with multiple queries but I'm seeking the elegant all in one query.


texts: Database for dictionary storage and querying

texts:
I'm looking for a database for the following data storage and query pattern:

Data consists of dictionaries - mappings string to string.

Queries are also dictionaries. The result of the query is a list of all matching dictionaries, where matching is the "obvious" relationship (dictionary d matches query q if d[k] == q[k] for all k in q).

For clarity, here is an example. Let's say you have the following data stored:

data = [{'a': '1'}
        {'a': '1', 'b': '1'}
        {'c': '2', 'b': '1'}]

Then

query({'a': '1', 'b': '1'}) == [{'a': '1', 'b': '1'}]

query({'b': '1'}) == [{'a': '1', 'b': '1'}
                      {'c': '2', 'b': '1'}]

While I think that this can be implemented using many databases, I'm looking for something that would be optimized for this kind of pattern. That is, it would easily handle thousands of different keys (across all the dictionaries), and tens of millions of entries.


texts: Slow MongoDB start time when data is replicated to a new machine

texts:
We have a 3.2.13 Mongodb replica set with WiredTiger on AWS.

We are observing a strange behaviour: when stopping and starting the mongod service, the start time is about 10 minutes, but when stopping the mongod service, then replicating the data disk to a new AWS instance, and restarting on that machine, the start time is about 2 hours.

What may be the cause for this?


texts: Is it safe to use pt-online-schema-change in a multimaster environment?

texts:
I have 2 MySQL servers with row-based replication between them. Both of them are masters and slaves for each other (active-active master-master setup).

If I understand it correctly pt-osc creates triggers to catch any changes while running. But from what I know triggers are not fired in a row-based replication environment. So I guess pt-osc is not able to catch changes made on the second master during the change, is it?

EDIT: While doing some tests and I saw that pt-osc was creating triggers on both masters which would cover changes from both sides. Still I'm quite unsure if I can safely do online changes in this environment.


texts:
i'm trying to update the table whenever complaint has been registered

create or replace trigger complaint_advertise
    BEFORE INSERT ON COMPLAINT_INFO FOR EACH ROW
    DECLARE BEGIN
    UPDATE ADVERTISEMENT SET AD_APPEAR='INVALID' WHERE AD_ID=NEW:AD_ID
    END;

But, getting error ERROR at line 2: PLS-00049: bad bind variable 'AD_ID'


texts: How to find out all the postgresql database cluster data directories (running and Shutdown instances) on linux server

texts:
Could you please help us to find below details.

How to find out all the Postgresql database cluster data directories (running and Shutdown instances) on Linux server.

Regards,
Siva.


texts: Solution on a complex sub select query SQL Server

texts:
I created a SQL query that requires me to pull data from 2 separate databases, and I noticed that what I did is this:

Select
    *,
    (Select Z.f From DatabaseZ.TblZ Z where Z.w = A.w) as valueZA,
    (Select Z.k From DatabaseZ.TblZ Z where Z.w = A.w) as valueZB,
From
    DatabaseA.TblA A
LEFT JOIN
    DatabaseA.TblB B A.x = B.x
where
    A.isActiveBit = '1';

The problem I encounter is that the query is slow, due to the fact that Databasez.TblZ contains about 200K records,and that there is new data every 30 minutes. I think that using a subquery here is the main issue, since DatabaseA.TblA also contains a lot of records, the database is already expending a lot of memory and computing power just for the external query, and it takes more time since the subquery also takes a while.

My question is how can I speed up the query, should I change the way I use my sub queries?
I am currently thinking of running the query without the subqueries, then do this:

//PHP Code, just a sample not actual code

//$queryResult is the result of my first query

$subqueryArray = array();

//I make a loop depending on the number of records I got
for($x=0;$x <count($queryResult);$x++)
{
$subqueryArray[] = $this->Main->query("select X.f from Databasez.TblZ Z where ",$queryResult['w']);
//w because it's the field name in the previous query, I'll also do the same for the other subquery
}

I am not sure if the solution I am thinking of is appropriate, since I think the query would take longer if I do it this way. I am also thinking of using indexes but I think that wont make any significant impact on my speed(I am still fairly new with databases) since I need to finish the entire query within 3 seconds(already giving it a lot of time). My current query currently runs at 37 seconds.

What should I do about this?


texts:
I'm connecting to a remote Microsoft server running MsSQL via PHP.  The code looks something like:

$con = mssql_connect('0.0.0.0:123', 'user', 'pass')
    or die(log_mssql_error(__FILE__, __LINE__, mssql_get_last_message()));
mssql_select_db('ShipWorks', $con)
    or die(log_mssql_error(__FILE__, __LINE__, mssql_get_last_message()));

mssql_query($query = "UPDATE [Foo].[dbo].[Bar] SET Biz = 'ABC' WHERE Baz = 'XYZ'")
    or die(log_mssql_error(__FILE__, __LINE__, mssql_get_last_message(), $query));

I receive the following error

  The UPDATE permission was denied on the object 'Bar', database 'Foo', schema 'dbo'.

In the object explorer, in 'Server' > Databases > 'Foo' > Security > Users > 'user' I've set GRANT on all the securables.
And in 'Server' > Databases > 'Foo' properties, I'ved added the 'user' user and given all GRANT permissions as well.

I feel like I've covered all the bases but I'm still getting the permission denied error. What did I miss?


texts:
I need to perform an incomplete recovery. We keep 7 days of backups, and I want to restore the database as it was 7 days ago. I've not done this before.
My question is: I can use the SET UNTIL TIME for the restore / recovery. But, with regards to restoring the control file and SPFile........do I need to restore those separately with SET UNTIL TIME, or will RMAN be smart enough to restore everything I need based on the timeframe I set?
Thank you.


texts: MySQL stores BLOB in innodb buffer pool (innodb_buffer_pool_size)?

texts:
I have several databases all in INNODB, 50GB are BLOB columns, only 700MB are not BLOB data. Tools as mysqltuner ask me to set innodb_buffer_pool_size = 51GB.

Does MySQL store the BLOB in RAM? If yes, how can I configure MYSQL so it doesn't use innodb_buffer_pool_size to store BLOB?


texts: Can two sessions create #temp tables with the same name?

texts:
I am creating a temporary table (#myTable) and using a cursor. Does this create a problem when concurrent users are accessing the cursor through my application? Does it allow me to create separate temp tables with the same name?

Following is the sample code:

Open cursor;
Fetch Next from cursor into @Variable_Temp_Table_Name;
Create table #myTable(pk int)
While @@Fetch_Status = 0
Begin
Fetch Next from cursor into @Variable_Temp_Table_Name;
End


texts:
I am in the next situation: I am using oracle 11g. I am connected to an user, lets say schema1, where are a lot of permissions. I want to create a script which will copy the Package1 (and its body) from schema2 to schema3.

The script will be executed from schema1.

PS: I already look for a solution and I am not interested in export/import or in using other tools from toad, sql developer etc.


texts: MSSQL how do I query sql for a latest state record after latest date

texts:
I have a table with openingdays, location, state (Open, Day Closed, Shift Closed) and the actual date. I would like to know when the day is closed for the last time (state = D) and everything what happens after that date (state Opened, Shift Closed) for the specific location.

location  day          state  actual_date
-----------------------------------------------------
ams,      2015-11-25,  O      2015-11-25 12:20:59.000
ams,      2015-11-25,  D      2015-11-26 02:10:00.000
ams,      2015-11-26,  O      2015-11-26 12:20:59.000
ams,      2015-11-26,  D      2015-11-27 02:10:00.000
sch,      2015-11-25,  O      2015-11-25 14:12:27.000
sch,      2015-11-25,  D      2015-11-26 03:23:13.000
sch,      2015-11-26,  O      2015-11-26 14:12:27.000
sch,      2015-11-26,  S      2015-11-26 18:26:24.000

What I would like to have is

ams,      2015-11-26,  D      2015-11-27 02:10:00.000
ams,      2015-11-26,  O      2015-11-26 12:20:59.000
sch,      2015-11-26,  S      2015-11-26 18:26:24.000
sch,      2015-11-26,  O      2015-11-26 14:12:27.000
sch,      2015-11-25,  D      2015-11-26 03:23:13.000
sch,      2015-11-25,  O      2015-11-25 14:12:27.000

I already created this:

SELECT k.location, k.day, k.state, k.actual_date
FROM myTable k
INNER JOIN (
    SELECT state, max(day) as MaxDate
    FROM myTable k2
    GROUP BY state
) k2 ON k.state = k2.state AND k.day = k2.MaxDate

Which outputs everything on the latest day, but it wont show me the results on the days after the latest Day Closed (state = D).


texts: Most recent fixed date (i.e. 4th July) before given date

texts:
Given a fixed date, for instance 27/04, what would be the best way to get the most recent year that the aforementioned date happened before another given date?

Some examples:

Date A: 27/04
Date B: 25/05/2016
Result: 27/04/2016

Date A: 27/04
Date B: 25/04/2016
Result: 27/04/2015

A bit of background as to why we need this:
I'm correcting an issue in a report to do with insurance claims for the broker I work for. The report joins a few tables, but the two relevant ones to this question are policies which stores the start date of a policy, and reportedclaims which stores the claims that have been reported while the client has been insured with us.
What we need is the start date of the most recent policy year.
Each year when a policy is renewed its start date is advanced by one year. This means that when we run this report the start date of the policy may be after the claim date, which makes no sense. We don't store the original start date, but even if we did we'd have a similar problem if a claim came in 3 or 4 years after the policy was first incepted:

Original start date: 27/04/2012
Current start date: 27/04/2016
Claim date: 26/04/2016
Required start date: 27/04/2015

I could do the calculation in PHP, but since the rest of the report is being created in MySQL I'd prefer not to do that if I can avoid it.

We are currently using a column call uyear which stores the underwriting year that the claim falls under. This is ideal for the task, and saves any weighty calculations in the SQL or the script. Unfortunately I've just found out that this data is unreliable as the people entering it are quite often entering the incorrect year.


texts: Find rows containing all keywords within a string across multiple columns

texts:
I'm trying to create a universal search functionality. The goal is to compare the values of multiple columns and see if all of the provided keywords appear in those columns. For various reasons, using a Full Text Index and CONTAINS is not an option, nor is utilizing a CLR function. Are there any other options to solve this problem?

Example:

----------------------------------------------------
|    Row    |    Col1    |    Col2    |    Col3    |
----------------------------------------------------
|     1     |    Foo     |    Bar     |    Stack   |
----------------------------------------------------
|     2     |    Foo     |    Blitz   |    Stack   |
----------------------------------------------------
|     3     |    Foo     |     Foo    |     Bar    |
----------------------------------------------------

Search text "Foo Bar" matches Rows 1 and 3.

Search text "Foo Stack" matches Rows 1, and 2.

Search Text "Foo" matches Rows 1, 2 and 3.

Search text "Foo Bar Stack Blitz" matches no rows.

Search text "Foo Blitz" matches Row 2.


texts: Setting default value equal to parent's value

texts:
I have a table of categories.  Each category has a parent category (other than the root category) and a color.  I currently have parent set as a FK referring to the key of the same table.

Is there a way to set default color equal to the parent's color?  I've tried setting it up as an after insert trigger or a default constraint, but I'm having issues with self reference in the query itself.  I have a feeling I'm just not 100% understanding how to do it as an inner join.


texts:
We found 3 different size of a MySQL Database Instance as follows:

Mysqldump output
Data directory size (even if we exclude size of bin-logs,error-logs)
SUM (data_length + index_length ) calculated from information_schema.tables

Why we faced these differences and what could be used as my Instance size?

Please help.


texts: How to avoid intermittent "duplicate entry" error on slave when replicating mysql.time_zone_transition?

texts:
Rarely, but intermittently, MariaDB 5.5 fails to replicate the timezone tables onto a completely new replica (instance creation is all automated, so there's no room for missing/changed steps). We never change these internal tables, so how can this happen?

Example result (formatted for readability):

$ mysql -e 'SHOW SLAVE STATUS\G'
[…]
           Master_Log_File: bin-log.000011
       Read_Master_Log_Pos: 5124
            Relay_Log_File: relay-log.000002
             Relay_Log_Pos: 527
     Relay_Master_Log_File: bin-log.000010
          Slave_IO_Running: Yes
         Slave_SQL_Running: No
                Last_Errno: 1062
                Last_Error: Error 'Duplicate entry '339--1956609120' for key
'PRIMARY'' on query. Default database: 'mysql'. Query: 'INSERT INTO
time_zone_transition (Time_zone_id, Transition_time, Transition_type_id) […]


texts:
I have tons of BPR in .xel file (XE) and I want to be able to view them in a graph form and dump them all in SQL Server table, is there any way to do so?
Instead of the 5 sec threshold is there any way I can see the reports coming in  LIVE?


texts: Optimize BETWEEN Query in SQL Server Azure

texts:
I have a table that stores time-series (5-minute) data and am trying to run a query that has a BETWEEN clause included in it. Below is the table structure:-
Timestamp | ComponenentID | Parameter1 | Parameter2 | Parameter3
The table has an index on ComponentId and also a Clustered Columnstore index (Azure S3 and above get this feature).
The query I am trying to run:-
SELECT * FROM table
WHERE Timestamp BETWEEN '2020-01-01'
AND '2020-01-02'

I am looping through multiple similar tables to fetch data and sometimes it takes about 30 seconds to get one day worth of data. Is there anything I can do to reduce this time?


texts:
I have materialized view for which i want to alter its refresh time:

REFRESH FORCE START WITH SYSDATE NEXT SYSDATE +1 DISABLE QUERY REWRITE

I want to know few things in this.

What does NEXT SYSDATE +1 depict?
How am I going to change it (e.g after every 6 hours)?
What is DISABLE QUERY REWRITE?


texts: SQL query with "INTO OUTFILE" does not work on MariaDB 10.4 but does on 10.0

texts:
I have an SQL query that executes perfectly on MariaDB 10.0.35 but fails with a syntax error on MariaDB 10.4.12. I attempted to contact them, but they told me to check forums and compare the changelogs from 10.0 to 10.4. I don't really have the time to scour through dozens of changelog files trying to figure out what is different, especially since their documentation appears to be compatible with what I am doing. Here's the query:

(SELECT 'Name','Address1','Address2', 'City', 'State', 'Zip', 'Phone', 'SSN', 'Credit Card', 'County', 'Comment', 'Company')
UNION (
SELECT name, address1, address2, city, state, zip, phone, ssn, credit_card, county, comment, company
FROM testcust
WHERE type = '0'
ORDER BY name
INTO OUTFILE '/tmp/sellers.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
LINES TERMINATED BY '\r\n'
);

And I even tried re-arranging it to put the INTO OUTFILE clause before the FROM (as it is in the example):

(SELECT 'Name','Address1','Address2', 'City', 'State', 'Zip', 'Phone', 'SSN', 'Credit Card', 'County', 'Comment', 'Company')
UNION (
SELECT name, address1, address2, city, state, zip, phone, ssn, credit_card, county, comment, company
INTO OUTFILE '/tmp/allcustomers.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
LINES TERMINATED BY '\r\n'
FROM testcust
ORDER BY name
);

but that didn't matter. (Both versions work on 10.0, by the way.)

And here is the error from MariaDB 10.4.12:

  ERROR 1064 (42000): You have an error in your SQL syntax; check the
  manual that corresponds to your MariaDB server version for the right
  syntax to use near 'INTO OUTFILE '/tmp/allcustomers.csv'

And here is MariaDB's Doc for "INTO OUTFILE":
MariaDB Doc page
where they have the following example:

SELECT customer_id, firstname, surname INTO OUTFILE '/exportdata/customers.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM customers;

I have disabled secure_file_priv in /etc/my.cnf.d/secure_file_priv.cnf:

[server]
# secure_file_priv = /var/lib/mysql-files
secure_file_priv = ""

I cannot find a way to attach an SQL file for anyone that wants to import it to test this with the above query, but would be glad to send it upon request (unless I am just missing how to attach a file).

I am at a complete loss on this, and cannot seem to find different versions of their documentation to see if there really is a syntax difference -- I can only assume their online docs are for the latest version.

I would really appreciate any enlightenment anyone might be able to bestow on me...


texts: MySQL dies when trying to access a specific database

texts:
For some reason, whatever I do I cannot access one of my databases (through command line or through something like phpmyadmin). However, the application that depends on this database is not having any issues and is working perfectly.

I've tried a few things to have it connect

$ mysql -u root -p

$ use my_database

$ show tables

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: my_database

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
ERROR:
Can't connect to the server

I really am only trying to get the dump of the database

$ mysqldump -u root -p my_database | gzip > my_database.sql.gz

this gives me the following error

mysqldump: Got error: 2013: Lost connection to MySQL server during query when using LOCK TABLES

I've also tried

mysqlcheck -u root -p -A

It goes through all the tables until it tries to access the tables on my_database where it fails and throws this error

mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ... '

I just can't figure out what's wrong. If there is any other way of backing this database up please let me know or how to fix these errors.


texts:
We are using TSM/TDPO SBT to backup Oracle Databases. When using Oracle Data Guard with TDPO, what is the best strategy to ensure backups continue after failover/switchover?
TSM/TDPO uses a feature called datamove which makes sure that backups from primary and standby are moved to one TSM node. (TSM node A (primary) and B (standby) move there Backups to C. Restore will use C).
The RMAN scripts called by TDPO need a logic to prevent backups are taken from both (primary and standby). This could be done by querying the role of the database.


texts: SQL Server 2012 is database name mandatory for logon?

texts:
is the database name mandatory SQL Server 2012 logon - like SERVERNAME\DBNAME ?
On 2005 Server the servername for logon with Windows credentional has been sufficient.

Thanks in advance,
 Tom


texts: MongoVUE connect to mongodb with authencation failed!

texts:
I had a mongodb, and a database named genbank. Genbank has a user whose role is "read" and "readAnyDatabase". It is very strange that I cannot connect to genbank through this role by MongoVUE, but I can connect successfully under administrator role.

The connection configuration is:

genbankReader is the username for genbank database.

the error message is:

Unable to connect to server *.*.*.*:40000: Invalid credential for database 'admin'..
Type: MongoDB.Driver.MongoConnectionException
Stack:    在 MongoDB.Driver.Internal.DirectMongoServerProxy.Connect(TimeSpan timeout, ReadPreference readPreference)
   在 MongoDB.Driver.MongoServer.Connect(TimeSpan timeout)
   在 MongoDB.Driver.MongoServer.Connect()
   在 MangoUI.MMongo.m7FnMeKibMFwcOmRDx4q(Object )
   在 MangoUI.MMongo.Open(Boolean useSamus)
   在 MangoUI.MMongo.Open()
   在 MangoUI.ComNavTree.d0Pck1r6Vh91JYOyd29(Object )
   在 MangoUI.ComNavTree.ExpandMe(MTreeNode expand)
   在 MangoUI.ComNavTree.tree_BeforeExpand(Object sender, TreeViewCancelEventArgs e)

Invalid credential for database 'admin'.
Type: MongoDB.Driver.MongoAuthenticationException
Stack:    在 MongoDB.Driver.Communication.Security.MongoCRAuthenticationProtocol.Authenticate(MongoConnection connection, MongoCredential credential)
   在 MongoDB.Driver.Communication.Security.Authenticator.Authenticate(MongoCredential credential)
   在 MongoDB.Driver.Communication.Security.Authenticator.Authenticate()
   在 MongoDB.Driver.Internal.MongoConnection.Open()
   在 MongoDB.Driver.Internal.MongoConnection.GetNetworkStream()
   在 MongoDB.Driver.Internal.MongoConnection.SendMessage(BsonBuffer buffer, MongoRequestMessage message, WriteConcern writeConcern, String databaseName)
   在 MongoDB.Driver.Internal.MongoConnection.SendMessage(MongoRequestMessage message, WriteConcern writeConcern, String databaseName)
   在 MongoDB.Driver.Internal.MongoConnection.RunCommandAs[TCommandResult](String databaseName, QueryFlags queryFlags, CommandDocument command, IBsonSerializer commandResultSerializer, IBsonSerializationOptions commandResultSerializationOptions, Boolean throwOnError)
   在 MongoDB.Driver.Internal.MongoConnection.RunCommandAs[TCommandResult](String databaseName, QueryFlags queryFlags, CommandDocument command, Boolean throwOnError)
   在 MongoDB.Driver.MongoServerInstance.Ping(MongoConnection connection)
   在 MongoDB.Driver.MongoServerInstance.Connect()
   在 MongoDB.Driver.Internal.DirectMongoServerProxy.Connect(TimeSpan timeout, ReadPreference readPreference)

Command 'authenticate' failed: auth fails (response: { "code" : 18, "ok" : 0.0, "errmsg" : "auth fails" })
Type: MongoDB.Driver.MongoCommandException
Stack:
Inputs::
Command:  authenticate
Ok:       False
ErrorMsg: auth fails
Request:  { "authenticate" : 1, "user" : "genbankReader", "nonce" : "264abe28fc4dd95", "key" : "ca2124ded060247d527941e55820fd80" }
Response: { "code" : 18, "ok" : 0.0, "errmsg" : "auth fails" }

My db user always uses mongoVUE but I cannot give him other privilege except for read. So, can any one help me solve my problem?


texts: PostgreSQL: mark current session as "unimportant"

texts:
If I want to drop a PostgreSQL DB I get this error:

postgres@server:~$ dropdb c123_d
dropdb: database removal failed: ERROR:
        database "c123_d" is being accessed by other users
DETAIL:  There is 1 other session using the database.

I know how to help myself. I can do select * from pg_stat_activity; and see what is still running.

In my case it was a LISTEN.

Is there a way to mark the LISTEN-statement or this session as "unimportant"? In this context this means that the DB is allowed to kill this statement if the database should get shut-down or dropped.

Is there a way to tell PostgreSQL this?


texts: Parse file with non-standard layout into table

texts:
I have a file that I am getting from a vendor that I need to put into an SQL Server table. The data in the file looks like this:

{"result":"success","ReqCtx_requestURI":"somethinghere","eventDate":"2018-04-23","timeStamp":"2018-04-23T00:36:21.012-07:00"}  {"member":"123456789","ReqCtx_canonicalId":"03539","ReqCtx_userId":"xxxxxxxxxxxxx"}

The format is "columnname":"value". My table has all the possible columns in it. What I am trying to do, if it is possible is to create sql insert statements based off of this info. Something like

insert into table(result,ReqCtx_requestURI,eventDate)
 values(success,somethinghere,2018-04-23)
insert into table(member,ReqCtx_canonicalId,ReqCtx_userId)
values(123456789,03539,xxxxxxxxxxxxx)

Does anyone have any idea on how I could go about this?


texts: How to view user created table in OracleDB?

texts:
So I created a table in my OracleDB, however when I use the command to view all tables in my environment, it gives a bunch of other tables, scrolling and filling my command prompt with lot of other tables.
Is there any other command to view just the tables that I created?
This is what I am doing currently
C:\> set ORACLE_SID=ocptech;
C:\> sqlplus / as sysdba
SQL> CREATE TABLE customers
     ( customer_id number(10) NOT NULL,
       customer_name varchar2(50) NOT NULL,
       city varchar2(50)
     );
SQL> SELECT table_name
     FROM user_tables
     ORDER BY table_name;

The above command gives me a lot of tables.
Also can someone also explain me, what these tablespace are while executing the following command
SELECT TABLESPACE_NAME FROM DBA_TABLESPACES


texts: BM25 (full-text search) implementation in SQL Server

texts:
I've hit a stump, while trying to implement the BM25 algorithm in SQL Server 2008 R2. I know that SQL Server includes the Full-Text Search option, which already implements a variant of BM25, but I would like to do some parameter tuning tests and since the FTS procedures are non-editable (as far as I know), I've decided to implement it myself.

I have two tables, TF (term frequency) and DF (document frequency) with the following structures:

TF

*Note: the weight column denotes importance of the word (it is usually 1)

ID | Term | DocumentID | Count | TermID | Weight*

DF

ID | Term | Count

The TF table contains the relationship between a term and a document; that is, the frequency of the term in a document. The DF table contains information about how many documents contain a term. The two tables can be linked using DF.ID and TF.TermID. Using these two tables I would now like to compute BM25 similarity values between two documents (one document acts as a query) according to the formula in the Wikipedia article. The tables TF and DF translate to functions f(q, D) and n(q) respectively:

I would like the result to be in this format:

DocumentA_ID | DocumentB_ID | BM25_Value

Here is some code that I have up to now:

DECLARE @N FLOAT;
DECLARE @AVGDL FLOAT;
DECLARE @K1 FLOAT;
DECLARE @B FLOAT;
SET @K1 = 1.2;
SET @B = 0.75;

-- number of all documents
SELECT @N = COUNT(DISTINCT DocumentID) FROM TF;

-- average document length (in words)
SELECT @AVGDL = AVG(DocumentLength) FROM (SELECT DocumentID, SUM(TF.Count) AS DocumentLength FROM TF WHERE Weight = 3 GROUP BY DocumentID) A;

-- BM25 implementation
SELECT  D.DocumentID AS DocumentA,
        Q.DocumentID AS DocumentB,
        *,
-- need help here (SUM or something ...)
        LOG((@N - DF.Count + 0.5)/(DF.Count + 0.5)) AS IDF
FROM TF AS D
        INNER JOIN TF AS Q ON D.Term = Q.Term
        INNER JOIN DF ON D.TermID = DF.ID
WHERE   D.DocumentID <> Q.DocumentID

I'm having trouble constructing the query in the last section (BM25 implementation) to get the desired result format. Any help would be greatly appreciated.


texts:
I'm new to Oracle database. I'm trying to install the 12c version, on Windows 64, but I'm getting the error shown in the picture

I've already searched for this error on Google, but there is no related topics. Does anyone know how to solve this problem please ? Thank you


texts: Is it better to install a very large Postgres database (billions of rows) in a real or a virtual machine?

texts:
From my common sense, my physical machine hosting a very large Postgres database (for big data) should be real rather than virtual or container.

Some people say it is better to install on a virtual machine or a container.  I would like to get opinion from those who works with database every day. I am looking for performance over everything else.

Another question is about the storage: external or internal? I would think real internal disks with the RAIDs. Not sure external storage with logical volumes are better or not.


texts: Disallowing table creation in the public schema

texts:
Postgres version: 9.3.16

Let's say we have two users, luser and editor. I'd like to make it so that luser (or any other non-superusers) cannot create any tables under the public schema, except editor. When I apply the following as the postgres user, I achieve this:

postgres=> select current_user;
 current_user
--------------
 postgres
(1 row)
postgres=# REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE
postgres=# GRANT ALL ON SCHEMA public TO editor WITH GRANT OPTION;
GRANT
postgres=# SET ROLE luser;
SET
postgres=> create table public.test (uid integer);
ERROR:  permission denied for schema public
postgres=> SET ROLE editor;
SET
postgres=> create table public.test (uid integer);
CREATE TABLE
postgres=> \d+
                   List of relations
 Schema | Name | Type  | Owner  |  Size   | Description
--------+------+-------+--------+---------+-------------
 public | test | table | editor | 0 bytes |
(1 row)

However, when if I try to run REVOKE ALL ON SCHEMA public FROM PUBLIC; as the editor user, this does not work (i.e. luser can create a table under the public schema):

postgres=> SET ROLE postgres;
SET
postgres=# GRANT ALL ON SCHEMA public TO editor WITH GRANT OPTION;
GRANT
postgres=# SET ROLE editor;
SET
postgres=> REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE
postgres=# SET ROLE luser;
SET
postgres=> create table public.test (uid integer); # I expect this to fail with `ERROR:  permission denied for schema public` but it works somehow!
CREATE TABLE
postgres=> \d+
                   List of relations
 Schema | Name | Type  | Owner |  Size   | Description
--------+------+-------+-------+---------+-------------
 public | test | table | luser | 0 bytes |
(1 row)

So, even though I can run REVOKE ALL ON SCHEMA public FROM PUBLIC; as the editor and it doesn't give any errors, luser can still create a table under the public schema. How can this happen?

Prior research:

From how do you revoke create table from a user on postgresql 9.4? :

  Also note the user/role executing the revoke also needs to be the
  owner of the schema (so you can check that as well).

Maybe this is the reason? But I'd still expect Postgres to give some kind of error when running REVOKE ALL ON SCHEMA public FROM PUBLIC; as the editor (it does, when I try to run this as the luser user)...


texts:
I have a cursor which gives parameters to columns to executes the stored procedure in order to update my table. Cursor statement is following:

declare @ColumnName nvarchar(30)
declare @ColumnID int

declare UpdateCurs cursor
for
select ColumnName , ColumnID from MyTable

open UpdateCurs

fetch next from UpdateCurs into @ColumnName , @ColumnID

while @@FETCH_STATUS = 0
BEGIN

  execute dbo.UpdateMyTable @ColumnName , @ColumnID

fetch next from UpdateCurs into @ColumnName , @ColumnID

END

close UpdateCurs
deallocate UpdateCurs

When I execute the statement directly, it works fine, but when I create maintenance plan via "Execute T-SQL statement" and schedule job, the job results with  following error:

  failed with the following error: "Invalid column name 'ColumnID'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

One thing I can`t understand that, if there is something wrong with my statement, why it is executed without problems directly and gives error on Maintenance plan ?
I will appreciate your thoughts on this


texts: SSIS Ensure a sequence container is not marked as failed if a specific task fails

texts:
Consider the following scenario for an SSIS package:

A Sequence Container contains two jobs for loading data from an Excel file:

Job A: Attempts to load data from an Excel file following a specific file definition.
Job B: In case JOB A fails, Job B will be executed instead.

Outside the container, a final task is executed to reset data.

The issue I have, however, is that if JOB A fails, even though JOB B takes over and manages to process the file correctly, the container is marked as a failure.

Although I can circumvent this issue with the use of some variables and "Precedent Constraint" (this can be seen in the second screenshot above), I wonder if there is a more elegant way, in which I simply ensure that the Sequence Container is not marked as failed is the first task of loading data fails.

Now, the first thing that comes to mind for me is the use of MaximumErrorCount property on the container. However I have other tasks within the container and if they fail it could mess with the logic.

Perhaps nesting yet another container for just JOBS A and B within the existing container would be the way forward?

But really (I'm a hopeless idealist) what I was hoping to find is some sort of property for JOB A that says if job A fails, don't mark the container as a failure in the simplest way possible. Thoughts?


texts: Oracle partition by interval using persian calendar

texts:
Oracle supports Persian (Jalali) calendar in DDL queries, I can easily say:

select to_char(register_date, 'YYYY-MM-DD', 'nls_calendar=persian')
  from my_table;

I created a table as:

create table test_temp_times (
  id number(18) not null,
  xdate date not null,
  str varchar2(20))
partition by range(xdate)
interval(NUMTOYMINTERVAL(1, 'MONTH'))
(partition p0 values less than (to_date('13920101', 'YYYYMMDD', 'nls_calendar=persian')))
enable row movement;

the table is created normally, but when I add records to it, and oracle creates new partitions, the partitions are:

create table TEMP_TIMES (
  id    NUMBER(18) not null,
  xdate DATE not null,
  str   VARCHAR2(20)
)
partition by range (XDATE)
(
  partition P0 values less than (TO_DATE(' 2013-03-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition SYS_P61 values less than (TO_DATE(' 2013-04-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition SYS_P62 values less than (TO_DATE(' 2013-05-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition SYS_P63 values less than (TO_DATE(' 2013-06-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition SYS_P64 values less than (TO_DATE(' 2013-07-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));

as you can see, the database changed the NLS_CALENDAR to gregorian (the same day as 13920101) and each partition is created according to gregorian calendar, not persian calendar.

Is there any way to force Oracle to use persian calendars for creating new partitions?


texts: How to update column with ascending value column

texts:
I would like to add an order column wich will be position of my record. Positions are connected with name. If the name is S1 - order should be 1, for S2 - order should equal 2 etc. How can I do this?

Real state:

id       name     position
-------  -------  -------
1        S4       null
2        S2       null
3        S3       null
4        S1       null

Expected:

id       name     position
-------  -------  -------
1        S4       4
2        S2       2
3        S3       3
4        S1       1

I tried with update:

WITH x AS
(
  SELECT
      id,
      name,
      ROW_NUMBER() OVER (ORDER BY name) position
  FROM
      stage
)
UPDATE
    stage
SET
    position = x.position
FROM
    x
WHERE
    x.id = stage.id;

Stage is the name of the table.


texts: Support ticket system - when/how to move old tickets?

texts:
I don't have access to the partitioning feature, but consider a support ticket system with tens of thousands of tickets being opened everyday and taking about a week to a couple of months in getting resolved. Obviously, if I tried to keep them all in one table ever since the beginning, the table would become HUGE.

My questions are:

Once a ticket gets resolved, should I move it out of the original table; or
Should I just move resolved tickets out of the original table after they are 90 months old; or
Should I move everything whether resolved or unresolved into another table after 90 months and do some kind of UNION every time unresolved queries are sought?


texts:
Consider an Oracle database that has a person, a date and a value in every record. When I pull a record, I want to very quickly and easily get the previous record where person is the same in both the one I fetched and the previous and date is the maximum date that comes before the record I fetched. When I ask for this, there is usually a hell of a lot of confusion, so here is an example data set:

PERSON,DATE,VALUE
1,2013-12-25,823
2,2013-11-20,384
1,2013-11-10,4837
1,2013-10-01 377
1,2014-01-01,8347

Now, when I fetch the first record, I want to also get the record 1,2013-11-10,4837 because person matches (as 1, not 2), and the date of the previous record is the maximum date that comes before the record I fetched.

I have tried using the connect clause, but it doesn't give the ability to ask for "the record with the maximum date that is less than the date of the currect record." The multiple join method that I'm using isn't functional because there are too many records - it takes hours to complete a single query.


texts: Fast database for a small microcontroller

texts: I am using a PIC32, which is a 32-bit processor clocked at 80 MIPS with about 64-128KB of RAM available. It will be accessing a microSD card - up to 4 GB, on a FAT32 filesystem. Running all of this is pushing it, but I need a compact database that can be easily ported to this platform and one which is fast. Does anyone have any suggestions?


texts: Is there a place to store ERDs in Postgres?

texts:
Is there a designated spot in Postgres to store Entity-Relationship Diagrams (ERDs)?

I could just create a table with a BTYEA column or something, but I want to make sure to use what exists if something already exists to document the database.


texts:
For a SQL 2000 server, is it bad to see too many

  exec sp_releaseschemalock 1

events in a SQL trace file?


texts: SQL Server 2014 In Memory OLTP - Garbage Collection on Transaction Rollback

texts: In SQL Server 2014, In Memory OLTP, What happens, if a transaction is rolled back and newly created version of row is no more required. Is it duty of garbage collector to remove even such type of rows or such garbage will be collected run time on transaction rollback ?


texts: Database design: same products in inventory but with different cost

texts:
I am trying to design a database for a Stock and Sale System and I have the following issue.

Suppose there is an item in stock that costs $ 100 (product cost, not the selling price), but there are only 3 pieces to sell, then the customer decides to buy another batch of the same product, but this time the lot comes to a different price, $ 76 for example.

The purchase cost is very important, because on it the percentage of profit is calculated, so if the cost is different, the selling price will be different too.

I wonder how to make the seller sell each piece to its corresponding price.

I am a novice in designing databases and I have the following tables:

PRODUCT     -> Record all products, whether or not available in stock.

product_id (PK)

PURCHASE    -> Record all purchase of new products to sell.

purchase_id(PK)
product_id(FK)

SALE        -> Record each sale operation

sale_id (PK)

SALE_ITEM   -> Records which product was sold in a sell operation.

sale_item_id(PK)
product_id(FK)

The SALE_ITEM table is related to the PRODUCT table through an attribute product_id.

So, should I relate the sale of each product with the identifier of the table PURCHASE instead of the identifier in PRODUCT table?

Or, should I force the client to sell the old lot before selling the new ones?

How you would handle this situation?

Thanks, and sorry for my english, I am not a native.


texts: How to select maximum row value using optimal way

texts:
I have a large table:
+----+-----------+------------+--------+--------+--------+
| Id | lClientId | datAccess  | lProp1 | lProp2 | lProp3 |
+----+-----------+------------+--------+--------+--------+
|  0 |         5 | 10/10/2020 |        |        |      9 |
|  1 |         5 | 10/11/2020 |      2 |        |        |
|  2 |         5 | 10/12/2020 |      2 |        |        |
|  3 |         5 | 10/12/2020 |      3 |     10 |        |
|  4 |         6 | 10/13/2020 |      3 |     10 |      8 |
|  5 |         6 | 10/29/2020 |        |     11 |        |
|  6 |         6 | 10/15/2020 |      9 |     13 |        |
|  7 |         6 | 10/16/2020 |        |        |     16 |
+----+-----------+------------+--------+--------+--------+

I need to extract each prop for each clients (lProp1 - lProp3 fields) accociated with the last date with the last date of access (datAccess can be repeated, in this case i need the last one (by Id)). I.e. the desired result is:
+-----------+------------+--------+--------+--------+
| lClientId | datAccess  | lProp1 | lProp2 | lProp3 |
+-----------+------------+--------+--------+--------+
|         5 | 10/12/2020 |     3  |   10   |      9 |
|         6 | 10/29/2020 |     9  |   11   |     16 |
+-----------+------------+--------+--------+--------+

I tried to create several subqueries with SELECT which selects the maximum of each column with corresponding maximum date but the performance is falling down. Is there a way to select desiried values by single query? Because this is very expensive to go thru all the rows in table each time it search maximum value of date. The single pass is enough to get desiried values.
I use ADODB and VBA so maybe it's better to go thru all the rows and extract desiried fields manually? Using Recordset.MoveNext i can check all the fields by using single pass. Just i thought the engine is faster than if i access each field by Recordeset/Variant/etc.
The database is MS ACCESS.
Thanks in advance.


texts: How to enable Oracle Database In-Memory feature in Oracle 11g R2 to reduce report generation time?

texts:
Problem:
Each report generation takes a few hours in Oracle 11g R2 and a few tens of reports has to be generated every day.

Objective:
To reduce report generation time

Understood:
a) "reports that used to take hours can now be executed in seconds using oracle in-memory database feature".
(Reference: https://blogs.oracle.com/database/oracle-database-18c-%3a-now-available-on-the-oracle-cloud-and-oracle-engineered-systems?elq_mid=104407&sh=26961462026142651578242315&cmid=WWMK170418P00047)
b) The feature is introduced in Oracle 12c and is available in its later versions.

Please advise on:
a) how in-memory/cache table feature of 11g and database in-memory of 12c are related? former one can be used to improve report generation performance?
b) Procedure to enable the in-memory database feature in Oracle 11g R2?
c) Procedure to make standby database of 11g to 12c?


texts:
I have database of 800GB average but when I run a backup job (of system and user databases) the transaction log backup reports a failure: There is not enough space on the disk.

When I make a transaction log backup the size of this would decrease or do I need a shrink (after or before run a backup)?