texts: What is the advantages of using auto_increment in database as primary key over set of attributes?

texts:
I have table as

CREATE TABLE circle
(
    circle_id int(11) auto_increment primary key not null,
    user_id int (11) not null,
    title varchar(255) collate  utf8_general_ci not null
);

CREATE TABLE circle_share1
(
    id int(11) auto_increment primary key not null,
    circle_id int(11),
    user_id int(11),
    start_time date
);

CREATE TABLE circle_share2
(
    circle_id int(11),
    user_id int(11),
    start_time date,
    primary key(circle_id , user_id)
);

What are the advantages of using table share1 over share2 ?

share2 has primary key of circle_id , user_id

and share1 has primary key id .

I checked 50 tables out of them none of the table is like share2  , Why developers are not preferred  share2 ?


texts: How to Skip Existing Data While Restoring mysqldump File?

texts:
I use this query to import data using MySQL Dump (Command Prompt / Command Line)
mysql -u root -p database_name < database_name.sql

I want to import the large database (only new deltas) as of now I drop database and re-import but I want to import only new deltas.


texts: Is it possible to choose RAISERROR or THROW depending on SQL Server version?

texts:
Here's my code right now:

BEGIN TRY
INSERT INTO TABLE (F1,F2,F3)
VALUES ('1','2','3')
END TRY
BEGIN CATCH
;THROW
END CATCH

Works great, unless it's run on a machine with SQL 2008. I'd like to have the CATCH block do a check against the SQL version and run THROW if it's equal or higher to 2012, and RAISERROR if it's 2008. I keep running into syntax errors, and I'm wondering if it's even possible. Even something simple like this is not working for me.

BEGIN CATCH
IF ((SELECT SERVERPROPERTY('productversion')) >= 11) ;THROW
END CATCH

Any advice is appreciated.


texts: Cannot Generate SSPI Context when connecting to the remote Computer

texts:
I am connecting to Windows Server 2008 R2 which has SQL server 2005 installed via ODBC from a client machine. Recently I started getting this error

  Cannot generate SSPI Context.

I have gone through similar questions but have not got any helpful answer.


texts: How to change permissions of Oracle generated logs?

texts: The oracle generated logs (ie. alert and listener logs) are being created with 600 permissions. These files are of course created automatically by the oracle user. How would I change the permissions to automatically be 640, for instance, in a Solaris 10 environment? This would allow DBAs to read them and such without logging into the oracle user account.


texts: "Merging" grouped rows (column-wise, picking the first non-null value, ordered by another column)

texts:
I'd like to apologize in advance for the poor title, but I had a hard time trying to describe my problem.

I have a table like this:

+---+--------------+--------------+-------+-------+-------+
|   | RECORD_LEVEL | RECORD_GROUP | COL_A | COL_B | COL_C |
+---+--------------+--------------+-------+-------+-------+
| 1 |            1 | group1       | NULL  | 0     | bar1  |
| 2 |            2 | group1       | foo2  | -1    | bar2  |
| 3 |            2 | group2       | foo3  | NULL  | NULL  |
| 4 |            5 | group2       | foo4  | 5     | bar4  |
| 5 |            1 | group3       | NULL  | 1     | NULL  |
| 6 |            2 | group3       | foo6  | 1     | NULL  |
+---+--------------+--------------+-------+-------+-------+

And I need to group it by RECORD_GROUP, using the first non-NULL value for each column. By 'first', I imply an ORDER BY RECORD_LEVEL ASC. The result I'd like is something like this:

+---+--------------+-------+-------+-------+
|   | RECORD_GROUP | COL_A | COL_B | COL_C |
+---+--------------+-------+-------+-------+
| 1 | group1       | foo2  |     0 | bar1  |
| 2 | group2       | foo3  |     5 | bar4  |
| 3 | group3       | foo6  |     1 | NULL  |
+---+--------------+-------+-------+-------+

I tried to reproduce this scenario in this fiddle: http://rextester.com/UMMP61375

Please, feel free to ask for more information or premises.


texts: Can SQL Server replication be used for Point-of-Sale (POS)?

texts:
We are looking for a solution that will synchronize the databases of our POS.

The scenario says we have 3 POS with SQL Server Express installed on each machine.

We want these databases to be in-sync at all times. Let's say POS A goes down, then it will resync as soon as it is up. Also, while POS A is down POS B and C should still work and continue to sync their data.

Is SQL Server replication the right solution? If yes, what type of replication?

Updated

No central database
3/4 nodes/POS are down, remaining POS will still work
Databases will be restored if POS is online
Pretty much we are think like MongoDb replica set
Database should be updated as much as possible


texts: MySQL starting 2 processes that can't be used

texts:
I am running MySQL on CentOS. When I attempt to start it gives an error but in the background 2 processes are started:

Stop:

$ sudo service mysqld stop
[sudo] password for arifu:
Redirecting to /bin/systemctl stop  mysqld.service

$ ps -aux | grep mysql
arifu    20332  0.0  0.0 112620   744 pts/0    S+   13:08   0:00 grep --color=auto mysql

Start:

$ sudo service mysqld start
Redirecting to /bin/systemctl start  mysqld.service
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

Logs:

$ systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: deactivating (final-sigterm) (Result: exit-code)
Process: 17465 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=1/FAILURE)
Process: 17443 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
CGroup: /system.slice/mysqld.service
        └─17468 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid...

$ ps -aux | grep mysql
mysql    17798  0.0  0.0  53032  3620 ?        Ss   13:03   0:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
mysql    17800 89.6  6.7 39831592 3308524 ?    Rl   13:03   0:02 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
arifu    17826  0.0  0.0 112620   744 pts/0    S+   13:04   0:00 grep --color=auto mysql

None of these can be used:

$ mysql -h localhost -u root -p mysql
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)

Any idea?

EDIT

I managed to trace the problem to wrong configuration - join_buffer & malloc-lib:

join_buffer=256M
malloc-lib=/usr/lib64/libtcmalloc_minimal.so.4

I had copied these when I migrated from MySQL 5.6 but it appears they no longer work for 5.7


texts: Full Text Catalog Indexes Not Populating

texts:
I ran sp_Blitz on a server and it indicated that full text indexes were not populating.  I got a list of the indexes that are not populating and looked at the definitions of the indexes.  The syntax of those indexes does not look like BOL examples for full text index creation, but instead they look like traditional row indexes.  And they are on BigInt data types not text. Here is the code that returns the full text indexes that are not populating.

SELECT FTCatalogName = c.name
      ,TableName     = t.name
      ,IndexName     = i.name
      ,LastCrawlEnd  = fi.crawl_end_date
 FROM sys.fulltext_indexes fi
 JOIN sys.tables t
   ON t.[object_id] = fi.[object_id]
 JOIN sys.fulltext_catalogs c
   ON fi.fulltext_catalog_id =  c.fulltext_catalog_id
 JOIN sys.indexes i
   ON fi.unique_index_id = i.index_id
  AND fi.[object_id] = i.[object_id]
   WHERE fi.is_enabled = 1
     AND fi.crawl_end_date < DATEADD(DAY, -7, GETDATE())
ORDER BY fi.crawl_end_date, t.name, i.name;

I'm not particularly familiar with full text indexing but it seems sp_Blitz is highlighting full text index issues that aren't really full text indexes.  Just confused by what I'm seeing.

Here is an example of the DDL of one of the indexes that is identified as a full text index.  but, it appears to me to be a traditional row based index.  The DDL doesn't look like what would be used to create a full text index.

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id =
OBJECT_ID(N'[dbo].[tblIntAnalysedItems_106]') AND name = N'PK_tblIntAnalysedItems_106')
ALTER TABLE [dbo].[tblIntAnalysedItems_106] ADD  CONSTRAINT
[PK_tblIntAnalysedItems_106] PRIMARY KEY CLUSTERED
(
[RowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =
ON, FILLFACTOR = 70)
ON [SomeObject]


texts:
I have query which has 40+ left outer joins. Query is not that slow but I'm interested that can I optimize this (procedure is most used one in db). I had idea that "a" table has type column which value basically says that there is data in c or d tables. So I thought that I add AND a.type LIKE 'type1' and AND a.type  LIKE 'type2' to b and c tables join clause. But I don't see any improvements and i do not understand why. every table has pk(id), Query returns 1 row and 2000+ columns.

Query is like this :

SELECT 'text1,text2,text3' cola, a.id,a.type, a.data1 ..
, 'text12,text14...' as colb, b.data,b.data2 ..
, 'textc1,textc2...' as colc , c.data,c.data2 ...
-- 40+ tables with same logic
FROM a
LEFT OUTER JOIN b on a.id = b.id
LEFT OUTER JOIN c on a.id = c.id
LEFT OUTER JOIN d on a.id = d.id
--repeat 40 times to different tables, always using a.id to join
WHERE a.id = 2

I tried some thing like:

SELECT 'text1,text2,text3' cola, a.id,a.type, a.data1 ..
, 'text12,text14...' as colb, b.data,b.data2 ..
, 'textc1,textc2...' as colc , c.data,c.data2 ...
-- 40+ tables with same logic
FROM a
LEFT OUTER JOIN b on (a.id = b.id and a.type LIKE 'typeb')
LEFT OUTER JOIN c on (a.id = c.id and a.type LIKE 'typec')
LEFT OUTER JOIN d on (a.id = d.id and a.type LIKE 'typed')
--repeat 48 times to different tables, always using a.id to join
WHERE a.id = 2

If there is no id in table then it returns col(table) which is text and several nulls.
Plan generated is clusteded index seek  and nested loop for every join in query (50 times). Every clustered index seek takes 2% of overall execution time.  exec time is 300ms.

QUESTION: Can this query run faster

Partial solution
 @ypercubeᵀᴹ recommended following test , which currently seems to be better solution :

SELECT * FROM a
OUTER APPLY (SELECT * FROM b WHERE a.id = b.id) b
OUTER APPLY (SELECT * FROM c WHERE a.id = c.id) c
OUTER APPLY (SELECT * FROM d WHERE a.id = d.id) d
-- repeat ~50 times
WHERE a.id = 1000

SQL Sentry plan explorer gives results that OUTER APPLY was twice as fast than original version with LEFT JOIN when comparing Duration and CPU values. Estimated costs when all test queries were run in same time had exactly same cost.
OUTER APPLY did 30% less reads and writes compared to LEFT OUTER JOIN (two versions, original and 'SELECT * FROM a LEFT OUTER JOIN b ON a.id =b.id etc..)

Adding "AND a.type = 'typeb'" to OUTER APPLY QUERY so that planner would not try seek table's index, did not work. Query with or without second test clause (added to each OUTER APPLY) had same plan and same performance.

OUTER APPLY version of plan is Clustered Index Seek on every table was followed with Compute scalar which was followed nested join loop like original execution plan was.

POSSIBLE ANSWER: Without changing query "logic" OUTER APPLY seems to be faster than LEFT OUTER JOIN when you join over 30 tables like this. Additional test in WHERE did not speed things up. Plan generation time when executing QUERY were more than execution time itself . So plan which checks id in every table index is best solution because generating new plan would take more time than it saves.


texts: SQL Server 2008 Full Text Index Never Seems to Complete

texts:
Our website has a SQL Server 2008 R2 Express Edition database with full-text indexing for our website search.  Each time a new record is added or updated in one of the indexed tables, the indexing process never seems to complete.

I have been monitoring the status over the last several weeks using basically the same query found on this site: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-search/2155/Why-is-this-population-taking-so-long

This is what I see when I run the query (click for full size):

The newest records in the indexed tables never complete and are not searchable. Even though there is not very much data in the tables, I have waited days to see if the indexing completes, but nothing ever changes.

The only way I am able to get the indexing to complete successfully is to either rebuild the catalog or to drop and re-create all of the indexes.

Every time I have done that, the same problem ends up coming back as soon as the first new record is added.

Here are the server stats just in case:

Quad-Core AMD Opteron 2.34GHz
4GB RAM
Windows Server 2008 R2 Enterprise SP1 x64
SQL Server 2008 R2 Express Edition with Advanced Services x64


texts: how to dump mysql schema without keys and without constraint ? and keep all of them to alter afterwards

texts:
i am using ubuntu 16.04 and want to dump large schema with hounded of tables from AWS rds, remove all keys and foreign key , load data and then add back the keys , is there any automatic tool that does this? or any well known script for this ? i am currently using mysqldump https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html with this command :

mysqldump --skip-triggers --compact --no-create-info --single-transaction --quick --max_allowed_packet 1G  -h host_name.rds.amazonaws.com -u <user> -P 3306 -p<pwd> <schema> {table_name}> /mnt/tables_just_data/{table_name}.sql


texts:
I have been studying the normal forms rules to guide me with database design and data storage, the problem I am having is that once I optimize for the first normal form (1NF), I figured I may well just go the whole way(even though not mandatory).

However a occurrence showed in my design that I haven't came across in examples online or books.

A example concept.

We want to store all data about the some car variations.

We must store values make, series, model, start year, end year.

Unormalized

+------+--------+-------+------------+----------+
| make | series | model | start year | end year |
+------+--------+-------+------------+----------+
| BMW  |   5    |  E12  |    1972    |   1981   |
| BMW  |   5    |  E28  |    1981    |   1988   |
| BMW  |   5    |  E34  |    1988    |   1996   |
| BMW  |   5    |  E39  |    1995    |   2004   |
+------+--------+-------+------------+----------+

Normalized

car_make

+----+------+
| id | make |
+----+------+
| 1  | BMW  |
+----+------+

car_series

+----+--------+---------+
| id | series | make_id |
+----+--------+---------+
| 1  |   1    |    1    |
| 2  |   3    |    1    |
| 3  |   5    |    1    |
| 4  |   7    |    1    |
+----+--------+---------+

car_model

+----+-------+------------+----------+-------- --+
| id | model | start year | end year | series_id |
+----+-------+------------+----------+-----------+
| 1  |  E12  |    1972    |   1981   |     3     |
| 2  |  E28  |    1981    |   1988   |     3     |
| 3  |  E34  |    1988    |   1996   |     3     |
| 4  |  E39  |    1995    |   2004   |     3     |
+----+-------+------------+----------+-----------+

One problem arises here in the last table, should I also include a column make_id in the car_model table?

It is beneficial as I would not have to join car_series, then select make_id, to select make by id, however I believe this may not be sticking to normalization, because I would be repeating data(even though it is a id its still repeat).

Can this design be normalized further?

On another note with regards to the start year, end year handling, How would you professionals do this? I am curios if there is any range tricks to enforce integrity(although the E39 overlaps, lets speak as though no dates overlap).

Thanks in advance for any thoughts.


texts:
I have a postgres view "people_codes" that has a column that is an ARRAY_AGG of intergers. I am trying to query on the view with an array of integers for all rows that may have one of these codes
uuid    |  name     |     codes
(uuid)  | (varchar) |   (bigint[])
abc-def |  john     |  {5294, 5295}
bee-aaa |  jane     |  {1123, 2222}
111-a98 |  bob      |     {1933}

I can successfully query the view with only one integer for the query
SELECT * FROM people_codes WHERE 5294 = ANY (codes);

And this returns the row that I need:
abc-def   | john    |    {5294, 5295}    |

My final goal is to be able to query my view with an array, so that I can find all rows that have one or more of the given codes.
I have tried changing my value into an int, also casting it as a ::bigint[], but I get a syntax error at the brackets. Occurs no matter the brackets (, [, or {
SELECT * FROM people WHERE [5294,1933]::bigint[] = ANY (codes);

ERROR:  syntax error at or near "["
LINE 6: SELECT * FROM people WHERE [5294,1933]::bigint...

I have also tried using the @> and && comparison operators, but having trouble with the array of values again. It's trying to hint to adding an explicit type cast, which is what I thought was doing. Same thing with trying to place the search values into an ARRAY_AGG
SELECT * FROM people WHERE codes @> IN (5294,1933)::bigint[];

ERROR:  operator does not exist: bigint[] @>
LINE 8: ...ROM people WHERE codes @> IN (529...

What is a correct query to get rows that contain any elements in the query array? By searching on the Array of [5294,1933] I'm expecting to get back the rows for both john and bob


texts: Finding rows in a table with a foreign key pointing back at itself where the referenced row differs in a certain column

texts:
I can't formulate my question, so I will try to explain here and hopefully change the title later on.

I have a table

Organisations (
   id,
   platform_id,
   parent_id
)

Here is the tricky part: organisation.id represents a number for said organisation, but the same goes for the parent_id, it is basically another organisation. platform_id is either 1 or 2.

Now what I'm trying to do is to select all organisations where the parent_id is not null, but also where platform_id of the two organizations represented by organisation.id and organisation.parent_id is different.

How can I structure it, so that I can use the parent_id to search it as normal id, so that I can get its platform_id as well?

I will try to explain better and shorter perhaps:

I want to be able to filter those organisations, whose parent_id have different platform_ids, for that I would need to perhaps do a second select somehow...

sorry it got even more confusing.

This is the same table, parent_id is just another id that is linked to it, so that means that I can somehow access parent.id as normal id and get its platform_id...

In short, I want to list all IDs, that have parent_ids where the platform_id is different than the parents_id's platform_id, however I don't know how to get the 'parents_id'splatform_id`.


texts:
I have a table that has two columns:

user_id int(11)
friend_id int(11)

In order to get the number of friends a user has has, I'd do something like:

SELECT COUNT(user_id) AS num_friends FROM friends WHERE friend_id = some_id

What I want to do is get the number of friends every single user in the table has and cache the results to another table so I don't have to run an expensive query like the one above. Basically a 'rollup'.

What's the best way to query for this data to get the number of friends that every friend_id has? I was thinking something like this, but it seems very wrong, especially if you have a large amount of distinct friend_id instances in the table:

SELECT COUNT(*) num_friends, friend_id FROM friends WHERE friend_id IN ( SELECT DISTINCT(friend_id) FROM FRIENDS) GROUP BY friend_id


texts: Creating a staging table when the target table has autoincrement

texts:
Is it possible to create a staging table when the target table has an autoincrement ID key, and then merge it back in?

I've attempted this:

 CREATE  TEMP TABLE stage (like target);

insert
into
    stage
    (colA, colB, colC)

Where the target table has an autoincrement column id.  When I run this script, I get back the following error:

[Amazon](500310) Invalid operation: Cannot insert a NULL value into column id
Details:
 -----------------------------------------------
  error:  Cannot insert a NULL value into column id
  code:      8007
  context:   query execution
  query:     896442
  location:  column:1
  process:   query1_51 [pid=15458]
  -----------------------------------------------;


texts:
I have just become a DBA for a large company they are currently looking to decommission a 2008 R2 server.
Is there any way of being able to find out if the databases are still being used and by which application without taking the databases off line and seeing who yells that it's broken.

Thanks In Advance


texts: Using sys.dm_db_index_usage_stats for unhelpful or unused indexes

texts:
I'm trying to discover indexes which may be in place which are only adding cost with no value to our database.  I've come across sys.dm_db_index_usage_stats and from MSSQL Tips the following:

  sys.dm_db_index_usage_stats

  This view gives you information about
  overall access methods to your indexes.  There are several columns
  that are returned from this DMV, but here are some helpful columns
  about index usage:

  user_seeks - number of index seeks
  user_scans- number of index scans
  user_lookups - number of index lookups
  user_updates - number of insert, update or delete operations

Does that mean if there is an index with very few / no seeks+scans+lookups but many updates, then the index is only costing us (have to update index when table is updated) but not gaining us anything?

Second question - Is the following query a decent way to discover this information?

SELECT DB_NAME([database_id]),
       OBJECT_NAME(dm_db_index_usage_stats.[object_id], [database_id]),
       TotalReads = (user_seeks + user_scans + user_lookups),
       Writes = user_updates,
       indexes.[name],
       *
FROM sys.dm_db_index_usage_stats
LEFT JOIN sys.indexes on dm_db_index_usage_stats.[object_id] = indexes.[object_id] and dm_db_index_usage_stats.[index_id] = indexes.[index_id]
ORDER BY (user_seeks + user_scans + user_lookups) ASC, user_updates DESC;


texts: Select the first row returned from the join query based on a condition

texts:
I have 2 tables PRODUCT and MANUFACTURER

Table1: PRODUCT, Columns: PRODUCT_ID,PRODUCT_NAME

Table2: MANUFACTURER, Columns: PRODUCT_ID,MANF_ID,ITEM_NO,DEFAULT_MANF

I have to select the default manufacture of a product from the MANUFACTURER table. If no default manufacture is available, i will select a manufacturer by the ITEM_NO from the MANUFACTURER table.

I have written query for this as below:

SELECT p.PRODUCT_ID, p.PRODUCT_NAME, m.MANF_ID, m.ITEM_NO, m.DEFAULT_MANF
FROM PRODUCT p
INNER JOIN MANUFACTURER m on p.PRODUCT_ID = m.PRODUCT_ID
WHERE p.PRODUCT_ID = 'PROD001'
AND m.MANF_ID = (SELECT TOP 1 MANF_ID FROM MANUFACTURER man WHERE man.PRODUCT_ID = p.PRODUCT_ID and coalesce(man.DEFAULT_MANF, 'N') = 'Y' ORDER BY man.DEFAULT_MANF, man.MANF_ID )

This query works as expected in SQL Server, but on Oracle it wont work as I am using 'TOP' in the query.

Is there any way I can modify the query to work in both SQL Server and Oracle?


texts: Multibyte replace not working in SQL Server

texts:
Why does one work and the other doesn't?

SELECT REPLACE(N'IAșI' ,N'ș','x') -- doesn't match (returns 'IAșI')
SELECT REPLACE('IAșI' ,'ș','x') -- works (returns 'IAxI')

My guess would be that in the second example SQL Server sees 'ș' as 2 characters (0x19 02) and doesn't do some collation optimisations that ruin the multibyte match.

What should be done for the multibyte example to work?


texts: In terms of databases, what does 'roach motel' mean?

texts:
Recently Larry used it in one of the OpenWorld keynotes.

Wikipedia says

  In computers, sometimes as slang use:

  Sometimes used to refer to a proprietary file standard -- "you can check your data in, but you can't check it out".

I fail to understand the meaning here. In what situations will one not be able to check out the data?


texts: Unexpected index scan instead of seek on update

texts:
We have a third party application which is performing badly. An update statement is performing a scan instead of a seek, and is not suggesting a missing index. Why is this happening?

I was expecting the execution plan to suggest a missing index like

create nonclustered index [ix_FluxInbox_tasklockedby] on Flux.dbo.FluxInbox (tasklockedby) include (tasklockedrole, tasklockedon)

Below is the execution plan

The details of the index scan

As requested, the DDL of the table

CREATE TABLE [dbo].[FluxInbox](
    [id] [numeric](19, 0) IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [appStatus] [varchar](20) NULL,
    [branchCode] [int] NULL,
    [groupName] [varchar](200) NULL,
    [inboxType] [varchar](20) NULL,
    [origin] [varchar](50) NULL,
    [parentPid] [numeric](19, 0) NULL,
    [pid] [numeric](19, 0) NULL,
    [priority] [int] NULL,
    [processCreatedOn] [datetime] NULL,
    [processStatus] [varchar](20) NULL,
    [processTarget] [varchar](50) NULL,
    [tag01] [varchar](100) NULL,
    [tag02] [varchar](100) NULL,
    [tag03] [varchar](100) NULL,
    [tag04] [varchar](100) NULL,
    [tag05] [varchar](100) NULL,
    [tag06] [varchar](100) NULL,
    [tag07] [varchar](100) NULL,
    [tag08] [varchar](100) NULL,
    [tag09] [varchar](100) NULL,
    [tag10] [varchar](100) NULL,
    [taskActivatedOn] [datetime] NULL,
    [taskCommand] [varchar](20) NULL,
    [taskCompletedOn] [datetime] NULL,
    [taskCreatedBy] [varchar](50) NULL,
    [taskCreatedOn] [datetime] NULL,
    [taskDescription] [varchar](100) NULL,
    [taskScheduledTime] [datetime] NULL,
    [taskStatus] [varchar](20) NULL,
    [taskTarget] [varchar](50) NULL,
    [tid] [numeric](19, 0) NULL,
    [userName] [varchar](50) NULL,
    [channel] [varchar](50) NULL,
    [processCompletedOn] [datetime] NULL,
    [taskLockedBy] [varchar](50) NULL,
    [taskLockedRole] [varchar](100) NULL,
    [taskLockedOn] [datetime] NULL,
 CONSTRAINT [PK__FluxInbo__3213E83F349C8E92] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 85) ON [PRIMARY]
) ON [PRIMARY]
GO

There are 9 other indexes, but none cover even one of the required fields


texts: Pull Data from one database and insert to another database ( same schema )

texts:
I am currently working on moving data from one database to another in PostgreSQL. I am studying python with psycopg2 to connect and run SQL queries in one database. My problem is how can I pull and insert the data that I queried from one database to another. Most of the posts I found here talk about database from local server to a remote server. Like:

How to work with multiple Postgres databases in psycopg2

This suggests to use dblink or FDW. My situation is that both databases are in the same schema.

I am really new to this kind of task. Any suggestions?

Here's sample code in python that I am working with:

conn_p = p.connect("dbname='p_test' user='postgres' password='postgres' host='localhost'")
conn_t = p.connect("dbname='t_mig1' user='postgres' password='postgres' host='localhost'")

cur_p = conn_p.cursor()
cur_t = conn_t.cursor()

cur_t.execute("SELECT CAST(REGEXP_REPLACE(studentnumber, ' ', '') as integer), firstname, middlename, lastname FROM sprofile")
rows = cur_t.fetchall()

for row in rows:
    print "Inserting ", row[0], row[1], row[2], row[3]
    cur_p.execute(""" INSERT INTO "a_recipient" (id, first_name, middle_name, last_name) VALUES ('%s', '%s', '%s', '%s') """ % (row[0], row[1], row[2], row[3]))

cur_p.commit()
cur_pl.close()
cur_t.close()


texts: After Rackspace server creation, PostgreSQL query planner doesn't work as expected

texts:
We created an image of one of our database servers in Rackspace.  Then, we created a new server using that image, expecting things to work.  However, the index performance we have seen seems to be bad, and even the query plans, when comparing the explain analyze outputs from the first server to the second one, have been different.  Basically, the second server - the one that was restored from a saved image - does not seem to rely on indexes as it should.

Is there something related to image restore that would cause this behavior?  If so, what may need to be done to fix the issue?  Should I look somewhere else?

Thanks very much!  I can provide more information if needed, so let me know if that would be useful.


texts: MySQL not using PRIMARY KEY to sort by default

texts:
I have a large MySQL table on 5.6.35-81.0 Percona Server with gaps in the PRIMARY key. The table has around 53 Million records and the auto-increment key is now at around 370 million. The table is a list of phone numbers and users add/delete the numbers very frequently hence the PRIMARY key which is BIGINT has gaps inside.

The problem I am facing now is that when I do a simple SELECT * FROM table, MySQL does not used the PRIMARY key to sort the results by default. It uses the phone number field from the table to sort the data and because of this the data is SELECTed as sequential instead of the way it's saved in the DB.

Offcourse SELECT with ORDER BY id takes a very long time. The other solution is to copy all the data to another table but that would require application downtime. How can I fix this or to avoid such in the future?

CREATE TABLE `contacts` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `phonebook_id` int(11) NOT NULL,
  `campaign_id` int(11) DEFAULT NULL,
  `contact` varchar(45) NOT NULL,
  `status` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `admin` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `call_status` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `active_call` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `test_call` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `dnc_contact` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `v_node_name` varchar(45) DEFAULT NULL,
  `timezone` varchar(4) DEFAULT NULL,
  `updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_date` timestamp NULL DEFAULT NULL,
  `contact_name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phonebook_id` (`phonebook_id`,`contact`),
  KEY `idx_001` (`active_call`,`campaign_id`,`test_call`),
  KEY `idx_002` (`phonebook_id`,`call_status`,`test_call`,`campaign_id`,`active_call`,`dnc_contact`),
  KEY `idx_003` (`phonebook_id`,`test_call`,`call_status`,`dnc_contact`),
  KEY `FK_campaign_id_idx` (`campaign_id`),
  KEY `idx_testcall` (`test_call`),
  KEY `idx_004` (`campaign_id`,`call_status`,`test_call`),
  KEY `idx_active_call` (`active_call`)
) ENGINE=InnoDB AUTO_INCREMENT=2369201 DEFAULT CHARSET=latin1;


texts: How to create several builds of PostgreSQL?

texts: I'm building PostgreSQL(version 9.3.1) from source code in Ubuntu. I'd like to create several builds of Postgres, like pg1, pg2, etc. How can I do it? Thanks in advance.


texts:
PostgreSQL ver 9.4.5.  Linux OS.
Application:  Web Based

Platform:

App Server (java) --> jdbc call --> Database Server (PostgreSQL)

I do know that PostgreSQL does not support the public synonym.  Now, for a user schema (let's call it MASTER_USER), if I coded in the stored function/procedure like the following, without using a public synonym to identify the table name:

select user_name from user_info_table;

then access it from the Java (web app) side via the JDBC call to the database, will that work?

OR,

I must use the identifier inside the sql, such as:

select user_name from  MASTER_USER.user_info_table?

I come from the Oracle world, there I first create the public synonym for the table, then in the stored procedure I just directly reference the table with no need to identify the table with a schema name.  I'd like to know how it works under PostgreSQL.


texts: How to constraint a foreign key to a subset

texts:
I have a table employee, and each of them can fulfill different roles, like inspector and constructor. I also have another table with a column where I can store what inspector is on the job.

People can play different roles, but roles don't have to be added dynamic; a column for every role on the employee would be sufficient. This would also be handy, because we are resources-constrained at the moment, and our users use Postico for maintaining data (for the moment), so a simple data-model is preferable. With a foreign key we can use the 1:N selector of Postico.

I tried to create a foreign key constraint to a (materialized) view, but that's not supported by Postgresql. Also the following foreign key is not allowed:

FOREIGN KEY("inspector_id", TRUE) REFERENCES employee("id", "is_inspector")

Do I have any option, other than duplicating data in multiple tables?


texts: SQLite table design for PK and/or UNIQUE

texts:
I'm trying to design a table in SQLite3, where I can perform these 2 actions.

  INSERT new row where a unique primary key is created.

And

  INSERT new row with an given primary key

Think of a scenario where 2 separate SQLite databases want to share rows created in either table, let's call the table "Messages". Let forget about how the information will get to from one db to another. Let's simply focus on the actual INSERT of the data.

Scenario A:

(t=0) DB-1 INSERTS new row at time 0, creating a new, DB-1 unique primary key. (PK=1)
(t=1) DB-1 reads the inserted line and sends it to the DB-2 for insertion there.
(t=2) DB-2 Gets the new line and INSERTS new line with given PK (PK=1).

This scenario works fine. My goal is to somehow make scenario B work in a simple way.

Scenario B:

(t=0) DB-1 and DB-2 separately INSERTS new rows at time 0, creating a new, DB-1 and DB-2 unique primary key in each. (PK=1 is present in DB-1 and DB-2)
(t=1) DB-1 reads the inserted line and sends it to the DB-2 for insertion there.
(t=2) DB-2 Gets the new line and INSERTS new row with incremented PK, because 1 already is used. (PK=1).

Let's use the initial table creation SQL as below, how would you change it?

CREATE TABLE Messages
(
    pk_row INTEGER PRIMARY KEY AUTOINCREMENT,
    created_in_db TEXT, // Identifier for which DB it was created in
    field2 TEXT
);


texts:
I solved this example to determine 2NF and 3NF. I would like to check if my answer is correct. Can somebody check the 2NF and 3NF of this example, please?

R( A B C D E F G H I J)

with functional dependencies:

AB -> C
A -> DE
B -> F
F -> GH
D -> IJ

My result:

2NF:
R1 (AB -> C , A -> E)
R2 (B -> F , F -> GH)
R3 (D -> I)

3NF:
R1 (AB -> C , A -> E)
R2 (B -> F)
R4 (F -> GH)
R3 (D -> I)


texts: Is it possible to create a Linked Server from SQL Server 2000 to SQL Server 2016?

texts:
I am trying to create a Linked Server from SQL Server 2000 to SQL Server 2016 R2. However, while creating I am getting the below error when trying to create Linked server from SQL Server 2000.

TCP Provider: An existing connection was forcibly closed by the remote
host.
Client unable to establish connection.

My SQL Server 2016 is on Windows Server 2016 and TLS 1.2 is only enabled. Do I need to enable TLS 1.0 and SSL 3.0 as well on SQL Server 2016 Windows Server ?


texts: Single value from a list of comma separated values when used in inner join in SQL Server

texts:
Can anyone please help me with a query to return a single value from a list of comma separated values when used in inner join. I can paste the example query here.

select *
from Test.dbo.Test1
   INNER JOIN  @TestTable T ON T.Name = ????

The table given is Test.dbo.Test1 .I had inserted the values to a table variable @TestTable and doing an inner join. The table is:-

Id  Name
1   John,Mili,Anil,Abhi
2   John,Abhi
3   John
4   Mili,Anil,Abhi
5   Anil
6   John,Mili,Anil,Abhi

I need a query in '???' space where I can select a single value from comma separated values in 'name' field and compare with the other.


texts: JDBC Sqlserver to elastic search : one input to different output?

texts:
I have a sqlserver2016 database that I want to index in elasticsearch using logstash.

Here is my logstash config file which somewhat works :

input {
jdbc {
jdbc_driver_library => "C:\elastic\Microsoft-JDBC-Driver-6.0-for-SQL-Server\sqljdbc_6.0\enu\jre8\sqljdbc42.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://10.11.12.13:1433;databaseName=testdb1;integratedSecurity=false;user=ElasticExtractor;password=flyingweisels;"
jdbc_user => "ElasticExtractor"
jdbc_password => "flyingweisels"
statement => "select top 150000 Item_ID itemid,merchant_id merchantid,modelnumber,language_id from items order by Item_Id desc"
}
}
output {
elasticsearch {
hosts => "localhost:9200"
index => "testdata"
document_type => "testtype"
document_id => "%{itemid}"
}
}

So what this file is supposed to do, as configured is insert 150k items in elasticSearch. Somewhat it only imports about a third of that, such as 62 382 in this case. If I try to insert 50k it only inserts about 20k.
Is there an obvious reason why it would do that ?

Here is the current execution log :

[2017-09-01T08:16:31,923][INFO ][logstash.outputs.elasticsearch] Elasticsearch pool URLs updated {:changes=>{:removed=>[], :added=>[http://localhost:9200/]}}
[2017-09-01T08:16:31,927][INFO ][logstash.outputs.elasticsearch] Running health check to see if an Elasticsearch connection is working {:healthcheck_url=>http://localhost:9200/, :path=>"/"}
[2017-09-01T08:16:32,006][WARN ][logstash.outputs.elasticsearch] Restored connection to ES instance {:url=>"http://localhost:9200/"}
[2017-09-01T08:16:32,007][INFO ][logstash.outputs.elasticsearch] Using mapping template from {:path=>nil}
[2017-09-01T08:16:32,042][INFO ][logstash.outputs.elasticsearch] Attempting to install template {:manage_template=>{"template"=>"logstash-*", "version"=>50001, "settings"=>{"index.refresh_interval"=>"5s"}, "mappings"=>{"_default_"=>{"_all"=>{"enabled"=>true, "norms"=>false}, "dynamic_templates"=>[{"message_field"=>{"path_match"=>"message", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false}}}, {"string_fields"=>{"match"=>"*", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false, "fields"=>{"keyword"=>{"type"=>"keyword", "ignore_above"=>256}}}}}], "properties"=>{"@timestamp"=>{"type"=>"date", "include_in_all"=>false}, "@version"=>{"type"=>"keyword", "include_in_all"=>false}, "geoip"=>{"dynamic"=>true, "properties"=>{"ip"=>{"type"=>"ip"}, "location"=>{"type"=>"geo_point"}, "latitude"=>{"type"=>"half_float"}, "longitude"=>{"type"=>"half_float"}}}}}}}}
[2017-09-01T08:16:32,050][INFO ][logstash.outputs.elasticsearch] New Elasticsearch output {:class=>"LogStash::Outputs::ElasticSearch", :hosts=>["//localhost:9200"]}
[2017-09-01T08:16:32,053][INFO ][logstash.pipeline        ] Starting pipeline {"id"=>"main", "pipeline.workers"=>4, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>5, "pipeline.max_inflight"=>500}
[2017-09-01T08:16:32,219][INFO ][logstash.pipeline        ] Pipeline main started
[2017-09-01T08:16:32,313][INFO ][logstash.agent           ] Successfully started Logstash API endpoint {:port=>9600}
[2017-09-01T08:16:32,643][INFO ][logstash.inputs.jdbc     ] (0.050000s) select top 150000 Item_ID itemid,merchant_id merchantid,modelnumber,language from items order by Item_Id desc
[2017-09-01T08:16:49,805][WARN ][logstash.agent           ] stopping pipeline {:id=>"main"}

Second thing is, let’s say I want to insert a row from SQL server that comes from this input, what plugin can I use so that if the row has a specific “merchant_id” it goes in an elastic TYPE named with that ID. Also, if it has a specific “language” it goes in an elastic INDEX with that language as the name.
Can that be done ?
Should I simply create multiple Logstash config files, one for each of those tasks ?


texts: What is meant by "nonclustered legacy instance of SQL Server"?

texts:
When I research nonclustered legacy instance in SQL Server, all I get is some results about indexes. This is obviously not related to or affiliated with indexes, so what is meant by "clustered instance of SQL Server" and nonclustered ones?

Context: SQL Server 2014 Upgrade Technical Guide (pdf download)

Example extract:


texts: Data Warehouse vs Data Mart vs database (separating logic from hardware)

texts:
I've been doing a lot of reading on Data Warehouse architecture as we are thinking about reforming our current setup feeding our BI. The 'maturity' of the Data Warehouse is quite low, despite being rapidly deployed.

I'm still confused over a "Data Warehouse" vs a "Data Mart."

What we currently do:

Go to source systems. Use an ETL process to insert "cleaned + transformed" data into data tables. We named these tables thinks like surveymonkey.FactSurvey. IE, the schemas are named after the data sources to aid in comprehensibility or 'what is this, where did it come from'. Tables are labeled as fact or dimension tables.

Currently we have 13-14 of these 'schema-sources' each with say 8-9 tables in one giant database.

How do we integrate data? Conformed dimensions, of course. Either dimensions are "cleaned/ conformed" via logic in the ETL scripts, or they are "cleaned" post-load in views atop the tables.

In my view, this giant database is a "data warehouse" - it feeds our business intelligence OLAP cubes.

What exactly is a data mart, and what is the purpose? I thought we were already following the Kimball approach -- fact and dimension tables. ETL from source to database. A series of 'views' denormalize the data to feed into the cubes. But isn't it the Kimball approach that says "well a data warehouse is simply all the data marts combined together" -- that sounds kind of like what we're doing. Dump all the transformed source data into one database. Views join table within source schema and between them (integrating business data).

I guess I'm fairly lost at what defines a "data mart" in the logic here.

Maybe I should post a separate question here, but then does one divide "data marts" via schema, or different databases?

I'm not sure the logic of dividing a Data Warehouse into separate Data Marts -- they all feed ONE BI application we are using. This BI application imports ALL data once a day into itself. If Finance uses their Finance data 10,000x more often than Marketing queries the Marketing table, that is completely irrelevant to our database structure as they aren't directly querying/ hitting our Data Warehouse whatsoever.

And what is the logic of separating Data Marts via Department or Function, generally? Is it a performance issue or a UX issue? If performance, then Department shouldn't be the divisor, IO load or usage should be ...

Does anyone know what the difference is here in terms of architecture and use cases? I've read a lot online, but honestly, it seems like most authors are waxing philosophical and not talking about specific tables or brass tacks. Maybe it would be easier if the literal architecture/ db/ schema structure were explained.


texts:
I have a strange issue with dumping post-data section.

Dump command is:

pg_dump -Fc --verbose --section=post-data --no-owner --no-acl -h localhost -p 9999 -U root db_name -W > constraints.dump

Here is an error message:

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation verification_code_user_mapping_id_seq
pg_dump: [archiver (db)] query was: SELECT sequence_name, start_value, increment_by, CASE WHEN increment_by > 0 AND max_value = 9223372036854775807 THEN NULL      WHEN increment_by < 0 AND max_value = -1 THEN NULL      ELSE max_value END AS max_value, CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL      WHEN increment_by < 0 AND min_value = -9223372036854775807 THEN NULL      ELSE min_value END AS min_value, cache_value, is_cycled FROM verification_code_user_mapping_id_seq

But when I'm trying to execute this query in psql it works well:

db_name=>  SELECT sequence_name, start_value, increment_by, CASE WHEN increment_by > 0 AND max_value = 9223372036854775807 THEN NULL      WHEN increment_by < 0 AND max_value = -1 THEN NULL      ELSE max_value END AS max_value, CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL      WHEN increment_by < 0 AND min_value = -9223372036854775807 THEN NULL      ELSE min_value END AS min_value, cache_value, is_cycled FROM verification_code_user_mapping_id_seq;
             sequence_name             | start_value | increment_by | max_value | min_value | cache_value | is_cycled
---------------------------------------+-------------+--------------+-----------+-----------+-------------+-----------
 verification_code_user_mapping_id_seq |           1 |            1 |           |           |           1 | f
(1 row)

Here are the permissions granted to user root

db_name=> \dg root
                        List of roles
 Role name |          Attributes           |    Member of
-----------+-------------------------------+-----------------
 root      | Create role, Create DB       +| {rds_superuser}
           | Password valid until infinity |

I granted default privileges, select and all to all tables and sequences in schema public but with no luck.

Could you assist please?
Thanks in advnce!

UPDATE

db_name=> \dp verification_code_user_mapping_id_seq
                                              Access privileges
 Schema |                 Name                  |   Type   | Access privileges | Column privileges | Policies
--------+---------------------------------------+----------+-------------------+-------------------+----------
 public | verification_code_user_mapping_id_seq | sequence | root=rwU/root     |                   |
(1 row)

I'm using an Amazon RDS.


texts: Is it possible to create a function in SQL server to act as clause e.g. Where or Limit

texts:
I've found a simple way to mimic the limit clause is SQL server by using

SELECT * FROM customers
OFFSET 22
ROWS FETCH NEXT 5 ROWS ONLY

I'd like to replace those two line with

LIMIT(22, 5)
e.g.

SELECT * FROM customers
LIMIT(22, 5)


texts: Updating data from a report: any potential pitfalls?

texts:
Question:

In general, are there any pitfalls with updating data from a SSRS report?*

Context:

More specific, I'm talking about this context:

SSRS 2008 (with the option to upgrade, if needed).
MSSQL 2008 (with the option to upgrade, if needed).
Web.forms ReportViewer 2010 in our own ASP.NET application.

I only need to do a minor DB update. The user can tick a checkbox parameter "I'm going to print this" which will do a small update or insert to mark the report has been run at a specific point in time. The report will only show items were added since the previous "print" run, so I filter in my WHERE clause based on that, giving the user only "new" items.

The reason I'm worried is because it doesn't "feel right" to do updates from a tool that's meant primarily for viewing data. On the other hand, with something as small as above it seems like the easiest solution.

Specific concerns:

Here are some specific concerns I have about which I'm seeking insight:

Security: the report's connection will need to be run with a user that's allowed to do inserts, where a more sensible default would be to run it for a select-only user? Are there known sql-injection-like attacks for SSRS?
Usability: I'd like to do the update depending on whether a user ticks a checkbox. I'm not sure how well this would play with refreshes, and if you can detect what the rendering format is while doing the queries (which would allow me to make the checkbox trigger updates only on -e.g.- a PDF export).
Robustness: update statements seem more prone to exceptions (violating constraints, etc). Not sure if there's an elegant way to prevent, log, track, and debug that kind of problem.


texts: SQL Server group by date, time between status


texts: Filter based on values after join operation - MongoDB

texts:
I have two collections in the following format -

collection1:

{
    "_id": "col1id1",
    "name": "col1doc1",
    "properties": [ "<_id1>", "<_id2>", "<_id3>"]
}

collection2:

{
    "_id": "<_id1>",
    "name": "doc1",
    "boolean_field": false
},
{
    "_id": "<_id2>",
    "name": "doc2",
    "boolean_field": true
},
{
    "_id": "<_id3>",
    "name": "doc3",
    "boolean_field" : false
}

The desired output is:

{
    "_id": "col1id1",
    "name": "col1doc1",
    "property_names": ["doc1", "doc3"]
}

The field properties of document in collection1 has three IDs of documents in collection2 but the output after join operation should contain only those which have the boolean_field value as false.

How can I perform this filter with a join operation in MongoDB?


texts: Estimating distribution of row access in mysql

texts: I am trying to get an idea of the size of the "hot data" part of a rather large table, and I was wondering if this could be done directly in mysql. I know that with the percona version of mysql, I can have access to figures like "number of rows accessed per table", but I would actually need those data on a per row basis (e.g. row with id 1 was read 200 times, row with id 2 was read 300 times, where id is the auto increment column)


texts: Does an Oracle nvarchar2 take twice as much space as varchar2?

texts:
Regarding point 4 of this question (which is in reference to MS SQL Server):

  VARCHAR does not store Unicode characters.
  NVARCHAR does store Unicode characters.
  Today's applications should always be Unicode compatible.
  NVARCHAR takes twice the amount of space to store it.
  Point 4 doesn't matter because storage space is extremely
  inexpensive.

Is the same true for Oracle database as well?


texts: What is best practice to organize / normalize database tables?

texts:
I manage group that uses a database that is about 12 years old.

There are more than 200 tables / views that have been created by various people over the years, many of them no longer with us.

Most of the table and field names are descriptive, but with the number of table/fields we've lost track of what is used for what.  Consequently, whenever a new process is created, we create new tables rather than looking for the data in existing tables so the problem just gets bigger.

I'm looking for suggestions on documenting, normalizing and getting the multiple tables under control. Any comments, recommended books, articles, etc would be appreciated.


texts: Resolve parametrized schema in PostgreSQL

texts:
I'm looking for a way to use a parametrized schema in the DECLARE section of a PostgreSQL function.
Why am I looking this way ?

This functions will refer %ROWTYPE from multiple schema like

Var1 s1.emp%ROWTYPE ===> to refer the columns of emp tables as variable from s1 schema
var2 s2.stg_emp_d%ROWTPE; ===> to refer the columns of stg_emp_d tables from s2 schema as variable
var3 s3.emp_comp%ROWTYPE;===> to refer the colums of emp_comp tables from s3 schema as variable

s1, s2, s3 (schema names) are not same across different env eg:

IN DEV ENV s1, s2, s3
IN TEST ENV s1_t, s2_t, s3_t
IN PROD ENV s1_p, s2_p, s3_p

As we accept schema names as parameter in function, same function can be deployed without any changes across environments. It will inherit appropriate schema name set during run-time.

As we use %ROWTYPE of respective table, any changes to table structure will not impact these function. These changes are inherited via %ROWTYPE.

These functions need to get individual table columns name via %ROWTYPE with in BEGIN section like

if var1.emp_type = 'C' then ... do some thing ;
elsif  var1.emp_type = 'T' then ... do some thing ;

end if ;

Here is an example:
CREATE OR REPLACE FUNCTION get_list(in_code text[], p_schema text)
  RETURNS text  AS
$func$

DECLARE

  var1 user.emp%ROWTYPE;

BEGIN

SELECT q.id, q.title, q.code
FROM   questions q, emp e
WHERE  q.code <> ALL ($1)
and    q.emp_id = e.emp_id;

END ;

$func$ LANGUAGE sql;

The above getting created.
When I change var1 user.emp%ROWTYPE; to

var1 p_schema.emp%ROWTYPE; or
var1 $$ || p_schema || $$.emp%ROWTYPE;

Function is not getting created, but throwing error
ERROR : relation emp not found

Are there any limitations using parameterized items within DECLARE section?
I used these kinds of parameters with queries within BEGIN & END section. It did not throw any errors.


texts: Avoid downtime of Microsoft SQL Server during restarts

texts:
We are running an SQL Server Standard 2016. From time to time Microsoft updates need to be installed and the server will be restarted.
We have been asked to look into keeping the database available during that restart time.
So we don't need another database server for performance reasons, the database should "simply" be available during the restart process of the regular Windows Updates.
What would be the preferred setup in this situation?


texts: Compare two servers on SQL query performance

texts: I need to compare SQL query speed on two servers, with the same databases, one is an Amazon RDS SQL Server instance and the other SQL server is on IBM Softlayer. What do I need to do and how do I go about doing it. I need to generate a report for the comparisons to management. I am a SQL novice. Thank you.


texts: Deciding which MySQL execution plan is better

texts:
Background:

I have a query that I'm trying to speed up, involving 3 tables:

omgenvelope has 5 million rows
omgcust has 195 rows
omginput has 35836 rows

I'm obtaining the distinct omginputs referenced by certain omgenvelopes and grabbing some data from omgcust belonging to the omginput.

My two query variants so far:

SELECT customer, custname, idomginput, filename, omginput.laststamp
 FROM omginput, omgcust
 WHERE idomginput IN
     (SELECT DISTINCT(lastinput) FROM omgenvelope WHERE envstate NOT IN (42,46,65,70,250))
   AND idomgcust=customer ORDER BY omginput.laststamp, filename;

vs.

SELECT DISTINCT customer, custname, idomginput, filename, omginput.laststamp
  FROM omgenvelope
  JOIN omginput ON (idomginput=lastinput AND envstate NOT IN (42,46,65,70,250))
  JOIN omgcust ON (idomgcust=customer) ORDER BY omginput.laststamp, filename;

Query plans:

EXPLAIN on the upper query, with the IN (SELECT...) subquery, shows this plan (abbreviated):

  select_type: PRIMARY
        table: omgcust
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 195
        Extra: Using temporary; Using filesort
--------------
  select_type: PRIMARY
        table: omginput
         type: ref
          key: fk_omginput_omgcust1_idx
      key_len: 4
          ref: tracksdb.omgcust.idomgcust
         rows: 109
        Extra: Using where
--------------
  select_type: DEPENDENT SUBQUERY
        table: omgenvelope
         type: index_subquery
          key: fk_omgenvelope_omginput1_idx
      key_len: 4
          ref: func
         rows: 867
        Extra: Using where

An the new query that I'm, considering to use instead:

  select_type: SIMPLE
        table: omgenvelope
         type: range
          key: fk_omgenvelope_omgstate1_idx
      key_len: 4
          ref: NULL
         rows: 886220
        Extra: Using where; Using temporary; Using filesort
--------------
  select_type: SIMPLE
        table: omginput
         type: eq_ref
          key: PRIMARY
      key_len: 4
          ref: tracksdb.omgenvelope.lastinput
         rows: 1
--------------
  select_type: SIMPLE
        table: omgcust
         type: eq_ref
          key: PRIMARY
      key_len: 4
          ref: tracksdb.omginput.customer
         rows: 1

Question:

How do I quantify which one is more efficient?
The old one with the DEPENDENT SUBQUERY looks pretty simple, and doesn't yield so many rows in each step (195 * 109 * 867). The new candidate on the other hand shows (886220 * 1 * 1) rows.

So my question is how to interpret these estimated numbers of rows.
Can I just multiply them and compare the products, or do I need to think more about what the RDBMS is actually doing when executing the queries?


texts: Is it possible to associate typed variables with an object, such as a database, table, or row? Suppose I want to store the schema version as a NUMERIC.


texts:
Is there a way to associate a description with database snapshot?

I usually name snapshots using original database name and timestamp:

DBName-05-27-2016_1018

I would like to be able to associate a comment like "Before executing schema update scripts related to feature NNN" with a snapshot.

This is something that I could put in database extended properties but since snapshot is read-only it is not possible after it is created and it doesn't look like extended properties can be specified as a part of snapshot creation. Making these comments a part of snapshot name is ugly.

Is there any other way to associate a metadata with database snapshot when it is created? Of course I can maintain a table in separate utility database just for this but this is not too convenient.


texts: Why must TVPs be READONLY, and why can't parameters of other types be READONLY

texts:
According to this blog parameters to a function or a stored procedure are essentially pass-by-value if they aren't OUTPUT parameters, and essentially treated as a safer version of pass-by-reference if they are OUTPUT parameters.

At first I thought the goal of forcing TVP to be declared READONLY was to clearly signal to developers that the TVP can't be used as an OUTPUT parameter, but there must be more going on because we can't declare non-TVP as READONLY. For example the following fails:

create procedure [dbo].[test]
@a int readonly
as
    select @a

  Msg 346, Level 15, State 1, Procedure test
  The parameter "@a" can not be declared READONLY since it is not a table-valued parameter.

Since statistics aren't stored on TVP what is the rationale behind preventing DML operations?
Is it related to not wanting TVP to be OUTPUT parameters for some reason?


texts: Do I need to create index for the column when using it in update statement?

texts:
I have a table in postgresql11 which has a userId as a column. And it is not primary key in the table.
I need to perform below update statement on the table.
update entity set name='xxxx' where userId = 'yyy.
The userId is in the where condition but I don't know whether I should create an index for userId. Will it improve performance? If yes, how can I specify index in the update statement?


texts: With SQL Server's Query Store, how can cleanup avoid deleting "least expensive" queries?

texts:
I'm only interested in resource consumption (QUERY_CAPTURE_MODE = AUTO), with a fairly short interval (e.g. INTERVAL_LENGTH_MINUTES = 5).  I'll be grabbing data from the query store on a fairly regular basis, such as daily, retrieving stats of all completed intervals not previously retrieved.  My concern is that the docs say "Size-based cleanup removes the least expensive and oldest queries first."  I certainly want to discard stats of old queries (older than my retrieval interval), but if a ton of low-resource queries were performed in the intervals I haven't yet retrieved, I don't want those discarded, because they could add up to a high total consumption.

Is there any way to force deletion of stats for old queries, or something else I can do to assure that when cleanup occurs, that it doesn't delete stats for intervals I haven't retrieved yet?


texts: PostgreSQL : Where is index kept until check point occures ?

texts: In PostgreSQL data is first written to WAL files. When commit is done, data is written to WAL files and then return to client (synchronous commit). When  checkpoint reached or WAL buffer becomes full, these WAL files are written to data files. My question is where does the database keep indexes before writing to data files. Assume checkpoint occurs in every 15 min , then in this time window all the data is in WAL files and when user request for data, database read WAL files and provide data. In this time period does WAL also keep indexes ?


texts: Subscription fails with Incorrect syntax near '9'

texts:
In my Distributor To Subscriber History I get the following error:

Command attempted:
  9
  (Transaction sequence number: 0x0018E94F0000207A006200000000, Command ID: 59)

Error messages:

  Incorrect syntax near '9'. (Source: MSSQLServer, Error number: 102)
  Get help: http://help/102

It's a transactional replication with a pull subscription.

How do I locate this syntax error?

EDIT:

I found out where the syntax error is.
It's in a Address_2.sch file in the snapshot folder.

The first line is 9drop Table [dbo].[Address]

If I remove the "9" i just get another error: Could not find stored procedure 'd'

This error I can't resolve.


texts: Cassandra data modelling for IOT devices

texts:
We are building a micro service based IOT platform with a dashboard and views of various sensors etc. I have seen a few data modelling videos but I am not able to arrive at a good data model in cassandra. I need help regarding the same.

So, the usecase is as follows:

We are rendering personalized dashboards per user per organization. For example sake, consider the iot platform is being built for a warehouse monitoring solution. A warehouse can have a bunch of equipments and each equipment is monitored by a bunch of devices. So, my hierarchy looks as follows:

organization >> has Many warehouses >> has many equipments >> has many devices. An organization has several users / employees each at different levels say warehouse manager, ceo, zonal manager etc

So the dashboard has a bunch of stats that have to be displayed based on the user that logs in . These stats are

1) Total number of warehouses that the user can see (say 500000 for a ceo and 50 for a zonal manager)

2) Total number of equipments which are available in those warehouses (say 10000000 for a ceo and 300 for a zonal manager)

3) The count for the equipment status for the 10000000 equipments ( say POWERED_ON (50000) , POWERED_OFF (50000), MAINTAINENECE(50000), DISMANTLED  (50000) etc for a CEO and POWERED_ON (100) , POWERED_OFF (100), MAINTAINENECE(50), DISMANTLED  (50))

4) The total number of alerts generated for the equipments and devices. (say 15000 equipments are in CRITICAL state and have to be fixed immediately for CEO and 15 equipments are in CRITICAL state for a zonal manager)

For the warehouse specific data , the model that I have come up with is as follows:

//Org >> to user >> to warehouse >> to equipment mapping
      @PartitionKey(0)
      private String organization;
      @PartitionKey(1)
      private String userId;
      @ClusteringColumn(0)
      private UUID warehouseUuid;
      @ClusteringColumn(1)
      private UUID equipmentUuid;
      @ClusteringColumn(2)
      private LocalDateTime timeOfEquipmentStatusUpdate;
      private EquipmentState currentEquipmentState;

//Equipment state Audit information
      @PartitionKey(0)
      private UUID warehouseUuid;
      @PartitionKey(1)
      private UUID equipmentUuid;
      @ClusteringColumn(0)
      private LocalDateTime timeOfEquipmentStatusUpdate;
      private EquipmentState currentEquipmentState;

The equipment state will keep getting updated as and when technicians are fixing it. This will run an update query on the first table in a background job for updating Equipment state information.

Since the dashboard only deals with counts , I was thinking, a single query to fetch a single row for a user and then compute the number warehouses and equipments in my application.

I am however struggling more with alerts, as when alerts are raised, all users who can view the site / equipment should see the alert.I dont think I can make AlertType (CRITICAL / INFO/ WARNING) as a partition key.

The sql query may look something like

select count(alertType) from <table> where userid = 'userId' and orgId ='orgId' and warehouse in ('1','2','3' ...n) group by alertType;

One of the models that I have come up with for alerts is along the lines of

//Alert audit information
  @PartitionKey(0)
  private UUID warehouseUuid;
 @PartitionKey(1)
  private String equipmentUuid;
  @ClusteringColumn(0)
  private LocalDateTime eventOccurrenceTime;
  @ClusteringColumn(1)
  private String alertName;
  private AlertType alertType;

I am not sure how to map user in an org to this alert information. Can someone please help me with the data modelling.

Thanks in advance.


texts: In SQL Server, what do you guys use extended properties for? Some articles and blogs are suggesting they be used for self-documentation of the database and its objects but I can't see this being used properly... is this largely ignored by most?


texts: pg_dump 9.2.x command does not work with pg_dump 9.2.3

texts:
This is how i used to create all the time backups of my databases with PostgreSQL 9.2.x:

pg_dump -Fc -Z 6 -U postgres mydatabase > 2013-xx-xx_xxxxx.db

However on a machine where PostgreSQL with version 9.2.3 is running I get all the time the error "too many command line arguments" starting with "-Z". If I remove -Z he is complaining about "-U"?
What is wrong?

Okay there is definitely sth. wrong. I removed argument for argument until i just started pg_dump. Then i added argument by argument and it worked. So I opened another command line window and tried the same: First "too many arguments", after calling only "pg_dump" and adding further arguments it worked...


texts: I would like to query a range of criteria on multiple columns in MySQL

texts:
I have 3 columns in a mysql table. I'm using Innodb engine. I want to be able to search for some values on those columns and also anything close to those values. For example :

We have 3 columns as "Fast" "Smart" "Sharp" and they are of type boolean/int
I have 1000 records in the table, as an example, I list 5 here.

Fast  |  smart  |  Sharp
------------------------
0         1          1
1         1          1
0         0          1
1         1          1
1         0          0

and let's say we wanna search for people who are smart and sharp as 0  1  1  but if there
is no record with the value of 0  1  1 we want to get the closest to it which is  1  1  1
or 1  0  1 or etc. So now when I search for it, I get the exact value, if the value doesn't
exist, I won't get anything back from DB. So how can I achieve this and if I get a bunch of
result, how can I sort them from the closets to my query to the feartest ?


texts: Precision of NVARCHAR2 column is 1.5 times lower than displayed by desc command

texts:
I wanted to update precision of an nvarchar2 column and came across an interesting case. I have tried to replicate it below:

create table test_table1
 (col1 nvarchar2(100));

table TEST_TABLE1 created.

desc test_table1
Name Null Type
---- ---- --------------
COL1      NVARCHAR2(150)

We observed that the displayed precision of the nvarchar2 column is 1.5 times the specified precision. So if I have to replicate the precision of a column of type NVARCHAR2(375), then we need to declare it as NVARCHAR2(250).

I tried searching for an explanation but couldn't find any. Does anyone have any idea on this please? We are using Oracle-11g, connecting with Oracle SQL Developer.

NLS_SESSION_PARAMETERS

select * from NLS_SESSION_PARAMETERS;

+-------------------------+------------------------------+
|        PARAMETER        |            VALUE             |
+-------------------------+------------------------------+
| NLS_LANGUAGE            | AMERICAN                     |
| NLS_TERRITORY           | AMERICA                      |
| NLS_CURRENCY            | $                            |
| NLS_ISO_CURRENCY        | AMERICA                      |
| NLS_NUMERIC_CHARACTERS  | .,                           |
| NLS_CALENDAR            | GREGORIAN                    |
| NLS_DATE_FORMAT         | YYYY.MM.DD HH24:MI:SS        |
| NLS_DATE_LANGUAGE       | AMERICAN                     |
| NLS_SORT                | BINARY                       |
| NLS_TIME_FORMAT         | HH.MI.SSXFF AM               |
| NLS_TIMESTAMP_FORMAT    | DD-MON-RR HH.MI.SSXFF AM     |
| NLS_TIME_TZ_FORMAT      | HH.MI.SSXFF AM TZR           |
| NLS_TIMESTAMP_TZ_FORMAT | DD-MON-RR HH.MI.SSXFF AM TZR |
| NLS_DUAL_CURRENCY       | $                            |
| NLS_COMP                | BINARY                       |
| NLS_LENGTH_SEMANTICS    | BYTE                         |
| NLS_NCHAR_CONV_EXCP     | FALSE                        |
+-------------------------+------------------------------+

NLS_DATABASE_PARAMETERS

select * from NLS_DATABASE_PARAMETERS;
+-------------------------+----------------------------+
|        PARAMETER        |           VALUE            |
+-------------------------+----------------------------+
| NLS_LANGUAGE            | AMERICAN                   |
| NLS_TERRITORY           | SWITZERLAND                |
| NLS_CURRENCY            | SFr.                       |
| NLS_ISO_CURRENCY        | SWITZERLAND                |
| NLS_NUMERIC_CHARACTERS  | .'                         |
| NLS_CHARACTERSET        | AL32UTF8                   |
| NLS_CALENDAR            | GREGORIAN                  |
| NLS_DATE_FORMAT         | DD.MM.RR                   |
| NLS_DATE_LANGUAGE       | AMERICAN                   |
| NLS_SORT                | BINARY                     |
| NLS_TIME_FORMAT         | HH24:MI:SSXFF              |
| NLS_TIMESTAMP_FORMAT    | DD.MM.RR HH24:MI:SSXFF     |
| NLS_TIME_TZ_FORMAT      | HH24:MI:SSXFF TZR          |
| NLS_TIMESTAMP_TZ_FORMAT | DD.MM.RR HH24:MI:SSXFF TZR |
| NLS_DUAL_CURRENCY       | SF                         |
| NLS_COMP                | BINARY                     |
| NLS_LENGTH_SEMANTICS    | BYTE                       |
| NLS_NCHAR_CONV_EXCP     | FALSE                      |
| NLS_NCHAR_CHARACTERSET  | UTF8                       |
| NLS_RDBMS_VERSION       | 11.2.0.4.0                 |
+-------------------------+----------------------------+

nls_length_semantics

show parameter nls_length_semantics;
Show parameters query failed

Resolution

Thanks for the effort guys. The problem is indeed with SQL Developer.

I tried running it after connecting manually in Unix. Got correct result

create table test_table1
 (col1 nvarchar2(100));

Table created.

desc test_table1;
 Name  Null?   Type
 ---------------------------
 COL1          NVARCHAR2(100)

Thanks for both the answers. Thanks Paul for declaring the bounty and thanks jsapkota and Giova for taking your time and helping me out.


texts: column_name ordered same as describe table

texts:
I was under impression that default order of columns is internal mysql order but for some reason describe table and selecting column_name gives me compleatly different order.

How can I set ORDER BY to be same as in 'describe table' ('default' table order - not by name)?

Server version: 8.0.12 MySQL Community Server - GPL
mysql> describe assetType;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| assetTypeID | int(11)     | NO   | PRI | NULL    | auto_increment |
| assetName   | varchar(45) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT column_name FROM information_schema.columns WHERE table_name='assetType';
+-------------+
| COLUMN_NAME |
+-------------+
| assetName   |
| assetTypeID |
+-------------+
2 rows in set (0.00 sec)


texts:
We are loading ETL data in two parts, first with an SSIS package and then stored procedure.

In the script below, Part2 stored procedure will run even before SSIS Part1 is complete. How do I get Part2 Stored procedure to run only After completion of Part1 SSIS?

Is there a way to conduct this in T-SQL? Should we use delay function and check every few minutes with SSIS dmv SSISDB.catalog.executions if package is complete? Is there another way?

Background:
We can place sproc in a control flow arrow after SSIS. However, our company does not want to conduct this. We are more of a script based company. Additionally trying to refrain from SQL agent jobs. Currently QA department needs a way to run items, without creating multiple jobs. We created jobs for parent level packages, however do not want to create unit jobs for our 500+ SSIS and sprocs.

--Part 1
EXEC [SSISDB].[catalog].[create_execution]
               @package_name=N'Customer_StageTable_Part1_Insert.dtsx'
               , @project_name=N'Datawarehouse'
               , @folder_name=N'Datawarehousefolder'
               , @use32bitruntime=False
               , @reference_id= 2
               , @execution_id=@execution_id OUTPUT

select @execution_id

--Part 2
execute Customer_StageTable_Part2_Insert

https://www.sqlshack.com/execute-deployed-package-ssis-catalog-various-options/


texts: Are there relations that cannot be normalized to second normal form (2NF)?

texts:
I came across this example that I can't normalize to 2NF, so I was wondering if relations that could not be normalized existed.

Here is the example relation:

R(A, B, C, D, E)

with the functional dependencies:

{A, B} → D
C → B
E → A

or, diagrammatically:

The primary key of that relation is {C, E}, and if we suppose that all the attributes are atomic, then R is in first normal form (1NF).

But how can we normalize it to 2NF?

Since A and B are not fully dependent on {C, E}, I feel like they should be set aside in two different relations, but then D isn't dependent on anything in R. What am I missing?


texts: Does alter column change datatype use tempdb version store?

texts:
I was running alter column from INT to BIGINT, my table size is 250 GB and after 1 hour I got the version store error because tempdb was full.

As far as I know it only takes your memory pages and log files but I am not sure about Tempdb. Can someone please explain this internals?

I am using read committed isolation.

select
    name,
    snapshot_isolation_state_desc,
    is_read_committed_snapshot_on
from sys.databases

...for this database returns:

Name : DatabaseName
Snapshot_isolation_state_desc : OFF
is_read_committed_snapshot_on : 0


texts:
I want to create a SQL Script to clean datetime2 field and set it up to datetime2 begin '1-1-0001 12:00:00'. How to set GETDATE() to  '1-1-0001 12:00:00' instead of using convert function ?

UPDATE XYZTable
SET ReferralDate = CONVERT(datetime2, '1/1/0001 09:00')


texts: Schedule replicating database from Standby mode to Recovery mode

texts:
I have a database in Azure VM with Standby mode (for read only).
This database is restored nightly with log shipping files (24 totals, for each hour) that I get from a FTP site.
I am trying to replicate this database (which is in Standby mode) to a Recovery mode on a nightly basis after log shipping update is done.
I would like to repeat/schedule this process (StandBy --> Recovery) every night.
What is the best solution?
Do I use mdf and ldf file from StandBy database?
If that is the case, what is script or is there other way using GUI?
I am trying to move all objects (tables, views, stored procedure) from StandBy mode database (in VM) to Azure SQL database.
I think Snapshot Replication would work to move objects from VM SQL database to Azure SQL database. Ultimately, I will use data to generate reports in Power BI.
This is a follow-up of How to replicate from Standby mode database. Now, I am trying to replicate from standby to recovery and schedule the task.


texts: Do I need to stop the service to back up the database?

texts:
We have several database driven applications using MS SQL Express, Pervasive, MySQL, and some sort of flat file database.  PostgreSQL "may" be added later.

We have backup software that is VSS aware.  I need to backup the database files off the filesystem with regular Windows-based backup software.  We do not have SQL plugins for the backup software.

Do I need to stop the database service before the backup system runs at night, to ensure the database files are in a "consistent" state that can be restored?  Or will the VSS snapshot taken by the backup software allow me to reliably restore data 100% of the time?

I simply need a quick & dirty way of copying the data files with my typical, vss aware, backup software.  Using the SQL plugin would only provide back ups for MS SQL Express anyway.


texts:
What indexes would you recommend for the following select (and why)? Keep in mind there can be tens of millions of rows.

SELECT * FROM mytable WHERE md5 = ? AND created_at >= ? AND created_at <= ? LIMIT 200

Where:

md5 = CHAR(32)
created_at = DATETIME

The question is: what indexes would you add?


texts: Best way to implement an insert-only table to prevent updating huge number of fields in SQL Server

texts:
I'm going to have a table that holds pricing data of a lot of hotels, and this data will be updated regularly, say every one hour. I have a terrible experience for such a scenario, that we have to update a lot of rows, that causes deadlocks and leads to bad performance, so I'm going to avoid updating at all.
I think a good way is to insert every newly received price record, and specify some kind of version for them. So the table becomes something like this:
================================
RoomId | Date  | Price | Created
================================
1      | 29Dec | $1000 | 12:00
1      | 29Dec | $1200 | 12:30
1      | 29Dec | $1300 | 12:40
...

So when I'm going to query the current price, the row with the largest version (grouped by RoomId and Date) will be the desired result. The query is probably something like this:
SELECT * FROM (
    SELECT RoomId, Date, Price, ROW_NUMBER() OVER (PARTITION BY RoomId, Date ORDER BY Created DESC) AS rn
    FROM Prices
) s
WHERE rn = 1

I'm not sure about this way being the best way, and also not sure about the structure of table and the appropriate query to achieve that.
Any ideas would be appreciated.


texts: How to optimize a table/db with 200 insert/sec

texts:
I'm facing a very hard problem.

I have a mysql DB (MyISAM) with a main table (user) where I write a row for every new user (around 10 fields). Every INSERT triggers around 10 new INSERT in 10 different tables (counters, settings, etc).

In normal days (10 users/min) it works fine. In high-traffic days (100-200 users/min) my app just become unusable.

I worked a lot on system variables, tables indexes, etc., without any improvements. Today I figured out that probably the problem is on my main table (user): the slow query log shows that every INSERT can reach 180s to be executed!

This is the main query because I need the user ID in order to go on and let the user open and use the app. Moreover, this table receives a lot of SELECT, some directly and some in JOIN. It means that the locks for INSERT also block all the SELECT.

90% of new users, in high-traffic days, can't "pass" the signup, they get an error and they leave the app.

I'm reading a lot and trying a lot of new settings but probably the problem is not in mysql settings. It's in the app structure or in the first INSERT.

Do you have any suggestion for me? I'm completely open to anything. I would like to discuss with you possible solutions.

Thanks a lot in advance for any help.

p.s. I didn't put any info about DB, query, etc... If you need more information just let me know.

EDIT:

I don't have privilages for commenting so I'll add here this information:

1) do you mean changing InnoDB only the user table or the whole DB?

2) I just checked the values of Max_used_connections (it's 271) and Threads_running (it's 29). This is my file my.cnf


texts: How do I guarantee data is the same after upgrading SQL Server?

texts:
I have to upgrade a database from SQL Server 7 and SQL Server 2000 to SQL Server 2008. Additionally I have to be able to prove in a court of law that the data pre-migration is exactly the same post-migration.

Any ideas?


texts: Column to be populated using other columns values

texts:
I would like to add a new column in my table using SQL code in PostgreSQL / PostGIS, the values in this column must be filled from the other eight columns and updated with a trigger as I insert data to a new line for the first rows. As I show in the attached image.

I have used this code to alter the table,

ALTER TABLE table_name ADD COLUMN NEW_COLUMN varchar;

UPDATE table_name SET NEW_COLUMN = "PP" || '.' || "MM" || '.' || "DD" || '.' || "BB" || '.' || "QQ" || '.' || "LL" || '.' || "ID"::varchar || '.' || "UU";

But I am having a problem using this trigger because it does not accept:

CREATE OR REPLACE FUNCTION update_en()
RETURNS TRIGGER AS
$$
BEGIN
NEW.codigo :=;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER update_en
BEFORE UPDATE OR INSERT ON public."EDIFICACAO_4"
FOR EACH ROW
EXECUTE PROCEDURE update_en();


texts: Select Count on Big table take 15 minutes

texts:
I try to run select Count on table with like 16M rows, with MySql 5.5 and workbench 6.2.
The query running almost 1000sec . Is it normal?
The query :

SELECT COUNT(1) FROM My_Table

when I run explain I see that mysql using the pk index.
Don't have other indexes or Unique values in the table.
I know I can use the estimation in ALL_TABLES , but I need accurate number.
Do you have any ideas ?

The DB locate on AMAZON AWS - RDS , and I using the defualt parameter group ,
There are any changes you recommended to do?

Thanks


texts: How to implement an association with restrictions

texts:
I'm working on an SQL database of useful plants. I'd like to be able to allow users to annotate the plants with a set of hierarchical categories and tags. For example:

category: medicine

body part: stomach
disease: IBS

An initial database structure could be:
Plant

id
name
category

1
ginger
medicine

PlantTags

id
plant_id
tag_id

1
1
1

2
1
2

Tags

id
type
value

1
body part
stomach

2
disease
IBS

I would like to restrict the categories and tags that can be used together. For example, one should only be able to apply the body part and disease tags if the medicine category is selected. Furthermore, one should only be able to select the disease: IBS tag if the body part: stomach tag is selected.
Put another way, I want a way to specify which combinations of tags are valid.
Is this possible at the database level?
EDIT:
Users should also be able to categorise a plant without tagging it. For example:
Plant

id
name
category

2
cedar
timber

EDIT 2:
Here is an example of a set of tags that should not be allowed:

id
type
value

1
body part
stomach

2
disease
pneumonia

pneumonia is a disease of the lungs, not the stomach, so having the two tags stomach and pneumonia should not be allowed.


texts: How to put Oracle Redo Log file at the outer edge of disk for performance?

texts:
I always read that the outer edge of disk is the fastest place. Somebody said "You better put your Oracle Redo Log file at the outer edge of disk". My question is:

How to put Redo Log file at specific location in disk?
How much performance will it will increase?

By the way I'm using solaris 10 for Oracle 11g Database

Updated:

Here is the information about my disk

/dev/dsk/c0t1d0s0 is currently mounted on /oracle/diag
/dev/dsk/c0t1d0s1 is currently mounted on /oracle/oradata01
/dev/dsk/c0t1d0s3 is currently mounted on /oracle/oraredo01
/dev/dsk/c0t1d0s4 is currently mounted on /oracle/oraredo02
/dev/dsk/c0t1d0s5 is currently mounted on /oracle/fast_recovery_area
/dev/dsk/c0t1d0s6 is currently mounted on /oracle/backup
/dev/dsk/c0t1d0s7 is currently mounted on /oracle/data

Virtual Drive: 1 (Target Id: 1)
Name        :
RAID Level  : Primary-1, Secondary-0, RAID Level Qualifier-0
Size        : 835.394 GB
Mirror Data : 835.394 GB
State       : Optimal
Strip Size      : 64 KB
Number Of Drives per span:2
Span Depth  : 3
Default Cache Policy    : WriteBack, ReadAheadNone, Cached, No Write Cache if Bad BBU
Current Cache Policy    : WriteBack, ReadAheadNone, Cached, No Write Cache if Bad BBU
Access Policy   : Read/Write
Disk Cache Policy   : Disk's Default
Encryption Type         : None


texts: Delete SQL Server logins on replicated server

texts:
We have a production database which is replicated on another server at a remote location. The local database users (not logins) are also replicated in this database.  However, the SQL logins are not replicated from production to the remote server.

What is the best way to replicate "logins" to the remote server with passwords and all?

Here's my approach and I need to know if I'm going the right direction.

Need help with a script that will drop all logins on the replicated server.
EXEC [production server instance].[master].[dbo].[sp_help_revlogin] from the replicated server to get an accurate production list of logins.
Find a way to actually execute the results from sp_help_revlogin that will script out the logins on the replicated server with hash passwords, SIDs etc.

Any major problems with my approach?  If not, I could use some help actually writing the scripts or outline the exact process for 1-3 (made some attempts but have had problems getting the scripts to work).


texts: Informix - Getting all descendants of a parent

texts:
I have a table with two columns, Parent and Child. Need to get the list of all descendants associated with the parent records.

Source Table:

+----+-----------+
| Parent | Child |
+----+-----------+
|  a     |     b |
|  b     |     c |
|  c     |     d |
|  d     |     e |
|  e     |     f |
|  f     |     x |
+----+-----------+

Expected Result:

+----+-----------+
| Parent | Child |
+----+-----------+
|  a     |     b |  // As b is the child of a, all the descendants of b
|  a     |     c |  // are also descendants of a.
|  a     |     d |
|  a     |     e |
|  a     |     f |
|  a     |     x |
|  b     |     c |  // As c is the child of b, all the descendants of c
|  b     |     d |  // are also descendants of b.
|  b     |     e |
|  b     |     f |
|  b     |     x |
|  c     |     d |
|  c     |     e |
|  c     |     f |
|  c     |     x |
|  d     |     e |
|  d     |     f |
|  d     |     x |
|  e     |     f |
|  e     |     x |
|  f     |     x |
+----+-----------+

Informix 11.50 doesn't have recursive CTEs (common table expressions). CONNECT BY can help about recursion levels, paths etc., but I can not get expected result using CONNECT BY. Any idea?


texts: SQL Server internal memory get full on SQL Azure database

texts:
We have a SQL Server database hosted in Azure. Recently we have seen the avg_memory_usage_percent showing 100 percent. And it remains there. We checked in sys.dm_db_resource_stats DMV. Database size is 150+ GB.

We did try to find out which processes running at that time, but seen nothing or few regular queries running from sp_who3.

Can you help, how to find out which process or query is consuming more memory and block it at 100%?

Why is SQL Server not releasing memory?


texts:
I am trying to import a simple CSV file into a table on SQL Server 2017.  The CSV file and table match each other in number of columns and data types.  This is an extract of the CSV file:
UID,customerID,DateT,TypeID,AssociatedID,AttributeID,LandingPage,jsonT,ClientIPAddress
9150,2345,2020-09-01T00:00:36Z,1,,859,campaign01.html,{'product': 'string01', 'type': '', 'string011'},192.168.200.2
9151,2356,2020-09-01T00:02:30Z,1,,640,campaign02.html,{'product': 'string02', 'type': '', 'string022'},192.168.200.1

This is my table:
CREATE TABLE [dbo].[activity](
    [UID] [bigint] NOT NULL,
    [customerID] [bigint] NOT NULL,
    [DateT] [datetime] NULL,
    [TypeID] [bigint] NULL,
    [AssociatedID] [float] NULL,
    [AttributeID] [bigint] NULL,
    [LandingPage] [varchar](256) NULL,
    [jsonT] [varchar](1024) NULL,
    [ClientIPAddress] [varchar](64) NULL,
)
GO

This is my import statement:
BULK INSERT dbo.activity
FROM 'C:\tmp\activity.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '0x0a',
    BATCHSIZE = 1000,
    MAXERRORS = 2
)
GO

I keep getting an error saying the last column (the client IP) is to be truncated.  I checked all the values and they have nothing but IPs. Also the error  is not on all rows and I cannot see for my life any difference between one IP format or the other, they are just IPs! I tried simply making the ClientIPAddress field bigger (just for the sake of it) but I just keep getting the same error.
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 2, column 9 (ClientIPAddress).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 5, column 9 (ClientIPAddress).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 6, column 9 (ClientIPAddress).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (2) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Any idea on what I could check or what I am doing wrong would be highly appreciated.
Thank you!


texts:
I have a tricky question and I do not exactly know where to find relevant information to this.

I have SQL Server 2014 Enterprise installed on a database server. On this server there exist multiple databases, some belong to us and some to other companies.

My concern is with cache memory. I have a basic understanding of how SQL Server uses cache memory to retrieve data pages fast. You run a query, and usually, data pages get loaded to the cache and with repeatable calls, it gets results from this.

But... our application is highly used in usual working hours by our customers but at night there are always maintenance tasks going on. I am assuming (please tell me if I am wrong) that during the day, the cache gets loaded with useful data pages, but at night it is deleted because of the maintenance tasks, and so the next day, when customers start doing transactions, data pages have to be again loaded into the cache first, which takes time and slows a lot of things down.

So, can I somehow prove if that happens?

If yes, can I manage it? Such as somehow telling SQL Server to not load into cache?

I would be grateful for some specific articles discussing this topic. I could not find any, or better say, I do not know how to name this problem.


texts:
We have stored procedure on SQL Server v12.0.5203.0. Within that procedure we have a dynamic SQL statement that performs BULK INSERT. We are trying to capture misformatted rows and continue processing using ERRORFILE. The problem is that the ERRORFILE does not get created or updated.
The SQL Server command is running as account NT Service\MSSQL$SQLEXPRESSSERVER. The directory where we are trying to write the ERRORFILE is on the C: drive on the same physical box and has account MSSQL$SQLEXPRESSSERVER with full control. Is there some additional flag or permissions that need to be changed that will allow the creation of the ERRORFILE?

The error is :

  Msg 245, Level 16, State 1, Line 6
  Conversion failed when converting the nvarchar value '""' to data type int.

Shouldn't it log this row into the ERRORFILE and keep processing?

this is a snippet from the stored proc:

SET @paramDefinition = N'@inputFileName as nvarchar(500),
                        @formatFileName as nvarchar(500),
                        @conEdBcpErrorLogs as nvarchar(500),
                        @firstRow as Int';

SET @sql2 = N'BULK INSERT [emioutagesbcp]
            FROM "' +  @inputFileName + '"
            WITH  (
                    FIRSTROW = ' + Convert(VARCHAR(10),@firstRow) + ',
                    FORMATFILE = ''' + @formatFileName + ''',
                    ERRORFILE = ''' + @conEdBcpErrorLogs + ''',
                    FIELDTERMINATOR = ''|'',
                    ROWTERMINATOR = ''\r\n'',
                    MAXERRORS = 100)';
--PRINT @sql2

EXECUTE sp_executesql @sql2, @paramDefinition, @inputFileName = @inputFileName, @formatFileName = @formatFileName, @conEdBcpErrorLogs = @conEdBcpErrorLogs, @firstRow = @firstRow;


texts: How to handle errors in a transaction in a stored procedure?

texts:
I need to catch errors in a transaction in a stored procedure and log them in a table in the catch block.

After starting the transaction, a loop will attempt to insert a handful of values in the table.  I surround each insert statement with a try/catch block, so that if a Primary Key violation occurs on any one of the inserts, I handle the error by inserting a record in a log table.  I will then commit the transaction once the loop completes and it has attempted all inserts, even if one of them failed.

I'm concerned that calling SET XACT_ABORT ON at the beginning of the procedure will cause the transaction to be aborted when the PK error occurs, even though I'm catching and handling the error.  Is that true, or does the try/catch intercept the error and suppress it in a way that the transaction is not aborted?

If try/catch doesn't stop the error from aborting the transaction, then could I call SET XACT_ABORT OFF instead to get the error trapping behavior I need?

Here is some code to test the behavior:

First, create the target table and a log table:

CREATE TABLE ErrorTestTable (a int primary key clustered)
CREATE TABLE ErrorLogTable (m nvarchar(500), d datetime2(7))

Next, create the procedure:

CREATE PROCEDURE [dbo].[ErrorTest]
AS
BEGIN
    SET XACT_ABORT ON;
    SET NOCOUNT ON;

    DECLARE @Try int = 0;
    DECLARE @MaxTries int = 5;

    BEGIN TRAN;

    WHILE @Try < @MaxTries
    BEGIN

        BEGIN TRY
            print 'begin try - @@trancount: ' + cast(@@TranCount as nvarchar(max)) + '; xact_state: ' + cast(XACT_STATE() as nvarchar(max))
            INSERT INTO ErrorTestTable (a) VALUES (1)
            INSERT INTO ErrorLogTable(m,d) VALUES ('successfully inserted record!', sysutcdatetime());
        END TRY
        BEGIN CATCH
            PRINT 'begin catch - @@trancount: ' + cast(@@TranCount as nvarchar(max)) + '; xact_state: ' + cast(XACT_STATE() as nvarchar(max))
            INSERT INTO ErrorLogTable(m,d) VALUES ('pk violation!', sysutcdatetime());
        END CATCH
        SET @Try += 1;
    END

    COMMIT TRAN;

 END

Finally, run these statements and observe the behavior:

DELETE FROM ErrorLogTable
DELETE FROM ErrorTestTable
SELECT * FROM ErrorLogTable
SELECT * FROM ErrorTestTable
BEGIN TRY
   EXEC dbo.ErrorTest;
END TRY
BEGIN CATCH
   PRINT 'Error thrown by procedure.';
END CATCH
SELECT * FROM ErrorLogTable
SELECT * FROM ErrorTestTable

From testing a simple procedure, I've found a few things out. With SET XACT_ABORT ON, when the first primary key violation occurs, execution jumps to the catch handler. At that point, the transaction according to XACT_STATE is uncommitable (-1). Within the catch block, when I try to insert into the log table, another error is thrown and the stored procedure exits. I catch that error outside the sproc and select from the two tables, where I can then see the uncommitted data.

At that point the transaction is still not rolled back, and it's not until the whole batch is complete (including the 'select' calls after the sproc call) that it finally displays an error message saying

  Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

and finally rolls back the operations

Response to comments

  Why not just validate the information first, or do the error handling in the application calling the proc?

Already doing both. Not only validating, but taking out a custom application lock on the value-to-be-inserted, so lock contention is virtually non-existant, and PK-violations are basically impossible. I just want to know if I can handle the error if it were to occur. This is a very high-concurrency (100+ threads) rapid insert of "unused" random identifiers, so it has to not only check whether X random values are already in use, but settle on one to insert by attempting to take out a lock on the value. If, after successful lock acquisition, it still is not in use, I'll insert it.


texts:
I have a quite large db (800+Mb dump file) that I imported to my local server. It's a Wordpress db from witch I need to extract certain posts. There are around 160,000 posts inside.

Currently I'm testing a bit with MySQL Workbench running simple queries with JOIN and it requires a lot of time, so long actually that Workbench stops processing.

Here's an example:

SELECT
    COUNT(*)
FROM
    wp_posts
        LEFT JOIN
    wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
        LEFT JOIN
    wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
        LEFT JOIN
    wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id
WHERE
    wp_terms.term_id = 195;

Running over 600 seconds. Here is the wordpress db schema:
https://codex.wordpress.org/images/2/2a/WP3.9.4-ERD.png

Of course chance is I'm just bad at SQL, not really my field...


texts: CMS Maintenance Plans not copying to TSX

texts:
I recently setup an SQL 2017 instance to act as a central management server.
I've deployed a number of dynamic jobs w/o issues, but when trying to deploy a standard maintenance plan, the plan never makes it to any of the TSX servers, giving the error:

  Could not load package "Maintenance Plans\SomePlan" because of error
  0xC001000A.  Description: The specified package could not be loaded
  from the SQL Server database

All I've seen is related to early versions of SQL 2012 and the .NET 2.0 Framework.  I did verify the SQLAGENT.exe.config file though and it is using v4.0.

The MSX and TSX servers are using the same domain users to run the SQL and the Agent services.

What might I be missing?


texts: Connection issues during a drop in CPU usage

texts:
I have a challenging issue that I am facing and would like to lean on this community for advice and possibly answers to this issue.

We are running a dedicated server: Windows 2008 R2 64bit OS, 32 GB of RAM.
Our SQL Server version:  MS SQL Server 2012  (11.0.3000).

Intermittently our server will drop to 0-5% CPU usage, during that time our applications have a hard time (or just cannot) connect to the SQL Server.  Further, within the database logs I am reading this:

  FlushCache: cleaned up 12007 bufs with 100094 writes in 130307 ms (ovoided 667 new dirty bufs) for db 7:0
  last target outstanding: 4, avgWriteLatency 44
  average throughput: 0.41 MB/sec, I/O saturation: 12161, context switches 25796

The above logs are written several times per day, however a few of these instances occur when our CPU drops to an extremely low rate.  Our CPU for this server normally runs around 30-60% during business hours and I have never seen it drop to this level.

The only major changes have been an update in RAM from 16 GB to 32 GB.  There were some updates to the maintenance backups, but those fire at 12AM (PST).

I am available to add any further logs or information and am extremely grateful for any help concerning this issue.