texts: How to delete previous incarnation in MySQL (/w Group Replication)

texts:
There exist almost no mentions whatsoever of MySQL "incarnations", so I'm opening this topic to make a precedent, even if I solve it myself. Feel free to help of course :)

2018-08-10T16:21:34.795206Z 0 [Note] Plugin group_replication reported: 'Old incarnation found while trying to add node fuelstat-db-1.novarumcloud.com:33061 15339180934204040.'

The Group Replication member node fails to join the GR cluster with the above error, after I have rebuilt the same server. I've checked the internal MySQL tables and I still can't locate what makes it unhappy with that "incarnation". Thanks in advance.


texts: Avg write latency spikes for disk more than usual. Need help finding the culprit

texts:
We recently had increased in disk write latency reported. Though from our monitoring team it was not confirmed from which drive but doing some dmv query I see tempdb around avg 32 ms wait on write. Also over the week tempdb usage I mean available free space has come down under 50 GB from 300 GB.
I am suspecting this spike could be from tempdb. The environment is a mix of OLTP and warehouse people querying data for large select when looking for real time data in case not available from LS standby.
My question is how can I check what in tempdb if it is would have caused this spike?
Also can those select queries also contribute to tempdb write latency? These select queries are run on tables over TBs of data and user can have part of queries like order by and group by from what I have seen. But I am not sure how to prove they are contributing to sudden spike.


texts: In PostgreSQL, I have a table which, due to sharding, exists across many schemas.  In sub-optimal cases, I need to execute a query on all of these schemas (due to an exception which violates the sharding interface.. but that's another story).  What is an efficient way to do this?  Assuming the tables have an index across the data that is interesting to this particular query (that is going across all schemas.)


texts: Performing join on 3 tables & retrieving selected columns

texts:
I need to write a query which fetches data from 3 tables & retrieve selected columns out of it.
The query below works fine. I am interested to include 1 more column in my SELECT statement which is available in 'PI_PUBLICATION' table.
Since I am using multiple column subquery here, can you please help me out?

SELECT pirhd.GTIN, pirhd.INFORMATION_PROVIDER, pirhd.RETAILER, pirhd.TARGET_MARKET, MESSAGE_XML
FROM PI_RESUBMIT_HIERARCHY_DETAIL pirhd
WHERE (pirhd.GTIN, pirhd.INFORMATION_PROVIDER, pirhd.RETAILER, pirhd.TARGET_MARKET)
IN (
SELECT item.GTIN, item.INFORMATION_PROVIDER, item.RETAILER, item.TARGET_MARKET
FROM Pi_Item Item
WHERE item.GTIN = pirhd.GTIN and item.INFORMATION_PROVIDER = pirhd.INFORMATION_PROVIDER AND item.TARGET_MARKET = pirhd.TARGET_MARKET
AND item.RETAILER = pirhd.RETAILER
AND (item.obj_id IN (SELECT PUB.OBJ_ID from PI_PUBLICATION pub))
) AND RESUBMIT_ITEM_DETAIL_ID='88956457' ORDER BY pirhd.GTIN;


texts: SQL 2014 Availability group of Same DB produces different size Backup from Node A to Node B

texts:
I have this code in a SQL Job to run a database backup on the hour.

IF sys.fn_hadr_backup_is_preferred_replica('AdventureWorks') = 1
BEGIN
  declare @seq varchar(7) = '-' + Right('00' + cast(datepart(HOUR, getdate()) % 24 as varchar(2)), 2) + '.bak'
  ...
  WITH INIT, FORMAT, COPY_ONLY, CREDENTIAL = 'AdventureWorksAzureBackupStorage', compression, stats=1
END

The question I have is if I take out the check for fn_hadr_backup_is_preferred_replica
and just run

BEGIN
  declare @seq varchar(7) = '-' + Right('00' + cast(datepart(HOUR, getdate()) % 24 as varchar(2)), 2) + '.bak'
  ...
  WITH INIT, FORMAT, COPY_ONLY, CREDENTIAL = 'AdventureWorksAzureBackupStorage', compression, stats=1
END

on first A, then on B, then the size of the databases are vastly different, e.g. Active Node database backup is 8 times larger than passive node database backup.

Is there an explanation for this please? Is the Active node not compressed perhaps? And the passive node is compressed?


texts: How to find the logical ordering of a page

texts: When I have a clustered index with logical ordering ASC, is there a way to find the logical ordering inside a data page in the output of DBCC PAGE?


texts: Is it possible to have unique together constraint on a SET of columns in PostgreSQL?

texts:
What I want to achieve is the following:

CREATE TABLE foo (
    bar1 int,
    bar2 int,
    type varchar(16),
    FOREIGN KEY bar1 REFERENCES bar(id),
    FOREIGN KEY bar2 REFERENCES bar(id)
);

ALTER TABLE foo ADD CONSTRAINT unique_link (bar1, bar2);

INSERT INTO foo (bar1, bar2, type) VALUES (1, 2, 'simple link')
--next line should fail, as {2, 1} as a set is already present, just in a different order
INSERT INTO foo (bar1, bar2, type) VALUES (2, 1, 'simple link')

Is it possible in any way? Maybe with some triggers maybe, or some auto calculated hashes of the bar* columns? Thanks for the help!


texts: I am very new to the subject of databases so this may sound ignorant, but I am curious why a key should be made explicit within a table.  Is this primarily to tell the user that the given column value is (hopefully) guaranteed to be unique within each row?  The uniqueness should still be there even if it isn't mentioned.


texts: After lowering maxmemory, redis still has high RSS memory

texts: I would like Redis to return some memory. I lowered the maxmemory by 10GB but noticed Redis is still "holding onto it" in RSS based on "used_memory_rss". Based on http://redis.io/topics/memory-optimization, it looks like Redis won't return the memory to the OS. How do I force this? Is there any way to do this without restarting Redis?


texts: Oracle CTE to SQL Server Using SSIS OLE DB Source

texts:
I wrote a cte against an Oracle database and the query executes without issue when working in Oracle SQL Developer. I need to use ssis to transport the results of that query to a SQL Server database.

In ssis I'm using a data flow task, with an OLE DB Source and an OLE DB Destination component. In my source component I'm using the SQL Command data access mode.

When I paste my query into the command text window and click preview I see the error No column information was returned by the SQL command.

Other threads I've seen seem to indicate that this is possible, so I'm wondering if something in my configuration is incorrect. All fields have names, and I can execute other queries against this data source. Is there something I'm overlooking here?

For clarification purposes, my query basically looks like this:

WITH CTE(column1,column2) AS (
    SELECT field1 --window function
           ,field2
    FROM table1
)

SELECT column1
       ,column2
FROM CTE
WHERE condition1 is true


texts: Postgres - Inject 0 values when rows do not exist

texts:
I have 3 tables that I need for reporting:

    *dates*
date_sk | full_date  | day_number_of_month
1       | 2013-01-01 |       1
2       | 2013-02-01 |       1
3       | 2013-03-01 |       1
4       | 2013-02-02 |       2
5       | 2013-02-03 |       3

    *person*
person_sk   | person_id  | person_name
1           |   10       |   John
2           |   11       |   Bob
3           |   12       |   Jill

    *person_portfolio*
person_portfolio_sk | date_sk | person_sk | res_value | report_month
1                   |   1     |     1     |     15    |  2013-01-01
2                   |   1     |     2     |     10    |  2013-01-01
3                   |   1     |     3     |      1    |  2013-01-01
4                   |   2     |     1     |     30    |  2013-02-01

(imagine the 'dates' table filled with every date for the past 10 and next 10 years which includes every day of every month. My reporting is for a monthly level which is why i need day_number_of_month = 1 for the first of every month)

I have been struggling to find out, for comparison reporting purposes using a date range, how to replace no entries during that timeframe with 0 values for the person. Here is the query I have tried:

SELECT
 p.person_id,
 COALESCE(pp.res_value,0)::NUMERIC(16,2) AS res_value,
 pp.report_month
FROM person p
LEFT JOIN person_portfolio pp
ON p.person_sk = pp.person_sk
LEFT JOIN date d
ON d.date_sk = pp.date_sk
WHERE person_id IN ('10','11','12')
AND pp.report_month >= '2013-01-01' --From Date
AND pp.report_month <= '2013-05-01' -- To Date
AND d.day_number_of_month = 1
ORDER BY p.person_id DESC;

The output I want to return would end up being 15 rows total. 3 people x 5 months of data = 15 total rows. It should look like this:

person_id   | res_value | report_month
10          |   15      |   2013-01-01
10          |   30      |   2013-02-01
10          |   0       |   2013-03-01
10          |   0       |   2013-04-01
10          |   0       |   2013-05-01
11          |   10      |   2013-01-01
11          |   0       |   2013-02-01
11          |   0       |   2013-03-01
11          |   0       |   2013-04-01
11          |   0       |   2013-05-01
12          |   1       |   2013-01-01
12          |   0       |   2013-02-01
12          |   0       |   2013-03-01
12          |   0       |   2013-04-01
12          |   0       |   2013-05-01

but I am only getting these results:

person_id   | res_value | report_month
10          |   15      |  2013-01-01
10          |   30      |  2013-02-01
11          |   10      |  2013-01-01
12          |    1      |  2013-01-01

So basically... is there currently a feasible way that I could inject the 0 value rows into the results when there is no entry for the 'report_month' for a specific person(s)? I would appreciate any kind of help as I have been working on this for 2 weeks now trying to complete this report. Thanks!


texts:
I have a collection with several fields, and I'd like to get the minimum value from one. I know a command for getting the max value:

collection.find_one(sort=[("myfield", -1)])["myfield"]

but not one for getting the min. Is there a way to do this?


texts: Efficient way to write query for multiple table which depends on column value of a table in mysql?

texts:
In the given schema, there is a table A having column A1,A2,A3,A4

There are three more tables i.e. B,C,D

If the value in A4 is b, i have to lookup table B. If the value in A4 is c, i have to lookup table C. If the value in A4 is d, i have to lookup table D.

Currently, we have implemented using switch case i.e.

select * from A
where
CASE
  WHEN SUBSTRING(A4,4,3) IN ( '023','009','011','013','015','017','019','021')
    THEN A3 NOT IN ( select Z from B)
  WHEN SUBSTRING(A4,4,3) IN ( '006','024','028','031')
    THEN A3 NOT IN ( select Z from C)
  WHEN SUBSTRING(A4,4,3) IN ( '004','025')
    THEN A3 NOT IN ( select Z from D)
END

Is there any better and more efficient way to resolve this problem?


texts: How to ensure NULLed encryption key columns are permanently erased

texts:
We are storing BLOB data (call recordings) in commodity cloud storage. These are encrypted and the keys stored in an SQL Server database.

I am working on a project to implement "Retention", i.e. deletion of data after an expiry period has elapsed. The plan is not to bother deleting the Blobs themselves, as the storage is cheap, but to remove the encryption keys from the database thereby making the call irretrievable and therefore logically deleted.

I need to prove (for compliance), that after the encryption key columns for logically deleted rows are NULLed (or updated to a new value or otherwise), the encryption keys would not be retrievable from the data files themselves (e.g. by a hacker using a hex editor to access the pages themselves).

My question is, is the data going to be physically scrubbed from the data pages after NULLing the columns, or do I need to update them (e.g. to a dummy value) first then NULL them? Will the UPDATE operation physically change the bits or will it simply be a metadata operation that leaves the bits on the disk untouched? I have done some reading and investigation but I'm still not sure. Table definition is below. Encryption Key columns are called EncryptionKey and EncryptionIV.

CREATE TABLE [File].[FileDetail]
(
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [AccountId] [bigint] NOT NULL,
    [TransferredDateTime] [datetime] NOT NULL,
    [IsEncrypted] [bit] NOT NULL,
    [EncyptionType] [nvarchar](50) NOT NULL,
    [EncryptionKey] [varbinary](2048) NULL,
    [EncryptionIV] [varbinary](2048) NULL,
    [OriginalPath] [nvarchar](400) NOT NULL,
    [SourceMachine] [nvarchar](4000) NOT NULL,
    [RelativeLocation] [nvarchar](400) NOT NULL,
    [FileCreationTime] [datetime] NOT NULL,
    [DurationMs] [int] NOT NULL,
    [EncryptionDurationMs] [int] NOT NULL,
    [CopiedSuccessfully] [bit] NOT NULL,
    [UserId] [bigint] NULL,
    [CustomerId] [bigint] NULL,
    [NumberCalled] [nvarchar](50) NULL,
    [TelephonyCallId] [uniqueidentifier] NULL,
    [SolutionInstanceId] [bigint] NULL,
    [Filename] [nvarchar](240) NULL,
    [NonCompliantFilename] [nvarchar](240) NULL,
    [StorageLocationType] [int] NOT NULL,
    [DeletedStatus] [int] NOT NULL,
    [DeletedStatusTimestamp] [datetime] NULL,
    [FileSize] [int] NULL
)


texts: Best setting of MadiaDB database Server (version: 10.1.29-MariaDB) for data about 400GB

texts:
I have a single server with 128GB and 64 core CPU.
I need to compare multiple tables (about 40 tables) having ~400GB data.
The maximum size of a table has ~60GB data for upload as CSV file.
With the given setting, how can I get the maximum performance for upload CSV and compare tables fields which are mostly numbers?


texts: best way to retrieve data using multiple parameters in Cassandra?

texts:
I have created a table that contains the userid, the content that they posted on their newsfeed, the date of when they posted it, and a primary key(I am using nodejs)

my first shot at retrieving the data was me trying use an IN clause and passing an array as a paramater.

"SELECT * FROM SomeKeySpace WHERE userId IN (?) ALLOW FILTERING"

But then after some research I realized that IN clauses should be avoided especially when passing alot of parameters.

This is what my table looks. Id is the primary key

 id      | date        | content             | userid
---------+-------------+-------------------------+--------
 1427181 | 27-Apr-2018 |           sometext  |   5332

I am trying to retrieve the date and the content filtered by userids. In the attempt that i mentioned above I had retrieved all the userIds from another db and pushed them to an array then i passed it as a parameter.


texts: Create postgresql function to be triggered before insert row

texts:
I've created a function to be executed before inserting rows in a table, in order to avoid the error "malformed array literal". It still throws the error and I deduct that the process starts earlier than my trigger. Is there other trigger which I can use to intercept the event earlier?

CREATE TABLE "user" (
permissions text[]
);

CREATE FUNCTION format_array() RETURNS trigger AS $func$
    BEGIN
        NEW.permissions := translate(NEW.permissions::text, '[]', '{}')::text[];
        RETURN NEW;
    END;
$func$ LANGUAGE plpgsql;

CREATE TRIGGER format_array BEFORE INSERT ON "user"
    FOR EACH ROW EXECUTE PROCEDURE format_array();

INSERT INTO "user" (permissions)
    VALUES ('["test", "test2"]');

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=ae1561db1f61f9458fabf02007f388ed


texts:
I have a monitoring tool that alerted me to high page splits, 50% of new allocations. After some Google Fu, I found the following query to look and see:

SELECT IOS.index_id,
       O.NAME                       AS OBJECT_NAME,
       I.NAME                       AS INDEX_NAME,
       IOS.leaf_allocation_count    AS PAGE_SPLIT_FOR_INDEX,
       IOS.nonleaf_allocation_count PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT
FROM
<db>.sys.Dm_db_index_operational_stats(Db_id(N'DB_NAME'), NULL, NULL, NULL) IOS
JOIN <db>.sys.indexes I
  ON IOS.index_id = I.index_id
     AND IOS.object_id = I.object_id
JOIN <db>.sys.objects O
  ON IOS.object_id = O.object_id
WHERE  o.NAME NOT LIKE 'sys%'
       AND i.NAME IS NOT NULL
       AND IOS.leaf_allocation_count != 0
       AND IOS.nonleaf_allocation_count != 0
ORDER  BY IOS.leaf_allocation_count DESC

The results showed that one index in particular was high in page splits/allocations caused by page splits. This table is empty.

Forgive my ignorance but how can an empty table cause page splits? There are no inserts, updates or deletes of the table.

SQL Server 9.0.5057

Any thoughts or direction would be appreciated.


texts:
I have below stored procedure..

it only works when I pass single value like "BH" but not when passing multiple values like "AE", "BH", "US", "FR"

CALL sp_populate_memo_country_companies("BH")

returns 5 rows

CALL sp_populate_memo_country_companies("BH", "US")

throw error:

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

here is the stored procedure:

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `sp_populate_memo_country_companies`(IN param_country varchar(255))
BEGIN
    SELECT locations.location_id, companies.company_name, locations.location_name, first_payroll, last_payroll
    FROM locations
    JOIN country ON country.country_code_alpha2 = locations.country_code
    JOIN companies ON companies.company_id = locations.company_id
    LEFT JOIN payroll ON payroll.location_id = locations.location_id
    WHERE locations.country_code IN (param_country) AND payroll_active = TRUE
    GROUP BY locations.location_id
    ORDER BY companies.company_name;
END

Kindly help...


texts: What is an ODBK file and how to restore that?

texts:
I have recently come across a situation where I am required to restore an ODBK file. Basically this is a database file of Intergy Server (An EHR platform). I don't know how to restore that file into a supported DB engine and how can I view the data. Upon searching I came to know that this is an OpenEdge DB file.
The server is running a Postgres engine as well but there is no table related to that EHR.


texts: Advanced search engine or server for relational database

texts:
In my current project we are storing big volume of data in relational database. One of the recent key requirements is to enrich application by adding some advanced search capabilities.

In the Project performance is one of important factors due to very large tables (10+ milions of records) with parent-children relations (for example: multi-level parent-child relationship, where I am looking for all parents with specific children). The search engine should also be able to check these references for hits.

I have found some potential engines on stack overflow, however it looks like that all of them are dedicated rather for text search than relational db and hosted on linux os:

Lucine
Solr
Sphinx

As I understand some of them use documents as a source of searching, but is it possible/ efficient to create programmaticaly documents based on my relational data? As I am not familiar with all of their features/capabilities can anyone please make some recommendations or propose some different solution?

To summarize my requirements:

framework/engine to search relational database including decendants.
support for Microsoft SQL Server
can be used in .NET applications
preferably hosted on Windows systems

Does any of mentioned above are able to solve my problem? do you know any better solution?


texts: How can I find out who is trying to connect to my MongoDB?

texts:
I have a MongoDB server that I recently configured to allow only SSL connections. Now my mongo.log is filled with this:

2017-05-30T17:39:24.528+0000 I NETWORK  [conn7881820] AssertionException handling request, closing client connection: 17189 The server is configured to only allow SSL connections
2017-05-30T17:39:25.053+0000 I NETWORK  [conn7881821] AssertionException handling request, closing client connection: 17189 The server is configured to only allow SSL connections
2017-05-30T17:39:25.592+0000 I NETWORK  [conn7881822] AssertionException handling request, closing client connection: 17189 The server is configured to only allow SSL connections
2017-05-30T17:39:25.593+0000 I NETWORK  [conn7881823] AssertionException handling request, closing client connection: 17189 The server is configured to only allow SSL connections
2017-05-30T17:39:26.369+0000 I NETWORK  [conn7881824] AssertionException handling request, closing client connection: 17189 The server is configured to only allow SSL connections

Unfortunately the IP address of the offending process isn't logged.

How can I find out what IP is trying to connect to my MongoDB?


texts: Updating rows from other rows in a huge table

texts:
I have a huge table with items

item-id | name | type | extra

Many of them are "old versions" of type 0.

I have to update the extra field in all new items (same item-id, but type > 0) with the extra data we have for the old version.

Since this is a very large table with millions of rows, I have to be very careful with joining the table with itself.

Basically I want to do this

update mytable t1 set extra = (select extra from mytable where t1.itemid = mytable.itemid and type=0) where type > 0;

But MySQL doesn't accept t1 in the sub-select.


texts: PostgreSQL utilize data from SELECT in BEGIN or use WITH?

texts:
I went through the documentation for 9.3 and did not find anything suggesting that I can or can not use data from a SELECT query within a BEGIN query. Is it possible or will I have to resort to a WITH query of some kind?

I'm trying to merge two queries in to a single BEGIN query to figure out how to be more efficient when executing queries to the database. I have another project this rabbit trails from where I've built a tool that quickly duplicates a table (so I can test other database tools I'm building on instead of the originals) however it does not replicate the primary key.

This first query fetches the sequence name of a table:

SELECT s.relname AS sequence
FROM pg_class AS s JOIN pg_depend d ON d.objid = s.oid
INNER JOIN pg_class AS t ON d.objid = s.oid AND d.refobjid = t.oid
INNER JOIN pg_attribute AS a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
INNER JOIN pg_namespace AS n ON n.oid = s.relnamespace
WHERE s.relkind = 'S'
AND n.nspname = 'public'
AND t.relname='parts';

This second query duplicates a table though already contains the sequence name that is fetched from the first query:

BEGIN;
LOCK TABLE parts;
CREATE TABLE parts1 (LIKE parts INCLUDING ALL);
INSERT INTO parts1 SELECT * FROM parts;
SELECT setval('parts_id_seq1', (SELECT max(id) FROM parts)+1);
COMMIT;


texts: Is index_type ignored on MySQL primary keys in create table?

texts:
I have seen a create table whose primary key had an identifier where it should have an index_type, something like that:

create table a (foo INTEGER, PRIMARY KEY foo_id (foo));

The thing is that according to documentation it shouldn't be allowed, as where foo_id is should be, if something, an index_type (USING {BTREE | HASH}).

If I run a show create table for this table foo_id is removed.

Does MySQL ignore it or I'm missing something?


texts: Implication in Increasing MySQL maximum keys allowed per table

texts:
We have a situation where we encounter a MySQL error

  ERROR 1069: Too many keys specified. Max 64 keys allowed

In our database (using Magento E-commerce) we need to add 76 indexed key columns but we are not allowed to add more than 64 keys, which is the maximum limit allowed.

So we have decided to increase the maximum limit to 80 by recompiling the MySQL / MariaDB, in that case what would be the implications ?

Kindly let me know if you need more server resource details.

Thank you !


texts: Unable to wrap a simple CTE in plpgsql loop

texts:
For a test database, I would like to create a bunch of test users (all with "default" values). There is a table called users, and another called user_properties. For each test user, I need to make corresponding entries in both these tables. I already have a nicely working CTE to do this, but I am unable to wrap this CTE in a loop.

CREATE OR REPLACE FUNCTION ins()
returns void as
$BODY$
BEGIN
--RETURN record;

  FOR Loopid  IN 0..10 LOOP

with
    user as (
    insert into user
    values(default)
    returning id
    )
    ,user_property as (
    insert into user_property (property_of)
    select id from user
    returning id
    )
select id from user_property;

END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

It creates the function, but on running it complains

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.

But it doesn't seem possible to replace the last select of the CTE with a perform instead - that's a syntax error. So how can I do this?

The table structures can be assumed to be very simple since most columns will take the default values. Table user has a single column id, and user_properties has two columns id and property_of - which is a foreign key to col id in the user table. Both the id columns are auto generated sequences.

The few questions (example) which have very similar titles have their answer that a loop/cte isn't really necessary, so they don't help me.


texts:
I have a mirrored database that was using a service broker to push notifications to a client.  The broker creation framework started making duplicates and this caused serious problems so i rolled back to a different framework.

The problem is i cannot cleanup the the existing services that are not longer in use (or should be).

This line hangs indefinitely.

DROP SERVICE [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271]

I've tried stopping the broker but i cannot because it is a mirrored database.
I've tried killing every single transaction in the database to free the lock but that didn't work.
I've tried working my way up by disabling the queue and then deleting the contracts and message types but i cannot while the service still exists.
I was able to disable the queues and delete the activation stored procedures but this didn't seem to do anything.
Most of the queues do appear to have some rows in them.
SELECT * FROM sys.transmission_queue hangs indefinitely as well.
EDIT: i was able to empty the queue with the following, but it didn't actually fix the issue.

DECLARE @dialog UNIQUEIDENTIFIER

WHILE EXISTS (SELECT 1 from <QUEUE>)
BEGIN
    WAITFOR( RECEIVE TOP (1)
        @dialog = conversation_handle
        FROM <QUEUE>
    ), TIMEOUT 500

    end conversation @dialog with cleanup
end

I'm afraid i'm going to have to break mirroring in order to make a new broker. This is a live production system and i'd rather not do that..  Anything else i can try?

BIG EDIT

So i was able to finally figure out a way to delete most of them.

I first issued this to empty out the queue

-- Create variables used to hold information
DECLARE @dialog UNIQUEIDENTIFIER

WHILE EXISTS (SELECT 1 from [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271])
BEGIN
    WAITFOR( RECEIVE TOP (1)
        @dialog = conversation_handle
        FROM dbo. [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271]
    ), TIMEOUT 500

    end conversation @dialog with cleanup
end

Then i altered the queue with ACTIVATION ( DROP )

ALTER QUEUE [dbo]. [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271] WITH STATUS = ON , RETENTION = OFF , ACTIVATION (  DROP  ), POISON_MESSAGE_HANDLING (STATUS = OFF)

After this i was able to drop the service and then the queue. It only seems to take 3-4 minutes per queue to delete.

DROP service [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271]
DROP QUEUE [dbo_TrackSessionsQueue_3a1daa40-ec44-4425-a81d-4ae5ecf90271]

At this point all but 2 of the 15 services that refused to drop to have been deleted after stopping and retrying over and over again.

There obviously is something blocking here but as discussed in the comments there isn't anything blocking when i run SELECT * FROM sys.dm_exec_requests


texts: Table design for departments with duplicate department numbers

texts:
How should I define a database table for my company's departments where we have a set of duplicate department numbers used by an upper management tier.

  dept_id   dept_name   manager_id  director_id   vp_id   description     mission_statement  over_view    web_url
      1       name1      10            20          30       ...
      2       name2      11            21          30
      3       name3      12            22          31
      4       name4      13            23          32
      4       name5      13            24          32
      4       name6      13            25          32

The column which makes the row unique is the director_id.  But I really only want to use the dept_id as the pk because I'm using the department data for a web application. I have the Employee table with a column named dept_id.  When I display an employee on a webpage, I dynamically create a link to the department's splash page containing the above information.  But if I list director 24 I need URL parameters that take me to the department named name5

blahblah.com/view_dept.jsp?dept_id=4

I know the obvious is to composite key with director_id  and have the URL be

blahblah.com/view_dept.jsp?dept_id=4&director_id=24

but when I list a regular employee I don't have the director_id. I suppose I could get it in the query, but it may not be as straight forward as it would be if HR just used unique ID's for all departments. Getting HR to do a reorg is probably out of the question, but it would be nice to know that that is the most recommended answer.

I'd like to hear suggestions and best alternatives to the reorg


texts: How to get month name from week number and year

texts:
I have a week number and a year (e.g. 34 and 2015). I want to get the name of the month to which the specified week belongs.

How can I do that?


texts: Segmenting data by percentage of a column total

texts:
I need to separate a list of records by the percentage of a column when they are summed to all records larger than the current record until a MAX percentage.

Records are sorted DESC in column A, and I need all rows in A summed up until (from large to small) they equal 60% of the sum(A) (col B). Then, I need to perform the same for the rest of the row to 30% of the sum(A), and then the last 10% or the rest of the records.

The top 60% records need to be marked as A, middle 30% records marked B and last ~10% marked C.
I have tried NTILE but can't get the segments to match up correctly.

╔══════════════════════╦══════╦═══╦══════╦═════╗
║            A         ║   B  ║ C ║      ║     ║
╠══════════════════════╬══════╬═══╬══════╬═════╣
║     197              ║  197 ║ A ║      ║     ║
║     192              ║  389 ║ A ║      ║     ║
║     190              ║  579 ║ A ║      ║     ║
║     185              ║  764 ║ A ║      ║     ║
║     184              ║  948 ║ A ║      ║     ║
║     184              ║ 1132 ║ A ║      ║     ║
║     181              ║ 1313 ║ A ║      ║     ║
║     180              ║ 1493 ║ A ║      ║     ║
║     175              ║ 1668 ║ A ║      ║     ║
║     172              ║ 1840 ║ A ║      ║     ║
║     171              ║ 2011 ║ A ║      ║     ║
║     170              ║ 2181 ║ A ║      ║     ║
║     169              ║ 2350 ║ A ║      ║     ║
║     169              ║ 2519 ║ A ║ 2602 ║ 60% ║
║                      ║      ║   ║      ║     ║
║     167              ║  167 ║ B ║      ║     ║
║     167              ║  334 ║ B ║      ║     ║
║     167              ║  501 ║ B ║      ║     ║
║     166              ║  667 ║ B ║      ║     ║
║     166              ║  833 ║ B ║      ║     ║
║     166              ║  999 ║ B ║      ║     ║
║     165              ║ 1164 ║ B ║ 1301 ║ 30% ║
║                      ║      ║   ║      ║     ║
║     164              ║  164 ║ C ║      ║     ║
║     164              ║  328 ║ C ║      ║     ║
║     163              ║  491 ║ C ║      ║     ║
║     163              ║  654 ║ C ║  867 ║ last
║                      ║      ║   ║      ║     ║
║     TOTAL A:A = 4337 ║      ║   ║      ║     ║
╚════════════════════=╩═════════╩══════╩═════╝


texts: DBCC CHECKDB grows tempdb to a huge size - Legacy MS SQL Server 2008 R2 Instance

texts:
I have an older Microsoft SQL Server Instance (2008 R2 SU3 GDR - Build: 10.50.6220).  When I run DBCC CHECKDB via a Maintenance Plan, the TempDB grows very large.

The largest DB on the instance is 95GB, and while running a data integrity check tempdb grew to 40GB before filling up the filesystem.

Here's the error message:

  Executing the query "DBCC CHECKDB(N'[dbname]')  WITH NO_INFOMSGS "
  failed with the following error: "Could not allocate space for object
  'dbo.SORT temporary run storage:  140907993825280' in database
  'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by
  deleting unneeded files, dropping objects in the filegroup, adding
  additional files to the filegroup, or setting autogrowth on for
  existing files in the filegroup. The transaction log for database
  'tempdb' is full. To find out why space in the log cannot be reused,
  see the log_reuse_wait_desc column in sys.databases Check terminated.
  A failure was detected while collecting facts. Possibly tempdb out of
  space or a system table is inconsistent. Check previous errors.".
  Possible failure reasons: Problems with the query, "ResultSet"
  property not set correctly, parameters not set correctly, or
  connection not established correctly.

My first action step is to move tempdb to its own drive.  However, I'd like to find a way to run a full CHECKDB (not PHYSICAL_ONLY) that uses less tempdb space.  I can't upgrade to a newer version of SQL Server at this time.

Any helpful tips?


texts: Best way to choose which columns to index to improve performance

texts:
I use a MSSQL Database for my web application and now the user audit(USER_AUDIT) records in the application have crossed beyond 5 million records/rows. Every day the performance of the application is degrading and the queries for the audit table are pretty straight forward from the UI.
The default sorting on the data is on the TimeStamp i.e. the time when the record was created so I was wondering if adding an INDEX on the TimeStamp column would improve the performance of the application or because the the ID column(Incremental IDs) already has the INDEX on it, its sufficient and would not improve the performance any further.
Also sometimes the records need to be filtered using the username which is also one of the columns in the same table but because the user names are not unique I was wondering if it would help at all to add INDEX to that column as well.
The clustered index is on the ID column on the table - Would it be helpful if I change the Clustered INDEX to be on a different column
Or
Based on the above information would it be of any help to add Non-clustered Indexes to either TimeStamp or Username or both columns
I come from a programming background with very limited knowledge of the Database management so please pardon any missing information and please feel free to let me know if I need to add more information to the Question.
I would really appreciate it even if you could point me in the right direction.


texts: What is the best way to upload custom-parsed data into AWS Aurora PostgreSQL

texts:
I have a large (5-10 GB) binary file on AWS S3 that will require custom parsing, probably in python.  It is essentially a sequential set of millions of dataframes, all having the same structure.  What is the best way for me to get this data into a severless/hosted AWS Aurora PostgreSQL instance?  So far I have thought of:
 1. I could write to a CSV file and use COPY, but the size would be astronomical
 2. I could send it over the wire in batches of rows
 3. use AWS Glue, though I'm still learning about that.


texts: Find material for improvement of my SQL ability

texts:
The following part that is needed to be improved is:

script
T-SQL
Procedure
Function

Problem:
Where can I get material to improve my ability in SQL coding? Is there any website for downloading a database with different task/assignment and solution?

Where should I go to review lots of SQL code?


texts: I have my data in gridb Database. To make the execution of my search query faster, I would like to do indexing on big data. Can we do indexing in GridDB? If yes, how?


texts:
I have a MySQL master with two slaves. Due to poor configuration both slaves had the same server-id.

My questions are:

Is the slaves' data fully synced with the master or did they miss transactions and I should start a new replication ?
During the past 4 days the master server was getting stuck randomly (3 times) with no apparent reason - is this the cause? if so, why would it get stuck? (By stuck I mean that it was running, no errors in the log, but not accepting connections)
The slaves have been busy for 3 days with a big ALTER that the master long finished, does this have anything to do with it?


texts: SQL Server The operating system returned error 21 (The device is not ready.) - can I recover without restarting the server?

texts:
So I have found the related questions and answers about how to avoid having a disk not ready issue when SQL Server starts, which appears to be the cause of the OS error 21 (Device not ready) error when trying to use this database.  And I've read that restarting the sql server with the drives up and available will fix it.  Has anyone figured out a way to "fix" this issue which is occurring with just one db without restarting the server?

I've tried taking this one db offline, I've tried detaching the db.  Even detaching the DB requires the server to find and have access to the files in the first place?  Honestly I'm not entirely sure why SQL Server puts it in online state when it could not read the files successfully at startup time?

For those who have a DB in this state, and where it would be painful to bring the whole DB server down briefly, is there any other recourse?

Today I'm having the issue with on DB in SQL Server 2014, not sure if the answer would be different in later versions.


texts: How to get a parallel nested loop in postgres 12?

texts:
I have a lateral join, and I would like each subselect to execute in parallel. According to the docs (https://www.postgresql.org/docs/12/parallel-plans.html), nested loops can be parallelized:

Just as in a non-parallel plan, the driving table may be joined to one or more other tables using a nested loop, hash join, or merge join. The inner side of the join may be any kind of non-parallel plan that is otherwise supported by the planner provided that it is safe to run within a parallel worker. Depending on the join type, the inner side may also be a parallel plan.
In a nested loop join, the inner side is always non-parallel. Although it is executed in full, this is efficient if the inner side is an index scan, because the outer tuples and thus the loops that look up values in the index are divided over the cooperating processes.

However, I can't get a nested loop - or any join - to execute in parallel. Here's a (contrived) example:
=> create table foo as
  select round(random()*100) as pid, val
  from generate_series(1, 10000000) v(val);
=> set force_parallel_mode = on;
=> explain
  SELECT pid, sum
  FROM (
    select distinct pid
    from foo
  ) x
  JOIN LATERAL (
    select sum(val)
    from foo y
    where x.pid = y.pid
  ) y
    ON TRUE;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop  (cost=358361.91..18288838.71 rows=101 width=16)
   ->  HashAggregate  (cost=179057.19..179058.20 rows=101 width=8)
         Group Key: foo.pid
         ->  Seq Scan on foo  (cost=0.00..154056.75 rows=10000175 width=8)
   ->  Aggregate  (cost=179304.72..179304.73 rows=1 width=8)
         ->  Seq Scan on foo y  (cost=0.00..179057.19 rows=99012 width=4)
               Filter: (foo.pid = pid)
 JIT:
   Functions: 12
   Options: Inlining true, Optimization true, Expressions true, Deforming true
(10 rows)

I know that parallel plans are sometimes generated on this database, here is an example:
=> explain select pid, sum(val) from foo group by 1;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=92560.09..92577.55 rows=101 width=16)
   Group Key: pid
   ->  Gather Merge  (cost=92560.09..92574.52 rows=404 width=16)
         Workers Planned: 4
         ->  Sort  (cost=91560.03..91560.28 rows=101 width=16)
               Sort Key: pid
               ->  Partial HashAggregate  (cost=91555.66..91556.67 rows=101 width=16)
                     Group Key: pid
                     ->  Parallel Seq Scan on foo  (cost=0.00..79055.44 rows=2500044 width=12)
(9 rows)

postgres server version: 12.4
linux kernel version: 5.4


texts:
I am writing a trigger in a MySQL to conditionally create a new record in another table based on the value of field in newly added record in a table. Here is the script for the trigger (which should be self explanatory):

CREATE TRIGGER TRG_CREATE_INT_SRV_ROW
AFTER INSERT ON survey_details
FOR EACH ROW
BEGIN
 IF(New.LossTo == 'Vehicle') THEN
    INSERT INTO survey_vehicle (RefNo) VALUES (New.RefNo);
 ELSE
    INSERT INTO survey_other (RefNo) VALUES (New.RefNo);
 END IF;
END;

While running the query, the error message displayed is:

  1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= 'Vehicle') THEN
      INSERT INTO interim_survey_vehicle (RefNo) VALUES (New.RefNo' at line 5

I am writing this kind of conditional trigger for the first time.

What is wrong with code please?


texts: Mysql update table from another table with condition

texts:
In mysql i have 2 table.
First

wp_postmeta_import

Second

wp_postmeta_temp

These tables have these columns:

meta_id
post_id
meta_key
meta_value

I need to update only my column meta_value of wp_postmeta_temp when post_id is egual to post id of wp_postmeta_import and the value of meta_key is _variation_description because in table are other post_id with other value
i try some code an this is the only that work a little bit but it is change the row that i need and in the other meta_key it gives me NULL.
UPDATE wp_postmeta_temp
SET meta_value = (
                    SELECT
                      meta_value
                    FROM
                      wp_postmeta_import
                    WHERE
                      wp_postmeta_import.post_id = wp_postmeta_temp.post_id
                    AND
                      wp_postmeta_temp.meta_key = '_variation_description'
                 );

The table have rows, for any post_id for exemple:

meta_id: variable (82649,82650..)
post_id: 5065
meta_key: _variation_description or _regular_price or total_sales ecc..
meta_value: variable (15, 0, 200 ...)

i hope that i'm clear
anyone can help me, please?


texts: Access to folder is denied (log shipping)

texts:
I am trying to log ship from server A to B. I have done everything to configure log shipping but when I try to run the job at Server B while copying a backup from server A, ikt gives me an error:

  can not access the path (Server A backup folder name) access is denied.

I have shared backup folder at server A with admin account moreover with Everyone. I also tried to change SQL Server agent config and use option (this account) in properties and fill my system account.

Please help me to fix this problem and explain a little like:

Is there any problem with sharing the account at server A
Do I need to change my SQL Server Agent service account and how?

Let's say at server A there is a folder backup in E drive and there is a folder restore at server B in E drive. I am trying to login to SQL Server through Windows service credential in server A.

Public ip for my two servers is 10.201.4.111 and 10.201.4.112 and my service accounts are local system, which are built in accounts.


texts: An SQL Server 2008 R2 based program fails when updating stored procedures?

texts:
I'm trying to update MIP software and one of the "companies" fails while it is updating a stored procedure.

Is there any place in SQL Server 2008 R2 / Windows where I could see an error message about what might be causing this?  When I did the installation I gave it the sa password, and it worked for all the other "companies" that we have in the system just fine.


texts: Compare an hour between two hour columns

texts:
Please help. I am kinda new to this database world. I am using PostgreSQL.

I have a table with defined shifts like this:

CREATE TEMPORARY TABLE shifts AS
SELECT id, start_hour::time, end_hour::time
FROM ( VALUES
  ( 1,'06:00:01','14:00:00' ),
  ( 2,'14:00:01','22:00:00' ),
  ( 3,'22:00:01','06:00:00' )
) AS t(id,start_hour,end_hour);

I have been using the between operator to compare a given hour with the start_hour and end_hour so I can get the corresponding shift, like this

SELECT * from Shifts S where given_hour BETWEEN start_hour AND end_hour;

It works when it is shift 1 or 2 but not with shift 3. It returns no value. Can you please give me a little advice on how should i do this?
Thank you!


texts:
We have a 4 server setup, 1 master, 3 daisy-chained slaves, in the following setup:
A (master)   ->   B (slave)   ->   C (slave)   ->   D (slave)
(the servers B and C and D are running with log-slave-updates)
In normal operation everything works as expected: if we add new data to A, we see it show up quickly in B and C and D
Now we want to create a failure scenario -- we shutdown A and want to make B the new master:
B (master)   ->   C (slave)   ->   D (slave)
It seems like what we want to do is fairly simple -- switch B from Slave to Master
We are trying to follow the documentation "Switching Sources During Failover"
https://dev.mysql.com/doc/refman/8.0/en/replication-solutions-switch.html
The doc says " On the replica Replica 1 being promoted to become the source, issue STOP REPLICA | SLAVE and RESET MASTER."
So if we're reading correctly, to switch B from Slave to Master all we have to do is run:
STOP SLAVE
RESET MASTER

Running "STOP SLAVE" causes no issues, but running "RESET MASTER" breaks the replication to downstream staves C and D. This is the error on C:
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size'
or sometimes:
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'I/O error reading log event; the first event 'mysql-bin.000001' at 14282060, the last event read from './mysql-bin.000001' at 14282683, the last byte read from './mysql-bin.000001' at 14282683.'
So what is the point of "RESET MASTER" and why does it break the chain? Is there any harm in omitting it/how does one properly do a failover in MySQL chain replication?


texts:
I am trying to create a basic database using access but I'm just learning at the minute.

I have a text file which contains, customer account code (contains duplicates), customer address type, customer name and telephone number.

I have created a table by linking to this source data.  (If I refresh this data in the future will I lose any linked data, if that makes sense?).

I want to be able to add through a form, company registration number and email address.

How do I do this? I know a bit about basic tables and forms but I am struggling to understand relationships.

Thanks in advance,

Dave


texts: What should one do if the auto increment primary key exceeds the maximum?

texts:
I have a primary key for a table with the following structure,

**Field**          **Type**
  nid     int(10)unsigned

I know the maximum limit of int(10) is 4294967295. But What happens if it exceeds the maximum? Should i have to change to bigint ?
if so what is the best practice is it to use int or big int in primary key?


texts: Exclude certain table data for all non public schemas (tenants)

texts:
I have tried running the pg_dump command with the following flag:

--exclude-table-data foo_bar

However it seems this flag is ignored completely if the public schema does not contain a foo_bar table. In my case foo_bar only exists for tenant schemas. Is there an undocumented function to achieve this?


texts:
I have 2 table and i want to pull data from both of them.
This is sample for my table structure:

Table tbl_a = id, time, note, customer
Table tbl_b = id, time, name, surname, note

This is my mysql request:

Select * from tbl_a left join tbl_b on tbl_a.customer=tbl.b=id where tbl_a.id=5;

When i run this query directly from mysql i can see result exactly as i want but i dont know how to display it in my php page.

i am doing it like this:

$sql = mysql_query("Select * from tbl_a left join tbl_b on tbl_a.customer=tbl.b=id where tbl_a.id=5");
$data = mysql_fetch_array($sql);

echo $data[id]." - ".$data[time]." - ".$data[note]." - ".$data[customer]." - ".$data[id]." - ".$data[name]." - ".$data[surname]." - ".$data[note];

i know that column names that are repeated could not be used again but i dont know what the right way is.


texts:
I'm working on a reservation system schema and have some problems defining availability of resources. I searched for similar question here and on Google but still don't have clear understanding

My app is for booking taxis. Where each taxi driver is either working today or taking a day off. So far I have come up with the following structure

table: Drivers
id INT PRIMARY KEY
name INT

table: Users
id INT PRIMARY KEY
name TEXT

table: Reservations
id INT PRIMARY KEY
start: DATETIME
end: DATETIME
userID: INT (FOREIGN KEY(Users.id))
driverID: INT (FOREIGN KEY(Drivers.id))

I was thinking of treating each driver as available if there is no reservation entry, and then  coming up with different types of reservations:

Unavailable => driver is taking a day off
Booked => driver is booked by a user between start/end times
Cancelled => cancelled booking

However, maintaining Unavailable/Booked status sounds a bit more complicated than I'd like to.

So, are there any suggestions how to improve the database schema?


texts:
We maintain a table that looks like this to perform search queries on our forums database. I'll draw your attention to the FULLTEXT columns:

CREATE TABLE `forums_posts_search` (
  `post_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `topic_id` bigint(20) NOT NULL,
  `topic_title` varchar(255) NOT NULL,
  `post` text NOT NULL,
  PRIMARY KEY (`post_id`),
  FULLTEXT KEY `search` (`topic_title`,`post`)
) ENGINE=MyISAM AUTO_INCREMENT=1285900 DEFAULT CHARSET=latin1;

For a row that has the following forums_posts_search.post value:

INSERT INTO `forums_posts_search` (`post_id`, `user_id`, `topic_id`, `topic_title`, `post`)
VALUES
(1, 1, 1, 'Hashtag Test', 'Hi,
\n
\nMine #HME as the #BC don\'t work, too.
\n
\nI had to send a ticket about this ?
\n');

the following query does not return the result when a user goes to search for the parameters "#hme" or "#bc":

SELECT [...], MATCH(p.topic_title, p.post) AGAINST ("#hme") as score
FROM forums_posts_search p
INNER JOIN forums_topics t ON (p.topic_id = t.topic_id)
INNER JOIN forums f ON (t.forum_id = f.forum_id)
LEFT OUTER JOIN users u ON (p.user_id = u.id)
WHERE MATCH(p.topic_title, p.post) AGAINST ("#hme")
ORDER BY score

Running MariaDB on top of MySQL 5.5.44. I recently rebuilt the FULLTEXT indexes manually and then ran a REPAIR TABLE [...] QUICK; but the results are still the same.

Why isn't this query returning records where forums_posts_search.post contains "#hme" or "#bc"? Strangely, a longer search parameter (i.e. "#hashtag") returns all of the results.


texts: Mysql 8: count(*) on INT primary is 13500 times slower than supposed to be. any innodb dev here?

texts:
I am aware there are many questions on this topic, mysql has not improved much on these single-query performance bugs in the past years but my case is more unusual.

If mysql would access the table index as it was supposed to do and count the entries it should take 70mil entries * 4 byte per entry = ~275MB at ~1000mb/sec = 270 milliseconds to count the entries
The actual mysql performance is about 13500 times slower than it should be.

Given that I have 100 gig of RAM for innodb I'd expect a subsequent count to be done in single digit milliseconds (not in again one hour)

I have the same behaviour on 3 servers, with slgihtly 3 different mysql versions on 5 different innodb tables, so it's not a localized issue.
I have had more severe cases than the current one (count takes about an hour, I've seen it going for days too).
In this case the table is compressed (which should actually increase the speed)

1590603 root    localhost       locdb1       Query   2004    Sending data    select count(*) from logos     1003611 0       0

EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "35130458.29"
    },
    "table": {
      "table_name": "logos",
      "access_type": "index",
      "key": "PRIMARY",
      "used_key_parts": [
        "id"
      ],
      "key_length": "4",
      "rows_examined_per_scan": 9851928,
      "rows_produced_per_join": 9851928,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "34145265.49",
        "eval_cost": "985192.80",
        "prefix_cost": "35130458.29",
        "data_read_per_join": "2G"
      }
    }
  }
}

CREATE TABLE `logos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_raw` mediumtext COLLATE utf8_bin,
  `a` mediumblob,
  `ab` varchar(3) COLLATE utf8_bin DEFAULT NULL,
  `b` mediumblob,
  `bb` varchar(3) COLLATE utf8_bin DEFAULT NULL,
  `last_error_code` smallint(6) DEFAULT NULL,
  `date_found` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=67945433 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPRESSED

IBD Filesize is 370 GiB
Disk performance is reliable 24,000 IPS at max 1.2 GiB/sec
I've tried moving the table on a single non used disk and mysql was accessing it constantly at 4 MiB/sec (in that test case max speed would actually be 250MiB/sec at max 16k iops)
CPU is 20% loaded

The table in this case was freshly rebuilt, I've also tested this on freshly optimized tables without improvement.

I'd be happy with 5 seconds as well. I'm not happy with an hour.


texts:
Is it possible to disable SSL/TLS on connections to AzureSQL? I have a driver which cannot handle SSL.

I know it is a terrible idea BTW since the traffic would then be open to snooping around.


texts: Modifying the Contents of SQL Server Agent Alerts

texts:
As is generally recommended to do, I have Alerts configured for errors of severity 19 and greater that occur on my SQL Servers.

However, as part of an email notification standardization process, I'm wondering if there is any way to modify the contents of an Alert, including the email title and the body of the message.

From looking around both the options provided by SSMS and through some searching on the Internet, I'm being led to believe that this might not be the case. I am aware that an additional message can be added to the end of a system-generated alert, but I haven't found anything suggesting the system alert itself can be modified.

My other immediate idea to achieve the general goal here would be to not have the alerts send notifications directly, but instead to trigger a job that triggers Powershell that ultimately sends the email. Would this be the only realistic way to do this, or are there easier ways to make an Alert generate a customized notification?


texts: What is the default order of records for a SELECT statement in MySQL?

texts:
Suppose you have the following table and data:

create table t (
    k int,
    v int,
    index k(k)
    ) engine=memory;

insert into t (k, v)
values (10, 1),
       (10, 2),
       (10, 3);

When issuing select * from t where k = 10 with no order by clause, how does MySQL sort the records by default?


texts: How to remove orphaned tables with #sql-ib file type

texts:
Having some space issues with my server and noticed that there are two massive #sql-ibxxx files with no .frm equivalents.
what is the best way to remove these?
My databases are setup so that we have
Database A
Database B  - the two files are located here and no other tables exist, its essentially an "empty" database
Database C
I have tried to remove the files with drop table issuing the command DROP TABLE #mysql50##sql-ib53761;
but i get Unknown table '#mysql50##sql-ib53761'
I have read online that i need to drop the database and bring it back up.
Does this mean i need to backup database A,B,C or can i just drop B and leave A and C unchanged?
When i did try to drop this database in the past, it would crash the server
Any help would be amazing!
I am using 10.3.18-MariaDB and have innodb-file-per-table =1
Thanks


texts: PostgreSQL data files have size more than data itself

texts:
We have a system that does some data archiving to a PostgreSQL DB. We found out that the PC storage was full due to the DB archiving. The problem is that I checked the data files residing in /var/lib/pgsql/data/base/ and they were about 70 GB in total, while when I dumped all the databases using pg_dump the output files did not exceed 24 GB. Am I missing something here or misunderstanding something? Where is this large difference in size going?

Edit: I did pg_dump to contain schema and data with the option -c to allow drop and create.

Edit 2: I investigated the DB schema file and I found out that table that contained almost 23.9 GB of the 24 GB (About 332.4 Milions rows) of data has an index on it. There is another index on another table but the table is empty.

Edit 3: The program stores values of about 1500 variables periodically, I mean all variables are recorded from 0.1 second to 1 minute or a bit more, so I think there is a huge DB access here.

Edit 4: I executed the second query here to find the size of each relation in the schema and I found out the following:

28 GB for the main data table.
about 42 GB for 3 indexes only! 24, 9, 9.

My purpose is I want to do a backup and restore frequently (Every few months). Should I care about these DB indexes when doing backup and restore or just focus on my data tables?


texts: Raise error from Stored PROC in SQL Server PDW

texts:
I need to do some validation inside a stored procedure before I continue processing but SQL Server PDW (SQL Server 2008 R2) does not support RAISEERROR inside stored procedures.

Is there any other way that I can raise an error with a specific error message inside a stored procedure?

I can try and do something illegal, like force a "division by zero" error, but the error would be misleading.

I'd like to be able to raise an error specifying the exact problem that's occurring.

Any other way?


texts: How can I speed up an ASC sort on a column that only holds an integer between 0 and 9 across multiple millions of rows?

texts:
CREATE TABLE foo (
  id  TEXT    NOT NULL,
  bar INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

CREATE INDEX foo_bar_idx ON foo(bar) WHERE NOT bar = 0;

I can sort on the bar column in DESC order about 3 times faster than I can sort on the bar column in ASC order. I assume an index on the column isn't helping with the sort since, relatively speaking w/ millions of rows, most of the values are the same.

The default value for the bar column is 0, but I only care about rows that have a value between 1 and 9, so my query includes a where clause for that. Not sure if the partial index is helping.

SELECT id FROM foo WHERE bar > 0 ORDER BY bar DESC;
SELECT id FROM foo WHERE bar > 0 ORDER BY bar ASC;

Across 9M rows, explain analize shows the ORDER BY DESC taking less than a second, and ORDER BY ASC taking a little over 3 seconds. Approximately 117K of the 9M rows get filtered by the WHERE clause.

Is the ORDER BY DESC faster because it doesn't necessarily have to worry about the 0 values? Any way to speed up the ORDER BY ASC query? Or... should I just be happy w/ a 3 second query time for a table w/ 9M rows in it? I'm continually adding rows, so I'm worried about the query time increasing over time...


texts: Single User mode can't be turned off and no users are connected

texts:
I have a database that is in Single User Mode.  When I run this:

exec sp_dboption 'MyDb', 'single user', 'FALSE'

It fails, telling me that there is still a user connected.

But when I run both of these:

exec sp_who2

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
  from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
 where d.name = 'MyDb'

I get no connections for MyDb.

How can this be, and how can I get my database out of single user mode?


texts:
Lets say a lot of my tables will have the same few columns for example a name, description, created timestamp, last updated timestamp.

When is it appropriate to put these common columns in a separate table compared to keeping them in their individual tables.

Just to keep track of it better here is a quick schema.

Books                  Movies
---------------------  ---------------------
|ID|Name|Description|  |ID|Name|Description|
|  |    |           |  |  |    |           |

or

Books         Movies        Meta
------------  ------------  ---------------------
|ID|Meta_ID|  |ID|Meta_ID|  |ID|Name|Description|
|  |       |  |  |       |  |  |    |           |

Obviously this isn't the best example but it gives you the idea.


texts:
I moved my bin-logs to a different folder:

log-bin = /storage/mysql/bin-log
log-bin-index = /storage/mysql/bin-log.index

When I try to start mysqld I get this:

/usr/libexec/mysqld: File '/storage/mysql/bin-log.index' not found (Errcode: 13)

Permissions for the files and directory are fine, sudo -u mysql touch /storage/mysql/bin-log.index is working fine and there is no apparmor installed.

Any ideas what could cause this?


texts: Duplicated values found in unique indexed column

texts:
I have a database table with an unique index idx_1(USER_ID, COURSE_ID), however I still found some entries with the same USER_ID and COURSE_ID (both not null). And the other columns of the duplicated rows are not the same. such as addtime (default current timestamp), and updatetime (default current timestamp on update current timestamp) of the duplicated 2 rows have a difference of 1 second:

SELECT * FROM user_course WHERE COURSE_ID='COURSEID1' AND USER_ID='USERID1';

----------------------------------------------------------------
UUID | COURSE_ID | USER_ID | ADDTIME         | UPDATETIME
----------------------------------------------------------------
abc1 |COURSEID1  | USERID1 | 2019-01-01 00:00| 2019-01-01 00:00

SELECT * FROM user_course WHERE UUID='abc1" OR UUID='abc2';

----------------------------------------------------------------
UUID | COURSE_ID | USER_ID | ADDTIME         | UPDATETIME
----------------------------------------------------------------
abc1 |COURSEID1  | USERID1 | 2019-01-01 00:00| 2019-01-01 00:00
----------------------------------------------------------------
abc2 |COURSEID1  | USERID1 | 2019-01-01 00:01| 2019-01-01 00:01

when I query the data with USER_ID and COURSE_ID of one of the duplicated rows, only one row returned, however I can get the rows with duplicated values when query the table with the primary key.

So my question is: Is this a bug of MySQL? and how can I avoid this error when working with MySQL?

server info

OS: CentOS 6

MySQL: MySQL 5.6


texts:
I am bit stuck in MongoDB while exporting my output to some other collection.

Scenario :

Actually, i want to query out my result to another collection which is successful in normal mongoDB( without Sharding).
But, when i Shard that same DB , Mongo DB is giving me error code 13435 which is cannot read from replica set and gives me below error set :

Error: command failed: {
    "ok" : 0,
    "errmsg" : "listIndexes failed: { ok: 0.0, errmsg: \"not master and slaveOk=false\", code: 13435, codeName: \"NotMasterNoSlaveOk\" }",
    "code" : 18631,
    "codeName" : "Location18631"
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:370:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1319:5
@(shell):1:1

Currently working on below versions of mongoDB :
Version : 3.4.6
OS : Windows Server 2012


texts: Find when Oracle 10g table was last populated

texts:
I am working on an Oracle 10g database which is being populated periodically with data from network nodes.
What Oracle table can I query to to get the time at which myTableOfInterest was last populated?

I have already searched and tried:
select max(ORA_ROWSCN), scn_to_timestamp(max(ORA_ROWSCN)) from myTableName;
Unfortunately this select results in no rows being found.


texts: LIKE search in bytea datatype (PostgreSQL)

texts:
Table:

\d blob

 Column  |       Type   | Collation |                 Default
---------+--------------+-----------+------------------------------------------
 id      | integer      |           | nextval('modlink_blob_id_seq'::regclass)
 content | bytea        |           |

This statement:

SELECT COUNT(*) "job" INNER JOIN "blob" ON ("job"."input_id" = "blob"."id")
  WHERE UPPER("blob"."content"::text) LIKE UPPER('%csv%');

Error message:

ERROR:  invalid memory alloc request size 1989028364

What can I do to make this statement not fail?


texts:
This is the structure of my table:

-- Table: t_keyword_conversion_ga
-- Create Table:
CREATE TABLE `t_keyword_conversion_ga` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`own_domain_id` int(11) DEFAULT NULL,
`keyword_id` int(11) DEFAULT NULL,
`traffic_date` date DEFAULT NULL,
`targeturl_id` int(11) DEFAULT NULL,
`entrance` int(11) DEFAULT NULL,
`transactions` int(11) DEFAULT NULL,
`item_revenue` decimal(9,2) DEFAULT NULL,
`goal1completions` int(11) DEFAULT NULL,
`goal2completions` int(11) DEFAULT NULL,
`goal3completions` int(11) DEFAULT NULL,
`goal4completions` int(11) DEFAULT NULL,
`goal5completions` int(11) DEFAULT NULL,
`goal6completions` int(11) DEFAULT NULL,
`goal7completions` int(11) DEFAULT NULL,
`goal8completions` int(11) DEFAULT NULL,
`goal9completions` int(11) DEFAULT NULL,
`goal10completions` int(11) DEFAULT NULL,
`goal1Value` decimal(9,2) DEFAULT NULL,
`goal2Value` decimal(9,2) DEFAULT NULL,
`goal3Value` decimal(9,2) DEFAULT NULL,
`goal4Value` decimal(9,2) DEFAULT NULL,
`goal5Value` decimal(9,2) DEFAULT NULL,
`goal6Value` decimal(9,2) DEFAULT NULL,
`goal7Value` decimal(9,2) DEFAULT NULL,
`goal8Value` decimal(9,2) DEFAULT NULL,
`goal9Value` decimal(9,2) DEFAULT NULL,
`goal10Value` decimal(9,2) DEFAULT NULL,
`medium` varchar(255) DEFAULT NULL,
`source` varchar(255) DEFAULT NULL,
KEY `id` (`id`),
KEY `keyword_id` (`keyword_id`),
KEY `traffic_date` (`traffic_date`),
KEY `own_domain_id` (`own_domain_id`,`traffic_date`),
KEY `targeturl_id` (`targeturl_id`)
) ENGINE=InnoDB AUTO_INCREMENT=581732054 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE COLUMNS(traffic_date)
(PARTITION p201101 VALUES LESS THAN ('2011-01-01') ENGINE = InnoDB,
PARTITION p201102 VALUES LESS THAN ('2011-02-01') ENGINE = InnoDB,
PARTITION p201103 VALUES LESS THAN ('2011-03-01') ENGINE = InnoDB,
PARTITION p201104 VALUES LESS THAN ('2011-04-01') ENGINE = InnoDB,
---->PARTITION p201105 VALUES LESS THAN ('2011-05-01') ENGINE = InnoDB,
PARTITION p201106 VALUES LESS THAN ('2011-06-01') ENGINE = InnoDB,
PARTITION p201107 VALUES LESS THAN ('2011-07-01') ENGINE = InnoDB,
PARTITION p201108 VALUES LESS THAN ('2011-08-01') ENGINE = InnoDB,
PARTITION p201109 VALUES LESS THAN ('2011-09-01') ENGINE = InnoDB,
PARTITION p201110 VALUES LESS THAN ('2011-10-01') ENGINE = InnoDB,
PARTITION p201111 VALUES LESS THAN ('2011-11-01') ENGINE = InnoDB,
PARTITION p201112 VALUES LESS THAN ('2011-12-01') ENGINE = InnoDB,
PARTITION p201201 VALUES LESS THAN ('2012-01-01') ENGINE = InnoDB,
PARTITION p201202 VALUES LESS THAN ('2012-02-01') ENGINE = InnoDB,
PARTITION p201203 VALUES LESS THAN ('2012-03-01') ENGINE = InnoDB,
PARTITION p201204 VALUES LESS THAN ('2012-04-01') ENGINE = InnoDB,
PARTITION p201205 VALUES LESS THAN ('2012-05-01') ENGINE = InnoDB,
PARTITION p201206 VALUES LESS THAN ('2012-06-01') ENGINE = InnoDB,
PARTITION p201207 VALUES LESS THAN ('2012-07-01') ENGINE = InnoDB,
PARTITION p201208 VALUES LESS THAN ('2012-08-01') ENGINE = InnoDB,
PARTITION p201209 VALUES LESS THAN ('2012-09-01') ENGINE = InnoDB,
PARTITION p201210 VALUES LESS THAN ('2012-10-01') ENGINE = InnoDB,
PARTITION p201211 VALUES LESS THAN ('2012-11-01') ENGINE = InnoDB,
PARTITION p201212 VALUES LESS THAN ('2012-12-01') ENGINE = InnoDB,
PARTITION p201301 VALUES LESS THAN ('2013-01-01') ENGINE = InnoDB,
PARTITION p201302 VALUES LESS THAN ('2013-02-01') ENGINE = InnoDB,
PARTITION p201303 VALUES LESS THAN ('2013-03-01') ENGINE = InnoDB,
PARTITION p201304 VALUES LESS THAN ('2013-04-01') ENGINE = InnoDB,
PARTITION p201305 VALUES LESS THAN ('2013-05-01') ENGINE = InnoDB,
PARTITION p201306 VALUES LESS THAN ('2013-06-01') ENGINE = InnoDB,
PARTITION pEOW VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */
1 row in set (0.02 sec)

I dropped the partition 'p201105'. Now I am trying to add it again with the below statement but it throws an error:

ALTER TABLE t_keyword_conversion_ga
ADD PARTITION (PARTITION p201105 VALUES LESS THAN ('2011-05-01'));

ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

How to add the same partition?


texts: Disconnected replica when recovering from a forced failover

texts:
I'm evaluating disaster recovery using read-scale availability group on SQL Server 2019 and Windows 2019. The cluster type is NONE.
The cluster works fine in general even when restarting replicas.
The issue arises when simulating a DR with the following action :

Stop the SQL server service on the primary node.
Force failover on secondary with ALTER AVAILABILITY GROUP [ag1] FORCE_FAILOVER_ALLOW_DATA_LOSS
Start the SQL server service on the primary node.

When started, the primary node joins as primary and exhibits a split brain behaviour, which seems to be fine following this document.

When the previous primary replica recovers, it will also assume the primary role ...

But the primary node can no more connect to the other node.
This secondary replica is not connected to the primary replica. The connected state is DISCONNECTED.
The same applies from the other node's perspective.
In the SQL Server logs I've got a message like

A connection timeout has occurred while attempting to establish a connection to availability replica 'xxxx(former secondary)' with id [yyy]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.

Of course, there is no firewall or intermediate issue since I can connect again by drop/create the AG.
Also, I used wireshark to get some insights into the traffic:

The flow looks okay, but the retransmits show some kind of "misunderstanding" between the nodes.
This seems like a software issue on SQL Server.
Appendix:
SQL Server version: Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64)   Jul 25 2020 11:26:55   Copyright (C) 2019 Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows Server 2019 Standard 10.0  (Build 17763: )
The cluster config
    CREATE AVAILABILITY GROUP [ag1]
    WITH (CLUSTER_TYPE = NONE)
    FOR REPLICA ON
        N'replica-node1' WITH (
            ENDPOINT_URL = N'tcp://10.61.137.7:5022',
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL,
            SEEDING_MODE = AUTOMATIC,
            SESSION_TIMEOUT  = 30,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL )
            ),
        N'replica-node2' WITH (
            ENDPOINT_URL = N'tcp://10.61.137.4:5022',
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL,
            SEEDING_MODE = AUTOMATIC,
            SESSION_TIMEOUT  = 30,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL  )
            );


texts: How to monitor InnoDB Disk I/O using Server Status Variables?

texts:
This question is extension of my previous question "How to monitor InnoDB Disk I/O Required for MySQL? "

As mention in my previous question i want to count total number of disk i/o per query, but while testing i got confused with some result ,

i am using below example ::

mysql> explain user;    ------->[user table from Website database]
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| username | varchar(50) | YES  | UNI | NULL    |                |
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| password | varchar(50) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

I have created a index on username.

My analysis is

before running any query system variable status was

'Created_tmp_disk_tables', '1'
'Created_tmp_files', '12'
'Created_tmp_tables', '8'
'Key_reads', '0'
'Key_writes', '0'
'Innodb_buffer_pool_reads', '425'
'Innodb_log_writes', '26'

when i tried

insert into user (`username`,`password`) values ("xyz1","xyz1");

'Created_tmp_disk_tables', '1'
'Created_tmp_files', '12'
'Created_tmp_tables', '8'
'Key_reads', '0'
'Key_writes', '0'
'Innodb_buffer_pool_reads', '425'
'Innodb_log_writes', '27' -------> (shows one write operation in Innodb_log )

when i tried

Select * from user where username ="xyz1";

'Created_tmp_disk_tables', '1'
'Created_tmp_files', '12'
'Created_tmp_tables', '8'
'Key_reads', '0'
'Key_writes', '0'
'Innodb_buffer_pool_reads', '425'
'Innodb_log_writes', '27'

and when i tried

UPDATE user SET `username`="xyz1", `password`="hello"
WHERE `username`="devlad1";

'Created_tmp_disk_tables', '1'
'Created_tmp_files', '12'
'Created_tmp_tables', '8'
'Key_reads', '0'
'Key_writes', '0'
'Innodb_buffer_pool_reads', '425'
'Innodb_log_writes', '29'------>[shows two write operation to log]

My first question are

  1). I am calculating disk i/o correctly or i am missing something here?

  2). I have create index on 'username' so while doing insert or update index >    count should be modify but varible "Key_writes" keep showing me '0' ,what >    does it means? is index is caching some where ?

thanks in advance!!!!


texts:
I'm building a game that have micro-services structure using docker.

The way i started to build my services is having a mongoDB instance to each service, so having OneToOne service to mongoDB instance,

Resources ---> resourcesDB (mongoDB instance 1)
Chat      ---> chatDB      (mongoDB instance 2)
Buildings ---> buildingsDB (mongoDB instance 3)
Battle    ---> battleDB    (mongoDB instance 4)
...

This really made sense at the beginning as those services for example have nothing in common and don't need have a connection to the same mongo instance, and when they do have something in common (building <-> resources) they just send a call to the needed service and that handles the rest (update his DB).

But i starting to think that this is not the right approach because of couple of things:

1 - resourcesDB for example have only 2 collections,

    Logs      ---> logging every resources change (building built, war lost etc.)
    Resources ---> 1 document per user holding how much he have.

meaning that this DB will not hold any huge data (logging will be   deleted after some time), so setting an instance just for those 2 collections is looking like a huge over kill, even if this instance will not have its own server and will share server with the service.

2 - maintaining and doing backups will require some extra work that I'm not sure that its needed in my case.

Each of the service will update his DB quite often, resource is doing calculation every second to see how much each user have, so it will update the db 'users amount' per second, aside with the rest of the requests like build a building (reduce resources) and other updates,
Chat service will update the DB every private message or chat message, Battle service will update troops place and stats and more stuff every second for each battle that is going on.

I'm thinking to chance that structure to one of the next:

maybe have only 1 instance of database with separated DB, so each service will connect to a separated database, but i read that it will still be a hassle to maintain backups of the DB's and scaling.
have 1 database, and have all the collections in it, every collection will be accessed by a different service and will never be accessed by 2, so i have nothing to worry about 2 service changing same collection.
leave it as it is, and go with many instances of mongoDB

few questions about the methods above:

what should i choose? having 1 database sounds good as it will save me a lot of resources and will make my life easier, but separating the services to many database sound logic to me as one service don't need to see collections or be able to access a collection that have nothing to do with his job.
I'm not sure how mongoDB handle many connections to the same instance, or to same database, as you can see it might get a lot of calls in a second even if i have pretty low amount of users.
I read about mongo Sharding, from what i understood it happens per database and not on the instance level, that's why i'm thinking about the 1 database option, as it quite useful in my case, am i right?


texts: Was Management Studio removed from SQL Server 2016 installation media?

texts:
I just tried installing SQL Server 2016 Enterprise but I noticed that Management Studio was not installed by default. I also noticed there is a separate link to SSMS in the installation guide which points to https://msdn.microsoft.com/en-us/library/mt238290.aspx

What was the reason for this decision?

The following is noted on the above URL:

  This generally available release of SSMS is free and does not require a SQL Server license to install and use.

Perhaps, this is it?


texts: Is it possible to create a MySQL slave from a MariaDB master?

texts:
I need to create a MySQL slave instance which replicates the data from a MariaDB master. (I guess it won't work.) Any advice on how to create this replica architecture using MariaDB and MySQL?

I am also investigating and considering using tungsten. Does anybody have experience with it?


texts: Is there overhead from using synonyms to access another database?

texts:
If one database is accessing tables on another database on the same SQL server through synonyms, does that incur any significant overhead to either of the databases?

Setup: We have one database (A) that contains all the data for our application. This database runs some time consuming calculations for a point in time, and then stores the output in another database (A_Reports).

Through the client application, a user can load reports hosted on SSRS. These reports connect to database A which uses synonyms to access the data stored on database A_Reports. Some reports might need some of the data from A, but that is rarely needed and only occurs under certain conditions that the report will always be aware of.

We want to keep the utilization of A low since it has all the data for the app servers and A_Reports is only used to store the calculated reports data.

I would assume that since they are on the same SQL server it doesn't matter which database is connected to. So the real question is, if we change the reports in SSRS to connect to A_Reports instead of A, will this allow reports to access the data quicker or enable database A to run its queries more efficiently?


texts: Powershell Error Handling W/ SQL Restore Jobs

texts:
I would like to rebuild many of our SQL scripts into Powershell to further integrate my department with the Windows Operations team and to open up a lot of other options.

Questions:

1-preference over invoke-sqlcmd vs. just using powershell and SMO cmdlets?

Do you have a major preference over invoke-sqlcmd vs. just using powershell and SMO cmdlets?

I am looking at using invoke-sqlcmd and copying over old code but with updated error handling, versus rewriting them into pure Powershell scripts.  I know there are some pitfalls with using invoke-sqlcmd but the ability to have my scripts already 80% written is a big driver to avoid rewriting them.

Would you recommend just reusing old scripts, or are there benefits I can't find which would justify rewriting them in powershell?

2-
Best Method To "Move To The Next Step"

How would you recommend having powershell execute the next entry in the array/loop only after the prior step has completed, successfully?

Each step of the job has a 'quit on success', I then plan on having it execute each step 1 at a time using a Powershell array or loop with all the step names in it.  If it fails at any step, break and display error.  Some steps take a long time however, and I don't want the other steps to start before the prior finishes completely.  There's probably some powershell logic I can use without having to query the job status tables.

3-Error Handling

In this case, would you use try-catch blocks in SQL and in Powershell, would you recommend doing it in just powershell?  I'm trying to pass the console output of the invoke-sqlcmd but this might be somewhere the native powershell variables might be easier to work with.

Currently I'm running:

  invoke-sqlcmd 'exec msdb..sp_start_job @job_name = "Refresh", @step_name = "step1"' -Verbose

This returns:

VERBOSE: Job 'Refresh' started successfully.

Would I then verify I had no errors by taking that output, perhaps using something like Write-Verbose, and performing my error handling that way?  It seems like there's a better way.

Thanks in advance.

Edit: I decided to just change the jobs into stored procs and execute those from Powershell using an array for now.  if anyone has a better suggestion let me know.


texts:
I found difficulty with writing SQL statement to calculate the time of each items of row.

My table structure is simple:

TblProduct(Product nvarchar(50), Serial nvarchar(50), Time_Checked datetime)

Below is  my sample data:-

PRODUCT1 Serial1 2014-04-02 07:01:21.000
PRODUCT1 Serial2 2014-04-02 07:02:21.000
PRODUCT1 Serial3 2014-04-02 07:03:21.000
PRODUCT2 Serial1 2014-04-02 08:01:21.000
PRODUCT2 Serial2 2014-04-02 08:02:21.000
PRODUCT2 Serial3 2014-04-02 08:03:21.000
PRODUCT3 Serial1 2014-04-02 09:01:21.000
PRODUCT3 Serial2 2014-04-02 09:02:21.000
PRODUCT3 Serial3 2014-04-02 09:03:21.000
PRODUCT1 Serial4 2014-04-02 10:01:21.000
PRODUCT1 Serial5 2014-04-02 10:02:21.000
PRODUCT2 Serial6 2014-04-02 10:03:21.000

I want to calculate the time for changing product by datediff(minute,start time, end time)
where start time as the last item of each series of product kinds

Start time: PRODUCT1 2014-04-02 07:03:21.000

End time:  PRODUCT2 2014-04-02 08:01:21.000

==>datediff(minute,start time, end time)=58 minutes

Finally, I want to return a result like a table below:-

PRODUCT1 Serial3 PRODUCT2 Serial1 58(minutes)
PRODUCT2 Serial3 PRODUCT3 Serial1 58(minutes)
PRODUCT3 Serial3 PRODUCT4 Serial4 58(minutes)


texts:
I have a transactions table with the following field:

Transactions
ID | amount | branch_id | supplier_id
1  | 1,200  |    2      |  1

Subsidiary
ID  | Name      | Type
1   | supplier1 | 0
2   | branch_2  | 1

I want to display this in my view with the id value properly so I joined the following table (I'm using laravel query builder)

$transactions = DB::table('transactions_table')
->leftJoin('subsidiary_table','transactions_table.supplier_id','=','subsidiary_table.id')
->get(array('transactions_table.id',
'transactions_table.amount',
'subsidiary_table.name as branch',
'subsidiary_table.name as supplier'));

I'm confused on how can I get it to display like:

Display View
ID  |   Ammount  |  Supplier  | Branch
1   |   1,200    |  Supplier1 | branch_2

Thanks for clarifying..


texts: dbo.SORT temporary run storage: 148643705716736' What does this number means?

texts:
I totally understand what this error means and how to resolve.
My question is what does this number (148643705716736) referring to?

Date      5/7/2018 1:36:58 AM
Log        SQL Server (Current - 5/7/2018 2:31:00
AM)
Source        spid125
Message
Could not allocate space for object 'dbo.SORT temporary run
storage:  148643705716736' in database 'tempdb' because the 'PRIMARY'
filegroup is full. Create disk space by deleting unneeded files,
dropping objects in the filegroup, adding additional files to the
filegroup, or setting autogrowth on for existing files in the
filegroup.


texts:
Hello Community,
To prepare for my exam I have completed many exercises. Most of them were solvable without any problems. But the following task I could not solve until now. It is:

You are given the relation scheme R (A,B,C,D) with the three functional dependencies:

AB → C
C → D
D → A

In which normal form is R (1st, 2nd or 3rd)? Justify your statement.

Could anyone of you please help me? A good explanation and substantiation would be very, very good!
Thank you sooo much!
P.S.: I already have one solution from a friend. But I do not know if it is correct…
He said that it is the second normal form because the non-key attributes C and D are fully functional depending on A, B. Besides he mentioned that it is not the third normal form because there is a transitive dependence between the non-key attributes (A, B → C and C → D).


texts: I am new to DBA stack exchange & this is my first question. Kindly help me in finding a low level documentation for Query Cache module in MySQL since I am planning to work with the source code of Query Cache. I googled a lot but I was provided with only high level details. Is it available in MySQL website (I tried but not an exhaustive search)? Please help me.


texts: How to store long string with netstring format in varchar column?

texts:
I am involved in project where we have to store very long string in varchar column (varchar(max)). Due to this case have to questions:

This column is one of other 40 columns in table (but only this has to be varchar(max)). To this table we will insert approximately 300 records per second. What is more this table will be partitioned for 32 partitions. What will be a good practise ?:

Leave this column in this table
Create new table with this column and ID (FK (references to main table)) ?
Any other approach ?

Data in this column will be stored in netstring format. Moreover we will have to retrieve some specific field from this long string from this column. What is the best and most efficient way to do that ? Is it s good idea to for example to parse this netstring to json format ?

Thank you in advance for your answers :)


texts: Set up replication and migrate to InnoDB with low downtime

texts:
Current situation is this:

I have a large legacy DB using only MyISAM tables, the size of whole
DB is 4GiB, one of the tables contains over 10 million rows, so it is
pretty large.
The database is used heavily and downtime should be as low as
possible, no more than 30 minutes (less is better).
All files are not under LVM (so, snapshot is not possible).

I want to set up simple master-slave replication and migrate all tables to InnoDB. What is the best plan for this migration? As I said, it should have low downtime and be as safe as possible

P.S. Currently I have no easily restorable backups, so adding somewhere a step of backing up is a good idea. But I think with master-slave replication backups will be easy. Also, please include what might go wrong during this plan.


texts: Execute Stored Procedure against CMS from within a SQL Server Agent job

texts:
Is it possible to execute a stored procedure (or any T-SQL) against a Central Management Server (CMS) via a SQL Agent job?

The idea here is to exercise the CMS to hit all the registered instances with a query (or action), in an unattended (non-SSMS, non-real time) way.


texts:
G'Day,

I found that someone had started on this topic on personal finance, I like to go further using a good personal finance plan I found on the internet which I would model. In advance please forgive me for a long post wanted to ensure much detail and it may sound like a rambling I'm just sorting my thoughts of this project as much as I could.

I would like to ask if there any concerns about this database design I am going to implement in MS SQL.

My main objective is to make our family's personal finance easier if a solution would involve MS SQL v12 (the free version) as a backend and the frontend is I'm unsure (?VBA Excel via ADO/?Powerquery via ADO/?C# program via ADO within Visual Studio 2015 Community with Web support so iPhone/iPAD could review result over personal network). Any thoughts on the frontend given this backend design?

The tables chosen as follows:
obvious field types isn't a concern at this time.
Meaning of word Term in Goals table helps classify short/medium/long term.

Users(**ID**,Name,Addtimestamp)
Reports(**ID**,Type,SubReportID,Addtimestamp)
SubHeadings(**ID**,Type,Description,Addtimestamp)
Transactions(**ID**,Datetimestamp,Description,Amount,Addtimestamp)
Goals(**ID**,Term,Priority,GoalDetail,TotalCost,Duration,Addtimestamp)
Targets(**ID**,Datetimestamp,Description,MonthlyCost,Addtimestamp)
Strategies(**ID**,*GoalID*,*TargetID*,Addtimestamp)
Evaluations(**ID**,*StrategiesID*,GoodDetail,BadDetail,Addtimestamp)

The relationships between tables are defined as follows:

Users "makes" Transactions  1:M
Transactions "identifies" Users M:1
Reports "summarise" Transactions 1:M  such as Assets / Liabilities for net worth
Transactions "creates" Reports M:1
Reports "contains" SubHeadings M:1  such as Cash, Property, Current Bills
SubHeadings "classified for" Reports 1:M
Transactions "involves" Goals M:1
Goals "requires" Transactions 1:M
Targets "is associated with" Goals 1:M
Goals "has" Targets M:1
Targets "supports" Strategies M:1
Strategies "contains" Targets 1:M
Strategies "reviewed by" Evaluations 1:M
Evaluations "is checked against" Strategies 1:M

Views from tables I will need but this only a rough sketch/guide.
There will be 4 members of the family as Users in database
Hoping Report table can calculate net worth as monthly figure instead yearly
SubHeadings is populated according to how the report containing headings, I believe queries can help me ensure all outputs from Transaction table is broken down by months Jul to Jun per Financial Year.
Transactions is manually entered at the moment but considering transforming data after importing via PowerQuery from my financial institution into Transactions.
Strategies (per financial year like 2016/2017) with Targets (like 5% decrease of say Telecommunication expenses) I am hoping MS SQL functions could or the frontend can make like "GoalSolver" in Excel to find a optimal solution given that populates Goals table to get me there. I can then track against actual for variation afterwards.

Main function of the front end would need to inital populate the tables via views? firstly, then frontend would receive manual input to place data via views into tables including transactions. Reporting will list how it goes for budgeting, forcasting is how the Strategies->Targets->Goals is played around and scheduling is sorting some transactions according to Goals's Priorities order where (I haven't decided if a lower number is a higher importance or vica versa).

That's about it I have in my thoughts for now.

Thanks and kind regards,
Peter.


texts: Copy Millions of Records from one table to another with the same schema, in the same database

texts:
I am trying to find the best way to copy a large amount of rows ( ~70million) from one table to another with the same schema inside the same database.

Our system currently runs with a set of A/B tables that are referenced elsewhere via a View. Our clients have the option to send us data either as a Full file or an Incremental file. When full files are ingested, the "inactive" table is truncated and loaded straight with the new data. For incremental files, we first copy all the data from "active" into "inactive" and then perform the incremental update. In both cases, when data loading is done we switch the view to the appropriate table.

This has been working fine for many years; typically clients send us data files at night time and their data is processed when nobody is using the system. However, one of our larger clients now wants to send us incremental files throughout the day. For a smaller sized client, the A->B copy operation is fairly quick (~30 seconds for 600k rows), but this large client's copy is taking 30-60+ minutes depending on load.

The schema for both tables is as follows (replace TableB with TableA):

CREATE TABLE [dbo].[TableA](
    [ClientId] NCHAR(10) NOT NULL,
    [ItemId] INT NOT NULL,
    [Amount] DECIMAL(19,6) NOT NULL,
    [Loaded] DATETIME NOT NULL,
 CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED ( [ItemId] ASC, [ClientId] ASC) ON [PRIMARY])

ALTER TABLE [dbo].[TableA]  WITH NOCHECK ADD  CONSTRAINT [FK_TableA_Items] FOREIGN KEY([ItemId])
REFERENCES [dbo].[Items] ([ItemId])

ALTER TABLE [dbo].[TableA] CHECK CONSTRAINT [FK_TableA_Items]

ALTER TABLE [dbo].[TableA]  WITH NOCHECK ADD  CONSTRAINT [FK_TableA_Clients] FOREIGN KEY([ClientId])
REFERENCES [dbo].[Clients] ([ClientId])

ALTER TABLE [dbo].[TableA] CHECK CONSTRAINT [FK_TableA_Clients]

CREATE NONCLUSTERED INDEX [IX_TableA_ClientId_ItemID_INC_Amount] ON [dbo].[TableA] ([ClientId] ASC, [ItemId] ASC )  INCLUDE ([Amount])

The insert/select statement we are currently using is a simple:

INSERT INTO [dbo].[TableB]  ( ClientId, [ItemId], [Amount], [Loaded] )
SELECT ClientId, ItemId, Amount, Loaded FROM [dbo].[TableA]

I've tried various methods for optimizing this copy; I've added TabLock to the target table, tried setting Trace Flag 610 and tried many different batch sizes, but nothing seems to work. One variation of the queries I've tried is:

DECLARE @BatchSize BIGINT = 50000;
WHILE 1 = 1
BEGIN
      INSERT INTO TableB WITH ( TABLOCK ) ( [ClientId], [ItemId], [Amount], [Loaded] )
      SELECT TOP ( @BatchSize ) ClientId, ItemId,Amount, Loaded
      FROM      TableA t1
      WHERE     NOT EXISTS ( SELECT 1 FROM TableB WHERE ClientId = t1.ClientId AND ItemId = t1.ItemId );

      IF @@ROWCOUNT < @BatchSize
         BREAK;
END;

I've tried dropping/recreating indexesbut this too was proving costly. The best time I've come up with is about 5 minutes, but this involves dropping the target table and performing a select into, then adding the indexes and check constraints. This was purely "educational"--I can't go this route due to schema-bound views and some other items preventing this approach (in addition to our normal view, we have a "B" view which points to the other table for use during this process only)

While I know that SSIS is probably the best approach here, it is out of the question for now--I'm tasked with speeding up the existing process, if possible. I do have control over what procedures are called, etc from C#.

With that said, do I have any other options? I can do pretty much whatever I want to the inactive table as nothing is using it except these procedures. One thing I was considering is to somehow export the data with BCP from C# and then re-ingest it. Aside from not being sure if its possible, I've no idea if it would be any better (or worse for that matter)


texts: Mysql query to retrieve the latest updates based on DATETIME for each parameter

texts:
I have a station_data table where sensors from different weather stations report their data.

Sample data:
id(AI),station_code,parameter_code,value,date_time
1,1,1,1.234,2015-01-01 00:00
2,2,4,2.345,2015-01-02 00:00
3,1,8,3.456,2015-01-03 00:00
4,1,9,4.567,2015-01-04 00:00
5,3,3,5.678,2015-01-05 00:00
6,4,2,6.789,2015-01-06 00:00

I am trying to get to this result:

id(AI),station_code,parameter_code,value,date_time
9000,1,1,1.234,2015-08-07 09:00
9001,1,2,2.345,2015-08-07 10:00
9002,1,3,3.456,2015-08-07 09:00
9003,1,4,4.567,2015-08-07 10:00
9004,1,5,5.678,2015-08-07 10:00
9005,1,6,6.789,2015-08-07 09:00
.
.
.
9998,10,1,1234.56,2015-08-07 09:00
9999,10,2,2345.67,2015-08-07 09:00

The result should contain every pair station_code / parameter code once and the corresponding latest update time.

At the moment I am running this query:

SELECT DISTINCT station_code,parameter_code
FROM station_data
ORDER BY station_code,parameter_code ASC;

which results to a list of the station and parameter codes being monitored and then I chain query the database (in a for loop) to select the latest update time from the above list.

SELECT * FROM station_data
WHERE parameter_code=X AND station_code=X
ORDER BY date_time DESC LIMIT 1;

But this way of getting the data is nowhere next to optimum. Chain querying the database overloads the mysql server for a few minutes.

Is there any way to get the same result set by using a more efficient single query ?


texts:
On occasion our MySQL 5.6 server loses some user privileges when restarted.  The CREATE USER and GRANT commands are run through phpMyAdmin and function for as long as the server is running.  After a restart of MySQL, the user record still exists but the privileges are missing.  In phpMyAdmin User Accounts, the user appears with global privileges of "Usage".  The expected SELECT permission is missing.

Users and privileges are created using:

CREATE USER 'userName'@'hostName' IDENTIFIED BY 'password';
GRANT SELECT ON *.* TO 'userName'@'hostName';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON `dbName` . * TO 'userName'@'hostName';

What could be causing the loss of privileges?  Is there something else that should be executed in addition to these commands to force MySQL to persist the permissions?