texts:
I am re-designing our company database, and am wondering whether or not to split this table up into two (or even three) different tables based on the type field. Prospects can become clients, and in rare cases, clients can revert to being prospects again (usually correction of a user error). Currently there are no suppliers who are clients, but theoretically there's nothing to stop that from happening in the future. As you can see, several fields are only needed in one case or another.

CREATE TABLE `companies` (
  /* book-keeping fields every data table should have */
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `created` TIMESTAMP NULL DEFAULT NULL, /* would love to specify DEFAULT CURRENT_TIMESTAMP here but MySQL won't let me */
  `modified` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  /* fields for all company types */
  `type` ENUM('prospect','client','supplier') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'prospect',
  `name` VARCHAR(25) DEFAULT NULL,
  `abbr_name` VARCHAR(8) DEFAULT NULL,
  `formal_name` VARCHAR(255) DEFAULT NULL,
  `web_site` VARCHAR(255) DEFAULT NULL,
  `email` VARCHAR(320) DEFAULT NULL,
  `logo` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `key` ENUM('false','true') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'false',
  `bust` ENUM('false','true') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'false',
  `head_office` INT UNSIGNED DEFAULT NULL,
  /* only used for clients */
  `accounting_code` VARCHAR(5) CHARACTER SET ascii DEFAULT NULL,
  /* used for prospects, and clients obtained by marketing */
  `marketing_user` INT UNSIGNED DEFAULT NULL,
  `marketing_datasource` CHAR(2) CHARACTER SET ascii DEFAULT NULL,
  /* only used for prospects */
  `marketing_dead` ENUM('false','true') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'false',
  /* only used for suppliers */
  `supplies` SET('materials','plant') CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `account_holding_branch` INT UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`head_office`) REFERENCES sites (`id`),
  FOREIGN KEY (`marketing_user`) REFERENCES users (`id`),
  FOREIGN KEY (`account_holding_branch`) REFERENCES sites (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

What do people recommend, and importantly, why?

A similar question, Splitting a table based on business logic provides one curt answer with two relevant reasons to help decide whether splitting is worth it:

Partition hot and cold data for cache efficiency
Many columns almost always null

As to data 'temperature', key clients and key suppliers are hotter than other clients or suppliers, and hotter than prospects. Any company which has gone bust or for which the marketing trail has died are pretty frigid.

As to NULL, it varies, but prospects almost always do not have a logo uploaded, and suppliers presently do not have data in any of the four accounting_code and marketing_* fields, though as I say there's no technical reason why a parts supplier could not also become a client of our business.


texts: Uninstalling SQL Server 2012, No option to select instance

texts: Hi I am currently trying to completly remove sql server 2012 from my machine, when I click uninstall from the control panel then > remove, I have no option to select and instance to remove and i've no idea why.


texts:
I have a problem in modeling a database that contains orders and invoices. Actually it's more of a problem from a business point of view, but it's related to modeling the database nonetheless.

Model of the Orders table:

Now I had another column called payment_deadline, that well had the payment deadline. What I'm thinking about is, what info should my Orders table have and what info should my Invoices table have?

Which one has Employee and Customer id? Which one has the prices? Which one has the payment info? Which one has the deadline info? I'm totally lost.

I understand that in the business world, an order signals that the provider of the goods has to act, and the invoice represents that the goods provider has fulfilled his part and the customer now has to pay for the goods/services. But I'm lost as to how model it in the database. Any insight is much appreciated.


texts: Is it a good practice to use index hints in production code?

texts:
I came across a situation where one of my MySQL (5.6) views (type - MERGE) was performing quite bad in order to fetch records (~3500 rows) for an entity id say 123.
This view performs inner joins across a few tables in order to get these records.
Surprisingly, the view returns results in ~200ms on my local docker MySQL container but takes ~10s on an AWS MySQL RDS instance.
On viewing the query plans in both environments, I found that the join order was different because the index utilised for one of the tables in the join was different (unique index is used by RDS but PRIMARY by docker mysql).
I tried a few things such as rebuilding indexes and rebuilding table stats using the analyse command on all concerned tables but all in vain. The connection collation is also the same as the table collations. The DB engine is InnoDB.
Moreover, the data on both environments is also identical as they have been setup using the same dump.
I have also verified that the indexes are the same in both environments.
The only way I can see running the query optimally on RDS is by using an index hint and that performs as expected.
In such a case, is it okay to use index hints in production code?
Or do we have any suggestions on how to troubleshoot this issue?


texts: How to conditionally stop a psql script (based on a variable value)?

texts:
Let's consider the following example (from the start of a psql script):

\c :db_to_run_on

TRUNCATE the_most_important_table;
-- tried to avoid similarities to anything that exists out there

Now if it is run this by the command

psql [connection details] -v db_to_run_on=\'dev_database\'

then it just runs and the user is happy.  But what if (s)he decides to specify -v db_to_run_on=production_database?  (Let's assume that this can happen, just like people run rm -rf / # don't try this at home!!! ocassionally.)  Hopefully there is a fresh backup of that table...

So the question arises: how to check the variables passed to a script and stop further processing based on their value?


texts:
I have a DB server 192.168.X.X, Now due to some Issue with the domain user we are unable to get the RDP of that server. but we can get the DB connected from other server which is there on the network.
Now I want to restore the database on 192.168.X.X, so Is it possible to restore the DB without taking the RDP of the server.
Any help will be appreciated.
I tried this step but with no luck:  Link
I tried to Restore the DB with T-SQL code as well:
Use master

RESTORE DATABASE TEST
FROM DISK = '\\192.168.X.X\New folder.bak'

But it gives me an error.
Msg 3201, Level 16, State 2, Line 3
Cannot open backup device '\192.168.X.X\New folder.bak'. Operating system error 1326(The user name or password is incorrect.).
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.


texts: Update column type when materialized views depend on the column?

texts:
I'm working in Postgres 9.4. I want to alter the type of a column in a table:

ALTER TABLE mytable ALTER COLUMN quantity TYPE integer;

But I get this:

ERROR:  cannot alter type of a column used by a view or rule

This is because there are materialized views derived from that table.

Is there a way to temporarily disconnect the materialized views from this table so that I can update this table, then refresh the materialized views? (or if necessary, refresh them separately)

Or do I absolutely have to delete all the materialized views before I can change the column type?

UPDATE: It took three days to build the materialized views, and will take as long to rebuild them - which is why I'm asking this question. It's not trivial just to delete and recreate them.


texts: Why is the ORDER BY clause in a view ignored as soon as it's called with a WHERE claue?

texts:
I have a view that joins several tables, just to simplify a frequently called query. This view has an ORDER BY clause on a calculated field (the order will never be different). (on SQL Server Express 2008 R2).

The basic idea is like this (simplified):

SELECT [EventType].Name,
    [EventType].TotalOccurrences,
    [Session].ID,
    [Session].TotalOccurrences,
    [Session].TotalOccurrences / [EventType].TotalOccurrences AS saturation
FROM [EventType]
    INNER JOIN [Session] ON [EventType].ID = [Session].Event
ORDER BY saturation

This view will never be called without a where clause though (which in my case would almost return the entire database). It will always be with WHERE [EventType].ID = x. However as soon as I add the where clause, the ORDER BY clause is ignored! Without the where clause it is working as expected.

Actually a different question but I'm throwing it in here while I'm at it, is there a better way to retrieve such info? The [EventType].Name and [EventType].TotalOccurrences will be identical for all returned rows, which is a small waist of network bandwidth. Not that that is a problem, but I'm wondering if anything exists to tackle that without the latency overhead of multiple round-trips to the database?

[Edit] The above sample was over-simplified, the calculated field has other factors so that it would return a different order than simply ORDER BY [Session].TotalOccurrences as it would in this example, but the principle should be clear.

[Edit2] From the answer I come to the conclusion that the Graphical Designer has more features than the underlying database:


texts:
I have some data in a DB, which I'm trying to get out into a spreadsheet programmatically.  The data is a dollar value for a number of categories.  Each fortnight, each category has a value added.

The data looks like this (simplified):

PayDay      Category        Average
2016-12-01  Entertainment   $32.32
2016-12-01  Extra Expenses  $39.18
2016-12-01  Shopping        $591.13
2016-12-15  Entertainment   $34.34
2016-12-15  Extra Expenses  $42.73
2016-12-15  Shopping        $577.69
2016-12-29  Entertainment   $34.34
2016-12-29  Extra Expenses  $53.22
2016-12-29  Shopping        $580.08

But I want to be able to select directly from the DB as:

PayDay      Entertainment   Extra Expenses  Shopping
2016-12-01  $32.32          $39.18          $591.13
2016-12-15  $34.34          $42.73          $577.69
2016-12-29  $34.34          $53.22          $580.08

Been trying, but I can't get my head around it.


texts:
A brief history.  I have an oracle sql query which I'm looking to pull data when a certain preference is enabled.  In this query I had a date range to filter the data to a specific range.  This was changed due to security of the service account having access to specific tables.  My DBA suggested a view.  This query is for this view, but currently takes several minutes to populate.  Any suggestions on how to optimize this query?

SELECT
(SELECT o.SALESFORCE_ID from org_unit o WHERE o.ORG_UNIT_ID = a.vendor_id AND o.delete_flag = '0') as SenderId,
(SELECT o.SALESFORCE_ID from org_unit o WHERE o.ORG_UNIT_ID = a.CUSTOMER_ID AND o.delete_flag = '0') as ReceiverId,
a.invoice_id,
a.INVOICE_DATETIME
FROM invoice a
WHERE a.invoice_id IN (
    SELECT document_id
    FROM oi_action oi
    WHERE oi.action = 'Submit'
    and oi.status='Submitted'
    )
and a.sub_type not in ( 10008,10007)
and EXISTS (
    SELECT 1
    FROM org_preference op
    WHERE op.ORG_UNIT_ID = a.customer_id
    AND op.PREFERENCE_TYPE_ID = 60276
    AND op.preference_value = 1
    AND op.modified_by_id IS NULL
    )
AND EXISTS (
    SELECT 1
    FROM org_unit o
    WHERE o.ORG_UNIT_ID = a.vendor_id
    AND o.delete_flag = '0'
    AND o.salesforce_id IS NOT NULL
)

GROUP BY a.vendor_id, a.customer_id, a.invoice_id, a.invoice_datetime;

If there is a way to only display the view when the preference is turned on
example:

AND a.CREATED_DATETIME >= (SELECT o.CREATED_DATETIME
FROM org_unit o
WHERE o.ORG_UNIT_ID = a.vendor_id
AND o.delete_flag = '0'
AND o.salesforce_id IS NOT NULL
)

Any thoughts suggestions on the best route I can take?

Here is the Explain plan:

UPDATE:

Here is my new query which displays MUCH faster, within a second:

SELECT
sender.salesforce_id as SenderId,
buyer.salesforce_id as ReceiverId,
a.invoice_id,
a.INVOICE_DATETIME
FROM invoice a
INNER JOIN org_unit sender on sender.org_unit_id = a.vendor_id
INNER JOIN org_unit buyer on buyer.org_unit_id = a.CUSTOMER_ID
WHERE EXISTS (
    SELECT 1
    FROM oi_action oi
    WHERE oi.document_id = a.invoice_id
    AND oi.action = 'Submit'
    AND oi.status='Submitted'
    )
and a.sub_type not in ( 10008,10007)
and EXISTS (
    SELECT 1
    FROM org_preference op
    WHERE op.ORG_UNIT_ID = a.customer_id
    AND op.PREFERENCE_TYPE_ID = 60276
    AND op.preference_value = 1
    AND op.modified_by_id IS NULL
    )
AND EXISTS (
    SELECT 1
    FROM org_unit o
    WHERE o.ORG_UNIT_ID = a.vendor_id
    AND o.delete_flag = '0'
    AND o.salesforce_id IS NOT NULL
);

On the explain plan the only difference is in the Nested Loops from 20944 to 15365.

If this looks okay I think this has solved my problem


texts: Comparing Query Accomplishment Speed By Using JOIN or not

texts:
I have been searching about this, and still didn't get satisfying answer. My problem is, I have 2 tables, for example table A and table B.

Table A consists of: id (pk), name, address
Table B consists of: b_id (pk), id(fk - from table A), tools

The relationship between these 2 tables is one to many. So each row of A could have more than one items in table B.

Commonly, I always use this way:

I select one row from table A

SELECT * FROM A WHERE id = 1

Then I select more data from table B

SELECT * FROM B WHERE b.id = 1

Is this way effective? Is that better than using JOIN, like

SELECT A.id, A.name, A.address, B.b_id, B.tools
FROM a
  LEFT JOIN b ON a.id = b.id
WHERE a.id = 1

should I better use JOIN instead?


texts: MySQL Update Ignore not working - errors and does not update all rows

texts:
I am trying to run an update on a table which I know will cause some duplicate key errors. I am therefore using UPDATE IGNORE as I understood this would ignore these errors and still update the rest of the table, but this is not the case - it just fails with an error:

[SQL] UPDATE IGNORE `activity-customer` set `customerid` = 12345 where `customerid` = 56789

[Err] 1062 - Duplicate entry '12345-180221' for key 'PRIMARY'

SQL_MODE is not set to STRICT (it is empty)

This is using MySQL 5.5.47.

I'm very confused! Any help appreciated!


texts:
After doing rs.initialize() on two repica instances (aws ubuntu 16.04) and mongo 4.0.1 I get the following SegFault which I assume happens during replicaset recovery. Is it a mongo 4.0 known issue?

Thank you in advance

Stack trace is as follows :

W NETWORK  [LogicalSessionCacheReap] Unable to reach primary for set rs0
F -        [rsSync-0] Invalid access at address: 0x18
F -        [rsSync-0] Got signal: 11 (Segmentation fault).

 NETWORK  [LogicalSessionCacheReap] Unable to reach primary for set rs0
F -        [rsSync-0] Invalid access at address: 0x18
F -        [rsSync-0] Got signal: 11 (Segmentation fault).

 mongod(_ZN5mongo15printStackTraceERSo+0x41) [0x55cc778e0051]
 mongod(+0x237D269) [0x55cc778df269]
 mongod(+0x237D8D6) [0x55cc778df8d6]
 libpthread.so.0(+0x11390) [0x7f3e2d4b6390]
 libpthread.so.0(pthread_mutex_lock+0x4) [0x7f3e2d4aed44]
 mongod(_ZN5mongo17FreeMonController8_enqueueESt10shared_ptrINS_14FreeMonMessageEE+0x40) [0x55cc763cb6b0]
 mongod(_ZN5mongo17FreeMonController27notifyOnTransitionToPrimaryEv+0x8A) [0x55cc763cb98a]
 mongod(_ZN5mongo4repl39ReplicationCoordinatorExternalStateImpl34_shardingOnTransitionToPrimaryHookEPNS_16OperationContextE+0x2C6) [0x55cc76219a36]
 mongod(_ZN5mongo4repl39ReplicationCoordinatorExternalStateImpl21onTransitionToPrimaryEPNS_16OperationContextEb+0xF3) [0x55cc7621c1d3]
 mongod(_ZN5mongo4repl26ReplicationCoordinatorImpl19signalDrainCompleteEPNS_16OperationContextEx+0x1D9) [0x55cc7625e9d9]
 mongod(_ZN5mongo4repl8SyncTail17_oplogApplicationEPNS0_11OplogBufferEPNS0_22ReplicationCoordinatorEPNS1_14OpQueueBatcherE+0xC48) [0x55cc76306738]
 mongod(_ZN5mongo4repl8SyncTail16oplogApplicationEPNS0_11OplogBufferEPNS0_22ReplicationCoordinatorE+0x189) [0x55cc76307209]
 mongod(+0xD95756) [0x55cc762f7756]
 mongod(_ZN5mongo8executor22ThreadPoolTaskExecutor11runCallbackESt10shared_ptrINS1_13CallbackStateEE+0x1B3) [0x55cc77163b43]
 mongod(+0x1C0222B) [0x55cc7716422b]
 mongod(_ZN5mongo10ThreadPool10_doOneTaskEPSt11unique_lockISt5mutexE+0x14C) [0x55cc770d649c]
 mongod(_ZN5mongo10ThreadPool13_consumeTasksEv+0xBC) [0x55cc770d699c]
 mongod(_ZN5mongo10ThreadPool17_workerThreadBodyEPS0_RKNSt7__cxx1112basic_stringIcSt11char_traitsIcESaIcEEE+0x96) [0x55cc770d7386]
 mongod(+0x248D600) [0x55cc779ef600]
 libpthread.so.0(+0x76BA) [0x7f3e2d4ac6ba]
 libc.so.6(clone+0x6D) [0x7f3e2d1e241d]


texts:
Imagine you have a meal service and everyday there is a different meal which customers can order.
Mon, Dec 12: Pasta
Tue, Dec 13: Pizza
Wed, Dec 14: Gnocci

Orders are possible until 9am.
This mean Monday on 8am customers may order Pasta for today, at 9:15am they are no longer allowed to order for today.
How to handle this is with PostgreSQL.
I see two ways:

Create a cron job which switches the column "orderable" to False.
Create a view which calculates the field "orderable" on the fly.

Is there a feasible third or forth way?
Which way is simple and robust?


texts: Merge replication between PostgreSQL  SQLite

texts:
In my app, the database (Postgres) consists of various tables referencing each other in one-to-many and many-to-many relationships, organized in subtrees. Each data subtree has a single parent row at the root.
Mobile clients can connect to the DB and "check out" a subtree, saving it to a local database (SQLite), with the same table structure as the server. The mobile client makes UPDATE/INSERT/DELETE changes, often while offline. Then, those changes must be synced with the original data on the server.
I can handle deletes by setting a flag, instead of actually deleting. Handling updates should also be simple. I can ensure that no two clients check out the same subtree at the same time, so there can be no conflicts.
However, I am confused about how to handle new data being created on the client via INSERT. Because the mobile clients only have a slice of the complete data, new row ids on the client will not match row ids on the server, nor will they match the id sequence. Furthermore, if the client updates rows that were originally also created on the client, the updates must map to the new row ids on the server.
The fact that there are many-to-many relationships to keep track of (some created on the client) makes the situation even more confusing.
How should I handle the client-to-server-to-client sync? I'm sure this problem has already been solved elsewhere - is there specific terminology I can look up to see how this was done? ("merge replication", etc?)
Many thanks for your help.


texts: Postgres: Getting the "partition number" instead of row_number

texts:
Figure a table with the following data, how to exclude 'alone' values?

id; data
(1, 'foo'),
(2, 'foo'),
(3, 'foo'),
(4, 'bar'),
(5, 'bar');
(6, 'jak');

I try with this:

select id, data, row_number() over (partition by data)
from t;

 -- RESULT
id  data    row_number
4   bar     1
5   bar     2
1   foo     1
2   foo     2
3   foo     3
6   jak     1

 -- EXPECTED (exclude partition with one row)
id  data    row_number
4   bar     1
5   bar     2
1   foo     1
2   foo     2
3   foo     3

row_number() gives an index to rows in each partition.

How to have the number of rows by partition? Some kind of WHERE partition_count > 1.

fiddle


texts:
If my MySQL master has heavy writes, my read replicas will in-turn gets bogged down by the replication logs & executing them. Then could it serve the reads?

Assume MYSQL master is executing at 100% CPU and disk utilization, now these needs to be executed in the read replicas too. So in turn read replicas would also incur 100% CPU and disk utilization. Then how could it serve the read requests?

Note: Assume the MYSQL master write is just updating a field Ex: UPDATE where ID = <>. No more complex queries so there is no possibility of optimizing in read replicas too.


texts: SQL Server Transaction Replication on different versions of Windows

texts:
Currently we use SQL Server 2014 Transaction Replication in Windows Server 2012 (both Publisher and Subscriber are on different servers with the same operating system).

We need to add a new subscriber, and my question is this: do we need to have the same operating system for the newly added subscriber, i.e. Windows Server 2012, or can I use the Windows 7 operating system.

Note : Both Publisher and Subscriber are in two different domains.


texts: SQL server 2012 service pack installation

texts:
which service pack I should apply first after installing sql server 2012 enterprise edition either sp1 or sp2 ?

Thanks in advance.


texts: How do I record SQL transactions with SQL Server Express

texts:
With a test migration from 2000 to 2008R2 Express, one function from the client application fails silently. I'd like to find out what SQL is running and why it fails.

I think I would have attacked this with the profiler in 2000 (not sure)? However this tool is absent from the Express Edition.

Is it possible to do what I want without any tool, eg just with T-SQL, and how would I go about it?


texts: XML load fails on postgresql 9.1, but not in 9.3, any workaround?

texts:
I have a problem loading an xml on an xml field of a table.
The load is done by ruby code, but this is not the point.
Everything worked well for years, but xml is increasing size....

So one day, today, xml is near 12 megabytes. Sysadmin called dba (me): the service is not working!

I did some test. Result is: when I test this query:

SELECT * FROM XMLPARSE(DOCUMENT convert_from(pg_read_binary_file('xmlstuff.xml'), 'UTF8'))

It works fine on Postgresql 9.3
It gives me this in 9.1 in production or test:

ERROR:  invalid XML document
DETAIL:  Entity: line 1: parser error : internal error

Entity: line 1: parser error : Extra content at the end of the document

Any workaround for this? I'm planning an upgrade in the next months, but I have the problem now :-(


texts: Should all machines which run mongo instances (mongod or mongos) in a Mongo DB cluster have unique hostnames (also at OS level)?

texts:
I have been using Mongo DB Cluster for some data analysis (Mongo DB version 2.4.5) and while inserting some data into the cluster, I found an error which is the same as mentioned in the bug : https://jira.mongodb.org/browse/SERVER-10578

My configuration is as follows:

1) Node1 - mongoconfig server, mongod shard

2) Node2 - mongoconfig server, mongod shard

3) Node3 - mongoconfig server, mongos

4) Node4 - mongos

I changed the OS hostnames of all the machines and made sure that hostnames specified in the DNS server and OS hostnames are the same. After that, the problem got solved.

But now I have a requirement that I need to add mongos instances (nodes/machines) on the fly which will have different IP addresses but the same hostname (both at the OS level and in DNS entries). I will still continue to have unique hostnames (both at OS level and in DNS entries) for all the mongo config servers and mongo shard servers.

Will that work correctly or will I run across the same problem as mentioned in the bug?

Does the requirement of unique hostnames at OS level hold true even for mongos instances or is it just for shards?


texts:
We will be switching from mysql cluster to either postgresql or oracle (with RAC). Mysql cluster is quite reliable, but lacks features in SQL that make programmers life easier (they consider it as datastore, not database;). I know there are multiple possibilities how to achieve redundancy in pgsql but I think none of them is reliable as mysql cluster. So

1) Is there any good conceptual design that could offer reliable pgsql cluster - reliability, data corruption prevention, high availability, no SPOF, load balancing and access to DB through one (virtual?) IP address?

2) Do you know some companies that are able to design and deploy the cluster?


texts: Transpose multiple columns into 2 column and single row to multiple row in sql

texts:
Using Select top 1 statement I have to retrieve data from an 8 column table without any unique key.  Now I want to transpose this table into two columns: original column name to the first and its respective value to second.

An original table like this:

Column1 | Column2 | Column3 | Column4
55      | 108     | 555     | 85

And the expected result:

ColumnName | Value
Column1    | 55
Column2    | 108
Column3    | 555
Column4    | 85


texts: Cannot connect to the AGListener name when in SSMS on the passive node

texts: We have a two node cluster built on Windows Server 2012 R2 with SQL Server 2012. This is a multi subnet configuration. I have tested the failover of the windows cluster and the availability group. Everything works as expected. In testing the connectivity to listener I open SSMS and can connect to the instance using the Listener name but only from the primary node. I cannot open the instance when logged into the secondary node using the listener name. I can ping the listener IP from the secondary node.


texts: SQL Azure Migration Wizard - Duplicate Primary Key

texts:
Just did a test at migrating a 120gb database onto RDS using SQL Azure Migration Wizard
Because we can't afford any downtime for "just" a test we ran the tool on the live database (meaning data was still getting inserted/updated/deleted while the tool was running)
Scripts were created ok but during the insertion of data we got the error "BCP copy in failed" "Cannot insert duplicate key"
My intuition is because the data was live when the .dat file got created, it somehow read the same row twice, but I don't really want to make an assumption I might regret when we will do the real migration, when the database will obviously be offline.
Could there be any other reason why this has happened and can I assume I'm safe using the tool on the "big day" with a read-only database?
-- Update --
We have tried again to run exact same .dat file of the table that failed and it worked. Which seems to indicate the duplicated key is not in the script... I'm even more confused, could it be a bug in the tool?


texts: Deadlock resource when try to drop datafile

texts:
This morning I did a mistake, I made a datafile offline by using

alter tablespace users drop datafile 'C:\ORACLE\ORADATA\ORCL\DATAFILE\O1_MF_USERS_6LD56LRR_.DBF';

The reason is I want to drop that datafile. After that I add one more datafile to that tablespace n try to delete the offline datafile. But I got the error:

  deadlock found while waiting for resource.

Can anyone help me to fixed this so that I can drop that datafile safely.


texts: sql server service broker queue pass parameter to stored procedure

texts:
Stored Procedure

Create PROCEDURE [dbo].[InitServiceLog]
(
@name varchar(max)=null
)
AS
BEGIN
    SET NOCOUNT ON;
    insert into [AdventureWorks].[dbo].[ServiceBroker_Log](Service) values(@name)
END

Service Broker Queue calling Stored Procedure Name "InitServiceLog"

CREATE QUEUE dbo.SSBSLearningInitiatorQueue
WITH STATUS = ON , RETENTION = OFF ,
ACTIVATION
(
STATUS = ON ,
PROCEDURE_NAME =[dbo].[InitServiceLog],

///// is there any way to pass Stored
///// Procedures Parameters from here
///// Like this "PROCEDURE_NAME =[dbo].[InitServiceLog]('log')

MAX_QUEUE_READERS = 10,
EXECUTE AS OWNER
)
ON [PRIMARY]
GO

In above query Queue how to call stored procedure


texts: Oracle Data Guard:When would data rows exist in the view v$archive_gap?

texts:
I am trying to learn how to resolve the gap between primary and standby manually, the version of my oracle database is 11gr2.

What I did as follow:

At the beginning, primary and standby is synchronized with same log sequence
Disable the parameter log_archive_dest_2,which is used by primary to send redo to standby
Create the archive log by alter system switch logfile at primary
perform a query against v$archive_gap at standby to find the gap between primary and standby

At step4, there is no rows return from v$archive_gap, so I came out with confuses with this situation:

Q1: I has the parameter fal_client and fal_server set at standby, is this factor responsible for “no data return from v$archive_gap”

Q2:If everything goes find, some data rows will be returned from v$archive_gap.In fact, I have log_archive_dest_2 disable at primary ,so standby and primary didn’t not realize each other, how standby know there is a gap?


texts:
I am very new to the world of DB maintenance and recently ran to an issue that will appreciate your advice on.

I have a Database that the earliest solid backup file I can see without any corruption belongs to 7 months ago. I have run DBCC CHECK DB for my current database and I see there is issues with the indexes.

When I do a compare between what has changed in database I realized that someone added a “Unique cluster index” to a child table. (Not sure how to determine if it is a child table or parent table but I assume it is a child table)

My question is this: is it possible to break the records in child/parent tables relationship with applying “unique cluster index”?

I learned what cluster index and non-cluster index are but couldn’t find a solid document that tells me if applying unique cluster index can impact other related tables or not?

I appreciate your help in advanced and will be grateful if you can point me to any link to learn more about this subject.


texts: Does SQL Server Only Perform Calculations In A SELECT List Once?

texts:
Take the following example:

SELECT <CalculationA> As ColA,
       <CalculationB> As ColB,
       <CalculationA> + <CalculationB> As ColC
FROM TableA

Would CalculationA and CalculationB, each be calculated twice?
Or would the optimizer be clever enough to calculate them once and use the result twice?

I would like to perform a test to see the result for myself, however, I am not sure how I could check something like this.

My assumption is that it would perform the calculation twice.
In which case, depending upon the calculations involved, might it be better to use a derived table, or nested view? Consider the following:

SELECT TableB.ColA,
       TableB.ColB,
       TableB.ColA + TableB.ColB AS ColC,
FROM(
      SELECT <CalculationA> As ColA,
             <CalculationB> As ColB
      FROM TableA
    ) As TableB

In this case, I would hope that the calculations would only be performed once?

Please can someone confirm or refute my assumptions?
Or instruct me on how to test something like this for myself?

Thanks.


texts:
I have two Oracle 11gR2 databases on separate servers. I want to duplicate these databases.

For this, I do these steps :

In auxiliary db :

create password file with password that is same if target password

In target database:

Create a pfile and set db_name to auxiliary db
in tnsname.ora set the auxiliary db
with sqlplus connect to auxiliary db and startup, nomount with pfile.

With rman connect to these db and when I want to duplicate these databases with command below, I have an error that's in attach of this question.

Duplicate database to orcl1 with active database spfile nofilenamecheck.

Can any one help me?


texts: Access 2010 linked table - solve the "decimal field's precision" error

texts:
I have an Access 2003 database (running on Win 7 64-bit, Office is also 64-bit) that has many linked tables pointing to an Oracle 11g instance through 64-bit Oracle ODBC driver. Most of the tables work fine, but some of them keep throwing at me this error:
"The decimal field's precision is too small to accept the numeric you attempted to add"

So far i have:

extensively surfed the web for a solution, but none apply in my case (my ODBC driver has no "enforce double" parameter, other suggestions i found were simply irrelevant)
checked that Access sets the correct precision and scale for numeric fields (for example, NUMBER(20,10) in oracle maps to Number data type, Decimal field size with precision 20 and scale 10 in Access). This is even true for the fields that generate errors.
concluded that if a record has at least 1 problematic field, all other fields in that record also display "#Error" in Datasheet view. The record is displayed normally if i set the problematic field to null.
figured that in my case the problem revolves about fields with WGS coordinates of type NUMBER(20,10) or NUMBER(8,6) (probably also other high scale numeric fields, not sure)
found out that if i round a problematic field in Oracle to the field's original scale, Access stops complaining. For example: in an Oracle view a NUMBER(20,10) field_name becomes round(field_name, 10), which makes Access happy.

I can't use the rounding workaround on tables and i don't have much freedom altering the Oracle db since it is a part of a very large and complex system.

I'm a newbie in regards of MS Access and have ran out of ideas on what else to try (except burning my computer and becoming a Jogi to curb my desperation).

Please, please help, kind database community!!


texts: Setting parameters like PCTUSED when creating new tables in ORACLE 12 C

texts:
When creating new tables in ORACLE 12 C, do we need to explicitly set parameters like PCTUSED, PCTFREE, INITRANS, MAXTRANS, STORAGE etc. (as shown in example below)?
Or will ORACLE automatically set these values efficiently? Developers generally tend to leave out these parameters as they focus only on table/schema/data model creation - "the bare minimum" needed for any application to run!

CREATE TABLE TABLE1
(
COL1 VARCHAR2(28 BYTE) NOT NULL
.....
)
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


texts:
I have created some views which give me some values.

So some thing like the following

select from a table of all ids gives me some thing like

id1
id2
id3
id4

view1 gives me

id1 2
id1 1
id1 3
id2 1

view2 gives me

id3 London
id3 Paris
id1 London

What I need is some thing like

id1  6  1
id2  1  0
id3  0  2
id4  0  0

As you can see that I need aggregation of numbers from first view and aggregation of number os cities from second view. What would be a way to do this?


texts: Deny access to one database, allow all the rest

texts: How can I deny access to specific databases from a login?  I know that I can walk through and make the login a db_owner on all the ones I want the login to have access to, but how do I do the opposite?  I want the login to have access to everything but the ones that I specify


texts: Using max for each sub group does not seem to work.


texts: Index on column with data type citext not used

texts:
In PostgreSQL 9.4, with following schema:

CREATE TABLE people (
    id INTEGER PRIMARY KEY,
    name TEXT,
    junk CHAR(1000)
);

INSERT INTO people(id, name)
 SELECT generate_series(1,100000), md5(random()::text);

CREATE INDEX ON people (name text_pattern_ops);

if I search by name, the index is used:

test=# explain analyze select id, name from people where name like 'a%';
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on people  (cost=248.59..1160.92 rows=6061 width=37) (actual time=2.412..8.340 rows=6271 loops=1)
   Filter: (name ~~ 'a%'::text)
   Heap Blocks: exact=834
   ->  Bitmap Index Scan on people_name_idx  (cost=0.00..247.08 rows=6266 width=0) (actual time=2.123..2.123 rows=6271 loops=1)
         Index Cond: ((name ~>=~ 'a'::text) AND (name ~<~ 'b'::text))
 Planning time: 0.600 ms
 Execution time: 8.991 ms

But if I replace TEXT with CITEXT:

CREATE EXTENSION CIText;

CREATE TABLE people (
    id INTEGER PRIMARY KEY,
    name CITEXT,
    junk CHAR(1000)
);

The index is not used anymore:

test=# explain analyze select id, name from people where name like 'a%';
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Seq Scan on people  (cost=0.00..2084.00 rows=500 width=36) (actual time=5.700..152.572 rows=6305 loops=1)
   Filter: (name ~~ 'a%'::citext)
   Rows Removed by Filter: 93695
 Planning time: 0.764 ms
 Execution time: 153.046 ms

According to CITEXT PostgreSQL documentation, the behavior should be as with TEXT:

  Otherwise, it behaves almost exactly like text.

How can I tell PostgreSQL to use the index?


texts: Can I pass a single record from a recordset to a module?

texts:
I am creating a module, RedemptionEstimate, in MS Access 2016 that requires 13 parameters to be passed to it and calculates how much is owed on delinquent property taxes. Some of the parameters can be null and will be handled appropriately within the module. I tried the clunky route of passing all the parameters from a function call within a query, but the null values are resorting in #Error in the returned query field.

I then decided to read up on Recordsets and this seems like a better solution as the module will typically be called from reports and forms based on a query. My understanding is that a recordset can be defined by a query and created within an event call on a report or form. My plan is to create the recordset within an OnLoad Event then parse through the recordset with a Do..Until with a module call within the loop.

Is it possible to pass just one record from the recordset to the module? If not, I'm open to other suggestions.

Other possible solutions I've considered:

Add an additional field to the original table and recalculate the redemption value. - I don't like this solution because the value changes every few months and it's how the old system does it and it's annoying to have the system recalculate 10,000s of records on a regular basis. It also seems unnecessary.
Is is possible to add an empty field to the recordset and just pass the entire recordset to the module then add the calculated value to that field? - Intuitively this feels like I'd have to run through the recordset twice in the OnLoad Event, once to calculate the values and a second time to "print" the data to the report or form.


texts: Do I still need OLAP cubes after a SQL Server Upgrade

texts:
After our DBA got sacked for poor performance, I am now our accidental DBA. I have been pushing to upgrade our SQL Server instance from 2012 to 2017 so I can use the clustered column store indices in our warehouse and downgrade from enterprise to standard at the same time.

We also had been paying a lot of money for enterprise analysis services. We have about SSAS databases that are used in a handful of PowerBI reports and remain mainly unused. I wanted to ask the community if people still find a need for OLAP cubes with the ever-increasing performance improvements in SQL Server and other databases in general.
Im sure there are many variables involved including volume data of data and the number of queries but does anyone have any general insights?
Thanks


texts:
How can I best do a waterfall join in SQL

SELECT * FROM A
LEFT JOIN B
ON A.col1=B.col1 AND
   A.col2=B.col2

but for the ones where there's no match only join on col1 and ignore col2:

LEFT JOIN B
   ON A.col1=B.col1 AND


texts: (re)creating empty tables in partially restored mysql database fails with “table exists”

texts:
Following situation:

I have a MySQL Server (running Percona Server 5.7) set up as a replication slave. This server should hold only a partial replication. To get to this I made a partial backup of another identical slave and applied it to the server (using innobackupex). This is running fine with no problems at all.

The problem that i have now:

There are a few tables that are "slave-specific" that i have to create. Unfortunately when i try to create one of these tables, mysql only creates the tablespace but stops after this, telling me that the table already exists. But in a "show tables" the tables don't show up.

In the mysql data directory only the .ibd files are created for said tables. Deleting these files and trying again doesn't help.

Is there any place where InnoDB saves it's tables that i don't know of? The information_schema database was copied with the backup so maybe there is something left in it but I do not know where that could be.


texts: Database design for multiple units of meassurement and pricings for each item/product

texts:
Table Products
id_product| name    | ....
1         | abcd    | ....
---
Table Pricing
id_price  | qty     | price_type | price
1         | 20      |  discount  | 20
---
Table Unit
id_unit   | fromUnit   | factor   | toUnit
1         | carton     | 8        | pkt
2         | pkt        | 24       | units

I am designing database for commercial business. Basically, each product may have multiple uom (unit[s] of meassurement) and pricing plans.

E.g. item A can be sold in carton or pkt (packet), and there will be a default uom to be converted to.

Case:

User can order in 1 carton which equal to 192 units, or order in one
pkt which equal to 24 units.
When user receive the item, the item might be in different uom. E.g. ordered in pkt but received in carton.

I will always convert and store the received uom, so that I can track in my inventory.

Any suggestion on how can I design my database and link the table based on the cases for multiple uom and multiple pricing issue for product? Thanks.


texts: MSSQL - GRANT EXECUTE permission inside stored procedure

texts:
Environment

SQL Server 2012 (11.0.2100.60)

Situation

user is allowed to execute a number of stored procedure;
user is readonly denywrite
one of stored procedure has an EXECUTE statement;

The Problem
The user can execute all the stored procedure but not the one having the EXECUTE (even if the EXECUTE is trying to read from a view).

CREATE PROCEDURE [dbo].[SP_INJECT_TEST]

@Query varchar(8000)

AS

SET NOCOUNT ON;
EXECUTE(@Query)

GO;

Question
Is there a specific permission for this situation? Should I switch context and impersonate another USER with all the rights inside the database?


texts: Postgresql similar of SQL Server Query Store

texts:
Is there any extension available for Postgresql that is similar to SQL Server query store. So that it can help identify performance bottleneck.

SQL Server query store provides information on how many times query executed, resource consumption per query. https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15 provides more detail on query store.


texts: SELECT very slow when no results and a LIMIT is specified

texts:
I am running into an issue where a SELECT query is very slow because it does not use an index when the number of final results is 0 and a LIMIT clause is specified.

If the number of results is greater than 0 then Postgres uses the index and the result return in ~ 1ms. This seems to always be true as far as I can find.

If the number of results is 0 and no LIMIT is used, Postgres uses the index and the results return in ~ 1ms

If the number of results is 0 and a LIMIT is specified Postgres does a sequential scan and the results take ~ 13,000 ms.

Why doesn't PostgreSQL use the index in the last case?

Cardinalities:

~ 21 million rows total.
~ 3 million rows WHERE related_id=1
~ 3 million rows WHERE related_id=1 AND platform=p1
2 rows WHERE related_id=1 AND platform=p2
0 rows WHERE related_id=1 AND platform=p3
~ 8 million rows WHERE platform=p2

Postgres Version: 9.4.6

Table Schema:

CREATE TYPE platforms AS ENUM ('p1', 'p2', 'p3');

CREATE TABLE mytable (
    id bigint NOT NULL DEFAULT nextval('mytable_sq'::regclass),
    related_id integer NOT NULL,
    platform platforms NOT NULL DEFAULT 'default'::platforms,
    name character varying(200) NOT NULL,
    CONSTRAINT mytable_pkey PRIMARY KEY (id),
    CONSTRAINT mytable_related_id_fkey FOREIGN KEY (related_id)
         REFERENCES related (id)
);

CREATE INDEX related_id__platform__index ON mytable (related_id, platform);
CREATE UNIQUE INDEX some_other_index ON mytable (related_id, lower(name::text));

Queries and plans:

This query returns 0 rows:

EXPLAIN ANALYZE
SELECT * FROM mytable
WHERE related_id=1 AND platform='p2'::platforms
LIMIT 20;

 Limit  (cost=0.00..14.07 rows=20 width=737) (actual time=12863.465..12863.465 rows=0 loops=1)
    ->  Seq Scan on mytable  (cost=0.00..1492790.47 rows=2122653 width=737) (actual time=12863.464..12863.464 rows=0 loops=1)
          Filter: ((related_id = 1) AND (platform = 'p2'::platforms))
          Rows Removed by Filter: 21076656
 Planning time: 3.540 ms
 Execution time: 12868.190 ms

This query also returns 0 rows:

EXPLAIN ANALYZE
SELECT * FROM mytable
WHERE related_id=1 AND platform='p2'::platforms;

 Bitmap Heap Scan on mytable  (cost=60533.63..1295799.94 rows=2122653 width=737) (actual time=0.890..0.890 rows=0 loops=1)
 Recheck Cond: ((related_id = 1) AND (platform = 'p2'::platforms))
  ->  Bitmap Index Scan on related_id__platform__index  (cost=0.00..60002.97 rows=2122653 width=0) (actual time=0.888..0.888 rows=0 loops=1)
         Index Cond: ((related_id = 1) AND (platform = 'p2'::platforms))
 Planning time: 0.827 ms
 Execution time: 1.104 ms

This query returns 20 rows (without LIMIT it would be upwards of 2 million rows):

EXPLAIN ANALYZE
SELECT * FROM mytable
WHERE related_id=1 AND platform='p1'::platforms
LIMIT 20;

 Limit  (cost=0.44..70.95 rows=20 width=737) (actual time=0.759..0.995 rows=20 loops=1)
   ->  Index Scan using related_id__platform__index on mytable  (cost=0.44..1217669.26 rows=345388 width=737) (actual time=0.759..0.993 rows=20 loops=1)
         Index Cond: ((related_id = 1) AND (platform = 'p1'::platforms))
 Planning time: 5.776 ms
 Execution time: 2.476 ms

This query returns 2 rows:

EXPLAIN ANALYZE
SELECT * FROM mytable
WHERE related_id=1 AND platform='p3'::platforms LIMIT 20;

 Limit  (cost=0.44..80.37 rows=20 width=737) (actual time=0.014..0.016 rows=2 loops=1)
   ->  Index Scan using related_id__platform__index on mytable  (cost=0.44..99497.62 rows=24894 width=737) (actual time=0.014..0.015 rows=2 loops=1)
         Index Cond: ((related_id = 1) AND (platform = 'p3'::platforms))
 Planning time: 0.972 ms
 Execution time: 0.123 ms


texts:
I am migrating SQL Server DBs to a new instance.

I have been told that it is possible to dynamically build a RESTORE script from available backups in the system catalog.

Does anyone know of a sample script to do this?

Thanks!


texts: Color formatting Pie Charts in SSRS 2008 R2

texts: I have a pie chart with Categories(Male, Female) with Percentages. If I want to color format Male and Female portion of the chart, what is the approach?


texts:
SQL has well understood ways to do one-to-one, one-to-many, and many-to-many relationships. But what about one-to-n, where n is a known natural number that isn't expected to change. (For example, let's say every weekly pay period statement is expected to have seven daily entries.)

Is there any way to maintain a constraint like this at the database level?

The best I can think of would be to have n foreign keys on the one, so in our example the weekly statement table would have columns for day_0, day_1, day_2, etc, which seems uncommon and possibly not 1NF.

This question is inspired by a recent meme of "making impossible states impossible" in the Elm programming language community.


texts:
I have a database with MongoDB 2.4.14 and I want to migrate the database to MongoDB 3.6.

Note: the file is original from the db path. Not from mongodump.

This process is messy as it requires doing so on many of our clients.

Any advice will be welcomed as I'm a bit lost in this process.


texts: When tables are locked does MySQL queue queries?

texts:
I have a small production website, but it's live 24/7. When running the Optimize, Analyze or Repair commands, MySQL locks  the table it is working on.
QUESTIONS

How would this effect users trying to make changes from a Perl application?
Are queries queued for the time table is locked and then processed (assuming the application does not time out)?
Do queries simply not work?

Thanks!
/usman


texts:
I am trying to run mysql but I get this errors and I don't know what to do. Are there any more error logs where I can find more information?

/usr/local/mysql/bin# ./mysqld

150321 15:35:20 [ERROR] Can't find messagefile '/usr/share/mysql/english/errmsg.sys'
150321 15:35:20 [Note] Plugin 'FEDERATED' is disabled.
./mysqld: Unknown error 1146
150321 15:35:20 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
150321 15:35:20 InnoDB: The InnoDB memory heap is disabled
150321 15:35:20 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150321 15:35:20 InnoDB: Compressed tables use zlib 1.2.3
150321 15:35:20 InnoDB: Using Linux native AIO
150321 15:35:20 InnoDB: Initializing buffer pool, size = 128.0M
150321 15:35:20 InnoDB: Completed initialization of buffer pool
150321 15:35:20 InnoDB: highest supported file format is Barracuda.
150321 15:35:20  InnoDB: Waiting for the background threads to start
150321 15:35:21 InnoDB: 1.1.5 started; log sequence number 1595675
150321 15:35:21 [ERROR] Aborting

150321 15:35:21  InnoDB: Starting shutdown...
150321 15:35:22  InnoDB: Shutdown completed; log sequence number 1595675


texts: mysql insert into indexed table taking long time after few million records

texts:
I have a table like this

CREATE TABLE IF NOT EXISTS `dnddata` (
  `numbers` varchar(10) NOT NULL,
  `opstype` char(1) NOT NULL,
  PRIMARY KEY (`numbers`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (numbers)
PARTITIONS 25 */;

i have to insert 300 million records. i am inserting 10 million records each time using load data in file from csv file.

To insert 10 million records taking nearly 5 min first time. Time is increasing each time gradually. after 30 million records it stops inserting and memory using 100% server not responding.

below my my.cnf file setting

bulk_insert_buffer_size = 100M
key_buffer = 100M
sort_buffer_size = 50M
read_buffer = 50M

i am using cpu with 2 G memory.

details for 30 million records

    Space usage
Type    Usage
Data    545.3   MiB
Index   694.8   MiB
Total   1,240.1 MiB

MySQL client version: 5.5.14

with out index it is inserting fine 10 million in 50 sec.

Please tell me what kind of setting need to change.

Edit based on user answers

I have changed my.cnf setting to below

key_buffer_size = 1G
sort_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size = 2M
bulk_insert_buffer_size = 32M
myisam_sort_buffer_size = 256M

no use. issue is not resolved.

I have tried below mentioned methods to load data

set autocommit = 0; //for innodb
load data infile into …
COMMIT;

START TRANSACTION;
load data infile into …
COMMIT;

ALTER TABLE dnddata DISABLE KEYS;
load data infile into …
ALTER TABLE dnddata ENABLE KEYS;

No luck..


texts:
I'm working on application that uses dynamic query to do a select statement based on user input, after discussing security with DBAs they want me to convert my dynamic select statement into Stored Procedure.

I have built dynamic sql using MSSQL but I can not figure out how to convert it to Oracle SQL.

CREATE PROCEDURE GetCustomer
@FirstN nvarchar(20) = NULL,
@LastN nvarchar(20) = NULL,
@CUserName nvarchar(10) = NULL,
@CID nvarchar(15) = NULL as
DECLARE @sql nvarchar(4000),
SELECT @sql = 'C_FirstName, C_LastName, C_UserName, C_UserID ' +
'FROM CUSTOMER ' +
'WHERE 1=1 ' +

IF @FirstN  IS NOT NULL
SELECT @sql = @sql + ' AND C_FirstName like @FirstN '
IF @LastN  IS NOT NULL
SELECT @sql = @sql + ' AND C_LastName like @LastN '
IF @CUserName IS NOT NULL
SELECT @sql = @sql + ' AND C_UserName like @CUserName '
IF @CID IS NOT NULL
SELECT @sql = @sql + ' AND C_UserID like @CID '
EXEC sp_executesql @sql, N'@C_FirstName nvarchar(20), @C_LastName nvarchar(20), @CUserName nvarchar(10), @CID nvarchar(15)',
                   @FirstN, @LastN, @CUserName, @CID

*please note that I want to prevent SQL injection I do not want to just add string together

**i have built a separate class for creating this dynamic query for my application in .net I have almost 1000 lines of code to handle everything and prevent sql injection, but DBAs have told me that they want stored procedures so they can control input and output.


texts: Simple Linux MySQL GUI Client That Reads UTF-8?

texts:
I'm an inexperienced MySQL user running Ubuntu and I need to open a database full of Hebrew data. I prefer a GUI and I downloaded MySQL Workbench. Not only is it incredibly confusing for me, but when I finally figured out how to import the database and run a query, the data just comes out like %16%19%22 etc. Apparently that's because it's using Latin encoding.

So my question is, is there a very simple MySQL GUI Front-end that will let me just see the tables and the data in it in Hebrew, and let me simply and easily execute queries containing Hebrew characters?


texts:
I want to build a labyrinth in which players can interact with others through leaving messages for one another, so in its most basic form each "tile" of the map is a forum thread or chat room.

However, I intend to have the maze continue to be generated for a very long time (the process is deterministic and will eventually terminate, but only if the 50% chance of each branch continuing fails for every single branch simultaneously. Interestingly a seed value of 42 results in a maze of size "3 tiles", so clearly 42 is not the answer here :D) and each room will have some data, such as which directions are open and which one points to the origin (can be useful for pathfinding later - find the path to the origin and they'll meet somewhere, thus you find the path between the two; this works because there are no loops, only branches).

Anyway, here's the table structure I have in mind for saving rooms:

CREATE TABLE `rooms` (
    `x` INT NOT NULL,
    `y` INT NOT NULL,
    PRIMARY KEY (`x`,`y`),
    `open_left` TINYINT(1) UNSIGNED NOT NULL,
    `open_right` TINYINT(1) UNSIGNED NOT NULL,
    `open_up` TINYINT(1) UNSIGNED NOT NULL,
    `open_down` TINYINT(1) UNSIGNED NOT NULL,
    `origin_direction` ENUM('isorigin','left','right','up','down') NOT NULL
) ENGINE=InnoDB;

Then, the chat messages can be saved like so:

CREATE TABLE `messages` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `x` INT NOT NULL,
    `y` INT NOT NULL,
    FOREIGN KEY (`x`,`y`) REFERENCES `rooms` (`x`,`y`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ...
) ENGINE=InnoDB;

This seems fine, but there's one problem I can forsee:

The maze will be absolutely massive if given enough time to be generated. Based on how it is generated, its size will be exponential and could easily reach millions, perhaps billions of rooms. And past experience has taught me, tables with millions of rows are trouble.

I imagine I will probably need to break the world down into chunks. Say, each chunk can be 1000x1000 tiles in size, giving a maximum capacity of 1,000,000 rooms (assuming somehow the maze generation fills every tile in the area). This seems simple enough, and I can procedurally create tables or even whole databases of the form zone_X_Y with X and Y being the respective coordinates of that sector.

I guess mostly this question is to voice my thought process and see if anyone has any suggestions to make for better alternatives. My DB experience is pretty much just picking up what I can as I go along and learning from past mistakes.


texts: MySQL 5.5 replication over SSL broken after upgrading OpenSSL, OS

texts:
I've got an issue where, after upgrading a replication slave from Ubuntu 14.04 to 16.04, this slave can no longer connect to its master using SSL.

Setup before the upgrade:

1 master, 2 slaves, ALL running mysql-5.5 (client and server).  All on Ubuntu 14.04, with OpenSSL 1.0.1f.

Setup now:

One change was made: I upgraded one of the slaves to Ubuntu 16.04.  I kept the mysql version on that server at 5.5.  However, OpenSSL did get upgraded to OpenSSL 1.0.2g.

Since the upgrade, this replication slave can no longer connect to its master via SSL.  The error received is:

ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)

I feel confident that this is an SSL issue, because I am able to connect to the master without using SSL (both from the commandline and via the slave threads).

I have tried disabling DH key exchange (https://bugzilla.redhat.com/show_bug.cgi?id=1228755) and specifying a different cypher (Why has my slave server stopped connecting to master in MySQL replication?), but none of that seems to help.  I continue to get the 2026 error that I mentioned above.

Anyone have any insight?


texts: Is SQL Server data compression categorically good for read-only databases?

texts:
Some literature on SQL Server data compression I read state that the write cost increases to about four times what would normally be required.  It also seems to imply that this is the primary downside to data compression, strongly implying that for a read-only archive database, the performance will (with few excep
tions) be improved by the usage of data compression of 100% filled pages.

Are the statements above true?
What are the primary "variations" between data compression and otherwise (for reading)

"CPU +x%"?
"IO -y%"?
page split occurence?
tempdb usage?
RAM usage?

And for writing?

For the purpose of this question, you can limit the context to PAGE-level compression of a big (> 1TB) database, but additional comments are always welcome.

References:

SQL Server Storage Engine Blog (The DW scenario shows compression to be very advantageous)
Data Compression: Strategy, Capacity Planning and Best Practices

  A more detailed approach to deciding what to compress involves
  analyzing the workload characteristics for each table and index. It is
  based on the following two metrics:

  U: The percentage of update operations on a specific table, index, or partition, relative to total operations on that object. The lower
  the value of U (that is, the table, index, or partition is
  infrequently updated), the better candidate it is for page
  compression.
    S: The percentage of scan operations on a table, index, or partition, relative to total operations on that object. The higher the
  value of S (that is, the table, index, or partition is mostly
  scanned), the better candidate it is for page compression.

Both of the above are demonstrably biased towards recommending page compression for DW-style databases (read-intensive/exclusive, big-data operations).


texts: How to count three consecutive records with same values in a column based on the value of another column in PostgreSQL?

texts:
I have a table attendance as follows:

create table attendance (id int, name varchar(30), status varchar(10), date date)

This table has the following records:

insert into attendance values (1,'John','absent','2016-03-01');
insert into attendance values (1,'John','absent','2016-03-02');
insert into attendance values (1,'John','absent','2016-03-03');
insert into attendance values (2,'Sam','present','2016-03-04');
insert into attendance values (3,'Sam','absent','2016-03-05');
insert into attendance values (1,'John','absent','2016-03-06');
insert into attendance values (1,'John','absent','2016-03-07');
insert into attendance values (1,'John','absent','2016-03-08');
insert into attendance values (1,'John','present','2016-03-09');
insert into attendance values (1,'John','absent','2016-03-10');
insert into attendance values (1,'John','absent','2016-03-11');
insert into attendance values (1,'John','present','2016-03-12');
insert into attendance values (1,'John','absent','2016-03-13');

Now I want to count that how many times a person has three consecutive absent records.
The result should be as follows:

id name count
 1  John   2

because John is absent for three consecutive days two times.

If they have 6 absents on 6 consecutive dates, then it should count 2 i.e (3+3).

It should count 1 for 3 consecutive absents on 3 consecutive dates i.e if John is absent for 1,2 and 3 march, it should count 1. but if John is absent for 1,2 and 4 march then it should not count 1.
4 or 5 absents should be counted as 1.

If there is no entry for a date, It is considered 'Present'.

Any help is appreciated.


texts:
CREATE TABLE Parent ( ParentID int NOT NULL PRIMARY KEY,
                      ParentName varchar(50) NULL )
CREATE TABLE Child  ( ChildID int NOT NULL PRIMARY KEY,
                      ParentID int NOT NULL REFERENCES Parent (ParentID),
                      ChildName varchar(50) NULL,
                      IsFavorite BIT NOT NULL )

The first desire is that I would like to prevent the situation of childless parents. Obviously when the pair is first created the Parent will have no Child, and I don't want to prevent the insert itself; but I want to outright forbid prevent deleting the last child. (For business reasons Parents will never be deleted.)

As a second desire, I need to prevent more than one Child with IsFavorite=1, which I know I can do with a filtered unique index.  I also would like to discourage having no Children with IsFavorite=1 -- i.e., don't permit setting IsFavorite=0 if this is the only Child of its Parent.  (From the app side, inserts should normally be done correctly with IsFavorite=1 for the first child; but I don't want the database to interfere with that.)

I'm sure I'm not the first with these desires but I couldn't find a matching question on dba or on stackoverflow. If this is a dupe, by all means mark it dupe and point me where I need to be.


texts: How to fill a tabe automatically with a procedure in MySQL

texts:
I have a very big table for users as below:

CREATE TABLE `karbar` (
 `uid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `email` varchar(70) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `password` char(41) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `token` char(40) NOT NULL,
 `joined` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `totalspam` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `flname` varchar(60) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `activation_link` char(40) NOT NULL,
 `role` tinyint(3) unsigned NOT NULL DEFAULT '1',
 `gender` char(1) NOT NULL DEFAULT 'u',
 `is_shop_active` char(0) DEFAULT '',
 `english_name` varchar(30) NOT NULL,
 `followings` mediumint(8) unsigned NOT NULL DEFAULT '0' ,
 `followers` mediumint(8) unsigned NOT NULL DEFAULT '0' ,
 PRIMARY KEY (`uid`),
 UNIQUE KEY `email` (`email`),
) ENGINE=InnoDB

I want to fill this table with lots of data. Let's say millions of records. How to do this? I need to produce unique emails. Different data for emails. I know I can use CONCAT but don't know how to produce random data


texts: MySQL 5.6 @ AWS RDS read IOPS plateaus lower than write IOPS

texts:
I have an RDS MySQL 5.6 instance which plateaus ReadIOPS at around 6.5k. My WriteIOPS is most often lower, but sometimes higher than ReadIOPS, but has never entered such a rigid plateau.  The disk was gp2 750 GiB (2250 IOPS) a couple days ago, then gp2 1000 GiB (3000 IOPS), now io1 1000 GiB / 10000 provisioned IOPS, and the plateau level is the same. Instance type is db.r4.xlarge.

Here is a chart which shows the problem:

As strange as it seems, it seems like something in the MySQL side is capping reads. Have enabled all sorts of monitoring in the instance and nothing obvious jumps out.

Is there anything in MySQL itself which would somehow limit the reads (but not the writes)?

EDIT: table structure:

CREATE TABLE `position` (
  `id` binary(16) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `analyzed` int(11) DEFAULT NULL,
  `version` varchar(255) DEFAULT NULL,
  `machine_serial` varchar(255) DEFAULT NULL,
  `station_id` int(11) DEFAULT NULL,
  `accelerometer` varchar(255) DEFAULT NULL,
  `altitude` float DEFAULT NULL,
  `area_id` int(11) DEFAULT NULL,
  `coordinates` geometry DEFAULT NULL,
  `course` float DEFAULT NULL,
  `gps_status` varchar(255) DEFAULT NULL,
  `gps_date` datetime DEFAULT NULL,
  `original_speed` float DEFAULT NULL,
  `speed` float DEFAULT NULL,
  `analytics_determined_field_1` bit(1) DEFAULT NULL,
  `gps_precision` float DEFAULT NULL,
  `area_uuid` binary(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `positioncreated_at_idx` (`created_at`),
  KEY `positionanalyzed_idx` (`analyzed`),
  KEY `positionstation_idx` (`station_id`),
  KEY `gps_date_idx` (`gps_date`),
  KEY `gps_date_station_id_idx` (`station_id`,`gps_date`),
  KEY `area_id_idx` (`area_id`),
  KEY `area_uuid_idx` (`area_uuid`),
  KEY `created_at_idx` (`created_at`),
  CONSTRAINT `FK_station` FOREIGN KEY (`station_id`) REFERENCES `stations` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1


texts:
Monthly I need to sync a SQL Server database. Basically the source database generates a .bak file, then I take that file and restore it to the destination database.

That worked for the first few times, now the destination database changed because there were some users added, and some tables created.

I thought to open the .bak file, query the destination DB, removing the duplicates and then do and INSERT with only the new data.

Is that something reasonable?

How can I read/open a .bak file?

Also, I have been following this instructions

Basically it's my problem, but it use robocopy, as well jobs and I can't use them now.

It use this code to restore the backup:

PRINT '*** Restore full backup of DB $(DB) ***'
:CONNECT $(TGT)
GO
USE [master]
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = '$(DB)')
BEGIN
    ALTER DATABASE $(DB)
        SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    DROP DATABASE $(DB)
END

RESTORE DATABASE $(DB)
FROM disk = '$(RESTORE_PATH)\$(DB).bak'
WITH RECOVERY, NOUNLOAD, STATS = 10, REPLACE,
     MOVE '$(DATAFILENAME)' TO '$(RESTORE_DATA_PATH)\$(DATAFILENAME).mdf',
     MOVE '$(LOGFILENAME)' TO '$(RESTORE_DATA_PATH)\$(LOGFILENAME).ldf'
GO

From what I remember from SQL Server, it will restore the entire database. Is it possible to restore only one table?

Thanks


texts:
Let me preface my question by saying, that my teacher stared at my code for 2h and could only guess that it might be the collation.

Here's the problem: for an exercise we were asked to create a movie database and then write several procedures. One procedure ist supposed to do the following: - take the id of a movie (as a parameter), the director's last and first name and then test wether the director already is in the table. If the answer is no, the name is added to the table. Either way, the procedure then adds the movieID and the ID of the director to a second table. Here's my code:

delimiter //
create or replace procedure p_director
( par_movieID integer,
 par_firstName varchar(50),
 par_lastName varchar(100))
 BEGIN
 DECLARE var_id  INTEGER;
DECLARE var_firstName  varchar(100);
DECLARE var_lastName   varchar(100);
DECLARE var_control integer;
DECLARE exit handler for SQLEXCEPTION
Begin
rollback;
End;
Start TRANSACTION;

set var_firstName = par_firstName;
set var_lastName =  par_lastName;

SELECT directorID FROM director
where lastName = var_lastName AND firstName = var_firstName
INTO var_control;

select var_control as debug;

IF (var_control IS NULL) THEN

select max(directorID)+1 from director into var_id;

insert into director ( directorid, firstName, lastName)
values (var_id, par_firstName, par_lastName);

insert into moviedirector (movieID, directorID) values
( par_movieID ,var_id) ;

ELSE

insert into moviedirector (movieID, directorID)
values ( par_movieID ,var_control);

END IF;
END//
delimiter ;

As you can see I added the line "select var_control as debug;". I did this to get the value of the variable displayed on the screen. Anyway. This code should run fine. I know this, because I wrote a routine that did the exact same thing for a homework assignment. And for that database it works. However if I run this procedure unter MariaDB, it doe not work. Despite no syntax error, the statements between else and end if are never reached and, even stranger, the "select var_control as debug;" statement genereates no output on the screen, not even null!

As I said, my teacher stared at it for a solid 2 hours, finally he found the only difference between the code in my homework assignement and this exercise: the collation and caracterset of the database: For my homework, I used the collation latin1_swedish_ci, while at school we use utf8_general_ci.

Could this be the reason? Can the collation really have such a profound Impact? Has anyone of you ever run into thsi kind of problem?


texts: adding node to existing availability group

texts:
Currently have a 2 node availability group, trying to add a 3rd.

Added Node3 to the Windows Cluster. Node3 appears as UP in "Nodes" on the Failover Cluster Manager console. However I have the following message on Cluster Events:

    Cluster network name resource 'CAUBC-CLamf' encountered an error enabling the network name on this node. The reason for the failure
    was:   'Unable to obtain a logon token'.

  The error code was '1326'.

  You may take the network name resource offline and online again to
  retry.

Notably, the Cluster Core Resources console doesn't have the IP address for the new cluster node.

Another source of confusion here is found in the cluster log file:

  Logon failed for user CAUBC-CLamf$

Where is this user coming from?

EDIT: despite this error, I have been able to add the new node to the availability group and automatically seed the databases. I don't get why the error persists, however.

In addition, only the new node's ip address has registered in DNS - the listener/cluster resource ips are not in DNS

Is it necessary to add the new node's secondary ip to the Cluster Core Resources, in a multi-subnet setup?


texts: What is a mysqld process in a MySQL cluster?

texts:
As I know, a mysqld process is a kind of a router of a MySQl cluster. It directs requests to the right ndbd node.

Assuming I have 3 ndbd nodes on 3 different machines and I don't use replication. How many mysqld proccesses do I need? I would say one? For me its like a master-slave architecture (comparable with mongodb) But I saw configurations where each ndbd node also has one mysqld proccess.
Why?


texts: Query perfomance with multiple JOINs on MyISAM-based DB

texts:
I'm working with Enron Email Dataset in MySQL database. After I executed data import from the dump file into MyISAM-based DB, I got the set of tables without any connections between them via foreign keys.

I read that MyISAM is more suitable for the case if the tables are more static with lots of select and less update and delete operations. In my case, all queries will be read-only with multiple joins inside of each query. Considering the fact that MyISAM doesn't support foreign keys at all, will this affect the performance of my queries and if yes, how can I optimize them?

Should I convert the DB into InnoDB-format or, it's better to stay with MyISAM-format?


texts: How to repair backup of .mdf file database?

texts:
One of our clients gets the following error when trying to backup a database:

  System.Data.SqlClient.SqlException: SQL Server detected a logical
  consistency-based I/O error:  incorrect checksum (expected:
  0x121db60d; actual: 0x521db60d).  It occurred during a read of page
  (1:1632) in database ID 4 at offset 0x00000000cc0000 in file
  'C:\Program Files (x86)\Microsoft SQL
  Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf'.  Additional messages in the
  SQL Server error log or system event log may provide more detail.
  This is a severe error condition that threatens database integrity and
  must be corrected immediately.  Complete a full database consistency
  check (DBCC CHECKDB).  This error can be caused by many factors; for
  more information, see SQL Server Books Online. Could not insert a
  backup or restore history/detail record in the msdb database.  This
  may indicate a problem with the msdb database.  The backup/restore
  operation was still successful.

I tried to perform checkdb, checkdb repair_rebuild as well as checkdb REPAIR_ALLOW_DATA_LOSS but none of these corrected the issues is the MSDB database.

My question is: can I just grab a clean MSDB database from another instance to recover corrupt one or do I really have to uninstall/reinstall SQL Server?


texts: Mysql server requires to explicitly state the database in the query

texts:
I am running mysql-client (Ver 14.14 Distrib 5.7.19) and mysql-server versions 5.7.19 with user root from localhost on Ubuntu server 16.04.2 LTS.

I have noticed that mysql doesn't return the data I'm asking unless I explicitly specify the database in the query. I have replicated the behavior below over and over again.

Can someone please explain why this is happening ?

use db1;
select database();
    db1

select count(*) from users;
    145

select * from users;
    Empty set, 28416 warnings
select * from users;
    ERROR 2006 (HY000): MySQL server has gone away

The result is changing every time you run the query above.
One empty set, one Error2006, one empty set, one Error2006,...

show warnings;
    ERROR 2013 (HY000): Lost connection to MySQL server during query
show warnings;
    Warning 1287 'COM_FIELD_LIST' is deprecated and will be removed in a future release. Please use SHOW COLUMNS FROM statement instead

There's 64 rows of this warning. I get Error2013 only the first time that I run "show warnings". There is no pause or delay.
All subsequent runs return the same 64 rows.

select * from db1.users;

This returns the full table (145 rows) with no problems whatsoever.


texts: Excluding (or subtracting) multiple dateranges from a daterange in PostgreSQL

texts:
I have a list of items which contain: product_id, period and availability.

The link below references my SQLFiddle Example.
I want to get the availability periods of each product for a given period, e.g:

List the availability periods for the period [2018-11-01, 2018-11-30] of the prod_id = 10.

It will display:

 1. [2018-11-01, 2018-11-01]
 2. [2018-11-03, 2018-11-03]
 3. [2018-11-06, 2018-11-07]
 4. [2018-11-10, 2018-11-31]

The operator difference of daterange does not support this type of differences and it returns :

ERROR: result of range difference would not be contiguous

I realized that it is not possible to get the suitable results using the operator with such data.Is there any link or another solution to select the products in this way?


texts: How to find the stored procedure which executed an event?

texts:
I have a problem where a certain stored procedure disappears occasionally and I need to find out which script deletes it. I found this piece of code which gives the events related to the deletion of this stored procedure.

DECLARE @path NVARCHAR(260);

SELECT
@path = REVERSE(SUBSTRING(REVERSE([path]),
CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
  LoginName,
  HostName,
  StartTime,
  ObjectName,
  TextData
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 47    -- Object:Deleted
AND EventSubClass = 1
AND ObjectName like N'%usp_GetPendingConfiguration%'
ORDER BY StartTime DESC;

Is there a way that I can find which stored procedure or event dropped this stored procedure? Please advice.


texts: can't connect to mysql via command line but workbench works, ssh tunneling required

texts:
I am trying to connect to a mysql server running at B.edu. I want to connect via SSH tunnel.
I first set this up in MySQL Workbench:
Connection Method: Standard TCP/IP via SSH
SSH hostname: B.edu
SSH username: me
MySQL hostname: B.edu
MySQL port: 3306

This works fine. I connect and can do as much as I can through Workbench.
Now I'd like to connect via terminal command line. I try:
ssh -N -L 9999:127.0.0.1:3306 me@B.edu &
mysql -h 127.0.0.1 -u me -P 9999

The ssh connection works fine, but the mysql command fails with the error messages:

channel 2: open failed: connect failed: Connection refused
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

I see that I'm not really asking for the same thing through the two methods, because from the command line I'm using 127.0.0.1 as the mysql host name, while from Workbench I'm using B.edu as the hostname. Since the Workbench version works, I'd like to change my command line approach to work similarly, but I don't see how.
My ultimate application, once I have the command line version working, is to automate using Python and PyMySQL (presumably with paramiko to handle the tunneling), but I'd like to cross that bridge later and get the terminal line approach working first.


texts: MySQL COUNT Query taking too long time to execute

texts:
I have below query that takes 7 secs to run

SELECT COUNT(*) AS `count`
FROM `yuldi`.`businesses` AS `Business`
  LEFT JOIN `yuldi`.`businesses_categories` AS `BusinessesCategory`
    ON (`Business`.`id` = `BusinessesCategory`.`business_id`)
  LEFT JOIN `yuldi`.`categories` AS `Category`
    ON (`BusinessesCategory`.`category_id` = `Category`.`id`)
WHERE `Category`.`slug` = 'building-construction'

when i run it second time it takes 160 ms. what wrong why it takes so long on 1st run on everytime

EXPLAIN:

| id | select_type | table              | type   | possible_keys                                                                                | key                                          | key_len | ref      | rows  | Extra                    |
|  1 | SIMPLE      | Category           | const  | PRIMARY,UNIQUE_SLUG,index_lug                                                                   | UNIQUE_SLUG                                  | 302     | const    |     1 | Using index              |
|  1 | SIMPLE      | BusinessesCategory | ref    | PRIMARY,fk_businesses_has_categories_categories1_idx,fk_businesses_has_categorie_businesses_idx | fk_businesses_has_categories_categories1_idx | 4       | const    | 49630 | Using where; Using index |
|  1 | SIMPLE      | Business           | eq_ref | PRIMARY                                                                                | PRIMARY                                      | 4       | yuldi.BusinessesCategory.business_id |     1 | Using index              |


texts: What does the wait type PREEMPTIVE_XE_CALLBACKEXECUTE mean?

texts:
I have two instances in the last week where a DROP command for an Extended Events session has taken over a day to complete, sitting with the wait: PREEMPTIVE_XE_CALLBACKEXECUTE.

Can anyone explain what this wait type means?

More Background Info:
In the first instance, I ran a T-SQL command to drop the session and it completed a day later. Using sp_whoisactive, I saw that the query was waiting on PREEMPTIVE_XE_CALLBACKEXECUTE.

During this time, Object Explorer's queries to gather metadata were being blocked and recieving lock-timeouts, but there were no other complaints (or so I thought at the time).

I tried to drop another session on Friday, and the same behavior occurred, except it didn't just go away after a day as the with the first event. Instead, I found out this morning that the client application could not connect. It was being blocked by the DROP EVENT SESSION query.

A restart of the SQL Service cleared out the blocking query.

I can't find anything that helps to diagnose what this wait type this is... and why my event sessions won't drop like they should. Can you help?

Server Info: SQL 2008 R2 Enterprise with SP2


texts: PostgreSQL doesn't use index on inherited tables

texts:
I have two tables in PostgreSQL 9.4.5 deployed on Amazon RDS:

an append only, weekly partitioned, All Positions table. These store all position readings from about 100,000 sensors. There are about 50 million records per week (80 records per second), with about 2 weeks of data so far (100 million records). This table is used to retrieve the historical positions of any individual sensor
a Current Positions table, which stores the latest reading from any sensor by it's primary key. An update trigger updates which row stores the latest reading from a sensor.

Definition for All Positions:

CREATE TABLE all_positions
(
  pk BIGSERIAL PRIMARY KEY,
  sensor_id UUID,
  rcv_tme TIMESTAMP WITHOUT TIME ZONE,

  -- many other fields...
)

CREATE TABLE all_positions_y2016w01 (
    CHECK (rcv_tme >= '2016-01-04 00:00:00'::timestamp without time zone AND
            rcv_tme < '2016-01-11 00:00:00'::timestamp without time zone)
) INHERITS (all_positions);
CREATE UNIQUE INDEX all_positions_y2016w01_pk_index ON all_positions_y2016w01(pk)

CREATE TABLE all_positions_y2016w02 (
    CHECK (rcv_tme >= '2016-01-11 00:00:00'::timestamp without time zone AND
            rcv_tme < '2016-01-18 00:00:00'::timestamp without time zone)
) INHERITS (all_positions);
CREATE UNIQUE INDEX all_positions_y2016w02_pk_index ON all_positions_y2016w02(pk)

-- ... other weekly tables

-- upserts into current_position table the latest pk for a sensor_id
CREATE TRIGGER insert_positions_current_trigger
  BEFORE INSERT
  ON all_positions
  FOR EACH ROW
  EXECUTE PROCEDURE insert_positions_current_function();

-- bins into appropriate child table
CREATE TRIGGER insert_positions_partition_trigger
  BEFORE INSERT
  ON all_positions
  FOR EACH ROW
  EXECUTE PROCEDURE insert_positions_partition_function();

and Current Positions:

CREATE TABLE current_positions
(
  sensor_id UUID,
  pk BIGINT
)
CREATE UNIQUE INDEX current_positions_sensorid_idx on current_positions(sensor_id)
CREATE UNIQUE INDEX current_positions_pk_idx on current_positions(pk)

The issue is when I perform the following query:

 EXPLAIN ANALYZE
 SELECT t1.*
   FROM current_positions t2
     INNER JOIN all_positions t1 ON t1.pk = t2.pk

it performs a seq scan of all child tables i.e. 100 million rows which takes 7 minutes.

But if I perform the following query (assuming that only week 1 and week 2 hold all the records):

EXPLAIN ANALYZE
(SELECT t1.*
  FROM current_positions t2
    INNER JOIN all_positions_y2016w01 t1 ON t1.pk = t2.pk)
  UNION
(SELECT t1.*
  FROM current_positions t2
    INNER JOIN all_positions_y2016w02 t1 ON t1.pk = t2.pk)

it performs an index scan on the child tables, and takes about 2 seconds.

I believe this is because the tables are partitioned by time, and PostgreSQL doesn't understand that the pk column is unique across all the child tables i.e. for each (sensor_id, pk) record in current_positions, there will only be one corresponding record which matches pk across all the all_position child tables. Is there any way to force an index scan on the child tables, instead of a seq scan?

Thanks!

Note: I've linked the two EXPLAIN ANALYZE below

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

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


texts: Postgres - Revoke database acess from user, but allow replication acess

texts:
The DB have a replication server, i need to revoke the permission on the user "user_bi" but allow the acess of this same user on the replication server!

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM user_bi; -- on master.

This replicate the revoke to the slave.

GRANT SELECT ON ALL TABLES IN SCHEMA public FROM user_bi; -- on the slave.

This returns a error "SQL Error [25006]: ERROR: cannot execute GRANT in a read-only transaction".

Can i revoke the permissions in only one databank ?


texts: Oracle: Truncate large table takes forever

texts:
Problem

I'm truncating a very large table ( 210+ million rows ).
It's been hours since it started and I have no idea when it will be done.
There're several tables like that, that I want to truncate.
The database is Ora9i.

Question

How can I do to make truncates faster ?
Also, Is there a way to see the progress of the truncate operation ?


texts:
I get this error message occasionally when I execute a stored procedure that pulls information from multiple linked servers and aggregates the data. It looks similar to what I have below.

DECLARE Cur CURSOR FOR SELECT name FROM sys.servers WHERE is_linked = 1 AND (@Environments IS NULL OR name IN (SELECT Item FROM clrStringSplit(@Environments, ',')))
OPEN cur
FETCH NEXT FROM cur INTO @Environment
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY

    SET @Query = '
        EXEC ' + @Environment + '.db.dashboard.storedproc ''' + CAST(@StartDate AS VARCHAR(10)) + ''', ''' + CAST(@EndDate AS VARCHAR(10)) + ''''

    INSERT INTO #Table (Environment, Column1, Column2, Column3, Column4, Column5, Column6, Column7)
    EXEC(@Query)
END TRY
BEGIN CATCH
    PRINT 'There was an error trying to query against ' + @Environment
    PRINT ERROR_MESSAGE()
END CATCH

FETCH NEXT FROM cur INTO @Environment
END

The stored procedure it calls on each server is the exact same on each one and is a basic select statement that pulls from tables underneath the dbo schema. It will "randomly" error out with the message of "Could not complete cursor operation because the table schema changed after the cursor was declared." Sometimes it runs fine, other times not. Each time its ran, even if back to back will sometimes work and sometimes not. What could possibly cause this to happen and why is it so intermittent? The error can be generated on any given call to a procedure on one of the servers. It's not always the same server it tries to query against that produces the error.

It is worth mentioning that I have several every procedures that do something extremely similar but never produce the issue. But they are all under the same schema (dbo) and this is the first time where a different schema is used.

EDIT - I forgot to mention that I had tried using a FAST_FORWARD cursor and it does not seem to help.


texts:
I am getting the errors below when trying to start MYSQL.
I've deleted both the ./ib_logfile0 and ./ib_logfile1 files and still no success.  I'm assuming is has something to do with not being able to write to the temp file but I don't know the location of that directory.

140527 15:56:04 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
140527 15:56:04 [Note] Plugin 'FEDERATED' is disabled.
140527 15:56:04 InnoDB: The InnoDB memory heap is disabled
140527 15:56:04 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140527 15:56:04 InnoDB: Compressed tables use zlib 1.2.3.4
/usr/sbin/mysqld: Can't create/write to file '/tmp/ibmZXLyt' (Errcode: 13)
140527 15:56:04  InnoDB: Error: unable to create temporary file; errno: 13
140527 15:56:04 [ERROR] Plugin 'InnoDB' init function returned error.
140527 15:56:04 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
/usr/sbin/mysqld: File '/var/log/mysql_slow.log' not found (Errcode: 13)
140527 15:56:04 [ERROR] Could not use /var/log/mysql_slow.log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
140527 15:56:04 [ERROR] Unknown/unsupported storage engine: InnoDB
140527 15:56:04 [ERROR] Aborting


texts: How to bring data from two tables that could/could not be present in one of them

texts:
Background: I have two tables, let's call them "purchaseTransactions" and "Products", the first one, holds all the transaction made by product, and the second one has all my products available, let's see an example oh how the look:

PurchaseTransactions

PurchaseDate
ProductId
Quantity
Some other fields...

Products

ProductName
ProductNumber
Some other fields...

Basically what I want to obtain is the quantity sold by month and product from a specific timelapse, For instance:

Let's say we have the Products

Name: Product A, No: 01
Name: Product B, No: 02
Name: Product C, No: 03

For Purchase Orders

PurchaseDate: Jan/01/2018, ProductId: Product A, Quantity: 100
PurchaseDate: Jan/18/2018, ProductId: Product A, Quantity: 150
PurchaseDate: Feb/02/2018, ProductId: Product A, Quantity: 150
PurchaseDate: Feb/24/2018, ProductId: Product A, Quantity: 200
PurchaseDate: April/04/2018, ProductId: Product A, Quantity: 200
PurchaseDate: January/01/2018, ProductId: Product B, Quantity: 222
PurchaseDate: March/02/2018, ProductId: Product B, Quantity 250
PurchaseDate: May/04/2018, ProductId: Product B, Quantity: 333
PurchaseDate: May/20/2018, ProductId: Product B, Quantity: 666

An example of what I want is to bring ALL the products sold grouped by Year,Month, Product and Sum of its quantity. Something like this.

 Year Month, ProductId, QuantitySold
 -----------------------------------
 2018 Jan   Product A   250
 2018 Jan   Product B   222
 2018 Jan   Product C   0
 2018 Feb   Product A   250
 2018 Feb   Product B   0
 2018 Feb   Product C   0
 2018 March Product A   0
 2018 March Product B   0
 2018 March Product C   250
 .... etc

Notice that the query should return even the products that does not have any purchases in the specified month, this is what I have tried so far:

SELECT YEAR(PurchaseDate), MONTH(PurchaseDate), ProductId, SUM(Quantity)
FROM PurchaseOrders
WHERE Product IN (Product A, Product B...) AND MONTH(PurchaseDate) => DATEADD(MONTH, -3, GETDATE()) --Could be by some specific products and for a specific timelapse, ex the past three months.
GROUP BY YEAR(PurchaseDate), MONTH(PurchaseDate), ProductId

But this only returns the Products existing in the Purchase table, I have tried with a LEFT JOIN with the Products table to get all the products, but I think I am not implementing it correctly.

Any Idea of how to do this?


texts: Can we use the multiple databases system concept in a single web application?

texts:
Am building a simple web based expert application.I have created two databases in the back-end,One database holds user data and the other holds user activity data.Hence it is accessed by a single web app.[e.g Distributed and Parallel both use in single website for back-end]

My question: Can a single web app have multiple databases in the back-end?


texts: recover postgresql database after removal with --purge

texts: I noticed, after a while, that when I got new versions of postgresql (I'm using debian testing) they were being installed while keeping older versions of the DBMS running in parallel (one listening on a different port and so on). So, without thinking much about it, I just ran dpkg remove --purge on the older versions without realizing that the DB that I was using was actually running on the oldest version installed. Is there DB data on the FS that is not deleted if removing the postgresql package with --purge so that I can get the DB back?


texts:
I've read a number of articles regarding efficiency of primary keys depending on the storage engine, and I am left confused.

Given a simple many-to-many table with two fields, storeId and zoneId, which one of the following designs is the most efficient with InnoDB and why?

using the two fields as a composite primary key:

CREATE TABLE store_zone(
    storeId INT(10) UNSIGNED NOT NULL,
    zoneId  INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY(storeId, zoneId)
);

Using a specific auto increment primary key:

CREATE TABLE store_zone(
    id      INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    storeId INT(10) UNSIGNED NOT NULL,
    zoneId  INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY(storeId, zoneId)
);

Notes:

I need a unique key on the (storeId, zoneId) pair anyway
I have foreign keys to the store and zone tables, not shown here for readability, so in both cases there's an extra required index on zoneId as well


texts: Need a query to show user present/absent within given interval

texts:
I am working on a project where I need to show whether users were logged in or not over a given interval (currently within a 24-hour period over the course of a month, but could change).

I have a table with, among other things, the user's login and a timestamp (in milliseconds)

CREATE TABLE tableA (user_login varchar(64), tstamp double, ...)

(NOTE: I don't want to store unix time seconds or date in sql - as I lose precision of 4 places - here in this case it is not relevant but project is big. I expect to need this information, with full precision, elsewhere. I don't need to worry if I store unix time - seconds as date/timestamp divide by 1000 or multiply etc.)

From this, I need create a "map" to see if each user has logged in daily ( so the step will be one day in milliseconds ).  The start and end will vary by days in month. In addition to a simple present/absent Boolean value, I'd like to produce an aggregated daily login count as an additional value.

Now, I could query the table, select back all the data, and put this together easily enough in my preferred coding language. However, we'll have thousands of rows per user; transporting all this over the network is a waste of resources for the client. So, I decided it made more sense to do this at the database server end.

So: Given the following data:

base data ( input  table A ):

user_login     |   tstamp                 |  ...
---------------------------------------------------------------
    a          | 1                        |  ...
    a          | 3                        |
    a          | 5                        |
    a          | 6                        |

And the following definition of the intervals we're interested in:

start of analysis period (dstart) = 1
end of analysis period (dend) =  10
interval size = 3

How do I get the following results?

login|dt|presence | count (facultative column with aggregated count)
-----------------
a    |4 |1        | 2
a    |7 |1        | 2
a    |10|0        | 0

where:
 - dt = the end of each interval
 - presence = was there at least one row found for the user within the interval (1 = yes, 0 = no)
 - count = the total number of rows fond for this user in this interval

Additional notes:
 - if a tstamp falls on the boundary between two intervals, it's OK for it to be counted in both intervals.


texts: scenario to update or insert big amount of data

texts:
I try to find the best scenario to update or insert 1 million contacts in my postgres database.

My actual scenario is:

read contacts 1000 per 1000 and try to insert multiple rows with one request
if my request fails, I go line per line to insert or update my 1000 contacts

It takes 7 minutes for 50K lines.

What is the best scenario to perform my update or insert?


texts: fn_dblog not showing up deleted table data

texts:
I unintentionally deleted 4 rows from a table. I tried to fetch back the deleted data using a stored procedure (got from internet). However, I was able to see my data in the result of stored procedure. Then I updated the original schema of the table and remove the primary key before restoring the deleted data and again tried to run the stored procedure and found that fn_dblog is no more showing my deleted rows of that table.

All other deleted data from other tables are present in fn_dblog. How can I now fetch my deleted rows without using any external tool?
I am using SQL Server 2012.

Edited:
I have attached a snapshot as well of ApexSQL. The last 4 rows are my data but i am unable to recover, as the redo script says:

  00000088:0000011B:0002: ROW DATA NOT AVAILABLE FOR DELETE ON
  [UNKNOWN].[UNKNOWN(1394104007)]

I also asked at stack overflow the same question. No responses.


texts:
I have a data structure similar to this:

date        foo     bar     baz     boo
----------------------------------------
2015-11-18  1       null     2.5    81.0
2015-11-19  1       78.4    12.8    null
2015-11-18  2        2.5   999.9    83.0
2015-11-19  2       null    18.0    82.4
2015-11-20  2       99.0    71.0    80.0
2015-11-18  3       32.1    18.0    null
...

In this example foo is a foreign key each row can be associated with, and date is an index. I've been thinking about two distinct ways to model this structure, and my question to people with much more database experience is which structure would be better, especially in terms of performance?

The first structure has the following columns:

date (int, pk)
foo (int, fk, pk)
bar (float)
baz (float)
boo (float)

And the other is:

date (int, pk)
foo (int, fk, pk)
label (varchar, pk)
value (float)

While the latter is better normalized, my main question is which would execute a query faster -- a particular example is finding all baz values when foo == 1.

And a final note: it is expected that the table will grow very large, with the number of individual values approaching billions in numbers.

UPDATE: Forgot to note that some values can be null.

NOTE: The only reason why I'm even considering the latter option is that it is highly likely that further columns/labels will have to be added later, and I'd like to avoid changing the schema if possible.


texts: DB2: Database alias already exists when cataloging

texts:
SQL1005N  The database alias "XYZ" already exists in either the local database directory or system database directory

I am an accidental DB2 LUW on linux administrator; primary background in MSSQL and Oracle. We have an application that has an integrated DB2 Enterprise server as part of the stack. Within that, the application creates databases based on user defined workloads. When those workloads are dropped, the databases are removed as well. Unfortunately, I've reached a scenario where i need to reuse database names. For example, the databases are named like WORKOB0[0-9][0-9] and we've exceeded 99, historically.

If I manually create database WORKOB01, the create is successful. However, when I attempt to catalog it, I get the error message above. The databases do not exist in the user directory, no question about that. However, there is a twist. The application creates databases AT NODE 'HOMEDB01'. When I use commands like list database directory, no objects are present. If I use list node directory, it states that "the node directory cannot be found".

I'm lost. I've spent several hours on this and I'm in a position to say: I have no idea what I'm doing!


texts:
In clustered index, we have root, intermediate and leaf level pages. Every page has few records where indicating range of pages from level below.

How SQL check whether some index key belong to specific range, that is, which operation it uses for that?

Is it cursor, which traverse through all rows from one page, or something else?