texts: Mysql max connections and event scheduler

texts:
Is there a limit to how many "instance" of a scheduled event MySQL can run? I have set this up:

CREATE TABLE IF NOT EXISTS t (c1 INT UNSIGNED NOT NULL) ENGINE=INNODB;

SELECT * FROM t;

DELIMITER $$

DROP EVENT IF EXISTS e_second;
CREATE EVENT e_second ON SCHEDULE EVERY 1 SECOND
DO BEGIN
    INSERT INTO t (c1) VALUES (UNIX_TIMESTAMP());
    SELECT SLEEP(1000);
    INSERT INTO t (c1) VALUES (UNIX_TIMESTAMP());
END$$

DELIMITER ;

SELECT * FROM t;

SHOW PROCESSLIST;

SHOW VARIABLES LIKE '%connect%';

This creates a dumb event started every second, that runs for 1000 seconds. In my query log, I indeed have:

2020-02-18T15:47:00.289050Z      1016 Query     INSERT INTO t (c1) VALUES (UNIX_TIMESTAMP())
2020-02-18T15:47:00.430801Z      1216 Query     INSERT INTO t (c1) VALUES (UNIX_TIMESTAMP())
2020-02-18T15:47:00.430950Z      1216 Query     SELECT SLEEP(200)
2020-02-18T15:47:01.290518Z      1017 Query     INSERT INTO t (c1) VALUES (UNIX_TIMESTAMP())
2020-02-18T15:47:01.430943Z      1217 Query     INSERT INTO t (c1) VALUES (UNIX_TIMESTAMP())
...

So the event is run every second, and hangs (on purpose). Now, the processlist shows (after a while):

"559"   "root-heidisql" "127.0.0.1" "test_sql"  "Connect"   "702"   "User sleep"    "SELECT SLEEP(1000)"
"560"   "root-heidisql" "127.0.0.1" "test_sql"  "Connect"   "701"   "User sleep"    "SELECT SLEEP(1000)"
"561"   "root-heidisql" "127.0.0.1" "test_sql"  "Connect"   "700"   "User sleep"    "SELECT SLEEP(1000)"
"562"   "root-heidisql" "127.0.0.1" "test_sql"  "Connect"   "699"   "User sleep"    "SELECT SLEEP(1000)"
"563"   "root-heidisql" "127.0.0.1" "test_sql"  "Connect"   "698"   "User sleep"    "SELECT SLEEP(1000)"
...

So I have about 528 rows in this processlist now. But, my connections limit is 151 as shown by showing variables:

"max_connections"   "151"

So clearly, the running events are not part of the connections limit. Then, what is the limit? Is there one?

It feels to me that at some point, some mysql queue/list/whatever will be full of the pending events... Is this case documented somewhere?


texts: How to determine the root cause of a performance spike during a heavy load on DB2?

texts:
I am testing the performance of an application that links to a DB2 database engine at the back end.

Looking at the transaction timer logs, I see that on the 37'th minute into the test run, transactions take a very high time to process. This lasts for maybe a few seconds and then the run proceeds at acceptable speed.

I've done three test runs (restoring the data to initial test data, clearing up everything = basically, ensuring each run has the same starting position) and they all experience the same phenomenon: at the 37'th minute, a performance spike:

My current lead is that I notice, on the second run it's more visible, that every 37-ish minutes, there's a "minispike".

I think what's going on is the DB2 is doing some regular maintenance every 37 minutes, and the first maintenance job is a bit harder, as the machine hasn't cached a required program or...?

I am wondering what advice can I get to investigate this further? Debugging my own application did not show any reason to hang as it does there.

Info:
DB: DB2 10.5.0.7
OS: SLES 12 0
App: Precompiled to bind to DB2 in 64-bit mode


texts:
I've been reading about the benefits for all triggers and procedures having "set nocount on" implemented unless you make use of DONE_IN_PROC.

I have a scalar-valued function that is called from an update trigger to calculate the sum of values across multiple tables.

Searching msdn and past articles, I haven't been able to find an explicit example of a function using set nocounton.

What is the impact on a function to include or exclude 'set nocount on'?
Is there a point or code smell in functions that should indicate the addition of set nocount on?


texts: Constructing an SQL query with time intervals

texts:
I've a simple but large table in an SQL database which has four fields:

id    (int PK)
time  (unix timestamp, not null)
value (double, not null)
ID_fk (integer foreign key)

Data is recorded every minute, but at a seemingly random second for subsets of sensors, each sensor has a foreign key value though.

My question is this:

I need to write a query that will tell me when, over all two minute intervals in the dataset, a series of sensors have a value greater than 0.9.

I was thinking for trying to create a temporary table and do it that way, simplifying the time by only storing it at minute resolution?

Any advice would be greatly received.


texts:
So I'm recently starting at a new company and have a lot of ArcGIS users who seem really keen on going forwards with a PostGIS instance to serve some data to our customers. While I don't have an issue with this, we are a 95% SQL Server and 5% Oracle shop. Our current internal GIS runs off SQL Server and I have yet to hear any complaints.

I know SQL Server has a lot of improved spatial / geometric capabilities as of 2012, but are there any killer features in PostGIS that are worth breaking into the new platform for? I've tried to research it but can't find anything truly in depth or that isn't completely bias.

I want to give them the best tools to get their work done, but also have to weigh the fact that I'll be learning Postgres/GIS from the start and that's an entire journey in and of itself.


texts:
UPDATE RBI.RBI_OV_F_DETAIL A
     SET (BRANCH_ID, BRANCH_NUMBER) = (
           SELECT PRICE_BRANCH_ID, PRICE_BRANCH
             FROM RSE.RSE_SO_F_ORDERS B
                WHERE A.SOURCE_ID = B.FULL_ORDER_NUMBER AND A.METRIC_GROUP_ID='1');

I am getting error in this syntax:
single query returns multiple rows

Please suggest what to do.


texts: Mysql Or MariaDb for high traffic server

texts:
What server should I choose to install for a server that will host mostly shopping carts?

I'll have average 10-20K products in store and some tables with up to 1 million rows (cache tables for filtering).


texts: Is SQL Server Backup affects the query execution performance? Is there an effect while running the SQL Server backup in remote query execution? Thanks.


texts: can we transfer sql database backup from one server to another server directly?

texts:
I want to transfer sql database from one server to another server directly, instead taking back up of that database and copy that back up in another server and then restore it.

Please tell me, Is there any way to do it?


texts: Pgpool II : unable to read message length between two network interfaces

texts:
Start log file:

2015-03-06 01:57:56: pid 2760: LOG:  Setting up socket for 0.0.0.0:9999
2015-03-06 01:57:56: pid 2760: LOG:  Setting up socket for :::9999
2015-03-06 01:57:56: pid 2760: LOG:  pgpool-II successfully started. version 3.4.
0 (tataraboshi)
2015-03-06 01:57:56: pid 2760: LOG:  find_primary_node: checking backend no 0
2015-03-06 01:57:56: pid 2760: LOG:  find_primary_node: primary node id is 0
2015-03-06 01:58:02: pid 2792: ERROR:  unable to read message length
2015-03-06 01:58:02: pid 2792: DETAIL:  message length (8) in slot 1 does not mat
ch with slot 0(12)

Debug log file:

2015-03-06 01:55:07: pid 2680: LOG:  find_primary_node: primary node id is 0
2015-03-06 01:55:42: pid 2712: DEBUG:  I am 2712 accept fd 6
2015-03-06 01:55:42: pid 2712: DEBUG:  reading startup packet
2015-03-06 01:55:42: pid 2712: DETAIL:  Protocol Major: 1234 Minor: 5679 database
:  user:
2015-03-06 01:55:42: pid 2712: DEBUG:  selecting backend connection
2015-03-06 01:55:42: pid 2712: DETAIL:  SSLRequest from client
2015-03-06 01:55:42: pid 2712: DEBUG:  reading startup packet
2015-03-06 01:55:42: pid 2712: DETAIL:  application_name: psql
2015-03-06 01:55:42: pid 2712: DEBUG:  reading startup packet
2015-03-06 01:55:42: pid 2712: DETAIL:  Protocol Major: 3 Minor: 0 database: post
gres user: enterprisedb
2015-03-06 01:55:42: pid 2712: DEBUG:  creating new connection to backend
2015-03-06 01:55:42: pid 2712: DETAIL:  connecting 0 backend
2015-03-06 01:55:42: pid 2712: DEBUG:  creating new connection to backend
2015-03-06 01:55:42: pid 2712: DETAIL:  connecting 1 backend
2015-03-06 01:55:42: pid 2712: DEBUG:  reading message length
2015-03-06 01:55:42: pid 2712: DETAIL:  slot: 0 length: 12
2015-03-06 01:55:42: pid 2712: DEBUG:  reading message length
2015-03-06 01:55:42: pid 2712: DETAIL:  slot: 1 length: 8
2015-03-06 01:55:42: pid 2712: ERROR:  unable to read message length
2015-03-06 01:55:42: pid 2712: DETAIL:  message length (8) in slot 1 does not mat
ch with slot 0(12)

psql command:

[enterprisedb@testing_ppas93]$ psql -h localhost -p 9999 -d postgres
psql: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request

I use pgpool II to control failover event between two servers (master on google cloud, slave on my local, centos x64, database: Postgres Plus Advanced Server 9.3). When I try to connect my database via port 9999 (example: psql -h localhost -p 9999 -d postgres) , pgpool raises the error

  ERROR: Unable to read message length


texts: Schema design for metered billing depending on account usage

texts:
Sorry if this has been asked somewhere else, but so far I haven't found any questions like this, or can't find the words to search for.

TL;DR: What's the best way to implement counters in the database to track usage for billing purposes? Specifically when those counters will reach into the 10's or 100's of thousands?

I have a LAMP application that allows users to send out emails (Laravel, MySql).  I want to start charging them for "email usage" and imposing monthly sending limits, which if they exceed, I will charge them extra, rounding to the next thousand.

So from a database standpoint, how can I make this happen in a way that is stable and maintains integrity, while not burning up all my CPU with updating counters?  I also would like to record usage month by month for good record keeping.  Here is what I'm considering:

accounts:
    account_id
    company_name
    email_limit (counter integer, example might be 20,000)
    email_usage (counter integer, example might be 13,425)
    ...

users:
    user_id
    account_id
    email
    ...

email_history
    account_id
    period (date field or something to know which month it was)
    email_usage (the historical usage they used that month)
    email_limit (the historical limit they had that month)

In this scenario, my system would process, let's say 5000 emails at once, and increment the email_usage counter 5000 times.  On the other hand, I could just use an internal counter ($i) and when the process is complete, just add $i to the usage counter.  But what if there's an error during processing?  I could lose my counter half way through.

Another way I could think to do this would be to save batch operations, so when we process 5000 emails at once, we just record that:

accounts:
    account_id
    company_name
    ...

users:
    user_id
    account_id
    email
    ...

email_batches
    account_id
    date (date batch was processed)
    emails_sent (integer, total emails in batch)

That way, when it comes time to process billing each month, we just SUM(email_batches.emails_sent) WHERE MONTH(date) = [this_month] and process that.

We're going to be billing people based on this mythical usage tracking, so any advice from someone who's done it before would be appreciated.

UPDATE: My project's been put on hold for a short bit, but I will definitely mark an answer when I start working on it again (this is a high priority, so it won't be long).


texts:
I'm starting off with a fresh mysql-server installation and am trying to import data into it. I do CREATE DATABASE mydb; and then do the following:

mysql mydb < mydb_schema.sql

mydb_schema.sql was generated by doing mysqldump mydb --no-data > mydb_schema.sql.

The problem is that mysql mydb < mydb_schema.sql is stopping midway through with the following error:

ERROR 1813 (HY000) at line x: Tablespace '`mydb`.`mytable`' exists.

Here's what's in the mydb_schema.sql file:

DROP TABLE IF EXISTS `mytable`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `mytable` (
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Those are the only two times mytable is referenced in that file. Further, the word "TABLESPACE" does not occur in that file.

https://stackoverflow.com/a/21692066/569976 doesn't really seem applicable since this is a from scratch import idk.

Any ideas?


texts: Changing master database compatibility level

texts:
We did an upgrade of SQL Server 2014 to 2016. In the development environment we did an in-place upgrade, which left the master database at compatibility level 120. But in production we did migration to new server so there master is compatibility level 130.

I would like to have both environments as similar as possible. Additionally we have one application which is running its queries from the master database, so theoretically they can behave differently between environments.

Is it safe to change compatibility level of the master database from 120 to 130?


texts: MariaDB/mySQL is there a way to limit a user to SELECT and SHOW queries?

texts: We have a support team member who we'd like to limit his access to our database. He has never caused any problems with UPDATE or DELETE but we'd like to limit him to SELECT/SHOW/etc if it's possible. Is there a way? I'm not talking about limiting the number of queries, but the types of queries he can run.


texts: Is there a way to construct SQL statement to return desired value only if there is a single row found, and return 0 otherwise?

texts:
Consider below:

    SELECT
        customer.contact.id
    FROM customer.contact
    WHERE
        customer_office_id = [INTEGER]

If only a single row is returned, I want the result set to return the customer.contact.id, but if 0 or more than 1 rows are returned, I want the SQL statement to return 0 (that is, despite that there are multiple rows, or no rows, I want SQL to return a single row with a value that is 0)

Note - if there is a special SQL case for when only 0 or 1 rows returned are possible, I'd be interested in that as well, i.e if there are no more than 1 rows possible given the data.


texts: How to transfer 3.5 million records from one Access 2010 table to another

texts:
Because my table has over 3.5 million records I'm having issues with changing the data type in certain fields in the original table (Table A).  Since it's so big I can't simply copy and paste the entire table and if I went with the 65,000 record limit, I'd have to do it 54 times, which I'd rather not do.

I created a "Copy of A" (structure only), fixed the data type and tried an append query, but that didn't work either.  It couldn't open the table.

Anyone have any suggestions?


texts: Why SQL Server has 900 byte index size limit

texts:
Why can't we create an index having size greater than 900 bytes? This question was raised in a presentation to which no one had an answer.

I can think of this way to answer this question.

Page size 8K with usable 8060
900 bytes * 8 = 7200 bytes (900*9 = 8100), so only 8 records can fit in a page at max. If this index is a non clustered, it would also need to store clustered index key which can again be 900 byte long
i.e.
For a single record memory required would be (900 + 900 bytes) i.e. 4 records per page can be stored.

If only 4 records can be stored in a page, IO operations overhead compared to performance  gain would nullify, hence index size limit is 900 bytes.


texts: Sum of last value of multiple measurements in InfluxDB timeseries data

texts:
I have a database some_db with multiple measurements first_measurement, second_measurement that contain timeseries data. I want to query the sum of the most recent value of each of these.
I tried entering the database and then querying this sum via:
SELECT last("value") FROM "first_measurement" fill(previous) + last("value") FROM "second_measurement" fill(previous)

receiving the error:
ERR: error parsing query: fill must be a function call


texts: Difference between mongod and mongodb upstart jobs

texts:
I just noticed that there are two possible ways to start mongodb:

service mongodb start (uses /etc/mongod.conf)
service mongod start (uses /etc/mongodb.conf)

Can someone tell me what's the difference between the two?

I'm running mongodb 2.6.4 on Ubuntu 14.04 (64 bit)


texts: How to prevent misuse of rights in monitoring application?

texts:
I have a monitoring application that runs on the computer of each user in my organization. This application needs to insert some monitoring data in several tables of a database (MS SQL Server 2008 R2). We are using Windows authentication.

If I give INSERT rights or grant access to execute a stored procedure, how will I make sure that the users cannot misuse these rights and say for example, add a lot of unwanted data in the tables?


texts: Internals of backup compression with TDE (SQL Server)

texts:
Does anyone have any (inside?) knowledge of how backup compression works with TDE on SQL Server (version 2016+).

In general I would expect compression of encrypted data to have minimal benefit, however we notice that with TDE, when we do a full back up with compression we still see a significant reduction in the backup file size. That has made us wonder whether the backup process decrypts the data, compresses it, encrypts that result, then backs up that to disk. Obviously that is going to increase the CPU consumption for the backup process due to the need to decrypt and re-encrypt data.

Details

select @@version

  Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0  (Build 17763: ) (Hypervisor)'

TSQL Scipt

  BACKUP DATABASE TestTDE TO DISK = 'c:\Test\TestTDE_Compressed.bak' WITH COMPRESSION, MAXTRANSFERSIZE = 65537

Or is the space saving achieved in a different way?


texts: How to give access to a user on only two specified procedures and specified tables on SQL Server?

texts:
We would like to introduce an external orchestrator in order to feed some tables from two stored procedures on SQL Server. However, we will need to create an 'orchstr' user for the orchestrator.
We want to give access to the user 'orchstr' to execute only the two specified stored procedures (so there is a restriction on the other procedures). And access on the tables to be fed only.
Is it possible? How could we do this?
I'm still a beginner, it would be too kind of you to help me.
We have SQL Server 2012 Enterprise Edition.


texts:
We have a PostgreSQL database 'db1', which is having around 500 schemas.
I am trying to create a read-only user for this particular PostgreSQL database.

I could find the following command for creating a user and granting permission schema wise

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO readonly_user;

but

1) We have around 500 schemas, so granting permission to each schema is difficult.

2) These schemas will be dynamically created or dropped on a daily basis, so giving permission at each time a schema is created is also a difficult task.

Is there any way to give the read-only permission for a whole database instead of schema?

In MySQL, I can do it using the following command

grant select on *.* to 'user_name'@'IP';

I am looking for a similar command in PostgreSQL

We are using PostgreSQL 10.


texts: Select a table with multiple columns according to IN clause

texts:
I've a query

SELECT t1.user,t1.answer as 'Q1',t2.answer as 'Q2'
from
(select answer,user from survey_answer where question_id=45) t1
JOIN
(select answer,user from survey_answer where question_id=46) t2
ON
t1.user=t2.user

In this the question_id i.e. 45,46,47,48,...etc. is in hundreds
the number on join increases number of columns as q1,q2,q3... so on...

like :

________________________
user | q1 | q2 | q4 | q5
------------------------
1    | ok | yes|    | no
------------------------
2    |    | yes| no |

Is there a way to form the query using IN(45,46,47,....) or use loop in procedure/function...


texts: Stored proc getting ANSI_NULLS and ANSI_WARNINGS error when executed by application

texts:
I have a stored procedure that is getting the following error when executed via an application :

  Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

This stored procedure has a SELECT statement in it that joins to a linked server table to return results. If you execute this stored procedure directly in SMS, there is no problem whatsoever. If I execute the stored procedure via a script through an ODBC connection, then there is no problem. The only way to generate the error is when the application tries to execute it.

When viewing the application, there are no errors. In fact, it looks like it runs successfully, except there are no results. Only way we were able to identify this error is through running a SQL Trace.

The trace shows that none of the proc is ever actually running, instead it is just erroring immediately with that error. Trace also shows that it is not executing in a different way (such as exec sp_executesql or something like that). I can see the stored proc start executing, then get the error, and then stop.

I have researched this a ton and the answers seem simple enough, but nothing has worked.

I tried adding SET ANSI_NULLS ON & SET ANSI_WARNINGS ON to the stored proc, even moving it around different places in it. But never able to get it to succeed

Also, tried adjusting the database level settings -

ALTER DATABASE [NGDevl] SET ANSI_NULL_DEFAULT OFF WITH NO_WAIT
GO
ALTER DATABASE [NGDevl] SET ANSI_NULLS OFF WITH NO_WAIT
GO
ALTER DATABASE [NGDevl] SET ANSI_WARNINGS OFF WITH NO_WAIT
GO

But did not work, even after rebooting the SQL Instance.

The only other thought I had would be to have the application execute the proc with the settings part of that, so like ---

SET ANSI_NULLS ON;SET ANSI_WARNINGS ON;exec sp_storedproc 0,0,'A','0001'

But unfortunately, we dont have control over the application to try that.

Also, recreated the stored procedure so that it pulled all of the data needed from the linked server over into a temp table so it was local, and then joined to there, but same issue.

Do you guys have any ideas or something to try here. The error seems like it should be something pretty simple to fix, just not sure what I am missing.


texts: Using PostgreSQL both with (not only) SQL Workbench - how to apply alternate delimiters

texts:
I have created a PostgreSQL database. Now I try to work with it in SQL Workbench, but I have a problem with the way it understands statement delimiters. The documentation on alternate delimiters sais that SQL Workbench takes semicolon as the end of statement, which causes problems for functions and similar code blocks with more than one semicolon. That's why there's the 'alternate delimiter' option. However, the default alternate delimiter (/) makes the code invalid elsewhere - I picked SQL Developer for a workaround and as an alternate front-end, I can't sacrifice pgAdmin for it and I can't afford maintaining two versions of my source code just to avoid this limitation.

I tried to set --/ as the alternate delimiter instead - this disables the default '/', but it doesn't apply - seems that SQL Workbench can't handle multi-char delimiters.

Is there any way how to solve this problem? Is there any char that wouldn't make me trouble in pgAdmin but would be suitable for SQL Workbench alternate delimiter? Or is there any way to enable alternate delimiters consisting of some harmless char sequence?

EDIT: I tried to define the alternate delimiter both at global and connection settings, and with and without the "single line" option, and no success.


texts:
Have anyone experienced this?

  String or binary data would be truncated

Raised during ALTER INDEX; even in CREATE TABLE. I'm not having this problem in other databases. I may have reconfigured something as this is only a test database.

All I am seeing in the internet is regarding length of strings inserted by INSERT statement exceeds in the length of defined data type.


texts: Detect if `pg_crypto` in Postgres was built with strong random feature enabled

texts:
The source code for pg_crypto module mentions an option to build without cryptographically-strong random generation.

/*

Generate random bits. pg_backend_random() will do here, we don't promis

UUIDs to be cryptographically random, when built with

--disable-strong-random.

*/

Discussed further in this thread.
In the particular build I may be using, how can I detect if the strong random feature is enable or disabled?


texts:
Is it possible to know how many times a user connected to an Oracle database?

If possible where we can find this information?


texts: "show procedure status" displaying the non existing databases stored procedures in mysql server

texts:
i have taken a mysql server backup using mysqldump and imported the backup file in another mysql server.Import failed because of disk full issue. Only 11 databases are created out of 15 . when i issue show procedure status command,
i can see the procedures related to the missing databases( 4 databases) also along with the created databases in the server.when i debug this , i felt like when i have  backedup mysql database and import it, these procedures would have come through mysql.proc table and show procedure status is referring to mysql.proc table not the existing databases and their procedures in the server.
My question is please let me know whether my understanding is correct or not regarding the above.


texts:
I asked question in very blurry manner because I cannot explain in short sentence what is my problem about.

In general I have a set of procedures which transforms text and XML. Inside the procedures I have a lot of NVARCHAR and XML variables and a table with XML column. Inside my data I have special letters (for example ò). When I run all the code the special character disappears and is visible as ? - which mean that I got VARCHAR instead of NVARCHAR.

In general I got everything ok until the final steps. I got EXEC which call stored procedure with ad hoc constructed variable of type NVARCHAR. I have into a table which contains XML field. In this case I got ?. If I run procedure manually (not in EXEC INTO) I got proper symbol.

Any ideas why this happens?
I checked all variables and they are NVARCHAR.


texts: How would you make your case for RDP access to a Data Warehouse?

texts:
We are moving our Data Warehouse to a hosting company, and they don't want to give RDP access to the server. This kind of bothers me, because it will make my life more difficult. Generally, I tend to shoot myself in the foot when making my case for things like this. My go-to script is that I can make it work with whatever I'm given, because I'm that awesome. If my server enviroment was a space ship, it would be like Rick's from Rick and Morty. That's starting to bother me.

I know that you actually don't need to RDP into SQL server very often. But I still want the ability to do so.

Question: How would you make your case for getting RDP access to your SQL Server?


texts: Strategy for setting up a 60gb MySQL database from a dump?

texts:
I have been trying to set up a 60gb MySQL database from a dump. I loaded it into MySQL Workbench on my standard-issue dual-core 2.4 Ghz, 4gb RAM, 2011 Macbook Pro. After roughly 24h, it had only been able to load a few percent. During other attempts MySQL Workbench crashed completely. Doing the same thing on the command line generates roughly the same results.

What is a viable strategy for loading a 60gb MySQL database? Do I need to buy a very powerful computer? How powerful? Is there a way of loading it onto an Amazon Web Services instance that is feasible?


texts: move a database on sql server 2017 with memory OLTP feature

texts:
I have a database on sql server 2017 standard edition with in memory OLTP feature enabled, I have to move it on another disk, which is the best way to do it? are there any possible problems?

Can I manage to get rid of this OLTP feature? Somebody said to drop an re- create the db ? but how ... preserving the data ?

I have this script :

   SELECT type_desc, name, physical_name from sys.database_files
   USE master
   EXEC sp_detach_db mydb
   CREATE DATABASE mydb ON
   PRIMARY ( NAME = mydbdata,
       FILENAME = 'Y:\Database\SQLServer\DATA\mydbdata.mdf'),
   ( NAME = mydbdata2,
       FILENAME = 'Y:\Database\SQLServer\DATA\mydbdata2.mdf'),
   FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = mydbdata_mod1,
       FILENAME = 'Y:\Database\SQLServer\DATA\mydbdata_mod1')
   LOG ON  ( NAME = mydbdata_log,
       FILENAME = 'Z:\Database\SQLServer\DATA\mydbdata_log.ldf')
   ,  ( NAME = mydbdata_log2,
       FILENAME = 'Z:\Database\SQLServer\DATA\mydbdata_log2.ldf')
   FOR ATTACH
   GO

update:
this dba.stackexchange.com/questions/52007/… replies to the question how to move , and it's ok but there are a lot of methods ... although I am confident with all methods. My fear it's all about the OLTP feature and the OLTP files


texts: How do I Continue After a Failure in SSIS

texts:
I want to implement something like the following:

In this package, I want to loop through a list of database names, dynamically modifying connection manager settings.

However, the first failure on "Test Connection" will halt and fail the package execution.

But I'd like to continue my Foreach loop. I get the sense that this should be possible (otherwise what's the use case for these red failure arrows?)

Things I've tried include messing with "MaximumErrorCount".


texts: Set up sql server db mirror specifying IP address for mirroring_partner_instance

texts:
Ok. I run:

ALTER DATABASE DbName SET PARTNER = 'tcp://10.0.0.0:5022'

Command completed succesfully
Then run

SELECT DB_NAME(database_id) AS 'DatabaseName', mirroring_partner_instance
FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL;

mirroring_partner_instance comes back with a machine name.
I have machines on a different lan that won't recognise the machine name. I need it to come back with an IP address because this value is then cached at other .net applications for failover. (because of the brilliant geniuses who decide the "Failover Partner" value in a connection string should be completely disregarded)


texts: One SQL Server RDS instance plus one Ubuntu instance cost money on my initial free trial year

texts:
Why do I get charged when I only have one micro instance running as Ubuntu and another one a RDS. Does the extra RDS count as a separate instance? My RDS is Microsoft SQL Server exchange which should be free.

I wonder if there is any place where I can get started with node and a database that isn't as annoying as aws. Their documentation is horrible. It feels like the medical system. I never know what will cost me until I get the bill and then it's always surprising.


texts: SQL Update Efficiency and Blocking Concern

texts:
Say I have some SQL similar to the below, which is an update with many joins. The number of rows actually being updated may be 1000's or potentially only a few. The actual join takes a long time as there are millions of rows involved, probably around 30s. Would the below only lock the trade table t on the rows that need to be updated or is it being locked as the joins are underway? So would it be better to use a CTE or temp table to gather this data and then update?

I'm having deadlock issues and wanted to make sure updates like this aren't part of the problem.

update
t
set
t.price = d.latestprice * f.rate
from
trade (nolock) t
inner join tradedetails (nolock) d on d.ID = t.ID
...
...
inner join fxdetails (nolock) f on f.CCY = t.CCY


texts: Select rows where smaller row does not exist

texts:
Consider the following table data:

ID NAME
1 Kshitiz
2 Aman
5 Aakash
6 Manjeet

Here there are records missing between 2 and 5. What query can I use to select row 5 in this table?


texts: Replicating from master to slaves and from slaves to master

texts:
I have a master and multiple slave machines. All SQL operations on the master node should be sent to the slave nodes. But the tables on the slave nodes may also be written to locally. These local changes on the slave node should be reflected to the master node.

For example let's say I have a master machine, lets call it "M", and two slave machines lets call them "A" and "B"

All these machines have a table named test_table with a column named "id".

I insert data to the test_table on M machine.

(M)test_table
   |id|
   |1 |

(A)test_table
   |id|

(B)test_table
   |id|

now this change is reflected to the slaves:

(M)test_table
   |id|
   |1 |

(A)test_table
   |id|
   |1 |

(B)test_table
   |id|
   |1 |

Now on slave A, I make a local change.

(M)test_table
   |id|
   |1 |

(A)test_table
   |id|
   |2 |

(B)test_table
   |id|
   |1 |

Now this change is reflected to the master server:

(M)test_table
   |id|
   |2 |

(A)test_table
   |id|
   |2 |

(B)test_table
   |id|
   |1 |

And then master server replicates this change to the slaves:

(M)test_table
   |id|
   |2 |

(A)test_table
   |id|
   |2 |

(B)test_table
   |id|
   |2 |

What is this kind of replication named? And how can I achieve this on postgresql?


texts:
I am trying to use bulk insert to insert data from a csv file into a sql server table, but it is returning 0 rows.

This is the bulk insert statement I am using

BULK INSERT dbo.DEA_Availability from 'C:\SQLPOWERDOC\CSV\DEA_Availability.csv'
with (
FIRSTROW=2,
FIELDTERMINATOR=',',
ROWTERMINATOR='\r\n',
ERRORFILE = 'C:\SQLPOWERDOC\CSV\DEA_Availability_ERROR.log',
DATAFILETYPE='char',
keepnulls
)

The table definition is:

CREATE TABLE [dbo].[DEA_AVAILABILITY](
    [Server_Name] [varchar](max) NULL,
    [Database_Name] [varchar](max) NULL,
    [Priority] [varchar](max) NULL,
    [Description] [varchar](max) NULL,
    [Details] [varchar](max) NULL,
    [URL] [varchar](max) NULL,
    [IMPORT_DATE] [datetime2](7) NULL
) ON [PRIMARY]
GO

This is the contents of the CSV file

Server Name,Database Name,Priority,Description,Details,URL
HOSTNAME,,None,Cluster Node,This is a node in a cluster.,http://msdn.microsoft.com/en-us/library/ms189134(v=sql.100).aspx

I have verified that each line of the CSV file does end with \r\n


texts: Create phone call with Mockaroo for Neo4j

texts:
I'm following this guide to create a dataset with a call log.
But i don't understand the datatype used for the Date fields and the condition for Start_Date, End_Date and Duration to have coerent values (for example date_start = 10/10/10:1050 date_end=10/10/10:10.55 DURATION = 5)


texts:
I am a little bit confused about pricing for Data in Azure.
I found on one MS Blog that 50GB has a price of $125.

Missing part for me is, if I have needs only for a database in the cloud:

Do I pay only the amount of $125?
Or I have to pay separately for RAM, storage, processors and others parts which one server needed to operate?
What is the story about the performance of an Azure database?
Do all the databases have the same resources available to operate?


texts: How can I insert into a Destination Table from multiple tables concurrently..?

texts: I have a monthly single threaded process that restores about 2,000 databases and pumps data into a table then drops the DB. It takes about 18 hours for the insert to complete (Its via an SSIS Package). How can I make this multi threaded; avoiding locking/deadlocks on the destination table. I have thought of partition switching but it won't work because the destination has to end into one partition in the destination table.


texts: Insertion into postgres database stops (hangs) after table reaches a certain size without error

texts:
We are currently trying to insert a large amount of data, about 27 million records with 200 columns, into a single table in a postgres 9.4 database.  (Yes it is probably better to refactor the table into smaller tables but trying this method first)

The insertion is managed by a libpq application that inserts around 300 rows of data at a time using "INSERT INTO ...".  We've read about using the COPY command, but for our current usage, the former was a better choice with decent performance.  Some columns are arrays, which can be fairly large, but using this setup, we had no problem inserting around 10 million rows of data.

Problem: Whenever the table gets to around 600GB (~20 million rows), the insertion just stops.  The pg_stat_activity shows the insertion query as active, and there are no errors in the output nor in the log. We've waited for more than 12 hours for an insertion query that usually takes a few seconds.

We've tried a few things like dropping the only index (primary key), running ANALYZE, and restarting the insertion, but whenever the table gets to around 600GB, the insertion stops.  We don't believe we're hitting anything close to the maximum size of a table, so not really sure what is happening.

Does anyone have any idea? Or have some advice on how to track down what is going wrong during the insertion?

Thanks!

Update #1: As stated above, the rows being inserted are 200 columns wide, with some fields being arrays.

When the insertion to the table stopped, we experimented with manual insertion of records:

A typical row would not be inserted and the query would hang as we have previously seen.  When we reduced the number of populated columns (e.g. insert a record with only 30 out of the 200 values), then the insertion was successful with no delays/slow downs. By varying the number of populated columns in the query, we found a cutoff point where insertion would hang if the number of columns being inserted became larger than a certain number. (We also played with different combinations of columns, it did not have any effect)

We have no idea on how to directly address this problem, so we are currently proceeding by partitioning the data as suggested into different (smaller) tables.  However, this problem is not mentioned in any documentation, so any advice would still be appreciated!


texts: Is it bad practice to add columns to an existing table?

texts:
Conversely:  Is it better to get all the columns created at the time when a new table is created?

I'm working on a new system and new requirements are coming up all the time.  The latest requirement is to add a new tag field to each customer so that it will be easier to correlate data between the old and the new system.  The new system is not in production yet, but some test runs of the migration process have been done.

At the moment it is still viable to delete the table and rerun the batch load, but what about the future - when there is a lot of live data in the system and a requirement for a new column comes up:  Does it have any impact on performance for example, to export the data, recreate the table with all the columns, and import the data again, over just doing an ALTER TABLE ADD column .... ?

In case it makes any difference, the solution is based on PostgreSQL 9.5, and if it does make a difference it would be interesting to know which DBMS'es care more or less.

Does whether or not an index is created on this column affect the answer?  E.g when a Unique constraint is set.


texts:
I have what I believe is SQL Server 2008 R2 Express installed on a production server. When I connect to it with SQL Server Management Studio it shows up as SQL Server 10.50.1600.

If it is in fact SQL Server 2008 R2 Express, my understanding is that the database size limit is 10 GB. However, something is just bugging me because I am close to 1 GB in size and there is a message on the properties screen that is scaring me. The database is running in a production environment. There were a lot of rows in it and I deleted a lot, but it has not changed the message. When I go to the properties for the database it says "Size  1005.75 MB" and right below it says "Space Available  0.03 MB".

I tried to use "Shrink" but it just flashes and goes away. Is my production server about to crash because the database is too big? Why would that happen at 1 GB when I thought the server should allow 10 GB? How can I reduce it? Deleting records for inactive clients has done nothing.


texts:
We recently encountered PFS page corruption in our production DB.  We are in the process of checking old backups to find which ones are clean, but I was wondering if a different process would work to shorten the time needed to fix this issue.  So far we have not encountered any client issues, and the DB remains usable, but obviously need to fix the problem.I know we can't repair the individual PFS pages, but would the below work?

Msg 8948, Sev 16, State 6, Line 1 : Database error: Page (8:15390050) is marked with the wrong type in PFS page (8:15383376). PFS status 0x40 expected 0x60. [SQLSTATE 42000]
Msg 8948, Sev 16, State 1, Line 1 : Database error: Page (8:15390051) is marked with the wrong type in PFS page (8:15383376). PFS status 0x40 expected 0x60. [SQLSTATE 42000]
Msg 8948, Sev 16, State 1, Line 1 : Database error: Page (8:15390052) is marked with the wrong type in PFS page (8:15383376). PFS status 0x40 expected 0x60. [SQLSTATE 42000]
Msg 8948, Sev 16, State 1, Line 1 : Database error: Page (8:15390053) is marked with the wrong type in PFS page (8:15383376). PFS status 0x40 expected 0x60. [SQLSTATE 42000]
Msg 8948, Sev 16, State 1, Line 1 : Database error: Page (8:15390054) is marked with the wrong type in PFS page (8:15383376). PFS status 0x40 expected 0x60. [SQLSTATE 42000]
Msg 8948, Sev 16, State 1, Line 1 : Database error: Page (8:15390055) is marked with the wrong type in PFS page (8:15383376). PFS status 0x40 expected 0x60. [SQLSTATE 42000]

I used DBCC Page and discovered and all 6 pages belong to 1 table, so I am hoping this will work so we don't need to restore from old backups, but everything I've read said its either restore from backup, or repair allow data loss.  Also, what is the 0x60 status?  using DBCC Page shows the 0x40 status is Allocated, so i am curious what the status of 0x60 is.
What do people think about this process?  Would it work to clear the corruption?

Copy all data to a backup table - New data pages should be assigned, which would mean the PFS page entries for these pages should be clean.
Delete the source table - I assume this removes the bad PFS page entries since the table is no longer there...  But can SQL Server remove the errors at all if the PFS pages are corrupt, or would they remain even after deleting the table?
Rename the table back to the source table - Hopefully this will solve the issue since new data pages and new PFS page entries were created in step 2.

Has anyone attempted this or heard of ways to deal with PFS page corruption without going back to an old backup?


texts: Is there a way to see what were the Candidate Execution Plans generated by the Query Optimizer?

texts:
The Query Optimizer creates more than one possible execution plan. How can I see all the plans generated before the executed one was chosen?

Microsoft says:

  The Query Optimizer must analyze the possible plans and choose the one
  with the lowest estimated cost.

Please, notice that I'm not talking about Execution Plan Caching and Reuse, I'm talking about candidate execution plans, the ones that were generated but not selected to be executed.

According to Benjamin Nevarez's article The SQL Server Query Optimizer:

  The generation of candidate execution plans is performed inside the
  Query Optimizer using transformation rules, and the use of heuristics
  limits the number of choices considered in order to keep the
  optimization time reasonable. Candidate plans are stored in memory
  during the optimization, in a component called the Memo.

Would it be possible to visualize the candidate plans rendered the way we can Display an Actual Execution Plan?


texts: Query to return multiple rows with same UID and entered on the same date

texts:
I've revised the post per MarkP's suggestions. Thanks for the suggestion as I am new to posting.

I have a table with the following data, UID, Order Number,Date In

I need to select the all of the rows where the Date In is the same for all UIDs , as in rows for UIDs 1 and 4, and ignore rows for UIDs 2 and 3 where the same UID has different dates. (Note that those orders may have duplicate dates as well). So far I've come up with some ways to not achieve the desired results. (I removed all of the samples except one). Since they all returned the same data, all orders with multiple entries on the same date, rather than all orders with only entries on the same date.

The results would look like this:

with uidlist as
(select s.uid,s.ordernumber, count(s.uid) as counter, cast(floor(cast(ot.DateIn as float)) as datetime) as ldate
              from service s join ordertask ot on ot.orderuid = s.uid
              where ot.datein > = '2017-04-01'
              group by s.uid,s.ordernumber, cast(floor(cast(ot.DateIn as float)) as datetime)),
uidfinal as (select count(uid) as counter, uid,ordernumber from uidlist group by uid,ordernumber having count(uid) = 1)
select ul.uid,ul.ordernumber
from uidfinal UL
order by ul.ordernumber;

Thanks for any assistance.


texts: Getting sum result for last year divided quarterly

texts:
I am trying to get financial data grouped by each quarter of last year and also grouped by the last three weeks.

Also, if it is possible, I want to have the type as a separate column with the type as the header.

This is the query I am using right now which is giving the result for last month.

Select
  to_char(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1), 'MM')
    || '-'
    || to_char(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1), 'YYYY') AS Month,

  case
     when (ap.asset = 2 or AP.ASSET_TYPE not like 'CFTD%' and
          AP.ASSET_TYPE not like 'CASH%' and AP.ASSET_TYPE != 'FTL') then
      'AUM'
     when (AP.ASSET_TYPE like 'CFTD%') then
      'Depostis'
     when (AP.ASSET_TYPE like 'CASH%') then
      'Cash'
     when (AP.ASSET_TYPE = 'FTL') then
      'Loan'
   end as Type,

   sum(ABS(AP.Eval_Market_Value)) as Total

from (select p.account,
           p.open_date    as Open_Date,
           ac.description as RM,
           s.*

      FROM k$portfolio p, k$client k
      LEFT JOIN k$client_role cr
        ON cr.client = k.id
       AND cr.role = 136
      LEFT JOIN k$actors ac
        ON cr.actor = ac.id,
     table(gtw$reporting.Statement(p.id,
                                      2,
                                      trunc(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1)),
                                      trunc(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1)),
                                      1,
                                      1002,
                                      1,
                                      'USD')) s
     wHERE s.line_type = 'P'
       and k.id = p.client
       and p.id = s.portfolio
       and p.portfolio_type = 'C'
       and p.status = 1

    ) ap

group by to_char(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1), 'MM') ||'-'|| to_char(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1), 'YYYY'),

      case
        when (ap.asset = 2 or AP.ASSET_TYPE not like 'CFTD%' and
             AP.ASSET_TYPE not like 'CASH%' and AP.ASSET_TYPE != 'FTL') then
         'AUM'
        when (AP.ASSET_TYPE like 'CFTD%') then
         'Depostis'
        when (AP.ASSET_TYPE like 'CASH%')  then
         'Cash'
     when (AP.ASSET_TYPE = 'FTL') then
         'Loan'
      end

=====================

Current output

Desired result


texts: How many LEFT JOINS make a VIEW unusable?

texts:
I have a view which involves LEFT JOINS on 20+ tables to a base table but when querying this view and looking at the execution plans I am getting something totally unexpected.

For example, with the query below I would expect a clustered index seek on the table:

SELECT id FROM dbo.TableName WHERE id = 256

Whereas when I use the view I get the clustered index seek on the table along with 3 nested loops (left outer join).

SELECT id FROM dbo.ViewName WHERE id = 256

Why would this occur?
The above is a very simple example but when selecting additional columns from the view more and more unnecessary LEFT JOINS appear in the execution plan.


texts:
We are planning to have some tests to predict how mongo db operates and functions with 2 terabyte of inforamtion on a single collection.

So the plan is to have 15-20 sharded machines (on the same environment / cabinet).
All are considering to have shared EMC storage

I wanted to ask before we start the test :

IS there any different between:
    * Having physical machines writing to independent disks (on the machine).
    * Having physical machines writing to the same storage.
    * Having virtual  machines writing to same storage.
About memory consumption, What are the hardware considerations about querying this amount of data? If I understand correctly, the advantage when you have massive writes is to do sharding with small-level servers, but is it going to change when you need to have some reads?

Thanks


texts: Why should I use Visual Studio 2010 over SSMS for my database development?

texts:
Visual Studio 2010 introduces database projects and a whole array of related features that supposedly facilitate database development. I've used SQL Server Management Studio (SSMS) for many years to do my database development without issue.

Why should I bother with VS2010 when SSMS works for me?  What, specifically, does it do better than SSMS?
But perhaps my premise is incorrect and SSMS still trumps VS for database development. If so, in what specific ways is that true?


texts: SQL Server transactional replication process and error

texts:
I need to setup a replication.

Which replication is better if I want to keep my data updated in the second server as soon as new data is pushed into the primary server.
I tried setting up by clicking on New Publication and got error message:-

So I found I need to use sp_addserver. How does it work, In which table it will add the server name. How will it affect my current setup if I change the server name. Will my APIs or other services connected to this server will start failing if I chnage the server name.

A step by step guide for whole replication setup with details would be helpful.


texts: Is there anything called "Hashed User" in the IBM DB2 version 10.1? Please help required urgently.


texts:
Is there a quick way to find all columns in SQL Server 2008 R2 that are encrypted/have encrypted data?

I need to nullify the data in all encrypted columns in a development server (according to our business rules). I know most of the columns because we use them regularly, but I want to be thorough and also I want to be able to prove that I've found them all.

I've searched the web, looked in INFORMATION_SCHEMA and checked the DMVs I thought would be useful and also sys.columns and sys.objects -- but so far no luck.


texts: How to force order of WHERE clauses in PostgreSQL?

texts:
I have 6 columns in Postgres table:

A1 character varying(5)[]
A2 character varying(5)[]
A3 int REFERENCES ... -- FK
B1 character varying(5)[]
B2 character varying(5)[]
B3 int REFERENCES ... -- FK

I need a SELECT where the first row matched is the winner (limit 1)
with matching to A group and B group.

I know Postgres doesn't care about the order of WHERE clauses and I must prepare an ORDER clause or find a different approach.

I want to prepare lookup with priority of matching, my importance of WHERE clauses is as follow:

highest priority: (A1 and B1) OR
.                 (A1 and B2 OR A2 and B1) OR
.                 (A1 and B3 OR A3 and B1) OR
.                 (A2 and B3 OR A3 and B2) OR
lowest priority:  (A3 and B3)

The query is matching with 6 values, like:
a1 to A1, a2 to A2, a3 to A3, b1 to B1, b2 to B2, b3 to B3

So A1 and B1 means a1 matched with A1 and b1 matched with B1,

in SQL this part is written as:

  ("A1" @> ARRAY['19956']::varchar(5)[] AND "B1" @>
  ARRAY['27407']::varchar(5)[])

with a1='19956', b1='27407'.

Is it possible to prepare it as single query, despite declarative aspect of SQL? I am considering 5 joins on the same table, but maybe there is an easier way.


texts: Generic SQL Job Scheduler for multiple RDBMS's?

texts:
I have been searching for an answer to this, but can't seem to find anything. So my problem is this - we have an environment with MS SQL Server 2008, MySQL, and RedShift, and have some complex dataflows between the databases. Right now, the scheduling is done through independent systems, but I want to have one scheduler that controls the dataflows from beginning-to-end, and is able to script flows from MS SQL to RedShift, etc. Is there a system that can accomplish this already? I'm not a DBA, so I am guessing someone has had this problem before...

Thanks in advance!

EDIT: So one of our dataflows might look like this - file posted on SFTP --> run normal ETL routines -->
compile final complete file --> send to customer/push to S3 --> Run SQL commands on Redshift to load* --> Nightly batch processing on RedShift* --> Unload to S3* --> Load into MySQL*

*These are manually run using a tool that just connects via jdbc (can't remember the program)

My DB-related experience is very light, so I was about to write some python scripts and schedule them in CRON, but that is custom and hard to expand - surely someone has had this problem before. We would like to be able to see a status of the job in one place, create new dataflows/ETL's between all three systems (like an SSIS job).


texts: How does SQL Server's optimizer estimate the number of rows in a joined table?

texts:
I am running this query in the AdventureWorks2012 database:

SELECT
    s.SalesOrderID,
    d.CarrierTrackingNumber,
    d.ProductID,
    d.OrderQty
FROM Sales.SalesOrderHeader s
JOIN Sales.SalesOrderDetail d
    ON s.SalesOrderID = d.SalesOrderID
WHERE s.CustomerID = 11077

If I look at the estimated execution plan, I see the following:

The initial index seek (top right) is using the IX_SalesOrderHeader_CustomerID index and searching on the literal 11077. It has an estimate of 2.6192 rows.

If I use DBCC SHOW_STATISTICS ('Sales.SalesOrderHeader', 'IX_SalesOrderHeader_CustomerID') WITH HISTOGRAM, it shows that the value 11077 is between the two sampled keys 11019 and 11091.

The average number of distinct rows between 11019 and 11091 is 2.619718, or rounded to 2.61972 which is the value of estimated rows shown for the index seek.

The part I don't understand is the estimated number of rows for the clustered index seek against the SalesOrderDetail table.

If I run DBCC SHOW_STATISTICS ('Sales.SalesOrderDetail', 'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID'):

So the density of the SalesOrderID (which I am joining on) is 3.178134E-05. That means that 1/3.178134E-05 (31465) equals the number of unique SalesOrderID values in the SalesOrderDetail table.

If there are 31465 unique SalesOrderID's in the SalesOrderDetail, then with an even distribution, the average number of rows per SalesOrderID is 121317 (total number of rows) divided by 31465. The average is 3.85561

So if the estimated number of rows to be loop through is 2.61972, and the average to be returned in 3.85561, the I would think the estimated number of rows would be 2.61972 * 3.85561 = 10.10062.

But the estimated number of rows is 11.4867.

I think my understanding of the second estimate is incorrect and the differing numbers seems to indicate that. What am I missing?


texts: How to version control PostgreSQL schema with comments?

texts:
I version control most of my work with Git: code, documentation, system configuration.
I am able to do that because all my valuable work is stored as text files.

I have also been writing and dealing with lot of SQL schema for our Postgres database. The schema includes views, SQL functions, and we will be writing Postgres functions in R programing language (via PL/R).

I was trying to copy and past the chunks schema that I and my collaborators write but I forget to do that. The copy and past action is repetitive and error prone.

The pg_dump / pg_restore method will not work because it looses comments.

Ideally I would like to have some way to extract my current schema into a file or files and preserve the comments so that I can do version control.

What is the best practice to version control schema with comments?


texts: Restore master database from SQL Server 2008 R2 on SQL Server 2012

texts:
I have a process that restores all my backups on a centralized server for testing. I have recently upgraded this server to SQL server 2012, and now I am getting an error when restoring master from SQL Server 2008 R2. Restores from SQL Server 2012 and SQL Server 2005 work fine. I get the same results when trying to restore to SQL 2014 as well.

This is my restore statement:

RESTORE DATABASE masttest
FROM DISK = 'c:\temp\master.bak'
WITH MOVE 'master' TO 'c:\temp\masttest.mdf',
MOVE 'mastlog' TO 'c:\temp\masttest.ldf';

And this is the results. The database ends up in Recovery pending.

Processed 400 pages for database 'masttest', file 'master' on file 1.
Processed 3 pages for database 'masttest', file 'mastlog' on file 1.
Converting database 'masttest' from version 661 to the current version 706.
Database 'masttest' running the upgrade step from version 661 to version 668.
Database 'masttest' running the upgrade step from version 668 to version 669.
Database 'masttest' running the upgrade step from version 669 to version 670.
Database 'masttest' running the upgrade step from version 670 to version 671.
Database 'masttest' running the upgrade step from version 671 to version 672.
Database 'masttest' running the upgrade step from version 672 to version 673.
Database 'masttest' running the upgrade step from version 673 to version 674.
Database 'masttest' running the upgrade step from version 674 to version 675.
Database 'masttest' running the upgrade step from version 675 to version 676.
Database 'masttest' running the upgrade step from version 676 to version 677.
Database 'masttest' running the upgrade step from version 677 to version 679.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 5570, Level 23, State 3, Line 1
FILESTREAM Failed to find the garbage collection table.

Filestream is not enabled on the source server or the server I am restoring to. Is there anything I can do to restore this successfully or does this look like a bug?


texts: mysql diff and add diff records into new database

texts:
I have two database on two different hosts:

host A has database A and table A

table A contains records with primary key 1 to 10

host B has database B and table B

table B contains records with primary key 1 to 5

Now I have to take a diff of the two separate host/databases/tables
i.e. "host A/database A/table A" contains 1-10 and "host B/database B/table B" contains 1-5 so records 6-10 are missing in host B/database B/table B. Now add these diff records into "host B/database B/table B" from "host A/database A/table A".
In short, update development database with new production data since last update.
What I have tried:
mysqldump  --replace --complete-insert --skip-disable-keys --no-create-info -h A
  --port=3306 -u A --password=A database_A table_A
  | mysql -h B -u B --password=B database_B


texts: Creating the database on different file groups

texts:
What is the pros and cons of creating the database for a operational system on different file groups? I know it is useful if you want to keep the static part of a database online when the other part (which is on a different File Group) can be restored. However this scenario is not useful for us (according to the nature of the system).

Based on your experience, is there any other benefit that we can get if create the database on the different File Groups? If so, what would be the best practice in assigning file groups?

Thanks a lot.


texts: Cannot perform SELECT COUNT(*), rows over 1 billion on a table

texts:
I have this table that contains over a billion rows and to issue the query, I issued:

SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'audit_tb' AND (index_id < 2)

Output:
1055075195

I was asked to DELETE records in 2014 so to test out, I tried Deleting the last 3 months in 2014, it's been over 1 hr and it's still executing, any ideas?  I suspect there are bad rows.

 DELETE
  FROM [audit_tb]
  where datetime >= '2014-01-01 00:00:00'
  AND datetime <= '2014-03-31 00:00:00' --3 months in 2014

The table also has a CLUSTERED INDEX.


texts: How to unlock a table in IBM DB2 Data Studio 3.1?

texts:
I know the process to unlock a table through IBM DB2 Control Center using activity monitor, but I am completely new to IBM DB2 Data Studio 3.1 Administrative Tool. Whenever i am right clicking on that particular table only, and clicking on edit data*(to update the records in the table)*, i get the error com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-668, SQLSTATE=-57016,SQLERMC=-7;
How do i unlock it using IBM DB2 Datastudio 3.1?


texts: How to Use OPENROWSET function within a Transactional SSIS Package

texts:
I have an SSIS Package and one of its components is "Execute SQL TasK"
and I've written an OPNEROWSET query inside this task.  When I configure the transaction option of the whole package from "Supported" to "Required" I receive this error:

  The requested operation could not be performed because OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not support the required transaction interface.

When you want to configure the Transaction in SSIS you have to run the service
"Distributed Transaction coordinator" and it seems that this service is in conflict with openrowset. What should I do?  Has anybody come across this issue?


texts: Performance of mysql equi-join observed in HDD and SSD

texts:
I experimented performance of queries having equi-joins. I have simplified version of EXPLAINs of the queries.

Mysql version : 5.7.18

Engine : Innodb

Cache state : Mysql restarted before experiment so no buffer pool cache. Since, physical machine is restarted before every experiment there won't be OS cache.

Note : Primary key is nothing but an increasing INT.

CPUs: SSD machine is a Mac machine with 2.5 GHz Intel Core i7.
      HDD machine is a Mac machine with 2.3 GHz Intel Core i7

Case 1:

| id | select_type | table | partitions | type   | possible_keys | key     |  ref             | rows   | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+--------+----------+----+
|  1 | SIMPLE      | c     | NULL       | ref    | PRIMARY       | PRIMARY |  const,const     | 190864 |   100.00 | Using index |
|  1 | SIMPLE      | r     | NULL       | eq_ref | PRIMARY       | PRIMARY |  c.id.           |      1 |    10.00 | Using where |

Case 2:

+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+--------+-------
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                         | rows   |Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+--------+------+
|  1 | SIMPLE      | c     | NULL       | ref    | PRIMARY       | PRIMARY | 771     | const,const                 | 190864 |Using index
|  1 | SIMPLE      | g     | NULL       | eq_ref | PRIMARY       | PRIMARY | 775     | const,const,c.id            |      1 |Using index
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+--------+

I both cases it can be noted that:

the first table having 190864 rows ready to be joined to second table
the second table is about to join in 'eq_ref' order.

Interpretation:
 Each row from first table is going to find its pair in second table. Since there are 100k rows in first table , there will be 100k i/o to disk in random fashion (i.e., simply random i/o correct me if I were wrong). I have run this experiment with HDD and SSD.

Results (time taken):

Case 1 : 3.409s (SSD) and 26.651s (HDD)

Case 2 : 0.248s (SSD) and 1.364s (HDD)

It can be seen that there is a huge difference in performance. Is this because of that SSD are better at random I/Os? or simply SSDs are better at I/O itself?


texts: Is it a good way to use different schemas for storing the data and provide data access?

texts:
For a new product we came along with a pattern, where all the data tables are stored in one schema (lets call it storage), while the clients connect to another one (here called api). The api schema defines a set of views and stored functions in order to retrieve and modify the data.

The thought about this is that the client does not see the real table structure, but only an abstraction of it, so that the storage schema can be changed without breaking any client.

Is this a good approach to achieve a stable client interface, or are there some hidden problems with such a practice.


texts: Search string into dbms_xplan.display_awr output

texts:
I want to search into the result of this query.

Select
cursor(select * from table(dbms_xplan.display_awr(sql_id,
plan_hash_value,null,'all +outline +peeked_binds'))) "PLAN"
from gv$sql
where sql_id='3cu6fsfskqgst'

This query give us the plan of a select execution

{<PLAN_TABLE_OUTPUT=SQL_ID 3cu6fsfskqgst>,
PLAN_TABLE_OUTPUT=-------------------->,<PLAN_TABLE_OUTPUT =
Select count(*) ................................................
Degree of Parallelism is 4 because of hint>,<PLAN_TABLE_OUTPUT=   -
SQL patch "patch_forzar_paralelo4_tcarga" used for this statement>,
< PLAN_TABLE_OUTPUT= >,}

But I only want to show this piece of the string

SQL patch "patch_forzar_paralelo4_tcarga"

The "string" is not always the same.

Can I cut this piece of the string and show it into the screen?. I want to use only sql execution (not plSql).

Thanks


texts:
In SQL Server 2012, I have a Job Agent Alert set up as follows:

EXEC msdb.dbo.sp_add_alert @name=N'Deadlock Alert',
        @message_id=0,
        @severity=0,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @performance_condition=N'Locks|Number of Deadlocks/sec|_Total|>|0',
        @job_id=N'00000000-0000-0000-0000-000000000000'

I have a job scheduled to run every weekday at 5:30pm that executes this step:

EXEC msdb.dbo.sp_update_alert @name = N`enter code here`'Deadlock Alert', @enabled = 0;

I have another that re-enables the alert at 8:00am every weekday. Both jobs (and the alert) have been working fine for months in dozens of instances. But the job at 5:30pm on Friday failed on one instance with the following messages:

  The specified object_name ('Locks') does not exist. [SQLSTATE 42000]
  (Error 14262)  The specified object_name ('Databases') does not exist.
  [SQLSTATE 42000] (Error 14262).  The step failed.

If I execute the sp_update_alert command directly for that alert (with enabled set to either 0 or 1). I get this error:

  Msg 14262, Level 16, State 1, Procedure
  sp_verify_performance_condition, Line 50 The specified object_name
  ('Locks') does not exist.

I tried disabling the Alert using the SSMS UI and get:

  The specified object_name ('Locks') does not exist. (Microsoft SQL
  Server, Error: 14262)

This pair of jobs is still working without error in all of the other instances. What could be causing sp_update_alert to fail in this was on just one instance and only since Friday?

[Added:]

The enable and disable jobs also enable and disable another alert, which looks at the SQLServer:Databases|Data File(s) Size (KB)|tempdb counter and those commmmands throws the error "The specified object_name ('Databases') does not exist". Again, only in this one instance. The same jobs and alerts still work fine on another instance on the same server.


texts: Moving SQL Server 2012 Database to SQL Server 2008 R2

texts:
I follow the answer of Mr. Kin in this post

How to move a database from SQL Server 2012 to SQL Server 2005

I run the query without errors, but when I check my blank database created on SQL Server 2008 R2, there's no change or new added data on my database.

Please help.

Sorry for duplicate post, I don't have enough reputation to comment on the link that I put here. Sorry.


texts: can't set batched_key_access=on in optimizer_switch on mariadb 5.5

texts:
I am switching my application from a Mysql5.6 to a Mariadb5.5. I am support to add batched_key_access=on in optimizer_switch, but when I do that, mariadb refuse to start :

170104 17:16:14 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
170104 17:16:25 mysqld_safe Starting mysqld daemon with databases from /data/mysqldata
170104 17:16:26 [ERROR] /data/mysql/bin/mysqld: Error while setting value 'batched_key_access=off' to 'optimizer_switch'
170104 17:16:26 [ERROR] Aborting

When I read Mariadb Docs, I am supposer to set mrr=on which is what I do :

optimizer_switch='index_condition_pushdown=off'
optimizer_switch='mrr=on'
optimizer_switch='mrr_cost_based=off'
optimizer_switch='batched_key_access=on'

Am I doing something wrong?

Thank you for your help.


texts: When is space be allocated in databases?

texts:
I came from the developer-side, not the administration.
But here and there I have to choose the datatype when I add a new column in our server (MS SQL or MySQL).

I want to add a column for numbers, so my choice is int or tinyint. At the moment I choose tinyint just because I know I want to store a value between 0 and 100 (a percent value) and it can't be higher.

But is there a difference for the space needed on HDD?
Basically: When is space allocated in databases?

When I create a nullable column for tinyint instead of int (1 byte instead of 4 bytes), and I insert a row of data with NULL for this column, is there still space allocated? Or just when I enter a number?

When I create a int column and just save a 1 into it, is there still 4 bytes allocated?


texts:
I have a User table with 3 column; name, email, password, etc.

I would like to create another user that can only SELECT the name column.

SELECT name     FROM User;    -- Ok!
SELECT email    FROM User;    -- Not Ok!

Can this be done on MySQL?


texts: Using Oracle12C Client with Oracle11.g database

texts:
I have been using Oracle12c client to connect to an Oracle11g database in a remote server for about one month, it wasn't giving me any problems until this week, when I started getting the error:

  The Network Adapter could not establish the connection Provider Code 17002

First I used Oracle Sql Developer to design the tables of the database, and a week after that I started to develop an app with Visual Studio 2013, where I used the native adapter and the library Oracle.DataBase.Client as part of the Visual Studio Integrated Tools that oracle provide to work with their databases.

I would like to know if the errors could be because the client is 12c and the database is 11g or if this question doesn't matter and another thing can be happening.


texts:
I'm creating my database tables inside a flask application via SQLAlchemy.
According to this answer, my code should look like this:

id = db.Column(db.CHAR(36), primary_key=True, server_default=str("uuid_generate_v4()"))

which, after migrations and upgrades are done, its definition on the database looks like this:

 id character(36) COLLATE pg_catalog."default" NOT NULL DEFAULT 'uuid_generate_v4()'::bpchar,

Is this the correct way of specifying a UUID default value in PSQL?


texts: I have a .net website connecting to SQL. Just wondering if connection pooling is  enabled by default or is it something I have to activate myself?


texts: Transaction with lock on a specific table

texts:
NOTE : If there is similar question, please refer me to it.

I have two tables Request & Receipt.

I want table Receipt remain locked for CRUD operations from anywhere until transaction finished. I need the transaction be in a Stored Procedure.

Is it impossible? How?

Why should Receipt remain locked?
Cause an internal request for insert or delete Receipt will be unable to manipulate it! Note that I need ReceiptId's be consecutive per each Request from its start, to its finish!


texts: Accessing Service Broker from Different Databases

texts:
In my Application we have a lot of databases nearly 100 dbs.
We are planning to use Service Broker for calling a Stored Proc Async.
Instead of running Service Broker on all the Databases, Is it possible
to run the Service Broker on only one Database and all the databases can
share the Service Broker. I appreciate your guidance.
I am new to Service Broker and I apprecieate if you show me some example how to
implement it.


texts:
We're developing an application that allows our clients to submit jobs to our server. After a job is submitted, the server spins up worker machines to process parts of the job.

Worker machines connect to a postgres database and get necessary model parameters for their work. Once they complete their task, they write results back to the database on the server.

Our client has asked us to encrypt the job information that they submit, such that our staff is unable to access it in any way. Unfortunately, the worker machines need this data to generate results for the client.

In looking through the postgres docs on encryption, client-side encryption appears to be the only viable option that meets their requirements ("If the system administrator cannot be trusted ..."). Can we implement this in a way that allows the worker machines to decrypt the data they need?

Are there alternative approaches that would meet the client's needs? How common is this level of security?


texts: What is the difference between Completion and Success in SQL Server Maintenance Plans?

texts:
Using SQL Server 2008/R2 Maintenance Plans to setup some automated backups/cleanups and notifications. One thing that I can't figure out is what is the difference between Success and Completion?

The way I think about it, is that completion doesn't care if the job failed or succeeded, but if it failed, then obviously it didn't complete maybe??

Perhaps its left there in case of scripts where there might not be a way to tell what the actual outcome was, just that the script completed, but this still feels like its the same as success and failed.

I couldn't find much documentation for it either.


texts: pt-online-schema-change tool will work for existing triggers for a table?

texts:
I have a table name "user" ,I have tried to do partition the table and it was having a trigger already.

When i have tried to alter the table with pt-online-schema-change.It was showing the below error.

"The table Client.user has triggers.  This tool needs to create its own triggers, so the table cannot already have triggers."

I have tried to do partition by this below way,

pt-online-schema-change --execute --user=root --password=***** --host=localhost  --alter "partition by range(id) (partition po values less than (963299666),partition p1 values less than (963499666),partition p2 values less than (963699666),partition p3 values less than (MAXVALUE))" D=Client,t=user

The table structure is,

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `scrip` int(11) DEFAULT NULL,
  `entered` int(11) NOT NULL DEFAULT '0',
  `customer` varchar(50) NOT NULL DEFAULT '',
  `machine` varchar(64) NOT NULL DEFAULT '',
  `username` varchar(50) DEFAULT NULL,
  `clientversion` varchar(20) DEFAULT NULL,
  `clientsize` int(11) DEFAULT NULL,
  `priority` int(11) DEFAULT NULL,
  `description` varchar(80) DEFAULT NULL,
  `type` varchar(20) NOT NULL DEFAULT '',
  `path` varchar(255) DEFAULT NULL,
  `executable` varchar(20) DEFAULT NULL,
  `version` varchar(20) DEFAULT NULL,
  `size` int(11) DEFAULT NULL,
  `lenth` int(11) DEFAULT NULL,
  `windowtitle` varchar(255) DEFAULT NULL,
  `string1` varchar(255) DEFAULT NULL,
  `string2` varchar(255) DEFAULT NULL,
  `text1` text,
  `text2` text,
  `text3` mediumtext,
  `text4` text,
  `servertime` int(11) NOT NULL DEFAULT '0',
  `uuid` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `sservertime` (`servertime`,`customer`,`machine`,`scrip`),
  KEY `custmach` (`customer`,`machine`,`servertime`),
  KEY `customer` (`customer`,`scrip`,`servertime`),
  KEY `machine` (`customer`,`machine`,`scrip`,`servertime`),
  KEY `servertime` (`servertime`)
) ENGINE=InnoDB

Whether can I do partition with the existing trigger using pt-online-schema-change?

How to resolve this issue?

Can anyone give solution?


texts:
I need to save incoming and outgoing emails from an application in a MySQL database server.

It used to be simple to do in that all that needed to be tracked was the from address, to address, subject, and body, but now I need to support multiple to addresses and cc addresses in a single email.

I am struggling with the ideal schema (or structure) and query needed to do this the most efficiently. I started thinking of having one table for the email, another for the to addresses, associated back as a many-to-one relationship, and another table for the cc addresses also associated back with a many-to-one relationship and simply running three separate read queries to get the email, to addresses, and cc addresses to display on the front-end when the saved email gets opened.

Is this the most efficient way to achieve this at this point or is there a better and more efficient solution?


texts:
I'm trying to set up a basic NDB cluster, the data nodes register correctly but the mysqld API node doesn't accept

Connected to Management Server at: 174.138.80.168:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @104.131.16.252  (mysql-5.6.37 ndb-7.4.16, Nodegroup: 0, *)
id=3    @174.138.80.126  (mysql-5.6.37 ndb-7.4.16, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @174.138.80.168  (mysql-5.6.37 ndb-7.4.16)

[mysqld(API)]   1 node(s)
id=4 (not connected, accepting connect from manager.mysql.cluster)

Manager Node's my.cnf

[ndb_mgmd]
config-dir=/usr/local/mysql/mysql-cluster
config-file=/usr/local/mysql/mysql-cluster/config.ini

# MySQL Config
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
user=mysql

# Run ndb storage engine
ndbcluster
# IP address management node
ndb-connectstring=174.138.80.168

# MySQL Pid and Log
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Management Node's /var/lib/mysql-cluster/config.ini file

[ndb_mgmd]
# Management process options:
hostname=manager.mysql.cluster  # Hostname of the manager
[ndbd]
hostname=node1.mysql.cluster    # Hostname of the first data node
datadir=/var/lib/mysql-cluster   # Remote directory for the data files

[ndbd]
hostname=node2.mysql.cluster    # Hostname of the second data node
datadir=/var/lib/mysql-cluster   # Remote directory for the data files

[mysqld]
# SQL node options:
hostname=manager.mysql.cluster

Each of the data node's /etc/my.cnf files:

# MySQL Config
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
user=mysql

# Run ndb storage engine
ndbcluster
# IP address management node
ndb-connectstring=174.138.80.168

[mysql_cluster]
# IP address management node
ndb-connectstring=174.138.80.168

# MySQL Pid and Log
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Thanks


texts: How to avoid GROUP BY on one field in Oracle SQL

texts:
i'm using a query :
SELECT
         t.tID,
         min(t.CONTEXTTIMESTAMP) contextTime,
         CONCAT(' ',CONCAT(ac.NAME, CONCAT('=',ac.VALUE))) as idf
FROM TABLE1 t
  INNER JOIN ( select t.tID, t.COLOR, t.ETAT )
  INNER JOIN TABLE2 ac ON t.tID = ac.tID
WHERE
        t.CONTEXTTIMESTAMP BETWEEN '29/05/20 11:36:21' AND '26/06/20 11:36:21'
GROUP BY
        t.tID,ac.NAME, ac.VALUE
ORDER BY
        contextTime desc

What i would like to do is not adding ac.NAME, ac.VALUE in my group by because it make me lose some of the rows i'm looking for.
I have been looking for solutions but didnt find anything relevant.
Thanks for your help


texts: How to find Stored Procedures With Recompile?

texts:
In my databases I have some Stored procedure with recompile

The way I find them is:

SELECT OBJECT_NAME(ID)AS SP_NAME,*
FROM SYSCOMMENTS
WHERE TEXT LIKE '%WITH RECOMPILE%'

when I look at:

select * from sys.procedures

I find no indication of recompiles.

Is there a better or more elegant way to find the with recompile stored procedures?


texts: dev.mysql.com has 2 different installers to download, which do I need?

texts: If you go to http://dev.mysql.com/downloads/windows/installer/ there are 2 installers you can download. They both are named exactly the same and have the same version number. There is nothing on the download page to indicate how the 2 versions are different. Does anyone know?


texts:
I need to save a list of Places and Persons in a Database including their Addresses and Latitude / Longitude. Sometimes I have an address other times only Latitude / Longitude other times none. So I have:

create table dbo.Persons (
  Id int identity not null primary key clustered (Id),
  AddressId int not null,
  Name nvarchar (100) null,
  Localization geography null
)

create table dbo.Places (
  Id int identity not null primary key clustered (Id),
  AddressId int not null,
  Name nvarchar (100) null,
  Localization geography null
)

create table dbo.Addresses (
  Id int identity not null primary key clustered (Id),
  CityId int not null,
  Street nvarchar (100) null,
  PostalCode nvarchar (100) null,
)

create table dbo.Cities (
  Id int identity not null primary key clustered (Id),
  Name nvarchar (100) null
)

I have two questions:

Should I move the Localization from Places and Persons to Address?
I am doing this way because I can have an address and not a Localization or vice versa.
Is there a way to improve my database scheme?


texts:
I would like some help please with the following.

Here is my SQL Server 2014 table schema:

Date [DateTime]
Invoice1 [float]
Invoice2 [float]
Operator [nvarchar]
Client [nvarchar]

In my Operator column I have 2 values: Operator 1 and Operator 2.

I would like to achieve the following output (in columns) in a single query:

Date
Invoice 1 of Operator 1
Invoice 1 of Operator 2
Invoice 2 of Operator 1
Invoice 2 of Operator 2
Count of Clients

I tried the following query but it returns an error:  Aggregates on the right side of an APPLY cannot reference columns from the left side

SELECT *
FROM
(
    SELECT
        [Date],
        CASE
            when col = 'Invoice 1' then col + ' of ' + [Operator]
            when col = 'Invoice 2' then col + ' of ' + [Operator]
            when col = 'Clients' then col
    END AS pivot_col,
    CASE
        when col = 'Invoice 1' then SUM(value)
        when col = 'Invoice 2' then SUM(value)
        when col = 'Clients' then value
    END AS myvalues
FROM [table]
CROSS APPLY
(
VALUES
    ([Invoice1], 'Invoice 1'),
    ([Invoice2], 'Invoice 2'),
    (COUNT([Client]), 'Clients')
) x (value, col)
GROUP BY
    [Date],
    col + ' of ' + [Operator],
    col
) AS [Source]
PIVOT
(
    SUM(myvalues)
    FOR pivot_col IN (
        [Invoice 1 of Operator 1],
        [Invoice 1 of Operator 2],
        [Invoice 2 of Operator 1],
        [Invoice 2 of Operator 2],
        [Clients]
    )
) AS [Pivot]

I assume the error is due to the fact that I am using COUNT([Client]) inside the CROSS APPLY VALUES block. However I am not able to solve the problem.

Any ideas please?

Thanks
Reda

[EDIT]

Bonjour Julien,

As requested:

Sample DB Data:

  Date | Invoice1 | Invoice2 |  Operator  |  Client  |  Entity
----------------------------------------------------------------
  JAN  |    10    |    15    | Operator 1 | Client 1 | Entity A
  FEB  |    20    |    25    | Operator 1 | Client 1 | Entity B
  JAN  |    30    |    35    | Operator 1 | Client 2 | Entity C
  FEB  |    40    |    45    | Operator 1 | Client 2 | Entity D
  JAN  |    50    |    55    | Operator 2 | Client 1 | Entity E
  FEB  |    70    |    75    | Operator 2 | Client 2 | Entity F
  JAN  |    80    |    85    | Operator 2 | Client 1 | Entity G
  FEB  |    90    |    95    | Operator 2 | Client 2 | Entity H

Desired output (please scroll to the right):

  Date  | Sum of Invoice 1 for Operator 1 | Sum of Invoice 2 for Operator 1 | Sum of Invoice 1 for Operator 2 | Sum of Invoice 2 for Operator 2 | Count of Distinct Clients | Count of Distinct Entities
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   JAN  |             40                  |             50                  |         130                     |         140                     |       2                   |       4
   FEB  |             60                  |             70                  |         160                     |         170                     |       2                   |       4


texts: Using MATCH, AGAINST multiple times in MySQL fulltext search

texts:
I'm using MySQL database for my e-commerce project.
And here is my query for search:

SELECT DISTINCT p.id, p.name, p.price, p.created_at,
    MATCH(p.name)   AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE) as Prv,
    MATCH(b.name)   AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE) as Brv,
    MATCH(bm.name)  AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE) as BMrv,
    MATCH(o.name)   AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE) as Orv,
    MATCH(ov.name)  AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE) as OVrv
FROM products p
    LEFT JOIN brands b          ON  b.id = p.brand_id                     AND MATCH(b.name)  AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE)
    LEFT JOIN brand_models bm   ON  bm.id = p.brand_model_id              AND MATCH(bm.name) AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE)
    LEFT JOIN options o         ON  o.product_type_id = p.product_type_id AND MATCH(o.name)  AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE)
    LEFT JOIN product_option_values pov ON  pov.product_id = p.id
    LEFT JOIN option_values ov  ON  ov.id = pov.option_value_id           AND MATCH(ov.name) AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE)
WHERE
    (MATCH(p.name) AGAINST('Sony* xperia* FHD*' IN BOOLEAN MODE)
    OR MATCH(b.name) AGAINST('Sony* xperia* FHD*' IN BOOLEAN MODE)
    OR MATCH(bm.name) AGAINST('Sony* xperia* FHD*' IN BOOLEAN MODE)
    OR MATCH(o.name) AGAINST('Sony* xperia* FHD*' IN BOOLEAN MODE)
    OR MATCH(ov.name) AGAINST('Sony* xperia* FHD*' IN BOOLEAN MODE))
    AND
    COALESCE(b.id, bm.id, o.id, ov.id, pov.id) IS NOT NULL
GROUP BY
    p.id
ORDER BY
    ((Prv*100) + Brv + BMrv + Orv + OVrv) DESC, p.updated_at DESC;

Here I used MATCH, AGAINST 3 times. First in SELECT for relevance. Second in JOIN and third time in WHERE.

1) Can usage MATCH, AGAINST multiple times affect to speed?

2) If yes, how can I change my code to get same result and reduce speed?


texts:
I am unable to read below logs of mysql after executing SHOW ENGINE INNODB STATUS command. Can anyone please help me out where the deadlock has happen?

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-02-10 11:39:35 7f12f044d700
*** (1) TRANSACTION:
TRANSACTION 364307, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 1066 lock struct(s), heap size 112168, 71366 row lock(s), undo log entries 12
MySQL thread id 16407, OS thread handle 0x7f12f28a3700, query id 176797 localhost 127.0.0.1 synapqadb updating
DELETE FROM E_CUST WHERE M_ID=7419
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 139 page no 4 n bits 384 index `PRIMARY` of table `xyz`.`E_CUST` trx id 364307 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 800000e8; asc     ;;
 1: len 6; hex 00000000196a; asc      j;;
 2: len 7; hex f2000001a00110; asc        ;;
 3: len 4; hex 80000053; asc    S;;
 4: len 7; hex 43555354303038; asc CUST008;;
 5: len 14; hex 435553545f504f5354414c5f4344; asc CUST_POSTAL_CD;;

*** (2) TRANSACTION:
TRANSACTION 364314, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
259 lock struct(s), heap size 46632, 77517 row lock(s), undo log entries 7
MySQL thread id 16405, OS thread handle 0x7f12f044d700, query id 176810 localhost 127.0.0.1 synapqadb updating
UPDATE R_CUST SET RES_LABEL= '' , DESCRIPTION='Postal Code Missing|Address, Phone And Email Are Invalid / Missing|Error Record From Wiley', COMMENTS=CONCAT(IFNULL(COMMENTS,''),'') WHERE STATUS='0' AND TYPE='DATA VALIDATION' AND M_ID=19191
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 139 page no 4 n bits 384 index `PRIMARY` of table `xyz`.`E_CUST` trx id 364314 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Thank you.


texts:
This tablespace is in my case 8GB. I'm on oracle 10g and got critical warning:

Tablespace usage critical: UNDOTBS (96%)
I also executed this command:

select sum (bytes/1048576) MB
FROM
DBA_SEGMENTS
WHERE
tablespace_name = 'UNDOTBS' ;
It gives me 7625,625MB.

Could I extend this tablespace with this command:

ALTER TABLESPACE "UNDOTBS" ADD DATAFILE '/path/to/ts.db' SIZE 4194304000;
Can I do it on running system and wont I destroy anything?


texts: Memory Allocation in Shared DB & IIS Environment

texts:
In my use case, we have production servers that host both IIS & SQL on the same machine, not ideal but no flexibility to change that.

Basically a single machine is both a DB and an Application server, hosting multiple DBs and multiple instances of ASP.NET sites.

The last time I checked MS' best practice said ~75% RAM allocation to SQL, however this config doesn't leave much room for other processes to run.

Does any one know what Microsoft recommends for servers that host both SQL & IIS together ?


texts: Invalid object name 'master.dbo.MSreplication_options' when restoring DB

texts:
I've tried Googling for an answer to this but have got nowhere.

I have one SQL Server instance whose replication is set up to be a publisher and a distributor of FooDatabase.  I have a second instance whose replication is set up to be a subscriber of FooDatabase on the first instance.  Replication updates work.  What I want to do is backup the subscription FooDatabase, delete it on the subscriber instance, and restore the backup.  However, when I do this and try to restore the backup, I get the following error:

  Restore failed for Server 'DAVEG1525-162'.  (Microsoft.SqlServer.SmoExtended)

  System.Data.SqlClient.SqlError: Invalid object name 'master.dbo.MSreplication_options'. (Microsoft.SqlServer.Smo)

I've checked and dbo.MSreplication_options does exist as a system table in the master database on the production instance, but not on the subscription instance.

My questions are: if it doesn't exist on the subscription instance, why is it being included on the backup made from the subscription instance of SQL Server? and how can I fix this problem?


texts:
After Loading data into the table and running  a query on it, it tends to lose the last column of it. Like somewhere in import process it lost it. The file i was using was UTF-8 .csv format. A
Is there anything i need to add to this :

LOAD DATA LOCAL INFILE 'xxx/xxxx/Desktop/xxx/xxxx/file.csv'
INTO TABLE opendns_cc
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

/Names in the path are changed due to security/


texts: Why DB2 cannot allow creation of database with keyword "BACKUP" in its name?

texts:
I'm using DB2 Express C v 10.5

I'm trying to create databases with names "BACKUP", "BACKUPTEST", "BACKUP_TEST" and even "TEST_BACKUP".

In all above cases DB2 throws error as below:

SQL1001N "BACKUPTEST" is not a valid database name. SQLSTATE=2E000

I tried with following command, it didn't work.

> db2 create database BACKUP

or

> db2 create database "BACKUP"

This shows an error as:

  "SQL1001N  "BACKUP_TEST" is not a valid database name.  SQLSTATE=2E000"


texts: Can I load a CSV into a stored procedure that inserts into multiple tables?

texts:
OK, an example is often easier to follow, so here is my procedure:

delimiter //
CREATE PROCEDURE mysql.insertTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime,  _hashid int,
  _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1))
begin
  START TRANSACTION;

  INSERT INTO MYDB.person (`first_name`, `last_name`, `hashID`)
    VALUES (_first_name, _last_name, _hashid);

  INSERT INTO MYDB.id_crimes (`id`, `id_status`, `criminal_status`, `hashID`)
    VALUES (_id, _id_status, _criminal_status, LAST_INSERT_ID());

  INSERT INTO MYDB.dob_state (`stateID`, `stateID_status`, `dob`, `hashID`)
    VALUES (_stateid, _stateid_status, _dob, LAST_INSERT_ID());

  INSERT INTO MYDB.timestamps (`create_time`, `update_time`, `hashID`)
    VALUES (_create_time, _update_time, LAST_INSERT_ID());

  COMMIT;
end//
delimiter ;

Now, what I'd like to know is whether I can load a CSV file to populate the tables directly using that procedure. I've seen workarounds for how to load to a single table, but this is to multiple tables. Any thoughts?


texts: Comparing Columns that can contain NULLS - is there a more elegant way?

texts:
I know that you can't compare a value to NULL and expect a result without adding something like in the following code...

SELECT
    *
FROM
    A INNER JOIN
    B ON A.ID = B.ID
WHERE
    A.STRING <> B.STRING OR (A.STRING IS NULL AND B.STRING IS NOT NULL) OR (A.STRING IS NOT NULL AND B.STRING IS NULL) OR
    A.DT <> B.DT OR (A.DT IS NULL AND B.DT IS NOT NULL) OR (A.DT IS NOT NULL AND B.DT IS NULL) OR
    A.B <> B.B OR (A.B IS NULL AND B.B IS NOT NULL) OR (A.B IS NOT NULL AND B.B IS NULL) OR
    A.NUM <> B.NUM OR (A.NUM IS NULL AND B.NUM IS NOT NULL) OR (A.NUM IS NOT NULL AND B.NUM IS NULL)

My question is:

Is there a more elegant way to test for changed values across the two tables where either one could be null?

Solution needs to work uniformly across the data types.

Here is the code to set up the test tables...

CREATE TABLE A
(
    ID INT IDENTITY(1,1) NOT NULL,
    STRING VARCHAR(20) NULL,
    DT DATETIME NULL,
    B BIT NULL,
    NUM INT NULL
)

CREATE TABLE B
(
    ID INT IDENTITY(1,1) NOT NULL,
    STRING VARCHAR(20) NULL,
    DT DATETIME NULL,
    B BIT NULL,
    NUM INT NULL
)

INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES (NULL, '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', NULL, 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', NULL, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, NULL)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)

INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('STAGE', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2555-11-11 00:00:00.000', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 999)


texts: Database with insert data every 2 minutes

texts:
I have a site to develop into PHP and mysql where I take, with a cron, every two minutes 10 xml from a server parse each and insert data into my database.
Well this data are simple string (maximum 10) and about 20 field (string(10)).
Every two minutes I insert 10 records with 20 fields (the number of xml can be incremented).

Into this database I have other tables but the biggest table is where I insert every two minutes some records.

I want to know if is bettere to separate database by year like: database_2013, database_2014... create database automatically when the year change beacuse I dn't want that this table of data can generate every year Gb of database and ten years from now I have a very big database where to make query is very slow.

One database (isn't a problem a lot of Gb for a database) or more database divided by years?

This is my table that I implement:

CREATE TABLE IF NOT EXISTS `station` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `station_id` int(11) unsigned NOT NULL,
  `dewpoint` varchar(5) NOT NULL,
  `heat_index` varchar(10) NOT NULL,
  `pressure` varchar(10) NOT NULL,
  `relative_humidity` varchar(5) NOT NULL,
  `temperature` varchar(5) NOT NULL,
  `wind_degrees` varchar(10) NOT NULL,
  `wind_direction` varchar(10) NOT NULL,
  `wind_speed` varchar(5) NOT NULL,
  `windchill` varchar(10) NOT NULL,
  `rain_rate_in_per_hour` varchar(10) NOT NULL,
  `rain_rate_hour_high_in_per_hour` varchar(10) NOT NULL,
  `rain_storm_in` varchar(10) NOT NULL,
  `leaf_wetness` varchar(2) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


texts: Ndb cluster change variable for heartbeat and disconnect

texts:
I search for a variable that I can increase in the server that I don't get any more this error:

Node 2 missed heartbeat 2
Node 3 Disconnected
Node 2 Disconnected

I don't find anything.

Regards,