texts: Does MySQL 5.6 support fan-in replication?

texts:
I've known for quite awhile that Continuent's Tungsten Replicator supports fan-in replication (example).

By fan-in replication I mean combining multiple datasources into a single instance (like a datawarehouse).

I recently came across this MySQL deck that seems to suggest that this could be done with MySQL 5.6. Specifically slide/page 35 says "Arbitrary Topology" under Topologies:.

Can someone confirm that fan-in replication is possible with MySQL 5.6?

Has anyone setup this topology in 5.6?

Thanks!


texts: SQL Server Buffer Pool Extension in FullText Index Population

texts:
I have a server that has 32GB of RAM and SQL Server 2014 Enterprise installed over it. I want to try the impact of Buffer Pool Extension (BPE) on Full Text Population.

I created a table and populated it with dummy information till it reached 100GB of size.

First I tried populating the full text index before BPE.  It took 3 hours.
Then I tried populating the full text index with BPE configured on a 140GB SSD with 120GB allocated memory. The weird thing is the population of the full text index didn't get any faster.

I used the following query to create the BPE on SSD then restarted the instance:

alter server configuration
set buffer pool extension
on ( filename = 'Z:\Cache.bpe' , size = 120gb )

The questions are :

How to check the buffer pool usage of SQL Server ( I want to know if the BPE is used )?
Does BPE affect FullText index population?
Did I miss any Server or Buffer Pool configuration?


texts: Side effects of using updlock lock hint?

texts:
I was just exploring UPDLOCK hint in SQL-Server 2008 and I found it is very useful in avoiding certain deadlock scenarios. There are always some side effects when using these types of lock hints but I could not find any for this one.

So what happens if I use it in all my update queries? I am not talking about select, just update. How will it affect me negatively?


texts: Index Fragmentation Monitoring: Can I make this query faster?

texts:
I have a query that is run against each database on two of my servers using a PowerShell script to initiate the script and throw the results into a CSV thus the need to have temp tables.

Is there a way I can limit what I am returning any further then I already have?  I tried adding my WHERE clauses to the temp table I am iterating through, as WHERE clauses on sys.dm_db_index_physical_stats do not actually work as a predicate, but rather just a filter after the fact according to MS docs.

DECLARE @object_id int
DECLARE @index_id int
DECLARE @partition_number int
DECLARE @DatabaseID int
SET @DatabaseID = DB_ID()

SELECT [object_id], [index_id], [partition_number], [rows] INTO #tempTest FROM (
SELECT indexes.[object_id], indexes.[index_id], partitions.[partition_number], partitions.[rows] FROM sys.tables AS tables
INNER JOIN sys.indexes AS indexes on tables.[object_id] = indexes.[object_id]
INNER JOIN sys.schemas AS schemas on tables.[schema_id] = schemas.[schema_id]
INNER JOIN sys.partitions AS partitions ON indexes.[object_id] = partitions.[object_id] AND indexes.[index_id] = partitions.[index_id]
WHERE schemas.[name] <> 'Audit'
AND indexes.[type] IN(1,2,3,4)
AND indexes.is_disabled = 0
AND indexes.is_hypothetical = 0
AND partitions.[rows] > 1000000) AS x

CREATE TABLE #tempTest2 (NK varchar(35), ServerName varchar(35), TimeStamp datetime, DatabaseName varchar(50), SchemaName varchar(50), ObjectName varchar (250), IndexName varchar (250),
ObjectType varchar(30), IndexType varchar (30), PartitionNumber int, PageCount bigint, avg_fragmentation_in_percent float)

DECLARE db_cursor CURSOR FOR

SELECT [object_id], [index_id], [partition_number] FROM #tempTest

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @object_id, @index_id, @partition_number

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO #tempTest2 (NK, ServerName, TimeStamp, DatabaseName, SchemaName, ObjectName, IndexName, ObjectType, IndexType, PartitionNumber, [PageCount], avg_fragmentation_in_percent)

       SELECT CONCAT(objects.[object_id], indexes.[index_id]) AS NK,
       @@SERVERNAME AS ServerName,
       GETDATE() AS TimeStamp,
       DB_NAME(@DatabaseID) AS DatabaseName,
       schemas.[name] AS SchemaName,
       objects.[name] AS ObjectName,
       indexes.[name] AS IndexName,
       objects.type_desc AS ObjectType,
       indexes.type_desc AS IndexType,
       dm_db_index_physical_stats.partition_number AS PartitionNumber,
       dm_db_index_physical_stats.page_count AS [PageCount],
       dm_db_index_physical_stats.avg_fragmentation_in_percent AS AvgFragmentationInPercent
FROM sys.dm_db_index_physical_stats (DB_ID(), @object_id, @index_id, @partition_number, 'LIMITED') dm_db_index_physical_stats
INNER JOIN sys.indexes indexes ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] AND dm_db_index_physical_stats.index_id = indexes.index_id
INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] IN('U','V')
AND objects.is_ms_shipped = 0
--AND indexes.[type] IN(1,2,3,4)
--AND indexes.is_disabled = 0
--AND indexes.is_hypothetical = 0
AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
AND dm_db_index_physical_stats.index_level = 0
AND dm_db_index_physical_stats.page_count >= 1000
--AND schemas.name <> 'Audit'
    FETCH NEXT FROM db_cursor INTO @object_id, @index_id, @partition_number
END

CLOSE db_cursor
DEALLOCATE db_cursor

SELECT * FROM #tempTest2

I think I am going to try moving the WHERE objects.[type] IN('U','V')
AND objects.is_ms_shipped = 0 to the temp table above and comment it out from the below section.

I have already read:

SQL Server Index Maintenance – You’re Doing It Wrong by Sean Gallardy
Stop Worrying About SQL Server Fragmentation by Brent Ozar
How much will it cost or save to rebuild that index? by Jeff Moden


texts: Accessing ORAWSV servlet via browser - Error 404 (NOT FOUND)

texts:
Based on the recommendations in the official Oracle documentation link below, I have activated the "orawsv" servlet. The servlet has been added successfully on the xdbconfig.xml file. The application server is based on Apache Tomcat 8.50.
Link of Oracle documentation: https://docs.oracle.com/en/database/oracle/oracle-database/19/adxdb/native-XML-DB-web-services.html#GUID-108279B4-0315-4D8D-86A2-6487767ED280
But, when I try to access the servlet via the browser (via the link http://xxxxx:8080/orawsv) the following error appears:
HTTP Status 404 – Not Found
Type: Status Report
Message: /orawsv
Description: The origin server did not find a current representation for the target resource or is not willing to disclose that one exists.
Please can anyone help me?


texts: seeking a confirmation -- SQL Server backup performance

texts:
Possible Duplicate:
  Is SQL Server Backup single or multi-threaded?

Does SQL Server uses a separate writer thread for each backup device (backup file) or separate thread is used only when you stripe your backup across multiple backup volume (drive letter, LUN or mount point)?

for e.g. do we get three write thread here or

backup database dbName to DISK = 'D:\file1.bak',
                      DISK = 'D:\file2.bak',
                      DISK = 'D:\file3.bak'

OR  the following speeds up the backup because you are writing to different volume

backup database dbName to DISK = 'D:\file1.bak',
                      DISK = 'E:\file2.bak',
                      DISK = 'F:\file3.bak'

In above each drive letter is a separate IO path. I understand IO throughput is also very important for backup speed.


texts: How many DBMS's can be installed on a single server?

texts:
The software in our organization uses various databases, many on different database platforms.

Can we put all the various database platforms on a single machine?  What are the limitations to this?

Should we have a single machine for each DBMS type?  What are the limits to the number of database per DBMS in this example?


texts: SQL Server 2008: how do I hide data from a DB Maintainance User?

texts:
Our organisation employs a third party company to do maintenance on our database. To be honest, currently they act as our DBA's since we do not have one.

In order to do the maintenance work, they are using the SA user.

Is there a way to make them a new user they can use, to do all of the administrative operations without being to actually see the data. So, they can see all the tables and columns, but they will be unable to see any row.

If I'm missing something, please feel free to enlighten me since my DBA knowledge is fairly poor.


texts: How to trace a MySQL session like in Oracle

texts:
I have an application running in MySQL 5.1.58 community edition.

My application is working fine, but suddenly any query stuck and then after all queries is getting logged in slow query log. I am curious to know whether which query is the real culprit.

In Oracle, we can easily trace the session to find all queries executed in a user session. Is there feature in MySQL through which I can find the list of all queries during a single session.


texts: Alternatives to LIKE and FTS for mid name search

texts:
I have a question about what are the alternatives for LIKE '%%' and Full-Text Search when it comes to searching organisation names, firstnames, surnames, etc.

The LIKE '%%' causes table scan, however returns the exact results for names containing . The FTS keeps returning results that are "sort of same". FTS is usually offered as an alternative, but I don't understand why. Isn't this meant for unstructured data search? In my opinion it's no alternative for structured data like names.

I've also tried implementations of SOUNDEX, Levenshtein, etc., etc. But nothing gives me the desired performance.

I'm really curious how the experts handle this!

I was afraid of that already :). I have several "names", like organisationname, and indeed, firstname, surname, etc. So the thing our users sometimes only want to use parts of the name where the part to search for is in the middle of a name.

I've tried the following

SELECT  *
FROM    [dbo].[companies] a
WHERE   a.[organisationname] LIKE '%FEK%'

This returns 60 rows, which is the preferred result. However causes a table scan because of the first % wildcard.

When using FTS

SELECT  *
FROM    [dbo].[companies] a
WHERE   CONTAINS(a.[organisationname], '"*FEK*"')

This returns 11 rows. It seems like the wildcard before the term is eliminated.

SELECT  *
FROM    [dbo].[companies] a
WHERE   CONTAINS(a.[organisationname], 'FORMSOF(INFLECTIONAL, "FEK")')

This returns 2 rows. Inflectional doesn't have any effect (probably "of course").

Hope you can give me a clue on how to better use FTS in this, or perhaps another alternative.


texts:
I am ran into a weird problem this particular query is not working

insert into `timesheet`
    (`type`,`app_id`,`app_name`,`app_title`,
     `app_path`,`timestamp_start`,`timestamp_end`,`duration`,
     `user_id`,`matter_id`,`updated_by_user_id`,`timestamp_create`,
     `source`,`user_rate`,`cost`,`description`,
     `location_name`,`conflict`,`suggestion`)
Values
    ('PLUGIN', '5232', 'oMgqTrYiZVQp.exe', 'qZXhgWtnwvQq',
     'k', '2016-05-06T18:21:29.000Z', '2016-05-18 19:29:50.000', '1021101',
     '4', '1', '-1', '2019-06-15T01:13:34.000Z',
     NULL, '15000', '4254.5875', NULL,
     NULL, '0', NULL)

WHat I was doing is running man this query with different values, rest are working fine, but for this particular query, it throws the error.

Error Code: 1136. Column count doesn't match value count at row 1

Can you pelase let me know what I am doing wrong or any hint would be appreciated so that i can debug it


texts: PostgreSQL force upper case for all data

texts: Is there a way to force all text data to uppercase without recurring to writing a function for every table or doing it client side?


texts: Cassandra in a Public Cloud - Which Snitch?

texts:
I am planning on deploying Cassandra in a public cloud (like Amazon AWS).  The cluster will span multiple zones and regions.  The cluster will also require logical DC separation within a given region.  I know that this is achievable using the Ec2MultiRegionSnitch and the logical DC separation can be achieved using the "dc_suffix" parameter in the cassandra-rackdc.properties file.  I also know that this can be achieved using the GosspingPropertyFileSnitch by assigning DC and RACK values to each Node in the same aforementoined file.

Since both snitches can be used to achieve my needs, then what is the benefit of using one snitch over the other?  Are there any specific pros and cons to using the Cloud specific snitch over the Gossip based snitch in a public cloud?  Any restrictions to one over the other in the public cloud?

I have googled around myself and while there is plenty of information regarding each snitch, I can't seem to find anything related to why one snitch would be preferred over the other in the public cloud.

Any guidance is greatly appreciated.


texts:
Say I have a constraint

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);

that needs to be modified so that zipcode length is 6.

Do I have the drop the constraint zipchk first and then recreate it ?

ALTER TABLE distributors DROP CONSTRAINT zipchk;
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 6);

or is there a MODIFY (like) command :

ALTER TABLE distributors MODIFY CONSTRAINT zipchk CHECK (char_length(zipcode) = 6);


texts: MySQL Replication : 1 Slave / Multiple Masters

texts:
I have a web app with 2 web servers and 2 database servers.
The DBs are setup for multimaster replication. (this is the primary environment)

I also have the exact same setup on a different location acting as standby, in case the primary env fails. (this is the backup env)

What I need is for the backup env to be in sync with the dbs of the primary site.
However, all DBs in both environments have already replication configured.

How can I achieve my goal?

Thanks


texts: Change column value status to be interrupted if no new record return after 10 mins

texts:
I have three tables ,scadainfo_online, scadainfo_limit and scadainfo_status, data populated just like their name, self-described.

My purpose is changing automatically "status" column's values in scadainfo_status if no new record get in scadainfo_online values in 10 minutes. there is a one-to-one relationship using column item_id.

So, how can I do it? Google told me about a job with about 30 seconds interval, is there another answer? Thanks.


texts: most effective architecture to apply mulitple functions

texts:
I have a table that is the only source for a second table.  Currently the second table is created using a CTAS (create table as select) statement that pulls in all the columns from the first table and includes a dozen more columns that are functions that we have written.  These functions take a column or two from the source table and transform them into new data elements.

In some cases, the columns that are used as parameters for the functions are sparsely populated.  The functions are compiled natively and utilize the result_cache.  The first thing that happens in each function is a null check on the input parameters and a return of NULL.

I have a few questions:

I have full control over the architecture of the source table (as I create it in an earlier process).  Is there anything I can do with the source tables (index, partitions, etc.) that will help the CTAS run more efficiently?
Should I be using a CTAS at all or would an UPDATE be more efficicent (my gut here says no way, but I'd like some validation)
Is there something else I can do to the functions to make them more efficient?
Is there anything else I should be thinking about?


texts: Split datetime records that fall between 10pm to 7am into new record

texts:
I am very new to SQL Server. I am trying to split time in time out records that fall between night shifts from a employee shifts table but I am having a difficult time doing so. Night Shift runs from 10pm to 7am.

Example below might help make things clearer.

E.g

EmployeeId   InTime                     OutTime
----------   -----------------------    -----------------------
1            2018-08-10 21:00:00.000   2018-08-11 09:00:00.000

Desired Output

    EmployeeId   InTime                     OutTime                   Type
    ----------  -----------------------    -----------------------   -------
    1            2018-08-10 21:00:00.000    2018-08-10 22:00:00.000   day
    1            2018-08-10 22:00:00.000    2018-08-11 07:00:00.000   night
    1            2018-08-11 07:00:00.000    2018-08-11 09:00:00.000   day

code for current table:

DECLARE @tbl TABLE
    (
        EmployeeId INT,
        InTime DATETIME,
        OutTime DATETIME
    )

INSERT INTO @tbl(Employee_Id,Shift_Start,Shift_end) VALUES (1,'2018-08-10 21:00:00.000','2018-08-11 09:00:00.000')


texts: Conditionally insert a value in an array when creating a new column

texts:
I have some data in a jsonb col that I need to clean and insert into another text array column.
What I have:
[
  {
    "v": "birthday"
  },
  {
    "v": "reference"
  }
]

What I want (_text):
{birthday, reference}

My query:
select array[col -> 0 ->> 'v', col-> 1 ->> 'v'] from src

gives the expected result as above.
However, when the value is null, I want the value not to be inserted i.e. no NULL value nor empty:
[
  {
    "v": "birthday"
  }
]

should result in
{birthday}

and not
{birthday, [NULL]} -- NULL value
{birthday,} -- Empty string with coalesce

How do you conditionally insert an element into an array when creating one, based on the value of the element?


texts: SQL Server 2012: Generate Scripts from command line

texts:
I am running SQL Server 2012.

The SQL Server Management Studio has the option to right click on a database then select Tasks and Generate Scripts.

Is there a way to automate that via command line somehow?

I want to create a script that includes the schema and data of the entire data base.

Tools like ScriptDB and sqlpubwiz.exe all seem to target SQL Server 2005.
What about SQL Server 2012?


texts: Error: CImageHelper::Init () Failed Load of symbol file with path

texts:
Using SQL server 2017 (14.0.3029.16) with Always On High Availability environment.
Getting error:

CImageHelper::Init () Failed load of symbol file with path = C:\Program Files\Microsoft SQL Server\MSSQL14..... Error Code 0xc0000004

There is nothing before or after this error and all I have to go by is a SPID. there is no set time, its happened once at 12:30AM and another time at 10:1 5AM.
I did some googling and there was one recommendation of reducing the MAXSIZE of the server memory. We have it set to 433,600 with 500,000 available.
How can I solve the problem and get rid of this error?
Additional information

Total RAM on the server is 500GB
Forced encryption is not enabled

Responses to comments

Anything in windows event log - app or system? Any mini dumps generated?

Event viewer has nothing that correlates with those time frames but something I will have to review further because I do see some errors.

For mini dumps - just check select * from sys.dm_server_memory_dumps

Nothing came back.


texts: combined contents of 2 columns can't contain duplicates

texts:
This is a hard question to ask. I find it difficult to put into words.

I have two columns where the content of column A can not appear in column B and vice versa. So, the combined contents of both columns needs to be unique.

In other words: I want any value that already exists in column A not to be allowed in either A or B. Likewise for B (no duplicates in B or A).

Is there a way to do this in MySQL with only a table schema change?

I also don't want to use triggers to prevent insertions.

MySQL version 8.0.18


texts: Need help understanding very high NumberOfActiveConnections counter data

texts:
Need help understanding the following Performance Counter data that my DBA just forwarded the following image for a stress test.

The reason is after the stress test continues for some time the Application comes to a standstill (very very slow) - but not crashes and the CPU usage (on the Sql Server) is also quite high but the RAM usage (for the Sql Sever) is not - and the following error is caught in the log:

ExceptionSystem.Data.SqlClient.SqlException: Timeout expired.
The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

So we started capturing Performance Counters in ADO.NET data for understanding the issue with Connection Pooling.

Here is the ActiveConnections data:

Here is the Performance Counters graph data:

And only the ActiveConnections graph:

And here is the runnable_tasks graph:


texts:
The documentation for MySQL isn't explicit on this point, but I'm wondering whether the scope for a function with respect to DETERMINISTIC is database global, or by some other scope?

Consider this definition:

CREATE
FUNCTION test()
RETURNS VARCHAR(255) DETERMINISTIC
RETURN USER();

This function is determinstic for all calls in the same connection, as user() will always return the same value. However, does DETERMINISTIC mean that "computed" values are shared across connections?

Defensively I would treat this function as NOT DETERMINISTIC but the question came up and I'm curious about the answer.


texts: Why does SQL Server resume mirroring after server is restarted?

texts:
I paused database mirroring before I rebooted the Windows server, and I found that the database had resumed mirroring automatically after the reboot.

This is not what I want--I want to resume it manually after I do some verification.  Is it normal for database mirroring to resume automatically after a reboot, even if it was paused prior to the reboot?


texts: Transaction log for SQL Server is too big

texts:
I have a database that has a 33 gb transaction log. It is caused by the fact that the database is in full recovery mode has been used for over 5 years prior to the first backup.

I want to truncate and shrink the log file with the following command

Backup log dbname with truncate_only
go
DBCC shrinkfile('logicalfilename',100)

I want to know after I ran the above command, in the future, will I be able to do a restore to a point in time between Future full backup 1 and 2?

Time Line

Pre-Truncate Backup
truncate and shrink
Future full backup 1
Future full backup 2

Thanks,


texts: Update single field in a document (document.update($set)?)

texts:
Can't seem to figure out how to update a single field.

Whatever I try, to update one field (picture: "url") the rest of the fields are being removed and set to null.

I use the following command now:

Meteor.users.update(
          userId,
          {
            $set: {
              profile: {
                picture: fileURL
              }
            }
          }
        );

And this is what happens with the rest of the object after update.

{
  "data": {
    "user": {
      "_id": "J222CqhR777GcXD",
      "createdAt": "Tue Sep 11 2018 19:23:55 GMT-0400 (EDT)",
      "email": "scott@example.com",
      "profile": {
        "about": null,
        "firstName": null,
        "lastName": null,
        "phone": null,
        "picture": "https://image.path/source.jpg"
      }
    }
  }
}

Is there another operator I am missing or some other way to do this?

In another document I am querying for all the data before, storing it in a React state and then passing all the fields that I have not changed to the update, but that seems rather inefficient and unnecessary step(???)

Is there really not a way to update just a single field without affecting the rest of the document?

cheers guys


texts:
Can we reply on the order of columns in an UPDATE?

SET @x = 0
UPDATE table1 SET
col1=IF(col1 IS NULL, NULL, @x:=@x+col1),
col2=IF(col2 IS NULL, NULL, @x:=@x+col2),
col3=IF(col3 IS NULL, NULL, @x:=0)

Is it guaranteed that col1 is UPDATEd before col2?

I do care about the order in calculating the value @x.


texts: Do a lot of error checks affect query performance and is it bad practice?

texts:
If I have a stored procedure with a handful of error checks that come before the queries are executed and throw exceptions as necessary, could this affect performance? Is it bad practice to do this?

For example, say I had 10 logical case checks against the parameters of this stored procedure, would that be fine?...What if I had 100 check cases?

(The functional programmer in me wants to not leave any holes, but the relational developer in me feels like this could lead to a bad query plan being generated.)

Example of how I'm doing the error checking:

IF @Parameter1 IS NULL OR @Parameter2 IS NULL OR @Parameter3 IS NULL
BEGIN
    THROW 50000, 'Error Message 1', 1;
END
ELSE IF @Parameter1 = 'Value1' AND @Parameter2 <> @Parameter3
BEGIN
    THROW 50001, 'Error Message 2', 1;
END


texts: MySQL 5.6.27: How to get latest pairs for different points in time in a single query?

texts:
I use the following query to get pairs of latest s1.value for given group ids and some fixed date. Now if I want to query pairs for different dates I have to run this query several times with different dates. My question is can I rework this query so I could query value pairs for several dates at once?

SELECT
    s1.group_id, s1.value, s1.ts_week_start
  FROM stats_kpi AS s1
  WHERE
    (SELECT COUNT(*) FROM stats_kpi as s2
       WHERE s2.group_id = s1.group_id
         AND s2.ts_week_start >= s1.ts_week_start
         AND s2.ts_week_start <= timestamp('2018-10-01')
    ) <= 2
    AND s1.group_id IN (3939)
    AND s1.ts_week_start <= timestamp('2018-10-01')
  ORDER BY s1.ts_week_start DESC;

UPD #1

Sample input:

group_id | value | ts_week_start
       1 |     1 | 2018-01-01 00:00:00
       1 |     2 | 2018-01-02 00:00:00
       1 |     3 | 2018-01-03 00:00:00
       1 |     4 | 2018-01-04 00:00:00
       1 |     5 | 2018-01-05 00:00:00
       2 |     1 | 2018-01-01 00:00:00
       2 |     2 | 2018-01-02 00:00:00
       2 |     3 | 2018-01-03 00:00:00
       2 |     4 | 2018-01-04 00:00:00
       2 |     5 | 2018-01-05 00:00:00

I want to get the following, for two groups(1, 2) and three dates (2018-01-05, 2018-01-02, 2018-01-01)
get pairs for latest and previous to latest values to the date, i.e. the desired result would be like:

group_id | value | ts_week_start
-- for group 1 and dates 2018-01-02/2018-01-01
       1 |     1 | 2018-01-01 00:00:00
       1 |     2 | 2018-01-02 00:00:00

-- for group 1 and date 2018-01-05
       1 |     4 | 2018-01-04 00:00:00
       1 |     5 | 2018-01-05 00:00:00

-- for group 2 and dates 2018-01-02/2018-01-01
       2 |     1 | 2018-01-01 00:00:00
       2 |     2 | 2018-01-02 00:00:00

-- for group 2 and date 2018-01-05
       2 |     4 | 2018-01-04 00:00:00
       2 |     5 | 2018-01-05 00:00:00

For 2018-01-01 there's only one record in the result - latest one and no previous.

Schema and data:

CREATE TABLE `stats_kpi` (
  `group_id` bigint(20) NOT NULL,
  `value` double unsigned DEFAULT NULL,
  `ts_week_start` timestamp NULL DEFAULT CURRENT_TIMESTAMP
  )

INSERT INTO `stats_kpi` VALUES (1, 1, timestamp('2018-01-01'));
INSERT INTO `stats_kpi` VALUES (1, 2, timestamp('2018-01-02'));
INSERT INTO `stats_kpi` VALUES (1, 3, timestamp('2018-01-03'));
INSERT INTO `stats_kpi` VALUES (1, 4, timestamp('2018-01-04'));
INSERT INTO `stats_kpi` VALUES (1, 5, timestamp('2018-01-05'));
INSERT INTO `stats_kpi` VALUES (2, 1, timestamp('2018-01-01'));
INSERT INTO `stats_kpi` VALUES (2, 2, timestamp('2018-01-02'));
INSERT INTO `stats_kpi` VALUES (2, 3, timestamp('2018-01-03'));
INSERT INTO `stats_kpi` VALUES (2, 4, timestamp('2018-01-04'));
INSERT INTO `stats_kpi` VALUES (2, 5, timestamp('2018-01-05'));

UPD #2

I modified proposal of Kapil Bhagchandani so it's a single query now. What I don't like is the querying dates from the stats_kpi table while I know them upfront:

SELECT
DISTINCT s1.group_id, s1.value, s1.ts_week_start
FROM stats_kpi AS s1,
  (SELECT DISTINCT(s3.ts_week_start) AS ts FROM stats_kpi AS s3
     WHERE s3.ts_week_start IN (TIMESTAMP('2018-01-05'), TIMESTAMP('2018-01-02'), TIMESTAMP('2018-01-01'))) AS dates
WHERE(
    SELECT COUNT(*) FROM stats_kpi as s2
    WHERE s2.group_id = s1.group_id
    AND s2.ts_week_start >= s1.ts_week_start
    AND s2.ts_week_start <= dates.ts
) <= 2
AND s1.group_id IN (1,2)
AND s1.ts_week_start <= dates.ts
ORDER BY group_id,value,s1.ts_week_start DESC;


texts: How much should I care about prepared statements for simple queries?

texts:
I wonder how much I should care about prepared statements (and therefore try to optimize my code) in case of pretty simple statements.

The question originates from the fact that I recently noticed ActiveRecord (a Ruby ORM) writes different SQL queries depending on the way I chain the commands. Here's an example.

2.1.1 :004 > Account.first.domains.explain
  Account Load (0.5ms)  SELECT "accounts".* FROM "accounts" ORDER BY "accounts"."id" ASC LIMIT 1
  Domain Load (0.8ms)  SELECT "domains".* FROM "domains" WHERE "domains"."account_id" = $1  [["account_id", 1]]
 => EXPLAIN for: SELECT "domains".* FROM "domains"  WHERE "domains"."account_id" = $1 [["account_id", 1]]
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on domains  (cost=0.00..1.35 rows=1 width=222)
   Filter: (account_id = 1)
(2 rows)

vs

2.1.1 :005 > Domain.for_account(Account.first).explain
  Account Load (0.7ms)  SELECT "accounts".* FROM "accounts" ORDER BY "accounts"."id" ASC LIMIT 1
  Domain Load (0.9ms)  SELECT "domains".* FROM "domains" WHERE "domains"."account_id" = 1
 => EXPLAIN for: SELECT "domains".* FROM "domains"  WHERE "domains"."account_id" = 1
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on domains  (cost=0.00..1.35 rows=1 width=222)
   Filter: (account_id = 1)
(2 rows)

As you can see, the first statement generates a prepared statement, whereas the second one does not.

Is there a truly benefit in using prepared statements for such simple queries, or generally speaking the benefits I get in these cases are small enough to not justify major refactoring or workaround at code level?


texts: Is there a canonical list of MySQL defaults which should be changed

texts:
I'm setting up a new mysql server as we're building a new app at work.
Most of the developers/operations guys are familiar with mysql.

Many of mysql's settings (eg strict mode) are set badly by default for backwards compatibility.

Is there a widely accepted list of which settings have defaults that are dangerous, confusing or otherwise wrong?

I'd like to avoid things like the string 'hello' being considered equal to the integer zero, for instance.


texts:
I've been tasked with gathering performance metrics on a few database servers that have one TempDB data file.  It seems prevailing wisdom is to have one TempDB data file per core up to a certain point.  Both servers in question have 4 CPUs, so we're going to add three more on each.

My question is which performance metrics should I gather to get a solid depiction of where my servers are at?  I want to gather data pre-change and post-change for comparison.


texts: sql server 2012 master database log is corrupted

texts:
sql 2012 master database is corrupted, i have recent backup of master, what will happen to my user databases after restoring master, do i have to restore all db's from latest backups?

Thanks


texts: How to select top 10 records from each category

texts:
I have a table with fields

Hour,
PathId,
Duration,
Event,
CellId,
Channel

I have 50+ CellId. Each CellId has four PathId (i.e. 0, 1, 2, 3). Each PathId has many Events and Durations. Now I want to display the top 10 records (each PathId) for each CellId.

Sample Table

Sample Output


texts: Read show create table in mysql stored procedure

texts:
How can I read the output of following query and assign to a stored procedure's variable?

SHOW CREATE TABLE users;

Or, is there is an information schema from where I can draw the full table structure my querying with a table name?


texts: Bad bind variable in audit trail trigger in Oracle 11g R2

texts:
I have been trying to construct audit trail triggers and procedures in oracle and I am facing a problem with bind variables. Following is the code

create or replace trigger trg_audit_trail
for insert or update or delete
on tbl_mst_books
referencing NEW AS New OLD AS Old
for each row
declare
  colName VARCHAR2(50);
  cursor audit_cur is
   select column_name from all_tab_columns where table_name = 'tbl_mst_books';
begin
  for rec_audit in audit_cur loop
   colName := rec_audit.column_name;
   if inserting then
    if (inserting(colName)) then
     prc_audit_trail('tbl_mst_books',colName,:new.colName);    /*stored procedure call
              to insert audit trail data from multiple triggers to audit trail table */
    end if;
   end if;
 end loop;
end trg_audit_trail;

The stored procedure prc_audit_trail is now inserting the values passed as parameters into audit trail tables.
The problem I am having is with the bind variable :new. I am getting an error of bad bind variable. Apparently I am not being able to do :new.colName but instead of colName if I directly write the name of the column(eg: BOOK_NAME or BOOK_AUTHOR) it's not giving any error. Since I have to fetch the column name of the affected column I am fetching the column name to a variable and trying to do :new.colName. How can I resolve the issue?? Please help me. Also can I get column names of the affected columns(during insert, update and delete) in some other way?
Why am i getting the bad bind variable error for :new.colName? How can I resolve it??

P.S. I have just shown the portion for IF INSERTING but I am following the same process for IF UPDATING in the same trigger where I am getting the same error also for :old.colName

Thank you.


texts: Performance and correctness of selecting only most recent entries across joined tables

texts:
I have 2 tables: things and purchases. I am trying to select only the most recent purchase record for each thing.

CREATE TABLE `things` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `product_image` varchar(1024) DEFAULT NULL,
  `barcode` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `unique_barcode` (`barcode`)
) ENGINE=InnoDB AUTO_INCREMENT=643 DEFAULT CHARSET=utf8

CREATE TABLE `purchases` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `thing_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `status` enum('IMMEDIATELY','SOON','LATER','INACTIVE') DEFAULT 'IMMEDIATELY',
  `estimated_number_of_days` int(4) NOT NULL DEFAULT '7',
  `created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `purchase_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `predicted_replace_days` int(4) NOT NULL DEFAULT '7',
  PRIMARY KEY (`id`),
  KEY `foreign_key_things` (`thing_id`),
  KEY `foreign_key_users` (`user_id`),
  CONSTRAINT `foreign_key_things` FOREIGN KEY (`thing_id`) REFERENCES `things` (`id`),
  CONSTRAINT `foreign_key_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8

The following query (based on this answer) appears to work. My questions are:

Is this actually doing what I want it to?
How can I optimize the performance of this query (indexes, etc.)?
Is there a more efficient way to do this in MySQL?

SQL:

SELECT purchases.id AS purchase_id, thing_id, purchase_date, name, predicted_replace_days, product_image, barcode
FROM (
    SELECT id, MAX(purchase_date)
    FROM purchases
    GROUP BY id
    ORDER BY purchase_date DESC
) AS sub
JOIN purchases ON purchases.id = sub.id
INNER JOIN things on thing_id = things.id
WHERE user_id = 1
GROUP BY thing_id;

Thanks!

UPDATE: I think I was overcomplicating it. Here is what looks like a simpler and more correct query:

SELECT purchases.id AS purchase_id, MAX(purchase_date) AS purchase_date, predicted_replace_days, name
FROM purchases
INNER JOIN things ON purchases.thing_id = things.id
GROUP BY thing_id


texts: Postgres 9.6 - Taking differenctial backup

texts:
I have a few CentOS boxes running Postgres 9.6. I am planning a central server which can make local copies of all DBs on the other servers. These servers are accessible via SSH on Internet.

What is the best approach to take differential backup? I am considering below points :

Central Backup Server may not be up 24/7.
Internet Link may fail.
Bandwidth Consumption on the DB server.

And I am considering the below approaches :

Directly Rsync the Postgres data directory.
Some kind of log archive, to be read by Postgres.
Best of open source solution such as the pitrtool or pgpool2.

Which is the most efficient or even possible from above? Any other recommendation?


texts: SQL optional where clauses without exponentially large number of only slightly different queries

texts:
I'm working with postgres in python and I want to write a clear query that optionally allows WHERE clause subsetting, but doesn't require  me writing an exponential explosion of whether the subsetting where clauses exist or not.  Specifically, I have the following:

query = """
select *  -- in reality, something more complicated
from myTable t
WHERE
  t.first_id in :args1 AND
  t.second_id in :args2
"""

I would like to duplicate as little as possible, and for this query to return sensibly:

When args1 is null, and args2 is null
When args1 is null, and args2 has 1 argument
When args1 is null, and args2 has n arguments
When args1 has 1 argument, and args2 is null
When args1 has 1 argument, and args2 has 1 argument
When args1 has 1 argument, and args2 has n arguments
When args1 has n arguments, and args2 is null
When args1 has n arguments, and args2 has 1 argument
When args1 has n arguments, and args2 has n arguments

Where args1 can be:

args1 = []
args1 = ["a"]
args1 = ["a", "b"]

...for example, and similar for args2.

As you can see above,

   (1) is effectively no WHERE clause
   (2) is effectively remove the first argument and the AND
   (2) Would also likely want an equality operator = 'a', rather than an in ('a')

...etc.

For 2 clauses and 3 options, we have 3^2 = 9 different queries to write.  If we add another 1-2 where clauses, this quickly
becomes overwhelming and duplicates a lot of code for what is effectively the same problem. While I can write each of these queries out and switch case on them, I suspect this is a syntax SQL can do for me, and that has possibly been answered a thousand times on stackoverflow but I can't seem to find.

I'm using sqlalchemy in python if that changes anything.


texts: Are all Oracle DB Control features available in Enterprise Manager Express or Cloud Control?

texts:
I'm having a hard time figuring out if the new replacements for DB Control (Express and Cloud Control) support all of the features that were available in DB Control. I know Express is very limited, so I'm mostly unclear about what Cloud Control supports. More specifically I'm hoping to find the following features, available in DB Control:

Performance -> Top Activity/Search sessions/Search SQL
(I think i've seen this in Express already, not sure...)
Availability -> Creating, Restoring and managing backups
Server -> Manage tablespaces, datafiles, roles/profiles and advanced user settings
Data movement -> Export and Import data, Clone and Duplicate database

Are these interfaces missing, and is the intent to do this commandline based from now on? Or are they available in Cloud Control or some other extension?


texts: Is DBCC SHOWFILESTATS an accurate way to see unused space?

texts:
We have a few tables each with near 100 million rows of data.  A co-worker has these tables setup with partitions based on a date field.

Our disk space is starting to run low and knowing that partitions have their own file group, I wanted to see if some of the partitions were larger than they needed to be and were chewing up disk.

I ran the poorly documented command:

dbcc showfilestats

and here is a snippet of the output:

TotalExtents   UsedExtents   Name         FileName
363200         200701        LogJun2013   F:\SQL\DATA\logs_phyJun2013.ndf
812800         432127        LogJul2013   F:\SQL\DATA\logs_phyJul2013.ndf
718400         401500        LogAug2013   F:\SQL\DATA\logs_phyAug2013.ndf
1016983        555565        LogSep2013   F:\SQL\DATA\logs_phySep2013.ndf

If I am understanding this correctly (IANADBA), there is a lot of unused space in these files given the high number of unused extents.  In addition, we only collect new data so we will never have a need to grow these old partitions from 2013.

Is my understanding of this correct?  If so, how can I go about freeing up the space from these files?


texts: What's the best way to order a list of tables based on their foreign key constraints

texts:
I'm working on an etl task that takes tables from one schema (with no constraints) and inserts them into the same tables (plus constraints).

I don't want to type out a bazillion insert statements, so I wrote some dynamic sql that looks like this:

declare @table varchar(255),
@stgtable varchar(255),
@columns varchar(max),
@sql nvarchar(max)

declare c cursor for

select table_name from INFORMATION_SCHEMA.tables

open c

fetch from c into @table

while @@FETCH_STATUS = 0
begin

set @stgtable = replace(right(@table, len(@table) - 1), 'LZSTG','STG')

--print @stgtable

select @columns = stuff( (
    select ', ' + Column_Name from information_schema.columns
    where Table_Name = @table
    for xml path ('')),1,2,'')
--print @columns

begin try
set @sql = 'truncate table ' + @stgtable
exec sp_executesql @sql
set @sql = 'insert into ' +  @stgtable  + ' (' + @columns + ') select ' + @columns + ' from ' + @table

print @sql
exec sp_executesql @sql

set @sql = 'select * from ' + @stgtable
exec sp_executesql @sql
end try

begin catch print 'womp womp ' + @stgtable end catch

fetch next from c into @table
end

close c
deallocate c

Problem: If you go through information_schema.tables in its default order, the insert will often fail because of a foreign key violation.

If I want this solution to work, I have to insert into parent tables first and only then insert into child tables.

I've got this, but it is hardly an ironclad solution:

with fks as
(SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table_name],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id)

select t.table_name, fks.* from
information_schema.tables t
left join fks on t.table_name = fks.table_name
order by referenced_table

Question What is the best way to order information_schema so that the parent tables are on top and the child tables are on the bottom?


texts: Foreign Key Error with Transactional Replication from Backup

texts:
I'm getting a missing foreign key error when I begin transactional replication.

Steps taken:

Created a pull publication (live server)
Created a copy only full backup
Restored backup on reporting server
Created a pull subscription on from backup (live server)
Created a pull subscription on subscriber (reporting server)

I'm getting errors soon after looking through replication monitor:

  The INSERT statement conflicted with the FOREIGN KEY constraint.

The issue is it's trying to insert a record into a table for which there's no corresponding record (although the record does exist on the live server).

Unless I've missed a step I'm not sure why this is happening. I had suspected it may be a case of distribution records getting cleaned up  too early, so I disabled the SQL Agent job, but it's still happening.

Any help would be greatly appreciated.


texts: Can SQL Server 2016 extract node names from JSON?

texts:
I want to extract a complete list of the node names and their paths from any arbitrary, well-formed JSON document stored as nvarchar value in SQL Server 2016. Is there a reliable way to do this?

For Example, for a json value:

DECLARE @json_doc nvarchar(4000) = '{"Name1":"Value1", "Name2":"Value2"}'

Get this result upon querying @json_doc:

NODE_NAME
$.Name1
$.Name2


texts:
I can't find (using ctrl-r within psql and less ~/.psql_history) a particular query that I executed a couple months back. I also recently upgraded from 9.3 to 9.5 so I'm not sure if there is a limit on psql_history or if it was removed on the upgrade.

Is there a limit on the size of psql_history and, if so, how could I go about increasing it?

Thanks in advance!!


texts: Multilanguage category structure - standard vs. normalization?

texts:
I'm building a multilingual application and I need some advice on how to structure the table.

Basically I have a category table based on nested set. There are 4 languages and I plan to enlarge this up to 8 languages in the future.

Currently I plan a table like this;

[category]
id
lft
rgt
title_en
title_fr
title_de
title_es

If I go for the normalization way;

[category]
id
lft
rgt

[category_lang]
cid
lang
title

My main concerns are speed and size. Which way is more optimal?


texts:
Hi and thanks for reading,

I'm really new to oracle, I have an oracle database, and it needs to have a permanent log, not even the sys user should be able to modify it or disable it. I read all the docs I could find on google, and from here:

https://docs.oracle.com/cd/B28359_01/server.111/b28337/tdpsg_auditing.htm#CEGIDHAG

I learnt how I can prevent de sys user to modify the log by using AUDIT_TRAIL  OS. Using OS value makes oracle write the log in a os file, in which the sys user does not have access. But it is required to avoid the sys user to disable the log, to prevent frauds even from the sysadmin that has the password for sys or system user. (If there is another way to prevent frauds from the sys admin, it is also welcome)

I have oracle 11g and there is no budget to buy anything else.


texts:
I am trying to set a cron job for taking backup from my slave machine. So I need to stop the slave

I issued a command

mysqladmin --user=root --password=test_pass stop-slave

But it is throwing error:

  mysqladmin: connect to server at 'localhost' failed error: 'Access
  denied for user 'root'@'localhost' (using password: YES)'

Now I tried with the command

mysqladmin --user=root --password stop-slave

It prompts for password and I gave as test_pass and everything was fine.

Why so happened? What is the alternative?

NB: BTW my mysql version is mysql-5.0.95-5, it it make any sense.


texts:
We currently have a VM that is very underpowered and are proposing to move to an Azure VM with better specs.  The problem is, the Azure VM is much slower than the original VM even though it is of a higher spec.

The original server is a 2 core VM with 2GB of memory that is also a web server.  It's running Microsoft SQL Server Web Edition 2008 R2 and because this server is used for other things, we have had to limit the maximum server memory in SQL Server to 512MB.

The new server is a 4 core VM with 7GB of memory that is only a database server.  It's running Microsoft SQL Server Standard Edition 2008 R2 and we haven't limited the amount of memory that SQL Server can use.

This is one of two servers set up in a mirrored environment but the database that I am running tests on is not mirrored.  The other databases on this server aren't getting much traffic at the moment (in fact, Activity Monitor shows no activity on the other DBs whilst I was running these tests).

I do realise that an issue with Azure VMs is that the hard drives are a network resource so that would be the source of slow down but it is still slower even when there are 0 physical reads shown in the IO statistics.

I have followed the tuning advice on this page on the Azure VM including striping the disks (two disks per drive) and putting the log and data files on separate drives.

The only things that I haven't done are enable page compression, limit auto-grow on the database and move the SQL server error log and trace file directories to data disks.  I haven't done that on the older server either.

The old server has none of this tuning done and the log and data files are on the same drive which is not striped.

The database on the current server is 65 GB (45 data and 20 log) which was a bit too large to transfer to the new server so I'm testing on a smaller DB (6 data and 13.5 log)

The results on the old server are CPU time = 1311 ms,  elapsed time = 1057 ms. and on the new server are CPU time = 1281 ms,  elapsed time = 2525 ms.  That's just one run but the results are representative of what I am normally seeing.

The new server seems to constantly have an elapsed time significantly longer than the CPU time.  Is that an issue and is there something that I can do to track down what is causing it?

What other steps can I take to find out why this server is going so slowly when it looks like it should be faster than the old server?


texts: Preventing sysquerymetrics from being created ASE 15.7

texts: Can I prevent Sybase ASE 15.7 from creating the sysquerymetrics view for a database upon creation?  And also, if the view is dropped how can it be added later?


texts: how to synchronize Oracle data with SQL Server

texts:
I have an Oracle 11g database in one machine with unique a IP, and a SQL Server 2008 database in another machine.

A table (A) in Oracle has some fields (not all) which are the same as table (B) in SQL Server.

Now I want to synchronize the fields' values in A with the fields' values in B. That is (maybe it is wordy!), when the value changes in A, so it is automatically updated in B without any manual operation in SQL Server!

How to get this done?


texts: Reset search_path to the global, cluster default

texts:
Say I permanently alter my database's search_path like this:

ALTER DATABASE my_db SET search_path TO "$user",public,other_schema;

How can I reset it back to whatever the default that a pristine, unaltered database on this cluster would have?

This is usually "$user",public, but that can be changed from the configuration file. Having a way to read the value of parameters that come from the configuration file would work (so I could build a dynamic query at worst), but I haven't had any luck finding anything of the sort.


texts: Oracle 12c, exclude rows wher two columns are both null

texts:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit

I am trying to select rows with a non-null value in one of two columns. Data is being selected from a view (MY_VIEW) with both columns defined as NUMBER types, like so:

COLUMN_NAME, DATA_TYPE, NULLABLE, DATA_DEFAULT, COLUMN_ID, COMMENTS, INSERTABLE, UPDATABLE, DELETABLE:
...
num_1   NUMBER  Yes     8       NO  NO  NO
num_2   NUMBER  Yes     9       NO  NO  NO

Query 1) works

select * from MY_VIEW
  where num_1 is null and num_2 is null; --OK, results below

num_1    num_2
(null)  (null)
(null)  (null)
...

Query 2) works

select to_number(num_1) as n1, to_number(num_2) as n2 from MY_VIEW; -- OK, results below

n1      n2
(null)  1
(null)  (null)
(null)  0.32
(null)  (null)
(null)  15
(null)  (null)
7.5     (null)
24      (null)
(null)  (null)
...

Query 3) FAILS with ORA-01722: invalid number

select * from MY_VIEW
  where num_1 is not null and num_2 is not null;

Query 4) Trying to exclude rows where both values are null, the following query also FAILS with ORA-01722: invalid number

select * from MY_VIEW
  where not (num_1 is null and num_2 is null);

The failed queries above indicate that there may be some invalid characters in numeric data, but attempts to convert to_char() and/or use regexp to find invalid numeric characters also fail with invalid number. Any suggestions on why queries 3 and 4 failed or tips on how to track down possible bad data would be very helpful. Thanks!

SOLVED Two bad rows of non-numeric data had been loaded into the view that loaded the view that loaded the view.


texts: Snapshot for transactional replication taking too long for large database

texts:
I want to establish a transaction replication of a large database (200GB). But when I create the publisher and the snapshot agent starts, it takes very very long time and it sticks on the following message

  The process is running and is waiting for a
  response from the server

but the problem is when I see the size of the snapshot folder, I can see it is growing up! But too slowly.

Do you have any Idea about this? or any different way of doing the replication? how can I find the more detailed warning or errors messages?

P.S. In my database there are two tables for Images that they make 98% of the size of my database. when I don't include them in my publication, everything goes fine. but when I Include them, I will face the problem.


texts:
What is the difference between:

GRANT DELETE ON TABLE1 TO ROLE1

and

GRANT DELETE TO ROLE1

One of our DBAs executed the second version by mistake.  I want to be certain I understand all the ramifications.


texts:
I have Oracle 11g installed on my system and want to install 12c now. Read articles (Oracle docs and general) which suggest that I can do so in different homes. But, when I try to install 12c (12.1.0.1 release 1), it does not allow me to do so as it says "oracle _home (in environment variables) already defined and does not match the path specified (during installation)".
A search on this portal gives results that do not answer my query.

What path should I specify for software location (Oracle home) during installation? (It auto reflects the server path of 11g installed)
Do I need to manually add this specified path to Oracle_Home path (in addition to the already existing oracle home for 11g) in environment variables for the installation to auto-accept it and allow further installation?

How do I get around the installation?


texts: Updating records from the past with MySQL query

texts:
I've built an application that fetches reports from an external source. It downloads report data once a day. Each report covers the time from now to 30 days ago.

E.g. let's say it's 1 May and report is downloaded for the last 30 days and we end up with 30 rows in the table (let's have each day be represented by one row). Then on 2 May we fetch the report and we end up with 31 rows in the table - 1 row from the report fetched on 1 May, and 30 rows from 2 May. 29 older rows that overlap are discarded. The rows from the latest report replace them. Then on 3 May we end up with 32 rows in the table, 4 May, 33 rows, etc.

Now the query that does the clean-up of the older data in my application looks like this (records with the same date that have a lower ID are deleted):

DELETE FROM $tableName WHERE id NOT IN (SELECT MAX(id) FROM (SELECT * FROM $tableName) AS sth GROUP BY $date

The problem is that this query is very inefficient. As the amount of data in the table grows, it's starting to kill the database server as each the select statement is applied to the whole dataset. Aslo NOT IN and MAX become less and less efficient as number of rows grows.

The system has to update rows from the past as the external source normalizes data backwords in time so records from the past will change occasionally, and I'm always interested in the last 30 days.

I'd be grateful for advice on how I can optimize this part of the application, how I can overcome the performance problem of this SELECT and DELETE statement.


texts: Organizing databases and tables in phpMyAdmin

texts:
I have 2 servers, on one server the access6_identical sql file is on the second directory under identical as can be seen here:

On the other server, it is on the parent directory as can be seen here:

How do I set it so that it is in the secondary directory as shown in the first example?
Does this make any difference when linked to the SQL files, from e.g. Wordpress config?
Why is it then when I need to import the SQL file, I must create an empty table with a meaningless value, then I'm able to import the SQL file onto the server?


texts:
In PostgreSQL we use generate_series a lot to do quick test cases.

For example here I generate a quick test for that question using

CREATE TABLE foo
AS
  SELECT x::int AS a, (x%2*x)::int AS b
  FROM generate_series(1,3e6) AS gs(x);

What's the easy way to generate sample data in MariaDB? Is there anything like generate_series or how do experienced MariaDB users generate test cases?


texts: MySQL Insert into table, where name = id, if not exist, then insert name and use that ID

texts:
I am producing a MySQL database that will store results for a particular sport. I have a table containing people, (PERSONID, FIRSTNAME, LASTNAME), another table containing animals names (ANIMALID, ANIMALNAME).

Within the results table, I have the position, personid, animalid, and time. I want to create a query that will insert into the results table a large number of 'results'.
I have managed to create a simple query to look up a persons ID based on their firstname and last name, however I want to take this further and say 'If this person doesn't exist based on their firstname and lastname, insert this into the persons table, and then use their ID for the results'. The same will be the case for the animals.

Also, as a side note, would it be possible to create a sort of procedure that will run through a list of peoples names and animal, and insert it into the results table? (preferable with the above 'if not exist, then insert and use id' scheme)

Thanks,

Graham


texts:
131212 10:35:18  InnoDB: error: space object of table 'main/wp_m_membership_levels',
InnoDB: space id 119 did not exist in memory. Retrying an open.
131212 10:35:18  InnoDB: error: space object of table 'main/wp_m_membership_rules',
InnoDB: space id 122 did not exist in memory. Retrying an open.
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 5.
InnoDB: You may have to recover from a backup.
131212 10:35:18  InnoDB: Page dump in ascii and hex (16384 bytes):

I placed InnoDB into recovery mode 4 but had no luck fixing the errors.

Is there any other tricks I can/could try or do I need to restore from a backup?


texts: Reorganize full text catalog is offline or online?

texts:
I need to schedule a Full Text Search Maintenance and I was reading this link: http://msdn.microsoft.com/en-us/library/ms176095.aspx. Reading this documentation I am supposing that I need to rebuild/reorganize the unique index used by the Full Text Search and then reorganize the Full Text Catalog. I am only concerning here, if this process of reorganize the Full Text Catalog is done online or offline? When the catalog reorganize is ocurring the users will still be able to query the full text data?

I appreciate any help here!

Regards,
Marcos Freccia


texts:
I believe that innodb has a single read thread. However, with MariaDB you can set multiple read threads. I'm unsure what the recommended value would be.

Questions

Does anyone have any experience with MariaDB and performance tuning?
Can you think of any features within MariaDB that XtraDB uses that MySQL does not?
Are there any similar settings that can I can use to fully exploit XtraDB and Maria's enhanced functionality?


texts: How to implement optional query parameters

texts:
I have a relatively big (10^7 rows and growing) table and I want to query some rows from it.
Since table is this big - I want to use every perfomance boost that I can.

My query has optional parameter (let's call it agent_id) - it may be NULL or may have some numeric value.
The column agent_id in table is NOT NULL.

I have thought of two ways to create such a query, here are simplified examples:

Query 1:

select c.agent_id,
    c.date_time
from calls c
where 1 = 1
    and c.agent_id = nvl(agent_id$i, c.agent_id)

Query 2:

select c.agent_id,
    c.date_time
from calls c
where 1 = 1
    and (
        user_id$i is null
        or
        c.agent_id = user_id$i
    )

Is there any perfomance advantage for either one?

I guessed that second one will be slightly faster, because it can evaluate only half of expression, excluding the need to evaluate other half - but it's only a guess, and I want to hear what experts have to say about this issue.


texts: Sharing certificates encryped by password between DBs and instances

texts:
Question:

I am trying to create a single (self signed) certificate and deploy across many databases and instances.

I create and backup the cert with:

USE MASTER
CREATE CERTIFICATE DavesCert ENCRYPTION BY PASSWORD ='S3creT!' WITH SUBJECT = 'The master cert'

BACKUP CERTIFICATE DavesCert TO FILE = 'd:\DavesCert.cer'
WITH PRIVATE KEY ( DECRYPTION BY PASSWORD = 'S3creT!' ,
FILE = 'd:\DavesCert.pvk' ,
ENCRYPTION BY PASSWORD = 'S3creT!' );

I restore with

USE FOO
GO

CREATE CERTIFICATE ERecruitStatsGatheringCert
FROM FILE = 'd:\DavesCert.cer'
WITH PRIVATE KEY (FILE = 'd:\DavesCert.pvk',
DECRYPTION BY PASSWORD = 'S3creT!')

and get the following error: "Please create a master key in the database or open the master key in the session before performing this operation."

I don't want to create a database master key. I'm happy to decrypt the cert by password as needed.

Background:
SaaS application.  Many to Many relationship between DB's and instances.

I need each DB to be able to query it's own stats by executing a stored procedure that wraps up some calls to DMV's to return the stats.

App runs under a low-privileged account. DMV's require VIEW SERVER STATE permission, therefore, I'm implementing signing of the stored procedures using certs.

Basic way of setting this up is to:

Create a cert in the user DB.
Backup/restore that cert to master.
Create login in master, assigned that permissions, etc.
Add Certificate to stored procedure.

I have test code for the above and it works well, however the model does not scale very well for deployment across multiple instances/dbs.

Therefore, I think I want to use the same cert across all DB's/instances.

Happy for other suggestions/approaches.

--Originally posted to Stack Exchange then moved on suggestion from another use


texts: Getting n slip number based on the number of times in a month a slip has been created


texts: Can I use ASM 12c version with database 11g? This link and MOS Doc ID 337737.1 says I cannot use 12c version of ASM with a lower database version. Am I right? Or is it otherwise?


texts: How can I add another .ibd file to my existing table when the memory space is full?

texts:
I am working on an enterprise solution on MariaDB. The biggest challenge we are facing is to find a solution if our tablespace is full (.ibd file) and how can we add another .ibd file which will work like a joint memory space.

Considering we are using file_per_tablespace and MariaDB does not allow any DDM/DDL queries relating to tablespaces and datafiles.


texts: What is the best way to manage interdependent indexes

texts:
So I have a table of items as so

|firstIndex|subIndex|relatedIndexes|
-----------------------------------
|     1    |   1    |  2 ,6 , 8    |
-----------------------------------
|     1    |   2    |  2 ,6 , 8    |
-----------------------------------
|     1    |   3    |  2 ,6 , 8    |
-----------------------------------
|     2    |   1    |  1, 10 , 12  |
-----------------------------------
|     3    |   1    |  20, 50, 77  |
-----------------------------------
|     4    |   1    |  ...         |
-----------------------------------

So both firstIndex an subIndex are the primary keys (they cannot be two times the same combination, hope I'm clear :) ), subIndex is the index representing the number of the variation of firstIndex and finally relatedIndexes is the array of  firstIndex related to other firstIndex. As you can see in the sample firstIndex 1 is related to firstIndex 2 and 2 to 1

My question is how can I represent, store and manage this model the easiest and quickiest way in MYSQL?


texts: Postgres 9.1.6 Error index contains unexpected zero page at block 0

texts:
I have setup streaming replication on Postgres 9.1.6 running on a debian server and it's going on fine.

When I try to run a query on the replica DB I get the error below:

ERROR:  index "tbl_cust_id_idx" contains unexpected zero page at block 0
HINT:  Please REINDEX it.

What might be causing this error?

The question is also posted in https://stackoverflow.com/questions/17865135/postgres-9-1-6-error-index-contains-unexpected-zero-page-at-block-0


texts:
I need to restore a replica set. Unfortunately my lack of experience as administrator (I'm not) let me with the only solution of restore the replica using the data of the primary db (the only replica element still alive) as a "seed". At the moment I'm copying the files of the primary to secondary PC using rsync (there are 600G to copy). Then I have three questions regarding the restore:

1 - Once the files are copied should I use this data as the dbpath of the secondary?

2- Is the "local" db of primary copied? This may be a silly question but in https://docs.mongodb.com/manual/tutorial/resync-replica-set-member/#replica-set-resync-by-copying the say to be sure that this db is copied along with the data files, and I'm not sure about it.

3- How long it will take to "synchronize" with primary once I start the mongod instance in the secondary PC?


texts: Query of sys.login_token doesn't include particular windows group while connect within workgroup

texts:
I had two SQL Server Logins which correspond to two windows groups:

MachineName\MyAppAmdin
MachineName\MyAppUser

And I use

select principal_id, name, [type], usage
from sys.login_token
where principal_id > 0

to check if the current user is a member of any of these windows groups.

When the machine is under domain it will list

+--------------+---------------------------------+---------------+---------------+
| principal_id |              name               |     type      |     usage     |
+--------------+---------------------------------+---------------+---------------+
|            2 | public                          | SERVER ROLE   | GRANT OR DENY |
|            3 | sysadmin                        | SERVER ROLE   | GRANT OR DENY |
|          260 | MachineName\SQL Server Services | WINDOWS GROUP | GRANT OR DENY |
|          309 | MachineName\MyAppUser           | WINDOWS GROUP | GRANT OR DENY |
|          325 | MachineName\MyAppAmdin          | WINDOWS GROUP | GRANT OR DENY |
+--------------+---------------------------------+---------------+---------------+

as expected.

However, when I try another the machine which is under workgroup it only lists

+--------------+---------------------------------+---------------+---------------+
| principal_id |              name               |     type      |     usage     |
+--------------+---------------------------------+---------------+---------------+
|            2 | public                          | SERVER ROLE   | GRANT OR DENY |
|            3 | sysadmin                        | SERVER ROLE   | GRANT OR DENY |
|          260 | MachineName\SQL Server Services | WINDOWS GROUP | GRANT OR DENY |
+--------------+---------------------------------+---------------+---------------+

Even the account is actually a member of MyAppUser or MyAppAdmin.

Well, perhaps there are other factors I didn't notice but so far workgroup vs domain is the only difference I knew.

Is it supposed to work this way or I got something wrong?


texts:
I have two datetime columns in SQL Server 2005 that I need to query without the time portion of the datetime. Currently, my query looks something resembling this (just an example):

WITH Dates AS (
        SELECT [Date] = @StartDate
        UNION ALL SELECT [Date] = DATEADD(DAY, 1, [Date])
        FROM Dates WHERE [Date] < @EndDate
)
SELECT DISTINCT ID
FROM table t
CROSS APPLY DATES d
WHERE d.[Date] BETWEEN CONVERT(DATETIME, CONVERT(VARCHAR, t.StartDate, 103)) AND CONVERT(DATETIME, CONVERT(VARCHAR, t.EndDate, 103))

Now this results in a full Clustered Index Scan (surprise, surprise). I am trying to think of ways of making this faster (the actual query takes 3 mins :O). I have thought of doing the following but haven't tried any yet as I ran out of time earlier:

Use a computed column with just the date part
Index said computed column (Not sure if this is even possible?)
Use an Indexed view (Again, not sure if this is possible, will work?)

Easiest way would be to update the column and remove all time information, but I cannot do this :(

Any ideas?

Update

Thanks for all of the answers so far. I think the point of the question was missed because I was slightly unclear on what I wanted. My bad. I was only aiming to optimise the date conversion part of the query as the amount of data i'm dealing with is tiny in reality ( < 500,000 after the cross apply with a year long date range). Sorry for the confusion on this.

For those optimising the rest of the query for me, I can see what people are saying by using < > but consider the following:

The parameters that are passed in is a date range (eg. 1st to the end of this month)
The start date in the table can appear before or during the parameters date range (eg. only the end date is in the date range)
The end date in the table can appear during or after the parameters date range (eg. only the start date is in the date range)
Lastly, the start and end date in the table is in the parameters date range.

Personally, given the above, I could never get a < > solution to work. The only way I could get it to work properly and not miss anything is by using a CTE and saying where d.[Date] BETWEEN t.StartDate AND t.EndDate.

I hope this clears things up. Thanks again.


texts:
I am trying to create a Trigger to reduce the quantity of stock from my Products table when an order is received in my Orders table.

So far I have come up with the code on this DB Fiddle, which includes my query attempt. It seems to run without error but it does not reduce the Stocklevel.

I have read the standard documentation on Triggers but can't find a solution to this. Any ideas?


texts: Exporting data from Microsoft Access table file subsets to plain text

texts:
I have a problem in exporting data from Microsoft Access. I have an Access file made of several rows and columns, like this:

Every row contains a subset of data, tha could be observed by clicking on the "+" button:

I would like to export all these data (main table & subsets), but there's a problem.
When I click on External Data -> Export -> Text file, Ms Access exports only the main table data (1st Figure), and completely omits the data of the subsets (2nd Figure).

How could I export all these data (main table & subsets)?

Thanks


texts: Help with SQL Express and appending data to third party platform

texts:
New to DB Admin and kind of learning as I’m going to get to where I need to be.

Basically, I have an account on a third party data software and my organization purchased additional data (from another vendor) that I would like to append to this. The two platforms identify records by the same unique ID#

I have a 2.6GB .tab file (2.6 million rows) that have around 70 variables per row. Normally, I would just open in Excel, filter, and match using the third party’s tag management system. But obviously this .tab file is too large.

I’m trying to use SQL Express to get the data in a usable form, but having trouble populating the data table. Am I even going about this in the right way?

*EDIT** I should add: I can get the full .tab file into the Excel Power Pivot, but not really useful considering I need to figure out a way to tag and upload

I have the .tab file split up into smaller chunks (500K rows each), and can open and manipulate these, but I’d like to keep the database consistent so I can cross reference the unique ID numbers later with some of the data I don’t necessarily need to upload and append into the third party platform.

Any guidance would be extremely appreciated.


texts:
I have a specific situation and I will try to explain as much as I can.

L1-Master DB1 (Slave for L2-Master DB1)

L1-Slave DB2 (Slave for L1-Master DB1)

L2-Master DB1 (Slave for L1-Master DB1)

L2-Slave DB2 (Slave for L2-Master DB1)

So, 4 DB servers, master-slave and master-master replication in place.
What bothers me is this:
I should restore one database which is around 500 Gb in size and it should be restored on L1-MasterDB1.
What do you think is the best approach on restoring this amount of data, since the production is already under heavy load? How much of an impact will this be on those 4 servers since replication is in place?


texts: Service Broker error when ending a conversation

texts:
I use Service Broker infrastructure to develop data push approach between multiple servers ( many sources and one destination ).
I stick Remus Rusanu recommendations about how reuse or end conversations, for instance this one. I use an user table to store conversation handles and conversation timers to end their.
But sometimes ( not always ) I get errors when ending the conversations:

This message could not be delivered because the conversation endpoint has already been closed
The remote conversation endpoint is either in a state where no more messages can be exchanged, or it has been dropped.

Let's take a look two different conversation between the same services. In test purposes conversation timer is 60 seconds.
This is trace for first conversation ( without errors ):

I don't see any problems here.
But this is trace for the second conversation. This conversation has more messages, so the trace is longer to read, sorry about that.
Sender:

Target:

Conversation ending timeline in the second case:

So, my questions:

Why does the error occur? Sender didn't get confirming that target has acknowledged... but why sender tried to send another one message and what it tried to acknowledge?
This is not an isolated case - I get these error for some conversations in our environment.
Does this affect something or not? I mean, errors occur when I finish conversations and then I see proper statuses in sys.conversation_endpoints. More precisely there is no record on sender side and there is an record with "closed" status on target side after I get the error


texts:
Following snippet works within SSIS 2012 --- derive colum block.

TOKEN(REPLACE(@[User::FileName],".csv",""),"\\",TOKENCOUNT(REPLACE(@[User::FileName],".csv",""),"\\"))

But it does not work within SSIS 2005. TOKENCOUNT was not recongnized according to the error dialog. Any suggestions which alternative could be used?


texts:
Took me more time than I'd like to admit to figure out how count the number of n-tuples occurrences via Oracle SQL.

Lets say table TABLE :

| ID         | Order number| Value        |
|:-----------|------------:|:------------:|
| 1          |        10   |     A
| 2          |        10   |     B
| 3          |        10   |     C
| 4          |        10   |     D
| 5          |        15   |     A
| 6          |        15   |     B
| 7          |        15   |     C
| 8          |        15   |     D
| 9          |        20   |     X
| 10         |        20   |     Y
| 11         |        20   |     Z
| 12         |        30   |     M
| 13         |        30   |     P
| 14         |        40   |     X
| 15         |        40   |     Y
| 16         |        40   |     Z

I would like to count the number of order that share the same "Value" n-tuple occurrences. Output format is irrelevant as long as I can get the data, something like :

(A,B,C,D) = *2   // [Since Both order 10 and 20 share the same values]

(X,Y,Z) = *2

(M,P) = 1

n-tuple order is irrelevant (A,B,C,D) or (D,A,C,B) means the same.

Any tip or hint greatly appreciated.


texts: Exploring and optimizing SQL performance of your system (PostgreSQL and Hibernate)?

texts:
Imagine you have inherited some project with extensive usage of SQL queries.

Let us assume that Hibernate is used as ORM and PostgreSQL as database, but thats not the point.

A question is: could you suggest a plan to sequentially scan the system and optimize the SQL queries performance?

I suggest the following steps of that plan:

check which queries take the longest time to execute - how can we do it in PostgreSQL?
check which queries are executed most often - how can we do it in PostgreSQL?
find out which queries are not using the indexes - how can we do it in PostgreSQL?
find out if the connection pool is operating without any delays -  - how can we do it in PostgreSQL?
check the cache usage of Hibernate: are the queries from the first two points are cached? - how can we do it in Hibernate?
which queries tend to hit the database instead of hitting the cache - how can we do it in Hibernate?

Please suggest your own options.

May be there is some soft to recommend for this task?

May be there are some other database parameters to check, to log, to plot to monitor?


texts: Eliminate filter operator before columnstore index scan operator

texts:
I have a large fact table with millions of rows called MyLargeFactTable,
and its a clustered column store table.
There is a composite primary key constraint on it there as well
(customer_id,location_id,order_date columns).
I also have a temp table #my_keys_to_filter_MyLargeFactTable,
with the very same 3 columns,
and it contains few thousand UNIQUE combination of these 3 key values.
The following query gives me back the desired result set
...
FROM #my_keys_to_filter_MyLargeFactTable AS t
JOIN dbo.MyLargeFactTable AS m
ON m.customer_id = t.customer_id
AND m.location_id = t.location_id
AND m.order_date = t.order_date

but i notice that the Index Scan Operator on the fact table returns more rows than it should (about a million) and feed it into a Filter operator, which further reduce the result set to the desired few thousand rows.

Index Scan operator reads way to much rows (they quite wide rows) increasing IO, and significantly slows down the whole query.
Are my parameters not sargable?
How could I remove the Filter operator and somehow force the Index Scan operator to read only the few thousand rows?
Table definitions:
create table #my_keys_to_filter_MyLargeFactTable
(
customer_id varchar(96) not null,
location_id varchar(96) not null,
order_date date not null,
primary key clustered (customer_id,location_id,order_date)
)

create table MyLargeFactTable
(
customer_id varchar(96) not null,
location_id varchar(96) not null,
order_date date not null,
...
lot of wide decimal typed columns, and even large varchars
...
PRIMARY KEY NONCLUSTERED  (customer_id,location_id,order_date),
INDEX cci CLUSTERED COLUMNSTORE
)


texts: How do I 'hint' the cardinality of a recursive CTE?

texts:
I'm using the following recursive CTE as a minimal example, but in general, the optimizer has to use default 'guessed' cardinalities for recursive CTEs:

with recursive w(n) as ( select 1 union all select n+1 from w where n<5 ) select * from w;
/*
 n
---
 1
 2
 3
 4
 5
*/

explain analyze
with recursive w(n) as ( select 1 union all select n+1 from w where n<5 ) select * from w;
/*
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 CTE Scan on w  (cost=2.95..3.57 rows=31 width=4) (actual time=0.005..0.020 rows=5 loops=1)
   CTE w
     ->  Recursive Union  (cost=0.00..2.95 rows=31 width=4) (actual time=0.003..0.017 rows=5 loops=1)
           ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
           ->  WorkTable Scan on w w_1  (cost=0.00..0.23 rows=3 width=4) (actual time=0.002..0.002 rows=1 loops=5)
                 Filter: (n < 5)
                 Rows Removed by Filter: 0
*/

Note the rows=31 estimated and rows=5 actual cardinalities in the above plan. In some cases 100 seems to be used as an estimate, I'm not sure the exact logic behind the guesses.

In my real world problem, the poor cardinality estimate is preventing a fast 'nested loops' plan from being chosen. How can I 'hint' the optimizer cardinality for a recursive CTE to work around this?


texts:
I'd need to give (aside the instance owner and the administration server) the user rights as database administrator to a group of people already created on the AIX server. Is it possible? what will happen in case of someone will modify something to the db2 config for example?

Thanks


texts: How to fix the MySQL 5.6 System.Security.Principal.WindowsIdentity.get_User () not implemented exception in Linux?

texts:
I would like to know if programmatic intervention is necessary to fix the following C# Runtime exception when running MySql 5.6 Connector.NET on Ubuntu Linux 15.10 instead of Windows 7.

  Error connecting to SCDTYD database: System.NotImplementedException:
  The requested feature is not implemented.   at
  System.Security.Principal.WindowsIdentity.get_User () [0x00000] in
  :0    at
  MySql.Data.MySqlClient.MySqlPoolManager.GetKey
  (MySql.Data.MySqlClient.MySqlConnectionStringBuilder settings)
  [0x00000] in :0    at
  MySql.Data.MySqlClient.MySqlPoolManager.GetPool
  (MySql.Data.MySqlClient.MySqlConnectionStringBuilder settings)
  [0x00000] in :0    at
  MySql.Data.MySqlClient.MySqlConnection.Open () [0x00000] in :0

Here is the MySQL Connector.NET driver, MySqlPoolManager.cs source code found in this URL , https://github.com/pdonald/mysql-connector-net/blob/master/Source/MySql.Data/MySqlPoolManager.cs

  private static string GetKey(MySqlConnectionStringBuilder settings)
64     {
65       string key = "";
66       lock (settings)
67       {
68         key = settings.ConnectionString;
69       }
70 #if !CF && !RT
71       if (settings.IntegratedSecurity && !settings.ConnectionReset)
72       {
73         try
74         {
75           // Append SID to the connection string to generate a key
76           // With Integrated security different Windows users with the same
77           // connection string may be mapped to different MySQL accounts.
78           System.Security.Principal.WindowsIdentity id =
79             System.Security.Principal.WindowsIdentity.GetCurrent();
80

81           key += ";" + id.User;
82         }
83         catch (System.Security.SecurityException ex)
84         {
85           // Documentation for WindowsIdentity.GetCurrent() states
86           // SecurityException can be thrown. In this case the
87           // connection can only be pooled if reset is done.
88           throw new MySqlException(Resources.NoWindowsIdentity, ex);
89         }
90       }
91 #endif
92       return key;

If I programatically set settings.IntegratedSecurity to false in Ubuntu, this causes a runtime exception to be thrown and does not solve the Not Implemented exception problem with Ubuntu Linux 15.10. Furthermore, we are not allowed to set settings.ConnectionReset to true because there is a more elegant solution which we are looking for.  Thank you and have a great weekend.


texts:
I am stuck on a question I got in a coding challenge. Essentially I have 3 tables; ratings, reviewers, and books.

The ratings contain a list of book reviews from reviewers. I need to output a query that shows pairs of reviewers that reviewed the same book.

Please see this sqlfiddle to get the schema.

The desired output is:

"Name 1"     |  "Name 2"
Joe Martinez | John Smith

This is the desired output because in the ratings table reviewer_id 15201 and 44203 both reviewed book_id 101 and their names are Joe Martinez (reviewers.id = 15201) and John Smith (reviewers.id = 44203)

The only other requirements were to not have duplicate pairs. It didn't say anything regarding if there are multiple pairs for a single book. I would think if book_id 101 had 4 pairs then it would display 2 rows (4 names in total).


texts: Relation in 2NF with a transitive dependency?

texts:
Tomorrow I have my final exam of Database Design but I have a doubt about the Second Normal Form (2NF).

The definition says a relation is in 2NF if no non-prime attribute is partially dependent on a key.

My doubt is about if a relation could be in 2NF is if has attributes that are fully dependent on a key but transitively.

For example: R (A,B,C,D) with AB key and FD ( AB->C,C->D)

In this case the non-prime attribute C is fully dependent on the key so it meets the condition but in the case of the attribute D I don´t know if it meets the rule. D is dependent on C and due to C is fully dependent on the key,does it mean that D is fully dependent on the key being this relation in 2NF?


texts: SQL Server Extended event filters: can you use an IN predicate with a subquery?

texts:
I have an extended event that tracks and filters login events from specific users. The filter is something like this:
WHERE (([sqlserver].[nt_user]=N'fredJohnson') OR ([sqlserver].[nt_user]=N'sallySmith')))

What I would really like to do is use an IN, and have it filter off a much larger list of users. I have these usernames stored in a table. I want to go the subquery route because this EE will be pushed to many different databases with many different users. So for example, I would want it to do something like this, where the lname column is a list of a few hundred users from a table:
   WHERE (([sqlserver].[nt_user]=N'Select lname from @Tbl')))

But it doesn't works. Any ideas how to pull this off?
The full EE i'm trying to modify:
CREATE EVENT SESSION [TrackLogins] ON SERVER
ADD EVENT sqlserver.login(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.session_nt_username,sqlserver.username)
    WHERE (([sqlserver].[nt_user]=N'Select lname from @Tbl')))

WITH (STARTUP_STATE=ON)
GO


texts: User can't execute stored procedure despite in role with permission

texts:
I'm setting up a number of stored procedures and I would like to limit the users who can execute them to a specific server role. I've created my role and users like this:

USE [MyDB]
GO
CREATE ROLE [MyExecuters]
GO
CREATE USER [MyUser] FOR LOGIN [MyUser] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [MyExecuters] ADD MEMBER [MyUser]
GO

And I've granted permissions on the SP to the role like this:

USE [MyDB]
GO
GRANT EXECUTE ON [dbo].[MyProcedure] TO [MyExecuters] AS [dbo];
GO

However if MyUser tries to execute the SP, they get an access denied message. I tried explicitly granting the EXECUTE permission on the SP to MyUser (through the procedure properties in Management Studio) and now they can execute it fine. What's going on?


texts: MySQL INNODB SELECT query hanging on copying to tmp table

texts:
I have a query that is hanging (seemingly) randomly on a specific query.  Most of my website users do not have a problem when this query runs but a few users are having timeout issues on the website and when I check the processlist it gets stuck on copying to tmp table.

I believe the hard drive is just not keeping up, which is causing the hang on the copying to tmp table.

I don't believe I can optimize the query any more efficiently.  I was wondering if I could set up my server's second hard drive as the temp table storage so that way I'm spreading out some of the load being put on the server.

The query:
SELECT DISTINCT
    activity_seismo_info.ID,
    activity_seismo_info.CREATED_AT,
    activity_seismo_info.UPDATED_AT,
    ....
    activity_seismo_info.ASI_EXTRA_5,
    seismo.ID,
    ....
    seismo.SEISMO_NOTES

FROM `activity_seismo_info`
    JOIN activity_info ON (activity_seismo_info.ASI_ACTIVITY_ID=activity_info.ID)
    JOIN location ON (activity_info.ACTIVITY_LOCATION_ID=location.ID)
    LEFT JOIN seismo ON (activity_seismo_info.ASI_SEISMO_ID=seismo.ID)

WHERE location.ID='193'

ORDER BY activity_info.ACTIVITY_EVENT_TIME DESC LIMIT 30

I am on a P4 with 2GB RAM and two hard drives (the second hard drive is not used)

PHP5.2

MySQL 5.0.51a-3ubuntu5.4

symfony 1.2 application with Propel

UPDATE

thanks to @DTest's suggestion, I found my query was returning all the rows in the table before running a limit on the result.  But for future reference, is there a way to setup MySQL to use a second hard drive as the 'scratch' drive for temp tables?


texts:
I have a document like so:

{
    "_id" : ObjectId("52f29f91ddbd75540aba6dae"),
    "teams" : [
        {
            "name" : "Washington Bullets",
            "score" : 100,
            "home" : false,
            "won" : 1,
            "players" : [
                {
                    "player" : "Jeff Ruland",
                    "pts" : 19
                },
                {
                    "player" : "Cliff Robinson",
                    "pts" : 22
                },
                {
                    "player" : "Gus Williams",
                    "pts" : 21
                },
                {
                    "player" : "Jeff Malone",
                    "pts" : 18
                },
                {
                    "player" : "Charles Jones",
                    "pts" : 4
                },
                {
                    "player" : "Dan Roundfield",
                    "pts" : 2
                },
                {
                    "player" : "Perry Moss",
                    "pts" : 7
                },
                {
                    "player" : "Dudley Bradley",
                    "pts" : 0
                },
                {
                    "player" : "Darren Daye",
                    "pts" : 5
                },
                {
                    "player" : "Tom McMillen",
                    "pts" : 2
                },
                {
                    "player" : "Manute Bol",
                    "pts" : 0
                }
            ]
        },
        {
            "name" : "Atlanta Hawks",
            "score" : 91,
            "home" : true,
            "won" : 0,
            "players" : [
                {
                    "player" : "Dominique Wilkins",
                    "pts" : 32
                },
                {
                    "player" : "Tree Rollins",
                    "pts" : 9
                },
                {
                    "player" : "Cliff Levingston",
                    "pts" : 8
                },
                {
                    "player" : "Spud Webb",
                    "pts" : 12
                },
                {
                    "player" : "Kevin Willis",
                    "pts" : 13
                },
                {
                    "player" : "Randy Wittman",
                    "pts" : 6
                },
                {
                    "player" : "Scott Hastings",
                    "pts" : 4
                },
                {
                    "player" : "Lorenzo Charles",
                    "pts" : 4
                },
                {
                    "player" : "Sedric Toney",
                    "pts" : 0
                },
                {
                    "player" : "Ray Williams",
                    "pts" : 0
                },
                {
                    "player" : "Jon Koncak",
                    "pts" : 3
                }
            ]
        }
    ]
}

I want to shard by teams.name. I tried doing:

sh.shardCollection("finalNBA.list", {teams:{name:1}})

But I get the error that the shard key can only be 1 or hashed. Does anyone know how to to enable sharding using an embedded field?


texts: Can I find out which database is queried on the most?

texts:
We have a number of database which all have the same schema for each customer.
I was wondering if there was a query which could select count(*),db_name from somesysview group by db_name;

From a different angle, the same query broken down by user?


texts: Why are my Log Ship Jobs Blocking Each Other

texts:
I have 5 databases at a DR site all being log shipped to once a minute using the built in SQL Server log ship jobs. The server is running SQL Server 2017.

For the last week the log shipping keeps going down due to blocking, when I look at the blocked queries there are several queries all deleting from msdb.dbo.log_shipping_monitor_error_detail and they're all blocking each other...

If I add get_locks=1 to sp_whoisactive they all look very similar...

Session 104...

<Database name="msdb">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="log_shipping_monitor_error_detail" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="21" />
        <Lock resource_type="PAGE" page_type="*" index_name="nc3lsmonitor_error_detail" request_mode="U" request_status="GRANT" request_count="4" />
        <Lock resource_type="PAGE" page_type="*" index_name="nc3lsmonitor_error_detail" request_mode="U" request_status="WAIT" request_count="2" />
      </Locks>
    </Object>
  </Objects>
</Database>

Session 109...

<Database name="msdb">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="log_shipping_monitor_error_detail" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="21" />
        <Lock resource_type="PAGE" page_type="*" index_name="nc3lsmonitor_error_detail" request_mode="U" request_status="GRANT" request_count="3" />
        <Lock resource_type="PAGE" page_type="*" index_name="nc3lsmonitor_error_detail" request_mode="U" request_status="WAIT" request_count="2" />
      </Locks>
    </Object>
  </Objects>
</Database>

The queries never unblock and never get registered as deadlocks, at the minute when I see it happen I have to kill the connections then it goes back to working again for anything from a few minutes to a few days when it will eventually get stuck again. The table in question that its trying to delete from only has about a thousand rows in it.

At this point I'm running out of ideas.

Edit : Complete version details from @@Version are ....

  Microsoft SQL Server 2017 (RTM-CU15-GDR) (KB4505225) - 14.0.3192.2
  (X64)   Jun 15 2019 00:45:05   Copyright (C) 2017 Microsoft
  Corporation  Standard Edition (64-bit) on Windows Server 2016 Standard
  10.0  (Build 14393: ) (Hypervisor)


texts:
I'm using SQL Server Management Studio to generate scripts for all the objects in an existing database:

but the objects related to SQL Broker (messages, queues and services) are not generated.

I've fiddled with the advanced options but still can't get them out.
Is there a way to do it?


texts: SQL Server Mirroring disconnected for 1 of 5 mirroried DBs after initial setup completes

texts:
Environment: 2 Servers SQL 2008 R2 RTM Standard Edition, Windows Server 2008 R2 SP1 standard edition. Both servers using the same domain service account for SQL with sysadmin access and local Administrator group access on both servers.

4 databases are successfully mirrored without issue, all database owners are sa.

When we attempt to setup mirroring on the 5th database it successfully starts and then within a minute goes to a (Principal disconnected) state on Primary and stays at (Mirror, Syncronizing / Restoring) on the mirror. The Mirroring monitor just shows "Disconnected" from the beginning of the event for the fifth database.

I have run a SQL Trace while setting up mirroring and the only thing in them is "Attention" with the service account and host name, no other details.

The Windows Application logs on both Primary and Mirror server show SQL Server event ID 1438. We are not using a witness and have never attempted to set one up in this environment.

Any suggestions on what to check would be appreciated


texts:
Invoking the following SQL leads to a warning 'column "s" has type "unknown"' which I can understand. What I'm trying to accomplish is to conditionally create a temporary in preparation of running a SELECT over multiple schemas. Can I approach this in another way?

CREATE OR REPLACE FUNCTION table_exists(v_table text)
  RETURNS boolean AS
$BODY$
  DECLARE
    v_count int;
    v_sql text;
BEGIN
  v_sql =
    'SELECT ' ||
    '  count(1) ' ||
    'FROM ' ||
    '  information_schema.tables ' ||
    'WHERE ' ||
    E'  table_name=\'' || v_table || E'\'';

  EXECUTE v_sql INTO v_count;
  RETURN v_count>0;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
  COST 100;

DO $$DECLARE r record;
BEGIN
  FOR r IN SELECT distinct table_schema FROM information_schema.tables WHERE table_schema NOT IN('information_schema', 'pg_catalog', 'public') AND table_type='BASE TABLE'
  LOOP
  IF table_exists('combined_settings') THEN
    EXECUTE '
    INSERT INTO combined_settings
      SELECT '''|| r.table_schema ||''' AS s, prefix, id, value FROM '|| quote_ident(r.table_schema) ||'.settings WHERE id=''VERSION''
    ';

  ELSE
    EXECUTE '
    CREATE TEMP TABLE combined_settings AS
      SELECT '''|| r.table_schema ||''' AS s, prefix, id, value FROM '|| quote_ident(r.table_schema) ||'.settings WHERE id=''VERSION''
    ';
  END IF;

  END LOOP;
  EXECUTE '
  COPY (SELECT * FROM combined_settings) TO ''/tmp/out-' || current_database() || ''' CSV;
  DROP TABLE IF EXISTS combined_settings;
  ';
END$$;


texts: Allowing a user to view/edit only a specific table in a database with ~50 tables

texts:
The database I'm working with has about 50 tables in it.

A single user requires edit permissions on one of the tables, but should not have access to any other tables.

I know how to deny her the ability to edit any of the tables:
properties > permissions then denying her all the options.

Using this method, she can still view the tables.

Is there a way I can make it so she can only view the one table?


texts:
I'm trying to run a simple SELECT query in Toad (for MySQL 7.2.0.2922).

SELECT * FROM sometable

When I click execute (or press F9) a small timer shows up in the status bar (bottom right) and it seems like the query is running, but it doesn't finish.
No matter what query i try to run, on any table.

A few hours ago the same query executed nicely and gave me the desired results.

The table has around 400 rows and 11 columns.

Also, when executing this query with PHP (same user) the query is running fine, so that makes me think it has something to do with Toad.

EDIT:

Typing asdf instead of a valid query gives the same result.
Weird thing is, i can open tables and view its content