texts: Most efficient table storage for determining recurrence

texts:
What is the most optimal table structure to store schedules / recurrence definitions so that we can efficiently query it for all events that will occur within a date range (min:1 max:32 days)?

My problem is currently MySQL but comments on other RDBMS will also help, since algorithms are mostly transferrable.

Consider the schema:  Event -|--has one or many--<- Schedules

Schedule is a record defining a recurrence, e.g.

Monthly, every month Monthly, first Wednesday of every month
Monthly, Monday and Tuesday of every 3rd month from start date
Monthly, Monday and Tuesday of every May, June and July
Weekly, every Tuesday through Saturday every week
Weekly, Monday every 3rd week from start date

My table will have about 10,000 records at most.


texts:
I works in a company that provide Dwh views as an API for external Customers (We use MS-SQL 2008 & 2012).

every once in a while we release new views and updates the existing views.

We have difficult to track and document all the updates and changes (currently it is documented in WORD document).

In addition we got complains from customers telling us that the documentation isn't detailed enough and they want to see Entity Relationship Diagram (ERD), primary and foreign keys - in addition to the descriptions of the views.

My first thought is to make it available online (like other APIs documentation I saw) rather then WORD or PDF document.

My question is, do you have any experience or any suggestion what is the best way to document Dwh views?
Do you know any automation tools (free or not) that can help with this task?

Thanks,


texts: How to convert an XML deadlock file into a graph or something readable?

texts:
I'm looking for a easy or manageable way to read an XML file, specifically the XML output for SQL Server deadlocks.  I have some servers that are not being monitored for various reasons.

I found the following code on the interwebz, but its output is nothing but XML.

CREATE TABLE #errorlog (
                        LogDate DATETIME
                        , ProcessInfo VARCHAR(100)
                        , [Text] VARCHAR(MAX)
                        );

DECLARE @tag VARCHAR (MAX) , @path VARCHAR(MAX);

INSERT INTO #errorlog EXEC sp_readerrorlog;

SELECT @tag = text
FROM #errorlog
WHERE [Text] LIKE 'Logging%MSSQL\Log%';

DROP TABLE #errorlog;

SET @path = SUBSTRING(@tag, 38, CHARINDEX('MSSQL\Log', @tag) - 29);

SELECT
    CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport,
    CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]', 'datetime')
    AS Execution_Time
FROM sys.fn_xe_file_target_read_file(@path + '\system_health*.xel', NULL, NULL, NULL)
WHERE OBJECT_NAME like 'xml_deadlock_report';

Is there a way to take the raw XML output from this T-SQL code and display it as a deadlock graph or more easily readable text?


texts: Why isn't this PosgreSQL LIKE query working?

texts:
PostgreSQL 9.2.

[local] postgres@host=# select * from units where ip_address LIKE '192.168.43.%';
ERROR:  42883: operator does not exist: inet ~~ unknown
 LINE 1: select * from units where ip_address LIKE '192.168.43.%';
                                              ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
LOCATION:  op_error, parse_oper.c:722
Time: 0.309 ms

What am I doing wrong?


texts: How can I get the host_name who creates a DB (proactive mode) in SQL Server 2008?

texts:
First, I want to clarify that I am not looking for a SELECT HOST_NAME().

I am managing a database environment and have given the developers permission to create the databases they need in the development environment. However, I want to store the machine name (HOST_NAME()) from where the database is created independent of the database user that they use for that purpose. This way, proactively, when a developer creates a database in the future I want to know who creates databases, not only by the owner user but by the name of the machine.


texts: Databases in Suspect state after enabling TDE

texts:
A few of my databases are in a suspect state. How can I get them online? I don’t know what info is relevant and what isn’t, but I’ll give as much as I can. Here is what happened:

I enabled TDE in about 100 databases by the below. (The cert tde_certificate was created on another SQL server, same version, if it matters.)

use master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPass'
--the key/cert was generated on another sql server (same version), if it matters
CREATE CERTIFICATE tde_certificate
FROM FILE = '\\dir\cert.bak'
WITH PRIVATE KEY (
file = '\\dir\key.pvk'
,DECRYPTION BY PASSWORD = 'StrongPass')

--then for each database:
use [SomeDb];
create database encryption key with algorithm = aes_256
encryption by server certificate tde_certificate

alter database [SomeDb] set encryption on

Then TDE appeared to be “stuck” at 0 percent, for all TDE enabled databases. It was in this state for a while, at least a couple hours (some dbs are really small too).

The error message in log showed:

  Please create a master key in the database or open the master key in the session before performing this operation. Database encryption scan for database ‘dbname’ was aborted. Reissue ALTER DB to resume the scan.

After there was a failover to the other node. All databases, except for the system and non-tde enabled ones were stuck in "RECOVERY_PENDING”.

I was able to get most of the databases online by opening the master encryption key and setting the db’s online by:

use master
open master key decryption by password = 'ReallyStrongPassword'
alter database [SomeDb] set online

This worked for all of the databases except for 3. Those db’s are now in a SUSPECT state.

I tried a repair with no luck:

use master
open master key decryption by password = 'ReallyStrongPassword'

ALTER DATABASE [SomeDb] set emergency
ALTER DATABASE [SomeDb] set single_user
DBCC CHECKDB ([SomeDb], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
alter DATABASE [SomeDb] SET multi_user
alter DATABASE [SomeDb] SET online

  Results:
  Msg 926, Level 14, State 1, Line 30
  Database 'SomeDb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
  DBCC results for 'SomeDb'.
  CHECKDB found 0 allocation errors and 0 consistency errors in database 'SomeDb'.

Also, I tried to restart the encryption scan, no luck:

use master
open master key decryption by password = 'StrongPass'
DBCC TRACEON(5004)
DBCC TRACEOFF(5004)

exec sys.sp_resetstatus
    @DBName = 'SomeDb'

ALTER DATABASE SomeDb
SET ENCRYPTION on

  Result:
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Warning: You must recover this database prior to access.

  Msg 5069, Level 16, State 1, Line 131
  ALTER DATABASE statement failed.
  Msg 9001, Level 21, State 1, Line 131
  The log for database 'SomeDb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

I noticed that whenever I fail over to the other node, all databases (except system) are all back in a RECOVERY_PENDING state. I need to open the encryption key and explicitly set the database online to get them online (all except for the 3 SUSPECT dbs). This happens every time.

I am on SQL Server 2008 R2 Enterprise, clustered with 2 nodes. I was able to enable TDE using the same cert in 4 other environments without any issues.

Any ideas on what I can do to get these databases online?

Edit: Here are the results from the error log. I filtered it down to "one of each" databases. One that was suspect and another that was in a recovery_pending state.

http://pastebin.com/wasNk7Xw

Edit: The files referenced within sys.master_files physically exist. I noticed that the STATE = ONLINE as well, sys.databases.state_desc = SUSPECT.


texts: Why is my Azure SQL (SQL Server) database getting overloaded with data IO for periods at a time?

texts:
I am running an Azure SQL database under the S2 edition (50 DTUs). Normal use of the server usually hangs around 10% DTU. However, this server regularly gets into a state where it will send DTU usage of the database to 85-90% for hours. Then all of a sudden it goes back down to the normal 10% usage.

Queries against the server from the application still seem to be operating quickly during this overloaded state.

I can scale the server from S2 => anything (S3 for example) => S2 and it seems to clear out whatever state it is hung in. But then a few hours later it will again repeat the same overloaded state cycle. Another weird thing I've noticed is that if I run this server on an S3 plan (100 DTU) 24/7 I have not observed this behavior. It only seems to occur when I have downscaled the database to an S2 plan (50 DTU). On the S3 plan I am always sitting at 5-10% DTU usage. Obviously underutilized.

I've checked into Azure SQL query reports looking for rogue queries, but I don't really see anything unusual and it shows my queries using resources as I would expect.

As we can see here though, the usage is all coming from Data IO. If I change the performance report here to show top Data IO queries by MAX, we see this:

Looking at these long running quires seems to point to statistics updates. Not really anything running from my application. For example, query 16302 there shows:

SELECT StatMan([SC0], [SC1], [SC2], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], [SC1], [SC2], step_direction([SC0]) over (order by NULL) AS [SB0000]  FROM (SELECT [UserId] AS [SC0], [OrganizationId] AS [SC1], [Id] AS [SC2] FROM [dbo].[Cipher] TABLESAMPLE SYSTEM (1.250395e+000 PERCENT) WITH (READUNCOMMITTED) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SC1], [SC2], [SB0000] ) AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 16)

But then again, the report also shows that these queries are only using a small percentage of the Data IO usage on the server (< 4%). I also run statistics updates (and index rebuilds) on the entire database on a weekly basis as part of its regular maintenance.

Here is another report that shows MAX data IO queries for a timespan that covers several hours only during the high-resource-usage incident.

As we can see, not really any queries reporting significant data IO usage.

I have also ran sp_who2 and sp_whoisacive on the database and do not really see anything jumping out at me (though I will admit I am not an expert with these tools).

How do I figure out what is going on here? I don't think any of my application queries are to blame for this resource usage and I get the feeling that there is some internal process running in the background on the server that is killing it.


texts: SQL Server error log entry : Error: 17806, Severity: 20, State: 14

texts:
I have error in my log for a few weeks, I searched a lot but I couldn't found useful answer.

I did closed SQL Server port for public IP, but I have problem yet.

  Error: 17806, Severity: 20, State: 14.
  SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed [CLIENT: 10.10.3.25]

  Time raised: 27 Jan 2015 2:23 PM

It was raised error while this system was off.


texts: Upgrade path - Oracle Standard to Oracle Enterprise

texts:
Could I start on Oracle Standard edition and easily upgrade to Oracle Enterprise?

Or do I need to rebuild the entire server? how does the upgrade path work?


texts:
My boss has a kindle and likes to save a lot of stuff to his clippings.  Currently I'm going through and cutting out each clipping and pasting it into a seperate word document with the title of the article.  I have managed to import the txt file into sql server and have generated an SSIS package to clean up the data and to provide me with what I want.  The part that I'm having difficulty with is being able to take each article and generate a word document.  Is there a way to do this with SQL Server and Intergration Services?

edit-
Definition of the data that I have:

create table dbo.Articles
(
PK_ID int identity(1,1),
Title nvarchar(50),
Article nvarchar(max)
)


texts: Database design for a car dealers website

texts:
I am creating a new website for all the car dealers in my region to show their cars on and i need your opinion on the database I have created because I have some doubts.

For instance, is it best to have a  VehicleID as a foreign key in the TechnicalSpecifications table or should it be the other way around, like having a TechSpecID in the Vehicles table? I am a bit confused and every time I think about it I lose my focus and can't figure it out.

I appreciate your input on my work so far and if you have any comments on anything please do share.


texts:
I have been given a query that takes about 1:30 to run. I was asked to optomize it as much as possible but am having issues making any headway on it.

SELECT DISTINCT B.PROV,
                FACILITY=CASE B.FACILITY
                           WHEN '' THEN (SELECT FACILITY
                                         FROM   CLDOCTOR
                                         WHERE  DOCTOR = B.PROVCODE)
                           ELSE B.FACILITY
                         END,
                C.PLNAME,
                C.PFNAME,
                C.PMNAME,
                C.DOB,
                C.SEX,
                Cast(Cast(A.DATEBACK AS VARCHAR(12)) AS DATETIME) AS 'DATEBACK',
                C.CITY,
                C.STATE,
                C.ZIP,
                C.HPHONE,
                C.WPHONE,
                A.SSNO,
                C.PTYPE,
                B.ACCOUNT,
                B.ORDERDATE
FROM   CLLAB A,
       CLORDER B,
       CLMASTER C
WHERE  A.COMPANY = B.COMPANY
       AND A.ORDER_ID = B.ID
       AND B.DEPT = 'LAB'
       AND B.ACCOUNT = C.ACCOUNT
       --AND DATEDIFF(MM, DATEBACK, GETDATE()) <=6
       AND A.DATEBACK NOT IN (SELECT DISTINCT DATEBACK
                              FROM   CLLAB
                              WHERE  COMPANY = A.COMPANY
                                     AND DEPT = 'LAB'
                                     AND SSNO = A.SSNO
                                     --AND DATEDIFF(MM, DATEBACK, GETDATE()) <= 6
                                     AND OBSERVE_ID = 'REVIEWED'
                                     )

CLLAB contains 34,185,327 rows, CLORDER contains 13,455,600 rows, and CLMASTER contains 392,797 rows.

Do you see anything that jumps out as inherently wrong that could be cleaned up to help the query run quicker?

Edit 1:
There are indexes on CLLAB's ORDERID, SSNO, and a few other unused columns.
There are indexes on CLORDERS's PROV, ORDERDATE, and a few other unused columns.

The execution plan says Missing Index (Impace 48.1371): CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] on [dbo].[CLORDER] ([DBO].[CLORDER][DEPT]) INCLUDE ([COMPANY],[ID],[PROV],[ORDERDATE],[ACCOUNT],[FACILITY],[PROVCODE]) which makes me think that there could be huge gains made by indexing CLLAB's DEPT


texts: Replacing master.dbo.sysperfinfo with sys.dm_os_performance_counters

texts:
I'm working with some old code that pulls performance counters, and part of what I'm doing is making sure we're doing some processes efficiently and effectively.

Right now I have this code that I'm looking at:

SELECT REPLACE(RTRIM(OBJECT_NAME), 'SQLServer:', '') AS 'Object',
       RTRIM(counter_name)   AS 'Counter',
       RTRIM(instance_name)  AS 'Instance',
       cntr_value            AS VALUE
FROM   MASTER.dbo.sysperfinfo
WHERE  OBJECT_NAME <> 'SQLServer:User Settable'

I'm proposing to replace it with:

SELECT REPLACE(RTRIM(OBJECT_NAME), 'SQLServer:', '') AS 'Object',
       RTRIM(counter_name)   AS 'Counter',
       RTRIM(instance_name)  AS 'Instance',
       cntr_value            AS VALUE
FROM   sys.dm_os_performance_counters
WHERE  OBJECT_NAME <> 'SQLServer:User Settable'

Which is faster and more compatible with everything past SQL 2000. There are no environments this will be used in that run SQL 2000 anymore, the earliest version the above code would be run using is SQL 2008. I've checked that the returned values are congruent and it works in the context of the rest of the code.

My question is: What are the differences between dm_os_performance_counters and master.dbo.sysperfinfo? Do I need to pull dm_os_performance_counters from the Master context?


texts: SQL Server Backup and Restore Incrementally

texts:
I am investigating a reporting solution for a production database.

We currently have a production db which backs up fully at 01:00 each day. This backup is then copied using a Robocopy job to a separate SQL db which restores it.

We then report on the second database in order to keep some extensive queries away from production.

I am hoping someone can help me with my thoughts - is it possible instead of having our current setup - to copy only updated or newly added rows across incrementally (note not deleted - I need these to stay in the second database)?

Thanks in advance.


texts: I want to know is there anyway to get CPU usage per user in MySQL? for example I have 3 different users 'user1', 'user2' and 'user3'. I want to get the average/real time memory usage and CPU usage of these users.I googled a lot but I did not find any suitable tool. Maybe I can get these data from MySQL queries, but I don't know where are they stored.


texts: Discrepancy in CPU time and reads in SQL Server profiler

texts:
I had performance degradation in our production database server which is SQL server 2016 and I saved the long running query using profiler as you can see
in the following picture:

CPU time = 65141, Reads = 36474959, Duration = 114546

I have saved the trace file and open later, so I'm not sure if duration is millisecond or microsecond.

But, now when I run the same query on prod outside of business hours, the query run fast and "set statistics IO on" shows only few data pages being read, my assumption was CPU time and reads of a query should be constant regarding of execution time of a query despite of duration, any thought?
Should I consider this query as inefficient based on the numbers?


texts: Sql Server Service Pack Update Notifications

texts:
Is there any option in Sql Server to enable notifications from Microsoft in a way that it sends us emails every time a new service pack is released?

I have multiple servers with different versions of Sql Server installed and I want the sql server to send me an email every time a new service pack is available

I read this article and it says there is no such option.

Sql Server Service Pack Updates Article I found online

But I would like to ask the question anyways in case if it changed during the latest releases.


texts:
There is a table estate with numeric fields districtru and districthe (for a name of a district in Russian and Hebrew), which refer to primary keys of the table districts. The districts table has the following fields:

id INT - primary ID
str VARCHAR - the name of the district in either Russian or Hebrew.

The question: Is it worth to add ENUM('ru','he') ("Russian" or "Hebrew") field to the districts table. It seems not to be strictly necessary as it is possible to conclude if a string in this table is a Russian or Hebrew name (or maybe both) by consulting the table estate (which of its keys districtru or districthe refer to this string in districts table).


texts:
SELECT TOP N seats.*, eventtickets.eticketprice from seats
INNER JOIN eventtickets ON eventtickets.etickettype = seats.seattype
WHERE ((Seats.seatType ='BOXSEAT')
AND seatID not in (select seatID from ticketsales WHERE eventID=6))
ORDER BY convert (int, seats.seatSection), seats.seatrow ASC

My query above generates the best available # of N seats, but it will fill up one section and then the rest of that order is put in the next section.  I need it to find a new section that has all N seats available.  IF a seat is in TicketSales table it is not available.  If salecategory is Null then that means ticket has not been SOLD/BLOCK/HOLD so its Available.

It is obviously limited to 6 seats together, but I do have one section which has over 30 seats in one row.

Section 1:
Row 1: 1-2-3
Row 2: 4-5-6

Section 2:
Row 1: 1-2-3
Row 2: 4-5-6

etc.

I am at my wits end.  Been staring at this for 2 days straight and my mind is mush. I am pretty weak at SQL, amazing I got this far above.  I think I have gotten close, but no enchilada.

See pics of the 2 main tables attached.  I just need the seatprice from eventtickets table so it's not that important.

I thank you for any help you can give me on this.


texts: Analyzing the log backups to understand the pattern

texts:
I am not sure if my understanding is correct on this behavior of log backups.

We believe the increase in traffic, as log backups have increased after migration event to cluster from stanadlone SQL 2012.

Per the monthly analysis of log backups running every 10 mins, in a day for an avg i calculated the log backup size to be 400-450 GB. Now post migration, its been week and i can see that log backup size has increased to an avg of 650-700 GB in a day.

App says there is no increase in volume, so i am wandering why there is sudden increase in log backups.

Log file is same before and after =200 GB and VLF's 300.

Database is 10 TB on SQL server 2012 and many a tables are partitioned.

Let me know if there is further details needed to understand this behavior? Or is there another way to analyse this? Please suggest


texts: Is there any difference between Rem and -- comments for SQL*Plus?

texts:
On SQL*Plus prompt, both Rem and -- qualify as comment indicators:

Rem this is a comment
-- this is also a comment
create table emp (
id number primary key,
name cvarchar2(40));

Is there any difference at all between the two commenting techniques?


texts: SqlServer Database replication in remote datacenter. Which kind of replication?

texts:
Disclaimer: I am a developer, not a DBA.
Scenario
Currently two servers (Web Edition) in a private cloud in one single datacenter in Europe.
1 DB per client + 1 common DB used by all clients.
We are extending to North America. We need to host NA customers in a NA datacenter.
Requirement: Replicate the common DB to the NA datacenter.
The size of the DB is currently 3-4GB. The bulk of the load is in a time series table, growing at around 15000 rows a day (~2MB) (+ other modifications in other tables, less volume).
What replication scenario would you recommend ?
I don't need immediate replication/mirroring. There can be some delay, although not too long (e.g. 1 minute seems reasonable (rule of thumb). If options include longer delay, this can be discussed). Allowing extra subscriber nodes would be nice to have.
From what I read on the Web, I believe snapshot replication is not a good idea for replication across long distances. Transactional and peer-to-peer replication look interesting. What would work best for me?
TIA


texts: DB2 TRUNCATE command - DROP STORAGE option

texts:
I performed action TRUNCATE TABLE TABLENAME IMMEDIATE; but I did not used DROP STORAGE option. I read that it is default option if it is not put.
It was not so big table regarding table space but I am still interested if command I executed released database disk space on the server or not? I am not able to conculde that based on current disk space.

I would like confirmation for this?
Also I would like to know did I have to do DB2 REORG for this table after the TRUNCATE?


texts: How to get a value from a table that a value is referenced from?

texts:
I've two Postgres tables.

CREATE TABLE questions (
    id serial primary key,
    question varchar(64) unique
);

and

CREATE TABLE answers (
    id serial primary key,
    answerer serial references users(id), -- todo left off here, did not have answerer column
    question serial references questions(id),
    answer int
);

I want to write a query that returns the answers table id (answers.id) and question text (questions.id) where the answer is 1 in one query.

I've tried a query:

SELECT (id, question) FROM answers WHERE answer=1 UNION SELECT question FROM questions WHERE id=(SELECT (id) FROM answers WHERE answer=1 LIMIT 1) LIMIT 15;

and it doesn't work.

How can I fix this?


texts: Creating a database under a different owner

texts:
I'm trying to set up a role in Postgres which can
a) Create a new Role
b) Create a new Database
c) Make that new Role the owner of the database
d) Have no other rights (as far as is possible!)

I have tried this:

sc_1=# CREATE ROLE tenant_admin CREATEDB CREATEROLE;
CREATE ROLE
sc_1=# CREATE ROLE user1 IN ROLE tenant_admin LOGIN NOINHERIT ENCRYPTED PASSWORD 'xyz';
CREATE ROLE
sc_1=#

Followed by (in another session)

tahaan@Komputer:~/projects/acme-project$ psql -U user1 -h localhost -d postgres
Password for user user1:
psql (9.3.6)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=> SET ROLE tenant_admin;
SET
postgres=> CREATE DATABASE "Tenant1" TEMPLATE "tenant_template";
CREATE DATABASE
postgres=> CREATE ROLE "owner1";
CREATE ROLE
postgres=> ALTER DATABASE "Tenant1" OWNER TO "owner1";
ERROR:  must be member of role "owner1"
postgres=>

Background:  The requirement is to have an automated function that can setup separate databases in a multi-tenant system.  My hope is that this function can be performed by a role that does not have too much rights.


texts: Choosing the right storage block size for sql server

texts: There are many articles on what storage blocks size should be used for sql server e.g. Disk Partition Alignment Best Practices for SQL Server. The right blocks size should improve the performance of a sql server database. I’m looking for a recommendations and methods to identify which storage blocks size is appropriated for a database. Is there a guide on how to identify an appropriated block size?


texts: oracle - list users with access to certain tables

texts:
I'm sure this has been asked before but I can't seem to find the relevant details for the following.

Is there some sort of pre-built table that can do the following (I have used dba_tab_privs but it is limited and does not meet all my needs), if not does anyone have some queries for answering the following?

List all users who have been assigned a particular role?
List all roles given to a user?
List all privileges given to a user?
List which tables a certain role gives SELECT access to?
List all tables a user can SELECT from?
List all users who can SELECT on a particular table (either through being given a relevant role or through a direct grant (ie grant select on atable to joe))? The result of this query should also show through which role the user has this access or whether it was a direct grant.


texts:
I am in a weird problem. Firstly some information about our env.
We have around 50-55 servers and around 100 instances on them.
Backups are configured through netback and that is using Nt Authority\System account.
Everything is fine for the 99 instances but in one whenever we enable this account to login some databases go in recovery state.
And when we disable the account than we can recover it with normal restore with recovery.

Any pointers will be highly appreciated.


texts: sql server replication - how to find and drop subscriptions?

texts:
I had 2 subscriptions to a publication,
but they were from the same subscriber server and writing to the same subscriber database, just one was bad and one was running fine.

I could not find a way to delete the bad one, so I decided to drop both, and then recreate the subscription.

  on the publisher server

use [SupplierDB]
go
exec sp_dropsubscription @publication = N'SupplierDB',
@subscriber = N'REPLON1',
@destination_db = N'SupplierDB',
@article = N'all'
GO

That dropped the good subscription.

Because I could not find a way to drop or even see the bad subscription I decided to drop the publication, but when I try it I get the following error message:

-- Dropping the transactional publication
use [SupplierDB]
exec sp_droppublication @publication = N'SupplierDB'
GO

  Msg 14005, Level 16, State 1, Procedure sp_MSdrop_publication, Line 75
  Cannot drop the publication because at least one subscription exists
  for this publication. Drop all subscriptions to the publication before
  attempting to drop the publication. If the problem persists,
  replication metadata might be incorrect; consult Books Online for
  troubleshooting information.

How can I find and drop this subscription?

This is a problem that I originally had on another question:
NoSync subscriptions will need to be dropped and recreated


texts: How to daily copy selected data from MySQL and PostgreSQL servers to another PostgreSQL server

texts:
I have two source servers, a MySQL 5.7 and a PostgreSQL 9.6, respectively. I would like to daily update some tables from each source to another target PostgreSQL 11 server (via a cron-job).

On the target server I also have some views involving the migrating tables that should update regularly accordingly.

I used pgloader for the initial MySQL -> PostgreSQL data migration and pg_dump for the initial PostgreSQL-> PostgreSQL data migration. However, because of the aforementioned views, the pg_dump command (see below) refuses to alter the tables because of the dependencies. The specific pg_dump command was

sudo pg_dump -x --no-privileges --no-owner --dbname=postgresql://<source_user>:<source_pass>@<source_server>/<source_db> -t 'table_1'  -t 'table_2' -c | psql --dbname=postgresql://<target_user>:<target_pass>@<target_server>/<target_db>

How could I run the data update without running into the dependencies issues? Moreover, is there a unified way to achieve the above updates for both MySQL -> PostgreSQL and PostgreSQL -> PostgreSQL? Ideally, I would like to avoid having the views defined as part of the update process. I am running on Ubuntu-18.04-bionic.


texts: Distribution database (recovery pending)

texts:
I was trying to create a distribution database and I succeded at first, but since I checked the box to configure SQL server Agent to start automatically, the last step of configuring the distribution failed, but the distribution database was created.

However, I haven't realized that, and in order to repeat the process from scratch, I deleted the data files and log files of the distribution database even before dropping the database, and I didn't realize that distribution database will be created in system databases. After deleting the mdf and ldf files I see recovery pending besides that database. Even after I tried to drop the dristribution database using sp_dropdistributor I still see the icon of that database with recovery pending. I just want to get rid of that database. What should I do?


texts: Should I disable triggers, timestamps in a slave in mysql?

texts:
We're using MySQL. We have a master that eventually will have 2 slaves. There are triggers in the db that execute when data changes in certain tables. I am wondering whether to disable the triggers in the slaves. It seems to me that I should. It also seems like things like timestamps should be disabled in the slaves because otherwise the data will be different on the slaves than the masters.

I'm not a DBA, I'm a developer. We don't have a DBA at my company so the ops admin and I are figuring it out as we go along. We already have the master and one slave set up and replicating and we are getting duplicate entry error on the slave.

We were thinking of not halting replication for duplicate replication errors, as in this post: http://www.ducea.com/2008/02/13/mysql-skip-duplicate-replication-errors/. We don't know whether this is a good idea. I do feel that this masks the problem, but neither of us knows how to fix the problem.

If we should disable the triggers and/or set the slave not to insert timestamps, how do we go about doing that? Or does establishing a db as a slave automagically do these things anyway?


texts: Error: The user specified as a definer ('Admin'@'%') does not exist

texts:
Error de SQL (1449): The user specified as a definer ('Admin'@'%') does not exist

I have this error when I'm trying to update a table. I don't know what I must do, because this error only happens with one table, but I don't want to damage other tables and the database.

I've searched how to fix it and I've executed this query:

  Transact-SQL:  show procedure status

But it doesn't show me the definer of my database.

What do I have to do without damaging the database?


texts: I'm trying to create a database for contacts. I must have many categories for every contact, like Home, Office, Emails, Phones. In every category one can add more than one value. Also the categories can be dynamic. How can this be designed in a database?


texts: Insert statement sometimes runs slow and sometimes fast SQL Server 2014

texts:
we have an SSIS package that starts around mid night and does bunch of things. one of the insert statement in that package is causing slowness sometimes, runs for 90 minutes and sometimes runs in 42 or 20 minutes and during weekends it runs in 7 minutes.

I have checked for any resource contention, there is nothing going on except the database maintenance that runs in parallel and with the maintenance running  in parallel the insert statement sometimes runs fast.

Environment :
virtual
Windows 2012 R2
SQL 2014 Enterprise
Memory : 120Gb, max sql memory : 112GB
vCPU's : 10

Initially i thought the issue might be with the memory because of the PageIOLATCH_SH and PLE going down at the same time and after increasing the memory the performance is still bad.

I'm thinking the amount the records they insert might be different for each days but app team specified that they are almost same ?

Do you think is there any issue with the Vmware environment, i'm thinking may be something with VM NIC card limitation ? our infratructre team already checked the disk performance and it looks good.

Execution plan for the insert statement looks totally good.

Can anyone shed some light on this ?


texts: Safe to use SQLNET.ALLOWED_LOGON_VERSION_SERVER in 11g sqlnet.ora file?

texts:
I have a mixed environment running both 11g and 12c databases on the same server.  One of our databases was recently upgraded to 12c, and the older Oracle clients have started throwing ORA-28040 errors on connection attempts due to the use of older authentication protocols.

On the database server, each instance is installed in a separate Oracle Home with it's own Oracle software.  The sqlnet.ora files in each ORACLE_HOME/network/admin directory have been created as a symbolic link to a central sqlnet.ora file on a shared NFS disk.

I would like to use the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter in the central sqlnet.ora file to allow connections from the older clients until they can be upgraded.  The concern I have is this:  If the parameter is only supported by 12c, I need to be sure that including it won't cause problems for connections to the 11g databases.  Is it safe to include this parameter in the sqlnet.ora file of an 11g database? Will it simply be ignored?


texts:
I have been asked to "rotate" the Certs and Keys for a given database and the only way I knew of to accomplish this was to unencrypt the data first, using DECRYPTION BY CERTIFICATE, create new certs and keys, and re-encrypt using the new cert and key.
I was then told that I could not have the data in an unencrypted state (readable to users) for any amount of time and the application could not be brought down to ensure security for any amount of time. Then I found ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD
My question is, will this REGENERATE accomplish the "rotation" of certs and keys (create new ones, which I think it will), and if so, will it leave the data in a "readable" state at anytime during the process?
SQL Server 2019 Standard Edition build 15.0.4083.2


texts: Parsing out date_entered field to capture TIME 'hh:mm:ss' based on time range

texts:
Based on the date_entered column, which is a data_type of 'DATE' how would I parse out the data to pull the time.

Values in the DATE_ENTERED field (DATA_TYPE is DATE) looks like this 5/15/2015 8:43:04 PM

I need to run queries based on when DATE_ENTERED is between 05/15/2015 8:00 AM and 05/15/2015 10:00 AM

I then need to incorporate that into a PL/SQL script that will run every 2 hours with the last two hours as the filter. My best explanation of this would be where it grabs the current date and time, and pulls 2 hours back, every 2 hours. This will allow it to run at 8am, 10am, 12pm, 2pm etc and have the last 2 hours of new entries in the query. For instance 10am run time will include results for items entered in from 8:00am until 10:00am


texts: How to avoid locking problems while disabling/enabling triggers in a PostgreSQL function?

texts:
Whenever I try to disable a trigger from within a function, I get following error:

ERROR:  cannot ALTER TABLE "datace" because it is being used by active queries in this session

It seems logical that tables are locked for ALTER TABLE command while used for reading writing by some function. But how should I disable/enable some trigger from that function then?

What I want to do is to update a column that should be protected from updates from outside the function. I thought this should be done through DISABLE/ENABLE trigger, but if this should be done some other way (or is not possible at all in PostgreSQL), I would be glad for such an answer too.


texts: Query logical processing phases while mixing old and new join syntax

texts:
Could anyone clarify why order of tables in FROM matters if I mix old and new syntax for joining tables?
For example,

SELECT *
FROM table1 a, table2 b
INNER JOIN table3 c ON (c.a_id = a.id) -- error - a.id - invalid identifier
WHERE (a.id = b.a_id)

SELECT *
FROM table2 b, table1 a
INNER JOIN table3 c ON (c.a_id = a.id) -- ok, table1 a is the last in FROM
WHERE (a.id = b.a_id)

I'm not going to use such ugly queries, but I believe all tables specified in FROM should be visible to other parts of the query.
I don't think it's up to RDMS because SQLServer, Oracle, and Mysql behave the same way - error for first query, no error for second. I'm definitely missing something obvious, but I didn't find anything that describes why it works this way.

Thank you.


texts: Data design issue. Need help with normalization

texts:
I am developing one web based payment system. I am facing problem in database design.

Following are tables and their fields:

Customer: cust_id    , name
Supplier: supplier_id, name
Employee: employee_id, name
Expanse:  expanse_id , name

group: group_id, group_name
(groups are: {1,Customer},{2,Supplier},{3,Employee},{4,Expanse})

and there will be one more table:

payment: payment_id, group_id, name(ids), amount

In above table, name field will contain id of respective group. Like if group id is 1 and name is 15 that is customer whose id is 15 (customer_id=15)

I want to know that above tables satisfy 3NF? if not then what is the correct way.

Also i want to know that if i want list of all the payment, which will have fields like: payment_id, group_name, name_of_person/expanse. What will be the query to get this list.


texts: Multiple one to many relationships in series

texts:
I am making a DB for storing reports that have following fields:

Ad_system,
Ad_Campaing,
Ad_Group,
Keyword,
Date,
Impressions,
Clicks,
Conversions

Logic is that, Ad_system can have multiple Ad_Campaings, Ad_Campaing can have multiple Ad_Groups and Ad_Groups can have multiple Keywords, and for every Keyword there is a report. Should It be like this? Because it kind of makes sense to me, but it doesn't look correct. Primary key can also be:

ad.system.name for first table

ad.system.name ad.campaing.name for second table

ad.system.name ad.campaing.name ad.group.name for third table

ad.system.name ad.campaing.name ad.group.name keyword for fourth table

They will always be unique, but then Report will always have all DB columns in it, so it's kind of redundant.  Is the approach that I have in the diagram below correct? Or is a better one what I just described? If I am not mistaken both should satisfy 3rd NF.


texts:
I am planning on making a comment-reply system inside of a site. (A simple one)

I searched through youtube related to this, and I found a good source. The problem is, he has a 2 separate queries, one for parent comment and two for reply to parent comment and what's more is that the second query is inside a foreach loop (bad practice). So, I need to revised of how he query the comments and the tables.

This is the table I came up with:

comment_table
id | post_id | comment_id | date | message | reply_id

//In the source, he has 2 tables, parent and child comment table.

I want to create a single query where parent comments and child comments are get back at the same time, but I am not sure if it will work.

Let's say I have a table like this.

id | post_id | comment_id |     date | message | reply_id
 1 |       2 |          3 | jan 2016 |   hello |
 2 |       2 |          4 | feb 2016 |  hello2 |
 3 |       2 |          5 | mar 2016 |  hello3 |        3

Note: reply_id is where the comment_id replied to.

I need to output this results like this:

Comments

hello2 //comment
hello //comment
   hello3 //reply

This is what I need to do:

Get all comments in DESC order by date while checking if reply_id is not empty, and if not empty, it will check it based on reply_id in DESC order by date.

My problem is, the I only thing that I know how to query is the first part and that is to Get all comments in DESC order by date, the rest is I do not know.

My question is, is this possible to query? If so, what syntax do I need here? If you have a better solution than mine, please tell me.


texts:
I am working on a small sqlite3 database for a small simple mailserver. I have created a new sqlite3 database and add executed this to pürepare it for data:

BEGIN TRANSACTION;
CREATE TABLE `mailboxes` (
    `username`  TEXT NOT NULL,
    `domain`    TEXT NOT NULL,
    `mailbox`   TEXT NOT NULL,
    FOREIGN KEY(`domain`) REFERENCES domains(domain)
    UNIQUE (username, domain),
    UNIQUE (mailbox)
);
CREATE TABLE `domains` (
    `domain`    TEXT NOT NULL UNIQUE
);
CREATE TABLE `aliases` (
    `aliasname` TEXT NOT NULL,
    `domain`    TEXT NOT NULL,
    `destination`   TEXT NOT NULL,
    FOREIGN KEY(`domain`) REFERENCES domains(domain)
    UNIQUE (aliasname, domain)
);
COMMIT;

Foreign keys are enabled with PRAGMA and no errors are shown. As you can see, mailboxes and aliases are nearly the same. I have a list of available domains. Every mailbox/alias entry must use a valid entry of "domains". In addition to this, a "username - domain" pair should only appear once. BUT: the same mail can appear in mailboxes and aliases.

PROBLEM

I have no entries in my domains table, but executing this works (but it should not, because it is not a valid domain):

INSERT INTO aliases (aliasname, domain, destination)
VALUES ('test', 'myacc.de', 'other@worldwide.com');

Where is my fault?


texts:
I have a table like this:
SQLfiddle: http://www.sqlfiddle.com/#!9/340c8

CREATE TABLE Table1
(ID int, empid int, time datetime, state int);

+--------------+---------------------+-----------------+
|    empid     |       time          |         state   |
+--------------+---------------------+-----------------+
|            1 | 2011-08-16 14:59:08 | 0               |
|            1 | 2011-08-16 15:00:06 | 1               |
|            1 | 2011-08-16 15:14:51 | 0               |
|            2 | 2011-08-16 15:15:00 | 0               |
|            1 | 2011-08-16 15:59:01 | 1               |
|            2 | 2011-08-16 15:59:09 | 1               |
+--------------+---------------------+-----------------+

Where 0 means login and 1 logout.

I am trying to get the total hours worked per employee per day:

1      2011-08-16     total hours worked    08:32:00

With my query:

SELECT CONCAT(
    MOD(TIMEDIFF(MAX(CASE WHEN state = '1' THEN time END),
    MIN(CASE WHEN state =    '0' THEN time END)), 24 ), ' hours ',
    MINUTE(TIMEDIFF(MAX(CASE WHEN state = '1' THEN time END),
    MIN(CASE WHEN state    = '0' THEN time END))), ' minutes') as HoursWorked,
    empid,
    Date(time)
FROM emplog T
GROUP BY empid, Date(time)

I get the time worked per day, but only for the min max in out times so I am missing all employees that come in and out several times per day. Any ideas?


texts: SQL Server Management studio slow connection or timeout when using Windows Authentication

texts:
I'm getting extremely long delays (10~30 seconds) in SQL Server Management Studio 2014 when attempting to connect to a SQL Server 2012 instance over TCP using Windows Authentication. This happens when connecting Object Explorer or a new blank query window. Once connected, running queries is fast. The problem does not happen when I connect using SQL Server authentication.

Environment:

Windows 7, logged in as a domain user
TCP connection via IP address (not hostname)
The server is at a remote location connected via VPN
No encryption

When I logged into a co-worker's Windows 7 computer with my domain account, and connected to the same SQL Server through the same VPN, there was no delay. When the same co-worker logged into my PC with his own domain account, he experienced the delay. These tests show that the problem is unique to my PC. Also, the problem only appears when connecting to this specific SQL Server and VPN; I can connect to other SQL Servers on the local network via Windows Authentication without any delay.

Things I've tried with no success:

Disabled anti-virus and firewall
Renamed the "12.0" folder under "%userprofile%\AppData\Roaming\Microsoft\SQL Server Management Studio" to "_12.0" to force SSMS to recreate my user settings.
Force Network protocol to TCP rather than <default>. I also tried Named Pipes but my server isn't setup for that.
Installed SSMS 2012 and tried that instead of 2014.
Disabled IPv6
Blackholed crl.microsoft.com to 127.0.0.1 in my etc\hosts file.
Disabled the Customer Experience Improvement Program in SSMS, Visual Studio, and Windows.
Uninstalled all SQL Server related apps from my PC and reinstalled just 2012.

TCPView clues:

Using TCPView, I noticed that when I make a new connection, its state becomes ESTABLISHED right away, but then one or two more connections with the SQL Server are continually attempted and closed with TIME_WAIT. On my co-worker's computer, these connections are ESTABLISHED and solid. So I'm pretty sure this is the source of the timeouts, but what are the connections for, and why do they fail? (I don't have any addons in my SSMS.)

Any ideas?

Update: Intellisense/Autocomplete clue(?):

I noticed that once I finally do connect, Intellisense/Autocomplete doesn't work. Do those require separate connections from SSMS? I tried disabling them, and it didn't seem to resolve the long connection delay.


texts:
After given up with MySQL i have tried Elasticsearch and now wan't to see if I could go with PostgreSQL/PostGIS that could allow me to go with PostgreSQL only.

I need to fetch records from a Table by distance (must not be exactly) and sort by distance. The table has 10 million records.

As I got for my Query on PostgreSQL slower speed then on MySQL I think I must do something wrong.

What I can do better?

Table:

id | hash_id | town | geo_pt2

geo_pt2 is geography

Index:

CREATE INDEX geo_pt2_gix ON public.member_profile USING gist (geo_pt2)

Query:

SELECT hash_id, town
     , ST_Distance(t.x, geo_pt2) AS dist
FROM   member_profile, (SELECT ST_GeographyFromText('POINT(47.4667 8.3167)')) AS t(x)
WHERE  ST_DWithin(t.x, geo_pt2, 250000)
ORDER  BY dist
limit 100 offset 1000;

Explain:

Limit  (cost=9.08..9.08 rows=1 width=53)
  ->  Sort  (cost=9.07..9.08 rows=1 width=53)
        Sort Key: (_st_distance('0101000020E610000088855AD3BCBB474052499D8026A22040'::geography, member_profile.geo_pt2, '0'::double precision, true))
        ->  Index Scan using geo_pt2_gix on member_profile  (cost=0.42..9.06 rows=1 width=53)
              Index Cond: (geo_pt2 && '0101000020E610000088855AD3BCBB474052499D8026A22040'::geography)
              Filter: (('0101000020E610000088855AD3BCBB474052499D8026A22040'::geography && _st_expand(geo_pt2, '250000'::double precision)) AND _st_dwithin('0101000020E610000088855AD3BCBB474052499D8026A22040'::geography, geo_pt2, '250000'::double precision, true))

I'm using PostgreSQL 10 on a modern Server with high IOPS (NVMe) and the Query need 35 sec.

After suggestions of @Evan Carroll much better performance:

EXPLAIN ANALYZE SELECT hash_id, town
     , ST_Distance(ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2) AS dist
FROM   member_profile
WHERE  ST_DWithin(ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2, 250000)
ORDER  BY ST_MakePoint(47.4667, 8.3167)::geography <-> geo_pt2
OFFSET 10000
FETCH NEXT 100 ROWS ONLY;

Limit  (cost=9.31..18.21 rows=1 width=61) (actual time=392.608..394.138 rows=100 loops=1)
  ->  Index Scan using geo_pt2_gix on member_profile  (cost=0.42..9.31 rows=1 width=61) (actual time=26.624..392.776 rows=10100 loops=1)
        Index Cond: (geo_pt2 && '0101000020E610000088855AD3BCBB474052499D8026A22040'::geography)
        Order By: (geo_pt2 <-> '0101000020E610000088855AD3BCBB474052499D8026A22040'::geography)
        Filter: (('0101000020E610000088855AD3BCBB474052499D8026A22040'::geography && _st_expand(geo_pt2, '250000'::double precision)) AND _st_dwithin('0101000020E610000088855AD3BCBB474052499D8026A22040'::geography, geo_pt2, '250000'::double precision, true))
Planning time: 89.020 ms
Execution time: 395.039 ms

If the user paginate to end it got slow:

EXPLAIN ANALYZE SELECT hash_id, town
     , ST_Distance(ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2) AS dist
FROM   member_profile
WHERE  ST_DWithin(ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2, 250000)
ORDER  BY ST_MakePoint(47.4667, 8.3167)::geography <-> geo_pt2
OFFSET 1000000
FETCH NEXT 100 ROWS ONLY;

Limit  (cost=9.31..18.21 rows=1 width=61) (actual time=28872.156..28873.239 rows=100 loops=1)

->  Index Scan using geo_pt2_gix on member_profile  (cost=0.42..9.31 rows=1 width=61) (actual time=32.441..28764.569 rows=1000100 loops=1)
    Index Cond: (geo_pt2 && '0101000020E610000088855AD3BCBB474052499D8026A22040'::geography)
    Order By: (geo_pt2 <-> '0101000020E610000088855AD3BCBB474052499D8026A22040'::geography)
    Filter: (('0101000020E610000088855AD3BCBB474052499D8026A22040'::geography && _st_expand(geo_pt2, '250000'::double precision)) AND _st_dwithin('0101000020E610000088855AD3BCBB474052499D8026A22040'::geography, geo_pt2, '250000'::double precision, true))
Planning time: 50.979 ms
Execution time: 28875.403 ms


texts: What would be the maximum number of records that can be indexed with a three levels B-tree ? B+ tree?

texts:
I am learning dynamic tree-structure organizations and how to design databases.

Consider a DBMS with the following characteristics :

file pages with size 2048 bytes
pointers of 12 bytes
page header of 56 bytes

A secondary index is defined on a page of 8 bytes. What would be the maximum number of records that can be indexed with a three levels B-tree? And with a three levels B+tree ?

Here are two examples of these trees :

My attempt

B+trees

I have read that

  B+ trees are shallower than a B tree. because only the set of the highest key denoted as k in each leaf node except the last one, is stored in the non-leaf nodes, organized as a B-tree. Relational DBMS Internals, chapter 5: Dynamic Tree-Structure Organizations, p.46

Therefore there is a difference, something we store in the nodes in a B tree is stored in the leaves in a B+ tree. Thus, to my mind it was (m-1)h (m being the order and h being the height) as far as each nodes contains at most (m-1) keys to another node. But this is not linked with the number of bytes.

Yet I found in the book mentioned above the following table :

Therefore would it be 203.7 number of records ?

B trees

For them, as far as some values are stored in the node, I have to do a division by the number of nodes. And I'm stuck there.


texts: How to Insert into with postgresql and JSONB

texts:
I am using Postgresql 9.5

I have two tables(product and product_temp) with jsonb colums

I would like to insert the values from  product_temp to product

but the name of the data inside the json is not equal
ex:

INSERT INTO product(data1->>'__COD__', data1->>'__PAGE__', job_id)
    SELECT data2->>'cod', data2->>'page', job_id
         FROM product_temp where  someid = '123'


texts: .DBC (FoxPro DBF) > FoxPro ODBC Driver > MIDDLEWARE > SaaS

texts: I've been searching for Visual FoxPro ODBC Driver and finally found it. I am using a middleware to connect a .DBC (foxpro) > MIDDLEWARE > SaaS. I see from postings on MSDN that Microsoft suggests using OLEDB instead of ODBC, however I'm trying to interface a data source with an application that connects to data sources using ODBC connections. Would this be possible?


texts: Why doesn't Posgres parse Arabic tokens correctly (using ts_debug)?

texts:
PostgreSQL has a few features related to full-text search. It knows how to parse a string into tokens (see parsers). For instance, here is how it parses Hello, friends and contributors. into tokens:

# SELECT * FROM ts_debug('Hello, friends.');
   alias   |   description   |  token  |  dictionaries  |  dictionary  | lexemes
-----------+-----------------+---------+----------------+--------------+----------
 asciiword | Word, all ASCII | Hello   | {english_stem} | english_stem | {hello}
 blank     | Space symbols   | ,       | {}             |              |
 asciiword | Word, all ASCII | friends | {english_stem} | english_stem | {friend}
 blank     | Space symbols   | .       | {}             |              |
(4 rows)

On my Ubuntu machine, this can handle Arabic just as well, as you can see here. Even if you can't read Arabic, you can see that it parsed out each Arabic word into a separate Arabic word token:

# SELECT * FROM ts_debug('واحد اثنين ثلاثة');
 alias |    description    | token |  dictionaries  |  dictionary  | lexemes
-------+-------------------+-------+----------------+--------------+---------
 word  | Word, all letters | واحد  | {english_stem} | english_stem | {واحد}
 blank | Space symbols     |       | {}             |              |
 word  | Word, all letters | اثنين | {english_stem} | english_stem | {اثنين}
 blank | Space symbols     |       | {}             |              |
 word  | Word, all letters | ثلاثة | {english_stem} | english_stem | {ثلاثة}
(5 rows)

But on my macOS machine, it doesn't seem to be able to parse Arabic words into separate Arabic tokens at all, it seems to think that all Arabic characters are whitespace:

# SELECT * FROM ts_debug('واحد اثنين ثلاثة');
 alias |  description  |      token       | dictionaries | dictionary | lexemes
-------+---------------+------------------+--------------+------------+---------
 blank | Space symbols | واحد اثنين ثلاثة | {}           |            |
(1 row)

How do I get Postgres to parse واحد اثنين ثلاثة into three word tokens (as well as whitespace tokens)?

Possible clue

The documentation does say this:

  The parser's notion of a “letter” is determined by the database's locale setting, specifically lc_ctype. Words containing only the basic ASCII letters are reported as a separate token type, since it is sometimes useful to distinguish them. In most European languages, token types word and asciiword should be treated alike.

My LC_CTYPE is set like this:

# SHOW LC_CTYPE;
  lc_ctype
-------------
 en_GB.UTF-8
(1 row)

However, this doesn't explain why on Ubuntu parsing Arabic works, but no on macOS. If I attempt to create a database with an Arabic locale on macOS, I get this error:

$ createdb --locale ar_AR.utf8 -T template0 hello
createdb: database creation failed: ERROR:  invalid locale name: "ar_AR.utf8"


texts: What is the difference between SQLEXPRESS and MSSQLSERVER?

texts:
I am using SQL Server 2008 R2, when I looked into the services for SQL Server I saw there are two different services with different instances.

SQLEXPRESS
MSSQLSERVER

Can anyone help me to get clear the difference between #1 and #2?

Thanks in advance


texts:
I'm trying to optimise and speed up a mysql 5.6 datatabase.

I have two tables with a many to many relationship:

Reservation and Resource. Each Reservation can have many Resources and each Resource can have many Reservations. I have a pivot table between the two tables which simply joins the two via their respective Id's

Should I be creating foreign keys within the pivot table for each of the ID's? Will this speed up queries or does this make little difference with a pivot table.

My initial thoughts are that I should but don't want to create a mess!


texts: Can I use `on delete set null` on a compound foreign key which contains a primary key column?

texts:
The docs say:

  Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior if you do not specify anything. (The essential difference between these two choices is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not.) CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well. There are two other options: SET NULL and SET DEFAULT. These cause the referencing column(s) in the referencing row(s) to be set to nulls or their default values, respectively, when the referenced row is deleted. Note that these do not excuse you from observing any constraints. For example, if an action specifies SET DEFAULT but the default value would not satisfy the foreign key constraint, the operation will fail.

But it isn't spelled out whether on delete set null will fail if only one of the foreign key columns is nullable, or if it will merely set the nullable part to null, which is it?


texts:
I'm working on a ASP/MS SQL project that performs a lot of calculations based on dates and time.  In one case, the system will lookup a value based on the nearest half hour increment throughout the day.

When designing the database should I create a lookup table with just the increments, for example: 00:00:00, 00:30:00, 01:00:00, and then create another table with the value and a FK to the hour increments?  Or should I include the time increments in another column with the values?  Or should I not include the increments and build the business logic within a function or stored procedure?


texts: Trigger to insert new row into second table causes "Connection is busy with results for another command"

texts:
I have created this trigger to insert new row into table @AL_ORDR_TRACKER after new row is inserted in table ORDR.

CREATE TRIGGER dbo.trgAfterInsertORDR ON dbo.ORDR
AFTER INSERT
AS
BEGIN
INSERT INTO dbo."@AL_ORDR_TRACKER"
        (U_DocEntry, U_Updated)
    SELECT
        DocEntry as U_DocEntry, GETDATE() U_Updated
        FROM inserted
END

The trigger gets registered but when inserting new rows into ORDR I get the error message:

Connection is busy with results fro another commands

What might be going wrong here?


texts: How to Analyze and Document a SQL Server Stored Procedure

texts:
I have been handed a large SQL Server batch process containing <100 stored procedures and functions. There is no documentation, I cannot rely on comments.

High level, what are the best practices to efficiently document the logic and data flow?

Can anyone point me to documentation, tools or other guidance?

EG: One process is an ETL that imports text files to tables, massages the data then loads target tables.   This ETL spans 2 dbs, about 20 procs/functions and 20 or so tables.  Probably <5000 lines of code. (Its pretty gnarly)

I need to become SME on this bad boy.  In the past it has been hunt and peck, walking the code chunk by chunk.  There must be a better way.


texts: How can I genereate a string from the returns of a SELECT query in Mysql?

texts:
I need to create a string from data that is extracted from an SQL query, and wish to accomplish it within the SQL Query.

The return is something like

+---------------+
| foo_ids       |
+---------------+
| 63901-63601   |
| 64101-63501   |
| 65101-63501   |
| 65001-63401   |
| 64902-63401   |
| 64901         |
| 62401         |
| 63901         |
+---------------+

I would like to do 2 things to it,

separate strings where there are "-"s
create a string of the ids separated by ","

the strings will only contain numbers and "-"

my final output desired is,

63901,63601,64101,65101,63501,65001,63401,64902,64901,62401

is this possible/realistic or should I have a script or something to fetch it and do some string manipulation in there?


texts: Reshape table by converting columns to rows

texts:
The problem

I'm trying to reshape a table with many columns. I'm trying to do it independently of the specific table, so I'm trying to do it for any table.

Let's use a very simple table foo.

CREATE TABLE foo (id int, a text, b text, c text);
INSERT INTO foo VALUES (1, 'ant', 'cat', 'chimp'), (2, 'grape', 'mint', 'basil');

select * from foo;

| id|  a  |  b |  c  |
|---|-----|----|-----|
|  1| ant | cat|chimp|
|  2|grape|mint|basil|

I want to transform the column a, b and c to rows.

This query works (for this specific table):

SELECT id,
       unnest(array['a', 'b', 'c']) AS colname,
       unnest(array[a, b, c]) AS colvalue
FROM foo;

|id|colname|colvalue|
|--|-------|--------|
| 1|  a    |  ant   |
| 1|  b    |  cat   |
| 1|  c    | chimp  |
| 2|  a    | grape  |
| 2|  b    | mint   |
| 2|  c    | basil  |

But I want to make it generic for any table with lots of columns.

What I've already done

To get all the columns I want to transform in rows, I can use:

SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name   = 'foo' and column_name ~ '^[a-z]$';

So using the previous query, I can do the following:

WITH tablecolumns AS (SELECT array_agg( column_name ) as cols FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'foo' and column_name ~ '^[a-z]$')
select id,
       unnest( tablecolumns.cols ) AS colname,
       unnest( array[a, b, c] ) AS colvalue
FROM foo, tablecolumns;

But I'm not able to replace array[a, b, c] with something dynamic. If I use:

WITH tablecolumns AS (SELECT array_agg( column_name ) as cols FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'foo' and column_name ~ '^[a-z]$')
select id,
       unnest( tablecolumns.cols ) AS colname,
       unnest( tablecolumns.cols ) AS colvalue
FROM foo, tablecolumns;

the result is not the values of the columns, but just the name of the columns.

Question

How can I unnest the values of the columns?


texts: How much overhead is involved with querying a DB on the same server?

texts:
History

The company I work for had two databases, DB A and DB B, on two different servers. A contains information pulled from a COTS application database in addition to information from our own developed applications. This was done to ensure the COTS application database was never disrupted as it is required 24/7/365.

Some of our users required raw data from A, thus B was born. B pulls data from A into it's own local tables and provides views for users. (I am not a fan of this design at all)

Now

Upgrades and migrations happened. B eventually found it's way onto the same server as A. We are considering cleaning up our database structure and have come up with several options. Keep in mind that access to data is a top priority for the users, but we want to cut down on costs. For simplicity, lets call the COTS database Z

Each option begins with: A pulls data from Z via OpenQuery.

The data is stored in 2 different tables on A, one for applications, and one for the views. The views are moved to A. This method benefits from never having data unavailable to users if an application lock the first table and being able to remove B all together.
The data is stored in 1 table in A. A copy is also stored in B at either the same time or later via scheduled procedure (I'm thinking the former). The views stored in B will continue to access the data in B
The data is stored in 2 different tables on A, similar to option 1. Database B is maintained in order to strictly provide views to the users from A. This would require the least amount of work for us and our tech illiterate users. Though, it seems silly to maintain a DB just because we are too lazy to set up some accounts and permissions.

I would really love to be able to have an option where data from Z is stored in 1 table in A, and everyone plays nicely, but that's not the case. Although we are the owners of this data, there are other groups that utilize it too. They've managed to lock our database on several occasions, so we're being safe.

I am the type of person who likes to try to make things as efficient as possible, so I am really leaning toward option 1, but many of my coworkers are liking option 3 and some even option 2. 1 and 2 are pretty darn similar, so I was wondering what kind of overhead is involved with accessing another database within the same server.

Perhaps there is an option we have not considered?

EDIT

Note: The tables in question are read only.


texts: General Definition of/for Primary Key for Table with Repeated Entries (i.e., not in 1NF)

texts:
my first post, my apologies if this does not belong here, please refer me to the/a right site.
  So I am looking for a general form for a primary key given a table containing non-atomic repeated entries in one-or-more fields. Let the fields with repeated entries be F1, F2, F3 . I am guessing that any primary key must contain  an ordered triplet in F1, F2 and F3 respectively , maybe also from some other field. Is this correct? I am thinking when I expand the table along F1, F2, F3 to avoid repeated entries, fields other than these three will necessarily repeat.

I am thinking of a table with employee_num, dep_code , emp_lname, emp_dependants, emp_education and emp_training , with the last 3 the only ones containing repetition. I would then expand the table into a larger one to avoid repeated entries. Then each multiple entry of F1 (emp_dependents in this table) would combine with each of the entries of F2, and with each entry of F3 , for a total of |F1|x|F2|x|F3| new entries, where |F1| is the number of repeated entries of F1, etc. , i.e., I need a new row for each dependent, each combined with a new row for each entry in education, etc.

EDIT: I guess I am asking whether there is a general formula for " first normalizing" (finding a PK) for a table with repeated entries.

So, would a triple from F1, F2, F3, the 3 rows with repeated entries give me a primary key for my table? Sorry I don't know how to upload diagrams; I don't even own a scanner.
Thanks.


texts:
I will be populating a Miles Per Gallon (MPG) table. It's coming from an odometer source.

It's currently set up as so:

  id               (primary_key)
, truck_num
, start_date
, end_date
, start_miles
, end_miles
, start_fuel
, end_fuel
, miles
, gals
, mpg

There seems to be some redundancy. The miles is  (end_miles - start_miles), ditto for gals.

Should we have those miles and gals columns precalculated and stored in the database? It would definitely make querying easier, but at the expense of space. Same question for having the mpg calculated. A computed column would slow things down, no?

What indexes would work best? There's about 3,000 trucks (records) inserted in a batch every week.

I'm using SQL Server 2008 R2.

Edit: A sample query that would I'd be using

-- find average mpg for since ytd
select m.truck_num, avg(mpg)
from mpg m
join truck t on t.truck_num = m.truck_num
where start_date >= @begin_of_year and end_date <= @today
group by truck_num


texts: Mysql - Load data infile query for Particular Values

texts:
I'm developing a Java swing application with back end Database MySQL . I've a screen to upload my datas to the Database using the Query like

LOAD DATA local INFILE  'D:\\TEMP\\import\\jan_jun2013.txt'
INTO TABLE slsdata
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Here the datas are like from January to June. But if i want to skip any month data or to upload only a particular month then how will i modify the above query ?

In my database table , the date column is 'SLDATE'.

The Same question i've asked in some other forum's, but i'm not able to get any suggestions there!

Any suggestions please.?


texts: Fast insert from temp table to main table - advice needed, match index order?

texts:
I'm re-writing a C# console app from single insert to do bulk inserts

based on advice here, I need to bulk insert into a staging table (with id identity seed)
Then I load into temp tables, do a little processing, and then insert into the main table

This is for a real time 24/7 so it needs to be as fast and efficient as possible

1) what is the fastest way to bulk insert data from c# console app, to staging table (every 1 min, approx 50,000-150,000 records) - I'm planning on using SqlBulkCopy

2) Does having id with identity seed on the staging table slow down things ? should i be using a heap?

3) My main question and the highest risk area is inserting to the main busy table so this needs to be as fast as possible to reduce locking: when doing the bulk insert into main table, should i be pre-ordering the data so it matches the main clustered index (its on 3 columns: sensorId, DateTime, pkId) or will the I just be sorting twice? this table is extremely busy.
Any other advice on this area appreciated.

this whole bulk insert process is just a Stored Procedure, and i was thinking of calling it every 1 min from sql agent to make sure it runs (not sure if this is best solution)

sql server 2017 ent.


texts: How can I execute multiple statements at once in MySQL Workbench?

texts:
I'm trying to execute multiple LOAD DATA INFILE statements at once using MySQL Workbench. However, they execute one at a time. Anyway of forcing them to run in parallel?

Thanks for the help!


texts: "Unable to Open BCP host data-file" using xp_cmdshell from SSMS but command line works

texts:
I have a stored procedure that runs on a job every once in a while to BCP some files left by an application I've written.

I noticed the files were piling up and BCP wasn't picking them up so I tested in Management Studio using the following:

DECLARE @sql VARCHAR(MAX)
DECLARE @path VARCHAR(512) = 'C:\BCPFiles\'

--Use BCP to copy files in character format from the target directly into the table.
SET @sql = 'bcp [MyDB].[dbo].[MyTable] in ' + @path + 'bcpFile.dat -c -T'

EXEC master..xp_cmdshell @sql

And got this error message

Executing from the command line on the same server using the same command works no problem.

Here's a few other observations:

I can run EXEC master..xp_cmdshell 'dir C:\*.*' and the results return as expected.
If I copy and rename the file to something different, BCP picks it up
The file isn't being left open by my application: I can move it, rename it, edit and save it, delete it etc... without any complaints from Windows.
The internal format of the file is obviously okay (see 2.)
NT SERVICE\MSSQLSERVER has Full Control permissions to the directory.
The Maple Leafs traded Dion Phaneuf yesterday

Any help is appreciated.


texts: Zero downtime copy/restore of a PostgreSQL database

texts:
I have a production server with static (no deletes/updates) PostgreSQL database, serving a huge amount of read queries.

Once a week or two I have to update my database with new data. Right now the process it pretty complicated, so I'm using a separate server to build new database from the scratch.

So, at some point I have live production server with outdated data and up to date database on some secondary server.

First I've tried backup/restore approach, but it resulted in 5 minutes downtime, which is not acceptable.

Then I've tried adding --single-transaction flag to pg_restore call, but it resulted in 2 minuted downtime, because all requests to PostgreSQL hanged for a couple of minutes after transaction was committed.

The best solution I came up with so far is to copy new database to production server under some new name and then switch my application to it.

But I wonder if there's any better solution, which will allow me to ship new data to production server without restarting my application.


texts:
I want to add about 1000 records to a table that contains 31 fields.

The worst part is those 1000 records are in the form of an excel sheet having some columns left unfilled. Is there any easy way to import this excel sheet into my table having the unfilled entries as NULL ?

Using Linux and MySQL


texts: Acceptable deadlock rate and how to diagnose them

texts:
Our system is doing about 50 inserts per second. During ~2 hour period we are seeing up to 3 of "Deadlock found when trying to get lock; try restarting transaction" conditions. Yes, the client restarts the transaction so it's not catastrophic.

Is this something I should start looking into, like ensuring order of accesses, or it's inevitable to have deadlocks at some concurrency level with InnoDB?

With C/C++ I didn't have much of a problem ensuring order of acquisitions of mutexes, but with SQL it's not obvious to me how to ensure orderly locking nor how to locate problem areas. For instance, is there a log-level I can turn on in MySQL that will show what concurrent SQL statement held a lock that prevented "this" statement to rollback (my error.log is empty). I understand this can have something to do with my indexes, but I'm not sure where to start.

One more question: if I split read accesses like reports and delegate them to read-only DB replicas, would that be helpful in avoiding deadlocks on inserts?

Now my deadlock has its own post: Simple conditional update causing a deadlock hints at MySQL bug


texts: MySQL compare multiple values in WHERE clause, find subset

texts:
I have a table segments with column comp_id, segment_id.
comp_id: 1
segment_id: 23,11,223,3331;
comp_id: 2
segment_id: 231,11,223,3331;

I have another table: comps with segment_id as well.
comp_id: 1
segment_id: 11, 3331
comp_id: 3
segment_id: 7, 63

I need to filter out rows from comp where all segment_id values are inside segment_id from segments table.
This is a simplified version of what I need to do. simple JOIN statements wouldn't help, I need to do this inside WHERE clause.
i.e.
WHERE
comp.segment_id = ALL (SELECT segment_id FROM segments)

this won't work because this compares single "comp.segment_id", I need all "comp.segment_id"s compared.
Is there any function or some method of doing this? And btw performance is crusial.
Is there any intersect methods to do so?
To simplify I need to find subsets of data.
Actual query is very big and I won't post it here, don't give me JOIN examples, please.


texts: How to access objects created by a different user

texts:
User A created few objects with schema "UserAPriv", but now he is either leaving the company or changing the department.
Now we need to restrict his access to his previously created objects and make some arrangements so that other team mates from previous project can access his objects and work normally


texts: MYSQL Optimize group by query with aggregators

texts:
SELECT  id_num
    ,   sum(expired) as expired
    ,   max(`date`) as max_date
FROM accounts
where `date`<=20170505
group by id_num;

The accounts table has a compound index on id_num,date and has about 100mil rows. This query seems pretty basic but it takes forever and I'm not sure how to break it up to speed things up. I thought about first creating a helper table for DISTINCT id_num (~3mil rows) but then I'm not sure how to get the sum(expired) and max(date) columns without joining the helper against the accounts table and doing the same thing as the original query.

CREATE TABLE `accounts` (
    `id_num` int(11) NOT NULL,
    `date` date NOT NULL,
   `time` datetime NOT NULL,
   `price` decimal(10,4) NOT NULL,
   `cost` decimal(10,4) NOT NULL,
   `time_slices` int(11) NOT NULL,
   `sub_expired` tinyint(1) NOT NULL,
   PRIMARY KEY (`id_num`,`date`),
   KEY `date` (`date`),
   CONSTRAINT `accounts_ibfk_1` FOREIGN KEY (`id_num`) REFERENCES `cust` (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

The date range goes back roughly 18 months and the data is pretty evenly distributed over all dates. The rows are being inserted chronologically and there are rarely updates/deletes.


texts: Idiomatic way to implement UPSERT in PostgreSQL

texts:
I've read about different UPSERT implementations in PostgreSQL, but all of these solutions are relatively old or relatively exotic (using writeable CTE, for example).

And I'm just not a psql expert at all to find out immediately, whether these solutions are old because they are well recommended or they are (well, almost all of them are) just toy examples not appropriate to production use.

What is the most thread-safe way to implement UPSERT in PostgreSQL?


texts: Why is Full Recovery model a requirement for an Availability Group?

texts:
We have all our production databases in simple recovery model, and are very happy about it because it fully satisfies our RPOs and RTOs.

Now we want to implement AG DR solution for our databases and we found out that full recovery model is a requirement.

My question is WHY? Theoretically, is there any technical reason why AG or other SQL Server DR solutions (Log shipping, DB Mirroring) shouldn't work with simple recovery model?


texts: MySQL Server Instance Configuration Wizard does not start for the first time

texts:
I tried to install MySQL on my machine (win 8 64 bit). I downloaded mysql-5.6.24-winx64.exe and after completing the MySQL 5.6 setup wizard, MySQL Server Instance Configuration Wizard does not start and nothing happens.

In the Windows start menu for MySQL I have only 2 options:

MySQL 5.6 command line client
MySQL 5.6 command line - Unicode.

So what can I do to resolve this problem and run MySQL on my machine.


texts: MySQL date range if data not available display 0

texts:
I've a table here here is the structure : id,id_berita,ip_user,tanggal

I try to use this query :

select count(id) as jumlah,tanggal from tb_trafik_berita WHERE (tanggal BETWEEN '2020-01-02 00:00:00' AND '2020-01-07 23:59:00') group by tanggal

and the output of that query,like this :

tanggal             | jumlah  |
____________________|_________|
2020-01-02 04:13:25 | 1       |
2020-01-02 04:13:39 | 1       |
2020-01-02 05:02:44 | 1       |
2020-01-03 13:23:22 | 1       |
2020-01-03 13:29:20 | 1       |
2020-01-04 01:36:45 | 1       |
2020-01-04 01:41:30 | 1       |
2020-01-07 14:37:08 | 1       |
2020-01-07 15:05:21 | 1       |

the output only showing data that available on table (only these date : 02,03,04,07)

if I want the output also showing date that not available on table : 02,03,04,05,06,07 , but put value 0,

so the expected output is :

tanggal             | jumlah  |
____________________|_________|
2020-01-02 04:13:25 | 1       |
2020-01-02 04:13:39 | 1       |
2020-01-02 05:02:44 | 1       |
2020-01-03 13:23:22 | 1       |
2020-01-03 13:29:20 | 1       |
2020-01-04 01:36:45 | 1       |
2020-01-04 01:41:30 | 1       |
2020-01-05 00:00:00 | 0       | <<< set 0 cuz data on table not available
2020-01-06 00:00:00 | 0       | <<< set 0 cuz data on table not available
2020-01-07 14:37:08 | 1       |
2020-01-07 15:05:21 | 1       |

I've try this query :

SELECT XX.dm AS 'Tanggal',IFNULL(YY.jumlah,0) AS 'Jumlah' FROM
(SELECT dm FROM
(SELECT CONCAT_WS('-',df,CONCAT(b,c)) dm FROM
(SELECT DATE_FORMAT(CURDATE(), '%Y-%m') df) a,
(SELECT 0 b UNION SELECT 1 b UNION SELECT 2 UNION SELECT 3) b,
(SELECT 0 c UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION
SELECT 9) c ) d WHERE DAY(dm) BETWEEN 1 AND DAY(LAST_DAY(dm))) XX
LEFT JOIN
(SELECT COUNT(id) AS jumlah,tanggal
FROM tb_trafik_berita where id_berita = '15'
GROUP BY tanggal) YY
ON XX.dm=DATE(YY.tanggal)
#add WHERE clause here
WHERE XX.dm BETWEEN 2020-01-02' AND '2020-01-07'
ORDER BY XX.dm;

but it's still has bug if i put WHERE (XX.dm BETWEEN '2019-12-13' AND '2020-01-11' ) filter date range in different year it showing like this :

the bug is begin from date 01 at 2020 only...

Anyone can help me to fix the bug ?


texts: Mysql query for get repeated customers list

texts:
I have a two tables oc_order and oc_customer. I want to get customers who order more than once.

This is sample:

oc_order:

order_id | cus_id | cus_name
-------------------------------
1        | 1      | Kumar
2        | 2      | Vimal
3        | 1      | Kumar
4        | 3      | Ramesh

oc_customer

  cus_id   | cus_name |  cus_address
    -----------------------------------
    1        | Kumar    |  Chennai
    2        | Vimal    |  Coimbatore
    3        | Ramesh   |  Selam
    4        | Deepan   |  Chennai
    5        | Ashok    |  Coimbatore

  For the above example i want to select only repeated order customer details only.

I mean now select only cus_id- 1 details, because customer one order more then one time.

This is the query am used

SELECT *
FROM oc_order o
LEFT JOIN oc_customer oc ON (o.customer_id = oc.customer_id)
WHERE  (SELECT *
        FROM oc_order
        WHERE customer_id=o.customer_id)


texts: Encryption in transit not working in MongoDB

texts:
I am using MongoDB 4.0.6 with 3 node replica set. I am upgrading my cluster to use SSL, below are steps which i am following :

1) generated CA.pem, server.pem and client.pem file and updated in my mongo config,

  dbPath: /xvdf/data/lib/mongodb
  journal:
    enabled: true
  engine: wiredTiger

systemLog:
  destination: file
  logAppend: true
  logRotate: reopen
  path: /var/log/mongodb/mongod.log

net:
  port: 27017
  bindIp: (IP address)
  ssl:
    mode: allowSSL
    PEMKeyFile: /etc/server.pem
    CAFile: /etc/ssl/ca.pem

processManagement:
  pidFilePath: /var/run/mongodb/mongod.pid

replication:
  replSetName: test

security:
  keyFile: /etc/mongodb-test-keyfile

2) After updating my config i restarted mongo, It is successfully started with SSL option.

3) Next i ran command db.adminCommand( { setParameter: 1, sslMode: "preferSSL" } ) so it will allow both SSL and nonSSL connection from client.

4) After that when i enabled SSL from client side, I updated my API config with "SSL : true" and with certificates, I am getting bellow error on mongo log file :

"Error receiving request from client: SSLHandshakeFailed: SSL peer certificate validation failed: certificate signature failure"

Please help me on this ASAP.

Thanks.. :)


texts: Backupthread Wait backup with ola.hallengren solution

texts:
I am running SQL 2014 Enterprise.   It’s about a 6 TB db.  It is mirrored and not clustered.  It is a 48 core physical server with a 3par SSD storage.

When I run the backup job manually, it sometimes acts like it stuck.  In the backup folder is the backup file that is 0 k.   In activity monitor, the wait type is Backupthread over many lines with the same spid.  When I kill the process even after 1 minutes or 2 hours it takes over hour to kill/rollback.   The same wait type exists.

Have you ever seen this before?

I am using the job to launch the backup manually.    If I run the command the solution generates from SQL Window, it appears to work normally.

Scott


texts:
Following case statement is not working.

case when (product_name = 'premium' AND SUM(answered_calls)>0 then (SUM(total_duration)/60.0)/SUM(answered_calls)) else 0 end AS Duration


texts:
Another simple SQL backup question from me....

I've got a SQL Server database running on SSMS 2008 and am trying to configure some backup jobs. I am now seemingly fine with backing up and restoring the databases but have been asked to backup from one of our servers to another's C$, though am having trouble doing so. I've got the line:

backup database MyDB to disk = '\\NetworkLocation\C$\Backups\backup.bak'

And am being given the error:

Cannot open backup device '\\NetworkLocation\C$\Backups\backup.bak'. Operating system error 5(failed to retrieve text for this error. Reason: 15105).

Which (if I'm correct) is an access denied sort of error.

Anyways.... I was told to 'just set permissions to C$' but haven't been able to find out how to, or even if that's possible. I have got it working such that the server will back up to a shared folder on the second machine if one's set up but the boss has asked, specifically, if it can be done straight onto C$. Am I just going about it the wrong way?
Many thanks


texts: PHP/MongoDB: Unset all fields in a document

texts:
I want to use PHP to remove documents from a MongoDB collection, but it's a capped collection (and it's stuck that way until I can get more memory), which means I can't remove documents. I have to do the next best thing. How can I unset all the fields in a document?

$collection->update(array('name' => 'Amy'), array('$unset' => array(WHAT GOES HERE?)));

Of course, I could list all the fields I want to unset, but then I'd have to change the code any time a new field was added. Can I unset all fields without knowing the names of all the fields?


texts: Details of transactions in SQL SERVER 2008 R2

texts:
I need following details:

Username of the user who initiated the transaction
Origin of the query  i.e. Whether Query executed in SQL server New
Query Interface or Transaction due to editing in Table or Via any
other application.

I know that all the transactions are saved in .ldf file.
Using sql query/other tools i was able to fetch some details but not all the above for all transactions.
Is there any way to get all the above information for all transactions.


texts: MySQL InnoDB Cluster - Add instance issue with local port

texts:
I have an issue to add instance to MySQL InnoDB Cluster using MySQL Shell. Here are the steps that I have done:

Login to one DB-01 node
Run command: mysqlsh
shell.connect('username@ip-address:port');
cluster = dba.createCluster('cluster-name');
Checking the cluster status: cluster.status(). Here is everything ok. I have one instance in the cluster.
cluster.addInstance('username@ip-address-of-db-02-node:port');
Getting an error.

MySQL log from DB-02 node contains the following errors:

[Repl] Plugin group_replication reported: '[GCS] Error connecting to all peers. Member join failed. Local port: 33061'
2020-12-26T14:40:29.234106Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'

It's same when I try to add DB-03 node into cluster. All servers are in the same VLAN and have been configured using mysql shell (there is same user and pass for all nodes). IP tables service is not enabled and SELinux is disabled.
When I tried telnet from DB-02 to DB-01 on port 33061, it's opened but when I run telnet from DB-01 to DB-02 then connection is refused.
Does anyone have an idea how to fix this issue?


texts: Is there a way to grant a user permissions to create certificate in SQL Server?


texts: concurrent insertion in mutually exclusive tables in oracle

texts:
There are three tables:

create table C(id_row integer primary key)

create TABLE A(col_a1,col_a2,col_a3,..., id_row integer primary key, constraint fk_a_c foreign key (id_row)  references C(id_row))

create TABLE B(col_b1,col_b2,col_b3,..., id_row integer primary key, constraint fk_b_c foreign key (id_row)  references C(id_row))

Table C is populated with a number of values.
I have to write an application that will allow its users to insert in table A or B an id_row already present in table C, constrained by the fact that a single id_row can be present at most in only one of A and B.

The application I have to write will establish a connection to the database for each user and start a new transaction for every id_row to be inserted.  The isolation level must be READ COMMITTED.

The procedure I would write to implement mutually exclusive inserts would be this:

 select * from table c where id_row =in_id_row for update; --lock the row in the parent table
 check id_row is not already in a or b, if it is raise exception;
 proceed to insert the row in a or b.

Does anybody know  other approaches to achieve this behavior without using   'select for update' or 'lock table'?


texts: DB design for my project (addressing people)

texts:
Here is my database design:

My first goal is easily grouping people and their own devices (this project will be for asset management) by country, city, office or state.

Second goal is preventing duplicate entries for the same office. Once an office is created with dependency information, other people need to be added with existing information of certain office.

What do you think about my design? Ideal or more complex than required?

edit:

Thank you for your wisely comment. If I change my structure like below, can an user add a city for multiple countries. Isn't it? Can add London city for England and France. As well as state or address. Sholud I prevent this condition with filtering to populate listbox with query?


texts:
I am performing a load test for my application. All my SQL queries which are fired from my application, individually look better and perform fast.

The same SQL are becoming bottleneck when they are part of a load where response time of this queries varies from 1 second to 100 seconds. There is no high usage of the CPU or memory in the database machine and there is no network latency as well.

I am wondering what else is causing this queries to perform slow under load conditions. During the load itself, sometimes I get the response time below a second, the same query gets response time of 100 seconds in another executions.

Are there any locks or waits happening during a synchronous access of the tables? Most of these queries are SELECT queries only which should not be the cause for locks or waits.

The application environment is Oracle 11g, JBoss 5.1. The elapsed times of the queries are calculated at JDBC level.

I/O looks quiet better, though there are few high physical read ratios. If the I/O is a bottleneck then it should happen for all the time, right? I get slow response times once in a while. If there are locks/waits happening, is there a way to find them out during the test? may be with the help of a perf stats or tool?


texts:
I'm considering using CLUSTER to reorder a table by an index. I understand that this recreation of the table data makes all the existing indexes either bloat or be useless. I've seen some indications that a REINDEX is required after a CLUSTER. I've found other references that indicate that CLUSTER does a REINDEX. The Official Documentation says nothing at all about REINDEX being part of CLUSTER or required (Although it does suggest running ANALYZE after the CLUSTER)

Can anyone definitively (i.e. with some sort of reference to official docs) say whether or not a REINDEX is required after a CLUSTER?


texts: switching from normal list partitioning to reference partitioning in oracle 11g

texts:
I have a main parent table and multiple child tables which have normal partitioning in it, wherein the partition column is repeated in all child tables. I want to switch to reference partition(oracle 11g). My child tables currently have 2 partitions each, both of which I need to drop and replace with reference partition.

Currently I am facing issues with dropping the last partition and adding the new reference partition using ALTER commands.

Is there a way to do this as I cannot drop the tables and lose the data
There are indexes created on the partition column.

The queries I am using:

ALTER TABLE CHILD_TABLE_1
    DROP PARTITIONS part1, part2
    UPDATE INDEXES; --- here only one partition was dropped

ALTER TABLE CHILD_TABLE_1 ADD PARTITION BY REFERENCE (foreign_key_with_parent);

this gives error

  Error: ORA-00902: invalid datatype SQLState:  42000 ErrorCode: 902


texts:
Here is my script of my db ,

CREATE TABLE [dbo].[MyTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Code] [nvarchar](25) NULL,
[Name] [nvarchar](25) NULL,
[dob] [date] NULL ,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
  (
[ID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
  ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
   ) ON [PRIMARY]

When I insert , I got this error

Cannot insert explicit value for identity column in table 'MyTable' when
IDENTITY_INSERT is set to OFF.

How can I solve it ?


texts:
I have a table which has three columns: account, idNumber, and position. The account and idNumber are unique.

Here is my thought to analyze and I don't know whether it is right or not.

account is primary key and account ->idNumber, account->position,
idNumber->account, idNumber->position

But I think that the idNumber->position dependency violates the Third Normal Form (3NF) rules (I am not sure.)

So, I decomposed it into two tables: one has account and idNumber, the other has account and position.  But this seems to waste storage space with one more account column.

So, I am confused - is the method I used correct?


texts: Difference between TDE/VPD/Data Masking/OLS - Oralce

texts:
I've come across many kinds of data masking techniques used in Oracle database.. some are licensed some are included in enterprise edition itself.. leaving the licenses thing behind as it is restricted to ask about it here..

I would like to know what is the difference between

Transparent Data Encryption

Virtual Private Database

Oracle Label Security

Data Masking pack

Oracle Vault

Ultimately each one of the above does the same job - mask the data right?

What are the differences between them??

If data masking pretty much only overwrites the data which I actually need to mask, won't a simple SQL update statement do that trick on a particular table? Why buy a tool for that?

Thanks!


texts: Very slow query despite index being used

texts:
I have this table, created to hold tons of modbus measurements that comes from my devices:

CREATE TABLE public.tv_smartdevicemeasurement_modbus (
    measurement_id serial NOT NULL,
    insert_time timestamp with time zone NOT NULL,
    data jsonb NOT NULL,
    smart_device_id integer NOT NULL,
    CONSTRAINT tv_smartdevicemeasurement_modbus_pkey PRIMARY KEY (measurement_id),
    CONSTRAINT tv_smartdevicemeasur_smart_device_id_62c12ed0_fk_tv_smartd
        FOREIGN KEY (smart_device_id)
        REFERENCES public.tv_smartdevice_modbus (device_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED
);

CREATE INDEX idxgin
    ON public.tv_smartdevicemeasurement_modbus USING gin (data);

CREATE INDEX tv_smartdev_insert__0ae03a_idx
    ON public.tv_smartdevicemeasurement_modbus USING btree (insert_time);

CREATE INDEX tv_smartdev_insert__1f0611_idx
    ON public.tv_smartdevicemeasurement_modbus USING btree (insert_time DESC);

CREATE INDEX tv_smartdevicemeasurement_modbus_smart_device_id_62c12ed0
    ON public.tv_smartdevicemeasurement_modbus USING btree (smart_device_id);

Now, the table has something like 200 million rows (and it's growing every day). I need to select with a simple datetime clause like this one:

SELECT data
FROM tv_smartdevicemeasurement_modbus
WHERE (tv_smartdevicemeasurement_modbus.insert_time > '2019-08-01'
AND tv_smartdevicemeasurement_modbus.insert_time < '2019-9-25')

But this simple and without hassle query takes up 8 seconds, as reported here with EXPLAIN ANALYZE VERBOSE.

The same with the track_io_timing enabled.

How to resolve this problem? Is there an effective long term solution?


texts: What kind of table structure to use to display product, testing, and test results?

texts: I am creating a database, but I am a beginner. We test several different kinds of paint, with specific test methods, and I would like to document all of the results. So for an example, I would test one type of paint for adhesion, blister resistance, and sag. But, the adhesion testing records both 1 and 7 day results. I have created a table already, and was informed the structure was completely wrong. I only have one table, with about 100 columns that has all of the testing results. An example of my column headings are: ProductCode, BatchNumber, Adhesion(1 day), Adhesion (7 day), Sag. And the product information and test results are listed below. How should this actually be structured?


texts:
Say I have a database Live on some instance. I have created a backup of it and now I need to restore it into a new database named Dev.
In SSMS I am choosing a device Live.bak, then in destination I am changing the name to Dev and in Files tab I see that database and log files are also renamed Dev.mdf and Dev_log.ldf. When I proceed the Live database changes its status to Restoring.

Now I tried to uncheck tail log option and it restored normally. Is it safe way to restore the database? I mean I don't want any changes to Live. Why it tries to restore Live database. I am confused.


texts: List the CMS SQL Server Instances using Powershell

texts:
I need to run a set of Powershell commands from my Central Management Server onto all of the SQL Server Instances that it contains. Can someone let me know how this could be achieved?

I need to loop each server instance from the central management and calculate/get the free space. I am using SQL Server 2012. I need to try out the following things:

Search particular extension files on Server Instances
Check the space occupied by them
Check Free Space
Store the above info. in a file on the current system

I am simply aiming for getting the size of backup files (.bak files) only. I have a solution already where we query Invoke-Command to find the files for us on remote machine. I wanted to know whether there exist a better solution to get the task done.