texts: MongoDB replica sets and data duplication

texts:
I am new to MongoDB and trying to understand replica sets. Specifically, I am trying to understand how MongoDB would handle the following situation:

A record is written to a table with a unique index on the primary Mongo member (Mongo 1).
Mongo #1 crashes before the new record is propagated to the secondary Mongo member (Mongo 2).
Mongo 2 and the arbiter elect Mongo 2 to be the primary.
The application attempts to create a duplicate record in the table. Because Mongo 2 is not aware of the record written to Mongo 1, the unique constraint is not enforced.
Mongo 1 becomes available again and replication resumes.

What happens next? Does Mongo 1 attempt to replicate the record to Mongo 2? Or Mongo 2 to Mongo 1? Or both simultaneously? How is the conflict resolved?


texts: MySQL subqueries that use range based on values of main queries don't use indices properly

texts:
I think I've isolated a problem that has been affecting many of my queries lately.
And would like some help to figure out a solution for this.

Ok so my findings are that a normal query that runs very fast using like a couple of rows can actually use indexes improperly when used in a subquery which is based on values from the main query.

Lets take an example:

DROP TEMPORARY TABLE IF EXISTS Person;
DROP TEMPORARY TABLE IF EXISTS CofeeBreaks;
CREATE TEMPORARY TABLE IF NOT EXISTS Person
(
    `person_id` INT(11) AUTO_INCREMENT,
    `age` INT,
    `lastCofee` DATETIME,
    KEY `idkey` (`person_id`) USING BTREE,
    KEY `datekey` (`lastCofee`) USING BTREE
) ENGINE = MEMORY;
CREATE TEMPORARY TABLE IF NOT EXISTS CofeeBreaks
(
    `id` INT(11) AUTO_INCREMENT,
    `cofeeBreakStart` DATETIME,
    `cofeeBreakEnd` DATETIME,
    KEY `brekIdKey`(`id`) USING BTREE
) ENGINE = MEMORY;
INSERT INTO Person (age, lastCofee) VALUES (24, '2013-03-27 14:45:34');
INSERT INTO Person (age, lastCofee) VALUES (34, '2013-03-27 14:46:38');
INSERT INTO Person (age, lastCofee) VALUES (26, '2013-03-27 15:25:24');
INSERT INTO Person (age, lastCofee) VALUES (28, '2013-03-27 16:33:54');
INSERT INTO Person (age, lastCofee) VALUES (46, '2013-03-27 17:11:03');
INSERT INTO CofeeBreaks (cofeeBreakStart, cofeeBreakEnd) VALUES ('2013-03-27 15:11:03', '2013-03-27 17:25:24');
INSERT INTO CofeeBreaks (cofeeBreakStart, cofeeBreakEnd) VALUES ('2013-03-27 14:45:34', '2013-03-27 15:25:24');
INSERT INTO CofeeBreaks (cofeeBreakStart, cofeeBreakEnd) VALUES ('2013-03-27 17:11:03', '2013-03-27 17:11:03');
SELECT * FROM Person WHERE lastCofee BETWEEN '2013-03-27 15:11:03' AND '2013-03-27 17:11:03';
SELECT
    *,
    (SELECT AVG(Person.age) FROM Person WHERE Person.lastCofee BETWEEN CofeeBreaks.cofeeBreakStart AND CofeeBreaks.cofeeBreakEnd) AS averageAge
FROM CofeeBreaks;

So the explain results for the first select are as follow:

1   SIMPLE  Person  range   datekey datekey 9       1   Using where

But the second query doesn't use the index properly in the subquery and analyses more rows than necessary:

id  select_type         table   type    possible_keys   key key_len ref rows
1   PRIMARY CofeeBreaks ALL                                               3
2   DEPENDENT SUBQUERY  Person  ALL       datekey                         5

As we can see the subquery needs to analyse all rows in the person table when none of the cofeebreaks ranges surrounds all of the 5 persons.

The way I've been fixing the performance issues in a very busy database is by doing a select into a temporary table and than looping the rows of that table and updating it with the aggregates that i need. This is obviously ugly code and shouldn't be built that way.
But I really haven't found a way to optimize queries for this kind of subqueries and I've tried a lot of possible ways to do this without success on the optimization.

Thanks in advance for any inputs.


texts:
I am trying to get a sum inside a case statement, but I am getting the error

  Cannot perform an aggregate function on an expression containing an aggregate or subquery

This is my query syntax

Select case
          when sc.ars = 0 then 0
          else SUM(sc.ars / NULLIF(SUM(ic.lit),0))
       end
FROM salesconfig sc
Left Join income ic
On sc.empID = ic.empID

How do I need to change this query so it gives me the result and not an error?


texts: Merge Replication: Service Account Password Change

texts: Is it possible to change the password of a SQL Server service account that is used for not only on the publisher server but the 6 subscriber servers as well without seriously affecting merge replication? I'd like to avoid (or at least be forewarned of) any potential issues because replication is not my strong suit and I hate troubleshooting it.


texts:
I am developing a Stocks management application, the Portfolio register table has 4 columns, other than date and registry_key, and it looks like this:

----------------------------------------
| stock_key | user_key | units | price |
----------------------------------------
| fund_a    | user_1   | 1000  | 100.35|
| fund_c    | user_2   | 4000  | 101.55|
| fund_d    | user_3   | 2000  | 105.65|
| fund_a    | user_1   | 1500  | 101.45|
| fund_b    | user_1   | 500   | 103.35|
----------------------------------------

I wish to select the portfolio of user_1 with distinct stock_key. Plus I need the price as the aggregate total paid over the number of units. For example:

For user_1 the result should be:

[
    {fund_key: "fund_a", units: sum(1000,1500), price: sum(1000*100.35,1500*101.45)/sum(1000,1500)},
    {fund_key: "fund_b", units: 500, price: 103.35}
]

This is important because once the user has brought more units of the same stock at a lower/higher price than before, the accumulated stocks price is averaged out.


texts: ORA-21780: Maximum number of object durations exceeded

texts:
I am getting this error when records are streamed over to another database

Oracle DB version:11.2.0.3.0

Transaction Flow:
DML on Schema1@DB1 Streamed to Schema2@DB2 then trigger on this table will insert into
Schema3@DB2 then trigger on this table will insert into table in Schema4@DB2

ORA-21780 error happening at the last stage.

Please advise.


texts: Running SSIS from a different server than the primary database instance

texts:
We currently have a single SQL Server Integration Services (SSIS) package that performs an extract-transfer-load (ETL) process to move some data from SQL Server to DB2.  On the current production server SSIS is installed on the same server as the database instance and the package execution is managed by the SQL Agent via a Job.

We are upgrading our SQL infrastructure to be clustered and the senior database administrator would like to have SSIS on it's own server, so the ETL server was set up by just installing integration services service onto the machine.

Does the SSIS server also need it's own database instance and/or SQL Agent to manage the execution of the packages?

I'm having some trouble understanding how all the pieces would work in this scenario.

We are using SQL Server 2008 R2 SP1.


texts: How to modify the block size of my S3 parquet files that are being queried in Athena?

texts: I read that adjusting the block size of the parquet files being queried with Athena can affect and possibly improve the performance of the queries. My parquet files for my database are currently created with DMS (hooked into MS SQL Server as the source). Is it a property of my DMS job I need to change, the parquet files in my S3 bucket themselves, or would I need to create an ETL job with Glue to modify the block sizes after they're created?


texts:
I want to change my column data type from "Datetime" to "Datetime2(7)". If I try to do it with the GUI SQL Server 2014 asks to save all FKs table dependencies and then I get a lock timeout because this table has millions of rows. Is this possible while not losing the FKs dependencies and any data?

It is a Test environment. The reason I want to do this is because I think I'm getting locks because of datetime precision (milliseconds). I have many rows with the same datetime and not ordered.


texts:
When I issue the following command:

mysql -u user -ppassword database_name < /path_to_database.sql

I get:

  sh: database_name: not found

And it stops processing. I have created database_name and it exists.

Can somebody help? I'm running on Ubuntu 10.04.


texts: Index usage stats DMV indicates no index activity

texts:
I'm trying to obtain information on indexes.  According to several websites, the below TSQL query should join information on indexes to the indexes table, returning indexes and related information.  The below query is simplified to the bare join, as I'm currently trying to return data and then from there, I will return what I need:

SELECT *
FROM sys.indexes i
     INNER JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id

This query returns nothing.  Yet, in several databases there are hundreds of tables that have at least one index, and according to various articles online, this query should return those indexes (because it's joining on the object ids).  Also, if I try to change the query to the below query, it returns nothing as well:

SELECT *
FROM DATABASENAME.sys.indexes i
    INNER JOIN DATABASENAME.sys.dm_db_index_usage_stats s ON i.object_id = s.object_id

Why isn't this query returning data?

Added

If I run the below queries, I receive data from both of these SELECT queries:

SELECT *
FROM sys.indexes

SELECT *
FROM sys.dm_db_index_usage_stats

The problem (on the original query) is the object_id join.  However, I don't know why I have objects in one table that don't exist in another.

Final: Martin had the right answer originally - even though data was being returned from the sys.dm_db_index_usage_stats table, it was on the msdb database, not the specific database I was seeking.  I ran queries on tables again, and found the database id in the table.  Unsure of why the sys.dm_db_index_usage_stats table didn't retain data from previous queries on that database.


texts: MySQL insists on trying to use InnoDB even when told not to, please help

texts:
We're trying out MySQL 5.5, and have installed it on one of our slave servers as a test run. Our main server is 5.0, and we have zero optimization here for InnoDB, so I want everything to just be MyISAM for now. I've disabled InnoDB everywhere I can think, but the replication is throwing errors like:

Error 'Unknown storage engine 'InnoDB'' on query. Default database: ''. Query: 'CREATE TEMPORARY TABLE ... SELECT ...

The query never specifies the engine is InnoDB. default-storage-engine is set toMyISAM. innodb is set to OFF. Why is it trying to create this termporary table as InnoDB? This is 5.5, not 5.6, so we don't have the default-tmp-storage-engine variable yet. Please help, this is driving me insane.


texts: Is it possible to do this math in a view?

texts:
I have been tasked with creating a view for a client. Specifically it must be in a view. However, there is some math that I am not sure how to do within a view. I do not know if it is even possible. But then again, my mind is feeble.

I am using SQL Server 2008R2, so advanced OVER() functionality doesn't work.

Let's say a person is given $400 to spend. They can spend more, but the first $400 is free. One column of the report will have the amount the person spent on something, and another will have the total amount the person needs to pay out of their own pocket.

So, for the first record in the report for this person, one column will have an amount they have spent, say $50, and then a second column will have a $0. Behind the scenes they still have $350 to spend.

The next record has the person spending $300. The second column will still show a $0, and behind the scenes the initial $400 is now $50.

The third record for the person shows they spent $75 dollars, but they only have $50 left over from the initial $400. The second column should now have a $25 value in it. They have exhausted the initial $400 and are now spending their own money.

The fourth record shows they spent $40, so now the second column will show a $65. etc...

I have briefly read about CTEs and table valued functions and such, but is it possible to use them in any combination to give the desired behavior above?

Below is some sample code for structure and desired results

CREATE TABLE Payroll (
    PersonID int,
    PlanCode varchar(10),
    Deduction int NULL
)
GO

INSERT INTO Payroll (PersonID, PlanCode, Deduction)
VALUES (1, 'Medical', 200)
  ,(1, 'Dental', 250)
  ,(1, 'Vision', 300)
  ,(2, 'Medical', 100)
  ,(2, 'Dental', 150)
  ,(2, 'Vision', 100)
  ,(2, 'Disability', 100)
  ,(2, 'Life', 140)

Desired results:

It may make sense to think of the OutOfPocket as TotalOutOfPocket.

There is nothing like a timestamp in the source data for ordering of the entries. The ordering is not too important. If any ordering is done, it will be on the PlanCode.

Based upon our constraints and a 3rd column that was not necessary to include, there will not be any duplicate entries possible.


texts: Accessing nested array in json blob in PostgreSQL

texts:
I have a query I need to run that accesses a doubly nested array inside a json blob. The data is structure like the following:

{
"id" : "5",
"data" : "[[1,2,3],[2,3,4],[4,5,6]....]
}

My query needs to take the max of the second value (e.g. 5 in the case given). The following query returns almost what I need:

SELECT
  id,
  (
    SELECT
      string_agg(value :: text, ',') AS list
    FROM
      jsonb_array_elements_text(data -> 'MMC')
  ) as MMCPow
FROM
  comp

gives me:

[1,2,3],[2,3,4],[4,5,6]....

Can anyone get me the rest of the way?


texts: How does SQL bind rows in filestream-enabled table with their files

texts:
Every BLOB stored in a filestream-Enabled table actually stored in a physical file, How can I know the actual filename for a specific row in that table.
For example, I have the Images table which is filestream-enabled, it have a filestream column called Data its type : VARBINARY(MAX).
Here is an example of that table that have 19 rows and also the filestream folder contains 19 rows :

Is it stored in some system table, or there is direct relationship between the "ROWGUIDCOL" and its name ?


texts:
I've been reading multiple resources on SQL Server Encryption, and everyone does it basically the same way, but no one explains why we use asymmetric keys in SQL Server. And by this I mean when we follow "proper" procedure and encrypt data symmetrically first, followed by an asymmetric key or certificate as in this MSDN heirarchy:

So if I have data, first I encrypt it with a symmetric key for speed and avoidance of re-encryption if compromised. We then secure that key with a second-tier of encryption.

Now lets assume I secure the second key with a password. What advantage does a Certificate, Asymmetric Key, or Symmetric Key have in this scenario? It seems to me we never utilize the public aspect of a Certificate or Asymmetric key using this strategy since we are only using the decryption aspect. So shouldn't be be agnostic to which of the three methods we choose? We can revoke access to all three key types and grant permissions to use all three keys. Why not use another symmetric key?

The only difference I see here is that you can protect a Cert or Asym key with the DMK so users need not enter a password. This makes access easier at the expense of losing the security of forcing users to enter that second password.

Is this a correct understanding?


texts:
I have a quiz site with approx 2000 questions. The function in question worked great when I wasn't tracking that many users, but now there are 300,000 question that users have seen. The function only checks if a user has seen a question and returns questions that they have not seen.

SELECT q.id
FROM questions q
WHERE
      q.chapter_id = ?
      && (q.module = 1 || q.module = ?)
      && q.question_type != 4
      && q.id NOT IN(
          SELECT u.question_id
          FROM user_quiz_questions u
          WHERE user_id = ? && chapter_id = ?
      )
ORDER BY RAND()
LIMIT ?

Just to elaborate on the code a little:

questions is the table where all the questions are.
user_quiz_questions is the table that tracks the users previous answers.
id is the id for the question
module checks if it in the users study group
question_type makes sure it is an active question.

Any help on this issue would be great. Thanks.


texts: There's a long delay in this query that I need to resolve

texts:
This is a multi-tenant/portal DNN (formally DotNetNuke) database, and a core query as part of the registration process seems to be terribly slow.

Here it is:

declare @PortalID int = 0;

SELECT R.*,
    (SELECT COUNT(*)
     FROM   dbo.[UserRoles] U
     WHERE  U.RoleID = R.RoleID
            AND U.Status = 1
            AND (U.EffectiveDate < GETDATE() OR U.EffectiveDate IS NULL)
            AND (U.ExpiryDate > GETDATE() OR U.ExpiryDate IS NULL)) AS UserCount
FROM  dbo.[Roles] R
WHERE (R.PortalId = @PortalID OR R.PortalId is null)
      AND (R.RoleId >= 0)
ORDER BY R.RoleName
OPTION (OPTIMIZE FOR (@PortalId UNKNOWN))

I'll be honest, I haven't used optimization options before. What's critical here is it's a multi-portal site so what I change I have to be absolutely sure it doesn't spring up a nasty gotcha down the road.

I'm assuming the execution plan has to accommodate potential nulls in the @PortalID that of which is a parameter within the stored procedure. My example code above is extracted and I'm setting the @PortalID to 0.

The core developers created this procedure, and response times to get assistance vary so I'm asking here.

Is there a risk to changing this optimization to:

OPTION (OPTIMIZE FOR (@PortalId = 0))

This is the main portal used and is by far the most important. The core developers have to account for sites that have hundreds or even thousands of portals.

Even so, changing this leaves no guarantee of future upgrades as they may overwrite it. Just trying to track down a problem and want to know of a fail-safe way to optimize this query.

So if I set:

OPTION (OPTIMIZE FOR (@PortalID = 0))

It runs fast... 10x faster.

But I'm uncertain of caching or other problems that may arise from changing this.


texts:
I have a sql server which being monitor by Nagios through snmp string.
One day, i received some alert from nagios come with some sensitive information like:

Critical Alert: dbi connect (Server name: ABC, Encrypted Password: 1, User:
Sa.......)

How can i hide this ?


texts: Index usage for database: where are the sys tables located

texts:
I have seen a couple of scripts that query some sys.someName tables to find usage of indexes in a database. My guess is that these tables are tied to a database e.g. there is a sys.indexes table in every database. But where can I see these tables in SSMS? Or are all these tables in the msdb database?
An example of a script I have found is this. Of course when I change the context of the database the result set changes also but I cannot find these tables in SSMS.
SELECT  Tab.name  Table_Name
             ,IX.name  Index_Name
             ,IX.type_desc Index_Type
             ,Col.name  Index_Column_Name
             ,IXC.is_included_column Is_Included_Column

           FROM  sys.indexes IX
           INNER JOIN sys.index_columns IXC  ON  IX.object_id   =   IXC.object_id AND  IX.index_id  =  IXC.index_id
           INNER JOIN sys.columns Col   ON  IX.object_id   =   Col.object_id  AND IXC.column_id  =   Col.column_id
           INNER JOIN sys.tables Tab      ON  IX.object_id = Tab.object_id

Source (https://www.sqlshack.com/gathering-sql-server-indexes-statistics-and-usage-information/)


texts:
I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds.  It seems that MySQL rebuilds (a part) of the full text index after each insert/update.  Is this right?

The table has about 30 varchar and text fields, some indexes on varchar-fields and a fulltext index over the three fields title_text (char(255)), content_text (text), author_text (char(255)). In the table are about 210.000 rows, the table size is about 2.5 GB and the index size about 0.5 GB. The insert queries are done for every new row (no combined query for all inserts).

How can I get better performance from the INSERT? Is there perhaps an option to set when MySQL rebuilds the full-text index?


texts: Comparing records from same table and deleting the old ones

texts:
Greeting,
I have a beginner experience with programming in Oracle PL/SQL. The problem I am trying to solve is that in a hypothetical situation, I want to compare records in the same table based on the arbitrary columns called effdt(effective date) and position_nbr(position number). They would be based on new and old records. If the the new effdt field is greater than or equal to(>=) the old effdt based on the old and new position_nbr being equal, I want to delete the old effdt record from the table. Below is the small procedure I have developed to execute.
DECLARE
table_cnt_001           NUMBER;
CURSOR ITR1
IS
(SELECT *
FROM
TABLE1 O
INNER JOIN TABLE1 N
ON
  N.POSITION_NBR = O.POSITION_NBR
WHERE
  N.EFFDT >= O.EFFDT);
BEGIN
SELECT COUNT(*) INTO effdt_cnt_001 FROM TABLE1;
--Checking if table is empty
IF table_cnt_001 !=0 THEN

FOR IT IN ITR1 LOOP
--BEGIN
IF (N.EFFDT >= O.EFFDT) AND (N.POSITION_NBR = O.POSITION_NBR) THEN
DELETE FROM TABLE1 O
WHERE EXISTS(SELECT O.EFFDT FROM TABLE1 O);
END IF;
COMMIT;
--END;
END LOOP;
END IF;
END;

I have gotten the error identifier 'N.EFFDT' must be declared. As far as I am concerned, I am refencing the cursor in the for loop and I am not sure why the if statement requests for this column to be declared. Please let me know what could be wrong with this logic or if there is a better way to solve this problem. Thanks in advance.


texts:
I am testing my application in mysql 5.7 and have some code that doesn't work with ONLY_FULL_GROUP_BY ON. I will fix this in a later release. I found a way to turn this off for just my application by executing 'SET SESSION sql_mode=""' before running any queries.

I have 2 questions:

Will this work on shared host services that don't have root access to database? It appears to have worked on my testing.
Since the mode is set to '' what does this actually mean? To me it seems like it is setting it back to nothing and making at non restrictive. (My other thought is that it set it to nothing and lets mysql decide what to do).

Based on my reading of the manual empty mode use to be default in mysql 5.1 but has become more restrictive with each version. For mysql 5.7 it is ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

So based on this it would seem that mode is a list of rules and if it is empty non the rules apply?

Also if I set the mode to "" will this always disable the modes set by server?


texts: SQL AlwaysOn & System Center Configuration Manager TP 4 1601

texts:
Recently Microsoft released Technical Preview 4 for Configuration Manager (1601).  Per TechNet, "Configuration Manager now supports using a SQL Server AlwaysOn availability groups to host the site database. When you install a new site, you can direct setup to use the availability group instead of a normal instance of SQL Server."

They then note "Successful configuration and use of availability groups requires you to be comfortable with configuring SQL Server availability groups, and relies on documentation and procedures provided in the SQL Server documentation library."

Currently, the best practice for creating fault tolerance and to avoid a single point of failure for the Configuration Manager Site Database is to install SQL replicas at each management point.  I've watched several overview videos (from 2013) to get a better understanding of how AlwaysOn works.

In the DBA community, is AlwaysOn a better solution to providing fault tolerance and avoiding a single point of failure over SQL replicas?

Full discloser:  I'm learning Configuration Manager and have very basic knowledge of SQL Server so any advice you have or suggestions would be greatly appreciated.


texts: Postgresql. Real-time copy of a database to another disk on the same server

texts:
How to make a real-time copy of the database to another disk?

My database is very small, but WAL-logs are very large (many changes in time). I don't need 'Point-in-Time Recovery', I just need the latest version of the database. How to force a sql server to maintain an additional copy of the database to another disk in case the first disk lost data (hardware failure)?


texts: Deletion of orphaned rows in a violation-free & non-blocking manner: predictive order of execution of predicates in case of double-checked locking

texts:
I have tables person and toy. There is a foreign key person.favorite_toy_id from person to toy. This foreign key is declared as ON DELETE RESTRICT. I now want to delete all toys which are no longer declared as favorite in a violation-free and non-blocking manner:

I want to delete as much orphaned toys as possible, while avoiding a foreign key violation because we attempt to delete a toy which is still in use.
I do not want to wait for other ongoing transactions, which are possibly introducing a reference to a toy (which requires a key share lock) or simply updating a toy (which requires a (no key) update lock), to finish. Both lock types block our update lock request, required to delete the toy.

The first, naive approach would be:

delete from toy
where       not exists(select 1 from person where person.favorite_toy_id = toy.id)

This will not work in a concurrent environment: after the completion of the not exists predicate, a concurrent transaction could declare the toy in question as favorite. In such case, we end up with a foreign key violation. Also, as said, I prefer this delete to happen in a non-blocking fashion, which is not attempted in this query.

So, my second approach in attempt to avoid this foreign key violation and any blocking is:

delete from toy
where       toy.id in
            (
              select toy.id
              from   toy
              where  not exists(select 1 from person where person.favorite_toy_id = toy.id)
              for update skip locked
            )

However, this doesn't solve the requirement to avoid the foreign key violation, because the lock is taken after the evaluation of the not exists predicate. So there is a small chance that we attempt to delete a toy which is still marked as favorite, resulting in a foreign key violation.

My third attempt to fix this is the following:

delete from toy
where       toy.id in
            (
              select toy.id
              from   toy
              where  not exists(select 1 from person where person.favorite_toy_id = toy.id)
              for update skip locked
            ) and
            not exists(select 1 from person where person.favorite_toy_id = toy.id)

This applies double-checked locking (https://en.wikipedia.org/wiki/Double-checked_locking). This would work if and only if we have the guarantee that the subquery is always evaluated before the additional not exists predicate. As far as I know, there is no such guarantee.

My question is rather educational: can this be solved in a pure SQL query? We can of course implement this in a plpgsql function as shown below, but let's assume we want to solve this in one single plain SQL query.

create function prune_toys() returns void as
$$
declare
  _id int;
begin
  for _id in select toy.id from toy where not exists(...) for update skip locked loop
    delete from toy where toy.id = _id and not exists(...);
  end loop;
end;
$$
language plpgsql;

In all this, I assume the read committed transaction isolation level.


texts: How to store static list of hashes in MySQL effectively?

texts:
Problem: I have a list of pairs (md5_hash, id). I want to store the data in MySQL and I want to make queries like this

SELECT id FROM table WHERE md5_hash = <some_hash>

The number of pairs can be tens or hundred of millions and the number is static - i.e., I do not add new records. It is important for me to save disk space. The time is not so important in this case (if a lookup takes less than say 1 second it is okey).

My thoughts:

I started by creating a table where md5_hash is represented in this way:

CREATE TABLE `myTable` (
   `md5` binary(16) NOT NULL, ' the values are not unique but we can suppose they are.
   `id` int(10) unsigned NOT NULL
) ENGINE=MyISAM

so md5 hashes are represented as binary numbers instead of hexadecimal characters to save space. For the same reasons the columns are defined as "NOT NULL". I chose MyISAM engine because my tests show that MyISAM requires less disk space than InnoDB. In addition MyISAM tables can be compressed by myisampack utility.

Now the hard part comes. When I create an index on md5 column the index requires more disk space than the data itself! I tested it with 17 millions records and my table took around 300 MB of disk space and the index took about 330 MB of disk space. The size of the index is  crazy.

One idea is to provide the md5 hashes presorted to MySQL which should result in smaller index size. But I do not know how to do that.

Another idea is to divide myTable in several smaller tables in order to decrease size of the index. I tried mysql partitioning. However, the purpose of the feature is to improve query time and not disk space usage.

Do you have any idea how to decrease the required disk space usage?


texts:
I am trying to use roundcube and it recently just broke. I don't know if this is due to a MySQL update that happened recently or not but in phpMyAdmin I get the following error if I try and view a table:

1286 - Unknown storage engine 'InnoDB'

and

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

and

[mysqld]
default-storage-engine=MyISAM
local-infile=0
symbolic-links=0
skip-networking
max_connections = 500
max_user_connections = 20
key_buffer = 512M
myisam_sort_buffer_size = 64M
join_buffer_size = 64M
read_buffer_size = 12M
sort_buffer_size = 12M
read_rnd_buffer_size = 12M
table_cache = 2048
thread_cache_size = 16K
wait_timeout = 30
connect_timeout = 15
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
low_priority_updates=1
concurrent_insert=ALWAYS
log-error=/var/log/mysql/error.log
tmpdir=/home/mysqltmp
myisam_repair_threads=4
[mysqld_safe]
open_files_limit = 8192
log-error=/var/log/mysql/error.log

[mysqldump]
quick
max_allowed_packet = 512M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

Ideas as to how to fix? It used to work just fine.


texts: Is support for Parallel Scalar UDF a reasonable feature request?

texts:
It is fairly well documented that scalar UDF's force an overall serial plan.

Running functions in parallel

Given a large number of rows coming into a point in the pipeline where a UDF must be calculated, why can't the engine just distribute them among the processors?  If there is no state within a UDF then the order shouldn't matter.

There are claims about UDF's being a black box must use cursor.  I can see that a user cursor cannot be parallelized within an SP for the cases where some state is maintained between iterations but seems like it should be parallelizable otherwise.

Extra points for explaining why the engine forces the whole plan to be serial instead of just the UDF calculation stage.

Is support for parallel UDF a reasonable feature to request?


texts: storing data from different sources in a single column?

texts:
I want to ask a general question. We are getting data from different stores like magento, woocommerce, amazon aws, shoppify and many others. Data like customers info, orders info, products info is being fetched. All the stores are returning data that is bit same, bit different. Here are two database designs that we are proposing:

Use different columns for every store like magento_customers, shoppify_customers but this do not seems right.
Make a single column for customers from all sources. fill the fields that has data and make others null, like magento returns group_id, but woocommerce do not, so fill group_id by '1' lets suppose and when it's null, keep it null.
I am a junior developer, and this is my first commercial project so I'm little hesitant.


texts:
I am trying to put the following entity-model into practice:

We have a list of Payments, each Payment belongs to a certain store and is of a certain payment type (e.g. 10€ maintenance cost in Store1, 20€ rent in Store2)
A Payment Type is a categroy like (rent, maintenance, legal fees, licenses) and each category is assigned a certaing account_type (e.g. budget_legal, budget_monthly, budget_extraordinary ) where multiple Payment types can have the same account_type
We have accounts: An account belongs to a certain store for a certain account type. So Store1 can have account-types budget_legal, budget_monthly, while Store2 has account types budget_legal, budget_monthly and budget_extra

Relationship Model:

Image Created with http://draw.io/

Now I want a constraint that each payment must belong to an existing account. I want to guarantee the following:

When I change the account_type of a Payment_type, all payments of this type will then "belong" to the respective accounts.
If I delete an account I want to delete all payments in this account ( like delete cascade in a foreign key )
I can only insert/update Payments so that each payment refers to a valid store_id/account_type, which exists in the accounts table.

Possible solutions which I tried but don't really work:

I tried to add the account_type to payments, but then I have doubled the field - and when I change a Payment type I will have to change the referenced payments via trigger. - But in the best case I don't want triggers to enforce data integrity - and I don't want duplicate data storage.

I tried to create a foreign key constraint over multiple tables / a view which joins payment with payment_types. Unfortunately this seems to be impossible in Oracle DBMS

I tried to create a virtual column on Payments, which calculates the value from payment_types, but virtual columns can only reference columns in the same table.


texts: How many rows will be locked by SELECT … ORDER BY xxx LIMIT 1 FOR UPDATE?

texts:
As was asked on stackoverflow but then for MySQL, I was wondering how this works in PostgreSQL. How many rows get locked when I do a "FOR UPDATE" in combination with a "LIMIT", a "ORDER BY" and some "WHERE"'s. And they update the returned row. I was hoping the "FOR UPDATE"-query would only lock ONE row but maybe I miss some implementations issue. (I'm planning to write some stress-test to reproduce this more reliably)

These queries cause a deadlock in my system:

(session1) select field1, field2, ... from documents where transaction_path='some/path' for update
(session2) select field1, field2, ... from documents where (transaction_path is null) and queue_id=2 and next_pickup_ts<now() order by next_pickup_ts limit 1 for update
(session3) select field1, field2, ... from documents where (transaction_path is null) and queue_id=2 and next_pickup_ts<now() order by next_pickup_ts limit 1 for update

But I do not understand why. (There is a unique index on transaction_path.)


texts: Are there any fully declarative implementations of SQL

texts:
I was recently bitten by a bug ( in my code ) where these two queries had drastically different runtimes:

select * from smalltable st
    inner join bigtable bt on st.btid = bt.btid

select * from bigtable bt
    inner join smalltable st on bt.btid = st.btid

As well as the individual queries finding different execution plans for different strings in the where filters.

Are there any optimizing, fully declarative implementations of the SQL standard, such that the two above queries would have equal execution plans?


texts: Creating SQL Server triggers to copy a record from one table and populate another

texts:
I want to create a trigger that will copy a record from one table and populate another after an insert or update. Users will enter a date in an inspection table and I want that date inserted or updated into an asset table date column. I am a newb to SQL and T-SQL and have not created triggers before. Guidance on how to get this setup would be appreciated.

Here's a better explanation:

The table I want to create the trigger on is called [Inspections]. Users will add new records to this table and included a DateInspected value in that row. I want the trigger to take that new DateInspected value from [Inspections] and update an existing value in a table called [Hydrants].

The [Inspections] table and the [Hydrants] table have a primary key called [Id]. How do I ensure the trigger is updating the correct existing record in the [Hydrants] table?


texts: How to convert primary keys into clustered primary keys for a great number of tables keeping referential identity

texts:
After discovering, that I better change the primary keys from non clustered to clustered. Cf this question about clustered indexes on identity columns, the next step is how to do this in a prcatical way.
I was surprised that I didn't found this question on SO.
Having referential references between my tables, I can't simply do
if object_id('MyChild') > 0 drop table MyChild
if object_id('Mytable') > 0 drop table Mytable
create table Mytable(
    id int Not NULL
)
go
create table MyChild(
    id int Not NULL,
    masterid int NULL
)
go
alter table Mytable add constraint PK_Mytable primary key  (id)
alter table MyChild add constraint FK_MyChild_MyTable foreign key (masterid) references Mytable(id)
go
-- Now I want to change the primary key into a clustered primary key
-- this throws an error
ALTER TABLE Mytable DROP CONSTRAINT PK_Mytable
GO
ALTER TABLE Mytable ADD CONSTRAINT PK_Mytable  PRIMARY KEY CLUSTERED (id)
GO

Here I get the following error
Msg 3725, Level 16, State 0, Line 1
Auf die 'PK_Mytable'-Einschränkung wird von der 'MyChild'-Tabelle, FOREIGN KEY-Einschränkung 'FK_MyChild_MyTable' verwiesen.
Msg 3727, Level 16, State 0, Line 1
Die Einschränkung konnte nicht gelöscht werden. Siehe vorherige Fehler.

OK I know that it works, when I drop the foreign key constraint before the change and reestablish it afterwards.
alter table MyChild drop constraint FK_MyChild_MyTable
go
ALTER TABLE Mytable DROP CONSTRAINT PK_Mytable
GO
ALTER TABLE Mytable ADD CONSTRAINT PK_Mytable  PRIMARY KEY CLUSTERED (id)
GO
alter table MyChild add constraint FK_MyChild_MyTable foreign key (masterid) references Mytable(id)
go

That sounds like, I have to script all the foreign key relations from the database, drop them, do my primary key changes and reestablish the foreign keys from the generated script.
Are there any better ways for this task.
BTW. Finally we want to do this on production databases. (Downtime is an allowed option). All databases are SQL Server 2005 or SQL Server 2008.
Edit:
A coworker proposed to drop all indexes before this operation and to rebuild them afterwards. Sounds reasonable to me.
2. Edit:
Looking at this dba.stackexchange  question, I'm starting to ask myself whether it would suffice to just add clustered indexes to the tables primary key columns.


texts: Four-part distributed Query issue from 2014 to 2008R2

texts:
From a SQL Server 2014 RTM in our DEV environement, I am executing this query using a SQL login.

select t.noshipid, y.nofolder
                        from distantserver.distantdatabase.dbo.sometable t (nolock)
                        inner join  somedatabase.dbo.sometable y (nolock)
                            on cast(t.nobill as bigint) = y.nobill
                        where t.type_payment = 'CE'
                        group by t.noshipid, y.nofolder

distantserver is in 2008 R2.

I get back an error

  Msg 8522, Level 16, State 3, Line 1 Microsoft Distributed Transaction
  Coordinator (MS DTC) has stopped this transaction.

If I execute the same query on the production server which is 2008 R2, no errors.

1: The SQL login in question has the necessary access.

2: The provider being used to connect to the 2008 R2 server is SQLNCLI10

Questions:

1: Is it possible that upgrading the DEV server to 2014 is causing these issues?

2: Are there any know issues using a four-part identifier from 2014 to 2008 R2 ?

from distantserver.distantdatabase.dbo.sometable

instead of using openquery

FROM    OPENQUERY([distantserver],

because using OPENQUERY, the query does not return errors.

3: Are there any sp_configure options or linked server options I need to configure because of 2014?

Update 14:41

If I add

BEGIN DISTRIBUTED TRANSACTION

at the top, the distributed part works fine. I just want to emphasize the fact that before upgrading, this was not necessary.


texts: How to avoid Forced reboots using unattended upgrades to SQL Server 2014

texts:
Upgrading seven instances from SQL Server 2008R2 to 2014 via the ADMINISTRATOR command line using a configuration file. The upgrade runs fine but when I move on to upgrading the next instance I get the following error.

From : C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\20150105_152519\Summary.txt

Error result: -2067919934
Result facility code: 1214
Result error code: 3010

Exception type: Microsoft.SqlServer.Configuration.RulesEngineExtension.RulesEngineRuleFailureException
    Message:
        A computer restart is required. You must restart this computer before installing SQL Server.
    HResult : 0x84be0bc2
        FacilityCode : 1214 (4be)
        ErrorCode : 3010 (0bc2)
    Data:
      SQL.Setup.FailureCategory = RuleViolationFailure
      DisableWatson = true
    Stack:
        at Microsoft.SqlServer.Configuration.RulesEngineExtension.RunRulesAction.ExecuteAction(String actionId)
        at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
        at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClasse.<ExecuteActionWithRetryHelper>b__b()
        at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(ActionWorker workerDelegate)

I am able to continue after restarting, but something is very wrong if I have to restart after every upgrade. Can someone help me figure out, why I have to restart after upgrading every instance?

Here is the configuration file I'm using

INSTANCENAME="SOME_INSTANCE"
SQMREPORTING="False"
INSTANCEID="SOME_INSTANCE"
IACCEPTSQLSERVERLICENSETERMS="True"
ACTION="Upgrade"
ENU="True"
QUIET="True"
UpdateEnabled="True"
ERRORREPORTING="False"
USEMICROSOFTUPDATE="False"
UpdateSource="MU"
HELP="False"
INDICATEPROGRESS="False"
X86="False"
FAILOVERCLUSTERROLLOWNERSHIP="2"

Here is the command being used to upgrade :

setup.exe  /ConfigurationFile="Confguration_Upgrade.ini"

Update

More information from detail.txt

# (01) 2015-01-05 15:45:00 Slp: File operations are pending for "SQLCTR100.DLL"
# (01) 2015-01-05 15:45:00 Slp: File operations are pending for "SQLAGENTCTR100.DLL"
# (01) 2015-01-05 15:45:00 Slp: Rule 'RebootRequiredCheck' results: IsRebootNotRequired=False
# (01) 2015-01-05 15:45:00 Slp: Evaluating rule : RebootRequiredCheck
# (01) 2015-01-05 15:45:00 Slp: Rule running on machine: G06SQL001
# (01) 2015-01-05 15:45:00 Slp: Rule evaluation done : Failed
# (01) 2015-01-05 15:45:00 Slp: Rule evaluation message: A computer restart is required. You must restart this computer before installing SQL Server.


texts: PLSQL: Package stored procedure cannot call exe imediate statement

texts:
I'm working with oracle 11g R2.

Issue:

I am unable to preform dynamic SQL in SchemaB package against SchemaA tables. SchemaB is being called by the owner of SchemaB.

Background:

I have a package/procedure that has the rights to preform a selection statement on another schema's table.

So I can call the below with no issues.

select * from SchemaA.TableA where blah

I need to be able to run this call from dynamic SQL, because the table from SchemaA could be any of 10. I created the below SQL. I get an error

  ORA-01031: insufficient privilegeswhen I run the same statement but withexecute immediate

Code:

v_query varchar2(2000);
v_query := 'select id, value1, value2 from SchemaA. ' || TableName || ' where id = ' || id;

execute immediate v_query ;

I know that the package does not get the user roles, so I even put the following code into the signature of my package AUTHID CURRENT_USER ; which changed nothing.

What other permissions do I need to set in order to do this?

OR if you have another solution for dynamic table selection, that would be great too.


texts: SQL Server 2005, non-yielding scheduler Spinloop on Snapshot

texts:
I was running dbWarden's procedure for CheckFiles on my server.

The job never ended and my logs started to fill up with message at a rate of 1 per minute: 2014-01-07 08:40:04.07 Server Process 58:0:0 (0x1438) Worker 0x00000000D62B41C0 appears to be non-yielding on Scheduler 4. Thread creation time: 13033413586324. Approx Thread CPU Used: kernel 46 ms, user 2421 ms. Process Utilization 10%. System Idle 82%. Interval: 79691009 ms.

From what I understand is that it means the thread on the CPU is just locked up.

The SSMS GUI just doesn't work with the instance any longer. In TSQL most things work, unless they reference the snapshot, which nixes most dmvs and system procedures. SP_Who2 doesn't work, but SP_Who does.

SP_WHO shows me 58 alternating between ready and spinloop.

58 is running the code to check files (locations, growth information, etc) on a snapshot.

When I KILL 58, it says it's rolling back, but nothing happens and it's still in a spinloop.

When I try SP_Who2, it gets blocked by the spinloop. When I drop the snapshot, it's blocked by the spinloop. When I restart or Stop/Start the instances service, the errors return to the log and the spinloop resumes.

Any ideas or advice?


texts: How to do SUM() but with some rules to follow?

texts:
Maybe this question is confusing to understand ( as I'm not fluent in English ) but,
I need to select sum(cod) for example, but this sum() needs to have the following rule:

Part 1:
it needs to be ALL cods, except for 21.

Part2:
Another column, with another sum(), but only with cod 21.

it's not the entire query, But after this, I think I can do it.

I tried this:

select sum(vit_codigotiposexames) from
    (
    select * from tb_vitima where vit_codigotiposexames
        in (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,22 )
    )Corpo_Delito,
sum(vit_codigotiposexames) from
    (
    select * from tb_vitima where vit_codigotiposexames  = 21 )
    )Cadaverico

It works If I have only

select sum(vit_codigotiposexames) from
    (
    select * from tb_vitima where vit_codigotiposexames
        in (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,22 )
    )Corpo_Delito

is there a way to make this query, a unique query? and, is there a way to name these columns? because I know I need to alias them, but they stay with that (no column name) above it.


texts: Workaround for lack of init-file on AWS RDS MYSQL

texts:
I'm trying to replicate my existing hosting configuration on AWS.  My problem is that in the current MARIADB configuration, I have an init file that loads several memory tables.  And apparently AWS RDS precludes the use of init scripts.

My work around is below... It involves scheduling a recurrent event that checks how long the server has been up and runs the init code if it goes below 300 secs (indicating a reboot).  This seems to work but feels a little kludgy.

Any better approaches? Any downsides to the kludge?

BEGIN
SET @uptime := (SELECT variable_value
    FROM information_schema.GLOBAL_STATUS
    WHERE VARIABLE_NAME LIKE  'uptime');
IF @uptime <300 then
     Put init code here....
END IF;
END

You will need to give yourself privilege to set up event Link

https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-functions/


texts:
I am playing around with a MariaDB Galera Multi Master Cluster and I have some performance issues which I do not understand.

I have 2 virtual test machines with Ubuntu 16.04 and MariaDB 10.1 installed. I wrote a simple "benchmark" tool which simulates the creation of a contact in an example application. If I run this benchmark on a single machine, I get around 1050 transactions per seconds on each machine. I did not change anything on the default configuration of mariadb.

If I now cluster those two machines together via galera to a multi-master cluster, I get a significant performance drop to around 99 transactions per second.

The two machines are connected via a Gigabit network. I measured the throughput with iperf and its 950Mbits. The ping times are around 0.35ms.
The galera configuration is:

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.193.220,192.168.193.221"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_sst_method=rsync
wsrep_node_address=192.168.193.220
wsrep_cluster_name="DB Cluster"

I do not wonder about the absolute transactions per second, but about the relative drop from a standalone instance to the cluster. What am I missing?


texts: MySQL: fetch multiple global status and system variable in a single query

texts:
When we execute SHOW global status; or SHOW variables; we get list of 291 and 278 records in the resultset. For performance perspective, only few of them are much important. I have to fetch these variables many times during performance hit.
For example, can we write below multiple statement into one?

show global status like 'Threads_cached';
show global status like 'Threads_connected';
show global status like 'Threads_created';
show global status like 'Threads_running';
show global status like 'Select_scan';

If we fetch these variables in a single statement, it will be really a great help...


texts: What is the relevance of 'SET default_with_oids = true' in a Postgresql dump?

texts:
I have an SQL backup from an old database which may have been a PostgreSQL  7 or 8 database I am importing which contains a SET default_with_oids - true statement, into a an existing 8.4 database and wonder what the relevance is and how it might affect the existing database.

I don't think the OIDs were actually used in the original database. Can I simply ignore it?


texts: Select All Instances of Substring in a String

texts:
I have a varchar(max) column that holds a JSON request string. I need a SQL statement for a report that will select all of the instances of that substring. More specifically I need to grab the LoanId Value from that substring entry pulling a result set that contains a row for each LoanId. Any help would be greatly appreciated.

A considerably abbreviated JSON string containing the stuff I'm looking for.

[
  {"loanId":"1111111111","someotherValue":7},
  {"loanId":"2222222222","someotherValue":4},
  {"loanId":"3333333333","someotherValue":5},
]


texts: Is there DMV for seeing view access count?

texts:
I saw it once during a presentation when someone showed a query to get a count of how many times views have queried in a SQL server. I don't remember if it was from DMV or a combination of some other statistics, but I clearly remember when they ran a query that selects from a view and after it would show that count going up by one. Another interesting fact that I recall about the presentation was selecting from a CTE increased the count by two because SQL Server has to create a "temporary view" and later select from it.

Does anyone know how this can be demonstrated?


texts: Shrinking a SQL Server 2008 Standard database to move it into SQL Server 2014 Express

texts:
I need to shrink a popular CRM database with SSMS to move it onto a new server. I am going from SQL Server 2008 Standard to SQL Server 2014 Express. Database is in Simple Recovery Model. It needs to be under the 10 gig limit.

There's 39% space to erase but shrink does not work. I've done this many times including this database after removing 100000's of rows in different tables but it does not want to shrink this time.

No errors just runs and does nothing.


texts: Help with a complicated MySQL Query inserting data using select from 2 tables

texts:
I am stuck with a case and unable to get the desired result from my query..

I have to insert into a table some records based on multiple records from 2 different tables..

Tables: Target: player_list_items Source1: list_items Source2: map_details

A player is supposed to play a list which contains a list of items; A list needs to be played over a map.

The map contains placeholders with their certain X and Y positions and I need to place list_items behind those placeholders. for that I have created the target table that will contain the list_items positioned randomly behind the placeholders.

Below is the query in which I was able to dump the list_items for a user into the target table but, now the problem is that how to get X and Y positions randomly for each list_items from the source2 table.

My First Query:

INSERT INTO player_list_items
(player_list_list_id, player_list_player_id, player_list_item_id, player_list_item_cellX, player_list_item_cellY)

SELECT li.list_item_list_id, 584488596, li.list_item_item_id, 2, 5
FROM list_items li
WHERE li.list_item_list_id = 2

Each list contains multiple items so the above query returns multiple items and its working fine, what needs to be considered is that the map_details table also contains multiple place holders and they would be more than the # of list_items for sure.

The required query is supposed to get all positions X and Y from the map_details table and assign then randomly to each individual item, positions for every item must be unique.

I have seen examples of multiple selects for insert but they are for single row insertion and my problem is multiple rows from multiple tables and random too.

My 2nd query:

INSERT INTO player_list_items (player_list_list_id, player_list_player_id, player_list_item_id, player_list_item_cellX, player_list_item_cellY)

SELECT list_item_list_id, 656328662, list_item_item_id,  game_map_details.cellX, game_map_details.cellY
FROM list_items
JOIN (SELECT * FROM game_map_details WHERE map_id = 1 ORDER BY RAND()) AS game_map_details
WHERE list_item_list_id = 2
GROUP BY list_item_item_id

This do adds the data from the map_details table but the data is not random, in fact its inserting only 1 pair of record for all 4 records from list_items table


texts: unique constraint violated (ORA-00001) in concurrent insert

texts:
I have a procedure that is called from concurrent transactions:

//Some actions here

INSERT INTO table1
                (table1_id, table1_val1, table1_val2, table1_val3)
              VALUES
                (gettablenewid('TABLE1'), val1, val2, val3);

INSERT INTO table1
                (table1_id, table1_val1, table1_val2, table1_val3)
              VALUES
                (gettablenewid('TABLE1'), val1, val2, val3);
INSERT INTO table1
                (table1_id, table1_val1, table1_val2, table1_val3)
              VALUES
                (gettablenewid('TABLE1'), val1, val2, val3);
//some other actions

Function gettablenewid code (id_table stores PKs for each table):

create or replace
    function        GetTableNewId(tablename in varchar2)
      return number is
      PRAGMA AUTONOMOUS_TRANSACTION;
      Result number;
    cursor c1 is SELECT ig.id_value+1 id_new
                              FROM id_table ig
                              WHERE ig.table_identifier = tablename
                              FOR UPDATE of  ig.id_value;
    begin
    for c1_rec in c1 loop
      UPDATE id_table ig
         SET ig.id_value = c1_rec.id_new
       WHERE current of c1 ;
       Result:=c1_rec.id_new;
    end loop;
    commit;
      return(Result);
    end GetTableNewId;

Occasionally insert statement fails with ORA-00001 for table1_id, I can't undestand why it happens.


texts: How should I select a primary key in my use case

texts:
I have an app wherein I want to give offers to certain devices which fall in range of IMEI numbers (I have a separate algo to calculate this range).
So I want to create a structure in which I have a column as IMEI and next column as status, which can be true or false.
Now for those devices which have more than one IMEI I am pretty confused how I should structure my database?
I am expecting transactions which can rise up to a million inserts per 5 hours once the API is released and in the above table my use case is that of transactional database, which helps me generate analytics.
Two solutions come to my mind:

Make a mix of IMEI numbers seperated by a hash and set it as primary key.
Flaw: The IMEI numbers I receive from the client side are not always guaranteed to be in the same order, so maybe I will have to use a like query on the primary key thus created, slowing my transactional database yet again.

I create a new record for each IMEI and set it as true. Which means upon selecting I have to run queries on multiple primary keys with a where clause.

Please advise


texts:
I am running merge replication in SQL2012 using web sync.

Has anybody done any experiments to compare the performance of web sync to connecting to replication using a straight TCP/IP connection?

Just wondering if it might work to open up a port for a TCP/IP connection over the web and not to use web sync. If it could work is it faster?


texts: Depending on Foreign Key Constraints to avoid deletion

texts:
In PostgreSQL, I have a 2 tables with father/parent relationship. I wish to delete the father record if no records of child are associated with it (using after triggers)

Currently, I have been doing something like

Get the count of records (having the same parent id as the current child)
If the count is 1, delete current child record and delete the parent record of it
If the count is greater than 1, just delete the child record (since more child's depend on the parent record)

I was wondering if I directly fire the delete statement without getting the count of parent record, like

Delete Child record
Delete Parent Record (shall throw a error if there are dependencies)

In this case if any child is associated with the parent (via a foreign key), it shall throw a error and not be deleted

Is the 2nd way right, to get this done, are such errors (foreign key constraints dependancies) logged in PostgreSQL


texts: purge worker, purge coordinator, shutdown handler stuck


texts: Cant get rid of filesort in simple query

texts:
I have two tables. One for texts and one to assign those texts to categories. I want to select texts of one specific category and order them by last change date. So, I am using this query:

SELECT
     t2.text_id
FROM
    texts_to_categories AS t1,
    texts AS t2
WHERE
    t1.category_id = 123
    AND t2.text_id = t1.text_id
ORDER BY
    t2.time_changed DESC
LIMIT 10

I tried a lot of index combinations but I keep getting a temporary and filesort:

id  select_type table   type    possible_keys                   key         key_len ref         rows    extra
1   SIMPLE      t1      ref     text_id,text_id_2,category_id   category_id 5       const       203     Using where; Using temporary; Using filesort
1   SIMPLE      t2      eq_ref  PRIMARY,text_id                 PRIMARY     4       text_id     1       Using where

Edit: Here is a CREATE TABLE for the two tables:

CREATE TABLE `texts` (
`text_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`time_changed` datetime DEFAULT NULL,
PRIMARY KEY (`text_id`),
KEY `time_changed` (`time_changed`)
) ENGINE=InnoDB AUTO_INCREMENT=132207 DEFAULT CHARSET=utf8

CREATE TABLE `texts_to_categories` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`text_id` int(11) DEFAULT NULL,
`category_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `text_id` (`text_id`,`category_id`),
KEY `text_id_2` (`text_id`),
KEY `category_id` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=740789 DEFAULT CHARSET=utf8


texts:
I am trying to react to a Sql Table change very quickly.

I have set-up a SqlDependency in c# and on receipt I query the table for "New" records.

My table contains a few thousand rows. There are 2 areas where delays slip in:

SqlDependency. On Average there is a 3 ms delay before I get notified.
Table Read. I select all rows from the table where a column contains the string "NEW". This table has max 5,000 rows. On average this "read" takes 4.5 ms.

So my minimum time to start acting on new data is 7.5 ms. This is quite long for my application.

Are there any tricks to improve this?

Additional

When I run this query in MS Studio with time statistics I get:

  SQL Server parse and compile time:     CPU time = 0 ms, elapsed time =
  0 ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
  time = 0 ms.

  (9 row(s) affected)

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms.

But If run it in c# like this I get average 1.67 ms.

private void button1_Click(object sender, EventArgs e)
{
    DataTable dt = null;

    SqlOMS2 sqlOMS2 = new SqlOMS2();

    Stopwatch sw = new Stopwatch();
    sw.Start();
    int num = 100;

    for (int i = 0; i < num; i++)
    {
        sqlOMS2.GetAnyNewOrders(out dt);
    }

    Console.WriteLine("Time taken: " + sw.ElapsedMilliseconds / (double)num);

    if (dt == null || dt.Rows.Count == 0)
    {

    }
}

public bool GetAnyNewOrders(out DataTable dt)
{
    dt = null;

    try
    {

        string commandText = "SELECT * FROM [" + DBName + "].[LB].[Orders] WHERE [AccountIdent] = 'LB:B' AND [Exchange] = 'FX' AND [Status] = 'Sent'  ORDER BY [OrderDate],[OrderUTC] DESC";

        SqlCommand cmd = new SqlCommand
        {
            Connection = conn,
            CommandText = commandText
        };

        cmd.ExecuteNonQuery();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        dt = new DataTable();
        ds.Reset();
        da.Fill(ds);
        dt = ds.Tables[0];

        if (dt.Rows.Count == 0)
            return false;

        return true;
    }
    catch (Exception)
    {
        return false;
    }
}


texts: How to force a delayed member to become primary in case of failure in mongodb

texts:
I have three member set [primary,secondary & arbiter] in my data center 1 [DC1]. Now i want a delayed member in data center 2 [DR]. In case DC 1 is completely down then my sub net application pointed will be redirected to DR. In that case :

a. How i will use DR mongodb as its a delayed member.

b. When the DC come back online then how i will sync the database from DR to DC.


texts: Oracle 11g http listener configuration for PL/SQL server pages

texts:
Could please someone point me how to configure oracle db to be able to display PL/SQL Server pages. I have successfully created and mapped dad with the DBMS_EPG.

Here is the listener:

# listener.ora Network Configuration File: D:\app\Ja\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 8080))
    )
  )

ADR_BASE_LISTENER = D:\app\Ja

When I want to enter

  localhost:8080/my_dad/home

I get error: No data received.

How do I solve this?


texts:
I am trying to restore several databases and I have no clue why some of them fail with the following error:

  Msg 3634, Level 16, State 1, Line 1
  The operating system returned the error '1244(The operation being requested was not performed because the user has not been authenticated.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'K:\Logs\MyDatabase.ldf'.

  Msg 3156, Level 16, State 8, Line 1
  File 'MyDatabase_log' cannot be restored to 'K:\Logs\MyDatabase.ldf'. Use WITH MOVE to identify a valid location for the file.

  Msg 3119, Level 16, State 1, Line 1
  Problems were identified while planning for the RESTORE statement. Previous messages provide details.

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

This is the SQL statement that I'm using

RESTORE DATABASE [MyDatabase]
FROM DISK = 'K:\Backups\MyDatabase_backup_2014_10_14_055006_4726051.bak'
WITH REPLACE,
MOVE 'MyDatabase_data' TO 'K:\Data\MyDatabase.mdf',
MOVE 'MyDatabase_log' TO 'K:\Logs\MyDatabase.ldf',
STATS = 10

As I said, some of them will work perfectly fine, while others will give me an error similar to the above. Unfortunately I can't find a pattern for the ones that fail / succeed.

If there was some sort of authentication issue, why does my SQL work on some but not on others? What am I missing?

LE: I had to give this user the sysadmin role for a completely unrelated issue. This may have also fixed this. I'll investigate and come back.


texts:
I have two databases that are in an availability group, one as the primary replica and the other is the secondary.  I have a store procedure that returns the following error.

DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Msg 2767, Level 16, State 1, Procedure xxxx.sys.sp_table_statistics2_rowset,
Line 105 [Batch Start Line 2]
Could not locate statistics '_WA_Sys_00000026_0143D405' in the system catalogs.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.

when I run the following query on my secondary replica(read-only):
SELECT OBJECT_NAME(s.object_id) AS object_name
    , COL_NAME(sc.object_id, sc.column_id) AS column_name
    , s.name AS statistics_name
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like  '%_WA_Sys_00000026_0143D405%'
ORDER BY s.name;

the stat appears:
STOJOU QTYPCU_0          _WA_Sys_00000026_0143D405
STOJOU QTYPCU_0          _WA_Sys_00000026_0143D405_readonly_database_statistics

when I run the query on the Primary replica it does not appear in the system tables but when you look under the table STOJOU it does appear.
How do I rebuild and/or resync the stats between my two systems?  I am unable to drop/or create the Stat on my primary.  Do I need to stop my synchronization between the primary and secondary to address this?    Thanks for your assistance.


texts: How is an idle SQL Server connection causing blocking?

texts:
I have a busy, transactional SQL Server (2012) which is having intermittent problems with blocking (every couple of days or so). I've been using extended events to capture blocked-process reports, but these show that the blocking-process seems to be idle, with the input buffer being use [_dummy].

The application connecting to this server uses multiple databases, so _dummy is a blank database which connection pools open connections to; the database context is switched as required.

What I thought was happening was that a query was timing out after obtaining locks, not rolling back the transaction and the connection was being released by the application back to the connection pool, which was resetting the database context before problems occurred due to the open transaction. To try and resolve that I changed the user connection settings so that XACT_ABORT is on by default, thinking that this would cause timed out transaction to rollback automatically, but this doesn't seem to have resolved the problem.

Is there anything else which could cause an idle connection (part of a .NET connection pool) to block other queries? Is there any way I can capture more information about the last query run on the blocking-process?

Note: I'm also monitoring deadlocks and the blocked-processes are sometimes blocked for several minutes but do not become deadlocks.


texts: MYSql - Ubuntu 20 - When importing DB, it gets duplicated in lowercase instead of writing to file

texts:
This is my first post here, I'm trying to import a DataBase.
I'm using Ubuntu with MySQL to run .net core app.
I've exported the Database in Windows MYSql WorkBench, uploaded the file to the server via FileZilla.
After creating the DB in MYSql, it appears as "WebApp3DB" after SHOW DATABASES; is run.
after importing the file with the command:
mysql -u root -p WebApp3DB < /home/ubuntu/netcoreapp/WebApp3DB.sql

The WebApp3DB db won't get updated with the tables and values, but a new DataBase is created with the name webapp3db (all lower case)and it has the tables and values inside.
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| WebApp3DB          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| webapp3db          |
+--------------------+

Being so .net won't recognize it.
Please advise.


texts: Is there any way that I could find how many tables has statistics on them,and how many tables which doesn't have statistics on them.


texts: charindex thrown off by extended characters

texts:
I've got a column with a filenames stored in a nvarchar(255). I'm trying to parse out the file extension using

reverse( left( reverse(filename), charindex('.', reverse(filename) ) -1 ) )

This works great for most of my records, however some are failing due to charindex returning 0 while a '.' is definitely part of the string.

I think I've isolated it to an issue with accented characters and a few others.

One of them I have is ité.pdf, when reversed, charindex is not finding the '.'

fdp.́eti

I've taken to converting it to it's hex representation to narrow down what's going on using

cast(reverse(filename) as varbinary(max))

0x6600640070002E000103650074006900

From what I can work out The '.' is 2E 00. 01 03 65 00 is the 'é' which is encoded differently than the others I have in the same column.

Since 2E 00 comes first I would still expect charindex to get a match, but it's returning me 0, which in turn make the call to LEFT fail.

Am I missing something obvious here? Converted to HEX it seems the '.' I am looking for is definitely there, but the character after is tripping charindex somehow.

EDIT: Simplified test

When outputting the string there is clearly a ., but charindex is not finding it?

select charindex('.', cast(0x6600640070002E000103650074006900 as nvarchar(max)))

Fiddle:
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=cda6f0a77fa9e206bff9217ec58be075


texts: How can I batch a drop column operation?

texts:
I have a BLOB column in my database schema that I no longer need. I need to drop this from my production server. I'm running MYSQL-5.6

Testing this on my development copy suggests that it will take about 1.5 hours to run. Right now, it's not feasible for us to bring the production DB down for that long for maintenance. What's the best way for me to do this in batches? e.g., I can acquire a write-lock on the table for about 5-10 mins at a time without causing any production failures.

I don't have a DB administrator background so I hope this isn't a really basic question covered elsewhere.


texts: Cannot drop the user because it does not exist or you do not have permission

texts:
I am getting this message for some users when executing sp_revokedbaccess procedure for database users. Users I am trying to remove are selected from sys.database_principals with type = S, W or G (sql server or windows users) and are not system users (principal_id > 4).

I checked if the user I can't remove owns any schema and it is not the case. The user is not an orphan user also. I am comparing a user that was removed and a user that gave me this error and can't find any differences. I do see the user in the database security folder so it does exist and has a corresponding server login (no sid mismatch).

Also, I am a sysadmin on a server level and should have permissions. I have been stuck with this for the last couple of days and can't solve it. I am not a DBA, just a developer and maybe there is something else I am missing. Please help.

Here is a screenshot of sys.database_principals. No problem with the first user (principal id = 7) but getting "Cannot drop the user...." with the second one (principal_id = 8):


texts: AlwaysOn Availability Groups go to Resolving status

texts:
We have a two-node cluster set up running SQL Server 2012 on Windows Server 2012 R2.  The base cluster consists of only those two nodes.  The quorum is set up as node + disk majority, with a shared disk sitting on an EqualLogic array connecting via iSCSI.  Cluster configuration validates with no errors.

Earlier today, the two Availability Groups running on the cluster (both primary on Node #1) went into a resolving state on both nodes.  Looking at the cluster events, there is nothing until it tries to restart the service.  Everything underneath the Windows Failover cluster shows green (online and no warnings) - disks, network interfaces, nodes, etc.

Looking at the application log in Windows Event Viewer, the event showing that the Availability Groups are entering the Resolving state have been requested to do so by the cluster because a quorum could not be established.  I cannot find anything else in any logs to support this, and the quorum passes during cluster validation.

The AGs do not recover.  We found in the AlwaysOn log in SQL Server logs that the first node had a mirroring endpoint failure.  This occurred immediately following a memory access violation with symptoms similar to a problem Microsoft has resolved with Cumulative Update 6, so we're going to try that next.

I was able to restart the SQL Server service on node 1 (the one with the memory access violations that the second note could not communicate with), and I was able to bring the availability group back online at that point.  A reboot also brings everything back to 100%.

Do you have any insight as to why that wouldn't have failed node 1 and kept the database alive on node 2 when the mirroring endpoint failed?  We're currently at AG level 3; would bringing that up to 4 trigger the failure on node 1 while leaving the service alive on node 2?


texts:
How to  List the room numbers that are ‘currently’ occupied at Holiday Inn Express.

I wrote the MySQL query ,but I don't get any output. Can anyone help to solve this?

SELECT r.rm_number
FROM   hotel h,
       reservation rr,
       room_booking b,
       room r
WHERE  rr.res_id = b.res_id
       AND b.rm_id = r.rm_id
       AND r.htl_id = h.htl_id
       AND h.htl_name = 'Holiday Inn Express'
       AND rr.res_checkin <= CURRENT_DATE
       AND rr.res_checkout >= CURRENT_DATE


texts: Sub query not working as expected trying to order by then group by, MySQL 5.5 vs 5.7

texts:
I do not know/understand DB's as well as I would like. I'm trying to ORDER BY and then GROUP BY so that I get the first record from each group of results in the database.

-- Example table (just an example. real table may contain hundreds of rows where field 2 is a duplicate but field 3 and 4 are different.)

CREATE TABLE IF NOT EXISTS `docs` (
  `field1` int(6) unsigned NOT NULL,
  `field2` int(6) unsigned NOT NULL,
  `field3` int(6) NOT NULL,
  `field4` int(6) NOT NULL,
  PRIMARY KEY (`field1`)
) DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`field1`, `field2`, `field3`, `field4`) VALUES
  ('27908', '82', '1', '17'),
  ('27907', '82', '1', '50'),
  ('402', '25', '1', '90'),
  ('312', '25', '10', '8');

If I ORDER BY the results are as expected. Note it works without sub-query.  I'm just breaking apart my original query:

SELECT * FROM
(
SELECT * FROM docs
ORDER BY field2, field1 DESC
)

Result (formmated with table generator):

+--------+--------+--------+--------+
| field1 | field2 | field3 | field4 |
+--------+--------+--------+--------+
|    402 |     25 |      1 |     90 |
|    312 |     25 |     10 |      8 |
|  27908 |     82 |      1 |     17 |
|  27907 |     82 |      1 |     50 |
+--------+--------+--------+--------+

The ORDER BY then GROUP BY works as expected in MySQL v5.5:

SELECT * FROM
(
SELECT * FROM docs
ORDER BY field2, field1 DESC
) temp
GROUP BY field2

Result:

+--------+--------+--------+--------+
| field1 | field2 | field3 | field4 |
+--------+--------+--------+--------+
|    402 |     25 |      1 |     90 |
|  27908 |     82 |      1 |     17 |
+--------+--------+--------+--------+

But it no longer works in v5.7 and results in error:

Query Error: Error: ER_WRONG_FIELD_WITH_GROUP: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'temp.field1' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I don't understand how to fix when using a sub-query or another way to write the query that works in v5.7. How can this query be changed to work in MySQL v5.7?


texts: What's the most efficient way to perform a case insensitive search for a word in a text field?

texts:
What is the most efficient way to perform a case insensitive search for a word in a TEXT (or CLOB) field?

I've been using WHERE LOWER(body) LIKE '%word%' but I think the calls to LOWER() might be slowing the query significantly.


texts:
This link clear some of the queries about dual table.

But now I am having following doubt is the execution of two queries on dual table:

Query 1

Select * from dual;

output:

D
-
X

Query2

Select NUll from dual;

output:

D
-

Does Dual contain default value as NULL? If yes why blank is not displayed, as in case of second query.


texts: Accessing Encrypted SQL Server database through a web-based application

texts:
I have secured a database within SQL Server Instance on my local machine using Netlib Encryptionizer. I can access the database by securing my SQL Server instance using the same password as encrypted database password otherwise it is unaccessible through SQL Server which is what i want.

However, I want a web-based application to access my encrypted database even when my SQL Server Instance is unsecured.

Can anyone help me with this??


texts: MySQL not using index on large WHERE IN clause

texts:
I am trying to retrieve chunks of records from my table via a generated SELECT WHERE IN statement. If the statement is small, it will use the index, if the statement is large (1000+ IN items) it performs a full table scan. The table is 17 million rows long, so this gets expensive when I only want to look for 10k records.

I have a unique index covering (in this order) date, world, player, city_id. I also have an index for the date and player. The table is also partitioned by world.

Example query:

SELECT * FROM mytable
WHERE
    (date, world, player, city_id)
IN (
    ('2017-05-30',4,'player',15466761),
    ('2017-05-30',4,'player',567599046),
    ('2017-05-30',4,'player2',34567866),
    ('2017-05-30',4,'player',2342467),
    ('2017-05-30',4,'player3',92341268),
    ... etc
)

If the query is small, it will use the index, if the query is large it performs a full table scan. Even adding FORCE INDEX (unique_index) doesn't do anything for the large queries.

Execution plan output:

 "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "20928166.60"
    },
    "table": {
      "table_name": "city_data",
      "partitions": [
        "w3",
        "w4",
        "w5",
        "w6",
        "w7"
      ],
      "access_type": "ALL",
      "possible_keys": [
        "unique_index"
      ],
      "rows_examined_per_scan": 17440138,
      "rows_produced_per_join": 8720069,
      "filtered": "50.00",
      "cost_info": {
        "read_cost": "19184152.80",
        "eval_cost": "1744013.80",
        "prefix_cost": "20928166.60",
        "data_read_per_join": "3G"
      }
    }

SHOW CREATE TABLE for city_data:

CREATE TABLE `city_data` (
   `date` date NOT NULL,
   `world` int(3) NOT NULL,
   `player` varchar(45) NOT NULL,
   `alliance` varchar(45) NOT NULL,
   `score` int(11) NOT NULL,
   `continent` int(11) NOT NULL,
   `castle` tinyint(1) NOT NULL,
   `water` tinyint(1) NOT NULL,
   `temple` tinyint(1) NOT NULL,
   `x_coord` int(11) NOT NULL,
   `y_coord` int(11) NOT NULL,
   `city_name` varchar(45) NOT NULL,
   `city_id` int(20) NOT NULL,
   `entry_id` int(55) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`entry_id`,`world`),
   UNIQUE KEY `unique_index` (`date`,`world`,`player`,`city_id`) USING BTREE,
   KEY `world_index` (`world`) USING BTREE,
   KEY `date_index` (`date`) USING BTREE,
   KEY `player_index` (`player`(8))
 ) ENGINE=MyISAM AUTO_INCREMENT=17999069 DEFAULT CHARSET=utf8
 /*!50100 PARTITION BY LIST (`world`)
 (PARTITION w3 VALUES IN (3) ENGINE = MyISAM,
  PARTITION w4 VALUES IN (4) ENGINE = MyISAM,
  PARTITION w5 VALUES IN (5) ENGINE = MyISAM,
  PARTITION w6 VALUES IN (6) ENGINE = MyISAM,
  PARTITION w7 VALUES IN (7) ENGINE = MyISAM) */

How can I get it to use the index instead of relying on a full table scan?

I have read this post: https://stackoverflow.com/questions/586381/mysql-not-using-indexes-with-where-in-clause


texts: Does an IOT guarantee order in a select?

texts:
We need to add a priority column to a table that gets hit about 250 times a second, approx 170 selects, 125 inserts and 60 updates. The column will be a simple number(1).

The priority does not matter for the inserts or updates i.e. not part of the primary key, which I'll enforce separately.

We basically don't want to have to do an order by over a range scan 170 times a second, as the number executed will drop massively.

Does an index organised table guarantee that priority = 1 will always come before priority = 9 when running the following query:

select *
  from my_table
 where rownum < 2

For slightly more context a typical query would be:

select *
  from my_table
 where mod(to_number(to_char(tstamp,'ss')),1) = 0
   and done is null
   and country = 'gbr'
   and rownum < 2

The pk constraint, for the IOT, would become priority, rest of the pk with a separate constraint on the pk solely for structure. done is null in approximately 99% of the table so this isn't very selective anyway.

Main index used is, I think, country, done, to_number(to_char(tstamp,'ss'), we tested about 20 combinations and this came up top by a long way.

I am completely unwilling to add any time at all to these queries, 0.01s a day added by select is 41 minutes a day. We'd much rather settle for "good enough" than perfection.


texts: Postgres window function to calculate summary position of orders

texts:
I tried windows functions, CTE, RECURSIVE but I still can't get my head around it

If I have rows of stock orders buy/sell and I want to know what was position after each trade.

If each next order is increasing existing position - I want to have average price,

If the order is decreasing - I want to calculate ProfitLoss and leave price of position as it was

If the order is changing direction of the position  - I want to calculate ProfitLoss and new price of position

order_profit_loss = LEAST(ABS(qua), lag(ABS(position_qua))) * price
position_price = (LAG(position_price) * LAG(position_qua) - order_profit_loss) / position_qua

DDL

CREATE TABLE trades
(
    id VARCHAR(255) PRIMARY KEY NOT NULL,
    symbol VARCHAR(10) NOT NULL,
    price NUMERIC NOT NULL,
    qua INTEGER NOT NULL,
    dt TIMESTAMP WITH TIME ZONE
);

INSERT INTO public.trades (id, symbol, price, qua, dt) VALUES ('1', 'FB', 124.89, 116, '2016-07-29 23:01:46.334000');
INSERT INTO public.trades (id, symbol, price, qua, dt) VALUES ('2', 'FB', 123.56, -104, '2016-07-30 23:41:13.297000');
INSERT INTO public.trades (id, symbol, price, qua, dt) VALUES ('3', 'FB', 125.23, 20, '2016-07-31 00:20:41.092000');
INSERT INTO public.trades (id, symbol, price, qua, dt) VALUES ('4', 'FB', 123.56, -21, '2016-08-01 12:25:34.482000');

How to get such outcome

id | symbol | price  | qua  |             dt             | order_profit_loss | position_qua | position_price
---+--------+--------+------+----------------------------+-------------------+--------------+----------------
1  | FB     | 124.89 |  116 | 2016-07-29 23:01:46.334+03 |                 0 |          116 |         124.89
2  | FB     | 123.56 | -104 | 2016-07-30 23:41:13.297+03 |           -138.32 |           12 |         124.89
3  | FB     | 125.23 |   20 | 2016-07-31 00:20:41.092+03 |                 0 |           32 |       125.1025
4  | FB     | 123.56 |  -21 | 2016-08-01 12:25:34.482+03 |          -32.3925 |           11 |       125.1025

Solution:

CREATE OR REPLACE VIEW v_pos AS
WITH RECURSIVE pos AS
  ( SELECT * FROM (
          SELECT DISTINCT ON (symbol)
               t.*,  0::numeric  AS pnl,
                     qua         AS pos,
                     price       AS p_price
           FROM public.trades AS t
           ORDER BY symbol, dt
         ) AS starting
    UNION ALL
    SELECT
        n.*,
        c.pnl,
        p.pos + n.qua,
        CASE
          WHEN p.pos > 0 AND n.qua > 0 THEN (p.p_price * p.pos + n.price * n.qua) / (p.pos + n.qua)
          WHEN p.pos < 0 AND n.qua < 0 THEN (p.p_price * p.pos + n.price * n.qua) / (p.pos + n.qua)
          WHEN p.pos / n.qua < 0 AND p.pos > n.qua THEN p.p_price
          WHEN p.pos / n.qua < 0 AND p.pos < n.qua THEN n.price
          ELSE 0
        END
    FROM
        pos AS p,
        LATERAL
        ( SELECT t.* FROM trades AS t
          WHERE t.symbol = p.symbol AND t.dt > p.dt ORDER BY t.dt  LIMIT 1
        ) AS n,
        LATERAL (
          SELECT
            CASE
              WHEN p.pos < 0 AND n.qua > 0 AND -p.pos > n.qua THEN n.qua * (p.p_price - n.price)
              WHEN p.pos < 0 AND n.qua > 0 AND -p.pos < n.qua THEN p.pos * (p.p_price - n.price)
              WHEN p.pos > 0 AND n.qua < 0 AND p.pos > -n.qua THEN n.qua * (p.p_price - n.price)
              WHEN p.pos > 0 AND n.qua < 0 AND p.pos < -n.qua THEN p.pos * (p.p_price - n.price)
              ELSE 0
            END pnl,
            p.pos * p.price cb
        ) AS c
  )
SELECT symbol, qua::BIGINT, price FROM (
  SELECT
    DISTINCT ON (symbol)
    symbol,
    pos qua,
    p_price price
  FROM pos
    WHERE qua != 0
  ORDER BY symbol, dt DESC
) as tmp
WHERE tmp.qua != 0


texts:
We have a rather big database (about 400'000'000 records) in roughly 100 tables.
The foreign keys in these tables are defined, so that No Action is taken, when deleting data from a table.

We now have to archive some of these data since only the last year is of any use (from the 9 years of data available).

We decided on a strategy to create a backup database with all the data inside and delete all data except the last year from the production database.

We're asking ourselves what is the best approach to this.

Should we change the existing foreign keys so that they cascade delete?
Are there any risks involved with this?
Should we delete table per table?
Is there a simple way to change all existing foreign keys so that they cascade delete?
Is there another even better option?

Thanks in advance


texts: Backup via mysqldump. Is this safe to do on production or will it crash the server?

texts:
I want to run this command during the time in which the production server is running the application:

mysqldump -u sbp -p databasename_production > databasename_development_copy.sql

Is this safe to do while production is running or no?

In short, development would be a lot easier if I had a copy of production data. Is this safe to do?

I'm doing this in digitalocean fwiw.


texts: How to use DBRefs to fetch records from multiple collections in MongoDB

texts:
I am working on a database for library system. I have a collection called 'books' and another collection called 'publishers'.

This is my documents structure in collection 'book'.

{
  "_id" : 1.0000000000000000,
  "book_name" : "C++",
  "book_details" : "This is book details"
},
{
  "_id" : 2.0000000000000000,
  "book_name" : "PHP",
  "book_details" : "This is book details"
},
{
  "_id" : 3.0000000000000000,
  "book_name" : "JAVA",
  "book_details" : "This is book details"
}

This is my documents structure in collection 'pulishers'.

{
    "_id" : 1.0000000000000000,
    "pub_name" : "Sky Publishers",
    "pub_details" : "This is publisher details",
    "published_books" : [
        {
            "$ref" : "books",
            "$id" : 1.0000000000000000
        },
        {
            "$ref" : "books",
            "$id" : 2.0000000000000000
        }
    ]
}

Now, my question is how to write a query in mongodb to show books details which are published by this publisher.


texts: How to reduce database size with BLOB fields

texts:
I have a database that's used to store fingerprints. Under my conception this should be a small database but the problem is that the original developer was probably tripping on peyote and decided it would be a great idea to store the fingerprints in BLOB fields.

Thanks to this magic trick the database is now 3 GB in size and the frontend its crashing all the time, no wonder since a simple SELECT TOP(1000) takes about a minute from MSSMS.

So I'm left with a doubt about how to proceed. Is there a way I could reduce the size of the table containing the pictures? Or maybe another alternative to this madness.

Regards.


texts:
I have a table with millions of rows and something like 20 columns. This table have an unique identifier as primary key, but the most likely query is based on three varchar columns (col1 and col2 of type nvarchar(35) and col3 of type nvarchar(14)). For a given {col1, col2, col3} correspond at maximum 1 row. col3 is the less fragmented column. In the vast majority of cases, the table will be stressed with query like this:

SELECT * FROM table
    WHERE col1 = "xxx0" AND col2 = "yyy0" AND col3 = "zzz0"
    OR col1 = "xxx1" AND col2 = "yyy1" AND col3 = "zzz1"
    ...
    OR col1 = "xxxn" AND col2 = "yyyn" AND col3 = "zzzn"

Given this situation? What will be a good indexing strategy? Multi-column with col3 as first column? Three separated indexes?


texts:
I'm using Toad for MySQL, and I have to make a trigger so that:

When the rent of a car is <=15 and >= 25, I insert a letter "A" in the field category
When the rent is > 25 and <= 35, insert the letter "B"
When the rent is >35 and <= 50, insert the letter "C"

Here is my table:

CREATE TABLE `automoviles` (
  `id_automoviles` int(11) NOT NULL AUTO_INCREMENT,
  `tipo_automovil` varchar(25) NOT NULL,
  `rentamiento` decimal(5,2) NOT NULL,
  `marca` varchar(25) DEFAULT NULL,
  `año` int(11) NOT NULL,
  `estado` varchar(10) DEFAULT NULL,
  `categoria` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`id_automoviles`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

I have the trigger this way, but it gives me an error:

DELIMITER $$
CREATE TRIGGER categoria AFTER INSERT on automoviles
  FOR EACH ROW
  BEGIN
    IF (old.rentemiento >=15 and old.rentamiento <= 25)
    THEN
    UPDATE autoviles
       set new.categoria = 'a'

    or if(old.rentamiento >=25 and old.rentamiento <=35)
    THEN
    UPDATE automoviles set new.categoria = "b"

    else if (olD.rentamiennto >=35 and old.rentamiento <=50)
    THEN UPDATE automoviles set new.categoria = "c"

and here is the error!

  ERROR! There is no OLD row in on INSERT trigger


texts: Understanding row estimation for timestamp in postgresql

texts:
PostgreSQL 9.4

I have a table (call it tbl) that's got the column registration_date timestamp. Now, for simplicity I set the following statistic parameter:

ALTER TABLE tbl ALTER COLUMN registration_date SET STATISTICS 2;
ANALYZE tbl;

and than I've got the following statistic for the table (from pg_stats)

avg_width n_distinct  most_common_vals most_common_freqs
    8         -1             ""               ""
                         histogram_bounds
 2012-03-26 10:32:15.379,2013-11-05 19:33:09.828,2015-11-19 14:39:04.676

And the query

SELECT reltuples,relpages FROM pg_class WHERE relname = 'tbl'

returns this:

reltuples relpages
 240656     2476

Now, I execute the query:

EXPLAIN ANALYZE SELECT *
FROM tbl
WHERE tbl.registration_date > '2014-11-11' -- 2014-11-11 is not in histgrom_bounds

And got the following ananlyzed plan:

Seq Scan on tbl  (cost=0.00..5484.20 rows=60441 width=47) (actual time=3.872..26.406 rows=604 loops=1)
  Filter: (registration_date > '2014-11-11 00:00:00'::timestamp without time zone)
  Rows Removed by Filter: 240052
Planning time: 0.119 ms
Execution time: 26.439 ms

QUESTION: How is the estimated number of rows computed in my case? I don't understand the 60441 rows count in the plan. Note that

SELECT reltuples/4 FROM pg_class WHERE relname = 'tbl'

returns 60164 which is less than 60441.


texts:
This question is regarding the PDO_SQLSVR driver for PHP and Microsoft SQL Server and how it interacts with the database, whether via sp_prepare (prepare statement execution) or sp_execute (direct statement  execute).
By default the driver uses sp_prepare but swapped it out after reading the Why sp_prepare Isn’t as “Good” as sp_executesql for Performance post on Brent Ozar's blog by Erik Darling, which states that sp_execute is a better choice for reasons listed in the article.
However, this conflicts with Microsoft's recommendations of sp_prepare being better.
Does anyone have experience with either and maybe have some recommendations what approach worked better for them?


texts: What preventive measures can be taken to get over this scenario with minimal data loss

texts:
I am SQL server dba and came across weird scenario our cluster which had 10 nodes 5 primary 5 secondary each nodes had SQL role.... example Ams1pd11 to Ams1pd15 are primary.. and Ams3 side from pd11 to of 15 was secondary... in this scenario whole cluster behaved abnormally going 2 nodes down and all nodes availability groups were inaccessible leading to multicustomer outage.

Explanation of the real-time scenario as it started when I was in shift....

Ams1pd12 got down which was hosting primary server A
So automatically the role A was failed over on best possible node and he choose Ams1pd11.

But Ams1pd11 already had one role on it e.g B
Now Ams1pd11 was hosting both A and B as Ams1pd12 got down

As both primary roles were on one node it was a risk so to balance I failed over B node to one of secondary node Ams3 side.. now it was balanced and I was just about to investigate on Ams1pd12 why it got down and all...

But suddenly Ams1pd11 node also went down and the role was not failed over and stucked over there...

So now 2 nodes out of 10 were down and one role was stuck so the customers on that role were impacted....

We were troubleshooting with Microsoft for same and noticed that other nodes were showing up by the availability  group for alll the nodes were stuck and they were not opening and stucked in expanding state....

So this way all the nodes on that cluster were impacted and as due to this our backups stopped..
There was data loss...

The one stucked role and customer on that node faced only 15 mins data loss as the service was down fr us and them too at the same time, but

The nodes which were showing up and AG groups were inaccessible weird thing was for the already logged in users they were able to change modify the data... It was only refusing new connections... But old connection were still active...

So if issue started and backup stopped at 7 am most of the customers were able to access the database till 6pm so there was 11 hours of data loss....

And it took lot of efforts to restore databases manually...
Those online nodes were easy to recover as we just had to attach the dbs as we migrated data and log files bt for stucked role databases we had to manually recover them.

Please suggest the strategies which will be best to be followed in this type of disaster how we can obtain speedy recovery.


texts: How do I restore a plain text postgres .backup file

texts: I exported a postgres db from pgAdmin in a plain format because I wanted to search the whole db text. I then tried to restore the database back but it gives an error saying that the archive file is not valid archive file. How do I restore the db back if it can be restored?


texts: Why is vacuum at a critical level in PostgreSQL when I'm running autovacuum?

texts:
I just checked the state of vacuum in my PostgreSQL database and it's at a critical level:

$ check_postgres --action=last_vacuum
POSTGRES_LAST_VACUUM CRITICAL: DB "postgres" DB: postgres TABLE: pg_catalog.pg_shdepend: 09:22 August 12, 2013 (40 weeks 2 days 4 hours 9 minutes) | time=0.52s postgres.pg_catalog.pg_shdepend=24379780s;86400;172800

but I am running autovacuum. Why would this happen?


texts: How to find out the activity which increasing the response time of the Sql Server?

texts:
We are using SQL Server 2008 as our database server. Recently we came to know, by using a 3rd party tool, that the response time of our database server is increasing at a specific time for example at night 1AM to 4AM. That 3rd party tool provides no information about the activity which is causing this.

Some Facts:

We are using SQL Server 2008.
We have scheduled some SQL Jobs but their execution time is different; not in the range where the response time is high.

I hope my question is clear enough if not please let me know.


texts: SQL Server Replication: Is there a way to know which articles are being used in a replicated database?

texts:
I've come into a situation where there are about 1000+ tables being replicated to a database.  When it was created, I think the creators just put a bunch of tables they thought would be needed.  I'd like to reduce the number of tables being replicated to only the ones needed.  Are there any good ways to figure out which tables are needed in replication?
Thank you for your help.