texts: Why Does UNPIVOT Work on Compatibility Level 80 DB?

texts:
I'm trying to pull data using UNPIVOT on a SQL Server 2008 SP3 database running at Compatibility Level 80. This should mean UNPIVOT doesn't work, but in my case it does under certain circumstances...

Works:

Independent SELECT query of the form:

SELECT...FROM...UNPIVOT...WHERE...GROUP BY

Doesn't work:

Same query, within a LEFT JOIN () on other tables on different databases within the same server. All at compatibility level 80.

I get the usual error message:

Msg 325, Level 15, State 1, Line 165
Incorrect syntax near 'UNPIVOT'. You may need to set the compatibility level
of the current database to a higher value to enable this feature. See help for
the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.

UNPIVOT workarounds seem cumbersome and I'd like this query to be self-contained and refreshable if possible. It seems logical that if I can get the query to work in isolation, it should be possible to use it in a JOIN.

Questions:

Why does this work sometimes?

How can I achieve the UNPIVOT sub-query inside the JOIN under these conditions?

Unpivot Sub-Query: (By request...)

SELECT
    YEAR(CAST('1-'+UnPiv.[Date] AS DATE)) "Year",
    MONTH(CAST('1-'+UnPiv.[Date] AS DATE)) "Month",
    CASE [Channel_Org]
        WHEN 'TPR' THEN 'ERP'
        ELSE [Channel_Org]
    END AS [Channel_Org],
    ISNULL(SUM(CASE WHEN [GL Desc] = 'EIP' AND
        [OPEX_Group] = 'Postpaid SAC' THEN [Amount] END),0) "Post EIP",
    ISNULL(SUM(CASE WHEN [GL Desc] = 'EIP' AND
        [OPEX_Group] = 'Prepaid SAC' THEN [Amount] END),0) "Pre EIP",
    ISNULL(SUM(CASE WHEN [GL Desc] = 'EIP' AND
        [OPEX_Group] = 'SRC' THEN [Amount] END),0) "Upg EIP",
    ISNULL(SUM(CASE WHEN [GL Desc] = 'MDF (OEM)' AND
        [OPEX_Group] = 'Postpaid SAC' THEN [Amount] END),0) "Post MDF",
    ISNULL(SUM(CASE WHEN [GL Desc] = 'MDF (OEM)' AND
        [OPEX_Group] = 'Prepaid SAC' THEN [Amount] END),0) "Pre MDF",
    ISNULL(SUM(CASE WHEN [GL Desc] = 'MDF (OEM)' AND
        [OPEX_Group] = 'SRC' THEN [Amount] END),0) "Upg MDF"
FROM [Channel_Steering].[dbo].[AQ1_OPEX_View]
UNPIVOT (Amount FOR [Date] IN ( [Jan-14],
                                [Feb-14],
                                [Mar-14],
                                [Apr-14],
                                [May-14],
                                [Jun-14],
                                [Jul-14],
                                [Aug-14],
                                [Sep-14],
                                [Oct-14],
                                [Nov-14],
                                [Dec-14])) UnPiv
WHERE   (   [Channel_Org] IN ('Retail','TPR')
                AND
            [GL Desc] IN ('MDF (OEM)', 'EIP')
        )
GROUP BY
    [Channel_Org],
    YEAR(CAST('1-'+UnPiv.[Date] AS DATE)),
    MONTH(CAST('1-'+UnPiv.[Date] AS DATE))


texts: SQLCMD command is not able to insert accents

texts:
I am trying to run sqlcmd.exe in order to setup a new database from command line. I am using SQL SERVER Express 2012 on Windows 7 64 bits.

Here's the command I use:

SQLCMD -S .\MSSQLSERVER08 -V 17 -E -i %~dp0\aqualogyDB.sql -o %~dp0\databaseCreationLog.log

And here's a pieceof the sql file creation script:

    CREATE DATABASE aqualogy
    COLLATE Modern_Spanish_CI_AS
    WITH TRUSTWORTHY ON, DB_CHAINING ON;
    GO
    use aqualogy
    GO
    CREATE TABLE [dbo].[BaseLayers] (
    [Code] nchar(100) NOT NULL ,
    [Geometry] nvarchar(MAX) NOT NULL ,
    [IsActive] bit NOT NULL DEFAULT ((1))
    )

    EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'Capas de     cartografía base de la aplicaicón. Consideramos en Galia Móvil la cartografía(...)'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'BaseLayers'

Well, please check that there are some accents on the words; which is the table's description . The database is created with no problems. 'Collate' is understood by the script, as you can see in the attached screenshot. Despite of this, accents are not properly shown when examining the table.

I'd really appreciate any help. Thank you very much.

[Edit]: Hi all. Changing the SQL file encoding using Notepad++ worked fine! Thank you very much for you help: I learned something interesting with this problem!


texts:
From SQLite FAQ I've known that:

  Multiple processes can have the same database open at the same time.
  Multiple processes can be doing a SELECT at the same time. But only
  one process can be making changes to the database at any moment in
  time, however.

So, as far as I understand I can:
1) Read db from multiple threads (SELECT)
2) Read db from multiple threads (SELECT) and write from single thread (CREATE, INSERT, DELETE)

But, I read about Write-Ahead Logging that  provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.

Finally, I've got completely muddled when I found it, when specified:

  Here are other reasons for getting an SQLITE_LOCKED error:

  Trying to CREATE or DROP a table or index while a SELECT statement is
  still pending.
  Trying to write to a table while a SELECT is active on that same table.
  Trying to do two SELECT on the same table at the same time in a
  multithread application, if sqlite is not set to do so.
  fcntl(3,F_SETLK call on DB file fails. This could be caused by an NFS locking
  issue, for example. One solution for this issue, is to mv the DB away,
  and copy it back so that it has a new Inode value

So, I would like to clarify for myself, it is necessary to avoid the lock? Can I read and write at the same time from two different threads? Thanks.


texts: Is it possible to write a function in postgresql which directly writes its parameters to a text file?

texts: I have a text file which contains some rows of data,say 10 rows. Each row has 5 columns, each of the type varchar. Is it possible to write a function in postgresql, which takes its parameters as 5 varchar values, and appends those values to that file directly?


texts: How to select records where NOW is between two dates in fields?

texts:
I've searched all over for this, and I seem only to find how to get records where a single date is between two "outside" dates.

I want to find out how to select records where the current date is between the value in the startDate field and the value in the endDate field.

What I have so far (PHP):

$now = new DateTime();
$sql = "
    SELECT content, image, imageHeight, imageWidth, link, linkDescription
    FROM news
    WHERE appearanceDate < $now
      AND $now < termDate
    ORDER BY appearanceDate DESC
";

But it doesn't work.


texts: Trace to find "error messages"( SQL Server 2008 R2 )

texts:
I'm using this config, trying to find error when a certain host executes a task ( it runs inside a software ):

The user says that the software is running, but it's not updating what it needs to update.

So, I'm tracking their queries and procs, to find some errors.

Is this correct? If I use this:

select * from table where

It shows me an error ( that's what I want ).

Do you guys use something different? Is this a good way to find errors using trace? I use HOSTNAME with his machine.

Again, it's working. I just would like to know some other tips.

Thank you very much.


texts: verifying non-additive join decomposition

texts:
I am reading a DMBA book and have come across non-additive (lossless) join property. The algorithm for decomposition into multiple tables is borderline useless for practical work, so I am frequently relying on intuition.

I am wondering, how does a typical DB designer tackle decomposition? By that I mean, is there a helper tool that takes a relation schema + functional dependencies as an input and outputs possible lossless-join decompositions?


texts: MySQL Slave lag in SHOW SLAVE STATUS does not match SHOW PROCESSLIST

texts:
I'm trying to figure out why the reporting of slave lag is different in SHOW SLAVE STATUS and SHOW PROCESSLIST, in MySQL 5.5.13.
The only difference to other slaves of this master is that it is replicating over a relatively slow connection, about 10Mb/sec (cross site).

SHOW SLAVE STATUS reports a slave lag of 0, or occasionally, the real slave lag, once every 5-10 requests. (I'm looking at Seconds_Behind_Master)

SHOW PROCESSLIST, shows the slave lag under the system_user's time column (the one that belongs to the replication sql thread), like so:

mysql> show processlist \G
*************************** 1. row ***************************
     Id: 1
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 63953
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 61077
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL

Why would these differ? Or rather, why would SHOW SLAVE STATUS lie? The monitoring system is looking at this command, and goes crazy due to "spikes" once in a while.
I know for a fact the right number is what SHOW PROCESSLIST reports, since the slave took a good few days to be initialized, and is catching up slowly.


texts: Will Temporal Tables be included in the Standard Edition of SQL Server 2016?

texts:
The new version of SQL Server 2016 wil support so called Temporal Tables.

I can't find any documentation that would say whether this feature would be Enterprise-only or available in Standard edition as well.

On the one hand, Temporal Table Considerations and Limitations suggests it will be Enterprise-only:

  By default, the history table is PAGE compressed.
  An optimal indexing strategy will include a clustered columnstore index [...] The default history table has a clustered rowstore index

As far as I know, data compression is an Enterprise-only feature, but technically Standard edition could create a non-compressed history table.

Columnstore index is an Enterprise-only feature, but by default it will create a rowstore index, so there is still hope.

On the other hand, documentation for sys.dm_db_persisted_sku_features is the same for 2014 and 2016 and the list of Enterprise features doesn't have Temporal Tables (maybe the documentation is not updated yet?).

Does anyone know, maybe unofficially, what is the likely variant?
If someone has the latest 2016 RC installed, could they tell us what sys.dm_db_persisted_sku_features returns if the database has Temporal Tables?

If there is a good chance that this feature will be included in Standard Edition, I could wait for half a year until the release of 2016 before migrating my 2008 database to the latest version. If there is no hope, I would migrate it to 2014. If I migrate to 2014 it will likely stay on this version for the next 10 years.

Regarding putting this question on hold:

I don't want an "opinion-based" answer to this question, I'd like to know the facts. I had hoped that this kind of information would have been published somewhere, and I just didn't know where to look. For example, until today I didn't know about sys.dm_db_persisted_sku_features.

The questions could be adjusted to:

How and when are SKU decisions made?
How would one know that decision was made?
If not for this version, how was it done for previous versions?
Is this kind of information published when the RTM version is released or earlier?

If a hard fact is not known (the decision is not made yet and even SQL Server developers don't know), an educated guess would be also great, for example, based on how this decision was made in previous versions.


texts:
We're looking at utilizing a new proprietary high performance database engine for OLAP and OLTP scenarios. One important aspect of it right now is that it does not support foreign key or other constraints. What is your opinion on whether this limitation should be considered a non-starter for OLTP applications?

Would it be crazy to think about implementing our own referential integrity checks for any inserts, updates, and deletes?


texts:
I have a table that looks like this:

Table:

CUSTTRIPID  TPTRIPID    CUSTOMERID  ENTRY_TOLLTXNID EXIT_TOLLTXNID  ENTRYTRIPDATETIME   EXITTRIPDATETIME    TRIPIDENTMETHOD TRIPCHARGETYPE  ENTRYLANEID ENTRYPLAZAID    EXITLANEID  EXITPLAZAID VEHICLENUMBER   VEHICLESTATE    VEHICLECLASS    VEHICLEID   TAGREFID    TOLLAMOUNT  FEEAMOUNTS  DISCOUNTSAMOUNT OUTSTANDINGAMOUNT   TRIPSTAGEID TRIPSTATUSID    TRIPSTATUSDATE  POSTEDDATE  PAYMENTSTATUSID CREATEDDATE CREATEDUSER UPDATEDDATE UPDATEDUSER REFLINKTRIPID   PLAZA
39038181    34040337    20272679    35126165    35126165    2017-05-04 08:15:54.000 2017-05-04 08:15:54.000 980 P   NULL    NULL    NULL    NULL    KA03D8435   KA  VC10    272614  34161FA8203289720249F4C0    30.00   0.00    0.00    30.00   3   1   2017-05-04 08:31:54.657 2017-05-04 08:31:54.657 456 2017-05-04 08:32:11.130 TRIPPOST    2017-05-04 08:32:11.130 TRIPPOST    0   BangaloreNelamangalaTollPlaza

I want to retrieve CUSTOMERID more than 2 records with time so I have written this query and got the result.

Main Query:

SELECT   TOP (10) [CUSTOMERID] , [ENTRYTRIPDATETIME]
FROM     [ISSUER].[TOLLPLUS].[TP_CUSTOMERTRIPS]
GROUP BY [CUSTOMERID],[ENTRYTRIPDATETIME]
HAVING   COUNT(*) > 2

Result:

CUSTOMERID  ENTRYTRIPDATETIME
20063381    2016-12-08 00:24:35.000
20010438    2016-12-16 22:16:55.000
20015278    2017-01-28 05:18:20.000
20108092    2017-02-10 00:37:09.000
20013275    2017-01-06 07:42:05.000
20018833    2017-01-27 04:53:32.000
20113994    2017-01-27 01:13:44.000
20199820    2017-01-27 05:34:24.000
20124122    2017-02-01 02:47:01.000
20240710    2017-03-21 13:02:18.000

But my requirement is to split the Date and Time in the abobe result, I mean there should be 3 Columns, so my question is where I will be able to write below query in my Main Query?

CONVERT(VARCHAR(10),[ENTRYTRIPDATETIME],101) as DatePart,
CONVERT(VARCHAR(10),[ENTRYTRIPDATETIME],108) as TimePart


texts: Handling modifications of fields within a document

texts:
I have a basic web app where with the following hierarchy being persisted

>User1
  >Client1
    >ID
    >Name
    >Profession
  >Client2
    >ID
    >Name
    >Profession

>User2
  >Client1
    >Favorite Meal
    >Favorite TV Show
    >Favorite Animal
  >Client2
    >Favorite Meal
    >Favorite TV Show
    >Favorite Animal

So basically, each users have clients and each user's clients have different fields that I want to persist. Under a single user, all the clients will have the same structure. So what I'm doing is persisting each user with nested clients in it's own document within a Clients collection.

What bugs me now is I do not know what design would be good for the following feature :

I want my Users to be able to change the fields that are persisted. So if I take the example above, User1 could remove the ID field if he doesn't need it anymore and replace it with anything else or simply not replace it at all.

1 - Should I allow my user to be able to modify the design or is that a bad idea from the get go ?

2 - If it is not a bad idea, how should I handle these changes when they are made? Should I simply (I know I'm referring to columns but these are my old relational habits)

Delete a column when a column is deleted
Rename a column when is is renamed
Add a column when it is added and let all the old entries have null as a value for this new column

Or should I create a new document every time a modification is made to the data of a user and try to move all the data that fits from the old document into the new one. Then delete the old document.

Are there any other good design patterns that could apply.


texts: Nested Dynamic Query with SP_EXECUTE_REMOTE for binding the Parameters

texts:
I'm having two Azure Databases Lets say DB1(primary) and DB2(secondary).
I've Created two External Data sources for both the DB1 so that they can talk to each other

External Data Source for DB1 is External_DB2
External Data Source for DB2 is External_DB1

I'm having a Sp in my DB2.Lets say the sp is sp_DB2(@param1)
Like that another sp in my DB1.Say  sp_DB1(@param1,@outparam)
(Ps:@outparam is a output parameter)

My sp_DB2 is the Main Sp (which calls all other Sp i.e, sp_DB1).

Inside my sp_DB2 i have the code like

EXEC SP_EXECUTE_REMOTE
N'External_DB1',
N' DECLARE @param1 nvarchar(max),@outparam nvarchar(max)
EXEC SP_EXECUTESQL N''EXEC DBO.[sp_DB1]@param1,@outparam OUTPUT'',
N''@param1 nvarchar(max),@outparam nvarchar(max) OUTPUT'',
@param1 ,@outparam OUTPUT
SELECT @param1 AS Paramvalue,@outparam AS outputvalue'

Since sp_Execute_remote doesn't support the output param.I done like the above.But here the thing i want to bind the @param1 value from the Original Sp (sp_DB2).

If i tried like this it works,

EXEC SP_EXECUTE_REMOTE
    N'External_DB1',
    N' DECLARE @param1 nvarchar(max)=''Testing'',@outparam nvarchar(max)
    EXEC SP_EXECUTESQL N''EXEC DBO.[sp_DB1]@param1,@outparam OUTPUT'',
    N''@param1 nvarchar(max),@outparam nvarchar(max) OUTPUT'',
    @param1 ,@outparam OUTPUT
    SELECT @param1 AS Paramvalue,@outparam AS outputvalue'

Where i hardcoded the Value of @Param1 as Testing in the Code.

But actually the value of @Param1 should come from the original Sp which is sp_DB2

When i call the Original Sp like sp_DB2(Testing) It should bind with the @Param1 for the other Sp which is sp_DB1

By doing this we can reduce the Hardcoded value of Testing inside the Sp

Pls somebody help me to solve this

Thanks in Advance !
Jay


texts: Does CUBRID have Information Schema Views?

texts:
I am trying to access the Information Schema views as defined in SQL92, and cannot find any in the CUBRID RDBMS. Has CUBRID implemented this specification and how can I access them?

CUBRID version: CUBRID 10.1, manual: https://www.cubrid.org/manual/en/10.1/

There is something called System Catalog: https://www.cubrid.org/manual/en/10.1/sql/catalog.html, is this possibly the same, and why is it not called "information_schema" as specified in the SQL92 specification?

When querying the database for my table book i get following:

SELECT * FROM db_class WHERE class_name LIKE 'book';

class_name = book
owner_name = DBA
class_type = CLASS
is_system_class = NO
partitioned = NO
is_reuse_oid_class = YES
collation = iso88591_bin
comment =

Are there no schemas in CUBRID?

        Configuration configuration = new Configuration()
                .withJdbc(new Jdbc()
                                .withDriver(DRIVER)
                                .withUrl(URI))
                .withGenerator(new Generator()
                                .withGenerate(new Generate()
                                                .withDaos(true)
                                                .withImmutablePojos(true))
                                .withDatabase(new Database()
                                                .withName(DATABASE)
                                                // .withIncludes(".*")
                                                // .withExcludes("")
                                                .withInputSchema("jq"))
                                .withTarget(new Target()
                                                .withPackageName(PACKAGE)
                                                .withDirectory(DIRECTORY)
                                                .withClean(true)))
                .withLogging(Logging.TRACE);

        GenerationTool.generate(configuration);

I need it for code-generation with JOOQ, what catalog/schema do I have to specify?


texts: MS Access linked table displays short text instead of Decimal data type

texts:
I have linked tables in MS Access databases (various versions, 2007-2019) which are connected to SQL server 2017 via Sql server 2017 drivers (eg, msodbcsql_17.2.0.1_x64). Fields that are of data type Decimal(38,20) in SQL server are showing as type Short Text in the linked tables.

According to the Documentation, the data types are directly comparable.

Why might this be coming through as a different data type?


texts: Do OR conditions in Oracle SQL queries spoil index use / cause full table scan?

texts:
I have a query on Oracle 11g of the following type, which results in a series of highly inefficient full table scans:

select t.Id, t.ObjectId, t...[other Tasks columns]
    , od1...[ObjectData1 columns]
    , od2...[ObjectData2 columns]
from Tasks t
left join ObjectData1 od1 on t.ObjectId=od1.ObjectId
left join ObjectData2 od2 ...
... [more leftjoins] ...
where t.ObjectId = 12345
or t.Id in (
    select TaskId from ObjectAffectingTasks where ObjectId=12345
)

Tasks has indices on Id, ObjectId; ObjectAffectingTasks has index on both ObjectId and TaskId. All joined tables have proper indices, too. The ObjectAffectingTasks table contains task IDs affecting the object, but having another in the ObjectID, so all Tasks affecting object with ID 12345 shall be selected.

When analyzing the query, it seemed to be the OR condition that spoiled the execution plan. Where clauses with only ObjectId or only the subquery were using all the indices. Another workaround was to create a Union subquery, which also used the indices:

where t.Id in (
    select TaskId from ObjectAffectingTasks where ObjectId=12345
    union
    select Id from Tasks where ObjectId = 12345
)

I would, anyway, expect the original query and IN subquery to be translated into a semi-join with index lookup on both Id and ObjectId. However, I see the IN subquery is internally translated into EXISTS (select 0 from...)

May this be only a statistics problem, or does the OR condition spoil it all? Is it possible to lookup the indices on Tasks and ObjectAffectingTasks with such an OR condition?

Unfortunately, the queries are created by rather complex program code, so rewriting them would be way too much work. Another issue is that they're a bit generic, being used for both Oracle and MS SQL Server, so proprietary hints for index usage are not an option, either.


texts: PostgreSQL extension installation, best practices

texts:
I have a SQL only postgres extension I want to make public and share on GitHub, but I want to make sure the installation process is clear to users.

I've just been copying them to the correct directory, but is there a best practice I should follow?  Like providing a makefile and documenting that as the correct way of installing -- with an aside to point out that actually for this extension at present it's acceptable to just copy the SQL and control files

The extension in it's early state is visible here

Thanks

Edited to include link to GitHub extension


texts:
I wrote a statement similar to this one. I am new to SQL and wondering how I can or if I can use SUM(___)over(partition by ___)  to sum all the NonUsableTotal parts inventory for that PartID.

The issue is that f.Netflag is a numerical value either 1 or 0 to define it as usable or nonusable.

Here is the statement:

select distinct
       l.whse,
       l.partid,
       i.descr,
       l.qty,
       case when f.Netflag=0 then 'NONUsable' else 'Usable' end as UsableStatus,
       SUM(l.qty*f.Netflag) over (partition by l.partid) as TotalUsable
from   location l (nolock)
join   inventory i (nolock)
on     l.partid=i.partid
join   loctable f (nolock)
on     l.whse=f.whse

Here is what I want the results to look like:

+-------+--------+-----+------------+-----------+--------------+
|Partid |  Whse  | Qty |UsableStatus|TotalUsable|TotalNonUsable|
+-------+--------+-----+------------+-----------+--------------+
 134       INSP     10    Usable         30            20
 134       RRR      15    NONUsable      30            20
 134       VVV      20    Usable         30            20
 134       QRR      5     NONUsable      30            20

I appreciate your help thank you.


texts: Why does SQL Injection not happen on this query inside a stored procedure?

texts:
I made the following stored procedure:

ALTER PROCEDURE usp_actorBirthdays (@nameString nvarchar(100), @actorgender nvarchar(100))
AS
SELECT ActorDOB, ActorName FROM tblActor
WHERE ActorName LIKE '%' + @nameString + '%'
AND ActorGender = @actorgender

Now, I tried doing something like this. Maybe I am doing this wrong, but I want to be sure that such a procedure can prevent any SQL Injection:

EXEC usp_actorBirthdays 'Tom', 'Male; DROP TABLE tblActor'

The image below shows the SQL above being executed in SSMS and results being displayed correctly instead of an error:

Btw, I added that part following the semicolon after the query was done executing. Then I executed it again, but when I checked to see if the table tblActor exists or not, it was still there. Am I doing something wrong? Or is this really injection-proof? I guess what I am trying to ask here also is that is a stored procedure like this safe? Thank you.


texts: SQL Server - Copying a key from one table to another

texts:
In a trigger, I have created a table using SELECT * INTO. I have done that in a specific way, in order to create the table without also creating the identity column that exists in it (let's name this column ID):

SELECT * INTO #ins
FROM (SELECT * FROM inserted
      UNION ALL
      SELECT * FROM inserted WHERE 1 = 0) AS a

The problem is that the new table (#ins) does contain the identity column (which I want it to) but it has not marked is as Primary Key (which is how it was in the original table, and I would like to keep it that way).

I would like to know the easier of the following two:

How could I "read" the primary key from the original table and alter the new one to also add it there? I would like this to be generic, so assume the name of the ID column will be unknown.

or (if easier)

Is there another way to create table #ins from the original, retaining the column "ID" and its definition as primary key, but without it being an IDENTITY one?

EDIT: OK, as stated in a comment below, the task i was trying to achieve can be done in another way, using the primary key from the original table. This makes the question above rather pointless.


texts: mysqld got signal 11 - it's my fault but how do I fix it?

texts:
I was copying the database tables from our production server to my dev machine, and in the middle of the transfer I found out users were being locked out of the system because the select had locked a table. So...in my hurry to get the process to stop, I stupidly killed the mysqld process on my dev server.  Now, when I try to restart the service, I get the error below in my mysqld.log. I'm assuming it's because the table file for the table I was writing to is corrupted, but how do I go about getting the service to start again?  I did try moving the table files out of the database directory, but that didn't work (and yes, I put them back after!

141223 15:50:29 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
141223 15:50:29 [Warning] '--default-character-set' is deprecated and will be removed in a future release. Please use '--character-set-server' instead.
141223 15:50:29  InnoDB: Initializing buffer pool, size = 8.0M
141223 15:50:29  InnoDB: Completed initialization of buffer pool
141223 15:50:29  InnoDB: Started; log sequence number 1 1553165392
20:50:29 UTC - mysqld got signal 11141223 15:50:29 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
141223 15:54:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
141223 15:54:03 [Warning] '--default-character-set' is deprecated and will be removed in a future release. Please use '--character-set-server' instead.
141223 15:54:03  InnoDB: Initializing buffer pool, size = 8.0M
141223 15:54:03  InnoDB: Completed initialization of buffer pool
141223 15:54:03  InnoDB: Started; log sequence number 1 1553165392
20:54:03 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=8384512
read_buffer_size=131072
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337742 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x30000
/usr/libexec/mysqld(my_print_stacktrace+0x2e) [0x84c6d3e]
/usr/libexec/mysqld(handle_fatal_signal+0x484) [0x82ec8b4]
[0x9bd400]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
141223 15:54:03 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended


texts: Chance of duplicate on single select max into statement

texts:
Against my will, I am told to use the following method to generate a new pk value.

INSERT
    INTO
        SERVER_MONITERING(SM_SEQ, CPU_USE, MEMORY_BASE, MEMORY_USE, DISK_BASE, DISK_USE, SERVER_IP, SERVER_NAME, OBSTACLE_YN, CHECK_TIME)
    VALUES((SELECT MAX(SM_SEQ)+1 FROM SERVER_MONITERING), #cpuStat.combinedUsed#, #memStat.total#, #memStat.used#, #diskStat.size#, #diskStat.used#, #serverIP#, 'KW', 'N', TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI'))

I told him it still looks like there could be duplicate SM_SEQ values.

However, I don't know for sure whether this approach will eliminate duplication or not.

Will the approach above eliminate the chance of duplicate values ?
Is this a good practice ?
Will a sequence.nextval eliminate the chance of duplicate values ?
What is the best practice ?

Edit

Sorry, I forgot to mention.
There are multiple devs and their own code that inserts into this table and they all use select max()+1. I am a "guest" dev at this company. So I have little influence on their decision. If I switch to using a sequence only by myself, other devs will still select max()+1 and there will be collision between the two approaches.
Probably, I would have to tell all of them why even nesting a select max()+1 won't solve their problem, if it truly would not.


texts: I need to put the data from the diagram in 3NF (third normal form)


texts:
I have some master-slave replication with some schemas not being replicated, via replicate-ignore-db in the my.cnf. Doing some research about how that parameter works, found this:

  Statement-based replication.  Tells the slave SQL thread not to
  replicate any statement where the default database (that is, the one
  selected by USE) is db_name.

  Row-based replication.  Tells the slave SQL thread not to update any
  tables in the database db_name. The default database has no effect.

We're currently working with SBR and can't change it to RBR, because RBR reults in too much delay with some queries that UPDATE many rows.

Other thing to consider is that we have like 50 persons who access and modify the database using programs like SQLYog, DBeaver, etc... and work with various schemas in one day; so there's the risk that at one time they USE one schema and later in the day they modify other schema without changing the current database, so it wouldn't replicate.

Here's an example, hoping it helps me explain:

Let's imagine I have two schemas:

schema1 <- Don't replicate

schema2 <- Replicate

And one of the persons do the following

mysql> USE schema1;
mysql> CREATE TABLE dummy(id INT(1));
mysql> .... more queries in that schema...
mysql> CREATE TABLE schema2.table(id INT(1));

Then ends the session, later on he starts a new session and does:

mysql> INSERT INTO schema2.table VALUES (1);

This will break the replication, since schema2.table doesn't exists in the slave because the CREATE was ignored. Is there a safer way to ignore databases? One idea was to "deny" the USE schema to their users, this would ensure that everything is replicated correctly (could cause to some instructions for the schema1 to be replicated, but it's better than not replicating something important...)


texts: Window aggregate resulting in slow query on postgres 9.6

texts:
I'm using Postgresql 9.6 and I'm running into a performance issue. I have a relatively simple query:

explain analyze
 SELECT * FROM (
     SELECT A.person_id, O.session_id,  timestamp_ AS step_ts_min ,rank() over (partition by A.person_id order by timestamp_) as rn
     FROM event_page O
     JOIN alias A ON (O.person_alias = A.alias)
     WHERE O.timestamp_ between '2017-02-18 00:00:00.000' AND  '2017-03-13 00:00:00.000'
     and O.location_host like '2016.myhost.ca'
) as innerstep WHERE rn=1

The event_page table is actually a view over monthly tables union-ed together, you can see that in the plan.

This query takes over 2.5 minutes to run and you can see the lions share of that time is used by quicksort, along with 1.8G of ram!!!

Here is the execution plan:  https://explain.depesz.com/s/v51h

Is there a way to improve performance and optimize the query?


texts: MySQL import csv file ERROR 13 (HY000): Can't get stat of /path/file.csv (Errcode: 2)

texts:
I am an absolute beginner to MySQL (5.5.34) / Linux (Ubuntu 12.04 LTS)`

I have created a simple database with one table. When trying to import data into it via a 'data_test.csv file an error message 13 appears.

Exert from Terminal follows:

mysql> source /home/g/stockrecdb/load_test.sql;
Database changed
ERROR 13 (HY000): Can't get stat of '/home/stockrecdb/data_test.csv' (Errcode: 2)
mysql>

Note: if using LOCAL in LOAD DATA LOCAL INFILE an error appears:
ERROR 1148 (42000): The used command is not allowed with this MySQL version


texts: Find fulltext indexes in SQL Server 2008

texts:
How do I find a list of tables that have a FULLTEXT INDEX applied to them on a given database?

For example, you can enable and disable a FULLTEXT INDEX like this:

ALTER FULLTEXT INDEX ON [dbo].[OBJECT_FACT] ENABLE

ALTER FULLTEXT INDEX ON [dbo].[OBJECT_FACT] DISABLE


texts:
I have created a script that runs every night to rebuild & re-organize indexes based on the fragmentation, Indexes with Fragmentation > 30% are rebuilt, Indexes with Fragmentation 10% - 30% are re-organized.

After running the script I noticed we have about 400 indexes that are still reflecting a fragmentation count of > 10%, and after more investigation i came across some posts which mentioned any indexes with a page count < 1000 would not be re-organized by SQL.

I investigated further why my script was not updating all my indexes and found out that the results from the query

sys.dm_db_index_physical_stats(DB_ID(DB_NAME()),NULL,NULL,NULL, 'DETAILED')

show page counts of 5, 6 for tables that have > 1 mill records in, the record_count column had about 16 000 records, I then forced an index rebuild on the index by running

ALTER INDEX [indexname] ON [schema].[table] REBUILD WITH (FILLFACTOR = 85, STATISTICS_NORECOMPUTE = OFF)

and my index now shows 1 mill + record count.

My question is: why would a re-organize of he index not correct the record count in the index and the fragmentation & what would have cause this record count value to become so out dated on the index in the first place?

My plan now is to force a rebuild of all indexes tonight & then run my script as per normal going forward. Should I worry about the index record count becoming out dated again?


texts: MySQL: does Index Merge Sort-Union access algorithm use temporary table?

texts:
My question is about index_merge optimization in MySQL.

I have a query with a set of range conditions connected with OR. EXPLAIN of the query shows that index_merge method is used and the Extra column contains only:

'Using sort_union({list of merged indexes}); Using where'

The question is: "Does this query use a temporary table?"

In the 8.2.1.4.3 The Index Merge Sort-Union Access Algorithm section of the MySQL manual it is written that:

  The difference between the sort-union algorithm and the union algorithm is that the sort-union algorithm must first fetch row IDs for all rows and sort them before returning any rows.

That looks like the RDBMS is going to use a temporary table. But in the section 8.4.4 How MySQL Uses Internal Temporary Tables we can read:

  To determine whether a query requires a temporary table, use EXPLAIN and check the Extra column to see whether it says Using temporary (see Section 8.8.1, “Optimizing Queries with EXPLAIN”). EXPLAIN will not necessarily say Using temporary for derived or materialized temporary tables.

But there is no 'Using temporary' in the Extra column in this case. There are no subqueries or UNIONs in the query. The query looks like:

SELECT *
FROM A
JOIN B USING(pk)
WHERE (B.c1 IN ({list_of_values}) OR B.c2 IN ({list_of_values}) OR ... OR B.c9 IN ({list_of_values})) AND {some_other_conditions};

There are 9 single-column INDEXes on B.c1, B.c2,... , B.c9 columns.


texts:
I have three nodes(as recommended, in different switches) forming a Galera Cluster. Is there any real requirement of 'Garbd'? I don't see much reasons for using this apart from avoiding split-brain or taking backups?

Another doubt regarding garbd is that suppose I have 3 nodes in the cluster, and adding a garbd node will make the cluster size of 4, which is an even-numbered quorum. Will this not cause a split-brain situation?


texts: MySQL Workbench run SQL script file Character Set encoding issue

texts:
Upon running a .sql file for creating a schema in MySQL Workbench 6.1, it prompts a window saying it cannot decide the right character set

What's the proper thing to do?


texts:
MySQL 5.7 innoDB

My plan: Create an after insert trigger on one table, that copies some of the inserted data to another table. Create an before insert trigger on second table that includes some evaluation (collect data from another table) before doing the insert in second table or depend on the evaluation doesn't do the insert on the second table at all.

Will the planned triggers cause any locks on the tables/records they are created? Especially is there a possibility that the before insert trigger on second table will cause a lock or insert delay on the first table?

Some background: I have a heavy loaded table with actually 120GB data (growing) in it. About 15-40 inserts/sec.

In some occasions client connections raise up explosive and cause the applications to stall.

Not 100% sure but what I see is: Some queries in some occasions on that huge table does locks and delay the inserts. That causes the client connections to raise up.

The plan above, will put some work that actually clients do into the database itself. Is there a chance that my plan does eliminate that problem.

For doing a test I need to copy that huge table on a sandbox environment and simulate the inserts. Uff. So I ask before, is there is a chance that my plan works before occupy the net with all that data load.


texts: What happens to a query that is performed on a view?

texts:
What optimizations and/or changes occur to a query that is performed on a view? (It would be great to also know if each of the major DBs - Oracle, SQL Server, Postgres, MySQL/MariaDB - handle this differently).

For example, let's say that I have the following view:

CREATE VIEW my_view AS
  SELECT
      id,
      val0,
      val1,
      val2
    FROM my_table
    WHERE val0 = 42;

...and I want to perform the following query on that view:

SELECT
    id,
    val1
  FROM my_view
  WHERE val2 = 'Fish fingers and custard';

...does the database optimize the query or are two selects essentially performed? I.E. is it essentially the same as performing potential query 1 or 2 below or neither?

Potential Query 1:

SELECT
    id,
    val1
  FROM (
    SELECT
        id,
        val0,
        val1,
        val2
      FROM my_table
      WHERE val0 = 42
  )
  WHERE val2 = 'Fish fingers and custard';

Potential Query 2:

SELECT
    id,
    val1
  FROM my_table
  WHERE val0 = 42
    AND val2 = 'Fish fingers and custard';


texts: PostgreSQL WAL recovery not working due to missing WAL

texts:
I am using pgBarman for PITR recovery. Now it's a bit of a crisis, because the Barman backup and the WAL archive seems like it cannot be recovered. We're running PostgreSQL 9.3.6 and latest stable Barman.

I ran the following command:

$ barman recover --target-time "2015-09-03 10:00:00.000" ke 20150501T200001 /tmp/ke-recovery

Leaving us with /tmp/ke-recovery, a folder we can start once we change the path names in postgresql.conf.

It then starts with the recovery of the WAL files from barman_xlog/ but then fails in about half an hour due to one of the WAL files are missing.

We assume this is because of an rsync "gone bad" from the master to the Barman box, and it was not noticed.  It means we have almost all WAL files, but at least one is missing.

How can we "force" PostgreSQL to continue with the recovery (even at the potential loss of some data)?  Perhaps by using pg_resetxlog ?


texts: Backup Failure on SQL TDE Database with Maxtransfersize Specified for Compression

texts:
Recently, my company has upgraded to 2016 and enabled TDE on all of our databases. Additionally, we have the production databases replicated on high availability servers.

Our backups are taken from the HA server and use the maxtransfersize hack in order to compress the backups.

All of the backups are successful, save one, where the following error is spit out by our monitoring software:

  Error: 3624, Severity: 20, State: 1.
  A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.

In the server log there are a few more details at the point of failure:

  input buffer 70 bytes
  ProcessID: 1884
  SPID:200
  Expression: m_inputQueue.head()==null
  location: backuplog.cpp 1564

This is my backup command:

backup database @DBName to disk = @Path
  with noformat, noinit, name=@Name, skip, norewind, nounload,
  compression, stats=10, copy_only, MAXTRANSFERSIZE = 65537

Has anyone run into a similar problem before?

I've used a few different values for maxtransfersize, albeit in a kinda random manner, picking from a chart in TDE and Backup Compression: Together At Last by Erik Darling.

Not using TDE is not an option because it contains sensitive data. Not being compressed may be an option if we have the drive space.


texts: Postgres 9.5 Materialized View from a table with jsonb columns

texts:
For the purpose of improving performance of queries on a rather large table (5M+ rows) containing jsonb columns, I would like to create materialized views with data derived from those columns. Following is a simplified version of the problem:

               Definition

CREATE TABLE my_table (id int8, content jsonb);

               Contents

id |  content
-----------------------------------------------------------------------------
 1 | { "customer": "John Doe", "items": {"product": "Whiskey","qty": 3}}
 2 | { "customer": "John Doe", "items": {"product": "Whiskey","qty": 4}}
 3 | { "customer": "Danny Boy", "items": {"product": "Beer","qty": 2}}

Now I'd like to create two materialized views, one will contain all customer names and the other will contain the count of each product.

So the end result should be:

               Materialized View 1 / the names of the customers
customer_name
-------------
John Doe
Danny Boy

               Materialized View 2 / the quantity of all products

product | quantity
------------------
Beer    | 2
Whiskey | 7

For the first materialized view I tried the following:

CREATE MATERIALIZED VIEW
  customers AS
SELECT DISTINCT
  content->'customer' AS customer_name FROM my_table;

This works fine, but I am having trouble with the second materialized view.

How should I go about aggregating quantities for each product in an efficient way?


texts: In Powershell Retrieving Printed Message From SSMS

texts:
Our DBA team doesn't verify the backups using the below TSQL (which can easily be done post-backup and takes almost no time, so I don't get why not):

RESTORE VERIFYONLY
FROM DISK = 'D:\Backups\LOCATION'

They've had issues in the past, so even though we'd think they'd learn from it, they haven't.  I created a Powershell script to do this because we have about 100+ servers and I just want to run this script against all the backups just to ensure they're valid.  The below script runs correctly (in that it doesn't break or throw errors), I am curious if there's a way to get the printed message back in Powershell that we would normally get in SSMS where it states The backup set on file 1 is valid as verification.

$SqlCon = New-Object System.Data.SqlClient.SqlConnection
$SqlCon.ConnectionString = "SERVER=SERV\INST;Integrated Security=true;DATABASE=master"

$baks = Get-ChildItem "D:\Backups\" -Filter *.BAK

foreach ($bak in $baks)
{
    $SqlCon.Open()
    $cd = New-Object System.Data.SqlClient.SqlCommand
    $cd.Connection = $SqlCon

    $cd.CommandText = "RESTORE VERIFYONLY FROM DISK = @f"
    $cd.Parameters.Add("@f", $bak.FullName)
    $cd.ExecuteNonQuery()
    $SqlCon.Close()
}


texts: Instance and database (Oracle vs SQL Server)

texts:
I have been using Oracle up to now, where:

instance -> process + memory
database -> physical datafiles

Normally one instance maps to one database. Inside a database, there can
be many tablespaces (system , users etc).

However, I am not so sure about SQL server. It seems to me that for SQL Server, one instance = multiple databases? Or one instance = multiple schemas?

In Oracle, one database contains the system, sysaux, temp tablespaces and the user tablespace. In short, both system and user are in the same database.

In SQL Server, it seems like there is one system database and that can serve multiple user databases?


texts: Starting points for creating a mysql database proxy

texts:
I'm not actually entirely sure if this is possible, but I'd like to build piece of software that behaves a lot like a proxy between a client and a mysql server.
Essentially, I want this software to expose a connection string the same way a normal mysql database would, and listen to the same port.  When it receives traffic (mysql queries), it would apply some modifications to them before handing them off to the actual mysql database.
Is this possible? If so, where would I even start in building this? Finally, are there any recommendations on how to do this properly?
I'm also open to exploring off-the-shelf options if something like this already exists.


texts: pg_stat_monitor: see placeholders instead of actual query

texts:
I have started using pg_stat_monitor extension  from :  https://www.percona.com/doc/percona-monitoring-and-management/2.x/manage/conf-postgres.html  with my postgresql database.I  have a problem here. I though of using this so that I can see my query with actual parameters instead of  placeholders. However, still I see my query with placeholder. Even though , I have set pg_stat_monitor.pgsm_normalized_query =0 and by default it's 0 only. Which means it has to show me query with actual parameters.
You can refer attached screenshot:


texts: SSIS error in lookup for lookup yielding no rows

texts:
I am trying to have lookup component to find some column is equivalent to max number of date from TR table. My query is below

select CAST(max(prDate)as datetime) as date from TrTable

In SSMS it returns value as
    2018-05-17 00:00:00.000 while when I try same query in lookup connection it return result as 5/17/2018 12:00:00 AM.

Any idea how should I rectify lookup query to get exact same result as 2018-05-17 00:00:00.000 so that I would have a match and lookup will yield match output.

Lookup tasks produces following error -

[Lookup [114]] Error: Row yielded no match during lookup.

[Lookup [114]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Lookup" failed because error code 0xC020901E occurred, and the error row disposition on "Lookup.Outputs[Lookup Match Output]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Lookup" (114) failed with error code 0xC0209029 while processing input "Lookup Input" (128). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.


texts: Combining data from multiple sources in one column

texts:
Let's say I have a tables like

    create table dbo.A
    (
        Id int identity(1,1) not null,
        BId int null,
        CId int null,
        SomeData nvarchar(50),
        constraint [PK_A] primary key clustered ([Id] asc),
        constraint [FK_A_B] foreign key (BId) references [dbo].[B]([Id]),
        constraint [FK_A_C] foreign key (CId) references [dbo].[C]([Id]),
    )
    go

    create table dbo.B
    (
        Id int identity(1,1) not null,
        constraint [PK_B] primary key clustered ([Id] asc)
    )
    go

    create table dbo.C
    (
        Id int identity(1,1) not null,
        BId int null,
        constraint [PK_C] primary key clustered ([Id] asc),
        constraint [FK_C_B] foreign key (BId) references [dbo].[B]([Id]),
    )
    go

I would like to get back data that would have following in row:
A.Id, C.Id, A.SomeData where C.Id could be coming either from A.CId directly or as C.Id if you join A to by C on A.BId = C.BId. So let's say in my tables I have following data:

    B
    1
    2

    C
    1 1
    2 1
    3 2
    4 2

    A
    1 1 3 'test1'

Sample SQL to insert test data:

    SET IDENTITY_INSERT ON
    go

    insert B(Id)
        select 1
                union all
        select 2;

    insert C(Id, BId)
        select 1, 1
            union all
        select 2, 1
            union all
        select 3, 2
            union all
        select 4, 2;

    insert A(Id, BId, CId, SomeData)
        values(1, 1, 3, 'test1');
    go

    SET IDENTITY_INSERT OFF
    go

What I would like to get back is:

    1 1 'test1'
    1 2 'test1'
    1 3 'test1'


texts: How SQL server database reorganize memory

texts:
I am using [sys].[master_files] table to determine the size of the database, however i didn't get a sensible value yet. If i am inserting 100 rows into a table ideally db size should increase while i am getting the same size even after inserting 1000 rows , sometimes it may decrease unexpectedly. Then i drop all the tables from the database and after that it shows 188 MB from 180 , size increased after dropping all tables. This is also not good.

When i am using DBCC SHINKDATABASE ('dbName') , i am getting a good value. It reduced to 22 MB. My question is how i can determine the size of a particular DB , which is showing sensible changes. Any help will be appreciated , thanks.


texts: Can't create trigger in MySQL to make a simple audit

texts:
I have a table sales:

Create table sales ( sales_id BIGINT PRIMARY KEY,
                     customer_id BIGINT,
                     sales_amount DOUBLE ) ENGINE=INNODB;

Then to audit this:

Create table audit_log ( log_id BIGINT PRIMARY KEY AUTO_INCREMENT,
                         sales_id BIGINT,
                         previous_amount DOUBLE,
                         new_amount DOUBLE ) ENGINE=INNODB;

And I've tried for many, many ways of make this trigger:

DELIMITER //
CREATE TRIGGER log_sales_updates
AFTER UPDATE
ON sales
FOR EACH ROW
Insert into audit_log(sales_id, previous_amount, new_amount) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount)//
DELIMITER ;

The output I've for this is: (nothing) I other words the prompt disapeear, I change for this:

I tried with semicolon after insert but the result is the same the prompt disapeear, I have to quit with Ctrl+C

I'm using MySQL 8.0.19 for Linux on x86_64 (MySQL Community Server - GPL)


texts: How to Add or subtract rows of one columns this example in sql 2014?


texts: Infrastructure for Highly Concurrent, High Write DB

texts:
My requirements are:

3000 Connections
70-85% Write vs Read

Currently, we are maxing out a High-CPU, Extra Large Instance at 700 connections. All 8 cores are maxed. We think it's the number of concurrent connections as the memory is fine. The write itself is very simple (validations slow things). To scale to 3000, we need to go to multiple servers, current options:

MySQL Sharding
MongoDB Cluster
Cassandra
Hadoop & MySQL (Hadoop caches, single dump to MySQL)
MongoDB & MySQL (instead of Hadoop, we use mongo for cache)

To handle this number of connections, a number of questions:

Can MySQL Sharding handle the concurrent connections?
Can any single master handle these concurrent connections, or is a multi-head like Mongo a better option?

I apologize if I'm not describing my problem well. Please ask questions.


texts: How implement MySQL unique index with two foreign key that one can be null?

texts:
I would know if can be declared in MySQl to create an index constraints in a table with two foreign key that one can be null.
For example cannot be repeated a null foreign key with the other foreign key setted:

first foreign key: 1
second foreign key: 1

first foreign key: 1
second foreign key: null

so this two foreign keys can be saved correctly in the table but if I try to save again

first foreign key: 1
second foreign key: null

I should receive an error from the database.
I'm not able to express this control in SQLYog.

Someone have experience on this type of problem?

Thanks in advance.


texts: High number of undistributed commands after a "An existing connection was forcibly closed by the remote host" event

texts:
For the lasts days I'm getting a high undistributed commands value in the replication monitor. Restarting the Server Agent solve the problem (momentarily) but after few hours starts again. Even when there are a lot of undistributed commands, I inject a token and get low latency (00:00:09 the most high). sp_replmonitorsubscriptionpendingcmds return the same values from the Replication Monitor.

The 'Distributor To Subscriber History' shows the message "TCP Provider: An existing connection was forcibly closed by the remote host". I'm looking for network issues and I didn't find nothing, just in the Application logs are the events 14152.

There are 6 replication jobs and they fail in similar time, but not in the same moment (all are replicating to the same subscriber), I means, in a period of few minutes between them. If was a network issue, they must broke at same moment, right?

After restart the agent or the server, it's working for several hours. I didn't find any pattern in the time, sometimes broke in the morning, others in the afternoon, etc.

How can I continue debugging the problem?

note: SQL server 2008 R2


texts: Double Transactional Replication (e.g. Two report servers)

texts: Is it a good plan to use SQL Server Transactional Replication from server one to server two then another from server two to server three?  Most of us DBAs here are advise against it.  The boss wants it.  He does not want to have 2 subscriptions from the production server.  Are two subscriptions from the production server a big hit?


texts:
I am reading SQL Server Execution plans by Grant Fritchey, He mentions:

  SQL Server does not keep execution plans in memory forever. They are
  slowly aged out of the system using an "age" formula that multiplies
  the estimated cost of the plan by the number of times it has been
  used. The lazywriter process, an internal process that works to free
  all types of cache (including the plan cache), periodically scans the
  objects in the cache and decreases this value by one each time.

  If the following criteria are met, the plan is removed from memory:

  more memory is required by the system
  the "age" of the plan has reached zero
  the plan isn't currently being referenced by an existing connection.

He also mentions earlier in the book the following:

  Once the optimizer arrives at an execution plan, the estimated plan is
  created and stored in a memory space known as the plan cache –
  although this is all different if a plan already exists in cache.

I would assume that the plan could theoretically reach zero if the actual and estimated plan differ. This would give the estimated plan execution count zero even though it was stored in the cache.

My question is What are the different scenarios that a plan age could reach zero? And am I correct in my assumption?

Fritchey, G. (2012). SQL Server Execution Plans. Springfield, USA: Simple Talk Publishing.


texts: Call dynamically a function using plpgsql

texts:
I create a function that returns the value of a field when the id is given :

CREATE OR REPLACE FUNCTION get_dcteam(id int) RETURNS text
AS $$
#print_strict_params on
DECLARE
dc_team text;
BEGIN
    SELECT monitoring_table.dc_team INTO STRICT dc_team
        FROM monitoring_table WHERE monitoring_table.id = get_dcteam.id;
    RETURN dc_team;
END
$$ LANGUAGE plpgsql;

I call it this way :

select * from get_dcteam((select id from monitoring_table where id=30))

What I want to do is call the function  dynamically. I don't want to precise a known id(given value in parameter) but any existing value in my table. The result of this query I will use it in a trigger.
I'm a newbie and I'm struggling to learn dynamic SQL.
Your help would be precious.
Best regards.


texts:
I have several questions I cannot find definite answers for:

1) A quick googling about DMOs returns quite a few articles that state that those DMOs reside in master database.

I understand that they are actually defined in mssqlsystenresource.mdf resource database.

But how does this reconcile with the fact that DMVs (not DMFs) appear in every single database?  What is the underlying difference, if any,  when DMVs are used under different database context?

My understanding is if the view / stored procedure returns system-wide information (server-scoped), it shouldn't matter where they are executed, as long as the users have the appropriate privileges. And if they turn out to be database-scoped, it is even logical that each database should contain its own copies of definitions for DMVs.

2) About DMFs

2.a)   sys.dm_ and sys.fn_ functions, which are also defined in resource database, only appear in system databases. they cannot be found inside any user database. But they are perfectly usable within user databases.

4.b) Regular system functions, such as Aggregate and String functions, also defined in the resource database, appear in any user database, but not in any of the system database, not even in model, which is supposed to be a model. Yet again, the functions I tested worked fine in those system databases.

Can anyone help me with how all these are organized?

Thanks for any help.


texts: How can I create a filegroup on SQL Server attaching a bare filename?

texts:
I'm trying to script a database creation from a Java program. I need to create 2 file groups, and link them to the database. In the example I can see in the official docs, I only see examples with paths, like in this case:

  B. Adding a filegroup with two files to a database

  The following example creates the filegroup Test1FG1 in the
  AdventureWorks2012 database and adds two 5-MB files to the filegroup.

USE master
GO
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2012
ADD FILE
(
    NAME = test1dat3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1;
GO

However, I have no knowledge of what any user's setup would look like (and it could potentially be Windows OR Linux, so an answer I found where the person re), but I would like to add these file group files to the DATA/ folder of the installation, or some other standard/default location. To achieve that, I tried to execute the following command:

ALTER DATABASE myapp_db ADD FILEGROUP data_dg

ALTER DATABASE myapp_db ADD FILE (
    NAME=data_fg,
    FILENAME='data_fg.ndf',
    SIZE=32MB,
    MAXSIZE=4096MB,
    FILEGROWTH=32MB
) TO FILEGROUP data_fg

However, I get the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: A file activation error occurred.
The physical file name 'data_fg.ndf' may be incorrect.
Diagnose and correct additional errors, and retry the operation.

Is this because SQL Server does not allow relative file paths/names for FILEGROUPs? (if so, why is it called FILENAME instead of FILEPATH?) What options exist for achieving this?


texts: delete or rollback an insert if a trigger on insert fails

texts:
is it possible to delete or roll back insert statement if a trigger on that insert statement fails due to some reason.
for eg i have a trigger on insert into employe(id,name) values(1,'ali')
trigger
CREATE TRIGGER [dbo].[Customer_INSERT]
       ON [dbo].[employe]
AFTER INSERT
AS
BEGIN
       SET NOCOUNT ON;

       DECLARE @id INT

       SELECT @id = INSERTED.id
       FROM INSERTED

       INSERT INTO CustomerLogs
       VALUES(@id, 'Inserted')
END

now is there any way i can wrap all this up in one transaction so in case my trigger fails then the first inserted row is also removed?i know i can use try catch and delete in catch block
but i want to get it done with transaction is it possible?


texts:
I'm currently enrolled in a DBS class and am having problem with an assignment. I've searched around and have been unable to understand what it is I'm meant to be doing with this derivation formula.

  A plant file with TREE-GENUS as the key field includes records with
  the  following TREE-GENUS values: Tsuga,  Ficus , Arbutus, Quercus,
  Melaleuca,  Tristaniopsis, Cornus, Sequoiadendron, Lithocarpus,
  Liriodendron, Pittosporum.
  Suppose that records with these search
  field values are inserted into a random (heap) file with a maximum of
  3 records per block.  Derive a formula for the expected number of
  disk I/O to scan these records and to search for a particular record

I've been using some software that was given with the assignment and it also asks what are the maximum number of blocks that are allowed and that is not given by the above brief. I'm not really sure how to derive a formula for this. I've assumed that because there are 3 records per block there are 4 blocks required and that a random heap file uses 1 disk i/o per write/read.

If this is a larger topic than is worth explaining a link to a reliable few pages is also helpful.


texts:
I have the following table with values for different stations from 2014-01-01 to 2014-01-04. The data has some date gaps that I want to fill leaving the value as NULL, and assigning the missing date to each station. I'm working with PostgreSQL 10.9

This is my table:

CREATE TABLE stations (station_id text, value integer, date date);
INSERT INTO stations (station_id, value, date) VALUES
('001', 10, '2014-01-01'),
('001', 30, '2014-01-03'),
('002', 40, '2014-01-01'),
('002', 50, '2015-01-02'),
('003', 20, '2014-01-01'),
('003', 10, '2015-01-02'),
('003', 70, '2015-01-04');

I also have a table holding unique stations with identifiers.

And I want something like this:

| station | value | date       |
|---------|-------|------------|
| 001     | 10    | 2014-01-01 |
| 001     | NULL  | 2014-01-02 |
| 001     | 30    | 2014-01-03 |
| 001     | NULL  | 2014-01-04 |
| 002     | 40    | 2014-01-01 |
| 002     | 50    | 2014-01-02 |
| 002     | NULL  | 2014-01-03 |
| 002     | NULL  | 2014-01-04 |
| 003     | 20    | 2014-01-01 |
| 003     | 10    | 2014-01-02 |
| 003     | NULL  | 2014-01-03 |
| 003     | 70    | 2014-01-04 |

Following some DBA Exchange (questions)1, I tried a combination of a LEFT JOIN with a LATERAL JOIN:

WITH complete_dates_station AS (
    select station_id,
           generate_series(DATE '2014-01-01', DATE '2014-12-31', INTERVAL '1 day')::DATE as dt
    FROM stations
    GROUP by station_id
    ), temp_join AS (
        SELECT station_id,
               dt,
               s.value
        FROM complete_dates_station
            LEFT JOIN LATERAL (
                SELECT s.value
                FROM stations s
                WHERE s.station_id = complete_dates_station.station_id
                AND s.date = complete_dates_station.dt
                ORDER by s.station_id, date desc
                LIMIT 1) as s on TRUE
             ORDER BY station_id, dt
         ) SELECT * from temp_join

This works like a charm, but this join is really slow for my complete table, which has more than 2M rows and the date range goes over 18 years (I stopped after 4 hrs of running). I tried a simpler approach by using a regular LEFT JOIN, but the table outputs the not-joined groups as missings:

WITH complete_dates_station AS (
    SELECT station_id,
           generate_series(date '2014-01-01', date '2014-12-31', interval '1 day')::date as dt
    from stations
    GROUP BY station_id)
SELECT s.station_id,
       c.dt,
       s.value
FROM complete_dates_station c
    left outer join stations s
    on c.station_id = s.station_id and
    c.dt = s.date;

which yields the following:

| station | value | date       |
|---------|-------|------------|
| 001     | 10    | 2014-01-01 |
| NULL    | NULL  | 2014-01-02 |
| 001     | 30    | 2014-01-03 |
| NULL    | NULL  | 2014-01-04 |
| 002     | 40    | 2014-01-01 |
| 002     | 50    | 2014-01-02 |
| NULL    | NULL  | 2014-01-03 |
| NULL    | NULL  | 2014-01-04 |
| 003     | 20    | 2014-01-01 |
| 003     | 10    | 2014-01-02 |
| NULL    | NULL  | 2014-01-03 |
| 003     | 70    | 2014-01-04 |

There is any way to optimize the first query, or use a simpler approach to fill my station gaps in the second query? I tried already using multicolumn indexes in my source table, but the query is still taking a lot of time.


texts: Why does information_schema have "YES" and "NO" character strings rather than booleans?

texts:
I was taken by surprise to find that the is_nullable column for this query is typed as character varying (3) rather than boolean:

select
    column_name,
    data_type,
    is_nullable
from
    information_schema.columns
where
    table_schema = 'public';

I noticed that some other column use "YES"/"NO" as well. What is the rationale for this? My initial thought was that perhaps other values besides "YES" and "NO" are possible, justifying something other than a boolean, but I didn't see any examples of this in my current database.


texts: An entity may or may not be an existing customer

texts:
I am designing a complaints module. The complaints can come from people who are not customers. Namely, people who did not become a customer but are complaining that the sales reps are too aggressive. Complaints can also come from existing customers.

Currently I have 4 tables involved:

Complaint

ComplaintReporter_Id

Customer

Contact_Id

Contact

Name
Phone

ComplaintReporter

Customer_Id (Nullable)
Contact_Id (Nullable)

I don't like the nullable columns on Complaint Reporter.
When the complaint comes from an existing customer, I leave the Contact_Id null because every Customer has a Contact_Id. When the complaint comes from a non-customer, I leave Customer_Id Null.

Is there a better design to support this situation that won't require nullable columns?

I suppose I could get rid of ComplaintReporter table and add its columns directly to complaint which will make the diagram look less funky, but I'm still left with nullable columns.


texts: App domain crashed due to not enough storage available to process this command

texts:
We are running SQL server SQL 2014 12.0.2000.8 32 bit running on Windows 2008 server 32 bit.

This server is also part of T-Replication as a publisher and also distributor.

The server has 16 GB of RAM and has allocated Max Memory 2147483647 MB (Default setting) to SQL server.

Since yesterday I am getting the below error whenever start the SSMS. To solve it, I restarted SQL Server Service, then I am able to login to SQL Server, but after a few hours it happens again.

Please provide me help with any idea where to look to trouble shoot the issue.


texts: Is it possible in PostgreSQL to temporary grant roles/privileges to a user? I'm thinking about this as a solution for the situation when there is a need to troubleshoot an issue and an user may need some specific role/privileges (even superuser) only for a short period of time.


texts: Command to add column to all tables in a schema

texts:
Could you please give the command to add a column to all the tables if the table doesn't contain that column in a schema?

e.g. The schema schema1 contains 50 tables in which 20 tables contain a column named colTest.

I need to add the column colTest to the remaining 30 tables.


texts:
I have a very large table, lets call it example, and I try to execute a little alter command on that table:

ALTER TABLE `example` ADD `example_column` TINYINT(1) NOT NULL DEFAULT 0;

Checking the progress of alter command:

mysql -e 'show engine innodb status \G' | grep 'undo \| log \| entries'

Give me nice info, about the time - more or less it will take 17 days to complete ...

Alter block the table, so blocking production table for 17 days is not a good option. ;)

I try to investigate some great tools on the net, like:

Pt-online-schema-change - Percona
gh-ost - GitHub's Online Schema Migrations for MySQL

I have also read the documentation, and the limitation section of the above tools:

gh-ost limitations

does not support triggers at all
does not support foreign keys at all

pt-online-schema-change limitations

The use of triggers means that the tool will not work if any triggers are already defined on the table.
Does not provide any good way of altering tables which contain foreign keys

My example table have triggers and foreign keys...

Could you give me some advice, how to deal with this alter?

I have MySQL 5.6.
I use GTID (row-based) replication.

I will be very grateful for advice!


texts: How to debug locked transaction on a remote system?

texts:
I have a remote database that is currently throwing errors. On a primary key of type BIGSERIAL it says no new rows can be created because a value for the primary key already exists (the value itself is in the mid tens of thousands).

My problem is that we have very little information, just the error. Is there a way to rollback that transaction from a remote location? Is there a way to dump the db contents, not just export the tables, to check what the db engine is doing?


texts: Can I emulate Oracle's autonomous transaction in MySQL?

texts:
I have a number of tables that have triggers on them that insert records in queueing and logging tables. The problem is that those inserts are then part of the same transaction. That means that any IUD on the former tables, can be locked by other IUD that trigger inserts into the same queue, and that IUD that fails will not be logged, since those inserts will be rolled back as well.

I don't care much for entries in the queue where the triggering IUD was rolled back; the queue is read by some process that invalidates / refreshes cache and similar actions. Should anything have been rolled back, some cache will be refreshed with the same data. No big deal.
The business case here is a website with a great number of sets that consist of single products. Any one product can be in up to 15,000 sets. If the price of a product changes, the prices of all those sets need to be recalculated. I don't want to do that on the same transaction when saving the price of a product, because that would take too much time and lock too much of the database, so I enqueue the recalculation.

For logging, there are some actions I want to log, even (especially) if they fail. I can't do that now.

Were this Oracle, which I used until recently, I would've used PRAGMA AUTONOMOUS_TRANSACTION to handle the insert in a separate transaction, that would always commit no matter what the other transaction did.

Can I emulate this behaviour by using MyISAM tables for the logs and the queues, with concurrent_insert set to ALWAYS, while the rest of my tables are InnoDB? (I expect multiple inserts into them, that's the whole issue, while they're seldom read (logs), or only once (queues)).


texts:
After spending almost all of my day searching a fix to this I come to you guys.

I need to do an accent insensitive word search on my db(InnoDB), but I cannot get it to work.

I have t1 table, the field description which is an indexed varchar(250), furthermore my table collation is utf8_general_ci.

t1 have a record in which the field description have stored bla bla... instrucción... bla. just in case you are wondering how are the records stored.

I'm trying to fetch the records using the words instrucción and instruccion using this:

SELECT id FROM t1 WHERE description LIKE '%instruccion%'

If I use instrucción I get a non-empty result, but using instruccion I get an empty result.

Please help and thanks.


texts: MSSQL database physical design: RAID or spreading filegroups across different physical drives?

texts:
I'm going to build a Microsoft SQL server database which could end up being more than 40 TB in size. There are some quite large tables in the DB with more about 1010 .. 11 records. Every table has typical primary key index. Also most of the tables have composite non-clustered indexes too (B-tree). In a few cases we have defined columnstore indexes.

Which of the below physical designs of the server will result in better performance1 and db management2 overtime?

Scenario 1. create one (or more) filegroup per physical drive (we only have SSD NVMe drives), and spread the heavily used tables and indexes across different drives.

Scenario 2. Bundle all drives using RAID 10 and create a single large volume. Data will be striped on all disks by the OS. In this scenario, data file (.mdf), log file (.ldf) and tempdb are all stored on a large RAID 10 volume.

Scenario 3. Partition tables/indexes and store each partition on different physical drive. I should mention that it wold be close to impossible to distribute data uniformly across all partitions because of the type of data we are dealing with.

If you think none of the above are ideal solutions; I'd love to know what would you recommend.

Many Thanks.

[1] by performance I mean lower retrieval time and higher IOPs, for a given indexing architecture

[2] by db management I mean add/remove/update data, keeping the performance, redundancy, availability ..


texts: How do I grant permissions for a SQL Server Management Studio user to access system.views in an Azure Managed Instance?

texts:
I am connecting to an Azure SQL managed instance with Active Directory through SQL Server Management Studio.

I'm trying to access system views, like:

SELECT * FROM sys.dm_db_missing_index_details
SELECT * FROM sys.dm_database_encryption_keys

I get the following error message in SQL Server Management Studio:

The module being executed is not trusted. Either the owner of the database of the module needs to be granted authenticate permission, or the module needs to be digitally signed.

SELECT CURRENT_USER says that I am logged in as "dbo".

These views work for an on-premises SQL server, so it must be an Azure Managed Instance issue.

How do I grant the SSMS user (dbo) the appropriate permissions to access system views?


texts:
An general doubt about how restore in done in SQL server

While performing restore, will the MDF and NDF will be restored first or the LDF files?


texts: Postgresql Streaming replication - monitoring

texts:
I have setup streaming replication for my postgres servers. I am using PostgreSQL 9.2.6. Replication seems to be working fine. I am monitoring this with two ways using nagios:

Log_delay and
Byte_lag

I am very frequently getting critical alert for log_delay and in the same time byte_lag is not throwing up any alert. log_delay will be OK after 1 or 2 minutes. Can any one please suggest me If I am missing something in my setup?

Queries are given below.

Log_delay

SELECT
    CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()
    THEN 0
    ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
    END AS log_delay;

Byte_lag

SELECT sent_offset - ( replay_offset - (sent_xlog - replay_xlog) * 255 * 16 ^ 6 )
       AS byte_lag
FROM (
    SELECT client_addr,
           ('x' || lpad(split_part(sent_location,   '/', 1), 8, '0'))::bit(32)::bigint
           AS sent_xlog,
           ('x' || lpad(split_part(replay_location, '/', 1), 8, '0'))::bit(32)::bigint
           AS replay_xlog,
           ('x' || lpad(split_part(sent_location,   '/', 2), 8, '0'))::bit(32)::bigint
           AS sent_offset,
           ('x' || lpad(split_part(replay_location, '/', 2), 8, '0'))::bit(32)::bigint
           AS replay_offset
FROM pg_stat_replication ) AS s;


texts: Gap resolution of Logical Standby Database via Archive Log Backups

texts:
Due to reasons outside the scope of this question, we had to flashback our Logical Standby Database (at the VM level) to the previous day.

So, now i'm trying to resolve gaps in missing archivelogs between the primary and the Logical Standby Databases.

Standby:

SQL> select * from gv$logstdby_process where status_code != 16116;
READER  ORA-16240: Waiting for log file (thread# 1, sequence# 54888)

Sequence 54888 no longer exists in the Logical Standby (due to the flashback), and since we take a nightly archive log backup, this sequence no longer exists as an archived log on the Primary. However, I verified that this sequence is in the archived log backup on the Primary.

Primary:

RMAN> list backup of archivelog sequence 54888;

  List of Archived Logs in backup set 6236
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  1    54888   42289020451 30-MAR-16 42290599951 30-MAR-16

Therefore, I copied the archive log backup to the Logical Standby and issued the following on the Logical Standby database:

RMAN> catalog start with 'L:\oracle\backups';
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> recover database;
RMAN> alter database open;
RMAN> list backup of archivelog scn between 42289020451 and 42289020451;

Thrd Seq     Low SCN    Low Time   Next SCN    Next Time
1    54888   42289020451 30-MAR-16 42290599951 30-MAR-16

RMAN> list backup of archivelog sequence 54888;

specification does not match any backup in the repository

SQL> alter database start logical standby apply immediate;
SQL> select * from gv$logstdby_process where status_code != 16116;

READER  ORA-16240: Waiting for log file (thread# 1, sequence# 54888)

So I have 2 questions:

1) Even though I can see the archivelog backup of sequence 54888 on the Logical Standby Database when performing a list backup of archivelog scn, why does the list backup of archivelog sequence 54888 return no records?

2) How can I get the log apply process to pick up sequence 54888 from the archivelog backup?


texts: Error ORA-00918 not raised as expected when using the FETCH command

texts:
The Oracle 12c documentation about FETCH is telling the below

  If the select list contains columns with identical names and you
  specify the row_limiting_clause, then an ORA-00918 error occurs. This
  error occurs whether the identically named columns are in the same
  table or in different tables. You can work around this issue by
  specifying unique column aliases for the identically named columns.

But in Oracle Live SQL i am able to run the below Query succesfully.
(there we will have 2 columns returned in the result with same name "department_id")

select *
from hr.employees e
join hr.departments d
on (e.department_id = d.department_id)
fetch next 10 rows with ties
;

Could someone explain me if this is where we should expect the Oracle Error or it the documentation meant another scenario ?

Thanks

SD


texts: mongodb - enableSharding for database takes five to six minutes to complete

texts:
I'm seeing some odd behavior when invoking the enableSharding command for a database. First a bit of background:

I'm using MongoDB v3.2.7 and the Java driver. Currently I have only one replica set (three nodes) which represents one shard (just one shard for - plans to scale horizontally in the future). This is front ended by three mongos nodes.

The application leveraging this MongoDB "cluster" creates thousands (greater than 10K) of databases. Inside of each database is roughly 15 collections. The databases and collections are created and initialized at runtime by the application as needed. Part of the initialization is to invoke "enableSharding" on the database and then create the collections and finally shard the collections. Normal behavior for the application also includes dropping and creating this databases somewhat frequently.

This implementation is not yet in production but still in development phase. We are trying to mimic the MongoDB architecture that we plan on going to production with. However, it seems about twice a month the primary member of the shard/replica set will begin taking five to six minutes to complete the enableSharding command. The command does complete, but associated application logic "times out". All other operations on the primary node proceed as normal. There seems to be no impact to queries or writes. It also continues to act as the "primary". Again the enableSharding command completes - it just takes five to six minutes.

One other notable item is that we use "listDatbases" as part of our monitoring. I do notice that the call to "listDatabases" also begins to slow down when the enableSharding command for a database begins to take five to six minutes to complete. I guess a few questions would be:

Has anyone else observed this issue? Or maybe something related?
Should our application be initializing databases and collections at runtime so frequently?
Is the greater than 10K databases * 15 collections going to come back and bite us?

Mainly I'm just wondering why enableSharding for a new database would slow down so much. The only way we can clear it up is to restart the replica set member.

Thanks,
Terence


texts: Select all values in same group using value in group

texts:
I have following query:

SELECT
    SubstitutionGroupID,
    Material.Number,
    Material.DescriptionEN
FROM
    MaterialSubstitution
    INNER JOIN Material on Material.MaterialID = MaterialSubstitution.MaterialID
ORDER BY
    SubstitutionGroupID, Material.Number

Result example:

SubstitutionGroupID Number      DescriptionEN
52494               303H603021  HINGE R
52494               3H603020    HINGE R
52495               303H604031  TRAY EJECT
52495               3H604030    TRAY EJECT (1/2)

I want have a query where I can define one number and recieve all numbers in one SubstitutionGroupID.


texts:
I had an abstract idea of implementing a stack in MySQL. Tried a lot of Google work but couldn't reach to any logical conclusion.

The Stack must be a collection of tables with the stack top pointing at a particular table. We may have various such stacks in our DB. The stack length may increase if for example the field from the table is full. Likewise the stack length may decrease in the opposite manner!

Could you please give me an implementation idea! is it really possible at first place? Hope I'm clear with my question.


texts:
In Microsoft Access, if you create a delete query based on two or more related tables, you must open the Property Sheet and set the Unique Records property to "Yes".  If you leave it set to "No", you get the error message "Could not delete from the specified tables".

Now, I know how to make the delete query work (by changing the Unique Records property).  What I don't understand is why.  Why does that property have to be set to "Yes"?  What does that change about the delete query?  What problem is caused by leaving it as "No"?  If you could explain why setting that process is needed, I would greatly appreciate it.

Here is an example with the associated SQL code.  Let's say I have a table called goop with the primary key id and a query called blab with the foreign key goopid.  If I create a query that uses goop and blab with a relationship connecting goop.id to blab.goopid and include all fields from goop, when I change it to a delete query, the SQL code shows up as:

DELETE goop.*
FROM goop INNER JOIN blab ON goop.id = blab.goopid;

When I change the Unique Records property to Yes, the SQL code changes to:

DELETE DISTINCTROW goop.*
FROM goop INNER JOIN blab ON goop.id = blab.goopid;


texts: MariaDb: right syntax of PREPARE statement for CONNECT type tables when passing string variable

texts:
I run 10.4.12-MariaDB on Windows10 and I use 64-bit ODBC driver to connect tables from MS Access to MariaDB database. I am fighting with the right syntax of PREPARE statement for CONNECT type tables.

Having a table named fff which is ENGINE=CONNECT TABLE_TYPE=ODBC , I try to run prepared statements on it.
While I have no trouble to run statement with integer type parameter:

set @createStr = ("select * from fff where reltpfak = ? ");
prepare theSQL from @createStr ;
set @i = 1;
execute theSQL using @i;
-- works!

I can not find a right syntax to pass string variable. My trials lead to an error:

set @createStr = ("select reltpfak from fa where Firma = ? ;");
prepare theSQL from @createStr ;
set @i = "NameOfCompany";
execute theSQL using @i;

returns Incorrect arguments to EXECUTE .

The same one just with added ' around '?'

set @createStr = ("select reltpfak from fa where Firma = '?' ;")
prepare theSQL from @createStr ;
set @i = "NameOfCompany";
execute theSQL using @i

returns Error: Got error 174 'SQLExecDirect: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Firma= NameOfCompany'.' from CONNECT .

What is the right syntax of PREPARE statement for CONNECT type tables when passing string variable?


texts:
We are using an autoincrement column in a temporary table, and doing multiple time inserts. The autogenerated ids are not being generated consecutive (I have attached a sample simple query: 16 is generated after 9 instead of 10).

DROP TEMPORARY TABLE IF EXISTS tt_temp;

CREATE TEMPORARY TABLE tt_temp
   (
      id BIGINT   AUTO_INCREMENT PRIMARY KEY ,
      value VARCHAR(2)
   )  AUTO_INCREMENT = 1;

   INSERT INTO tt_temp (value) select ('a')  union all select  ('b')  union all select  ('c')  union all select  ('c')  union all select  ('c')  union all select  ('c')  union all select  ('c')  union all select  ('c')  union all select  ('c');
SELECT * from tt_temp;

INSERT INTO tt_temp (value) select ('d')  union all select ('b')  union all select ('c');

SELECT * from tt_temp;

will somebody help me on this.


texts: Drag and Drop table object explorer into query window issue Sql Server Management Studio 18.1

texts:
I recently installed SSMS 18.1 and I am having an issue when trying to drag and drop table names from the object explorer to the query window. For example, I am writing a query and dont want type out the table name I would drag and drop the table into the query editor and the name will be there.

This is the error I get: "An exception has been encountered. This may be caused by an extension".

Any help would be appreciated.


texts:
Simple question, when patching two nodes of 11g RDBMS, do I need to patch GRID as well, or only RDMBS is enough?

Thank you in advance


texts: Data validity and accuracy according to database integrity

texts: What does it mean that data is valid and accurate according to database integrity?


texts: How to check Data replicated time on subscriber server in merge replication?

texts: I have 10 servers and all are in merge replication. when i insert data in one of all then data replicated on all servers after some time. i just want to know the time when data is replicated on any specific server. please can anyone help me?


texts: SQL Server - "The connection must be in an opened state."

texts:
I run various queries against a SQL Server 2012 instance, and occasionally, the following error message will pop up:

  The connection must be in an opened state.
  Parameter name: connection (SQLEditors)

Although it usually happens while I've left that particular tab idle in SSMS for a while (~10-45 minutes), it also sometimes happen in between running queries nearly back-to-back. The connection state at the bottom will always say "Connected" before I run the query (and obviously immediately change to "Disconnected" when that error shows up).

The database's AutoClose option is disabled, and I also have the "Disconnect after the query executes" option disabled locally. There aren't any scheduled jobs I can find that might be routinely disconnecting queries. Help? Ideas?


texts: Can CDC read through a log file when the agent job hasn't been running?

texts: We currently had CDC running on our SQL server, however, at some point last week the CDC capture job stopped running and started throwing an error. At this point when it stopped somebody had restored the database from a backup during a failed upgrade. I am interested to know if CDC will pick up where it left off when the underlying issues stopping the job from running is resolved, and read through the log file creating CDC entries for the time in between, or will it simply start the capture again?


texts: What is the difference between CREATE INDEX and ALTER TABLE ADD INDEX?

texts:
I have searched the web but I have failed to find a conclusive answer for this question.

Is there any difference between CREATE INDEX and ALTER TABLE ADD INDEX?

Are there any problems that may arise when I use one or the other or do both work the same way?

Server version: 5.5.32 - MySQL Community Server (GPL)


texts: SQL Server 2016 : mirroring between 2 VMs

texts:
TLDR: SQL Server mirror setup between 2 different VM servers on domain, need help

I have 2 virtual machines, both running SQL server 2016, the principal and the mirror that I want to set up for mirroring

I can connect to the principal on the mirror and vice versa. Both are on the same domain, and the SQL service is being ran with the same domain account.

That domain account has connect permissions on the endpoints created for mirroring, and I can telnet to the port that the endpoints are listening on.

I've restored the principal database and transaction log with no recovery on the mirror

and using the mirror setup GUI, I get the error:

  Database cannot be opened it is in the middle of a restore

Using the SET PARTNER script I get the error:

  The server network address "[TCP:\network address:port]" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

Thoughts?


texts: Why does PostgreSQL explicitly cast NULL to parameter data type in functions?

texts:
Let's say I create the following function:

create or replace function my_new_function(
    p_something text default null
)
etc....

But if I go to check the function on PostgreSQL I see this instead:

CREATE OR REPLACE FUNCTION my_new_function(p_something text default NULL::text)

Why does it do that?


texts: Trying to find the last time that a value has changed

texts:
I have a table that has an ID, a value, and a date. There are many IDs, Values, and dates in this table.

Records are inserted into this table periodically. The ID will always stay the same but occasionally the value will change.

How can I write a query that will give me the ID plus the most recent time the value has changed? Note: the value will always increase.

From this sample data:

  Create Table Taco
 (  Taco_ID int,
    Taco_value int,
    Taco_date datetime)

Insert INTO Taco
Values (1, 1, '2012-07-01 00:00:01'),
        (1, 1, '2012-07-01 00:00:02'),
        (1, 1, '2012-07-01 00:00:03'),
        (1, 1, '2012-07-01 00:00:04'),
        (1, 2, '2012-07-01 00:00:05'),
        (1, 2, '2012-07-01 00:00:06'),
        (1, 2, '2012-07-01 00:00:07'),
        (1, 2, '2012-07-01 00:00:08')

The result should be:

Taco_ID      Taco_date
1            2012-07-01 00:00:05

(Because 00:05 was the last time Taco_Value changed.)


texts: Are there open source RDBMS that support non-backup snapshots?

texts:
I'm not looking for point-in-time backups but some kind of "time machine" read-only functionality.

For example when running a SELECT query I would like to tell the database to run it on the data as it was, say, 1 month ago. Snapshots granularity could go from on-demand to taking a snapshot after each transaction.

Ideally it would be possible also to purge data on-demand, something like "stop storing snapshots older than 10 days".


texts: How to save an interval of years in postgresql

texts:
I have to save an interval of years, like:

id intervalYears
1  2014/2015
2  2015/2016
3  2016/2017

And so on. Those years are like accademic years, so the first year must be lesser then the second, but greater or equal then the actual year.

So, how to save this datatype? Should i separate the dates and add some costraints?


texts: how to install a second server of mariadb

texts:
I have a server where cpanel is installed (it includes mysql 5, php). What I need is to install a second database server mariadb 10.1.20. I tried through:

yum install MariaDB-10.1.20-centos6-x86_64-common.rpm

But it shows me conflicts like

Transaction Check Error:
  file /usr/share/mysql/charsets/Index.xml from install of MariaDB-common-10.1.20-1.el6.x86_64 conflicts with file from package MySQL56-server-5.6.38-1.cp1162.x86_64
  file /usr/share/mysql/charsets/README from install of MariaDB-common-10.1.20-1.el6.x86_64 conflicts with file from package MySQL56-server-5.6.38-1.cp1162.x86_64

What can I do for this case?


texts: SQL Server: Is it possible to create a calculated index

texts:
In SQL Server, is it possible to create an index on a calculation?

The following does not work for me:

CREATE INDEX customermonth ON customers(month(dob));

Some databases allow me to do this. Is there a trick to doing this in SQL Server?


texts: Script out Oracle DDL in an automated fashion

texts:
Oracle SQL Developer is able to export DDL through Tools -> Database Export... This works very well, but requires manual intervention.

I know of DBMS_METADATA.get_ddl(), but have found that the export isn't perfect.  I ran into issues where the exported DBMS_METADATA DDL wasn't usable without first fixing up problems like breaks in the middle of a keyword, and worse. However, if anyone knows a way of exporting DDL through DMBS_METADATA that can run without manual fixes, that'd be a great solution too.

Basically, I am looking for an automatic/scriptable way to export DDL identical to what is exported through the manual way.

How can I do that?


texts: How to remove NULL values in multidimensional array?

texts:
SELECT dt.name,dt.description,dt.odobj,dt.defval,dt.datatypeid
      ,array_agg(array[dtv.description::text,dtv.odobj::text,dtv.name::text,dtv.datatypevalueid::text,dtv.datatypeid::text]) as arrayofdatatypevalues
FROM datatypevalue dtv
FULL JOIN public.datatype as dt on dt.datatypeid = dtv.datatypeid
WHERE dt.name !='Integer' and dt.name !='String' and dt.name !='Boolean'
GROUP BY dt.name,dt.description,dt.odobj,dt.defval,dt.datatypeid;

Returns output like (example with two result rows):

{{NULL,NULL,NULL,NULL,NULL}}
{{NULL,10,xxs,1,21},{NULL,9,xyz,0,21}}

How do I replace this {{NULL,NULL,NULL,NULL,NULL}} with {}?


texts: Is there impact on query execution as more partitions are added to a table?

texts:
We are looking to heavily use partitioned tables in our data warehouse environment. We are looking to take advantage of fast partition switching as well as partition elimination during analytical queries.

At initial try we partitioned the table daily for 10 years, however, noticed the queries that select from single partition are much slower than are expected when the table wasn't partitioned at all or started with only 10 partitions. I was wondering if there's something that needs to be done to allow for a large number of partitions in the single table.

All partitions are using CLUSTERED COLUMNSTORE index on SQL Server 2014 enterprise.


texts: Linked Server to Azure Error: "Access to the remote server is denied because no login-mapping exists."

texts:
I'm setting up a linked server from an on-prem SQL Server to a database on MS Azure (see below).  When non-admin users query the Azure database via the linked server they get:

  Msg 7416, Level 16, State 2, Line 1 Access to the remote server is denied because no login-mapping exists.

Any thoughts on how to get this working?

EXEC sp_addlinkedserver
@server='LincServer',
@srvproduct='',
@provider='sqlncli',
@datasrc='myAzureDB.database.windows.net',
@location='',
@provstr='Encrypt=Yes;TrustServerCertificate=No;User ID=myAzureID',
@catalog='MyDB'

EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'LincServer',
@useself = 'false',
@rmtuser = 'myAzureID',
@rmtpassword = '########'
GO


texts: Need an efficient mysql database schema to store points/ratings

texts:
I have four main tables-

1.user(user_id, name,...)
2. post(post_id, description, ...)
3.comment(comment_id,user_id,comment,...)
4.reply(reply_id,user_id, reply,...)

and

5.user_post(user_id,post_id)
6.post_comment(post_id,comment_id)

Now user can give points 1 to 10 (similar to rating) to each comment,reply and post and I also need to count the total points earned by a user with their contents(post,comment,reply). Currently I have two options.

option 1:
I can have a big table named points and store the number of points for each content with a type column which sacrifices referential integrity E.g.-

points(content_id, type, point, given_by)

OR,

option 2:
Having three separate tables which doesn't sacrifice referential integrity. E.g.-

post_point(post_id, point, given_by)
comment_point(comment_id, point, given_by)
reply_point(reply_id, point, given_by)

So, what would be the best and future proof option here or Is there any better solution ?? Thanks.


texts: The transaction log for database 'tempdb' is full but it should have grown

texts:
I have a SQL Server 2008 R2 that started showing that the TempDB transaction log was full. I checked the log file settings and there was no growth restriction and there was plenty of disk space.

I had no luck on finding an explanation of what could have caused this.

Has anyone experienced this in the past?

Thank you.