texts:
I am reading the book 'Pro SQL Server 2019 Administration' from Carter, where in the Log shipping section he mentions:

"Unlike other high availability and disaster recovery technologies, an
amount of administrative effort is associated with failing over log
shipping. To fail over log shipping, you must back up the tail end of
the transaction log, and copy it, along with any other uncopied backup
files, to the secondary server."

He previously specified that log shipping is mostly used in DR scenarios... My question is, if DR hit the primary data center, how is one supposed to "up the tail end of the transaction log, and copy it, along with any other uncopied backup files"?!
If DR hits, and we have set up log shipping, my guess is that we are only left with whatever the secondary replicas have... That is, if I am able to 'backup the tail' then the server must be up and running...


texts: Database design for bigger tracking table

texts:
I am creating a database for a prayer tracker. There are 5 types of prayer for each day. I have a table called "Types"
id, name
1, Type 1
2, Type 2
3, Type 3
4, Type 4
5, Type 5

I want to save records for each user against each prayer type for each day. Below is my structure for tracking table.
id, type_id (FK of Types), user_id (FK of user table), date (Y-m-d)

I have 500K+ users and for each user there can be max of 5 records against each day. This will be in millions. Basically I want to create an optimized db structure, So it should be faster while doing queries.

User is login and he should see all records of him/her against each
day.
After 1 year, if there are 5 records against each day then for
500K users, it will be 912,500,000 records. Table will be much
bigger.
No. of users are also increasing day by day.

So In order to optimize the database, what will be best practice for above structure of tracking
table?


texts: Best way forward for setting up Debian 9 / MariaDB (10.1.26-0+deb9u1) with root user Adminer access

texts:
I have a new Debian 9 LAMP server (includes MariaDB v10.1.26 rather than MySQL). I note that in this build, the debian-sys-maint user no longer exists. Instead a unix_socket is used to grant root user access (so no password is required when logged in as MySQL root user, from the Linux root user account).

All in all, that seems like a great idea. However, I note that I can no longer log in to the root account via Adminer (DB Admin web UI -somewhat like PHPMyAdmin).

So I disable the unix_socket and set a root password. Great, I can now log into the root account in Adminer. See the MariaDB doc page for info.

However, as stated at the bottom of that doc: "Note that this may break scripts used by your OS distribution." and they give the workaround as writing the password to a config file. Obviously that would work, it's how the old debian-sys-maint user worked after all. But it seems a little backward to disable this new feature and revert to previous behaviour.

My question is then, should I instead create a new user with root level permissions for use in Adminer instead of allowing root password login? Or should I just recreate the debian-sys-maint user style set up?

Additionally, I have read about the mysql_config_editor tool which saves the passwords in some encrypted format, thus making the storage of the relevant password more secure. I note that that tool explicitly comes from MySQL (not MariaDB) and is not shipped with the default MariaDB server in Debian. If reverting to the old debian-sys-maint user style is the way to go, is there any recommendation on using (or trying to use) this tool with MariaDB? Or do I have to just lock down the permissions on the config file (e.g. 600)?


texts: Changing passphrase for EncryptByPassPhrase

texts:
I am using EncryptByPassPhrase to encrypting some columns.

If I want to change the passphrase in the future, how can I change it?


texts: I want to store partial information related to a date. I might know the year and month but not the day. I might know the day and month but not the year. I might know the date lies in an open or closed interval. What are my options for modeling this type of data?


texts: Suppress successful insert statements or review errors on large inserts in Oracle 12c

texts:
I have ~130 SQL scripts that INSERT about ~750,000 records into an Oracle 12c database.

I have a master SQL that kicks all this off using this template:

set termout off
set echo off;

delete from table1;
@C:\Scripts\table1_DATA_TABLE.sql
set termout on;
REM ---
REM completed table1_DATA_TABLE.sql
REM ---
set echo off;
set termout off;
commit;

<repeat for every script>

The issue is, I'm getting thousands of 1 row inserted. and it's drowning out errors.  Is there a way I can suppress the successful INSERT notifications and only leave the errors?  Or, is there a way I can retrieve all the errors of a session/script ?


texts: Efficient way to fetch records since a given criteria

texts:
I'm trying to implement a logic where the user can say give me n records since a given id#. E.g.

SELECT TOP (100) col1, col2, ... colN
FROM Table1
WHERE ID > @id
ORDER BY ID

Performance is the biggest issue here, especially when you get into nested loops for complex joins. I've looked at the new OFFSET-FETCH feature in SQL Server 2012 but they require you to specify a number rather than a WHERE clause. I wouldn't know the offset unless I count num rows beforehand.

Is there an alternate way to do this efficiently in SQL Server (2008 R2 and above)?

Update: Complete SQL as generated by EF Code first

-- Region Parameters
DECLARE @p__linq__0 BigInt = 60375518904121
DECLARE @p__linq__1 BigInt = 60375518904121
-- EndRegion
SELECT
[Project3].[Id] AS [Id],
[Project3].[C2] AS [C1],
[Project3].[C1] AS [C2],
[Project3].[C3] AS [C3]
FROM ( SELECT
    [Limit1].[Id] AS [Id],
    [Limit1].[C1] AS [C1],
    [Limit1].[C2] AS [C2],
    [Limit1].[TmId] AS [TmId],
    CASE WHEN ([Extent4].[TmId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C3]
    FROM   (SELECT TOP (100) [Project2].[Id] AS [Id], [Project2].[TmId] AS [TmId], [Project2].[C1] AS [C1], [Project2].[C2] AS [C2]
        FROM ( SELECT
            [Extent1].[Id] AS [Id],
            [Extent2].[TmId] AS [TmId],
            CASE WHEN ([Extent2].[TmId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
            1 AS [C2]
            FROM  [dbo].[TextMessages] AS [Extent1]
            LEFT OUTER JOIN [dbo].[Tms] AS [Extent2] ON [Extent1].[Id] = [Extent2].[TmId]
            WHERE ( EXISTS (SELECT
                1 AS [C1]
                FROM [dbo].[Tmr] AS [Extent3]
                WHERE ([Extent1].[Id] = [Extent3].[TmId]) AND ([Extent3].[IsDel] = 1) AND ([Extent3].[UserId] = @p__linq__0)
            )) OR ([Extent1].[CreatedBy_Id] = @p__linq__1)
        )  AS [Project2]
        ORDER BY [Project2].[Id] DESC ) AS [Limit1]
    LEFT OUTER JOIN [dbo].[Tmr] AS [Extent4] ON [Limit1].[Id] = [Extent4].[TmId]
)  AS [Project3]
ORDER BY [Project3].[Id] DESC, [Project3].[TmId] ASC, [Project3].[C3] ASC

Update:
The real bottleneck turned out to be network latency caused by EC2 servers.


texts: OPENXML to get content by using element and attribute name

texts:
In this SQL Server OPENXML example:
declare @xmldata xml

set @xmldata =
'<?xml version="1.0"?>
<wddxPacket version="1.0">
    <data>
        <var dimension="task"><string>Task 1</string></var>
        <var dimension="startdate"><string>2020-11-14</string></var>
    </data>
    <data>
        <var dimension="startdate"><string>2020-11-15</string></var>
        <var dimension="task"><string>Task 2</string></var>
    </data>
</wddxPacket>'

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldata

SELECT *
FROM OPENXML(@hDoc, '//wddxPacket/data',2)
        WITH(
            task          varchar(200) 'var[1]/string',
            startdate     varchar(200) 'var[2]/string'
    )

EXEC sp_xml_removedocument @hDoc

This is the current output:
task         startdate
------       ----------
Task 1       2020-11-14
2020-11-15   Task 2

But I really want this output:
task         startdate
------       ----------
Task 1       2020-11-14
Task 2       2020-11-15

To identify the content, I cannot use "var" alone, because all elements of interest are named "var". I cannot use position [1] or [2], because the position numbers are inconsistent in the XML, resulting in the current mixed-up output. How do I identify the elements based on "var" and the "dimension" attribute names of "task" or "startdate"?


texts: Upgrade PostgreSQL 9.4 to 9.5 : LC_ALL unset

texts:
I'm trying to upgrade my PostgreSQL 9.4 database to PostgreSQL 9.5 but everytime while running sudo pg_upgradecluster 9.4 main I get this error:

Stopping old cluster...
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
    LANGUAGE = "en_US.UTF-8",
    LC_ALL = (unset),
    LC_CTYPE = "UTF-8",
    LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Error: The locale requested by the environment is invalid.
Error: Could not create target cluster

But when I run locale I see the LC_ALL is actually set:

LANG=en_US.UTF-8
LANGUAGE=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=en_US.UTF-8

I can remember having the same issue while upgrading from PostgreSQL 9.3 to PostgreSQL 9.4 but that time I fixed this by adding this line to /etc/environment:

LC_ALL=en_US.UTF-8

But this doesn't work anymore.


texts: Moving a date between tables is causing error ORA-01841

texts:
Here's my script:

INSERT INTO BRAND_RECORD
SELECT
    STRENGTH,
    GENERIC_NAME,
    TO_DATE(a1.FIRST_CLAIM_DATE) as INTERCHANGEABILITY_DATE
FROM DRUG_INFO
INNER JOIN
(
    SELECT
        FIRST_CLAIM_DATE,
        SEQUENTIAL_ID
    FROM DRUG_INFO
    WHERE
        SEQUENTIAL_ID IN
        (
            SELECT MIN(SEQUENTIAL_ID)
            FROM
            (
                SELECT *
                FROM DRUG_INFO
                WHERE
                    SEQUENTIAL_ID NOT IN
                    (
                        SELECT MIN(SEQUENTIAL_ID)
                        FROM DRUG_INFO
                        GROUP BY GENERIC_NAME, STRENGTH
                    )
            )
            GROUP BY GENERIC_NAME, STRENGTH
        )
) a1
    ON (a1.SEQUENTIAL_ID = DRUG_INFO.SEQUENTIAL_ID + 1);

Summary: Everything after INSERT INTO BRAND_RECORD works perfectly well. It is a query that produces a STRENGTH, GENERIC_NAME, and INTERCHANGEABILITY_DATE.
BRAND_RECORD is a table with the columns STRENGTH, GENERIC_NAME, INTERCHANGEABILITY_DATE.

However, I get the following error:

  Error report - SQL Error: ORA-01841: (full) year must be between -4713
  and +9999, and not be 0
  01841. 00000 -  "(full) year must be between -4713 and +9999, and not be 0"
  *Cause:    Illegal year entered
  *Action:   Input year in the specified range

This seems like some sort of conversion problem. But I'm selecting a date and putting it into a table that takes a date! Any idea what the problem might be?

UPDATE: I changed my default date format to MM/DD/YYYY HH24:MI:SS. I now get the following error when I try this:

  Error report - SQL Error: ORA-01858: a non-numeric character was found
  where a numeric was expected 01858. 00000 - "a non-numeric character
  was found where a numeric was expected" *Cause: The input data to be
  converted using a date format model was incorrect. The input data did
  not contain a number where a number was required by the format model.
  *Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation. Error
  starting at line : 1 in command - INSERT INTO BRAND_RECORD Error at
  Command Line : 1 Column : 24 Error report - SQL Error: ORA-00926:
  missing VALUES keyword 00926. 00000 - "missing VALUES keyword" *Cause:
  *Action:

Here are my create table statements for both tables:

CREATE TABLE BRAND_RECORD
(
  GENERIC_NAME VARCHAR2(60 BYTE) NOT NULL
, INTERCHANGEABILITY_DATE DATE
, STRENGTH VARCHAR2(25 BYTE)
)
LOGGING
TABLESPACE DATAL03
PCTFREE 10
INITRANS 1
STORAGE
(
  INITIAL 65536
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  BUFFER_POOL DEFAULT
)
NOCOMPRESS
NOPARALLEL;

CREATE TABLE DRUG_INFO
(
  DIN VARCHAR2(20 BYTE) NOT NULL
, TRADE_NAME VARCHAR2(50 BYTE) NOT NULL
, FORM VARCHAR2(40 BYTE)
, STRENGTH VARCHAR2(25 BYTE)
, GENERIC_NAME VARCHAR2(60 BYTE) NOT NULL
, MANUFACTURER VARCHAR2(100 BYTE) NOT NULL
, CR_DATE DATE
, NOC_DATE DATE
, FIRST_CLAIM_DATE DATE
, SEQUENTIAL_ID NUMBER NOT NULL
)
LOGGING
TABLESPACE DATAL03
PCTFREE 10
INITRANS 1
STORAGE
(
  INITIAL 65536
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  BUFFER_POOL DEFAULT
)
NOCOMPRESS
NOPARALLEL;


texts: popup windows doesnt showed on oracle OUI

texts:
I have a fresh vm station with the recent centos 7 with GUI (everything is standard).
This problem happens on every popup window that appears on the oracle OUI, DBCA, ASMCA etc.. I'm using X to display the runInstallar (or whatever GUI command). sometimes I can enlarge the size of the window and revel the content of it but sometimes I don't. It's really frustrating and I'm sure many cross this issue before but I couldn't find it in google.


texts:
I'm handling a big table which is at 200GB+ and has around 1 billion rows.

I'm looking into splitting the table into smaller tables and then joining the results whenever I query.

We do mainly reading on that table with occasional writing.

It it a good idea? Or, is it something I need not worry?

My database server has 4CPU and 32GB RAM.

I have the primary key as index to save space. Currently, there are no issues. However, I just considering whether to split it earlier or just keep it this way. What are the pros and cons of splitting into smaller tables with , let's say, 250 million rows each instead of plain 1 billion rows table? Do you think my 32GB RAM will be able to handle it?


texts: Rights to execute stored procedure, but not read underlying tables directly

texts:
I have a stored procedure that contains lots of different columns from many different tables; I want to give 20 users access to the results of the stored procedure via Excel.

Is it possible to make the user to get access only to the stored procedure, and not to the underlying tables and views?


texts: SQL Server: Can you join implicitly using key relationships?

texts:
If I have two tables;

Table A
id | etc
1  | etc
2  | etc
etc

And

Table B
id | A_id | etc
1  | 2    | etc
2  | 2    | etc
3  | 1    | etc
etc

The relationship between Table A and Table B is defined in the database as a foreign key with cascading delete. Do I still have to explicitly declare join columns between these tables? Several ORMs that I have previously worked with do not require this (for example the PHP Silverlight ORM), and I was thinking that if you requested unambiguous column names, why not? It seems to me that defining FK relationships should automatically define which columns to join a table one

For example, I want to know if I can write this query:

SELECT * FROM [Table B] B LEFT OUTER JOIN [Table A] A ON B.A_id = A.id

As

SELECT * FROM [Table B] B LEFT OUTER JOIN [Table A] A


texts: Analysis Services not allowing other/remote connections other than localhost

texts:
Analysis services not allowing any connections  except localhost. Authentication box is disabled and only allow windows authentication. How can I connect to different remote analysis servers using sql server authentication.
Thanks.


texts: Generate script to automate renaming of default constraints

texts:
Background: Some of our default column constraints were generated without explicit names, so we get fun names that vary from server to server like: DF__User__TimeZoneIn__5C4D869D

I would prefer to have them all manageable with a consistent naming like DF_Users_TimeZoneInfo so that we can ensure that the appropriate constraints exist on future target tables (like in RedGate compare, or even just visually)

I've got a script that mostly works for what I want:

select 'sp_rename N''[' + s.name + '].[' + d.name + ']'',
   N''[DF_' + t.name + '_' + c.name + ']'', ''OBJECT'';'
from sys.tables t
    join
    sys.default_constraints d
        on d.parent_object_id = t.object_id
    join
    sys.columns c
        on c.object_id = t.object_id
        and c.column_id = d.parent_column_id
    join sys.schemas s
        on t.schema_id = s.schema_id
WHERE d.NAME like 'DF[_][_]%'

But this just gives me a resultset, and not something I can actually pass into an exec or whatever.

How can I make this so I can just execute those sp_rename scripts without having to resort to copying out all the returned elements and pasting them into a new query window and running them again? Trying to save as many keystrokes as possible so I can correct this in many environments.


texts: What date/time literal formats are LANGUAGE and DATEFORMAT safe?

texts:
It is easy to demonstrate that many date/time formats other than the following two are vulnerable to misinterpretation due to SET LANGUAGE, SET DATEFORMAT, or a login's default language:

yyyyMMdd                 -- unseparated, date only
yyyy-MM-ddThh:mm:ss.fff  -- date dash separated, date/time separated by T

Even this format, without the T, may look like a valid ISO 8601 format, but it fails in several languages:

DECLARE @d varchar(32) = '2017-03-13 23:22:21.020';

SET LANGUAGE Deutsch;
SELECT CONVERT(datetime, @d);

SET LANGUAGE Français;
SELECT CONVERT(datetime, @d);

Results:

  Die Spracheneinstellung wurde auf Deutsch geändert.
  Msg 242, Level 16, State 3
  Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs.
  Le paramètre de langue est passé à Français.
  Msg 242, Level 16, State 3
  La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

Now, these fail as if, in English, I had transposed the month and day, to formulate a date component of yyyy-dd-mm:

DECLARE @d varchar(32) = '2017-13-03 23:22:21.020';

SET LANGUAGE us_english;
SELECT CONVERT(datetime, @d);

Result:

  Msg 242, Level 16, State 3
  The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

(This isn't Microsoft Access, which is "nice" to you and fixes the transposition for you. Also, similar errors can happen in some cases with SET DATEFORMAT ydm; - it isn't just a language thing, that's just the more common scenario where these breakages happen - and aren't always noticed because sometimes they aren't errors, it's just that August 7th became July 8th and nobody noticed.)

So, the question:

Now that I know there are a bunch of unsafe formats, are there any other formats that will be safe given any language and dateformat combination?


texts:
I have deployed a package with 2 package parameters which had their values set inside the package prior to deployment.  However if I look at those parameters in my SSIS catalog project the values of them are blank.

Is this usual behaviour?  Shouldn't the values have been passed to the parameters in my SSIS catalog?

Thanks


texts: How to verify if given user@host with 'password' exists in MySQL 8.0+?

texts:
I write sanity check script for an application. One of the tests is supposed to verify that a given user@host with 'password' exists in MySQL. I'm using simple native hash passwords. In MySQL5.7- there is the PASSWORD() function that allows me to query mysql.user and verify that the user exists and the passwords match. Is there a way to accomplish this in MySQL8.0+? Documentation page for PASSWORD() in MySQL 5.7 does not provide any alternatives and the function is removed in MySQL 8.0.

I could simply try to add the user again, but I feel my script should know and register an event of missing user with expected credentials.

Please note that I do not necessarily need to generate password hash, I just want to verify that given user/password exists. This needs to be automated so solutions like 'try to log in with user/password from host machine' do not count as I may not have access to the host machine


texts: what is the correct name for "rich" views?

texts:
I was reading about this the other day that with a good DB you can create a view of data, that is automatically updated and cached

say you have:

time user was online
this is measured with 2 datetimes, start and end date
many entries
what is the total time?

this would be an example of a "rich" view

a view that selects all entries, and adds up the dates, and provides them in a view

when a value is updated, automatically the view is updated

now I don't know the word for it.

What is the proper term for it?

I know mysql does not support it (at least that is what I think)


texts: Moving data in multiple tables from old database to one table in new database

texts:
I have two SQL Server 2008 databases. One is for my old application (full of data) and the other is for a complete re-write of my application.

I want to migrate the data, but I have no idea how to tackle it. Some of the tables have exactly the same structure, but some tables in the new database are composed from columns of multiple tables in the old database.

Is there a way to create such a script/project/application in which I can define the data transformation while also maintain constrains?


texts: Analyzing performance of an insert int postgres

texts:
I have a statement which is inserting a bunch of rows (doesn't really matter what or where for this question) into a Postgres DB and it isn't as fast as I'd like. I can run an explain query to see what it's doing and I get something like this:
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Insert on datapoints  (cost=0.00..6837.74 rows=44916 width=1774) (actual time=24718.425..24718.425 rows=0 loops=1)
   ->  Seq Scan on fred  (cost=0.00..6837.74 rows=44916 width=1774) (actual time=0.024..369.642 rows=44916 loops=1)
 Planning time: 0.081 ms
 Trigger for constraint datapoints_tag_source_fkey: time=191.508 calls=44916
 Trigger for constraint datapoints_sheet_type_fkey: time=524.057 calls=44916
 Trigger for constraint datapoints_subcontext_fkey: time=142.782 calls=44916
 Trigger for constraint datapoints_source_type_fkey: time=437.829 calls=44916
 Trigger for constraint datapoints_doc_type_fkey: time=270.409 calls=44916
 Trigger for constraint datapoints_comment_type_fkey: time=87.718 calls=44916
 Trigger for constraint datapoints_preferred_dpid_fkey: time=29.739 calls=44916
 Execution time: 26425.926 ms
(11 rows)

This is great in that I see the cost of the various triggers and the select part of the query but if I add up all those parts it's about 2s (less than 10% of the total time). It doesn't tell me much about the actual insert other than it took about 24 seconds. I'm guessing that a fair part of the cost is in updating indexes but I don't know this for sure. How I can get a breakdown of which indexes are being updated and the relative costs of each? Is this even possible?


texts: federated tables? synchronization? replication? [mysql]

texts:
I have a REMOTE suppliers' table with, let's say, a code and a name, and several client databases will be creating new suppliers.

Server database

code  |  name
------+-------
1     |  Mark
2     |  John
3     |  Jodie

I thought that a federated table would fit the best, as every client could create new suppliers in a centralized table which assigns incremental codes,

Obviously, if the Internet connection is down, clients won't be able to create new suppliers, so this won't work for me.

I thought that the best idea would be assigning a prefix to each client, and the code count would be assigned locally.

Local table client A

code   |  name
-------+------
A1     |  Mark
A2     |  Jodie

Local table client B

code  |  name
------+------
B1    |  John

Then, the data would be merged into a single remote table.

Remote merged table

code  |  name
------+---------
A1    |  Mark
B1    |  John
A2    |  Jodie

But at this point, I am lost, I don't know if there is a solution for this problem, or I would need to merge them with a cron job + script.

Maybe a multiple-masters-to-1-slave would work?

Is there any way to schedule merge jobs?

Any idea would be appreciated.


texts:
I have an admittedly-complex query that I'm optimizing and I'm stuck on the next step. I have pinpointed the bulk of my problem to one part of my query that's doing the following Key Lookup but I'm confused both as to why it's doing this and what it's so slow. I'm not even referencing the PK in that table. I'm doing:

WHERE EXISTS
  (SELECT 1 FROM tblPriceTierQuantities [Extent8]
   INNER JOIN xxxx ON xxx=Extent8.AccountRateScheduleID
   WHERE Extent8.PriceTierID=tblPriceTiers.oid)

The details on the execution plan show as this:

That shows up as the only thing taking significant time to execute. Indeed, if I simply comment out that WHERE Extent8.PriceTierID=tblPriceTiers.oid clause, then the query is as speedy as I want it to be. The thing is, this lookup seems to be pointing to Extent8's oid and not tblPriceTier's oid field.

I'm confused why it's hitting the clustered index on that table because of this query. Nothing else in this query hits that table at all. Can you explain why this clustered lookup exists and also why it might be so slow? The ONLY columns ever referenced on this tblPriceTierQuantities table are AccountRateScheduleID and PriceTierID, NEVER oid which is the only column in this clustered index.


texts:
I am using amazon rds mysql 5.6 master instance and 4 slaves.
there is big table with 800 GIB size that i want to drop it for releasing  space. But when i execute drop command, the server entering to "freeze mode" and cant execute any kind of commands.

similar issue : https://bugs.mysql.com/bug.php?id=44757

I need to remove it from production rds.

there is no transactions on this table
innodb_file_per_table  = ON

I though about Deleting rows in chunks for not create big lag for slaves and then (optional optimize) drop the table.


texts:
Trying to make a messaging system, similar to FB, people can message more than one person, when they do it creates a row for the user with the conversation id and user_id in conversation_user.

create table conversations(
  id int
);
create table users(
  id int,
  name text
);
create table conversations_users(
  user_id int,
  conversation_id int
);
create table conversations_reply(
  id int,
  user_id int,
  body text,
  conversation_id int
);

I want to select the last 10 conversation that user_id 1 is a participant in and select the last post of that conversation. How would I go about doing this?

http://sqlfiddle.com/#!9/eb0075


texts:
I have many databases for which I have configured data guard setup. When it comes to monitoring the standby's, I use OEM to check the lag and I have enabled mail alerts as well as and when gap is more than 50 archives.

But the thing what I recently observed is that in one of my standby databases my recovery got stopped for one or more reasons and unfortunately lag also did not go above 50 so there was no alerts. In such a situation how I can ensure when my standby's recovery MRP itself is not active and I should be alerted in such a situation..

How is it possible? Any soultions?


texts: What are the factors to consider or to worry when we migrate to SQL Server 2017 from 2012

texts:
We have been asked to migrate to SQL Server 2017 from 2012. Only coding experience as background we are not so aware of what are the crucial points to consider or to worry when migration.

Some of the points I'm concerned are:

Is there is anything missing in 2017 which was working fine in 2012.
Missing in the sense of an upgrade in 2017.
Can we restore a backup
from 2012 in 2017
Also is there is any chance of any kind of data truncation or something like that if we restore DB backup from 2017 into 2012 (If restoration possible)


texts: Why does SQL Server use a better execution plan when I inline the variable?

texts:
I have a SQL query that I am trying to optimize:

DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962'

SELECT
  Id,
  MIN(SomeTimestamp),
  MAX(SomeInt)
FROM dbo.MyTable
WHERE Id = @Id
  AND SomeBit = 1
GROUP BY Id

MyTable has two indexes:

CREATE NONCLUSTERED INDEX IX_MyTable_SomeTimestamp_Includes
ON dbo.MyTable (SomeTimestamp ASC)
INCLUDE(Id, SomeInt)

CREATE NONCLUSTERED INDEX IX_MyTable_Id_SomeBit_Includes
ON dbo.MyTable (Id, SomeBit)
INCLUDE (TotallyUnrelatedTimestamp)

When I execute the query exactly as written above, SQL Server scans the first index, resulting in 189,703 logical reads and a 2-3 second duration.

When I inline the @Id variable and execute the query again, SQL Server seeks the second index, resulting in only 104 logical reads and a 0.001 second duration (basically instant).

I need the variable, but I want SQL to use the good plan. As a temporary solution I put an index hint on the query, and the query is basically instant. However, I try to stay away from index hints when possible. I usually assume that if the query optimizer is unable to do its job, then there is something I can do (or stop doing) to help it without explicitly telling it what to do.

So, why does SQL Server come up with a better plan when I inline the variable?


texts:
I have three tables orders, customer_old and customer_new.

The orders table contains the old_user_login and associated order number.
The customer_old table contains the information on
customer, namely old_user_login and user_fullname.
The customer_new table contains new_user_login and user_fullname.

The idea is to replace the old_user_login with new_user_login for all the rows in the orders table, and the only thing that matches is the user_fullname in the two customer tables. The old customer table will effectively be made redundant and deleted after this operation is done.

How do I go about doing this?


texts:
SELECT property, geo, stars
FROM table
WHERE SUBSTRING_INDEX(geo, '>', 3) LIKE '%$mylocation%'
...loop to show results

geo column has values like - Spain>Canary Islands>Lanzarote>Some Resort

Index set on 'geo' column - Table is MyISAM


texts:
I have two tables and a trigger and am inserting values in ProjectsTable like this:

CREATE TABLE ProjectsTable
(
  ProjectID     NUMBER(6) NOT NULL,
  ProjectName   VARCHAR2(200) NOT NULL,
  Cost      NUMBER(10,2),
  ExpenseTotal  NUMBER(10,2),
  CostRemaining     NUMBER(10,2),
  PRIMARY KEY (ProjectID)
);

CREATE TABLE ExpenseTable
(
  ID            NUMBER(6) NOT NULL,
  ProjectID     NUMBER(6) NOT NULL,
  ExpenseAmount NUMBER(10,2),
  ExpenseDate   NUMBER(4),
  CONSTRAINT fk
  FOREIGN KEY (ProjectID)
  REFERENCES ProjectsTable(ProjectID)
);

CREATE TRIGGER ExpenseSum AFTER INSERT ON ExpenseTable FOR EACH ROW
BEGIN
    UPDATE ProjectsTable P
    SET ExpenseTotal =
    (SELECT SUM(ExpenseAmount) from ExpenseTable
    where ExpenseTable.ProjectID= P.ProjectID)
    where P.ProjectID = :New.ProjectID;
END;
/

INSERT INTO ProjectsTable VALUES (101,'AAA',5000,0,5000);
INSERT INTO ProjectsTable VALUES (102,'BBB',3000,0,3000);
INSERT INTO ProjectsTable VALUES (103,'CCC',2000,0,2000);

But when I tried to insert values into the ExpenseTable table, it gave me the following errors:

  ERROR at line 1: ORA-04091: table ExpenseTable is mutating, trigger/function may not see it
  ORA-06512: at ExpenseSum , line 2
  ORA-04088: error during execution of trigger ExpenseSum


texts:
So I have been tasked to write a query involving dates for a contract management system we have. The database stores the date the contract's primary terms expired, the frequency that the contract auto-renews, and the notice period required to make a change to the contract. Here is a query with some sample data:

WITH EX_DATA AS (
SELECT '28-FEB-2008' EXPIRE_DT, 30 TERM_NOTICE, 'YEARLY' NOTICE_FREQ, 12 NOTICE_MONTHS FROM DUAL
UNION ALL
SELECT '1-MAR-2005', 60, 'YEARLY', 12 FROM DUAL
UNION ALL
SELECT '1-DEC-2007', 30, 'MONTHLY', 1 FROM DUAL
)
SELECT * FROM EX_DATA;

I need to return the following data: the next day the contract expires and the day we have to give them notice. For example, in the first set of data above, the contract expires 2/28/2008, it auto-renews every year, and we have to give them 30 days notice of any change. So, today is 2/8. The next day the contract expires is 2/28/2013. Next month, the date would change to 2/28/2014. That's the first field I need to compute. The second is we have to give them 30 days notice if we want to renegotiate the contract. So, we our deadline for that is 30 days prior to 2/28, or 1/29/2013. Again, after 2/28, that will change to 1/29/2014.

In the last example, since it has a monthly renewal, it expires next on 3/1/2013 (then 4/1, 5/1, so on). With a 30 day notice period, they have to be notified 30 days prior to the next expiration date.

The NOTICE_MONTHS column is just an extra information field that has the month count of the auto-renewal, 12 for yearly and 1 for monthly. This might be helpful in the query.

I've tried several different ways of doing this but have not yet come out with a 100% foolproof result. I was taking the month and day and converting to CHAR then converting that back to DATE which gave me contract expiration for this current month. But when I tried applying other logic, I'd get stuck on an error that resulted from leap year.

Any help would be appreciated.


texts:
Why does my variable @FQN not set accordingly?  Is this a problem with my syntax?  A Problem with using a cursor?  What should I do so that my variable @FQN sets appropriately?  This is what my print statements show, my variable @table outputs properly, but my variable @FQN never changes?

  OneTwoThree
  Select Top 1 * FROM servername.database.dbo.newdatabase
  FourFiveSix
  Select Top 1 * FROM servername.database.dbo.newdatabase
  FourOmegaSix
  Select Top 1 * FROM servername.database.dbo.newdatabase

And here is full syntax:

Declare @table varchar(100), @sql varchar(4000), @FQN varchar(MAX)
Create Table #tablename (tablename varchar(1000))
Insert Into #tablename Values
('OneTwoThree'),
('FourFiveSix'),
('FourOmegaSix')

Declare c1 Cursor For

Select tablename
FROM #tablename

Open c1

Fetch Next From c1 Into @table

While @@FETCH_STATUS = 0
Begin
  if @table = 'OneTwoThree'
    BEGIN
        SET @FQN = 'servername.database.dbo.onetwothree'
    END
  if @table = 'FourFiveSix'
    BEGIN
        SET @FQN = 'servername.database.dbo.fourfivesix'
    END
  if @table != 'OneTwoThree' or @table != 'fourfivesix'
    BEGIN
        SET @FQN = 'servername.database.dbo.newdatabase'
    END
  Print @table

  Set @sql = 'Select Top 1 * '
             +'FROM '+@FQN+' '

  Print @sql

  Fetch Next From c1 Into @table

End

Close c1
Deallocate c1

Drop Table #tablename


texts:
Our users can only access the slave database.

How can I let them change their passwords on their own or when their passwords expire?


texts: What are static tables & dynamic tables?

texts:
I heard the term "static table" when I'am learning about dirty reads in database.

Can anyone explain what is a "static table" and what is a "dynamic table"? Thanks in advance.


texts: Why do I get an error when filtering subSelect?

texts:
select "masters".*, (select avg (rating)
from "reviews" where "master_id" = "masters"."id") as rating
from "masters" where rating > 3

  [42703] ERROR: column "rating" does not exist


texts: Keeping (Partial) Backups Small when using SQL Server FILESTREAM

texts:
I have a database with nearly 1TB of FILESTREAM data that I don't need backed up (if the data was deleted it'd be recreated automatically in a couple of hours, so it just isn't important). Most of the data is changed every couple of days, so differential backups wouldn't really help keep the size down.

I had the backups working the way I needed by setting Recovery Mode to Full, creating a separate FILEGROUP for the FILESTREAM, then taking backups of only the "Primary" FILEGROUP. The problem this caused was that the log file (which also gets backed up) is now unnecessarily large because it includes the FILESTREAM data.

SIMPLE Recovery Mode takes away my ability to do backups of specific FILEGROUPs, so I don't think that will be an option either.

My thoughts are to just move the FILESTREAM data to a separate database, but now I'm losing referential integrity and surely inheriting a host of other issues as well.

Is there any way to create partial backups in Simple recovery mode (without setting the FILESTREAM table to read only)? If not, are there any other sane solutions to my problem?


texts: Unexpected behaviour when got error in the middle of mysqldump backup file import

texts:
i am using mysql 5.5.40 version. i am importing a large .sql file having around 36000 lines using a script. The script contains below statements.

mysql -uxx -p < dumpfile.sql 2 > error.log
cat error.log|mail -s "import error" a@something.com

Sometime after executing the script, I got the mail saying "ERROR 1146 (42S02) at line 35687:Table 'dbname.tablename' doesn't exist. As i am not using force option while importing , it should not execute lines from 35688 to 36000 lines. it should abort when it hit with error. But when i have checked manually, it seems the lines are executed. Is it the expected behavior?

We know its expected behaviour when using source command in mysql command line client.


texts: Database design for a time tracking application

texts: I'm designing the database schema for a time tracking application and I need a little piece of advice. The application must permit the user to enter for each day of the week the amount of time he worked for a specific project. What would be, in your opinion, the best way to store these values?


texts: Failed (exit code: 1) when trying to restore DB from a .tar file in pgAdmin4

texts:
I'm been trying to restore my database and I'm getting an Exit error code 1. The database I'm trying to restore is SuperMart_DB.tar, but every time I get the same error message.

I have seen previous recommendation where it was mentioned to uncheck data option from Restore options.By default data section is in NO state but still I'm getting the same error.

I'm new to SQL and currently going through online course but unable to proceed further. I've also completely rebooted the computer several times since then but still the problem persists. Please help.


texts: SQL Server database snapshots for integration testing

texts:
I am trying to define a way of working with a test database (in SQL Server) for our integration testing.

My idea was to do these steps at the launch of the integration test assembly:

create a totally empty database
run the "create database objects" script to create all relevant database objects (table, views, sequences etc.)
fill the "base data" (lookup values etc.)
take a database snapshot called (db)_Basis as the "base line" for future integration tests

Now before every test class (containing 1-n tests), I was planning to simply do a "restore from snapshot" to get back to the well-defined, more or less "empty" state of the database. Works like a charm so far.

However, there are a set of integration tests that need to operate on a large test database - so I was hoping to do this before each of those test fixtures (classes with n individual tests)

restore database from the (db)_Basis snapshot
insert those 50'000+ rows of data into the database
create another snapshot (db)_With_Testdata snapshot

and then for each test, reset the database to the well-defined (db)_With_Testdata snapshot version, run the tests, verify the outcome and so forth.

Trouble is: I cannot seem to have two db snapshots at the same time - once I do, I cannot restore my database to either of them.... I keep getting this error:

  Msg 3137, Level 16, State 4, Line 9
  Database cannot be reverted. Either the primary or the snapshot names are improperly specified, all other snapshots have not been dropped, or there are missing files.

  Msg 3013, Level 16, State 1, Line 9
  RESTORE DATABASE is terminating abnormally.

Is that really how SQL Server database snapshots work?? Seems awfully restricting..... I would understand if I couldn't go back directly to the original "(db)_Basis" snapshot maybe - but just because I now have two snapshots, I cannot even go back to the most recent one?!?!?


texts: Postgresql 11 SSPI authentication failing

texts:
On windows 2016 in AD setup when I try to use SSPI with postgresql 11 and following settings :
pg_hba.conf:
host    all all 127.0.0.1/32    sspi    map=newmap #IP4

pg_ident.conf:
newmap  SrvSupportPsgr  srvsupportpsgr

log:

Log: no match in usermap "newmap" for user "SrvSupportPsgr" authenticated as "SrvSupportPsgr@domainname"
FATAL: SSPI authentication failed for user "SrvSupportPsgr"

this account has superuser on postgres and local admin + domain user on AD.
I have tried putting usernames in double quotes as well.
thanks


texts:
ALTER TABLE sample62 CHANGE newcol c;

This code makes me mad. I just followed the book I had.

Program said

  You have an error in your SQL syntax; check the manual that corresponds
  to your MySQL server version for the right syntax to use near '' at line 1.

My MySQL version is 5.7. Can you please help me?


texts: Lock pages in memory and Virtual SQL Server

texts: VmWare 2008R2 Enterprise SQL server with Lock Pages in Memory ENABLED.... I have read the Jonathan Kehayias blog post referenced from Brent Ozar's blog via sp_Blitz and still not sure if I should have LPIM enabled. I have no ballooning on virtual server in the past month, max memory in set to 52428 MB out of 64GB, min memory is set to 1024, vCPU 8, host CPU% avg for month 24, vMachine CPU% avg for month 17. Currently ~52GB of pages locked in memory...I should note that 3rd party app has lots of ad hoc queries and lots of heaps and I average ~15K fowarded fetches/sec via sp_BlitzFirst. I rebuild the heaps weekly. FINALLY.. LPIM helping or hurting? What else can I look at?


texts: Is twitter's character limit a way they found to avoid drastic database fragmentation?

texts:
so I just started reading a book about "Mongo schema design" and am currently reading the portion about MMAP and fragmentation.
From what I understood there are different ways to avoid database fragmentation. What I wanted to know, however is if the character limit in platforms like twitter a way they found to counter or solve the issue of database fragmentation and if so what would be another solution that does not involve tweaking the front end?


texts: When does SQL Server determine to use a new index?

texts:
I have been performing some maintenance on a server that runs some software the company bought, so I can't really change how the queries have been written.  I have used some tools to determine some of the missing indexes on the server.  Using my tools, though, I didn't see which queries were indicating these.  I do, however, monitor how much the indexes are being written and read.

So, my question is, when adding new indexes that have been determined as missing, when does SQL Server notice the new indexes and determines whether they actually help the query?  Is it immediately after the index is created and the query is ran again, or is it more complicated?


texts: Super simple mysql query taking too long

texts:
I have the following:

SELECT * FROM autorepair
WHERE zip_code IN (91316,91356,91416,91426,91436)
AND specialty = 'transmission'
ORDER BY ABS(91316 - `zip_code`)
LIMIT 0,10

Indexes:

-zip_code
-specialty

How can I improve the performance on queries like these?

BTW, I'm running this on XAMPP.


texts: How to restrict sequence like ID type to C type like int64_t, uint64_t, int32_t?

texts:
In C99 I can be pretty clear about the size/domain of an integer type.

When interfacing a SQL database like Oracle or Postgresql from a C program I want to create a table with integer types that match the client ones.

For example to match int64_t, uint64_t or uint32_t etc. for a sequence like artificial primary key column.

Just using NUMBER would allow for data that yields an over-/underflow or are not representable in the client language, right?

How to correctly define such a column?

Motivation

Why do you want to such a thing? Because you want to write reliable software. Say you design a database client application and you assume that you only ever need less than 2^32 IDs. Thus, to save space (e.g. with client side arrays etc.) you use uint32_t (or just int). But to be safe the database should enforce that restriction such that your system fails in a well-defined, gracious and noticeable way (e.g. rejecting violating inserts) in case 20 years later some user want to insert tons of IDs or something like that.

Database system

Ideally, the solution should be portable between databases (after all, SQL is an international standard) - but an answer which is Oracle/Postgres specific would be instructive as well - often if you know how to do it on one database system, you can lookup the abstract concept in the documentation of the other one.

Types vs. triggers

It does not have to be a type-only solution - triggers are other mechanism that enforce constraints are fine as well. I just imagined using types could be a convenient way.


texts: Help understanding SQL Server Developer License when users test via webserver

texts:
I'm trying to understand how many licenses I may need if we decide to pursue a SQL Server Developer license for our Testing environment.

We would have 5 actual developer and DBA's physically accessing the database to perform work.

The part where I get confused is our users will be testing our applications from a web server. This webserver will communicate with a 'logic' server that will perform all the database interaction.

At our highest level of testing we probably have 20 users on the system, but depending on the time of year they may be different users.  Example: During fiscal year end we may have 20 people from account on the system, but during benefit enrollment time of year we may have 20 HR/Payroll users testing on the system.

In reading different sets of documentation(Article 1)(Article 2) and speaking to our license reseller I still do not have a clear idea of how many licenses I need.

I think I can purchase 5 for the actual developers/DBA's and one license for each SQL accounts the services uses?

This is the how we are structured currently.  The servers have SQL Server accounts for authentication.


texts: How to implement effective MySQL database backup mechanisms?

texts: MySQL database backup is really critical for my company's business, can anyone share some experience on this?


texts: How do I setup a foreign key relationship based on two columns?

texts:
I have the following two tables:

CREATE TABLE [dbo].[company]
    (
      [CompanyID] [int] NOT NULL ,
      [CompanyName] [varchar](150) NULL ,
      [DepartmentID] [int] NOT NULL ,
      [DepartmentName] [varchar](150) NULL ,
      [ResidentialAddress] [varchar](500) NULL ,
      [ResidentialSuburb] [varchar](150) NULL ,
      [ResidentialPostCode] [varchar](20) NULL ,
      [ResidentialState] [varchar](150) NULL ,
      [ResidentialCountry] [varchar](100) NULL ,
      [PostalAddress] [varchar](500) NULL ,
      [PostalSuburb] [varchar](150) NULL ,
      [PostalPostCode] [varchar](20) NULL ,
      [PostalState] [varchar](150) NULL ,
      [PostalCountry] [varchar](100) NULL ,
      [Phone] [varchar](50) NULL ,
      [IsActive] [bit] NOT NULL ,
      [Created] [datetime] NULL ,
      [CreatedBy] [varchar](50) NULL ,
      [LastModified] [datetime] NULL ,
      [LastModifiedBy] [varchar](50) NULL ,
      CONSTRAINT [PK_department] PRIMARY KEY CLUSTERED ( [DepartmentID] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY]

AND

CREATE TABLE [dbo].[user]
    (
      [UserId] [int] NOT NULL ,
      [CompanyID] [int] NOT NULL ,
      [DepartmentID] [int] NOT NULL ,
      [EmailAddress] [varchar](150) NOT NULL ,
      [Title] [varchar](50) NULL ,
      [FirstName] [varchar](100) NOT NULL ,
      [MiddleName] [varchar](100) NULL ,
      [LastName] [varchar](100) NULL ,
      [DateOfBirth] [date] NULL ,
      [ResidentialAddress] [varchar](500) NULL ,
      [ResidentialSuburb] [varchar](150) NULL ,
      [ResidentialPostCode] [varchar](20) NULL ,
      [ResidentialState] [varchar](150) NULL ,
      [ResidentialCountry] [varchar](100) NULL ,
      [PostalAddress] [varchar](500) NULL ,
      [PostalSuburb] [varchar](150) NULL ,
      [PostalPostCode] [varchar](20) NULL ,
      [PostalState] [varchar](150) NULL ,
      [PostalCountry] [varchar](100) NULL ,
      [Phone] [varchar](50) NULL ,
      [Mobile] [varchar](50) NULL ,
      [BrowserDetails] [varchar](500) NULL ,
      [IsActive] [bit] NOT NULL ,
      [LastLogin] [datetime] NULL ,
      [Created] [datetime] NULL ,
      [CreatedBy] [varchar](50) NULL ,
      [LastModified] [datetime] NULL ,
      [LastModifiedBy] [varchar](50) NULL ,
      CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED ( [UserId] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY]

I would like a foreign key on dbo.user where a company id and department id identifies the correct row on dbo.company. Is something like this possible?


texts: Removing locks in Oracle without being admin

texts:
3 wild sessions each got a row exclusive lock in the same table:

SELECT * FROM DBA_DML_LOCKS;

How can I remove the lock without being admin?

If I was admin I would list (SID, SERIAL#) of the sessions that have the locks:

SELECT SID,SERIAL#
FROM V$SESSION
WHERE SID IN (SELECT SESSION_ID
FROM DBA_DML_LOCKS );

Then execute:

ALTER SYSTEM KILL SESSION 'SID,SERIALl#';

e.g.:

ALTER SYSTEM KILL SESSION '115,1931';

But since I am not admin I get an insufficient privilege error:

ALTER SYSTEM KILL SESSION '115,1931'
Error report -
SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

I am running Oracle 11gR2 11.2.0.3.


texts: selecting rows that have n foreign key conditions satisfied

texts:
I'm trying to filter a master table rows based on 2-more conditions from the relative foreign key relation, i came up with this which is working fine, but i just can't convince my self that it's that much of a fuss

select r.pageid
from
((
select co.pageid
from (cmscontentpublished as co join cmsnode as cm on co.PageID = cm.ID)
where cm.parentid=365 and co.Title='Title' and co.Content like '%test%'
)
union all
(
select co.pageid
from (cmscontentpublished as co join cmsnode as cm on co.PageID = cm.ID)
where cm.parentid=365 and year(co.DateContent) =2012
)) as r
group by r.pageid
having COUNT(*)=2

any ideas on how to do correctly


texts: is it safe to compress backups for databases with TDE enabled?

texts:
I've been reading about this for a long time, and it seems it's not safe to compress a backup when the database has TDE enabled.
is anyone experiencing errors during restore with compressed backups for tde databases?
I've never experienced these kind of errors but i'm really worried about it.
It seems the problem is with SQL SERVER 2016, and as I'm using 2012, I can't see any problems.
Also, even using MAXTRANSFERSIZE = 131072 the backup is not compressed. what could It be? I'm using NO CHECKSUM and WITH CHECKSUM both with the same size.


texts: Recursive CTE throws temp tablespace is empty error

texts:
I need to aggregate the contents of multiple rows into a row as a delimited text. Here's the simplified table with sample data which represents what i want to do.

CREATE TABLE SPD_OWNER.EMP (
    EMPID NUMBER,
    NAME VARCHAR2(20),
    MGRID NUMBER,
    DEPT VARCHAR2(5)
);

INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(1, 'GPM', NULL, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(2, 'SPM', 1, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(3, 'PM', 2, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(4, 'Dev1', 3, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(5, 'Dev2', 4, 'IT');

-- query which has issue
WITH tmp (rnum,dept,id) AS
    (SELECT rownum rnum, dept, to_char(empid) AS id FROM (SELECT dept, empid FROM emp WHERE dept='IT' ORDER BY empid)),
cte (rnum,dept,id) AS
    (SELECT rnum, dept, id FROM tmp WHERE rnum=1
        UNION ALL
    SELECT cte.rnum+1, tmp.dept, cte.id||'/'||tmp.id FROM cte JOIN tmp on tmp.rnum=cte.rnum+1)
SELECT * FROM cte cte1
WHERE rnum=(SELECT max(rnum) FROM cte cte2 WHERE cte1.dept=cte2.dept);

Am expecting below output.

5 IT 1/2/3/4/5

But running above query gives me an error SQL Error [25153] [99999]: ORA-25153: Temporary Tablespace is Empty

Isn't the condition tmp.rnum=cte.rnum+1 sufficient to break the recursive loop? Here's what am imagining to be happening.

1st select (above union all) will select rnum=1
1st recursive iteration will select tmp.rnum=cte.rnum+1, i.e.
tmp.rnum=2
2nd recursive iteration will select tmp.rnum=3
and so on till this condition is invalid, i.e. when cte.rnum=5 (because then tmp.rum=5+1 will not have matching record)

Am assuming that am getting the error since it's going into infinite loop.
What am i missing?

Side Note: Am using Oracle DB, so i can easily do this using LISTAGG. But i also want to run this query in H2 DB. So i want to have a solution that uses standard sql. Am open to other ways to achieve the same result.


texts: Postgres Partition Table Trigger inserting duplicate records

texts:
I have a trigger applied on my database table. But when i insert data into it via hibernate jpa it creates duplicate rows in master table. Here is the trigger

CREATE OR REPLACE FUNCTION SMS_RECEIPT_func_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.timedate >=  '2015-01-01' AND NEW.timedate <  '2015-01-31' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m01 VALUES (NEW.*);

    ELSIF ( NEW.timedate >=  '2015-02-01' AND NEW.timedate <  '2015-02-28' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m02 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-03-01' AND NEW.timedate <  '2015-03-31' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m03 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-04-01' AND NEW.timedate <  '2015-04-30' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m04 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-05-01' AND NEW.timedate <  '2015-05-31' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m05 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-06-01' AND NEW.timedate <  '2015-06-30' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m06 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-07-01' AND NEW.timedate <  '2015-07-31' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m07 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-08-01' AND NEW.timedate <  '2015-08-31' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m08 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-09-01' AND NEW.timedate <  '2015-09-30' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m09 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-10-01' AND NEW.timedate <  '2015-10-31' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m010 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER trigger_SMS_RECEIPT_insert
    AFTER INSERT ON "SMS_RECEIPT"
    FOR EACH ROW EXECUTE PROCEDURE SMS_RECEIPT_func_insert_trigger();

If use Before in place of AFTER it gives the following error in java

  CREATE TRIGGER trigger_SMS_RECEIPT_insert
  BEFORE INSERT ON "SMS_RECEIPT"
  FOR EACH ROW EXECUTE PROCEDURE SMS_RECEIPT_func_insert_trigger();

Caused by: org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1

I also tried using INSTEAD OF inplace of AFTER and before but its giving

Tables cannot have INSTEAD OF triggers.
I am new to table partitioning and using triggers. I have searched for all this in google and also gone through the post here: Insert trigger ends up inserting duplicate rows in partitioned table

But it didn't work for me.

Can anyone tell me how to resolve this.


texts: How to match a version of SQL Server 2008?

texts:
It is needed to install a new instance of SQL to the existing Windows SQL Server 2008. I have no idea what was used to originally install the existing 4 instances. Is it R5 SP32? Thanks to MS wonderful versioning system nobody knows just by looking at Add/Remove.

The version in Add/Remove is listed as 10.1.2531.0 for all the SQL components (funnily enough, all except the main "MS SQL Server 2008 (64-bit)". When I click "modify" under there it asks where is the original installation dir? I would be happy to know too.

How to find "just the right" installer, in order to avoid version hell, upgrading the existing instances (this is production) and other mess?


texts:
I working on a medium sized PostgreSQL database with some schemas, each with some tables.

In almost every schema there are tables that represent event tables, where something that has "happened to" or was "caused by" a user is inserted. Essentially making each row in those tables an "Event".

Since I need users (and not necessarily the inserting user) to be able to find Events that have happened since they checked last, I am looking for some way of taking rows inserted into those tables (different table name and schemas) and adding them into an "Events" table.

The Events table will have a UUID as PK, and a UUID indicating what user "owns" (caused) the event to happen. Then comes the information from the table representing the "Event".

My original plan was to have a INSERT trigger, for the tables representing the events, that inserted a new row in the Events table with the original insert.

Of course I would also need some kind of UPDATE trigger so that any updates made to the original row (in the original table) is also made to the row in the Events table, but this could just as well be a deletion of the original row (in the Events table) and then insertion of the new row.

The question is two-fold:

Is there a better way for me to have a "Events since last checked" function? (Without implementing a horrific amount of booleans and checks and timestamps.)

What is the best way to insert the necessary information from the separate tables, with different names, layouts and schemas, into the Events table?


texts: MySQL Performance schema : CURRENT_NUMBER_OF_BYTES_USED high for disconnected user

texts:
I'm investigating memory usage on one of my MySQL servers. I can see a continually increasing memory usage according to the stats in performance_schema when looking at the data in the memory_summary_by_ tables which doesn't seem to match up with expected usage.
One example is a user which is not currently connected but still has 20GB of CURRENT_NUMBER_OF_BYTES_USED for the memory/innodb/mem0mem event and the value has stayed at that level for over 5 days now.
How long is the data held in Performance Schema for user/host/account data? Or is there really something holding 20GB in memory that need investigating?
MySQL 5.7.30


texts: Can MongoDB manage balancing between the Mongos Dynamically using TCP Balancer

texts:
If I have a business where I maintain 10 applications and I need to divide the request load of these 10 applications between 3 Mongos Dynamically.

Like the TCP Loadbalancer need to connect to MongoDB config servers and taking the Health status of 3 Mongos constantly and distributing the request load from application depending on the health state(getting to whether a particular Mongos is busy or free).

Is anything like the above can be achieved using TCP Balancer? or Its just can be used to configure the load static.

Please, confirm.


texts: Notification for host failure and health status

texts:
I set up a MongoDB cluster with replica sets.
I wish to have something like a dashboard or a "health status". My current solution is to connect to the cluster and query the replication state once in a while.
How can I find out that a host failed or get the health status of the cluster?
I already searched for solutions and found solutions like this:
Percona Monitoring and Management but it considers performance metrics rather than failover related information.


texts: Does the order of a mysql multi-column MySQL index matter - if you're always using all columns in the query?

texts:
I'm aware that when you define a MySQL index that the order of the fields matters if the query is only using part of the index, as described here. But if your query is always going to use all columns defined in the index, then does index column order still not matter?

For example, consider a table with these columns:

name
gender
age

Any my query of:

SELECT name WHERE gender='M' and age<18

Will there be a difference in performance if I define the index as (gender, age) vs (age, gender) ? Especially considering there will be one field with more unique values than the other.

I've read some tips on Percona's blog here that indicate you should order your index columns on the most selective columns being first, but that's the only place I've heard that.

Anyone know? I'm using the innodb table type.

BONUS: Same question if your index is the primary key. Does it affect the answer?

Thanks so much for your input!


texts: MongoDB update multiple sub documents inside array, each according to a condition

texts:
I have a document like this:

{
    wid: 1,
    items: [
        {nid: 1, sent: 900},
        {nid: 2, sent: 12},
        {nid: 3, sent: 0},
        {nid: 4, sent: 1}
    ]
}

Now consider I want to update this document with an object I compute from somewhere else:

# All for wid:1, a mapping of nid:sent
# These values should be added to values already in the DB
{
    2: 37,
    3: 58,
    4: 172
}

so after the update, I want to have something like:

{
    wid: 1,
    items: [
        {nid: 1, sent: 900},
        {nid: 2, sent: 49},
        {nid: 3, sent: 58},
        {nid: 4, sent: 173}
    ]
}

Is there anyway to do this with only one update query?

What I know works->

db.test.update_one({wid: 1, 'items.nid': 2}, {$inc: {'items.$.sent': 37}})
db.test.update_one({wid: 1, 'items.nid': 3}, {$inc: {'items.$.sent': 37}})
db.test.update_one({wid: 1, 'items.nid': 4}, {$inc: {'items.$.sent': 172}})

But that's O(n) update queries issued and this is really contradicting all reasons I chose to stick to MongoDB.

Thanks!


texts:
At T1, insert comes for table_1, goes to disk block_1.
At T2, insert comes for table_2, can it go to same disk block_1(provided block_1 has space) or new block will be created per table ?
At T3, again insert comes for table_1, can it go either block_1 or block_2 based on available space ?


texts: Progress of mongodump operation jams at a random point

texts:
We are executing remote mongodumps to backup collections. For some time now sporadically one of these dumps stops to progress any further and remains to log out the same amount of documents processed over and over:

2016-06-27T06:35:06.428+0000    [##############.......]  col  1140198/1853120  (61.5%)
2016-06-27T06:35:09.428+0000    [##############.......]  col  1140198/1853120  (61.5%)
2016-06-27T06:35:12.428+0000    [##############.......]  col  1140198/1853120  (61.5%)

This time the dump is stuck at this point for about four hours and more than 5000 log lines showing 61.5% of progress.

I can still access the MongoDB Server from the backup machine, mongodb answers to queries, no annomalies in the logs.

MongoDB version 2.6.12


texts: How to find consecutive non zero values from a column

texts:
I have a table as follows
user  timestamp            counts
xyz   01-01-2020 00:05:00   12
xyz   01-01-2020 00:10:00   11
xyz   01-01-2020 00:15:00   45
xyz   01-01-2020 00:20:00   0
xyz   01-01-2020 00:25:00   0
xyz   01-01-2020 00:30:00   13
xyz   01-01-2020 00:35:00   12
xyz   01-01-2020 00:40:00   0

I want to slice it based on max consecutive values(non-zero) values in count column and find out start timestamp, end timestamp and total_count in that duration
starttime            endtime               total_count
01-01-2020 00:05:00  01-01-2020 00:15:00   68

How can I achieve this using BigQuery


texts: What is "Init for Update" thread state in mariadb?

texts:
Our production server gets stuck on Init for update state whenever we start a query like

update
    <some_big_table>
set
    <primary_key> = <some_sequence>.nextval
order by
    <some_indexed_field>

While this the query is stuck are this state, all other queries get stuck at commit or writing to binlog state.

I couldn't find any relevant documentation for the same either.


texts: Can I get notification when event occur?

texts:
SQL Servers has Traces and XEvents. These are used to capture and analyze what is going on with our SQL Server Instances. Events are stored in the stack for later analysis. For example,
If I decide to monitor any dead lock in the database, I just query the trace file to see the history of deadlock for a period of time. Here is my question:

While events occur, in our example deadlock event, is there a way to get an email notification using msdb.dbo.xp_send_dbmail?


texts: Does using Activity Monitor have an effect on server performance?

texts:
Our SQL Server VLDB is being monitored by Activity Monitor on a networked machine.
95% of all traced statements can be attributed to this tool, but is it known to have a negative effect on server/network?


texts:
I have a remote MySQL server running on x.x.x.x. And there is a database named abc.

I can do the following

mysql -h x.x.x.x -uroot
use abc

But when I try to

mysql -h x.x.x.x -uroot --database=abc

I get

ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'abc'

Why is that? And how do I fix it?

edit:

I have now resolved the issue by doing the following:

ssh in the box
run mysql -uroot
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

But I still don't understand why.

mysql> SELECT USER(),CURRENT_USER();

+-----------------------------+----------------+
| USER()                      | CURRENT_USER() |
+-----------------------------+----------------+
| root@Maxs-MacBook-Air.local | root@%         |
+-----------------------------+----------------+

mysql> SHOW GRANTS FOR root@'%';

+------------------------------------------------------------------+
| Grants for root@%                                                |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'%'                                 |
| GRANT ALL PRIVILEGES ON `mydb`.* TO 'root'@'%' WITH GRANT OPTION |
+------------------------------------------------------------------+

mysql> SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) from mysql.user WHERE user='root';
ERROR 1142 (42000): SELECT command denied to user 'root'@'Maxs-MacBook-Air.local' for table 'user'

Max


texts:
In the below code (that I've inherited), I'm getting the message "SQL80001 - incorrect syntax near ')'", the error being on the third line from the bottom with the + sign. I've looked at THIS SO answer to similar issues, but that wasn't the issue here.

select
b.batch_id,
b.submitted_dt,
b.batch_nm,
b.paused_cd,
b.job_cnt,
b.start_dt,
b.finish_dt,
m.module_nm,
m.module_id,
mc.hostname
from
    tbl_bat_chess b,
    tbl_job_bobs j,
    tbl_man_holes m,
    tbl_monkey_cokes mc
where
b.finish_dt is null
and  to_char(b.submitted_dt, 'DD-MON-YYYY') = to_char(sysdate, 'DD-MON-YYYY')
and  (to_number(sysdate - b.submitted_dt) * 1440)  >  5 -- 5 minutes
and b.batch_id = j.batch_id
and b.batch_nm like '%EXAMPLE%'
and j.module_id = m.module_id
and m.proc_group_id not in (10, 18)
and j.machine_id = mc.machine_id (+)
group by b.batch_id, b.submitted_dt, b.batch_nm, b.paused_cd, b.job_cnt, b.start_dt, b.finish_dt, m.module_nm, m.module_id, mc.hostname
order by submitted_dt desc

DBMS: PLSQL Developer 10.05.

Oracle Version: 11g

I've manually removed all non-breaking spaces and tried putting square brackets around the line mc.machine_id with no success. How should this be resolved?


texts: I understand that tablespace is a logical collection of tables and database objects. It could be made up of multiple data files (which is the physical collection of objects). Apart from this, what is the aparent advantages that tablespaces provide? Anyone care to share  use case(s) of how tablespace/data files can help solve problems.


texts: Relocate all files to folder checkbox unavailable sql server 2012

texts:
Using SQL 2012 Enterprise edition

Following instructions here for restoring a backup:
http://msdn.microsoft.com/en-us/library/ms186390.aspx#Restrictions

I need to be able to rename the database because it is the second copy on the instance (testing purposes). But I cannot rename it nor change the folders that it will restore the MDF/LDF files to.

For whatever reason the "Relocate all files to folder" checkbox is not available.

Restoring form a SQL 2005 backup.


texts: Update sys.job$ set this_date=:1 where job=:2 as one of the most time-consuming queries - oracle

texts:
I am currently helping some organization with their Oracle database performance. They are using Oracle 11gR2 Standard Edition in Windows 2008. As  there are no enterprise performance packages available on this standard edition, I've had to use v$sql to query longest-running ones to identify performance bottlenecks. Using the query below :

SELECT *
FROM   (
              SELECT sql_text,
                     sql_id,
                     elapsed_time,
                     plsql_exec_time,
                     cpu_time,
                     executions,
                     fetches,
                     loads,
                     buffer_gets,
                     elapsed_time / executions
              FROM   v$sql -- order by elapsed_time / executions)         )  where rownum <= 10;

It returned some queries which I will look into, but there are two rows in the results that I cannot understand the reason why they should exist here:

As you can see, the query executes on sys.job$, so it should be related to oracle jobs set on this database. My questions are :

1) What does these two rows mean? Should I query dba_jobs table for more information? How should I query them and what should I look for?

2) Both of the queries look identical, why they are echoed twice in the query result?


texts: Implement a deduplication trigger in Oracle

texts:
I am new in Oracle DB. We are using Toad for Oracle. I need to implement a deduplication trigger for our table. It's an alarms table and a third party vendor will be sending alarms to our database. Basically, it's a catch-all alarms. Duplicates are those with the same IP, alarm remarks and severity.

Sample data:

INSERT INTO t_alarms (IP, remarks, severity) VALUES ('10.10.10.10', 'VOICESERVER IS DOWN', '5')
INSERT INTO t_alarms (IP, remarks, severity) VALUES ('10.10.10.10', 'VOICESERVER IS DOWN', '5')
INSERT INTO t_alarms (IP, remarks, severity) VALUES ('10.10.10.19', 'VOICESERVER IS DOWN', '5')
INSERT INTO t_alarms (IP, remarks, severity) VALUES ('10.10.10.19', 'VOICESERVER IS DOWN', '5')

And then my table looks like this:

IP          REMARKS               SEVERITY TALLY FIRSTOCCUR         LASTOCCUR
10.10.10.10 'VOICESERVER IS DOWN' 5        2     2/21/2014 01:00:03 2/22/2014 02:00:03
10.10.10.19 'VOICESERVER IS DOWN' 5        2     2/21/2014 06:00:03 2/22/2014 06:30:03

where:
Tally is the count of how many times my alarms happen
FirstOccur is the date/time when it first inserted/happen
LastOccur is the date/time when it last occur

Is there a way or a script to implement this dedupe process through triggers?


texts: Window Functions: What is the purpose of Rows Unbounded Preceeding?

texts:
What is the purpose of using the Rows Unbounded Preceeding clause in a window function?
I think I understand that it's basically saying not to limit how far back to look when rolling up for the aggregate function, but how is that different than not using the clause at all?

Could you please provide an example to illustrate the difference between:

SUM(ColumnA) OVER (PARTITION BY ColumnB ORDER BY ColumnC DESC ROWS UNBOUNDED PRECEEDING)

And

SUM(ColumnA) OVER (PARTITION BY ColumnB ORDER BY ColumnC DESC)

Note: My question is in the context with no cap on the Rows Following.


texts: In a data warehouse, should a measure be based on a fact or a dimension?

texts:
Let's say there is a data warehouse created from a shop data. A fact is a single purchase of a product. There is a dimension that describes a customer.

There is a need to create a measure that stores a number of distinct customers. Can this measure be created based on customer identifier in the dimension table, or it needs to be fact table? In which cases one or the other solution is better?

Below I post a visualization based on an AdventureWorks2016 database:


texts: Sorting multiple columns in a full outer join, possibly containing NULLs

texts:
I am trying to compare a plan against a history to see if there are steps omitted or if unplanned steps have been included. The regular, planned history items as well as the anomalies are intended for an elaborate report. The problem is, I have a column in each joined table that I need for sorting, but due to the full outer join I cannot guarantee them to be not null, which breaks sorting. I'll continue with a simplified example, where the plan corresponds to a recipe:

Table: RecipeSteps

 Id | RecipeId | Position | Message
----+----------+----------+----------------------------
 50 |      123 |        1 | Wash vegetables
 51 |      123 |        2 | Peel vegetables
 52 |      123 |        3 | Cut meat
 53 |      123 |        4 | Turn on stove
 54 |      123 |        5 | Cook and stir it
 55 |      123 |        6 | Turn off stove
----+----------+----------+----------------------------

... and the history to the actual steps while cooking:

Table: History

 Id | TimeStamp | Session | StepId | Message
----+-----------+---------+--------+-----------------------------
 90 |  00:01:00 |     321 |   NULL | Chef entered kitchen
 91 |  00:02:00 |     321 |     51 | Chef peeled vegetables
 92 |  00:03:00 |     321 |     52 | Chef cut meat
 93 |  00:04:00 |     321 |   NULL | Chef picked his nose
 94 |  00:05:00 |     321 |     53 | Chef turned on stove
 95 |  00:06:00 |     321 |     54 | Chef started cooking
 96 |  00:10:00 |     321 |   NULL | Chef left kitchen
 97 |  01:00:00 |     321 |   NULL | FIRE!
----+-----------+---------+--------+-----------------------------

Now, I want to compare these tables in a result table with a full outer join similar to this:

Table: Result

 Sorting | TimeStamp | Position | Message                | Comment
 --------+-----------+----------+------------------------+---------------
       1 |  00:01:00 |     NULL | Chef entered kitchen   | unplanned
       2 |      NULL |        1 | Wash vegetables        | omitted
       3 |  00:02:00 |        2 | Chef peeled vegetables | planned
       4 |  00:03:00 |        3 | Chef cut meat          | planned
       5 |  00:04:00 |     NULL | Chef picked his nose   | unplanned
       6 |  00:05:00 |        4 | Chef turned on stove   | planned
       7 |  00:06:00 |        5 | Chef started cooking   | planned
       8 |      NULL |        6 | Turn off stove         | omitted
       9 |  00:10:00 |     NULL | Chef left kitchen      | unplanned
      10 |  01:00:00 |     NULL | FIRE!                  | unplanned
 --------+-----------+----------+------------------------+---------------

Then the reader of the report sees:

Entering/leaving the kitchen is not part of the recipe, but it has been recorded anyway.
The chef forgot to wash the vegetables (Ugh!) or the step has not been recorded for some reason (Phew!).
History continues as planned, with the exception of the chef picking his nose in between (double-Ugh!)
The chef forgot to turn off the stove, which caused the fire, in the end.

I came up with the following SELECT statement, selecting from this query with ORDER BY Sorting ASC, but I still have a problem to fill the Sorting criterion:

SELECT
  0 Sorting, -- this has to be replaced
  h.TimeStamp,
  r.Position,
  '...' Message, -- case-when-construct, not important for the question
  '...' Comment -- case-when-construct, not important for the question
FROM RecipeSteps r
FULL OUTER JOIN History h
ON h.StepId = r.Id
WHERE (r.RecipeId = 123 OR r.RecipeId IS NULL)
AND (h.Session = 321 OR h.Session IS NULL)

There is a natural order in the history given by the timestamps and one in the recipe given by the Position column. As they are never both null simultaneously, I could create the Sorting column based on these, but I couldn't figure out, so far, how to achieve this.

I know I can sort both separately, enumerate and compare both sequences etc. programmatically, but I'm curious if this is also possible with non-procedural SQL.


texts: Best way to handle this type of constraint

texts:
Guidance please.  We have a small configuration table where we need to ensure only 1 record has bit checked.  In the past, we have done check constraint with function that checks count, which we are told causes too much overhead.  But someone is suggesting unique index with where clause.  I want to make sure I understand all of the pros and cons.  Or is there a different better way to achieve this?  This is for in house applications.  Some people have also said to not bother putting anything in database, just make sure the application prevents it.

Edit: The column is a bit column that will not allow nulls.  The table itself should be < 50 rows total.  But only 1 can have this particular column marked true.


texts: MySQL Cluster - use subnet in config.ini for [MYSQLD] connections?

texts:
In MySQL Cluster (7.3), is there any way to lock down the [mysqld] section in config.ini to a small subnet, rather than setting it to a single IP Address, or open for any IP Address?

I've searched the documentation and the web, and I can't see anything that says Yes or No specifically.

I tried it on my test cluster (all running on my Win 7 PC), and trying 123.123.123.* didn't work, nor did 123.123.123.0 255.255.255.0.

It just seems to be a security risk leaving it open like that, but defeats the point of adding 'spare' slots if they have to be pre-allocated to a fixed IP address.


texts:
I am installing SQL Server 2014, including SSIS, on a Windows Server 2012 R2 machine.

From this link:
https://msdn.microsoft.com/en-us/library/ms137861(v=sql.120).aspx
ssis uses port 135 which should be opened on the firewall(s).

I Create a domain account to run SSIS

Install SQL - features can be seen on the picture below

All services are running fine
see a special ad account to run the ssis services

Grant the logon as a service and lock pages in memory

On control panel -> administrative tools-> Computer management

Add the users and the ssis account to “distributed com users”

On control panel -> Administrative Tools > Component Services

Then go to computer-> my computer -> DCOM Config -> and look for Microsoft
SQL Server Integration Services 12.0

As you can see on the picture below

You right click on Microsoft SQL Server Integration Services 12.0

I have left the Authentication Level as “call”.
This is more detailed explained if you follow this link.

These 3 above have to be set up, I have done, and the results are below:

Doing all this above,
When I run SSMS “As Administrator” on the Server itself, then it works fine.

AFTER I REBOOTED THE SERVER I CAN CONNECT SO THE MESSAGE BELOW IS FIXED

  But on the Server itself if I run SSMS as myself, I get the following
  error message:

  Connecting to the Integration Services service on the computer
  "sqldw01" failed with the following error: "Access is denied."

  By default, only administrators have access to the Integration
  Services service. On Windows Vista and later, the process must be
  running with administrative privileges in order to connect to the
  Integration Services service. See the help topic for information on
  how to configure access to the service.

I am administrator on the box.

and when I try to connect from my local machine, then I get the following error:

I CANNOT CONNECT USING SQL SERVER 2012 OR LOWER, BUT I CAN CONNECT FINE USING SQL SERVER 2014

Connecting to the Integration Services service on the computer "sqldw01" failed with the following error:

  "Class not registered". This error can occur when you try to connect
  to a SQL Server 2005 Integration Services service from the current
  version of the SQL Server tools. Instead, add folders to the service
  configuration file to let the local Integration Services service
  manage packages on the SQL Server 2005 instance.

What am I missing?

The problem that remains solving is
How to connect to SSIS 2014 from a client using SQL Server 2012?


texts: MariaDB multiple auth modules for a user?

texts:
MariaDB has pluggable authentication, like PostgreSQL. Unlike PostgreSQL, I can't figure out how to support two forms of auth for a user. For instance, in PostgreSQL we can have the following in our pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             all                     md5
local   all             all                                     peer

This permits

all users to connect using password auth
all users that are local to connect using the peer method

How do I make it so all local users can connect over Unix sockets if they're authenticated with the operating system, and with a password in the event one is set in the database?

MariaDB provides

mysql_native_password
unix_socket

It seems the mysql.user only supports one "plugin"

MariaDB [mysql]> SELECT user,host,plugin FROM user;
+------+-----------+-------------+
| user | host      | plugin      |
+------+-----------+-------------+
| root | localhost | unix_socket |
+------+-----------+-------------+
1 row in set (0.00 sec)


texts: Error on re-import: #1231 - Variable 'character_set_client' can't be set to the value of 'NULL'

texts:
Using phpMyAdmin, I was getting an error when importing a MySQL dump file to which I'd made a simple global find-and-replace:

Error
SQL query:

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */
MySQL said:

#1231 - Variable 'character_set_client' can't be set to the value of 'NULL'

The strange thing is I had just exported the dump, and this is all I did.

So then I tried exporting a .zip again, and re-importing without making any changes. And I got the same error.

I was able to import the file, finally, by deleting these lines from the dump:

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

But I'd like the export/import process to just work.

So my question is: How can I prevent this from happening with future imports, in a way that doesn't require looking up this issue again?

Note: I'm on a shared server setup; I don't currently have SSH access.


texts: Reached maximum possible value for auto_increment function set on column

texts:
I'm using a MySQL database. I have set an auto_increment function on "ID" column (which is also set as Primary key).

What will happen when it reaches its maximum possible value, according to the data type set for that column?

Can someone help me out what to do in such cases? Will it be handled automatically? Or will I have to handle it?


texts: Size and performance different on UPDATE with/out intermediate CTE

texts:
I've got two tables with an indexes id column in common. I'm trying to add a new column in the target table using the value of a column in the source table. The two tables are rather large with couple dozen columns or more.

The thing I don't understand is why the two update queries I've tried perform so wildly different.

This update takes more than an hour before failing due to lack of space (gobbled up +120gb of storage)

UPDATE target
SET created_at = src.created_at
FROM base_table src
WHERE src.id = target.id

While the following took 5 minutes and did not make a dent in space:

WITH subtable AS (
  SELECT
      id,
      created_at
  FROM base_table
)
UPDATE target
SET created_at = subtable.created_at
FROM subtable
WHERE target.id = subtable.id

Is creating an intermediate table with only two columns mean that the entire operation is executed in memory and that's why the two perform so differently? If that's the case I would've expected the optimizer to address this no?

What's happening?


texts: How to create entity-relationship diagram for tables that can potentially be united by an "IS A" relationship?

texts:
I have this schema:

create table hall(
  building-id int,
  hall-id int,
  capacity int,
  primary key(building-id, hall-id)
);

create table semester(
  sem-id char(5),
  start-date date,
  eend-date date,
  primary key(sem-id)
);

create table weekly-event(
  weid int,
  course-id numeric(7,0),
  sem-id char(5),
  weekday varchar(10),
  start-time time,
  duration interval,
  registered int,
  primary key(weid),
  foreign key(sem-id) references semester
);

create table one-time-event(
  oeid int,
  event-name varchar(50),
  end-date date,
  start-time time,
  duration interval,
  registered int,
  primary key(oeid)
);

create table timetable(
  start-date date,
  start-time time,
  hall-id int,
  building-id int,
  event-id int,
  duration interval,
  primary key(start-date, start-time, hall-id, building-id, event-id),
  foreign key(hall-id, building-id) references hall
);

where either events that occur on a weekly basis or one-time events are scheduled on some campus. I want to draw entity-relationship diagram (ERD) for this schema and first of all I don't have much experience in drawing ERDs.

Also, I'm wondering if I should use some kind of "IS A" relationship for the weekly and one-time events and unite some attribute under event table which would have children which would contain only the attributes specific to them.

This is the ERD I have so far:

As far as I see it, timetable is dependent on other tables that's why I chose rhombuses with double lines. I also think that the relationship between timetable and hall and weekly-events is one-to-many while the relationship between timetable and one-time-event is one-to-one.

Because weekly-event references semester it has total participation.
Also logical timetable cannot exist without at least one hall, one-time-event or weekly-event that's why it also has total participation in those.


texts: Preparing a former master to run as slave again

texts:
I have a streaming replication setup using postgresql 9.3. I have two servers, the master (server_1) and the slave (server_2).

The other day I did a failover to my slave (server_2). Now server_2 has been running as master for a couple of days, and I have now setup my primary master (server_1) again, and it now runs as master. I would like to avoid reinstalling server_2 before turning it into a slave of server_1 again. How can I do that? What dir's should I delete etc, in order to have a "clean" server that I can use as slave again?


texts: How can I Insert when not in table and when max timestamp T-SQL

texts:
I am trying to insert rows from one table to another that are not in the one I am moving them to. I also want to only move the ones that have the highest datestamp. (I want to only insert rows that are not in tb1 and have the max timestamp)

This is what I have so far:

INSERT INTO [db].[dbo].[tb1]
SELECT *
FROM tb2 WHERE(dbo.tb2.STime = (SELECT MAX(STime) FROM dbo.tb2))
  AND (EMPNO NOT IN (SELECT EMPNO FROM [db].[dbo].[tb1]));

I get this error when I execute:

  Msg 147, Level 15, State 1
  An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.


texts: Host Name Resolution in mysql.db and mysql.user tables

texts:
We're seeing something peculiar about the way MySQL does name resolution -- or peculiar to us at least (-:

We have two data centers, A and B. A is primary, B is used in case of failover. We have two MySQL servers in A, db1 and db2, sitting behind a load balancer in active(db1)-passive(db2) configuration. The MySQL "service" is accessed as db.domain.com, which is c-named to the load balancer, lb.domain.com.

When looking at the mysql.db table (and similarly in mysql.user), we see entries like:

Host                    Db                User
lb.domain.com         app1              user1
lb.domain.com         app2              user2
lb.domain.com         app3              user3

How does one get db.domain.com in the Host column instead of the lb.domain.com?


texts: Production database substantially bigger than Dev

texts:
I have a Production database and a Development database on two different servers. Both databases are being fed from the same data warehouse on a staging server. There are a series of automated SQL jobs that run and populate tables in a database. The jobs were copied from Dev into Prod so they should also be the same. However, Prod is growing much faster than Dev.

There are approximately 1 billion rows of data in the database. Total data files in Prod are about 123GB larger. Total index files are about 31GB larger. I'm new to this, but I would expect the two databases to be fairly similar in size. If anything I was expect Dev to have some extra 'junk' and potentially be the bigger database.

Any ideas how to find the source of this size difference? I can increase the disk space on Prod if needed, but indicates to me that there may be an issue that needs to be addressed. I'd like to recoup the 153GB if possible.

I'm admittedly a rookie when it comes to this, but I checked the Index folder in each environment and they both appear to have one Index. I checked properties and they look the same as well. Would more Index also cause an increase in size in the data files?

I'm running the IndexOptimize script from Ola Hallengren in both Prod and Dev and hoping that is adequately handling any significant fragmenting. I haven't actually migrated any data from Dev to Prod. We have a staging server that hosts the data. A set of SSIS and stored procedures moves the data from staging to databases on Dev. More SSIS jobs and stores procedures populated the tables in Dev. The SSIS and stored procedures are promoted from Dev to Prod and they run on Prod independently. The Prod jobs access the same staging server as Dev.


texts: replace all references to a drive inside the jobs to another drive

texts:
I have a server where all my sql server backups are currently being saved to the G: drive.

Now there is no enough disk space on drive G: to keep backups for the last 5 days.
I have asked to add another 100 GB to this G: drive but that was not possible, so they gave me another drive instead, the I: drive with extra disk space, as you can see on the picture below.

Now I need to replace all the references to the G: drive in the sql server jobs below to point to the new I: drive.

Suppose I have 3 servers in the same situation, how can I achieve this by T-SQL?


texts:
Recently I found some old questions that involve querying data from AdevntureWorksDB across several operations(DML,DDL).
One such question was :
Create and execute a query that computes the total number of employees with the
Addressline2 value as NULL
Now the query for this, is very simple as it appears but the challenge for me was to find tables whom I could join and retrieve data.
Employee details were in Employee table and Address details were in Address table.
And no keys to join both these tables.
Now ,since I can be sure that these questions were created many years ago, my question here is whether there existed a table called EmployeeAddress that served the purpose of a join table between the base tables Employee and Address.
I started out with SQL hardly 3 years back. So any of you experienced people here, can you please help me on this.
I did a bit of research and found the following:
Microsoft website labels this as a conceptual table that's deprecated.
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms124671%28v%3dsql.100%29
Saw the same table on another website too.
http://www.sqledit.com/sr/samples/frames2/adventureworks.html
I recently downloaded the oldest available backup file for SQL server ,i.e. 2008R2,but still fail to see this table.
Do I need to change any compatibility settings in SSMS to view this table in 2008R2?
Also, if someone can tell me a source for the version of AdventureWorks that has this table, I would be really glad.


texts:
i need to get the members roles for specific users id

example return for the user id 4 the members roles
e.g id 4 peter father des brother

INSERT INTO `testdb`.`users` (`id`, `name`,`year`,`rank`) VALUES (4,"jef", 2006,1);
INSERT INTO `testdb`.`members` (`id`, `first_name`,`last_name`,`gender`) VALUES (1,"peter","zex",1);
INSERT INTO `testdb`.`members` (`id`, `first_name`,`last_name`,`gender`) VALUES (2,"des","zex",1);
INSERT INTO `testdb`.`member_roles` (`member_id`, `user_id`,`role`) VALUES (1,4,"father");
INSERT INTO `testdb`.`member_roles` (`member_id`, `user_id`,`role`) VALUES (2,4,"brother");


texts: How does MySQL still function even though I move .ibd file to arbitrary places?

texts:
I have a table Person. AFAIU, the data for this table is stored in /usr/local/mysql/data/my_database/person.ibd.

Now, I make an insert. Then I check out the contents of person.ibd with vim. The data I have just entered is indeed there.

Then, I move the person.ibd to my home directory. I again make an insert and check out the contents of person.ibd with vim. The new data is inserted into the file even though the file is now in my home directory, instead of being at /usr/local/mysql/data/my_database/person.ibd.

I repeat this action by moving the person.ibd to various other places, such as to /etc/. The new data is always inserted.

So, what's going on? How does this happen? Does MySQL search the whole file system before writing the insert to a file in the file system?

Furthermore:

I open the person.ibd with vim and edit a varchar type field of a record and save the file. Then I run a select but MySQL sees the old data. I exit from MySQL and enter again, but MySQL still sees the old data even though that data does not exist anymore in person.ibd and it is replaced by the new content which I have edited into the person.ibd file using vim.

So again, what's going on?


texts:
I mistakenly deleted around 2,000,000 records from a remote SQL Server 2008 table.  The server is not granting me access to the backup files on the server side.

Is there any way to get back these records?


texts:
We have SQL server instances set up in multiple locations (Standard Edition). To aid usage, I have set up a CMS on my local instance, with connections for various development/QA servers that are required by the team.

Is it possible to set up the CMS registrations, so that each site can connect to CMS on their local servers, but manage the list from a single location?


texts: Need to change alphanumeric characters to next character

texts:
I have 2 columns Name, Passport No. in a table with more than 100k rows.

Name   Passport No.
KOP    A245
COS    C990

After the update it should look like this.

Name   Passport No.
LPQ    B356
DPT    D001

Update current character with the next character in the database. I don't want to select anything from table, I want to update the table.

Please don't ask me why.


texts: Getting the "Database does not exist" error message

texts:
Hoping someone can help out here. I've searched for an answer on my own as much as I could in the areas that I understand regarding SQL Server administration. Something which I'm fairly new at. ( Under 2 years experience )

  Msg 911, Level 16, State 1, Line 1
  Database 'dbname' does not exist. Make sure that the name is entered correctly.

The issue is when running this script (error above) ( script posted below ) it stops and says it's not able to hit a particular database as if its still connected. However, this database which was from a migration from old server to new and was renamed (all areas including files) no longer exists anywhere. I do not see it in any T-SQL outputs or any reports and the MDF / LDF files are long gone. However, the script still hangs and thinks this DB should be part of this instance.

In searching, I've come across various posts stating a possible permission issue and to check the master DB for residual information all of which have not panned out with any solution. I have full access under my active directory login for this instance and have the SA codes as well. Same error when running as that.

Any help would be appreciated, hopefully, its something simple and I'm just missing something obvious.

Thank you all for your help, it's appreciated.

DECLARE @CurrentDB sysname
DECLARE curDatabase CURSOR FAST_FORWARD FOR
SELECT name FROM master.sys.databases
--SELECT * FROM master.sys.databases
WHERE name not in ('master','tempdb','msdb','model','AdventureWorks','AdventureWorksDW')
OPEN curDatabase
FETCH NEXT FROM curDatabase INTO @CurrentDB
WHILE ( @@FETCH_STATUS = 0)
BEGIN
--print @CurrentDB
--
declare @sql1 varchar(100)
select @sql1 = 'USE ' + @CurrentDB + ';' + '

'

--EXEC sp_sqlexec @sql1
--print @sql1

declare @sql2 varchar(max)
select @sql2 =
-- Ensure a USE <databasename> statement has been executed first.
'
SET NOCOUNT ON

SELECT DB_NAME() AS DataBaseName;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''#work_to_do'') AND type in (N''U''))
DROP TABLE #work_to_do

SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

select rtrim(name) as objectname,frag as frag_percent,type,create_date,objectid from #work_to_do a , sys.objects b
where a.objectid=b.object_id
order by frag desc
'

declare @sql varchar(max)
select @sql = @sql1 + @sql2

EXEC sp_sqlexec @Sql

--print @sql

FETCH NEXT FROM curDatabase INTO @CurrentDB
END -- curDatabase WHILE loop
CLOSE curDatabase
DEALLOCATE curDatabase