texts:
I have three tables:

User (Table)
- Email
- Moblie
- User_id

Invoice_product (Table dbo.payware_factor_product )
- invoice_id
- Product_id

Invoice (Table dbo.payware_factor )
- User_id
- Mobile
- invoice_id

Each product has its own ID.

I want a query that, for example for product_id 16, returns all customers who bought product 16 and the mobile number of customers only once.

But with the below query, if a customer has bought product 16 five times, the result shows his  mobile number five times. How can I get it returned only once?

I wrote this query:

SELECT TBLA.mobile , TBLB.factor_id
FROM dbo.payware_factor_product TBLB
INNER JOIN dbo.payware_factor TBLA ON TBLB.factor_id = TBLA.factor_id
WHERE TBLB.product_id = 16
AND TBLA.mobile = (SELECT mobile )


texts: Executing a SQL Job only once a month and after completion of a specific job

texts:
Let me better explain. My first job executes every 15 minutes (top of the hour, 12:15, 12:30 and 12:45). It does this for every day of the week.
I have a second job that runs at 1:30 AM on the first day of the month. This job only runs once a month.

I need to daisy chain job one with job two only on the last execution of a given month. Meaning, job one executes at 11:45 pm and executes for 3 hours, at that point I need job two to start.

Thanks in advance.
RC


texts:
On a mysql 5.7 master, the following command generates syntax error.

CHANGE REPLICATION FILTER REPLICATE_DO_DB = ('mydb')
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REPLICATION FILTER REPLICATE_DO_DB = ('mydb')' at line 1

I have tried it with or without single quote around database name. official guide


texts: Why am I unable to skip a transaction in SQL Server 2017 transactional replication using the sp_setsubscriptionxactseqno stored procedure?

texts:
I have a transaction number of a transaction that is throwing the following error:
Command attempted: if @@trancount > 0 rollback tran (Transaction sequence number: 0x00036322000B2628000100000000, Command ID: 1)

Error messages: The row was not found at the Subscriber when applying the replicated (null) command for Table '(null)' with Primary Key(s): (null) (Source: MSSQLServer, Error number: 20598) Get help: http://help/20598 The row was not found at the Subscriber when applying the replicated (null) command for Table '(null)' with Primary Key(s): (null) (Source: MSSQLServer, Error number: 20598) Get help: http://help/20598

When I execute the following:
sp_setsubscriptionxactseqno @publisher = '<publisher>'
        , @publisher_db = '<publisher_db>'
        , @publication = 'ALL'
        , @xact_seqno = 0x00036322000B2628000100000000

in order to skip the offending transaction and then try to sync up the subscription, it doesn't skip the transaction, instead it keeps throwing the same error. When I execute the following:
USE distribution
GO

DECLARE @PublisherServer  VARCHAR(50),
        @PublicationDB    VARCHAR(50),
        @SubscriberServer VARCHAR(50),
        @SubscriberDB     VARCHAR(50),
        @PublicationName  VARCHAR(50)

SET @PublisherServer = '<Publisher Server>'
SET @PublicationDB = '<Publication DB>'
SET @SubscriberServer = '<Subscriber Server>'
SET @SubscriberDB = '<Subscriber DB>'
SET @PublicationName = '<Publication Name>'

EXEC Sp_helpsubscriptionerrors
  @PublisherServer,
  @PublicationDB,
  @PublicationName,
  @SubscriberServer,
  @SubscriberDB

I get 1,860 rows back all being:

id  time    error_type_id   source_type_id  source_name error_code  error_text  xact_seqno  command_id  session_id
23326   2020-07-15 15:52:27.640 0   0   MSSQL_ENG   20598   The row was not found at the Subscriber when applying the replicated (null) command for Table '(null)' with Primary Key(s): (null)  0x00036322000B2628000100000000  1   0
23326   2020-07-15 15:52:27.640 0   1   MSSQL_ENG       if @@trancount > 0 rollback tran    0x00036322000B2628000100000000  1   0
23326   2020-07-15 15:52:27.640 0   0   MSSQL_ENG   20598   The row was not found at the Subscriber when applying the replicated (null) command for Table '(null)' with Primary Key(s): (null)  0x00036322000B2628000100000000  1   0
23322   2020-07-15 15:50:09.100 0   1   MSSQL_ENG       if @@trancount > 0 rollback tran    0x00036322000B2628000100000000  1   0
23322   2020-07-15 15:50:09.100 0   0   MSSQL_ENG   20598   The row was not found at the Subscriber when applying the replicated (null) command for Table '(null)' with Primary Key(s): (null)  0x00036322000B2628000100000000  1   0
23322   2020-07-15 15:50:09.083 0   0   MSSQL_ENG   20598   The row was not found at the Subscriber when applying the replicated (null) command for Table '(null)' with Primary Key(s): (null)  0x00036322000B2628000100000000  1   0
23318   2020-07-15 15:47:45.423 0   0   MSSQL_ENG   20598   The row was not found at the Subscriber when applying the replicated (null) command for Table '(null)' with Primary Key(s): (null)  0x00036322000B2628000100000000  1   0

Why am I unable to skip that transaction? What am I missing?
EDIT:
I have tried to re-initialize the subscription, but it won't re-initialize. I manually attempt to start the 'Snapshot Agent' job from the 'Agents' tab and receive the following:
Status: Completed
Job: Snapshot Agent
Last Start Time: 7/16/2020 7:07:43 AM
Duration: 00:00:01
Last Action: [0%] A snapshot was not generated because no subscriptions needed initialization.

I marked two subscriptions for re-initialization and received the above. Below is a screenshot of the subscription listing:

But, even when I try to manually start the snapshot job, I receive the following:


texts: Is it possible to install oracle 12c without X window system

texts:
I am looking at installing Oracle 12c on a new server but the system admins are insisting that all new Linux servers don't have a GUI installed and that any administrative tasks are performed from a command line.

Now I have never installed Oracle this way and haven't found any information about it.

So is it possible and if so are there any links to documentation on how it's done.


texts: MySQL IN() statements: Avoiding duplicate lists

texts:
I need to check that two columns both appear in a list of IDs. So right now I have this:

SELECT
    `main_table`.*
FROM
    `activities` AS `main_table`
WHERE
    (actor_id IN ('10027' , '11535'))
        AND (original_actor_id IN ('10027' , '11535'))

The problem is that the list of IDs can get pretty big; into the thousands. I can't find a way without another temp table that lets me check that two columns are in a list without defining the list twice. I'm concerned that it's an excessively large query to send back and forth from my PHP web server.


texts: Availability groups taking time to change states

texts:
I created a 2 node AG setup as a POC today and when I tried to do an automatic failover by stopping the sql server service on the primary node, the one in the secondary went into resolving state and it took a long time for it to become a primary AG.

Does it have to do with networking issues? I also want to know if it is a good practice to have a separate network interface for the heartbeat network. When I go to the networks in WFSC I only see "cluster and client" and I assume it also includes the heartbeat.


texts: How is combined index implemented in SQL Server

texts:
I understand how single indexed column works in SQL Server and how it is implemented using balanced trees. There are plenty of interesting videos on YouTube on this topic. However I don't understand how it works if the index is based on multiple columns. For example:

CREATE NONCLUSTERED INDEX idxItemsCatState
ON Items (Category,OfferState)
INCLUDE ([Id],[Ranking])

And how it can speedup query like

SELECT ID, Ranking FROM Items where Category = 1 AND OfferState < 3

It is still implemented as B-Tree? How it can evaluate the combination of values? What are the restrictions for such feature?


texts: Long running innodb query generate a big undo file in mariadb

texts:
I have a big query in php using MYSQLI_USE_RESULT to not put all the results into the php memory. Because if I use MYSQLI_STORE_RESULT it will put all of the data into memory for all results, which takes multiple GB of ram, instead of getting row by row. it returns millions of rows and each row will generate an api request, so the query will be running for days. In the mean time, i have other mysql queries that update/insert the tables related to the first query, and i think it cause the undo log to grow without stopping.

I setup innodb_undo_tablespaces=2 and innodb_undo_log_truncate = ON so the undo log is separated from ibdata1, but the undo files are still big until i kill the queries that have been running for days.

I executed "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;" before running the long running query, hoping that it would prevent undo file to grow, but it didn't.

The other queries that are updating/inserting have autocommit.

In 1-2 day, the undo file is already 40gb large.

The question : how to prevent this undo file to increase ? As i don't want to keep the previous version of the data while the query is running. It's not important if i get updated data instead of the data that was at the time of the query.


texts: XRC error when trying to access PostgreSQL view properties

texts:
I have strange problem, when trying to access PostgreSQL view properties through PgAdmin III:

Error: XRC error: 197: invalid row index 1: must be less than 1

After this error is displayed, I am able to open the dialog.

I tried to Google it to no avail. I am using PostgreSQL 9.3.5 (on Debian 7) and PGAdmin III 1.20.


texts: Why does my DB2 silent/unattended Install instead invokes DB2 Install wizard and GUI. Can someone help on ths issue?

texts:
I want to install DB2 10.5.6 DPF on my VMWare windows machine. I manually created the response files (both the server.rsp and addpart.rsp files).

When it came time to perform the server install, I issued the following on the DB2 command line: ./db2setup -l db2server_installlog.txt  -t db2server_installtrace.txt  -r db2server.rsp

What happened is that instead of the above command installing DB2 silently and unattended, the DB2 Install wizard and the attendant GUI is invoked and I am invited to make the very same choices that produced the the response files that I am trying to use in the first place!!

Can anyone help explain the strange behavior that I have on my hand? I have installed DB2 unattended with response files before now with no issues. The difference with this install is that I am trying to see if I can install DB2 with Data Partitioning Feature on a trial basis for educational purposes only. Could not having a valid license or officially endorsed trial DB2 DPF version be the cause of the weird install behavior?

Any and all assistance will be highly appreciated.

Thanks

Okonita


texts: Connecting PhpMyAdmin to two different MySQL instances on the same host

texts:
I currently have running two MySQL instances on my machine, production and development - I can connect to each one using the following:

mysql --host=127.0.0.1 --port=3306 -u root -p
mysql --host=127.0.0.1 --port=6606 -u root -p

Now I just set up an instance of PhpMyAdmin, but I am having trouble getting it to connect to my Development instance, here is relevant context in config.inc.php

$i = 1;
$cfg['Servers'][$i]['verbose'] = 'Production';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['port'] = 3306;
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['AllowNoPassword'] = false;

$i = 2;
$cfg['Servers'][$i]['verbose'] = 'Development';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['port'] = 6606;
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['AllowNoPassword'] = false;

When logging on, whether I have Production or Development selected, I am also logged into the Production server running on 3306.

Thoughts?


texts: Amazon rds MySql -> concurrent connections deadlocks

texts:
I normally hang around in StackOverflow but I think this issue is better suited here.

I've built an application in C#.NET that uses MySql as a database.
My application inserts and updates data in the database in a using the Parallel API (so multithreaded). For reference, the queries that are being fired multithreaded would look like:

Update User set IsActive = 1 where UserID = 1 --each thread updates a different user id

This has always worked on servers that I manage and development machines.

We recently migrated the MySql database to Amazon RDS, however. Since then, if I run this query multithreaded (even with as low as two concurrent threads), it throws an exception:

Deadlock found when trying to get lock; try restarting transaction

Any experience with this issue? Are there some settings in Amazon RDS which are configured differently from a default MySql installation?

Thanks in advance!


texts: mysql 5.7 gtid based replication, Got fatal error 1236 from master when reading data from binary log

texts:
That post is not really a question, but I think stackexchange is a good place to keep record of a head scratching try, to get a GTID based replication slave working. Just in case someone hit's the same problem.

Machine 1,2,3 debian8 mysql5.7

replication M1 > M2 > M3
All machines use GTID
Replication is setup to use CHANNELS
M1 has db1.1 and db1.2
M2 has db2.3

replicating db1.1 and db1.2 from M1 > M2
replicating db2.3 from M2 > M3

replication M1 > M2 works for a long time w/o any problems

Now I tried additional replicating db2.3 from M2 to M3
db2.3 is a 168GB beast growing nearly 2GB each 2days and the
server on M2 is really busy on transactions.

So I used used percona's

innobackupex

innobackupex --apply-log

to backup databases on M2 and ssh'd it to M3

Started mysql on M3

SET @@GLOBAL.GTID_PURGED='as in xtrabackup_binlog_info';

CHANGE MASTER TO MASTER_HOST="X.X.X.X", master_user="myuser",
master_password="mypass", MASTER_AUTO_POSITION = 1 FOR CHANNEL 'c2';

I needed to provide the channel here because I setup MASTER MASTER replication on M1 and M2 and innobackupex copied also the replication channel data from M1 > M2.

START SLAVE FOR CHANNEL 'c2';

And got

Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

First, in that scenery gtid_executed looks a bit different than in the manuals

6e3e5e02-a1d5-11e6-941e-002590771320:1-913775,
85a95426-71b4-11e6-9328-002590a49aea:363280-365686

That's because we have already a replication running and so, two set's of GTID's.
The server UUID for

M1 : 85a95426-71b4-11e6-9328-002590a49aea

M2 : 6e3e5e02-a1d5-11e6-941e-002590771320

I didn't have any idea how to set that on the new slave.
I tried every possible combination and still got into the error above.

I guessed, because I don't replicate from M1 > M3 I don't need to set the values for M1 on M3.

SET @@GLOBAL.GTID_PURGED='6e3e5e02-a1d5-11e6-941e-002590771320:1-913775';
START SLAVE FOR CHANNEL 'c2';

but without any luck.

Then I read somewhere that mysql is able to catch up GTID_PURGED by itself.

so I:

STOP SLAVE FOR CHANNEL 'c1';

RESET SLAVE ALL FOR CHANNEL 'c1';

CHANGE MASTER TO MASTER_HOST="X.X.X.X", master_user="myuser", master_password="mypass" FOR CHANNEL 'c2';

NOTE: this time only providing credentials and
don't give
MASTER_AUTO_POSITION = 1
START SLAVE FOR CHANNEL 'c2';

First try, I got an Error

Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;

OK, that I now how to deal with.
In essential, it just says that the slave is trying to apply a statement, but cannot because of some missing data or data that is already there.

So now, let's set GTID another time, as it was on Master

SET @@GLOBAL.GTID_PURGED='6e3e5e02-a1d5-11e6-941e-002590771320:1-913775';
START SLAVE FOR CHANNEL 'c2';

And it worked, slave is trying to catch up with master now.


texts: Database design: same products but different seller relationships

texts:
I am trying to design a way to store historical pricing data sold by both regular users and organizations. I currently have the following database structure:
seller
- id (PK)
- name
- size

event
- id (PK)
- name
- location
- date
- seller_id (FK)

product
- id (PK)
- brand
- model
- serial_number
- price
- currency
- event_id (FK)

user
- id (PK)
- email
- name
- created_at

Each seller can have many events and each event can have many products, while an event can only belong to one seller, and a product can only belong to one event. All of the seller/event/product data was added in by me from external data, but I am now trying to incorporate a user to product table relationship, so users can directly add products they've sold to the same product table.
Sellers only make sales at an "event" where they sell many products, but users will simply make a sale and it will be connected directly to the user table, NOT connected to some "event" where the sale occurred.
My problem is that the "user" and "seller" table are quite similar, and will be using identical columns in the product table, but the event table is not applicable to a normal user. I need a way to separate the logic between a user on my site, and data that I gathered externally (i.e these sellers don't access the site as a normal user posting products would).
I've tried a few things including:

adding a polymorphic relationship where products morph to either users or events, but have had trouble querying seller data
adding a relationship with the user to the seller table, but have to create a dummy "event" that all of their sales go under, when in reality they don't have an event like the standard sellers, they just sell the product.

I'm essentially trying to find a way to simplify my queries to where I can get information on who sold the product without having to check whether a user or seller sold it, without having to create a fake "event" for the user.


texts: Reporting Services Maintenance and Performance Best Practices

texts:
I'm simply looking for what most folks do to keep their SSRS servers in top shape.  Beyond performing backups of the databases, symmetric key, custom extensions, configuration files, IIS settings, RDLs, SSL certs, etc. are there other tasks I can perform?

I'm really new to SSRS and I've been asked to do some research to keep our 4 servers in the best condition that I can.  I know this topic may seem subjective, but there has to be a general list of things that most SSRS pros use to make sure everything is running in tip top shape.


texts: Impact of an Asynchronous Mirror / Full Recovery Model on Performance

texts:
For an asynchronous mirror in SQL Server 2008 R2, using the full recovery model is required.

Assuming that the network and disk IO on both sides of the Mirror can keep up with the transaction logs and mirroring, are there still performance penalties for this over not having a mirror and having the database in simple recovery? If so, what kinds of operations are effected and what causes them to be effected?


texts: Size of individual transactions in log file

texts:
is there a way to find out the size of transactions from the transaction log by looking at the log file in SQL Server 2008? I do have an unexpected bloat of my logfile from some maintenance jobs which fail and do not seem to release the log space any more. So I would like to find out the statements and how much space it used in the transaction log during a specific period in time.

I'm thinking of something like using the output from fn_dblog(null,null) and aggregate the output accordingly. But that seems to be quite daunting as there is no official documentation.

Any suggestions and help welcome.


texts: "Server not open" / "Drop tablespace" catch-22 situation

texts:
I am getting the "server not open" error in SQLPLUS, and the equivalent on Oracle SQL Developer, which says "Oracle Initialization or shutdown in progress".

Fair enough. The fix for this is as follows:

You check to see the status of your database:

select status, database_status from v$instance;

and I see that it is mounted and active. Then I run the following command:

 alter database open;

Which generates the following error:

  ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
  ORA-01110: data file 6: 'F:\XXXX_INDEX.DBF'

To resolve this issue, I referred to this resource.

Which is telling me to open the database by running the same alter database open command as above.

The issue is circular.


texts: Separate archive tables or soft delete for inventory database

texts:
I'm building an inventory database that tracks computer equipment and other hardware devices. At some point in any device's life it is retired and gets archived. After it becomes archived it needs to be tracked as it is removed from service and properly disposed. I originally designed the archiving mechanism using an exact replica of the active database that would receive its data using a trigger on delete from the active database. The archive database includes replicas of all the related tables because as certain foreign related records are no longer pertinent, they should not be accessible to users to use with new devices, but are required for referential integrity and querying with the archive tables. Keep in mind that the concept of archive here is not just to keep a history or a log. The archive is a part of the business process, and users will need to query and update devices that are both active and archived.

The ERD below uses the Inventory.DeviceType table as an example where all entries and updates are copied to the Archive.DeviceType table. When users should no longer be able to enter inventory records of a certain device type, it is deleted from the Inventory.DeviceType table, but remains in the archive table. This pattern is used on all tables to ensure the archives refer to valid data, hence the replica of all tables.

Active Table Example (Other related tables omitted)

Archive Table Example (Other related tables omitted)

Problem

I'm trying to figure out how I would query the database if I don't know if a device is active or archived? For example, if a user has a serial number and wants to find out information about the device, and they are unaware of whether it has been archived.

Option 1: Create a view based on a union all?

Option 2: Query the active database and then query the archive if the first query returns nothing?

The saga continues...

An associate suggested that I eliminate the archive database and use a soft delete scheme. I built a model using this idea, and then started running into a host of other problems.

Here are the same tables using the soft delete scheme.

Soft Delete Example

With this setup, devices are archived by setting IsArchived field to true and entering an ArchivedDate. I could query any device easily whether it is active or archived. (Please ignore the IsActive field, as this is used for an unrelated concept).

Take notice of the Phone subtype table, where I had to propagate an index of DeviceID and IsArchived flag because phone numbers must be unique for active devices. I have to do this with other subtype tables as well. I don't know if this is a good or bad design.

This part really confuses me...

What are best practices for handling soft deletes where foreign key values can be marked as deleted. The only thing I can think of is create a routine that searches for all records that are related to deleted data, and create a report for users to resolve the discrepancies. For example, if a table of locations is related to the devices table, and some locations are soft deleted, then the devices refer to locations that no longer exist and must be moved.

By the way, I'm using MS SQL Server 2008 R2, and I plan on using Entity Framework 4 in my application. I value database maintainability over performance.

Thank you for reading.


texts: If a query contains two identical subqueries, will they both run?

texts:
Say I have a SELECT query that contains two identical subqueries, like the below:

SELECT
  (SELECT SUM(Price)
    FROM Product p
    WHERE p.GroupID = g.GroupID) AS 'TotalCost',
  (SELECT SUM(Price)
    FROM Product p
    WHERE p.GroupID = g.GroupID) * 0.1 AS 'GST'
FROM Group g

Will the SUM subquery be performed twice, or will SQL use the first result for the second field?


texts: Why Oracle 11g installs in folder C:\app\

texts:
Just curious, now Oracle 11g installs by default in folder C:\app\

Why? Before, Oracle was installed by default in folder C:\oracle... Why now is created "app" folder?


texts: Query to match exact column or partial data of the column: SQL Server / Azure

texts:
Bit confused on how to get it work better than what has been done now. I am new, apologies if I'm asking for too much.

I have trip details which have from and to locations stored in database from Google MAPS API.

One such record could be like this :

  From : Bangalore, Karnataka, India To : New Jersey, United States

If someone search for

From  : Bangalore, Karnataka, India

or

From : Karnataka, India

or

From : Bangalore

or

From : Karnataka

or

From : India

or

From : Bangalore, India

Query should be success and same condition applies to TO column also. And if any one of FROM or TO column match is valid result.

How to achieve above solution with ordering better matches on top?

What I tried

Exact match solution.(Either matching FROM or matching TO and MATCHING BOTH, if given) Not a right solution as it doesn't give all matches.
Using LIKE but results are in right order (most or complete matches should be on top is not achieved by me)
FREETEXT and CONTAINS cannot be done as my SQL Server is on SQL Azure which doesn't support.
Create 3 columns for FROM and 3 columns for TO and populate data from FROM to from1,from2,from3 and query for

(exact match)
UNION
this with (query for keyword LIKE in from1,from2,from3 columns)

and do the same for TO column.

None of the solutions as I did now are convincing for me. Any better possible solutions?


texts: Copy SQL Server database to same server & decrypt by symmetric key return null

texts:
I have a SQL Server 2012 database that I backed up and restored to the same server and instance.  When I attempt to use DecryptByKey it always returns null.

Since it is the same server, do I need to :

OPEN MASTER KEY DECRYPTION BY PASSWORD = '...';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

I have tried several things:

I dropped the symmetric key, certificate and master key.  I recreated them and added some data to a table.  The encryption portion works but the decryption still fails.  I'm using the following code to create my encryption objects:

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'mypass'

CREATE CERTIFICATE SomeCert
WITH SUBJECT = 'SomeSubject'

CREATE SYMMETRIC KEY SomeKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE SomeCert

I then encrypt and decrypt like so:

OPEN SYMMETRIC KEY CC_Key DECRYPTION
BY CERTIFICATE DB_CC_Cert;

UPDATE Table SET
     Column = ENCRYPTBYKEY(KEY_GUID('SomeKey'), @CCNum
WHERE SomeColumn = 1;

CLOSE SYMMETRIC KEY CC_Key;

OPEN SYMMETRIC KEY CC_Key DECRYPTION
BY CERTIFICATE DB_CC_Cert;

SELECT
  CONVERT(VARCHAR(50),DECRYPTBYKEY(Column)) AS Column
FROM Table

CLOSE SYMMETRIC KEY CC_Key;

This select query always returns null.  Would could be the issue?


texts: Duplicating rows but incrementing the ID each time

texts:
I have the strenuous task of merging multiple Magento databases together and I am importing hundreds of blocks into the main database but subsequently have to go through every one and change the store ID to associate each imported block with the relevant store in the multi-store environment.

Hold on before you move me over to Magento SE

I'm just after a single query I can run in the database to duplicate a row (x) amount of times and auto-increment the ID column following on from the last in sequence.

For example, I have an existing table like so:-

...
╔══════════╦═══════════╗
║      id  ║  store_id ║
╠══════════╬═══════════╣
║       49 ║        1  ║
║       50 ║        1  ║
║       51 ║        1  ║
║       52 ║        1  ║
║       53 ║        1  ║
║       54 ║        1  ║
║       55 ║        1  ║
║       56 ║        1  ║
║       57 ║        1  ║
║       58 ║        1  ║
║       59 ║        1  ║
╚══════════╩═══════════╝
...

And I need to add new rows like so:-

...
╔══════════╦═══════════╗
║      id  ║  store_id ║
╠══════════╬═══════════╣
║       60 ║        2  ║
║       61 ║        2  ║
║       62 ║        2  ║
╚══════════╩═══════════╝
...

So that the end result is like so:-

...
╔══════════╦═══════════╗
║      id  ║  store_id ║
╠══════════╬═══════════╣
║       58 ║        1  ║
║       59 ║        1  ║
║       60 ║        2  ║
║       61 ║        2  ║
║       62 ║        2  ║
║       63 ║        2  ║
║       64 ║        2  ║
║       65 ║        2  ║
║       66 ║        2  ║
║       67 ║        2  ║
║       68 ║        2  ║
╚══════════╩═══════════╝
...

I am hoping there is a single query that I can use which can add each row with a specified store_id of 2 and allow the id to auto-increment?

I have unfortunately not managed to find an example of this online but I suspect it should be possible/relatively straight forward?


texts:
I have a MySQL single server setup with all tables using the InnoDB engine (because I need support for foreign keys). As response times are getting longer, I now believe I need to create a cluster of servers to be able to cope with an ever-growing database load.

Is it possible to create a cluster of MySQL servers using my existing databases which use InnoDB? If not, what are my options?


texts:
The EXCEPT operator was introduced in SQL Server 2005 but what is the difference between NOT IN and EXCEPT ?

Does it do the same? I would like a simple explanation with an example.


texts:
I created a non-clustered index on a live server (SQL Server 2008 R2) and it turned out not to give the performance benefits that I was hoping for.  So I want to delete the index again.

Unfortunately I cannot get a lock on the index as it is in use. It's probably always going to be in use. Restarting SQL Server is not really an option.

I've also had this same problem in the past when I've wanted to rebuild an index, and I've had to work around the issue by reorganising instead (which is sometimes very inefficient).

Is there any way to tell SQL Server to stop using an index? Or is it possible to run a query that will attempt to obtain an exclusive lock on the index, wait indefinitely, and then delete the index? Any other options? Or do I just have to wait until there's some kind of outage and then hope I remember to remove it before everything comes back online?


texts: PAGELOCK Conversion Deadlock From Update to Intent Exclusive

texts:
A customer of ours is experiencing frequent deadlocks.  The deadlocks are mostly on the same UPDATE statement.  The deadlocks follow a pattern in which both SPIDs have acquired an update (U) lock on a page, and both try to up-convert the U page lock to an intent exclusive (IX) lock.  Sometimes there is only one page involved; sometimes several.

We captured a deadlock trace using the trace flag 1222.  The SQL Server log shows many, many deadlocks with the following pattern (in bottom-to-top order):

waiter id=processe0dc2088 mode=U requestType=convert
waiter id=process2f9db2478 mode=U requestType=convert
waiter-list
owner id=processe0dc2088 mode=IX
owner id=process2f9db2478 mode=IX
owner-list
pagelock fileid=1 pageid=5794 dbid=2 objectname=tempdb.dbo.Item_Package_Site_Costs_Work id=lock1b22de480 mode=IX associatedObjectId=72057594131775488
resource-list

Both processes are running the same UPDATE statement to set a flag on this tempdb table.  This tempdb table holds information that needs to persist between client calls until the client is done.  The table has a fairly long index that starts with a GUID representing a unique process ID.

I am having difficulty understanding and simulating this deadlock.  I have tried various amounts of records with simulated data.

My questions:

Why are these processes acquiring U locks and then converting to IX?  I would expect the DELETE to acquire IX locks to begin with.

How can I prevent the deadlock?

The statement causing the deadlock is below.  The process has just done a lookup of costs for a list of items at a single store.  It is trying to note that there was a cost found.

Note that there is a deprecated (NOLOCK) on an UPDATE statement.  Would this be a contributing factor?

UPDATE tempdb..Item_Package_Site_Costs_Work
SET ItemPkgSiteCost_VINCostFound = 1,
    ItemPkgSiteCost_VendCost_Key = SiteCosts_VendCost_Key
FROM tempdb..Item_Package_Site_Costs_Work (NOLOCK)
    INNER JOIN #SiteCosts
        ON ItemPkgSiteCost_GUID = @ProcGUID
        AND SiteCosts_Item_Type = 0 -- Standard
        AND ItemPkgSiteCost_Site_Key = SiteCosts_Input_Site_Key
        AND ItemPkgSiteCost_Item_Key = SiteCosts_Item_Key
        AND ItemPkgSiteCost_ItemPkg_Key = SiteCosts_Input_Sel_ItemPkg_Key
        AND ItemPkgSiteCost_VendItem_Key = SiteCosts_VendItem_Key
        AND ISNULL(ItemPkgSiteCost_Qty_Recv, 1) = SiteCosts_Input_Qty_Recv

The customer's server @@version is:

  Microsoft SQL Server 2005 - 9.00.4035.00 (X64)   Nov 24 2008 16:17:31
  Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition
  (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

So far I have not been able to capture the query plan used at the time of the deadlock, and the normal ways I try to retrieve the query plan are not returning anything(sys.dm_exec_query_plan and sys.dm_exec_text_query_plan both return NULL).

UPDATE 2013-08-29

The customer installed SQL Server 2005 SP 4, but they are still seeing this deadlock.  I will pursue removing the deprecated (NOLOCK) on the tables being modified and see if this fixes the deadlocks.


texts:
I've tried to add a trigger to the ALTER_SERVER_ROLE command using:
CREATE OR ALTER TRIGGER XXX
    ON ALL SERVER
    FOR ALTER_SERVER_ROLE AS

It creates fine but doesn't fire when using the ALTER SERVER ROLE [sysadmin] ADD MEMBER [Test]
Any ideas why it doesn't fire?
Using SQL2019 CU8


texts:
There are 3 tables:

Label (Label_ID,Label_Name), Label_ID is Primary Key.
Tracks (Prefix, Label_ID), Prefix is Primary Key,Label_ID is empty column
Top100 (Prefix,Label) No keys in this table.

My query should be such that , For each prefix row in Tracks table, find an equivalent prefix in top100 table then select Label from top100 table, Match thus selected Label with Label_name in Label table, get its Label_ID and Update its value in Tracks table.

Can anyone please help me with this query?? Any help much appreciated


texts: Problems caused by SQL Server 2016 Query Store

texts:
I have been testing SQL Server 2016 (Standard) on an Azure VM and have encountered a problem.

In my SQL logs I am seeing SQLExceptions and eventid 1001 and this is happening every 15 minutes.

2016-06-30 15:20:45.83 spid43s     ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\LOG\SQLDump2693.txt
2016-06-30 15:20:45.83 spid43s     SqlDumpExceptionHandler: Process 43 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2016-06-30 15:20:45.83 spid43s     * *******************************************************************************
2016-06-30 15:20:45.83 spid43s     *
2016-06-30 15:20:45.83 spid43s     * BEGIN STACK DUMP:
2016-06-30 15:20:45.83 spid43s     *   06/30/16 15:20:45 spid 43
2016-06-30 15:20:45.83 spid43s     *
2016-06-30 15:20:45.83 spid43s     *
2016-06-30 15:20:45.83 spid43s     *   Exception Address = 00007FF9796D4BB2 Module(sqlmin+0000000000064BB2)
2016-06-30 15:20:45.83 spid43s     *   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION
2016-06-30 15:20:45.83 spid43s     *   Access Violation occurred reading address 0000000000000948

I have enabled Query Store on my databases and it is set to a Data Flush Interval of 15 minutes.

With query store turned off these exceptions appear to stop.

I am not experiencing this problem on my local install of SQL Server (Developer Edition).

Data is identical on Azure and local machine, both build 13.0.1601.5.


texts: Is there any difference on performance when choosing DISTINCT or GROUP BY to bring distinct values?

texts:
I would like to know if there is any difference concerning performance when choosing DISTINCT or GROUP BY to bring distinct rows from a query.

I've tried comparing the execution plans, but they seem to be the same for both queries.

Or does it have to do with the complexity of the query? If so, an example would be appreciable.

Example - when I run the following queries against the database it shows exactly the same execution plan for each one:

select distinct table_name
from information_schema.columns
where table_name = 'Customer';

select table_name
from information_schema.columns
where table_name = 'Customer'
group by table_name;

PS.: These simple queries are just to illustrate the question with an actual example.


texts: Fastest way to delete matching rows in MySQL?

texts:
I have more than 10 million records in a table. I want to delete the
records where the email column contains .ac..

What is the fastest method to perform the task?

Table Schema

CREATE TABLE `master` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `email` varchar(255) NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Delete Query

DELETE FROM `master` WHERE ( email LIKE "%.ac.%" );


texts: Is it good to avoid Linked Server by utilizing JOIN task under SSIS package?

texts:
We are trying to setup a centralized SSIS server (SQL Server 2014). I want to know the following:

Any procedure which is utilizing Linked Server and if that procedure gets called from SSIS SQL Task, is it better to put that logic inside SSIS itself without utilizing Linked Server?
If we ask end user to put logic inside the SSIS package (i.e. JOIN...) and not use Linked Server, is it better performance wise?

Example: I have Table A from Server_1 and Table B from Server_2. If I am creating a stored procedure on Server_1, normally I will be joining Table A to Table B using Linked Server to Server_2. If I put this same logic inside the SSIS package and use [JOIN] task instead, would that be better?


texts: join same table twice and get the results of rows

texts:
i have table like wp_term_relationships

object_id  term_taxonomy_id    term_order
     194            2           0
     194            7           0
     194            8           0
     194            38          0
     194            44          0
     194            59          0
     194            100         0
     194            101         0

My question is: I need a query like the term_taxonomy_id of 44 and 8.

44 is country id and 8 is course id. I need query when am selecting country id and course id, than display an particular country having this paricular course.

7 is parent course id. 38 is country parent id.

I have written a query like this:

SELECT object_id
FROM   wp_term_relationships
WHERE  term_taxonomy_id IN ( 44, 8 )

its displaying the country having all the coursee and course having all the countries. But I need only particular country having the particular course in its cities.


texts: Delegation: Bulk Insert failed when executed from SSRS dataset but success from SSMS

texts:
I've configured delegation for SQL server engine and SSRS and it works fine.
SQL Server (2017) engine and SSRS installd on the same machine. Let's call it SqlSrv1.
What we should attain to:
User opens a report with a dataset which joins a database data and an external shared file (with sensitive data) via impersonation. If user has no access to a shared file, report should display him an empty sensitive columns.

When I execute BULK INSERT via SSMS, something like

BULK INSERT #t1 FROM '\\FS1\common\anyfile.txt'

it works fine. Computer Management shows my name (instead of sqlengine service account) in "Shared folders\Sessions" as User and SqlSrv1 as Computer.

Now when I execute BULK INSERT from SSRS report dataset against a local file (stored on SqlSrv1 C:\anyfile.txt) it works and ProcMon displays my name in Detail column, instead of ssrs service account.

At last I execute BULK INSERT from SSRS report dataset against a shared file \\FS1\common\anyfile.txt and I get error Operating system error 5(Access is denied.), but ProcMon displays my name in Detail column as Impersonating.

There is a something strange I've located:
When I change Data Source in connection string of dataset to FQDN, BULK INSERT begones to work from SSRS. After that I change back Data Source to NetBIOS but BULK INSERT continues to work. What is going on?
All fine and dandy, but after restart SSRS service I get error Operating system error 5(Access is denied.) again.
When I execute the query from the step 1, ProcMon shows me the path
\\FS1\common\\anyfile.txt (with double slash after shared folder name! What?!!)
instead of
\\FS1\common\anyfile.txt
When I get error 5, ProcMon shows
\\FS1\common\anyfile.txt (without double slash)
But when the step 3 begones to work (after FQDN manipulations), OMG! The ProcMon shows me
\\FS1\common\\anyfile.txt (with double slash)
and after SSRS service restart I see in ProcMon
\\FS1\common\anyfile.txt (without double slash)
I've tryed to add double slash to BULK INSERT, but with no luck, SQL engine drops it (as expected).
Have you any ideas?


texts: Compare Past Records to Most Recent Record

texts:
Is it possible to structure a single query that would return a 0 or 1 (true/false) by comparing first record to previous records?

Sample records in table:

ProductID    SampleDate    RunFailure    LevelCode
101          20160322      0             2
101          20160321      0             3
101          20160320      1             2
101          20160318      1             2
102          20160317      0             3
102          20160316      0             2
102          20160315      0             1
103          20160314      1             1
103          20160313      0             2
103          20160312      1             3

I need to evaluate the most recent record by ProductID.  In the example above the last record (on 20160322) has a LevelCode of 2.  It needs to be compared to the record on 20160320 because it is the last record that failed with the same LevelCode (so it would ignore the record on 20160318 because it is older than the record on the 20th), therefore, in this case the query would return 1 (or true).

For ProductID 102 the query would return 0 (or false) because the LevelCode on 20160317 is 3 and there isnt an older record that failed with that same LevelCode.

ProductID 103 would return a 0 (false) because on 20160314 the LevelCode is 1 and the last record for that ProductID that failed was on 20160312 was for LevelCode3

Ideally the query would return something like this:

ProductID    IsMostCurrentRunSameLevelAsLastFailedRun
101          1
102          0
103          0


texts: Active/active SQL Server Clustering in Installation Center (setup.exe)

texts:
I like to know which option to choose in the setup.exe in SQL Server 2012/2008? What I have here is Windows Active/Active Cluster setup with one quorum and one msdtc configured. I installed my first node by choosing "New SQL Server failover cluster installation", now I am not sure what to choose on the second node. On the second node should I choose the same "New SQL Server failover cluster installation" OR choose "Add node to a SQL Server failover cluster" option. Remember it is going to be A/A SQL Server Cluster.
Appreciate your answers.


texts: What happens when you alter a table column's length, but that length is already that length?

texts: Say we run an alter table command to change the table column length from 5 to 6. If we run the same command again, does Oracle alter the table again?


texts: PostgreSQL functions inside where clause

texts:
I have table like this:

table a (
   id
   --
   p1 text,
   p2 text,
   p3 text
)

When I write some function, which use this table, I need to write code like this

create or replace function ...

select * from a
   where a.p1 is not null
     and a.p2 is not null
     and a.p3 is not null

I want to create a function,

 create function my_function(??? val) returns boolean as -- what I need to place instead of ???
 $$
 begin
     return val.p1 is not null
        and val.p2 is not null
        and val.p3 is not null;
 end;
 $$ language plpgsql

in which I will pass "this" instance of current row (I don't know, how it called) and my functions will be like this:

select * from a
   where my_function(???) -- what I need to place instead of ???

My questions are:

Can I create function my_function like above?
If point 1 is true, what type I need to write for parameter? And what I need write on where clause?


texts: Why does dropping foreign keys take long?

texts:
I have made a script that, one at a time, deletes all the foreign keys from a database, just like this:

ALTER TABLE MyTable1 DROP CONSTRAINT FK_MyTable1_col1
ALTER TABLE MyTable2 DROP CONSTRAINT FK_MyTable2_col1
ALTER TABLE MyTable2 DROP CONSTRAINT FK_MyTable2_col2

What surprises me is that the script takes long time: on average, 20 seconds for each DROP FK.
Now, I understand that creating a FK may be a big deal, because server has to go and check that the FK constraint is not infringed from the beginning, but dropping? What does a server do when dropping FKs that takes so long?
This is both for my own curiosity, and to understand if there is a way to make things faster. Being able to remove FK (not just disable them) would allow me to be much faster during a migration, and therefore minimize downtime.


texts:
Using below command to restore the database

Restoration command:

cat my_db_dump.sql.gz | gunzip | psql -d new_database -U my_username

Note: my_db_dump.sql.gz size is 4.5 GB, (extracted size will be around 70 GB)

Getting the below exception (after executing above restoration command)

  gunzip: stdin: unexpected end of file
  ERROR:  unterminated quoted string at or near "'\x3c3f78
  (hexadecimal junks removed)
  LINE 1: SELECT pg_catalog.lowrite(0, '\x3c3f786d6c2076657273696f6e3d...


texts: Reuse SELECT query by adding results in array?

texts:
I have written a PostgreSQL function, which returns a specific order of products. Now I would like, not only to show, but to put the results of the first SELECT query to an array as well, so I can reuse the ID's inside another select query. I first tried to add an alias to the select query like SELECT * FROM (SELECT id FROM products) as pr and use pr inside the NOT IN(pr) statement of the second query, but that doesn't work ...

I will explain it more clearly with an example, this is a simplified version of the function:

CREATE OR REPLACE FUNCTION featured_products(
    valid_to_in timestamp without time zone,
    taxonomy_id_in integer,
    product_limit_in integer)
  RETURNS SETOF integer AS
$BODY$
  BEGIN
    RETURN QUERY

    (
    -- #1
    SELECT * FROM (
        SELECT "product"."supplier_id" FROM products AS "product"
    ) AS "featured"
    LIMIT 2
    )

    UNION ALL
    SELECT *
        FROM (
            SELECT "product"."supplier_id" FROM products AS "product"
        ) AS "featured"
        WHERE id NOT IN (
            -- #2
            SELECT * FROM (
                SELECT "product"."supplier_id" FROM products AS "product"
            ) AS "featured"
            LIMIT 2
        )
    LIMIT product_limit_in;
  END;
  $BODY$
  LANGUAGE plpgsql VOLATILE;

I deleted some joins and GROUP BY and ORDER BY statements, so the function is a bit more readable. And I added #1 and #2 inside the code above, so you know what I mean with select query 1 and 2.

As you can see the query #2 should return the same results as query #1. In reality these queries are much bigger. So you I just want to replace the second, identical query with just an array of ID's. Less code and probably faster.

I don't know how to add the IDs returned from the first query, to an array and put that in a NOT IN(<id's>) statement instead the second query.

Anyone who does know how to fix this?


texts: Should I use WHERE NOT EXISTS or LEFT JOIN for getting the ids of relationship table?

texts:
I have users table:

CREATE TABLE `users` (
 `uid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `email` varchar(70) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `flname` varchar(60),
 PRIMARY KEY (`uid`)
) ENGINE=InnoDB

And a relationship table:

CREATE TABLE `relationship` (
 `rid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `from` mediumint(8) unsigned NOT NULL,
 `to` mediumint(8) unsigned NOT NULL,
 PRIMARY KEY (`rid`),
 UNIQUE KEY `from_2` (`from`,`to`),
 KEY `to` (`to`),
 CONSTRAINT `relationship_ibfk_1` FOREIGN KEY (`from`) REFERENCES `users` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `relationship_ibfk_2` FOREIGN KEY (`to`) REFERENCES `users` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

I want to fetch those users that is not in a specific user's circle (Like G+). It means that user has not yet added those people to his/her circle. I tried the below query but it was empty:

SELECT uid FROM users WHERE flname LIKE'%john%' AND NOT EXISTS (Select `to` FROM relationship WHERE `from`=60)

The below query returns 3 records:

SELECT uid,flname FROM users WHERE flname LIKE'%john%'

Now users in the name of john who has been added by uid=60 should not have be shown! The result of this query is empty. I can't figure that out, what I'm doing wrong?


texts: Can a stored procedure reference the database in which it is stored?

texts:
Suppose I have a stored procedure that is duplicated, with some modifications, in several databases.  And I want to reference the database in which the stored procedure is stored, even if it is executed in another database.

Is there a way to retrieve the full path (..) or otherwise retrieve the database in which the stored procedure is stored, rather than the current database?


texts: Postgres 11 JSONB - How to get all distinct values of a field across all elements

texts:
I have a table called Shipments:
CREATE TABLE shipments
(
      shipmentId numeric,
      metadata jsonb
);

Inside the metaData column there can exist an array of JSON objects named stops which might look something like this:
{
  "stops": [
    {
      "stopId": 1,
      "stopType": "Origin"
    },
    {
      "stopId": 2,
      "stopType": "Destionation"
    },
    {
      "stopId": 3,
      "stopType": "Transit"
    }
  ]
}

I'm trying to query across the table and get all the distinct stopType values. I can get all the distinct stopType values for the first index in the stops array via this simple query:
select distinct metadata->'stops'->0->>'stopType' from shipments

This almost gives me what I need:
╔══════════╗
║ stopType ║
╠══════════╣
║ Origin   ║
╚══════════╝

But what I want is:
╔══════════════╗
║ stopType     ║
╠══════════════╣
║ Origin       ║
║ Destination  ║
║ Transit      ║
╚══════════════╝

Thanks in advance for your help!


texts: Can somebody help me understanding the difference between view,materialized view and snapshot?


texts:
I have two schemas: A and B.

A has one table with the name 'TEST'. B has created a view 'BASE' which does select on table 'TEST'.

Now I create another user C to which I grant, select on the view 'BASE'.

When the user C runs the following statement, he gets insufficient privileges error:

select * on B.BASE;

What privileges is required to select the contents of the view?


texts: Should the filtering column(s) always be in the keys / includes?

texts:
I'm considering creating a filtered index in my copy of the Stack Overflow database.  Something like this, for example:

CREATE UNIQUE NONCLUSTERED INDEX IX_DisplayName_Filtered
    ON dbo.Users (DisplayName)
    WHERE Reputation > 400000;

Should I always add the column in the filtering expression (Reputation in this example) to the key or includes for the index, or is having it in the filtering expression good enough?


texts: Selective error logging (based on error code) in PostgreSQL

texts:
I want to log all PostgreSQL errors except unique_violation (error code 23505), which I would like suppressed.

How do I achieve that?


texts: Table/Column Character Encoding and Indexes

texts:
Does table/column character encoding have any impact on how the MySQL optimizer chooses join indexes?

I have a table that have the same columns and indexes on two servers, one server does a eq_ref under type and the other does ALL

Only difference I see is the character encoding. Could that be the cause query not using an index? In the database that isn't performing, one table is latin1 and the joining table is utf8.

1st Server

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, PRIMARY, tx, , ALL, , , , , 234587263, 100.00, Using where; Using join buffer (Block Nested Loop)

2nd Server

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, PRIMARY, tx, , eq_ref, PRIMARY, PRIMARY, 198, db1.log.hash, 1, 100.00,


texts: I have PROD Site and DR Site. If PROD site is completely down.I have backups of PROD site for Databases which are in SYNC and backups using a Third party tool.So I have copied the latest backups to DR Site and I can restore them.If I have fresh DB copies and if I am able to bring the server online by restoring latest backups ,what exactly is the use of DR BCP server ?


texts: SP2-0667: Message file sp1.msb not found sqlplus oracle

texts:
I've a linux RedHat which contains oracle database. I setted all environnement variables, but when I running sqlplus command I've this error : SP2-0667: Message file sp1<lang>.msb not found

Here are my variables ans my sqlplus commands :

[oracle@as ~]$ echo $ORACLE_HOME
/oracle/product/12.1.0/dbhome_1
[oracle@as ~]$ echo $ORA_NLS33
/oracle/product/12.1.0/dbhome_1/nls/data
[oracle@as ~]$ echo $LD_LIBRARY_PATH
/oracle/product/12.1.0/dbhome_1/lib
[oracle@as ~]$ sqlplus
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

Note that I'm currently running as oracle user, which seems to be the owner of the /oracle folder with read and excecute permission. I also seen that a file named sp1us.msb exists in the following folder :

[oracle@as ~]$ find $ORACLE_HOME | grep sp1
/oracle/product/12.1.0/dbhome_1/sqlplus/mesg/sp1us.msb
/oracle/product/12.1.0/dbhome_1/sqlplus/mesg/sp1us.msg


texts: How do I track down wait events for a specific transaction in Oracle Enterprise Manager?

texts: I have an application that makes tens of thousands of calls per day to a stored procedure.  Normally this executes in about 50 ms.  However, periodically, it takes 2-3 seconds to execute.  I have an application log file that shows what time and how long each call to the stored procedure took.  I'm wondering...how can I track down this exact transaction within Oracle Enterprise Manager and display the associated wait events so that I can understand why this specific call is taking so long to execute.


texts: ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes

texts:
I got that error when I tried to execute this query:

select *
from lc_history2 lc
where  lc.view_name <> 'LC Main'
and lc.modifiedtime = (select max(modifiedtime) from lc_history2 l where lc.lc = l.lc)
 and lc.modifiedtime >= sysdate-30


texts: In what direction SQL Server WHERE clause conditions are checked?

texts:
I have this query:

SELECT RCodeID as 'ListItemID',
       RCode as 'ListItemName'
FROM RCode
WHERE RCode not like '%,%'
      AND ISNUMERIC(RCode) = 1
      AND CAST(RCode as int) >= CAST(@From as int)
      AND CAST(RCode as int) <= CAST(@To as int)
ORDER BY ListItemName;

So in what order (Right to left/left to right), these conditions are checked by the SQL Server?

Because I am getting those rows which have a , comma too, but I want to exclude them before ISNUMERIC(Rcode) is executed.

Data in the table:

+---------+--------+
| RCodeID | RCode  |
+---------+--------+
| 1       | 342    |
+---------+--------+
| 2       | 232,23 |
+---------+--------+
| 3       | 323xs  |
+---------+--------+

Input: @From=300 and @To=400

Expected Output:

+---------+--------+
| RCodeID | RCode  |
+---------+--------+
| 1       | 342    |
+---------+--------+

UPDATE

I moved the code with RCode not like '%,%' AND ISNUMERIC(RCode) = 1 in a view. The view is showing the correct data. But when I use that view in above query, the cast error for comma-seperated values is coming. If view is filtering that data then why those rows are coming?


texts: Considerable size difference between mysql versions

texts:
We usually employ different MySQL versions for different scenarios. And I realized that similar databases, whith the same amount of tables and data, just because of different mysql versions differs in size from 43M to 223M. (~5x more)

My goal is understand why of that 5x more disk space required..

Some version 5.5.46 have this size:

du -hs /var/lib/mysql/
43M

And some 5.7.11 habe this size:

du -hs /var/lib/mysql/
223M

Inside of it, we use to have those default datafiles like: ib_logfile0, ib_logfile1, ibdata1, mysql..

Within 5.5.46 thay usually have this sizes respectively:

5.1M   ib_logfile0
5.1M   ib_logfile1
26M   ibdata1
6.5M   mysql

..but within 5.7.11 are waaay bigger:

48M   ib_logfile0
48M   ib_logfile1
76M   ibdata1
12M   have also this ibtmp1
25M   mysql

In order to understand all that difference, I took a look to release notes where Oracle writes down what they're changing: https://dev.mysql.com/doc/relnotes/mysql/5.5/en/

But could not find something conclusive.
I appreciate your help on that.


texts: compare 2 tables with minus with a SDO Geometry shape column

texts:
if I have a column like SHAPE:

CREATE TABLE SOL_PUBLIC.PUNCTE_PERIMETRU_SIT
    (
      ID                NUMBER(10)                  NOT NULL,
      NUMAR             NUMBER(19),
      X                 NUMBER(19,2),
      Y                 NUMBER(19,2),
      FK_ID_CHESTIONAR  NUMBER(19),
      SHAPE
    )
    COLUMN SHAPE NOT SUBSTITUTABLE AT ALL LEVELS
    VARRAY "SHAPE"."SDO_ELEM_INFO" STORE AS LOB (
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      CACHE
      LOGGING
          STORAGE    (
                      INITIAL          64K
                      NEXT             1M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))
    VARRAY "SHAPE"."SDO_ORDINATES" STORE AS LOB (
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      CACHE
      LOGGING
          STORAGE    (
                      INITIAL          64K
                      NEXT             1M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))

when I try:

select * from (
select pub.nume_sit,cpu.* from
    SOL_PUBLIC.PUNCTE_PERIMETRU_SIT@RACDB cpu
    inner join SOL_PUBLIC.SOL_CHESTIONARE@RACDB PUB
        on cpu.FK_ID_CHESTIONAR=PUB.ID
minus
select prod.nume_sit,cpr.numar,cpr.x,cpr.y from
    SOL.PUNCTE_PERIMETRU_SIT cpr
    inner join SOL.SOL_CHESTIONARE PROD
        on cpr.FK_ID_CHESTIONAR=PROD.ID
) tmp_minus
ORDER BY nume_sit,numar;

it gives me an:

ORA-22804: remote operations not permitted on object tables or user-defined type columns

and if I make a:

select * from
    SOL_PUBLIC.PUNCTE_PERIMETRU_SIT@RACDB cpu
minus
select * from
    SOL.PUNCTE_PERIMETRU_SIT cpr

it gives me an:

ORA-22901: cannot compare VARRAY or LOB attributes of an object type

I need to insert the missing columns in the table where they are missing (so I need the SHAPE too).

I think I have to convert iti tu text to compare it and when I insert it into the table have to convert it back. How can I do it?

MANY THANKS!


texts: Is there a free software that can import excel into mysql on mac?

texts: not csv format files, but .xls or .xlsx format excel files, is there any free software for mac, that can import data into mysql?


texts:
I can't pick the right solution for my below requirement. Can you people please advise?

We have two SQL Server 2012 database servers:

First one is in Texas (USA). Our client will use this for UAT.
Second one is in Chennai (India). This one we will use for development/local testing.

While testing our application, the client will find issues/bugs in UAT environment, so we need to replicate to our development environment for further analysis, and to develop fixes for the bugs.

We are planning to sync two databases present in two different places. I am looking to synchronize the database in single direction. It should support write operations.

I have found a few potential solutions:

Transaction Log Shipping

In Transaction Log Shipping the secondary database is always in read only/restoring mode, so we can not perform any write operations. I need to do write operations to provide fixes for the issues/bugs. So I think log shipping will not be useful.
Database Mirroring and Always On High Availability

As a junior DBA, I don't have much knowledge of these. Please advise if these would fit for me.
Scheduling a daily job

Taking a backup of the UAT server and restoring it to our local development server, copying the database backup via FTP.

Can you please advise which solution will be the best fit for this?

Edit:-
           I would like to move data changes happening in Database 1(Server 1) to Database 2(Server 2). After moving changes to database 2(Server 2) it should be writable mode. And i don't want to move changes happening in database 2(Server 2) to database 1 (Server 1). So it's a single direction sync.

Below image will gives better understanding.


texts: Trigger to update a column in a new record to CURRENT_TIMESTAMP as it is inserted

texts:
Ok, so I'm trying to update a column to a CURRENT_TIMESTAMP in a specific record whenever the record is originally inserted. This is what I've got but otherwise I am lost.

CREATE TRIGGER dbo.TrgUpnInsert
  ON [CNLH Security Authentication].dbo.DateTimestamp
    AFTER INSERT,
    INSERT INTO [CNLH Security Authentication].dbo.EntranceInformation (DateTimestamp) Values (CURRENT_TIMESTAMP);


texts: How to enable implicit casts in PostgreSQL >=9.2?

texts:
Coming from this topic on SO a user suggest me to enable implicit casts in my PostgreSQL and I don't know how to do that. I also don't know if this is a good practice or if it's the solution to the issues I'm having but I want to try this one since others solutions isn't working. So find information on Google I found this:

  For enable implicit casts you must therefore execute the following
  commands in your PostgreSQL console when connected to the template1
  database, so that any database created afterward will come with the
  required CASTs (if your database is already created, execute the
  commands in your database as well):

CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int4out($1));';
CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.text(integer) AS IMPLICIT;
COMMENT ON FUNCTION pg_catalog.text(integer) IS 'convert integer to text';

CREATE FUNCTION pg_catalog.text(bigint) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int8out($1));';
CREATE CAST (bigint AS text) WITH FUNCTION pg_catalog.text(bigint) AS IMPLICIT;
COMMENT ON FUNCTION pg_catalog.text(bigint) IS 'convert bigint to text';

So my questions around this:

How do I execute that on template1? Any example will be helpful since I'm not a DBA just a simple developer and most of the time my tool & friend is pgAdmin or any other GUI on Windows
Can this be done easily from pgAdmin or any other GUI for Postgres or I need to work from command line?
Is this a good practice? Will be any performance issues afterwards?
Are this the only one queries I need to run to get ride of my problem?


texts: Is there a symbol I can use to have a where clause match all columns?

texts:
I'm using sql parameter's and I have a query that I'd like to use for two similar queries. The first uses two where clauses the other uses only one of the clauses is there a symbol I can use for the second query to always equal true?

For example:

SELECT path, markdown
  FROM public."Comment"
  where path = 'relative/path/' AND markdown = 'here I am';

Second example:

SELECT path, markdown
  FROM public."Comment"
  where path = '/other/relative/path/' AND markdown = ALL;

All being a stand in for some symbol in the query that would make it effectively only search based on the path (because the markdown where clause would return true on all columns).


texts: How should I store time series in mongodb

texts:
I need to create a database of time series, and perform the following tasks:

create new time series
update existing time series
query one or several time series at once (for instance all time series for the same date etc...)

Is Mongo adapted to that and if yes, how should I structure the database? (one time serie = one document? Or one document = one entry of the time serie, and all these documents form the collection which is the entire time series?)

I am a bit lost here and I find it difficult to find any information as usually Mongo is presented as very flexible so the user has the choice in the infrastructure.

Any link to tutorial that specifically explain how to manage time series in Mongo is very much welcome.

Thank you!


texts: Domain group hierarchical group membership and SQL Logins mapped to AD groups

texts:
SQL Version: Microsoft SQL Server 2005 - 9.00.4035.00 (X64)
    Nov 24 2008 16:17:31
    Copyright (c) 1988-2005 Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

This is the AD group structure:

Group A

User 4
Group B

User 1
User 2
User 3

I granted Group A login access to my SQL instance (connect and dataReader/writer to Northwind db).

I'd expect to be able to login to sql with User 1-4 but in fact only User 4 is allowed to login. If i grant Group B access then all users can login as expected.

So I'd venture to answer my own question and say "NO - sql only respects the 1st-level members of an AD sec group" but I wanted confirmation from someone else.

Logs show state 11 login failure which indicates "Valid login but server access failure."


texts: Does taking transaction log backups every 15 minutes consume more disk space than log backups every 6 hours?

texts: In our environment the network storage is low on space. At the same time I would like to make sure that we take transaction log backups every 15 minutes instead of current every 6 hours. My question is will changing the log backup interval from 6 hours to every 15 minutes consume more disk space?


texts: What are all the ways that a SELECT statement could be made to not terminate or take a *very* long time?

texts:
One way is using WITH RECURSIVE:
WITH RECURSIVE
  t AS (
    SELECT 1 n
    UNION ALL
    SELECT n + 1 FROM t
  )
  SELECT * FROM t;

Joins can be made to return a lot of rows from even small tables.
Tables themselves could be huge.
Views can be done with any such SELECT statement and so SELECT statements that use them may also not terminate.
Is that it? If SELECT statements did not have WITH RECURSIVE syntax, could not do arbitrary joins, were limited to small tables or hard a small LIMIT enforced, and could not use any such view, would they all be guaranteed to terminate in a reasonable time*?
I'm particularly interested in MariaDB/MySQL.
I'm thinking of making a new language based on SQL's SELECT statements that transpiles/compiles to SQL and I'm wondering what I can do to guarantee query termination in reasonable time*.
* What's reasonable time is of course subjective. I just mention it to exclude queries that are set to terminate in a time frame so long that we may as well treat it as non-terminating.


texts: Pull all parent records with or without child record - if child record exists select latest record

texts:
I'm building a table of ALL records on vacation watch which have not expired  and are not already scheduled for a visit

Select the property information
Select the mapzone cars
Select the last time the property was checked

My problem is if the property has never been checked the record is not selected

The code is:

FROM vacation_watch_master
LEFT JOIN property
    ON vacation_watch_master.MstrLink = property.MstrLink
LEFT JOIN property_map_carzone
    ON property.MapZone = property_map_carzone.MAP_ZONE
LEFT JOIN vac_watch_check_dates v1
    ON vacation_watch_master.VWLink = v1.VWLink
WHERE v1.VWDateLink = (SELECT max(VWDateLink)
                       FROM vac_watch_check_dates
                       WHERE vac_watch_check_dates.VWLink = vacation_watch_master.VWLink
                      )
HAVING vacation_watch_master.Watch_Expired  > -1
   AND vacation_watch_master.MstrLink NOT IN (
                              SELECT vac_watch_schedule.MstrLink
                              FROM vac_watch_schedule
                                             )


texts: Counting and grouping over multiple OUTER JOINs

texts:
I'm creating a view of advertisement logs (impressions, clicks, and clicks per impression). I have a simple table structure and some working queries but I'm having some trouble composing these into a single query that I can use as a view (not a materialized view, as this will be real-time data).

The tables are:

CREATE TABLE advert
(
  id integer NOT NULL PRIMARY KEY
);

CREATE TABLE advert_event
(
  code CHAR(1) NOT NULL PRIMARY KEY
);

CREATE TABLE advert_log
(
  advertisement integer NOT NULL REFERENCES advert(id),
  event_code CHAR(1) NOT NULL REFERENCES advert_event(code)
);

And some sample data that covers all possible cases:

INSERT INTO advert VALUES (1);
INSERT INTO advert VALUES (2);
INSERT INTO advert VALUES (3);
INSERT INTO advert VALUES (4);

INSERT INTO advert_event VALUES ('I'); -- Impression
INSERT INTO advert_event VALUES ('C'); -- Click

INSERT INTO advert_log VALUES (1, 'I');
INSERT INTO advert_log VALUES (1, 'C');
INSERT INTO advert_log VALUES (2, 'I');
INSERT INTO advert_log VALUES (2, 'I');
INSERT INTO advert_log VALUES (2, 'C');
INSERT INTO advert_log VALUES (3, 'I');
INSERT INTO advert_log VALUES (3, 'I');

For references, here is a set of the things I want to count in advert_log:

Query A.

SELECT * FROM advert,advert_event;

Result A.

 id | code
----+------
  1 | I
  1 | C
  2 | I
  2 | C
  3 | I
  3 | C
  4 | I
  4 | C
(8 rows)

Event counts by advertisement:

Query B.

SELECT DISTINCT advertisement,event_code,COUNT(*) OVER (PARTITION BY advertisement,event_code) FROM advert_log;

Result B.

 advertisement | event_code | count
---------------+------------+-------
             1 | I          |     1
             1 | C          |     1
             2 | I          |     2
             2 | C          |     1
             3 | I          |     1
(5 rows)

For any individual advertisement, the correct counts can be obtained by queries like:

Query C1.

SELECT COUNT(*) FROM advert_log WHERE advertisement=4 AND event_code='I';
 count
-------
     0
(1 row)

Query C2.

SELECT COUNT(*) FROM advert_log WHERE advertisement=4 AND event_code='C';
 count
-------
     0
(1 row)

Of course, my earlier query excludes zero counts, so it doesn't catch either of the above two cases.

Ultimately, what I am attempting to do is to turn the above numbers into the following, using clicks (the 'C' entries) divided by impressions (the 'I' entries) to derive the cpi column:

 advertisement | impressions | clicks | cpi
---------------+-------------+--------+-----
             1 |           1 |     1  | 1.0
             2 |           2 |     1  | 0.5
             3 |           1 |     0  | 0.0
             4 |           0 |     0  | 0.0 <- or NULL, NaN, 1.0, ...

My initial approach was to create a view for queries C1 and C2, and to call that function from a view based on Query A.

I suspect there is a simpler way to accomplish my goal with a single query.


texts: SSIS cannot deploy package to SQL server - An error occurred during decryption 15466

texts:
After recovering and re-importing a backup of a SQL Server 2019 instance none of my SSIS packages will deploy from Visual Studio.
Whenever I try and deploy I get the error
===================================

An error occurred during decryption. (.Net SqlClient Data Provider)

------------------------------
Server Name: SSRS02\HEALTHAWARE
Error Number: 15466
Severity: 16
State: 9
Line Number: 1

I have tried to reimport the backup of the encryption key that I have through the Report Server Configuration Manager, and have run the following and have seen it succeed, so I am assuming that my master key is correct, and I have the correct decryption password for the master key.
OPEN Master KEY
    DECRYPTION BY PASSWORD = 'xxxxxxxxxxx';
ALTER Master KEY
    DROP ENCRYPTION BY SERVICE MASTER KEY;
ALTER Master KEY
    ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY;
GO

I cannot however figure out what I need to do in order to get the SSIS Packages to deploy from Visual Studio


texts: Upgrading SQL 2016 AGs to SQL 2019 question

texts:
I am doing a rolling upgrade of my production SQL 2016 Servers to SQL 2019.  I have availability groups.  My current structure is 3 DR servers (secondaries), 3 HQ (on prem) secondaries and 3 HQ primaries.  I have updated 2 of the DR servers as per the upgrade instructions that I've found.  The instructions say the 2019 DBs should be in synchronized mode, but my 2019 DBs are in Synchronized/In Recovery mode.  I hope the "In Recovery" part is because the DRs are now SQL 2019 and the rest of the AG is SQL 2016 and when I failover from the primaries to the secondaries, there won't be any trouble.  Is this the case?
I am using this reference during the upgrade.


texts: Connection to MySQL fails via PHP while command-line works fine

texts:
I have a script to install MySQL 5.7 and run tests on Travis CI (running Ubuntu 12).

The script contains these lines, to install MySQL in non-interactive mode, with no password:

add-apt-repository 'deb http://repo.mysql.com/apt/ubuntu/ precise mysql-5.7-dmr'
apt-get update

echo mysql-community-server mysql-community-server/root-pass password "" | debconf-set-selections
echo mysql-community-server mysql-community-server/re-root-pass password "" | debconf-set-selections

DEBIAN_FRONTEND=noninteractive apt-get -o Dpkg::Options::=--force-confdef -o Dpkg::Options::=--force-confold -q -y install mysql-server libmysqlclient-dev

The install runs smoothly, and using the mysql command-line utility, I can connect to the server using no password:

mysql --user=root -e "SELECT VERSION();"

+-----------+
| VERSION() |
+-----------+
| 5.7.8-rc  |
+-----------+

Now if I try to connect using PHP PDO:

new PDO('mysql:host=localhost', 'root', '');

I get the following error:

  SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: NO)

I tried to allow root to connect from any host:

GRANT ALL ON *.* to root@'%' IDENTIFIED BY '';
FLUSH PRIVILEGES;

Without success.

What can be the problem?


texts:
I have a quite simple table with different values. When I search a specific column no results are given. What could be the error?

For example I search for an "id":

db=# select * from table where id = 160000;
 x  | y  | alt  |   lat    |   lon    |   id
----+----+------+----------+----------+--------
 80 | 80 | 2044 | 46.80125 | 12.99875 | 160000
(1 row)

Then I search for all "lat" with this value (limited to 1):

db=# select * from table where lat = 46.80125 limit 1;
 x | y  | alt  |   lat    |   lon    |  id
---+----+------+----------+----------+-------
 1 | 80 | 1365 | 46.80125 | 12.00125 | 31921
(1 row)

But with the same search for "lon" I get no result where there should be:

db=# select * from table where lon = 12.99875;
 x | y | alt | lat | lon | id
---+---+-----+-----+-----+----
(0 rows)

I did VACUUM and ANALYZE and now I don't know what to do else.

Test case:

CREATE TABLE public.table
(
  x integer,
  y integer,
  alt double precision,
  lat double precision,
  lon double precision,
  id integer,
  CONSTRAINT table_pkey PRIMARY KEY (id )
)

INSERT INTO table VALUES
    (1, 1, 0, 46.81375, 12.18625, 1),
    (1, 2, 0, 46.81375, 12.03625, 2),
    (1, 3, 0, 46.18875, 12.03625, 3),
    (1, 4, 0, 46.18875, 12.18625, 4),
    (80, 80, 0, 46.80125, 12.99875, 160000);


texts: What are the common reasons for a Subscription to be deactivated in SQL Server 2005?

texts:
I am trying to manage database replication in SQL Server 2005.  I have set up my publications, distributors and subscriptions and it seems to work properly at the start.

However, after a period of time, my subscription becomes deactivated, and requires reinitialization.

This is transactional publication, with a non-updatable, pull subscription.  The publication has the "Subscription Expiration" option "Subscriptions never expire, but they can be deactivated until they are reinitialized" selected.  The publishers and subscribers are on the same LAN.

What are the common reasons for a subscription to be deactivated?

For instance, could an insert failure on the subscriber like the following (due to badly handled schema change) cause a subscription to become deactivated?

Cannot insert explicit value for identity column in table 'SOME_TABLE' when
IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number: 544)

If this can cause a subscription to be deactivated, what are some other scenarios to look out for?


texts: Where has the Properties option gone when right-clicking a table in SSMS?

texts:
When I right-click a table in SSMS, I don't seem to have a Properties option. Is this normal? I seem to recall this was an option previously. I wonder if any Redgate tools I've installed have removed it.
What I would like to do is to manage the extended properties for a table via SSMS.


texts: SQL Server 2012 - sp_executesql multiple parameter error

texts:
I am receiving an error when attempting to execute the following code that builds a dynamic string to be executed by sp_executesql.

DECLARE @paramDefinition NVARCHAR(500);
DECLARE @ID1 NVARCHAR(20);
DECLARE @StageTable1 NVARCHAR(100);
DECLARE @StageTable2 NVARCHAR(100);

SET @ID1 = '111111111111111111';
SET @StageTable1 = 'TestTable1';
SET @StageTable2 = 'TestTable2';

DECLARE @sqlToExecute NVARCHAR(MAX)

SET @tableCreateSQL = 'SELECT @ID1
                       INTO   @StageTable1
                       FROM   @StageTable2 ';

SET @paramDefinition = '@ID1 NVARCHAR(20),
                        @StageTable1 NVARCHAR(100),
                        @StageTable2 NVARCHAR(100)';

EXEC sys.sp_executesql @tableCreateSQL,
                       @paramDefinition,
                       @ID1,
                       @StageTable1,
                       @StageTable2;

The error I receive states the following:

Incorrect syntax near '@StageTable1'.

Am I incorrectly constructing the SQL string, or is there an issue with the sp_executesql statement?


texts: Improve speed of index rebuild on SQL server

texts:
I'm importing a large amount of data into an empty database, and before I start I disabled all non-unique non-clustered indexes to see if I could improve the performance of the import.

Now I want to re-enable the indexes, and I'm wondering if there is anything that I can do to optimize this.

There are > 100 tables and almost 2,000 indexes to be rebuilt.  The database is 200GB in size.

The key section of the script I'm running is this:

declare c_toggle_index cursor FORWARD_ONLY READ_ONLY for
    select  'alter index ' + QUOTENAME(i.name) + ' on ' + o.name + ' rebuild'
    from    sys.indexes as i
    Inner Join sys.objects o
    On o.object_id = i.object_id
    Where o.is_ms_shipped = 0
    And i.index_id >= 1
    and i.type > 1
    and i.is_disabled = 1

I considered setting ONLINE=OFF for the alter index statement, but as the indexes start out disabled I wasn't sure that this setting would have any effect.  I also considered setting SORT_IN_TEMPDB = ON, but as the tempdb files are on the same drive as the .mdf files of the databases I assumed that there was also no benefit to doing that.

Whilst running the rebuild script I have noticed that I have a lot of CXPACKET wait types.  I don't really understand why that would be or if it's a problem that I should be looking to address.

One final point that may be relevant: my entire server is currently inactive other than this import of data into the database.  There is no other user activity to consider or worry about; my only concern is importing the data into the database in the shortest possible time.


texts: How do I change Postgresql to be listening on port 5432?

texts:
I've upgraded Postgresql from 9.3 to 9.6 on my machine (linux mint rafaella- so ubuntu 14.04)

As the previous version was listening on 5432 the new version is listening on 5433, but I want to change that to 5432 so that the previous configuration (rails, phppgadmin etc) work with the new postgres server.

I have changed the port in postgresql.conf from 5433 to 5432 and restarted postgres, but this didn't work:

$ sudo netstat -nltp |grep 5432
$ sudo netstat -nltp | grep 5433
tcp        0      0 127.0.0.1:5433          0.0.0.0:*               LISTEN      25467/postgres

I also tried setting the environment variable and restarting postgres:

PGPORT=5432; export PGPORT

again, still listening on 5433 and nothing on 5432.

trying to connect gives me:

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

What am I doing wrong?

Output of pg_lsclusters:

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

contents of pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5


texts:
I want to know the meaning of "2013.03010/permanent/0C94 6187 B194 DF07", Whether it means the end time of the SYBASE LICENSE?

sybase log

  00:00000:00016:2011/01/03 14:15:37.02 kernel  SySAM: Checked in
  license for 1 ASE_CORE (2013.03010/permanent/0C94 6187 B194 DF07).
  00:00000:00000:2011/01/03 14:16:53.08 kernel  SySAM: Using licenses
  from: /home/sybase/SYSAM-2_0/licenses
  00:00000:00000:2011/01/03 14:16:53.10 kernel  SySAM: Checked out license for 1 ASE_CORE
  (2013.03010/permanent/0C94 6187 B194 DF07).
  00:00000:00000:2011/01/03 14:16:53.10 kernel  This product is licensed
  to: BEV CORPORATION, an OEM license from Sybase, Inc.
  00:00000:00000:2011/01/03 14:16:53.10 kernel  Checked out license
  ASE_CORE    00:00000:00000:2011/01/03 14:16:53.10 kernel  Adaptive
  Server Enterprise (Enterprise Edition)


texts:
I use SQL Server 2008 R2 and know that in Indexed View cant have self join.

I have a tree table that have ID and ParentID Column and I need create indexed view on this table with self join between each record and parent record. can I simulate self join in this view?

EDIT

My table structure is :

 SAM.Detail (DetailID Int, ParentDetailID Int, Quantity Int, ...)

and my query is :

 Select A.DetailID,
       A.Quantity - SUM(B.Quantity) as RemainQuantity,
       COUNT_BIG(*) as CountBig
From SAM.Detail A
inner join SAM.Detail B ON B.ParentDetailID = A.DetailID
Group By A.DetailID, A.Quantity


texts: SQL to display one row as many based on one column

texts:
Is it possible to display one row as multiple rows based on the values in one column ON THE FLY?
Example...
Data:
column1 (text)  |  column 2 (text)
-----------------------------------
one             |  a; b; c

Desired Query Output:
a one
b one
c one

Again, I don't want to normalize this with separate tables and joining them.  E.g., I may have lots of these instances, so would prefer to perform this on-the-fly.
Forgive me; it's been many years since I touched SQL.  I want to see if the following is possible (and perhaps get some direction on how).
THANK YOU for any suggestions!


texts: Why pg_wal dir does not clean up while logical replication

texts:
my main question - why pg_wal dir is extremely growing up?

I have two postgresql instances.

The first was set up as master:

wal_level: "logical"
max_wal_senders: 5
max_replication_slots: 10

Also, I was enable archiving for pg_wal:

archive_mode: on
archive_command: 'gzip < %p > /.../data/pg_wal_archive/%f'

After that, PUBLICATION was created for some of my tables (not ALL TABLES)

For replication psql instance - I using default psql config, only created SUBSCRIPTION to master.

And I have two questions:

I run some script on master, which pulling ~ 10 Gb data to one table, which was not included in the PUBLICATION. And, I see, that my pg_wal dir size is increasing anyway. Why?
Why pg_wal dir does not cleaning? Is it normal? Looks like I have some data for restore table which is not using in replication, how to delete this data?


texts:
We have a requirement to configure a distributed availability group from Data center1 to Data Center2 using SQL Server 2017 Enterprise Edition.

DC1 (two nodes WSFC setup): SQLDB1, SQLDB2

DC2 (two nodes WSFC setup): SQLDB3, SQLDB4

The servers are in workgroups, not in a domain. Is it possible to configure distributed availability groups from DC1 to DC2 without domain?

As per my understanding, using certificates it's possible to configure Distributed SQL always-on if all four nodes are in same cluster. Is my understanding is correct?

Please guide me.


texts: Why does DBMS_JOB.SUBMIT execute immediately when NEXT_DATE is in the future?

texts:
I wanted to create a job that will run tomorrow at noon and recur every day at noon, so I executed this script:

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X
   ,what      => 'BEGIN  MyProcedure();  END;'
   ,next_date => to_date('24/04/2012 12:00:00','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'TRUNC(SYSDATE+1)+12/24'
   ,no_parse  => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
  SYS.DBMS_JOB.BROKEN
   (job    => X,
    broken => FALSE);
COMMIT;
END;
/

Job Number is: 131

When I looked at the status of job 131 it was executing already! THIS_DATE was 23/04/2012 13:16:12

Why did this job execute immediately?

Was it because of the SYS.DBMS_JOB.BROKEN call? I used TOAD to generate this script, and I changed the default broken => TRUE value that TOAD generated to be FALSE instead.


texts: How to create an "empty" partial index, or equivalent, in Postgres?

texts:
I have a boolean column that will be false for >99.9% of the rows. I need to efficiently fetch all rows where that column is true.

What's the best option? Creating an index on the column? Creating a partial index where the column is true? But I don't know what columns the partial index would / should hold.

This doesn't parse, but is there any way to do something like: CREATE INDEX mytable_cond ON mytable () WHERE cond = TRUE; ? Where the index holds literally zero columns?


texts: How do I copy SQL Server Data Discovery and Classification rules and suppressed recommendations to another database?

texts:
We have hundreds of SQL Server databases, all of which share a common schema. We are implementing Data Discovery and Classification policies. We wish to create one rule set and copy it to all like databases.
Using either SSMS or the Azure portal, it's possible to view recommendations for data classification, accept or suppress recommendations and create custom classifications.
It is well-documented that classifications are stored in sys.sensitivity_classifications and can be managed with ADD SENSITIVITY CLASSIFICATION and DROP SENSITIVITY CLASSIFICATION commands.
What is not documented as of this writing is where the suppressed recommendations are stored.
We wish to replicate the suppressed recommendations to all databases in our fleet, so that Azure Security Center will stop nagging us to finish classifying all our data.
Where are the suppressed recommendations stored? How can I copy the rules to other like databases?


texts: Providing SQL access to flat file (proprietary) database

texts:
We have a "database" that stores historical data in what could effectively be called a highly compressed proprietary "flat file" format.  Typically these are accessed and queried using some C++ code that accesses the files directly.
We would like to provide access to the data to a third party who would like to use SQL queries (e.g. MSSQL).
Are there tools, libraries, or software packages that we can use to present an interface, ideally something that could be connected to using MS SQL Server Management Studio to develop queries that when run, run against our flat file data (short of importing the data into a SQL database).
I have heard rumors of such tools but haven't seen anything yet in practice.


texts: Do the type-specific WKT- constructors function differently from ST_GeomFromText?

texts:
From the docs on MariaDB, for ST_LineStringFromText

  Constructs a LINESTRING value using its WKT representation and SRID.

  ST_LineFromText(), ST_LineStringFromText(), ST_LineFromText() and ST_LineStringFromText() are all synonyms.

It seems to be technically incorrect though, for example here we construct a point and a polygon using ST_LineStringFromText(),

WITH t(geom) AS ( VALUES
  (ST_LineStringFromText('POLYGON ((35 10, 45 45, 15 40, 10 20, 35 10),(20 30, 35 35, 30 20, 20 30))')),
  (ST_LineStringFromText('POINT( 0 0 )')),
  (ST_LineStringFromText('LINESTRING( 0 0, 1 1 )'))
)
SELECT ST_AsText(geom)
FROM t;

Returns..

POLYGON((35 10,45 45,15 40,10 20,35 10),(20 30,35 35,30 20,20 30))
POINT(0 0)
LINESTRING(0 0,1 1)

The Polygon functions are just as confusingly documented,

  Constructs a POLYGON value using its WKT representation and SRID.

  ST_PolyFromText(), ST_PolygonFromText(), PolyFromText() and ST_PolygonFromText() are all synonyms.

Yet you can replace ST_LineStringFromText with ST_PolyFromText and they work the same way.

MySQL Behavior

With MySQL 8 you seem to get an error on the above construction,

  WKT value is a geometry of unexpected type POINT in st_linestringfromtext.


texts: MySQL 5.5.8 InnoDB Foreign Key, JOIN performance

texts:
We are using aws/rds, MySQL 5.5.8. all InnoDB.

For performance purposes, we have merged some data-elements into varchar fields; mini-sized int and date type fields; added covering indexes; de-normalized some tables to behave almost like key-value stores; shareded tables containing user generated rows etc.

We are also looking to further improve insert performance by omitting some possible foreign key relationships between large tables (2+MM rows) since our app layer does a lot of similar integrity checking and prevention.

My question is about theoretical join performance between two tables A and B, using a primary key on one side A.F1 and single row index on side B.F2. Since I could also define the B.F2 as a foreign key (over A.F1), I would like to know if such a join would be any "faster/better etc." with or without the foreign key constraints - all other things being equal (indexes, no other fields, no order-by etc.).

We expect a lot of read queries over such situations, and fewer inserts that would benefit from foreign key omissions.


texts: How to check history of primary node in an Availability Group?

texts:
How do I see history of failovers of the primary node of a cluster? I'm trying to figure out at what point secondary node started to act as primary.

We are running Windows Server 2012 with SQL Server 2012 and Availability Groups.


texts:
Is there a recommended (official or not) style from Microsoft regarding naming and casing in T-SQL?

If there isn't one from Microsoft then is there one with broad acceptance?


texts: Shorthand for "UNIQUEIDENTIFIER" data type name?

texts:
The data type keyword "UNIQUEIDENTIFIER" is a chore to type.  I want a shorthand replacement.
At first glance, it seems that SSMS provides two; the words "GUID" and "UID" are both rendered in blue in the query editor (using the default syntax color scheme).
But they are not listed as reserved or future keywords in Microsoft Docs.  I tried to use them like non-reserved data type keywords (such as "DATE" or "INT")
Declare @guid uid;

and got this error message:

Msg 2715, Level 16, State 3, Line 1
Column, parameter, or variable #1: Cannot find data type uid.
Parameter or variable '@guid' has an invalid data type.

I can create a UDT for UNIQUEIDENTIFIER named "UID".  The result appears to behave as a non-reserved keyword:
Create Type [uid] From uniqueidentifier null;
GO
Declare @guid uid = newid();
Print @guid;
GO

B398DA7A-0DF2-49B6-8415-82D31EAA9013

So I have two questions.

Are the words "GUID" and "UID" actually keywords, or does SSMS color them for a different reason?
What considerations/risks might come with using the words "GUID" or "UID" as UDT names?  I'm mostly worried about future tSQL language compliance.


texts: PDO count(*) does not work with LIMIT and OFFSET

texts:
here's my code
it works normally, but this time i want to also count the records it gives. at the point of inserting COUNT(*) it will give NULL value to counted rows

        $query_list_records = "SELECT COUNT(*) from myl_news_comments WHERE news_id=:news_id LIMIT :ajax_read_how_many_comments OFFSET :items_already_loaded";
        $result_list_records = $db->prepare($query_list_records);
        $result_list_records->bindValue(':news_id', $_POST['news_id'] , PDO::PARAM_INT);
        $result_list_records->bindValue(':ajax_read_how_many_comments', (int)$ajax_read_how_many_comments , PDO::PARAM_INT);
        $result_list_records->bindValue(':items_already_loaded', (int)$_POST['items_already_loaded'] , PDO::PARAM_INT);
        $result_list_records->execute();
        $total_records = $result_list_records->fetchColumn();

echo $total_records;

how can i make this work ?

thanks
have a great night


texts: Need help identifying how to make a function selectable using table() clause and a custom data type

texts:
I would like to be able to run the following code to select records:
Desired Syntax
select * from table(my_new_package.get_data(123))

I have been successful in creating code that compiles, but I am unable to retrieve the data using the above syntax. Could anyone identify how I could update this to ensure the above query will run properly.
Here is the current code:
Package Spec
create or replace package my_new_package is

  -- Types
  -------------
  type my_data_type is record (
      myfield1          VARCHAR2(10)
      ,myfield2         VARCHAR2(10)
    );

   type my_data_type_TABLE is table of my_data_type index by binary_integer;

   -- Functions
   -------------
   FUNCTION get_data(i_id_number varchar2) return my_data_type_table;

end my_new_package;

Package Body
create or replace package body my_new_package is

  function get_data(i_id_number varchar2) return my_data_type_table IS
    current_row my_data_type;
    all_rows my_data_type_table;
    n integer;

    -- Will update the cursor to pull real data
    cursor cur is
      select '1' myfield1, '10' myfield2 from dual
      union all
      select '2' myfield1, '20' myfield2 from dual
    ;
  BEGIN
    n := 0;
    for rec in cur LOOP
      n := n+ 1;
      current_row := rec;
      -- do some things to current row

      -- Add modified current row
      all_rows(n) := current_row;
    END LOOP;

    return all_rows;
  END;

end my_new_package;

The above code compiles, but I can't select from the data using the desired syntax.
Is there anyway to do this where the custom types are embeded at the package level only and not created at the database level?
This will be for Oracle 12 (and Oracle 19).


texts: Manipulating a Column Containing Key/Value Pairs

texts:
I'm accessing and creating reports from a vendor via a replicated SQL Server database. They've done some absolutely insane things that I've been trying to solve for, but this one takes the cake.

They have a table that has many standard columns. But this table also has a column called "Data". The column is a legacy "text" data type, and it contains a giant (hundreds) list of key/value pairs. Each pair is separated by a CRLF, and the key and value are separated by an equal sign. Example:

select myTable.[data] from myTable where tblKey = 123

Result:

Key 1=Value 1
Key 2=Value 2
Key 3=Value 3
...
Key 500=Value 500

I'm trying to determine the most efficient way to break that column out into a usable table of data. The end goal would be to be able to query the table in a way that returns the table key along with specified key/values as column/fields as such:

tblKey | [Key 1] | [Key 3] | [Key 243]
-------|---------|---------|-----------
 123     Value 1   Value 3   Value 243
 124     Value 1   Value 3   Value 243
 125     Value 1   Value 3   Value 243

Is there a way to mold that column into a View? I can't imagine that a Function would be particularly efficient, but I'm sure I could parse things out that way using a string_split or something of that sort. Has anyone run into this type of atrocity before and found a good way to manipulate it into usable data?

Edit to add dbfiddle sample data.

The data is replicated from a vendor's source, so I can't create new tables. I can create views, procedures and functions. That's what I'm looking for advice for a decent way to accomplish.