texts: Can I have single tablespace excluded from DataGuard

texts:
Can I have single tablespace not being logged and excluded from DataGuard with in same database?  We are trying to keep objects owned by a schema isolated in one tablespace and set those objects for NOLOGGING, hence not using DataGuard (only for that one tablespace).

I am not an Oracle expert but from other RDBMS  knowledge this is not possible (with exception of Read_only filegroup).  I would prefer a yes/no answer if there is one.

I am being told:

  I am pretty sure that a tablespace can be excluded from logging when
  using Data Guard.  Then we just need to keep the two tablespaces apart
  physically in the filesystem.


texts: Select complete next row based on the current row

texts:
Hi i have a complicated condition. I have a table lets say 'Test'

 ID    Partner   Type       Amount
143854  CSTC    Purchase    -0.81
144029  CSTC    Purchase    -0.69
144030  CSTC    Purchase    -1.33
144031  CSTC    Purchase    -0.47
144032  CSTC    Purchase    -1.8
149527  CSTC    div         1574.48
149528  CSTC    Purchase    -1574.48
149531  CSTC    div         867.53
149532  CSTC    Purchase    -867.53
149539  CSTC    div         123
149540  CSTC    div         76.2
149541  CSTC    Purchase    -76.2
149550  CSTC    div          8.77
149551  CSTC    Purchase    -8.77
149554  CSTC    div         700.45
149555  CSTC    Purchase    -700.45

I want to remove each occurrence of rows having Type = 'div' and the next row should have Type = 'Purchase'

i.e if type = 'div' and next row type = 'Purchase' delete both else i want to perform some update action on the row having type = 'div'.

I have tried Lead i can get next rows Type col value but it is not helping.
Other thing i tried was

;WITH TestWithRowNumber AS
(
    SELECT *,
    ROW_NUMBER() OVER (ORDER BY ID ASC) AS RowNumber
    FROM Test where Partner = 'CSTC' and   (Type = 'div' or Type = 'Purchase')
)

      Select FirstRow.* From TestWithRownumber FirstRow
  left outer join TestWithRownumber secondrow on firstrow.RowNumber = secondrow.RowNumber -1 and secondrow.type = 'Purchase' and     firstrow.type = 'div' where FirstRow.ID >= (select TOP 1 FirstRow.ID From TestWithRownumber FirstRow
   left outer join TestWithRownumber secondrow on firstrow.RowNumber = secondrow.RowNumber -1 and secondrow.type = 'Purchase' and firstrow.type = 'div' Where FirstRow.[Transaction] = 'div')

Issue with this query is in the output i am getting two consecutive div i.e an extra div whose next row is not with Type = 'Purchase'.


texts: SQL SSRS and Default Values Stored Procedure

texts:
I am creating a stored procedure with a corresponding default value.
It is using a default value of 2018 below.

create procedure rpt.FinanceReport
    @AccountYearParam int = 2018
as
select * from FinanceTable where AccountYear = @AccountYearParam

How do I pass the default Value into an SSRS report? So when the user selects nothing for parameter, it will display 2018.

"Using Null values", does not work, since Null will product a nothing.
I want the SSRS report to display all values for 2018, if user selects nothing for parameter.


texts:
I am implementing a web-mail application.Only registered users can send, receive and view E-mails. When I start to design the database,I came up with following issue.
Lets say this application has pottentially 100 000 users (maybe more than this).
each user can have thousands ( n*1000 ) of emails in there inboxes.What my question is...
Is it okay to create inbox table for each user dynamically
OR create only one inbox table to all users?

If I use only one inbox table it will
contain 100000*(n*1000) records.If I do other way only particular user's
e-mails will stored in their respective inbox table. But there will be
thousands of inbox tables in the database.

Can any one suggest a design clue...


texts: Creating Users on secondary server in log shipping

texts:
I have a production Server say ServerA I have setup log shipping to ServerB which is left in read-only mode. The purpose of this log shipping is to lower the load on production server for some expensive queries (painful reports).

Now if I have to create some logins using our domain accounts. I cannot do this because the secondary database is in standby mode.

I thought if I create these logins on Primary server it will be copied over to secondary server then the logs are restored there but this isnt the case.

I have done a lot of research online finding a way around to this. I found the following resources for this. I tried every method suggested in this articles but none of them seems to work.

1) Log Shipping in SQL Server 2008 R2 for set BI on replicated database

2) How to transfer logins and passwords between instances of SQL Server

3) Orphaned Users with Database Mirroring and Log Shipping

Has someone experienced the same issue? what did you do? Is there any way around for this issue? Any suggestions any pointer please.


texts:
Do you see why is appearing this error "SQL command not properly ended" in the select statement below?

Select command in oracle:

 select
        l_orderkey,
        sum(l_extendedprice * (1 - l_discount)) as revenue,
        o_orderdate,
        o_shippriority
    from
        customer,
        orders,
        lineitem
    where
        c_mktsegment = 'BUILDING'
        and c_custkey = o_custkey
        and l_orderkey = o_orderkey
        and o_orderdate < date '1995-03-15'
        and l_shipdate > date '1995-03-15'
    group by
        l_orderkey,
        o_orderdate,
        o_shippriority
    order by
        revenue desc,
        o_orderdate
     where rownum <= 10;


texts: updating values in one table if depending table row is affected

texts:
there are two tables

structure is like:

product_table

item_id product_id count_subscriber
1          2           4
2          3           5

subscriber_table

item_id subscriber_id product_id
1         115           2
2         145           2
3         84            3

what i want to achieve is if i add/delete one item_id from subcriber_table this should update the value in count_subscriber (increment/decrement) of product_table where product_id matches.

is this possible to do this in one query? how should i achieve this in one query? right now what i am doing is first deleting the item_id in subscriber_table and than updating the count_subscriber in product_table and same for addition.

please advise.
thank you for your time.


texts: Is select * still a big no-no on SQL Server 2012?

texts:
Back in the days of yesteryear, it was considered a big no-no to do select * from table or select count(*) from table because of the performance hit.

Is this still the case in later versions of SQL Server (I'm using 2012, but I guess the question would apply to 2008 - 2014)?

Edit: Since people seem to be slating me slightly here, I'm looking at this from a benchmark/academical point of view, not whether it's the "right" thing to do (which of course it's not)


texts: SQLSERVER FullText search troubleshooting

texts:
I have a table with nchar(32) column, with FullText search and auto change tracking activated.

Data set:

Name, SuperID
Item1, (LO}(bJ$
Item2, (LO}(bJ$
Item3, (LO}(bJ$

I run two queries:

select Name, SuperID from Asset A WHERE A.SuperID LIKE
N'%(LO}(bJ$%'

and

select Name, SuperID from Asset A WHERE (   CONTAINS
(A.SuperID ,N'"(LO}(bJ$"')  )

The first one returns 3 rows, and the second returns only 1 row.

Why is it happening? How can I analyse the contents of the Full Text search index? The column values in the first three results are identical. I have tried to update indexes/stats but that doesn't change the outcome.

UPDATE:
I have tried removing quotes around the CONTAINS query but I get an error message:

  "Syntax error near '(' in the full-text search condition

UPDATE2:

I should have phrased my question in a different way, I think! Have a look at the SuperID column values for the three rows that I would like to return. I have converted them to VARBINARY to ensure that there is no hidden trailing space trickery going on and they are actually the same:

SuperIDasBinary
0x28004C004F007D00280062004A0024002..
0x28004C004F007D00280062004A0024002..
0x28004C004F007D00280062004A0024002..

So why does the CONTAINS query above return only one row instead of three?


texts: RANGE clause error within window function

texts:
I'm trying to use postgres' RANGE clause within a window function to dynamically set the window to the past 4 weeks not including the current date. Here is my attempt at it:

SELECT date("aa0"."created_at") AS "Day of Created At",
       "aa0"."user_id" AS "User Id",
       count (*) activities_today,
       count(*) over (PARTITION BY "aa0"."user_id"
                      ORDER BY date("aa0"."created_at") ASC
                      RANGE BETWEEN date("aa0"."created_at" - interval '4 weeks')
                                    AND
                                    date("aa0"."created_at" - interval '1 day')
                ) active_days_past_4_weeks
FROM "public"."activity_activity" AS "aa0"
GROUP BY date("aa0"."created_at"), "aa0"."user_id"
ORDER BY "Day of Created At" ASC LIMIT 1000;

However, I get the following error:

ERROR: syntax error at or near ")" Position: 459

If I use ROWS instead of RANGE it works but this is not the correct logic since it's not guaranteed that there will be a row for every day:

SELECT date("aa0"."created_at") AS "Day of Created At",
       "aa0"."user_id" AS "User Id",
       count(*) activities_today,
       count(*) over (PARTITION BY "aa0"."user_id"
                      ORDER BY date("aa0"."created_at") ASC
                      ROWS BETWEEN 28 PRECEEDING AND 1 PRECEEDING
                ) active_days_past_4_weeks
FROM "public"."activity_activity" AS "aa0"
GROUP BY date("aa0"."created_at"), "aa0"."user_id"
ORDER BY "Day of Created At" ASC LIMIT 1000;

Schema:

CREATE TABLE activity_activity (
  user_id int,
  created_at timestamp
)

Version:

select version()

PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-16ubuntu6) 4.8.2, 64-bit


texts:
I have a database with a primary key on the first column (words)
I want to trim the trailing and leading ends of my data, but I get an error that this causes duplicate data

EX: ' David ' will become 'David' and that is already in the database

Is there anyway to compare what I already have in the database to my Select statement (without spaces) and remove any that will cause a duplicate row?


texts: pros/cons of different ways to store whether a record is one of two options?

texts:
I am trying to store whether an address is a Work address or a Home address. There will never be another type of address.

I'm wondering what the pros/cons are of the different ways to store this, and if there is an accepted 'style' for this type of situation which is considered best practice,

Would it be better to just have a single
1)IsHome bool column, and if it's false, I just assume it's a work?
2) or both a IsHome and IsWork column,
3) or a AddressType column which is an ID that would correspond to another table which  has work and home with an ID?
4) or something I have not considered?

The third option seems a little cleaner however needing to join every time seems inefficient.


texts:
I suspect the answer to this is "you're boned", but I'd like to see if there are any good options that I haven't explored.

We're building a system that will integrate with a 3rd-party POS system and display tickets on boards at various stations in a kitchen. We don't have direct access to the POS system's database; rather, the database is replicated to another server that we can read data from.

In order to send new tickets to the clients, or change/remove modified tickets, we need to watch this database for changes. If we were running it all on a single database server, I'd add a few change logging triggers to the tables in question (which total a couple GB), then poll that log table every 5-10 seconds to see which tickets need to be rebroadcast.

Unfortunately, we're dealing with this replicated system. I know that I can create triggers on the slave, but they'll only run for operations that are logged in statement format rather than row format. The master server is configured for mixed binlog format, and looking through the list of operations that are unsafe to log in statement format, I'd be surprised if none of them are being used on the POS tables. We absolutely can't have tickets get missed due to the trigger not being run on the slave.

These POS tables also don't appear to have any update timestamp columns on them, so I can't simply query rows that have been updated since the last polling time (not to mention the possible race conditions we'd have to address there). Further, these tables are much too big to snapshot and diff them (or a relevant subset of their columns) at every polling.

Oh, and I'm guessing it would be way too much work to connect to the master directly pretending to be a slave and reading the binlog content directly. So, anybody have any clever tricks I haven't thought of before we tell the client we can't do it with what we have? (They would probably be comfortable letting us access the master server, but it's the application vendor that's cagey about it. The client owns the server and data, and we could certainly force our way in, but I don't want to void any contracts and leave them really screwed.)


texts: Mongodb automatic Replica Set creation with JS driver

texts:
The MongoDB docs state that one can use the mongo client console to initialize replica sets by running rs.initiate([config]). However, I don't want to have to manually use the command line for this. There is a driver method called Admin.command in the docs but I don't know how that would work here. How do I automate configuring replica sets using the MongoDB JS driver?

I have a js script that initializes my database installation by creating the data folder and automatically setting up users for access control. I want it to also configure the replica set so the database is completely ready to use after running it. I'm using MongoDB 4.2.1 and driver version 3.3.


texts: SQL server database backup - Destination Disk - Adding multiple files - does it duplicate or split backup into the files?

texts:
When we do full data backup (using SSMS UI), at the bottom of the window we have the option to specify the destination as Disk and also to add multiple files.

My question is - does adding multiple files create duplicate copies of the full backup? or does it create a split backup - that is split the full backup into the specified files?

This book suggests it does a duplication where as this link suggests that it does a split. Please can someone clarify.


texts: How would I change this relational model to fit a nosql database?

texts:
Because of things beyond my control, I have to switch from a relational database to MongoDB for the advertising platform I'm helping to build. I've got a relational schema sketched out, but I'm unsure where to begin switching to nosql. I'd like to know what tables it makes sense to embed, and where it makes sense to denormalize.

Here's my current schema (as you can see, it's pretty normalized):

Users: id, name, email, paypal_email, password, balance, created_at, updated_at

Clicks: id, bid_id, server_id, origin_ip, valid, created_at

Ads: id, user_id, title, content, link, created_at, updated_at

Bids: id, ad_id, continent, current, auto, max, created_at, updated_at

Servers: id, user_id, ip, created_at, updated_at

People: id, username, ip, server_id, created_at, updated_at

Transactions: id, amount, transactable_id, transactable_type, created_at

We'll be making pretty heavy use of these tables. I expect to get many click creations, people updates, and transaction creations per second. We obviously want to be able to provide statistics like clicks per ad, impressions, etc. This information will be updated real-time on our site.

Finally, at the end of each day or two, we'll be performing large queries against clicks in relation to a couple of other tables.

Is this realistic to do with something like MongoDB?


texts:
I've found nothing relevant on the internet about backing up postgres DB with raid1. So here's my question

My sys admin thinks that backing up the raid1 disk ,on which a Postgres database is sitting, is adequate as a Postgres backup. He would restored it when needed.

To my understanding, backup needs to have the database stopped, at least for some commercial database like Oracle.

Is it possible to backup the database without having it stopped with a raid1 scenario?

Thanks


texts: Sorting by month by chronological occurrence when the year doesn't matter?

texts:
Given a table with a date column, and given a query that selects dates in a given range of the year (like "November 20th through February 20th"), and where the year portion of the date doesn't matter (such as upcoming birthdays), how can the results be sorted by month in the correct chronological order for the selected time range?

For example, if the query grabs all dates between November 20th and February 20th regardless of year, it should sort them in the order:

Nov -> Dec -> Jan -> Feb

But a regular sort by month actually produces:

Jan -> Feb -> Nov -> Dec

Or an inverted sort produces:

Dec -> Nov -> Feb -> Jan

both of which are wrong.

Here's a sample query with a three day range at the end of the year:

SELECT EventDate
   FROM Events
   WHERE TIMESTAMPDIFF(YEAR, EventDate, "2012-12-28" + INTERVAL 3 DAY)
      > TIMESTAMPDIFF(YEAR, EventDate, "2012-12-28" - INTERVAL 1 DAY)
   ORDER BY DATE_FORMAT(EventDate, "%m-%d") DESC

It produces:

2012-01-01
2008-01-01
2008-01-01
1987-01-02
1994-12-28

Where it should produce:

1994-12-28
2012-01-01
2008-01-01
2008-01-01
1987-01-02

Is there a way to get it to sort by month and day in a "going forward from range start" order?

I searched here and on SO but didn't find an answer that dealt with both year end wrapping of months and a scenario where the year in the date field isn't relevant for sorting.

Update: The range should always sort forward, so if the range is Feb to Nov, it does a standard sort of Feb - > Mar -> Apr .... If the range is Nov to Feb, it sorts Nov -> Dec -> Jan ....

Although I don't anticipate that being an issue since I don't need to query more than a 3 month window.


texts:
I want to have 2 users of the DB (user_app and user_migration), one for executing migrations (which will have more privileges, like creating/dropping tables + basic usages) and one that the overlaying application will use to interact with the DB (which will have select, insert, update, delete privileges only).

The problem right now, that I'm facing, is that when I create a table from user_migration, the other user doesn't have the permissions to access that table.

This is the SQL code I've got for creating the users:

-- create the role
DO
$body$
BEGIN
  IF NOT EXISTS(
      SELECT *
      FROM pg_catalog.pg_roles
      WHERE rolname = 'app_users')
  THEN

    CREATE ROLE "app_users";
  END IF;
END
$body$;

GRANT CONNECT ON DATABASE :dbname TO "app_users";
GRANT USAGE ON SCHEMA "public" TO "app_users";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "app_users";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO "app_users";

-- create the users and grant them role privileges
DO
$body$
BEGIN
  IF NOT EXISTS(
      SELECT *
      FROM pg_catalog.pg_user
      WHERE usename = 'user_app')
  THEN

    CREATE USER "user_app" WITH PASSWORD 'somepswd';
    GRANT app_users TO user_app;
  END IF;

  IF NOT EXISTS(
      SELECT *
      FROM pg_catalog.pg_user
      WHERE usename = 'user_migration')
  THEN

    CREATE USER "user_migration" WITH PASSWORD 'somepswd';
    GRANT app_users TO user_migration;
  END IF;
END
$body$;

-- here, assign additional privileges to user_migration to create/delete entities

How can I set up the roles/users in order to achieve the described scenario above?


texts:
I'm working on DB2 stored procedure, which will be called by the Korn Shell (.ksh). My DB2 version is 11.5 and it is running on AIX.

I was stuck in TIMESTAMP to DATE conversion. Below is my requirement.

If s_day < day(s_date) then
start_date = YYYY-MM from s_date + 1 day from s_day
end if

Note here s_date is DATE variable, s_day is CHAR variable and start_date is DATE variable.

So I tried, the following code, but got an error.

   IF s_day < DAY(s_date)
    THEN
    SET start_date = DATE (DATE(s_date,'YYYY-MM') || s_day + 1);
    END IF

Sample values

s_day =03
s_date= 15/05/2020
expected output start_date is 2020-05-04

Can anyone help me to achieve expected output?


texts: Length of varchar column - is there an advantage to using 255?

texts: I often come across columns that are varchar(255), so presumably this is a convention of sorts. 255 is 2^8 - 1. So is there something 'magical' about lengths that are 2^n - 1? Or just specifically 255? Is there a performance or storage optimisation advantage to certain specific varchar lengths? Or is this just old wisdom which is no longer applicable to current versions of MariaDB and MySQL?


texts: Update same column, same DB with specific records

texts:
We are migrating a site from one Active Directory system to another but in the third-party app, we need to keep the old accounts, so we renamed the old accounts with "_Old" appended to the last name. Now we need to update a particular column from the old accounts into the new accounts so that their roles in the app match what they were before the migration.
I've figured out how to do it with one person.  I need to take this same logic and apply it to multiple accounts.
Here's the code I've got so far:
Update Staff
Set Role_Id =
    (   SELECT Role_Id
        FROM Staff
        where Staff_First_Name = 'Jane'
            AND Staff_Last_Name = 'Doe_Old')
Where Staff_First_Name = 'Jane'
    AND Staff_Last_Name = 'Doe'


texts: Is there a faster way to count JSONB tags?

texts:
I'm trying to squeeze some more performance out of this query in Postgres 9.5. I'm running it over 400,000 rows.

In playing around with it, I've noticed that the CASE statements are adding quite a bit to the query cost - if I replace them with simply summing some existing column it halves the execution time. Is there a more efficient way to calculate these sums?

SELECT sum("tag1"), sum("tag2"), sum("total_tags")
FROM (
    SELECT people.data->'recruiter_id' AS recruiter_id,
          (CASE WHEN people.data->'tags' ? 'tag1' THEN 1 END) AS "tag1",
          (CASE WHEN people.data->'tags' ? 'tag2' THEN 1 END) AS "tag2",
          ((CASE WHEN people.data->'tags' ? 'tag1' THEN 1 ELSE 0 END) +
           (CASE WHEN people.data->'tags' ? 'tag2' THEN 1 ELSE 0 END)) AS total_tags
    FROM people WHERE people.data->'tags' ?| ARRAY['tag1','tag2'] ) AS target
GROUP BY recruiter_id

Output of EXPLAIN ANALYSE:

HashAggregate  (cost=1076.30..1078.22 rows=550 width=202) (actual time=7043.115..7043.208 rows=449 loops=1)
  Group Key: (people.data -> 'recruiter_id'::text)
  ->  Bitmap Heap Scan on people  (cost=12.85..1072.72 rows=550 width=202) (actual time=13.908..2619.878 rows=48492 loops=1)
        Recheck Cond: ((data -> 'tags'::text) ?| '{tag1,tag2}'::text[])
        Heap Blocks: exact=26114
        ->  Bitmap Index Scan on index_people_on_data_tags  (cost=0.00..12.82 rows=550 width=0) (actual time=9.219..9.219 rows=48493 loops=1)
              Index Cond: ((data -> 'tags'::text) ?| '{tag1,tag2}'::text[])
Planning time: 0.139 ms
Execution time: 7043.291 ms

Running on:

  PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

The inner query and outer query are generated by separate portions of the application. Is it possible to optimise without restructuring?


texts:
I'm looking at a SQL Server environment in which jobs start in several different ways. One way is from a job running on a different SQL Server.

So, for example, "SQL Server A" runs a job named "DoStuff". One step in "DoStuff" is to run the job "MoreStuff" on "SQL Server B".

If I were looking at a problem on "SQL Server B", how would I answer the question, "What started 'MoreStuff'?"

The Job Properties "General" page has nothing about any external source.

The Application log records what "MoreStuff" is doing, but it doesn't seem to have any information about what started "MoreStuff". Other classic logs and newer Applications and Services Logs don't seem to have anything relevant in them. (I could have missed something.)

I'll take information on Server 2008 R2 and up, and on SQL Server 2005 and up.


texts:
I have a simple select distinct on some time series data:

SELECT DISTINCT user_id
FROM events
WHERE project_id = 6
AND time > '2015-01-11 8:00:00'
AND time < '2015-02-10 8:00:00';

And it takes 112 seconds. Here's the query plan:

http://explain.depesz.com/s/NTyA

My application has to preform a lot of distinct operations and counts like this. Is there a faster way to get this kind of data?


texts: 100's of 1000's of report server jobs added - how to kill all?

texts:
A colleague wanted to run about 70 SSRS reports using the EXEC ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='E3843E8F-632E-4F2F-BE7C-63D4CF10F2ED' command. (Changing @EventData)

Unfortunately he ran a cursor to loop through a list of them, but it seems to have just ran the first one around 500,000 times before it was cancelled. This seems to have added all of these to a queue of some sort that is constantly working them.

We have tried killing each process, restarting the ReportServer service, restarting the entire server, but each time new jobs continue to be created. How do I clear whatever queue these are sitting in?

This is kind of urgent, what do we need to do?

I am searching through the ReportServer database for a table to remove the rows from, the ones that stand out are Notifications and ExecutionLogStorage as these have enough data in them, but the likes of RunningJobs is empty.


texts:
With the SQL Fiddle at http://sqlfiddle.com/#!18/fce3e/1, I’m trying to get the following results, but can’t wrap my head around it.  I don’t want to specify a specific category or personId values.

Thank you.

| id | message | category | personId | enabled |
|----|---------|----------|----------|---------|
|  1 |   Hello |        1 |        1 |       1 |
|  2 |      Hi |        1 |        1 |       1 |
|  3 |     Hey |        1 |        1 |       0 |
|  4 |    Argh |        2 |        1 |       0 |
|  5 |     Yar |        2 |        1 |       0 |
|  6 |  Hooray |        2 |        1 |       0 |
|  1 |   Hello |        1 |        2 |       0 |
|  2 |      Hi |        1 |        2 |       0 |
|  3 |     Hey |        1 |        2 |       0 |
|  4 |    Argh |        2 |        2 |       1 |
|  5 |     Yar |        2 |        2 |       1 |
|  6 |  Hooray |        2 |        2 |       0 |
|  1 |   Hello |        1 |        3 |       0 |
|  2 |      Hi |        1 |        3 |       0 |
|  3 |     Hey |        1 |        3 |       1 |
|  4 |    Argh |        2 |        3 |       0 |
|  5 |     Yar |        2 |        3 |       0 |
|  6 |  Hooray |        2 |        3 |       0 |


texts:
I have about 2000 rows to be inserted at a time. Which is Optimized method Multiinsert or insert into .. SEelec colum1, column2 from table .
In case if Multi-insert, I will SELECT generate multiple statments in my application.
So in short, which is Optimized?
INSERT INTO table_name(column_list)
SELECT
   select_list
FROM
   another_table
WHERE
   condition;

OR
INSERT INTO table(c1,c2,...)
VALUES
   (v11,v12,...),
   (v21,v22,...),
    ...
   (vnn,vn2,...);


texts: SQL Server Select Count READ_COMMITTED_SNAPSHOT QUESTION

texts:
I seem to be getting a lot of deadlocks when doing select count(*) on a particular table.
I have already changed all the required parameters and made them into row only locking.

I've also changed the database to use READ_COMMITTED_SNAPSHOT isolation,

however, it seems that using a select count(*)  where column = ? on the table triggers deadlocks or locks on the table..

Am I correct that the select count(*) should only be accessing intermediate rows?, however, it doesn't seem that way and I'm still encountering deadlocks. Proper indexing would probably help,

The question is: Does SQL server 2008 R2 place shared lock on table during select count(*) even when read_committed_snapshot is set to on?

Thanks


texts: Clear schema from database without dropping it

texts:
I'm working on a school project where I have a SQL Server with a database for my team.

I already imported a local database created with Entity Framework.

Now the model has changed, table properties were added/deleted and I want to update my full database.

However, the teachers didn't gave us the create rights so dropping the whole database isn't really an option.

Now is my question, is it possible to drop all the tables currently in the database and just import the newly created one without problems? Or do I really need to drop the whole database?


texts: Benefits of table partitioning on same disk

texts:
Maybe is stupid question, but I must ask to be clear with that.

I know that table partitioning is powerful mechanism of speeding data search when partitions is placed on different disks.

What I need to know what I can get if I place partitions on same disk?


texts: COUNT and SUM at the same time after removing duplicate rows

texts:
I'm using PostgreSQL and I can't figure out a query for doing a COUNT and a SUM of two different columns. Basically I'm looking for something like a SUM(hits DISTINCT record_id)

The table:

status      record_id      hits
     0            123         1
     0            123         1
     0            124         1
     0            125         1
     0            126         0
     1            123         0
     1            124         0
     1            125         1
     1            125         1

The desired result:

status   unique_records_count  hits_sum
     0                      4         3
     1                      3         1

Doing a

SELECT status, COUNT(DISTINCT record_id), SUM(hits)
FROM table
GROUP BY status

returns

status   unique_records_count  hits_sum
     0                      4         4
     1                      3         2


texts: Unable to remotely connect to Postgresql 9.5 instance

texts:
I am struggling to connect to a remote Postgresql 9.5 instance running on Ubuntu 16.10.
The usual:

  psql: could not connect to server: Connection timed out
    Is the server
  running on host "xx.xx.xx.xx" and accepting   TCP/IP connections on
  port 5432?

My postgresql.conf includes

listen_addresses='*'

confirmed by output from

netstat -nlp | grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      22981/postgres
tcp6       0      0 :::5432                 :::*                    LISTEN      22981/postgres
unix  2      [ ACC ]     STREAM     LISTENING     3320886  22981/postgres       /var/run/postgresql/.s.PGSQL.5432

Apart from my specific IP address & range, I have also tried the following entries in pg_hba.conf:

host    all             all             0.0.0.0/0               md5
host    all             all             ::/0                    md5
host    all             all             all                     md5
host    all             all             *                       md5

I have the following ufw rules:

To                         Action      From
--                         ------      ----
22/tcp (OpenSSH)           ALLOW IN    Anywhere
80,443/tcp (Nginx Full)    ALLOW IN    Anywhere
5432                       ALLOW IN    Anywhere
22/tcp (OpenSSH (v6))      ALLOW IN    Anywhere (v6)
80,443/tcp (Nginx Full (v6)) ALLOW IN    Anywhere (v6)
5432 (v6)                  ALLOW IN    Anywhere (v6)

However, the problem persists even when disabling the firewall.

Scanning with nmap:

nmap xx.xx.xx.xx -p5432

Starting Nmap 7.01 ( https://nmap.org ) at 2017-02-13 09:17 SAST
Nmap scan report for xx.xx.xx.xx
Host is up (0.0074s latency).
PORT     STATE    SERVICE
5432/tcp filtered postgresql

Almost all similar questions on SE seem to be solved with

  listen_addresses='*'

which is not an issue here.

Really confused by this, help would be greatly appreciated.

The machine is not down as I am able to ssh into the machine. Additionally nginx server is also accessible. Telnet to port 5432 times out.

There is no local router in front of the ubuntu server, it is a dedicated server with public network interface that is bound to the xx.xx.xx.xx address. This is a migration from a previous generation box at the same hosting company who does not offer the option of an additional firewall device.  This is a clean Ubuntu 16.10 server install.


texts: After Update/Insert trigger sometimes fails to do its job

texts:
I've got this trigger:

CREATE OR REPLACE TRIGGER "DOKCENTRA"."INTDOC_AFTER" AFTER
  UPDATE OR INSERT
    ON "DOKCENTRA"."INT_DOC" FOR EACH ROW
BEGIN
IF UPDATING THEN
 IF (
     :NEW.DOCNUMBER   IS NOT NULL
   AND :OLD.DOCNUMBER IS NULL) THEN
   INSERT
   INTO
     DOC_IDS VALUES
     (
       :NEW.DOCNUMBER
     );
 END IF;
ELSIF INSERTING THEN
  IF (:NEW.DOCNUMBER IS NOT NULL) THEN
    INSERT
    INTO
      DOC_IDS VALUES
      (
        :NEW.DOCNUMBER
      );
  END IF;
END IF;
END;

I think it's obvious what it does but to make it clear let me give a brief explanation. In my INT_DOC table there's DOCNUMBER column. I assign a value to this column in Before Update trigger. And in the above trigger, which of course should fire after the Before Update Trigger, I check if the DOCNUMBER has value (and if it's for the first time) and if it does, I write that value to another table called DOC_IDS. The problem is, the above trigger sometimes, although very rarely, fails to insert the :NEW.DOCNUMBER to the DOC_IDS table. That is, sometimes I see a record in INT_DOC table with DOCNUMBER value that does not exist in DOC_IDS table.The DOC_IDS table has only one column called DOCID and it's part of a UNIQUE KEY constrsraint. Do you see any problem with this trigger?

EDIT:

I think you'll get a better idea if I post the BEFORE TRIGGER too:

create or replace
TRIGGER "DOKCENTRA"."INTDOC_BEFORE" BEFORE
  UPDATE OR
  INSERT ON "DOKCENTRA"."INT_DOC" FOR EACH ROW
 BEGIN
 IF INSERTING THEN
 IF (:NEW.CONFIRMED=1 AND :NEW.DOCNUMBER IS NULL) THEN
    SELECT DOCUMENTS.F_GENERATE_DOCNUMBER(:NEW.REGUSER,2)
    INTO :NEW.DOCNUMBER
    FROM DUAL;
  END IF;
 ELSIF UPDATING THEN
  IF (:NEW.CONFIRMED=1 AND :NEW.DOCNUMBER IS NULL) THEN
    SELECT DOCUMENTS.F_GENERATE_DOCNUMBER(:NEW.EDITUSER,2)
    INTO :NEW.DOCNUMBER
    FROM DUAL;
  END IF;
END IF;
END;

And the only place I set DOCNUMBER is this trigger.

EDIT2:

CREATE TABLE INT_DOC(
   DOCID VARCHAR2(50) NOT NULL,
   RECEIVEDDATE DATE NOT NULL,
   SENDERFULLNAME VARCHAR2(100) NOT NULL,
   SUBJECT VARCHAR2(200) NOT NULL,
   CONFIRMED NUMBER(1,0) NOT NULL DEFAULT 0,
   DOCNUMBER VARCHAR2(10),
  CONSTRAINT INT_DOC_PK PRIMARY KEY
  (
    DOCID
  )
  ENABLE
);
ALTER TABLE INT_DOC
ADD CONSTRAINT INT_DOC_UK1 UNIQUE
(
  DOCNUMBER
)
ENABLE;

CREATE TABLE DOC_IDS(
 DOCID VARCHAR2(10)
);
ALTER TABLE DOC_IDS
ADD CONSTRAINT DOC_IDS_UK1 UNIQUE
(
  DOCID
)
ENABLE;

INSERT INTO INT_DOC(DOCID,RECEIVEDDATE,SENDERFULLNAME,SUBJECT,CONFIRMED)
VALUES('AFFXRARA1',to_date('01.01.2015','DD.MM.YYYY'),'John Saunders','SomeSubject',1);--CONFIRM set to 1 at insert
INSERT INTO INT_DOC(DOCID,RECEIVEDDATE,SENDERFULLNAME,SUBJECT)
VALUES('AFCCYARA1',to_date('25.10.2015','DD.MM.YYYY'),'Steve Wonder','AnotherSubject');
INSERT INTO INT_DOC(DOCID,RECEIVEDDATE,SENDERFULLNAME,SUBJECT)
VALUES('BRTYRARA1',to_date('12.01.2015','DD.MM.YYYY'),'Ellie Johnson','YetAnotherSubject');

UPDATE INT_DOC SET CONFIRMED=1 WHERE DOCID='AFCCYARA1';--CONFIRM set to 1 with update

Here's the sample record in INT_DOC table which has the DOCNUMBER column value that does not exist in the DOC_IDS table:

           DOCID                       RECEIVEDDATE             SENDERFULLNAME                 SUBJECT                      CONFIRMED        DOCNUMBER
---------------------------------- |-----------------------|----------------------|-----------------------------------|------------------|------------
6A5889BB0190D0211A991F47BB19A777        23.02.2016 10:24:17       Rowan Priatski           Amendment to the last decree            1               1602000097


texts: Connecting to an SQL Azure Database from SSMS - IP Restriction

texts:
I am trying to connect to a new SQL Azure Database via SQL Server Management Studio. The client box running SSMS is hosted on a production DB so we restrict access on 1433 to just a handful of IPs using IP Sec. The client IP is configured on the Azure Portal for access to the Azure DB. I can only get access to the Azure DB from the client by opening 1433 publicly, which I'm not keen to do on account of their being a production DB on there.

I appreciate the cloud does not provide us with the certainty in terms of stable IPs. But we're only using this connection to perform some various admin tasks and we're happy to change the IP from time-to-time, as required.

But how can I know what IP Azure is responding on? When I ping {myserver}.database.windows.net I get an IP but when I add this to the IP Sec rule it still doesn't work. Is Azure responding on a different IP? If so how can I come to know what this IP address is as SSMS only throws a generic network error?

Additional Information:

The box running SSMS is not hosted within Azure. We are currently running this from another datacenter


texts: Tracking of deleted records who delete the transaction in SQL server 2008r2

texts:
how can we check who deleted the transaction even after few days in sql server 2008 r2
is there any command instead of logs


texts: Count the maximum number of clients and their manager ID using three tables

texts:
I need to find name, age, bonus and the maximum number of clients for a manager. I managed to get everything except for the maximum number of clients. I need to get the maximum number of clients from the client table, and match the client id with the building id in the building table and lastly match the building id with the manager id in the manager table. With the manager id I'll be able to retrieve the manager details.

This is what I have done:

select mfname, mlname, TIMESTAMPDIFF(year, m.mbdate, CURDATE()) AS "Age",
mbonus, MostClient = count(*)
from apartment a, manager m, building b
where MostClient = b.buildingid and
  b.buildingid = b.managerid and
  b.managerid = m.managerid
group by MostClient
having count(*)
order by max(MostClient)

but I'm facing an error as the MostClient in the where clause is showing unknown field list.

  Error Code: 1054. Unknown column 'MostClient' in 'field list'
  How can I proceed with this? Any hints would be appreciated.

APARTMENT

CREATE TABLE apartment
(   buildingid      CHAR(3)     NOT NULL,
    aptno       CHAR(5)     NOT NULL,
anoofbedrooms   INT     NOT NULL,
ccid        CHAR(4),
rent INT NOT NULL,
PRIMARY KEY (buildingid, aptno),
FOREIGN KEY (buildingid) REFERENCES building(buildingid),
FOREIGN KEY (ccid) REFERENCES corpclient(ccid) );

BUILDING

CREATE TABLE building
(   buildingid  CHAR(3)     NOT NULL,
bnooffloors INT     NOT NULL,
bmanagerid      CHAR(4)     NOT NULL,
PRIMARY KEY (buildingid),
FOREIGN KEY (bmanagerid) REFERENCES manager(managerid) );

MANAGER

CREATE TABLE manager
(   managerid       CHAR(4)     NOT NULL,
mfname      VARCHAR(15)     NOT NULL,
mlname      VARCHAR(15)     NOT NULL,
mbdate      DATE        NOT NULL,
msalary     NUMERIC(9,2)    NOT NULL,
mbonus      NUMERIC(9,2),
mresbuildingid  CHAR(3),
PRIMARY KEY (managerid) );


texts: Getting rows in left table only if not present in the right table

texts:
I need to get only those rows which dont have the corresponding entry in the right table advlabel , so I am doing a full left outer join :

SELECT newparsed.seqid,newparsed.wordindex,label,arg1,arg2,sublabel FROM newparsed
LEFT OUTER JOIN   advlabel ON ((`advlabel`.`seqid` = `newparsed`.`seqid`) AND (`advlabel`.`wordindex` = `newparsed`.`wordindex`))
WHERE sublabel = NULL

So is the above query correct sublabel is a column from the right side table advlabel  ?

both tables have primarykey(seqid,wordindex)


texts: Why is SQL Server Express creating twenty users on my system?

texts:
I was taking a look into Computer Management->Users and found that SQL Server Express has created a lot of users for me.

Why? Is my system infected?

The descriptions all say, "Local user account for execution of R scripts in SQL Server instance SQLEXPRESS".


texts:
My current team of 20 people use an Access database front end on their desktop. The backend of the access database is on a network drive. I have been asked to create a Access database front end with MySQL as the back end. I have installed the MySQL workbench and the ODBC connector on my computer. I have created the schema and tables and I have connected the front end of the database connected to the MySQL table I created in workbench.

My questions are:

How do I deploy this for the team to use. I believe I can move the front end of the Access database to the network drive and the team can copy it to their desktop. But what do I do about the MySQL backend? Where should it be placed?
Does the team need to have the ODBC connector installed in their computers?
Should I move the MySQL workbench to the network drive?

PS: I am a new developer and just learning about databases and I am only familiar with Access databases.


texts:
I have 40 views that are integrated/synced to a work order management system on a weekly basis.

The views are based on GIS tables which are notoriously messy. Over time, the views can end up becoming invalid.

Example:

A user deletes or renames a column in an underlying table, but fails to notify I.T. of the change, so the view is not updated accordingly.
As a result, the view would become invalid: ORA-04063: view "ROADS_VW" has errors or ORA-00904: "FIELD1": invalid identifier.

I would like to catch & fix invalid views (or fix the underlying data) before the views are synced to the work order management system each week.

Is there a way to get Oracle to notify me about invalid views?

For instance, if the integrations occur on Fridays, then get an email on Thursdays if any of the views are invalid.


texts: Query MongoDB collection and find rows from 7 days ago

texts:
I have a MongoDB collection called messages and this is an example document for that collection:

{
    "_id": {
        "$oid": "551943eee4c6acf40fe5a0f2"
    },
    "sender_id": {
        "$oid": "551943eee4c6acf40fe5a0f3"
    },
    "rep_sender_id": "120",
    "sender_name": "Karen Morris",
    "sender_title": null,
    "sender_photo_id": "https://reptool_rpc.pdone.com/web/bundles/pdone/media/representative/photo/qlv_morris_karen.jpg",
    "sender_company": "",
    "recipient_id": {
        "$oid": "5367e006e4c6ac5b0d1b8e29"
    },
    "recipient_name": "KARIS KNIGHT",
    "recipient_title": "",
    "recipient_photo_id": "webdrProfileIcon.png",
    "message": [
        {
            "message_link": "20150330073847782685415",
            "message_href": "//www.themedicalbag.com/pdone/pharma_brandpage?id=20150330073847782685415&brand_id=11",
            "message_href_text": "Prescribing "
        }
    ],
    "message_subject": "has shared information about ADHD treatment option with you",
    "sender_type": "rep",
    "message_type": "share",
    "supplemental": "",
    "reply_ok": "no",
    "mdate": {
        "$date": "2015-03-30T12:39:10.170Z"
    },
    "date": "Mon March 30, 2015",
    "read": "no",
    "timeago": "2015-03-30T08:39:10-04:00",
    "reference_count": 1,
    "chain": [],
    "rep_header": {
        "paccode": "PFIZER_EMAIL_SEG1",
        "sync": "",
        "_st": "ejR2NGQ0eDVrNWw1ajVqNXc0ZjN2NG4zNDQ0NDAzeDU2NDY0dTVsMjQ0YTVhMms1NjNuNDIzbDJzNG00YTU2MzU0NTNtNW0yMjQyNDIzbjJhMzA1YjNiNHQ1NTVkNGE0cDNsNHozdzIwM2o0",
        "hcp_email_address": "karis@magnoliafamily.com",
        "email_target": "tmb",
        "inactive": "no",
        "share_date": "3-30-2015",
        "share_date_2": {
            "$date": "2015-03-30T12:39:10.000Z"
        },
        "hcp_id": "109270",
        "rep_msg": "Thank you for taking time to speak with me today. Here are some additional materials about Quillivant XR&trade; (methylphenidate HCI) CII. I look forward to following up with you in the near future. Thank you.",
        "session_id": "201503300738361681657321",
        "rep_id": "120",
        "hcp_fname": "KARIS",
        "hcp_lname": "KNIGHT",
        "address": "402 JOHNSTON STREET SE",
        "city": "DECATUR",
        "zip": "35601",
        "program_path": "https://reptool_rpc.pdone.com/web/reptool/set/",
        "rep_fname": "Karen",
        "rep_lname": "Morris",
        "rep_title": null,
        "rep_alias": "Morris,Karen",
        "rep_photo_id": "https://reptool_rpc.pdone.com/web/bundles/pdone/media/representative/photo/qlv_morris_karen.jpg",
        "email_subject": "Karen Morris has shared information about an ADHD treatment option with you.",
        "mongo_id": "5367e006e4c6ac5b0d1b8e29"
    },
    "rep_shares": [
        {
            "media_show_citation": "no",
            "server_path_to_media": "https://reptool_rpc.pdone.com/web/bundles/pdone/media/qlv_veeva/brand/presentation/2014_Quillivant_Downloadable_Letter.pdf",
            "company_id": 6,
            "media_citation": "",
            "media_id": 246,
            "media_size": "11",
            "media_title": "Prescribing%20Quillivant%20XR%E2%84%A2",
            "media_description": "Learn%20how%20to%20individualize%20the%20dose%20of%20Quillivant%20XR%E2%84%A2%20%28methylphenidate%20HCI%29%20CII%20according%20to%20the%20needs%20and%20response%20of%20the%20patient.",
            "media_type": "pdf",
            "media_icon": "https://reptool_rpc.pdone.com/web/bundles/pdone/media/qlv_veeva/brand/presentation/2014_Quillivant_Downloadable_Letter-icon.jpg",
            "session_presentation_id": "20150330073847782685415",
            "media_units": "Pages",
            "brand_id": 11,
            "media_expiration_timestamp": 457187927.50113
        }
    ]
}

I've read oficial docs and also this and this post here at SO looking for something helpful but can't find the proper docs for query the collection and get documents from 7 days ago only by using share_date_2. Can any give me some advice here?


texts: How to accept lock request using intent locking?

texts: No matter how much I try, I do not understand compatibility matrix of intent locking. Why were SIX and IX denied if x lock has been held? Suppose x lock holds for record r1 then taking IX just means that some record in the table held X lock? If so, I think it should not be denied. Why is IX denied if S lock has been held?  Why is IX allowed if IS has been held? Why IS is allowed if IX has been held?


texts: Postgres 11 declarative partitioning by relative time

texts:
I'm working on a system that uses PostgreSQL 11 as it's main data store, and I'm looking at how to most efficiently query a large table of events (up to 120 million rows, so not 'big data' big, but not exactly tiny), mainly based on time ranges. In particular, events will be searched across these preset time periods:

Last 1 hour
Last 4 hours
Last 24 hours
Last 7 days
Last 30 days

I was immediately drawn to the declarative partitioning capabilities of Postgres 11, but I've never used it before.

All of the examples I've seen use static values for partitioning, such as FOR VALUES FROM ('2018-02-01') TO ('2018-03-01'), which isn't going to work without a background task to manage partitions - so I have a couple of questions:

Can rows overlap multiple partitions? For example, an event that occurred 3 hours ago fits the criteria for the first 2 time periods above
Can rows be partitioned by relative time periods, such as 'last 1 hour'?


texts: Linked-server query against remote DB does NOT fail when DB is in SINGLE_USER mode

texts:
This is really strange to me.  I have a primary server Foo2005 and it has a linked-server entry pointing to Bar2008R2; say the name of the linked-server is BarLINK.  I write a query such as SELECT * FROM BarLINK.MyDB.dbo.Stuff.  The catch is, over on Bar, MyDB is in SINGLE_USER mode.  Why would this query still succeed?  I would expect to get the error like "Could not open the specified database since it's in SINGLE_USER mode and is already open by a user" (forgive me for not remembering the exact phraseology).

Notes:

Linked-server is created using a SQL login/pwd, say readonly, which, as the name implies, has READ permissions on the target server & DB.
Testing the query when MyDB is in MULTI_USER mode seems "faster" than when it runs & MyDB is in SINGLE_USER mode, but I don't have any concrete metric of that, just the observation/feels.
As the names indicate, server Foo2005 (the "local" server where the query lives) is SQL 2005, while server Bar2008R2 (the "remote" server where the query is being sent / where MyDB lives) is 2008R2.

Any ideas?


texts: Selecting a record based off conditions CLOB

texts:
I have a table with a column storing JSON data as a CLOB. The JSON has a 'miscData' field that is made up of an array of JSON objects. Each object has 2 keys which are always the same, but the values are different.
.
.
.
"otherKeys" : "otherValues",
"miscData": [
        {
            "miscType": "date",
            "miscText": "2020-07-09T10:01:10.450Z"
        },
        {
            "miscType": "Comment",
            "miscText": "Comment body"
        },
        {
            "miscType": "CORRECT_TYPE",
            "miscText": "SELECT_ME"
        }
    ],
"confirmationNumber" : "123456789qwerty",
.
.
.

I need to
SELECT
    CLOB_COL.miscData.miscText
WHERE
    CLOB_COL.miscData.miscType = 'CORRECT_TYPE'
AND
    CLOB_COL.confirmationNumber = 'xxxx';

But I am having trouble since miscData is an array of objects which all contain miscType and miscData keys and I can't guarantee the array will be the same size, or in the same order.


texts: ASPState database locking and growth problems

texts:
We use an ASPState database to persist .NET Session state on a SQL Server 2005 cluster. We are seeing some strange behavior during peak periods

The DeleteExpiredSessions proc is run every minute via an agent job. Sometimes this job is taking many minutes to run and delete expired sessions
Requests from the application to the ASPState database are very slow. I believe this is because there are exclusive locks being held on the table by DeleteExpiredSessions procedure

Code:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[DeleteExpiredSessions]
AS
        SET NOCOUNT ON
        SET DEADLOCK_PRIORITY LOW

        DECLARE @now datetime
        SET @now = GETUTCDATE()

        DECLARE @tblExpiredSessions TABLE
        (
            SessionID nvarchar(88) NOT NULL PRIMARY KEY
        )

        INSERT INTO @tblExpiredSessions (SessionID)
            SELECT SessionID
            FROM [ASPState].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
            WHERE Expires < @now

        --EXPIRED SESSION LOGGING
        /*
        DECLARE @ExpiredSessionCount Int;
        SELECT @ExpiredSessionCount = COUNT(SessionID)
        FROM @tblExpiredSessions;
        */

        IF @@ROWCOUNT <> 0
        BEGIN
            DECLARE ExpiredSessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
            FOR SELECT SessionID FROM @tblExpiredSessions ORDER BY CHECKSUM(NEWID())

            DECLARE @SessionID nvarchar(88)

            OPEN ExpiredSessionCursor

            FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID

            WHILE @@FETCH_STATUS = 0
                BEGIN
                    DELETE FROM [ASPState].dbo.ASPStateTempSessions WHERE SessionID = @SessionID AND Expires < @now
                    FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
                END

            CLOSE ExpiredSessionCursor

            DEALLOCATE ExpiredSessionCursor

        END

        --EXPIRED SESSION LOGGING
        /*
        BEGIN TRY
             INSERT INTO DeleteExpiredSessionLog(RunStart, RunEnd, ExpiredSessionsDeleted)
             VALUES (@now, GETUTCDATE(), @ExpiredSessionCount);
        END TRY
        BEGIN CATCH
             --SWALLOW ANY EXCEPTION
        END CATCH;
        */

    RETURN 0

The log file is filling up and in some cases forcing autogrowth, despite the db being in simple recovery

In addition to these, a trace reveals multiple requests for the same session coming in quick succession. For example exec dbo.TempResetTimeout @id=N'32gghltsuoesnvlzbehchp2m2014c0f1' 14 times in <1 second, so we're wondering about this as a cause or congestion, but not sure of the root of this behavior.

Any suggestions or explanation around this behavior would be appreciated.


texts: Select ONLY Max(date) when there's repeating dates

texts:
Here is a sample of the data contained in my Tracking table:

tracking_id   date_action  action_id    staffing_id
------------- -----------  -----------  -----------
 1             2019/03/04  4             2
 2             2019/03/04  3             2
 7             2018/10/25  67            3
 4             2018/10/25  8             3
 5             2019/05/05  10            5
 6             2019/05/05  6             5
 8             2019/03/02  55            6

The result I want is the following:

  tracking_id   date_action  action_id    staffing_id
    ------------- -----------  -----------  -----------
     2             2019/03/04  3             2
     4             2018/10/25  8             3
     6             2019/05/05  6             5
     8             2019/03/02  55            6

I need to find the max(date_action_taken) group by staffing_id and join it with other tables to get the columns in the Action table using action_id and all of the columns in the Staffing table using the staffing_id.

I first try to get the the max date in the Tracking table for each staffing_id:

SELECT staffing_id, Max(Tracking.date_action_taken) AS MaxOfdate
FROM Tracking
GROUP BY staffing_id

When I join the query above back to the same table to get the other colmns of the Tracking table. It still returns the max(date_action) but it also returns the repeated ones. The query I used is below:

Select  tracking_id, t2.mxdate, t1.action_id, t1.staffing_id
FROM Tracking t1
inner join
(select  max(date_action) as mxdate, staffing_id
From Tracking
Group by staffing_id
) t2
on t1.staffing_id = t2.staffing_id and t1.date_action_taken = t2.mxdate

I even tried another query to get the max(tracking_id) instead using this query:

Select Tracking.*
From Tracking
Where tracking_id IN
(Select Max(tracking_id) as t_id
From Tracking
Group by staffing_id
)

This works great until I realize that the max(tracking) is not necessarily the max(date_action).

I even used DISTINCT. And it still returns the repeated max(date_action).

Select  DISTINCT tracking_id, t2.mxdate, t1.action_id, t1.comment, t1.staffing_id
FROM Tracking t1
inner join
(select  DISTINCT max(date_action_taken) as mxdate, staffing_id
From Tracking
Group by staffing_id
) t2
on t1.staffing_id = t2.staffing_id and t1.date_action_taken = t2.mxdate

I am not sure what to do. Is there a way, I can first get the max(date_action) by staffing_id and if two of them are repeated to get the max(tracking_id) between the max(date_action).

I am thinking of adding another column to act as a "flag". A data type yes/no, column named (isTheLastAction) so on each insert in the tracking table. It find  the previous  isTheLastAction, put it at false, and the new insert to true within the same staffing_id that way, it doesn't depend on the date_action nor the tracking_id. I am just not sure how to do that.

Any thoughts on either helping me solve the max(date_action) or using the flag on insert?


texts: Why securityAdmin does not have enough permission?

texts:
I am a server securityAdmin.

The securityAdmin role should be treated as equivalent to the sysadmin role, according to Microsoft, but I failed to do below 2 things

make myself as sysAdmin
grant myself write right to a database. (currently in db_denydatawriter role )

Did I miss anything?

Thanks

UPDATE

per RDFozz's comment, I was able to add a new 'dummy' login, but failed to set this login as a user of this database.

UPDATE 2 -- final script that works!

sepupic's answer is great! following his instruction, here is the SQL.

first, use myLogin to create a new super_user.

CREATE LOGIN [super_login] WITH PASSWORD=N'yourPassword'
GRANT CONTROL SERVER TO [super_login]
Grant IMPERSONATE ON LOGIN::[sa] TO [super_login]

then login with the super_login, run this.

Execute as login = 'sa'
  ALTER ROLE [db_denydatawriter] DROP MEMBER [mylogin]
  --ALTER ROLE [db_owner] add MEMBER [mylogin]
  ALTER server ROLE [sysadmin] add MEMBER [mylogin]
Revert


texts: How can I migrate a sybase database to mysql?

texts: I've been tasked with migrating a fairly complex legacy Sybase database into a MySQL database and I have no idea where to start.  I can't use any off the shelf software, so I'm going to have to write some sort of script that takes all the info out of the Sybase database and puts it into MySQL.  Does anyone have any resources or thoughts on how to do this quickly, as I have very little experience with Sybase or MySQL.


texts: Best way to get SQL Server databases on small capacity VPS

texts:
I have several small sites and I've just rented a Windows VPS server with 1&1. It includes 2 "vCores", 4 GB RAM and 120 GB SSD.

It runs great for my needs but now I need to determine the best way to get several SQL Server databases running.

Questions:

Would SQL Server Express kill such a configuration, as far as using up my RAM and bandwidth?
I wasn't able to find anyone who cost effectively would rent me just some space on a SQL Server database. Are there any other options short of either purchasing another VPS to house my database?

I realize some will scoff at this as being woefully inadequate. But my needs are not that significant and my budget for this is rather small.


texts: MySQL version of split_to_array or split_to_table?

texts:
PostgreSQL supports two String Functions, that are incredibly useful

regexp_split_to_array
regexp_split_to_table

Both of these functions essentially take two strings, (a) a pattern and (b) an input string and return either an array[] or set of row types.

This is great for problems like this. MySQL has a JSON type, and version 8 even brings binary JSON. Does it support any string splitting into a JSON_ARRAY or the like. Or, is there an easy method to implement this?


texts:
Shortly I will be migrating a 2008 R2 SSRS installation to a new SQL 2019 Always on AG in AWS.  This one will contain subscriptions, and I have never dealt with them before.  I read the documentation here: SSRS and AG's
and it states that the agent jobs that control the subscriptions are automatically recreated after a failover on the new primary, but still exist on the old primary.
For my other SSRS migrations, I wrote code that automatically takes care of restarting SQL agent and SSRS services on the appropriate nodes after a failover to use the new node.  I have the same jobs on all replicas, and I also have a job step in them that checks to see if the server is the primary.  If it's not, the job will not run.
Since my jobs are already synced and have controlled execution based on primary replica status, id rather use that to control the subscriptions, but will SQL overwrite the existing jobs for the subscriptions?  Or will it only add jobs that are not there?  I don't have a server to test this on at the moment, and I can't find this specific situation online.  I'd rather have the jobs always there, and let the job logic run the jobs based on the replica state.  Is that possible with subscriptions, or do I need to always disable the jobs on the old primary, and let SQL recreate the jobs on the new primary?
I'd also like to rename the sql agent jobs from their nonsense GUID to something more intuitive.  Would that mess with the jobs executing if I did that?


texts: MySQL - Enabling Scheduled Event on Master and Slave Simultaneously

texts:
This may seem a bit strange, but I am trying to get a Scheduled Event to execute on both Master and Slave.

I have two databases set up in a Master (A) to Master (B) replication environment.

Master A is READ_ONLY=OFF

Master B is READ_ONLY=ON

I create a user on both Databases:
GRANT INSERT, EVENT ON test.* TO 'user'@'localhost' IDENTIFIED BY 'Password';

I then create my Event on Master A:

DROP EVENT `e_test`;
DELIMITER $$
CREATE DEFINER=`user`@`localhost`
EVENT `e_test`
ON SCHEDULE EVERY 1 MINUTE
STARTS NOW()
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Adds a new row to test.tab1 every 1 minute'
DO
    BEGIN
        INSERT INTO test.tab1 (`fname`) VALUES (NOW());
    END;
$$

So far so good. It executes every minute, and adds an entry to the table, which replicates to the other Database.

However, on the Master B, it is marked as SlaveSide Disabled, and so doesn't execute.

If I do:

ALTER DEFINER=user@localhost EVENT e_test ENABLE;

on Master B, it starts to execute on Master B, but on Master A it is now flagged as Slaveside_Disabled, and so doesn't execute.

If I then enable it on Master A, Master B is Slaveside_Disabled.

The reason for wanting this (in case you were wondering), is so that as part of my failover script I simply need to execute SET GLOBAL READ_ONLY = { ON | OFF } on each database accordingly,
as opposed to having to enable / disable all my events (one command vs many commands).

Under normal circumstances, on Master A (READ_ONLY=OFF) the events execute as per normal and adds the entry; On Master B (READ_ONLY=ON) the events execute, but don't insert an entry as they don't have permission.

I looked at using SET GLOBAL EVENT_SCHEDULER = { ON | OFF } as the one command, but if I set it to OFF as the default, then we need to remember to enable it each server restart.
Alternatively if we set it to ON as the default we need to remember to disable it every server restart.
The use of READ_ONLY seemed a better option as it can be easily included in a failover script.

Any ideas?


texts: What are the benefits vs. drawbacks to using a clustered index in SQL Server

texts: I have scenario where the data will be loaded pretty much in the order it will be grouped by in reports (date, varchar foreign key, int foreign key).  Is there still a benefit to indexing?  I was going to define the run date as the clustered index, and index the two foreign keys.  Since the data will be loaded via a process that will already have the data sorted, I wasn't sure if the benefit to the indexes still outweigh the overhead?  I am really new at this.  The table will probably be defined with a total of 4 fields, a max of 22,000,000 rows per run date, and retained for 60 days.


texts:
select
convert(varchar(10),  TotalSeconds / 3600) +':'+
convert(varchar(10),   TotalSeconds % 3600 / 60) +':'+
 convert(varchar(10),  TotalSeconds % 60) as Seconds
from
(select
  DateDiff
(second, date,outtime )
 as TotalSeconds
    from attendance.attn_card_register) x -- this one


texts: What is the best way to keep track of all changes implemented on a database?

texts:
I am a new DBA to a company and in general. I am planning to do some changes to a database settings such as backup compression, enable some trace flags, create more files in TempDB ets.
My question is what would be the best way to keep track of all changes and new implemented settings So it can be easy accessible?
And so people after me can easy access and see what is the current condition of the database?
Is there maybe special software for that? Or common way that all DBA's are follows?
I just want to know how to record everything you did on a database? i can write everything down on a piece of paper, word doc, excel or maybe there is a better way of doing that?
Thank you


texts:
I have created a web site using VS 2013 that uses T-SQL queries to execute SSIS packages (created in VS2012). This seems to work however a couple of issues have come up. I must confess this is the first time my organization has attempted to use these 3 "system" together. So, we may be making some architectural mistakes.

1) The ETL package reports success, but doesn't logically complete. For example a lookup may error may occur so all the records are shuffled off to a text file. However, the because the ETL completes the SQL query believes it completes successfully and so doesn't throw an error to the web page.

2) The ETL package has an error but the web page reports successful completion.

A concern is that once the SQL query calls the SSIS package, the SQL query and SSIS package executions become parallel instead of sequential so the SQL query completes and reports success whether or not the SSIS package has actually completed execution. So, then question becomes how can I determine if the SSIS package has successfully complete exiting from the web page?

Regards, Tom


texts: Copy Specific tables from one database to another database on the same server

texts:
I have 2 databases that are identical. I make a backup of DB1 and restore as DB2.
2 days later I want to copy 5 tables from DB1 into DB2. All tables start with 'AG'. Is there a simple way of doing this? I would like to do this in a T-SQL script so I can do this often and easily.


texts: Backing entire Postgres database server up with pg_basebackup

texts:
I back Postgres DB 9.1 up using following command:

pg_basebackup -h 127.0.0.1 -U replicationuser -D /storage/test_backup -Ft -z -P

In postgresql.conf I have this set:

wal_level = hot_standby

However, at the end of pg_basebackup run I get this warning:

  NOTICE:  WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup

I care about the backup of data from time of starting pg_basebackup only (I can afford a bit of downtime in the night, so I shut down the app server that is using this PG instance as backend DB and then start the backup). I do not need nor aim to have "continuous backup".

Question: do I really have to copy the WAL to have complete backup from the time of starting pg_basebackup command? Or is that warning designed strictly for purpose of achieving "continuous archive" functionality?


texts: Oracle sqldeveloper Database Diff - not seeing indexes/sequences

texts:
I am using Oracle SQL Developer's database diff tool to compare schemas.  (SQL developer version : 3.2.09)  It works great on some environments, but in one, I can only see differences in tables - the sequences and indexes are all treated as if they are new and not compared against existing ones.

I've confirmed the indexes exist in the target environment, and that my user account should have permission to see them - I have both "select any dictionary" system privilege and the "select_catalog_role" role.  Further, I do see the indexes if I explicitly query "all_indexes" and "all_ind_columns" tables.

Any ideas what permissions I might be missing?  Again, this works on another environment.


texts: How to design a database for storing phone attribute?

texts:
I'm trying to capture following attributes :

Name,Company,Price,Size,Weight,Release date,Gms,No of sim,Camera details,SIM details,Screen size,Screen type,Processor and Ram details and all..

Any lead how to proceed?

Always I'm going to fetch data once entered.
Rarely updates are going to be performed.
All attributes to be fetched once only.
Number of attributes exceeds 40 and  all are tinytext.


texts:
Given the following table

CREATE TABLE `sms` (
    `_id`   INTEGER,
    `thread_id` INTEGER,
    `address`   TEXT,
    `person`    INTEGER,
    `date`  INTEGER,
    `protocol`  INTEGER,
    `read`  INTEGER DEFAULT 0,
    `status`    INTEGER DEFAULT -1,
    `type`  INTEGER,
    `reply_path_present`    INTEGER,
    `subject`   TEXT,
    `body`  TEXT,
    `service_center`    TEXT,
    PRIMARY KEY(_id)
);

I'd like to delete all rows where address and body are identical. In other words, eliminate dups based on address and body, leaving only the first occurrence.


texts: Why B-tree indexing is used instead of hash based indexing?

texts:
I have an employee table which has index on name. I want to understand how Oracle works at high level

My understanding based on resources mentioned below:-

DB constructs the index table/map where it will keep the name in sorted fashion with memory location as the value.

Question :-

My question is will oracle bring all index table/map entries in memory from disc and then do the binary search  or is it the search at disc level without bringing all index table records in memory?
If I compare this approach with Java based data structures, Oracle is maintaining the TreeMap of column where key is employee name and value is memory location of that entry. So to find any entry, look up time will be log(n). My question why not DB/oracle takes hash based approach where it keeps the hashtable where it calculate the memory location based on name value and put the entry there. So lookup time to find entry will be O(1) .

Resources I referred

"Indexes and Index-Organized Tables" from the Oracle manual
How does database indexing work


texts: How to use Common Table Expression properly - Example where it seems to be very slow in TSQL

texts:
I've been starting using common table expressions more and more and thought I was using them "right" but I've run into an example where the solution with CTE is very slow compared to using a temp table. I will put the two examples below, the first one using common table expressions takes a very long time to run (3min 27sec) and the second one using temp tables which only takes 0:50. Is this an inappropriate way to use CTE or am I implementing it poorly?

;WITH tmpRiskFactor AS  --Super slow. 3:27
(SELECT col1,col2,col3,col4,col5 FROM [tablevaluedfunction]() )
(SELECT DISTINCT col1 AS [RiskFactors] FROM tmpRiskFactor
UNION
SELECT DISTINCT col2 FROM tmpRiskFactor
UNION
SELECT DISTINCT col3 FROM tmpRiskFactor
UNION
SELECT DISTINCT col4 FROM tmpRiskFactor
UNION
SELECT DISTINCT col5 FROM tmpRiskFactor
) AS tmp;

Using temp:

SELECT col1,col2,col3,col4,col5 INTO #tmpRiskFactor FROM [tablevaluedfunction]() --A lot faster. 0:50
SELECT * INTO #RiskFactors FROM
(SELECT DISTINCT col1 AS [RiskFactors] FROM #tmpRiskFactor
UNION
SELECT DISTINCT col2  FROM #tmpRiskFactor
UNION
SELECT DISTINCT col3 FROM #tmpRiskFactor
UNION
SELECT DISTINCT col4 FROM #tmpRiskFactor
UNION
SELECT DISTINCT col5 FROM #tmpRiskFactor
) AS tmp;


texts: Connect to SQL Server 2012 running on Azure VM via local SSMS 2012

texts:
I have been testing VM on Azure. I have created a SQL VM running SQL 2012 on Windows 2012 and would like to connect to it via SSMS 2012 on my local instead of connecting via RDP through Azure Portal.

Thanks!


texts:
I want to make a gaming website for Magic the Gathering to report event results and management user decks.

Here's what I have so far.

Event is a tournament with Players competing in the tournament.
Players have Decks, which is a like deck of cards (in this case 60 Magic the Gathering cards).

So, the Decks table has these columns with foreign Ids related to the Events and Players table:

event_id
player_id

However, I also want the users to have Decks. Users are different from Players. Users log into the site. Then, they create and edit Decks.

Should I just create one Decks table or have a 2nd Decks table like UserDecks?

If I have one Decks table, it would have these columns:

event_id
player_id
user_id

However, the event Decks would not have user_ids and the user Decks would not have event_ids or player_ids. Is it okay to have null in columns with foreign Ids?

I could just do a Decks table for event decks and a UserDecks table for user decks but it doesn't seem right to have two Decks table that are the same except for some different columns with foreign Ids?


texts: Pros and cons of using many schemas in PostgreSQL as opposed to just one?

texts:
For a large SAAS application (backed by PostgreSql 9.4), with over 300,000 accounts (and growing), what are the pros and cons of using a schema per account to partition the data vs putting all the data in one schema and using foreign keys to partition it in the queries?

I know in the past pg_dump was painfully slow when working with many schemas but not sure if that is the case today. I'm also aware any change in the database structure will have to be done on all the schemas. And I know that on the plus side, moving a schema from one physical server to another is easy, as well as restoring a schema from backup, not to mention it makes sense to partition data that way.

So what are the pros and cons I'm missing?


texts: How do you restore (or check) only the extended properties in a MS SQL Server .Bak file?

texts:
We store the version number of our DBs as an extended property. As part of the automated build process, I want to be able to check that the "empty" DB that is part of the build set matches the version number of the executables being built.

I understand that I can't just read the *.bak file directly. I already query the (restored) DB to get that property. However, as part of the build process it seems silly to restore the entire DB just to get this one piece of information.

Is there a simple way to get just the extended properties without restoring the whole DB?


texts:
I have a 64-bit Windows 7 machine with an Oracle 11g version installed. I want to install the Oracle 18c version on the same machine.

Is it possible? If yes, what are the configurations I need to do for them (11g and 18c) to coexist?

Thanks


texts: Deleting old rows when a new one is added and there are more than 100 rows

texts:
For a school assignment we need to keep the number of rows in a mysqldb under 100. The newest rows must stay. I've tried using a trigger but with no success.

CREATE TRIGGER delete_rows
AFTER INSERT ON `check`.log
FOR EACH ROW
BEGIN
   IF(SELECT COUNT(*) FROM `check`.log)>100
   THEN
      DELETE FROM `check`.log LIMIT 1;
   END IF;
END

This resulted in the following error:

ERROR 1442 (HY000): Can't update table 'log' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Then I tried monitoring /var/lib/mysql/check/log.ibd with incron and let it execute a script. when opening log.ibd with nano, incron executes my script but when I use mysql to insert something into it, nothing happens.

The incron instruction:

/var/lib/mysql/check/log.ibd IN_ALL_EVENTS /etc/scripts/test

can somebody please help me.


texts: sql transactional replication - slow renitialization / slow bulk insert

texts:
i recently set up sql transnational replication and i have some performance issue
when i'm initializing/re-initializing it from snapshot.
The biggest table have is about 8gb as snapshot file
network connection is fine - when im copying (just to check network speed is not the case) this snapshot file into desktop of this subscriber machine then it is around 200 MBps.
When this source machine witch is distributor also is bulk inserting this table into subscription database then CPU on this subscriber machine almosts sleeps , batch requests is between 0 and 1 and DATABASE IO is 4 MBps max...
Why it is going so slow ? is there any way to speed this up ? 8gb table will be +- 30min what if i will have 50gb table some day?
Can it be the case that the publisher machine is distributor also ? and because of that there are some network bulk insert commands limitations ?
It is how i think that in that case that the distributor is actually opening this snapshot that he generates before and doing this bulk insert like thru linked server in that case?
On my destination recovery models is simple so it is not this also.
thanks and regards!


texts: I am part of a project to convert an old AS400 system to SQL server.  The server is used for our payroll processing.  Part of the payroll process is to break out the percentage of the biweekly wages to each job we work on.  To do so in the AS400 process a csv file was loaded from the P&B admin's to the server in a data access area that only the admin had access to.  I need to replicate this in the SQL server environment.  I am planning on using SQLCMD to execute an import of the local file into a temporary table.  I know it is highly unlikely that anyone would query the data in the temporary table at the time its in use but is there anyway to grant only access to the user that executed the SQLCMD script?


texts:
Recently I have added binary logs to mysql to start replication but after that setting, mysql process failed to start and keep crashing constantly. In mysql error logs some messages are there but not guiding in any direction towards resolution.

replication setting on master

server-id              = 1
log_bin                = /home/ebsbkdrive/mysql_repl_data/mysql-bin.log
expire_logs_days       = 10
max_binlog_size         = 100M
binlog_format           = ROW

mysql version

mysql  Ver 14.14 Distrib 5.5.32, for debian-linux-gnu (x86_64) using readline 6.1

Platform

Distributor ID: Ubuntu
Description:    Ubuntu natty (development branch)
Release:    11.04
Codename:   natty

error that reported in error logs,

150401  9:28:22 [Note] Plugin 'FEDERATED' is disabled.
150401  9:28:22 InnoDB: The InnoDB memory heap is disabled
150401  9:28:22 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150401  9:28:22 InnoDB: Compressed tables use zlib 1.2.3.4
150401  9:28:22 InnoDB: Initializing buffer pool, size = 10.0G
150401  9:28:23 InnoDB: Completed initialization of buffer pool
150401  9:28:23 InnoDB: highest supported file format is Barracuda.
150401  9:28:25  InnoDB: Waiting for the background threads to start
150401  9:28:26 InnoDB: 5.5.32 started; log sequence number 269850279391
09:28:26 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

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

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x29)[0x7f9e7008f349]
/usr/sbin/mysqld(handle_fatal_signal+0x483)[0x7f9e6ff54f33]
/lib/x86_64-linux-gnu/libpthread.so.0(+0xfcb0)[0x7f9e6ec9fcb0]
/usr/sbin/mysqld(_ZN9MYSQL_LOG17generate_new_nameEPcPKc+0x9b)[0x7f9e6ffe87ab]
/usr/sbin/mysqld(_ZN13MYSQL_BIN_LOG4openEPKc13enum_log_typeS1_10cache_typebmbb+0x204)[0x7f9e6ffec5a4]
/usr/sbin/mysqld(+0x284e21)[0x7f9e6fdbde21]
/usr/sbin/mysqld(_Z11mysqld_mainiPPc+0x59b)[0x7f9e6fdc0f3b]
/lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xed)[0x7f9e6e2f676d]
/usr/sbin/mysqld(+0x27e2c5)[0x7f9e6fdb72c5]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

please check provided information and help me to resolve the issue.


texts: Adding around 200 rows in a table grows size of the table by 400kb where avg row size is 0.2KB

texts:
We are running into an issue where if we look at the avg row size (0.2kb) x number of rows (200) inserted in one of our table, the size should grow by around 40 KB.  However the total used space has increased by around 400 KB.  I believe there is additional index size to be considered.  However it still should not show this big difference. What possibly should we look at to understand this difference and what is happening?

This is happening with couple of our tables which only do inserts related to data collected from a third party system.  It does not do any updates on any rows.  Also we have an index on the table on the primary key which is an ID (number) field.


texts: Combine rows with instruction from separate list

texts:
Is there a way to combine (SUM) rows with unique ID (column) numbers by specifying the ID numbers in a list and then make MYSQL combine rows in a table according to the specification in the list?

The data in the database I use change frequently changed but the customer ID that needs to be combined remains the same so it would be great if there was a a way to have the list in a separate file/table or similar to read form when I re-create the database with new values.

For example I want to combine ID 112 and 115 when I recreate the database to ID 112 with value 1 equal to 9 and value 2 equal to 12 (the sum of 112 and 115).

ID | VALUE 1  | VALUE 2
------------------------
...
112| 2       |5
113| 5       |6
114| 6       |6
115| 7       |7
...

To new table:

ID | VALUE 1  | VALUE 2
------------------------
...
112| 9       |12
113| 5       |6
114| 6       |6
...

This is sort of what I am looking for but is there a way to read the ID's from a list with several values? For example ( 112,115), ( 449,622), ( 123,423,321) etc.?

Something like:

select min(id) as newId,
sum(value_1) as newValue1,
 sum(value_2) as newValue2
 from myTable t

where id in list;

How would the list need to be formatted?


texts: Where to put my.ini file for 5.6.21 MySQL Community Server on Windows

texts:
I have copied the C:\Program Files\MySQL\MySQL Server 5.6\my-default.ini to C:\Program Files\MySQL\MySQL Server 5.6\my.ini and changed some settings including setting table_definition_cache=400.  When I log into the database and look at the table_definition_cache value I get the following:

mysql> SHOW GLOBAL VARIABLES LIKE '%table_definition_cache%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| table_definition_cache | 1400  |
+------------------------+-------+
1 row in set (0.05 sec)

Where do I need to put my my.ini file such that the database loads it on startup?


texts: Installing SQL Server 2016 for Azure development

texts:
Coming from a dev background, I'm not an expert in SQL Server. When installing SQL Server 2016, I have the option of installing three different components:

New SQL Server stand-alone installation
SQL Server Management Tools
SQL Server Data Tools

I'm only interested in SQL Azure development with Visual Studio. The question is should I only install the last two items on my local machine? Do I need the full-blown SQL Server 2016 from the first item?


texts:
I have a query that executes against a table that can grow to millions of rows.  The query comes out of a QA tool that we use that is outside the standard functionality of the DB (as far as what is indexed and how and why).  The query is:
SELECT id FROM thisTable t
WHERE col = 'val'
AND ((not exists (SELECT 1 FROM thisTable WHERE refid = t.id) and refbool = 0) or refbool = 1)
ORDER BY newid()

Basically, let's say the table has id, refid, refbool, and col columns.  So you could have data as follows:
  id  |  refid  |  refbool  |  col
------------------------------------
   1  |   NULL  |    0      |  val
   2  |   NULL  |    0      |  val
   3  |   NULL  |    0      |  val
   4  |    2    |    1      |  val
   5  |   NULL  |    0      |  val
   6  |    1    |    1      |  val

The query should never pick the rows for id in (1, 2) because they are referred to by other rows.  It should only grab rows where refbool = 1, OR refbool = 0 AND that row's id is not any other row's refid.  This statement is horribly non-performant, but I'm not sure what a better query would look like for this.  Assume that no indexes, views, stored procedures, or other underlying machinations can be added - it must be a query.
The overall query is significantly larger, JOINS to two additional tables and gathers quite a bit of data.  However, I've narrowed it does to this particular bit as commenting out this line takes the query execution time from 16s to <1s.
I'm also reordering the rows by newid() as I need to randomly select a sample item.  Removing the ORDER BY also make the query significantly faster even leaving the third row in.  It appears that the two operations combined causes the slowness.  I've tried designing a CTE, but have failed to increase performance doing so.
I've looked at the execution plan. There are indices that would be added that would improve this query. However, performance of internal QA tools do not take precedence over performance in client production environments, and making changes to the structure in a QA environment for the utility in relation to indices, etc. invalidates it's usefulness as a QA environment as it will likely perform differently than a production environment.
I could certainly write a query that would perform worse than my current query by changing the logic of the query itself. I'm sure we all could. I'm asking to apply that sort of reasoning to improve the performance of the query instead.


texts:
I recently had an incident that got me rethinking my backup plan: One of our MSSQL driven aplications stalled because the log fle had grown too big on the filesystem (it was 50 GBs IIRC).
It turned out that the aplication developer is setting the DB recovery method to 'Full', and the full backup nighlty routing does not purge and shrink that log file. In order to shrink it  had to backup it up, change the recovery method to 'Simple', and backup with shrink.
I obviously want to prevent that from happening, so I am looking for options.
This is an MSSQL 2012 Express x64 instance.
The application developer is totally non-cooperative (external contractor, works against everyone) so I am assuming he wouldn't want me to leave the DB in 'simple' mode.
I could easily tune my sqlcmd scripts to do a "backup in 'full' -> switch to 'simple' -> backup in 'simple' -> revert to 'full'" and keep the file at bay, but I fear I could be running some risk I am not foreseeing right now.
It seems to me that I should do this relatively often, as the larger the log file grows the more time it takes to backup and shrink, causing noticeable slowdowns.
If that routine is reasonable, and after each cycle I end up with 'a backup prior to purging the log' and 'a backup after the log was purged', and assuming both proceses exit without error, would it be wise to discard the first one? I would be keeping the 'data' backup  and the 'purged log' backup.
I should add that AFAIK by doing "full backups" but not "regular log backups" I am sacrificing the possibility to revert to "states between two full backups", but I am not hindering the full backups themselves, as I have restored and cloned them many times in the past without issues. But if this is wrong please do let me know.
Note: For simplicity's sake let's say that -for now- being able to revert to a nightly full backup is good enough.
I hope I was able to explain myself.
Thanks in advance.


texts: error 1118: row size too large. utf8 innodb

texts:
I have tried to find a solution guys, and I am guessing that someone has met this before.  But I cannot get hits down below 5000... most of which don't match my question.

Using MySQL 5.6 - windows (both XP and Win7 - same issue)

I have a utf8 mysql schema with the following structure (field names removed)
The schema was built just using standard tool - I did not choose compression nor anything else.

f1  varchar(5)
f2  varchar(255)
f3  varchar(255)
f4  varchar(255)
f5  varchar(255)
f6  datetime
f7  datetime
f8  longtext
f9  varchar(255)
f10 varchar(255)
f11 longtext
f12 varchar(255)
f13 varchar(255)
f14 varchar(255)
f15 longtext
f16 longtext
f17 longtext
f18 longtext
f19 longtext
f20 longtext
f21 longtext
f22 longtext
f23 varchar(255)
f24 varchar(255)
f25 varchar(255)
f26 longtext
f27 longtext
f28 varchar(255)
f29 varchar(255)
f30 longtext
f31 longtext
f32 longtext
f33 longtext
f34 longtext
f35 longtext
f36 longtext
f37 longtext
f38 longtext
f39 datetime
f40 datetime

This works fine, until I come to a record with a lot of text in a lot of the  longtext fields.  Say I have inserted values into 6 of the longtext fields, but when I try to paste in values into (say) the 7th, I get the 1118 Error.

Now, I am a bit puzzled as this data is going into a text field which should not contribute to the row length as it is stored outside the row (barring the 12 bytes used to define the entry).  Using my best guess for the row length of the structure above it comes to only 11009 bytes (give or take), well within the 64K limit.

This is proving frustrating as I can't really understand why it fails.  I could break the table down into multiple 1-to-1s, but that seems hackneyed, and I could take out the text fields and create a related table with foreign key and variable name and the associated text (using row modelling for this element) - but again, that is just rod for my back later.


texts: Oracle: How to find non-local (background) processes in Windows Machine

texts:
Following link advises to find non-local background processes and kill them using following bash script.

  Solution 1) : without shutdown anything just kill that database
  processes which are not local processes. local means background
  processes.

ps -ef | grep $ORACLE_SID | grep -v grep | grep "LOCAL=NO" | awk '{print $2}' | xargs kill -9

I read this script as.

ps -ef ,find all processs
pipe it to grep
filter $ORACLE_SID, that is find $ORACLE_SID lines, pipe it
filter out grep's line
filter "LOCAL=NO"
use awk to print column 2, that is process id
use xargs kill command to kill this processes.

This solution obviously in linux/unix since it uses the fact that "Oracle uses process architecture in linux/unix" and bunch of other unix commands. But Oracle in windows uses thread architecture.

Is there any equivalent in Windows Box, graphical or otherwise? I am aware of Process Explorer and I will look into it. But I wanted to ask here first.

To re-iterate I am not asking how to kill in windows box (orakill) but how to find session/thread in Windows using command line tools without logging in database so that I can use orakill.


texts: Applying indexes on columns used in join condition

texts:
In the query below,how beneficial it is to use index on columns used in join conditions?

select * from table_a
inner join table_b on
           table_a.col1 = table_b.col1 and
           table_a.col2 = table_b.col2

In above join, is it beneficial to index table table_a(col1,col2) or table table_b(col1,col2) and does order of columns in index impact performance?

If suppose there is an index on table_b(col1,col2). Does it mean we need to change the query as follows?

select * from table_a
    inner join table_b on
               table_b.col1 = table_a.col1 and
               table_b.col2 = table_a.col2


texts: How I can check whether 2 databases despite being in different postgresql servers of versions are the same?

texts:
I copied an existing posttgresql database from one server to another. The source of my data is a postgresql 9.6 whilst the newer server is in postgresql 11.
So I want to know how I can check that both databases have the same schema and data in a fast and easy way?


texts:
Hi i have a shell script for doing a selected databases backup.
Now my requirement is i wanted to take a particular tables in a database to be backed up.
Each database backup need to be created in seperate file. The script is

#!/bin/bash

MyUSER="user"       # USERNAME
MyPASS="pw"         # PASSWORD
MyHOST="localhost"  # Hostname

# If cleanup is set to "1", backups older than $OLDERTHAN days will be deleted!
CLEANUP=1
OLDERTHAN=60

# Backup Dest directory
DEST="/backup/mysql"

# Directory, where a copy of the "latest" dumps will be stored
LATEST=$DEST/latest

# Get hostname
HOST="$(hostname)"

# Get data in dd-mm-yyyy format
NOW="$(date +"%Y-%m-%d")"

# DO NOT BACKUP these databases (separate database names by space)
EXCLUDE=""

### Libraries ###
MYSQL="$(which mysql)"
if [ -z "$MYSQL" ]; then
    echo "Error: MYSQL not found"
    exit 1
fi
MYSQLDUMP="$(which mysqldump)"
if [ -z "$MYSQLDUMP" ]; then
    echo "Error: MYSQLDUMP not found"
    exit 1
fi
CHOWN="$(which chown)"
if [ -z "$CHOWN" ]; then
    echo "Error: CHOWN not found"
    exit 1
fi
CHMOD="$(which chmod)"
if [ -z "$CHMOD" ]; then
    echo "Error: CHMOD not found"
    exit 1
fi
GZIP="$(which gzip)"
if [ -z "$GZIP" ]; then
    echo "Error: GZIP not found"
    exit 1
fi
CP="$(which cp)"
if [ -z "$CP" ]; then
    echo "Error: CP not found"
    exit 1
fi

[ ! -d $DEST ] && mkdir -p $DEST || :
[ ! -d $LATEST ] && mkdir -p $LATEST || :

# Only root can access it!
#$CHOWN 0.0 -R $DEST
#$CHMOD 0600 $DEST

# Get a list of all databases available
DBS="$($MYSQL -u$MyUSER -p$MyPASS -h $MyHOST -Bse 'show databases')"

# start dumping databases
for db in $DBS
do
    skipdb=-1
    if [ "$EXCLUDE" != "" ];
    then
        for i in $EXCLUDE
        do
            [ "$db" == "$i" ] && skipdb=1 || :
        done
    fi

    if [ "$skipdb" == "-1" ] ; then

            FILE="$DEST/$db.$HOST.$NOW.gz"
            # do all in one job in pipe,
            # connect to mysql using mysqldump for select mysql database
            # and pipe it out to gz file in backup dir :)
        $MYSQLDUMP -u$MyUSER -p$MyPASS -h $MyHOST --single-transaction $db $j  | $GZIP -9 > $FILE
        $CP $FILE "$LATEST/$db.$HOST.latest.gz"
    fi
done

# Remove files older than x days if cleanup is activated
if [ $CLEANUP == 1 ]; then
    find $DEST/ -name "*.gz" -type f -mtime +$OLDERTHAN -delete
fi


texts: CDC on Sql Server 2017 with HA cluster stopped working

texts:
I am currently using Sql Server 2017 (14.0.3294.2) running on Windows Server 2016 as an HA cluster, one primary with two secondaries.
A few days ago, the primary was rebooted and I think the person that did it executed some operations that made the primary server primary again. Since then, CDC stopped working. Querying the cdc tables doesn't offer a clue about the status. The CDC job is enabled.
I found this post: https://stackoverflow.com/questions/23744083/cdc-is-enabled-but-cdc-dbotable-name-ct-table-is-not-being-populated, however, I am very reluctant to use the solution in this context.
The last error in the CDC job log is (after the primary was restarted):

Executed as user: UserName. Failed to update database "DatabaseName"
because the database is read-only. [SQLSTATE 25000] (Error 3906).
NOTE: The step was retried the requested number of times (10) without
succeeding.  The step failed.

Any suggestions on how to make CDC work again?
Thank you


texts: SQL Server - How to update two tables linked by foreign key with FROM statement?

texts:
I am a complete SQL novice and need some help on some basic syntax on how to perform an update on two tables which are linked by foreign keys.

Here is an example:

User

+-----+--------+----------+
| Id  | Active | Username |
+-----+--------+----------+
| 100 |      1 | bobby    |
+-----+--------+----------+

Orders

+-----+--------+-------------+--------+
| Id  | Active | OrderNumber | UserId |
+-----+--------+-------------+--------+
| 200 |      1 |         123 |    100 |
| 201 |     1  |         789 |    100 |
+-----+--------+-------------+--------+

Let's say I have a user which is used in another table as the foreign key UserId.  I want to write UPDATE statements that will set the user to inactive and any associated orders using that user id to inactive as well.

Here is what I have tried so far:

-- sets the User to inactive
UPDATE User SET Active = 0
WHERE <some complicated check>

-- this is where I have problems
-- how do I set the associated orders for that particular user to inactive as well?
-- I have tried...
UPDATE Orders SET Active = 0
FROM User u, Orders o
WHERE o.UserId = u.Id

But that is clearly not quite right. I can't just use the user ids in the second UPDATE statement because I don't know what users my "complicated check" will return.

Any help would be greatly appreciated.


texts: Problems connecting remotely to a RAC DB via sqlplus

texts:
When I create a user in an 11204 single instance DB and afterwards grant the sysdba privilege to that user I can connect remotely without any problems using:

sqlplus testuser/testuserpwd@testdb as sysdba

However, when I do this against a RAC DB I keep getting an invalid username and password error for some reason. I can login locally with that username and password so they are correct. I can login remotely as well using:

sqlplus testuser/testuserpwd@racdb

It's only when I add 'as sysdba' at the end that it gives me the invalid username/password error with the RAC DB.

Any ideas? Many thanks.


texts: SSAS Cube for tracking changes in parent child relationship over time

texts:
I would like to build an SSAS cube which tracks how objects in a graph who's edges represent a "belongs to" relationship change over time (daily). There are two components to the change:

which object belongs to which
attributes of each object.

Here is my schema:

fact.Edge:
    the_date date
    parent_id int
    child_id int

fact.Vertex:
    the_date date
    id int
    attribute1 int
    attribute2 int
    ...
    attributen int

dim.attribute{1...n}:
   id int
   value1 nvarchar(64)
   value2 nvarchar(64)
   ...
   valuem nvarchar(64)

These tables get new data once daily. If nothing changes, then there are two copies of the exact same data in the two fact tables with sequential dates.

I would like to know if it is possible to define a parent child hierarchy in SSAS based on the fact.Edge table referencing itself (via child_id->parent_id) but also only when the_date = the_date.

I am new to SSAS, but it seems only one attribute can be the parent attribute. Are there any workarounds?

Additionally, is it possible to treat the vertex table as two "fact" related dimensions -- ie parent_vertex and child_vertex? Or else do I need to include edges with either a null parent_id or null child_id and choose the other to have the only vertex reference?

If my questions don't quite make sense (likely due to my limited SSAS experience), is there an example cube definition that demonstrates best practices for this case?

I'd appreciate any insights you might have!