texts: Why is it not recommended to use RAID 5 for a log file?

texts:
While reading SQL Server Query Performance Tuning written by Grant Fritchey, I found it difficult to understand the following part: avoid RAID 5 for t-logs because, for every write request, RAID 5 disk arrays incur twice the number of disk I/Os compared to RAID 1 or RAID 10.

I know that RAID 5 differentiate from other RAIDs with its parity feature. It means that if some of the drives fails, then it is possible to recover lost data from the other drives. I want to understand why it is not recommended to use RAID 5 for a transaction log file. Explanation in the book was not enough for me to get it. Maybe someone could explain it to me or provide a good article.


texts:
In T-SQL there is a method, REPLICATE(char,N), which will make a string with N values of a certain  character.  This is very helpful when querying into JDE.
Does anyone know if there is a DB2 equivalent to this?


texts: parsing and purpose of oracle audit (.aud) logs

texts:
I am looking at the oracle audit logs and am trying to understand and parse.

The format appears to be consistent:

Date
Length:
Action:
...etc

My questions are: 1) what generates these? Is it every sql command executed against the db? 2) how can i parse these into a consistent format (xml/json/csv)

Thank you.


texts:
I keep getting this error and I dont  understand why. I checked all CONVERT functions but not sure where is the issue.
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

This is the query and you can see Line 1 does not have any type of conversion.
SELECT
  'Entity Code|Department Code|Job Code|Pay Code|Pay Period Code|Employee Code|Fiscal Year|Time
  Class|Unit Type|Amount|Home Entity Code|Home Department|Pay Period End Date' AS PRL_HEADERR
UNION ALL SELECT
  (ISNULL(RTRIM(LTRIM(RIGHT(REPLICATE(0, 4) + SUB.DIST_COMPANY, 4))), '')
  + '|' + ISNULL(RTRIM(LTRIM(RIGHT(REPLICATE(0, 4) + SUB.DST_ACCT_UNIT2, 4))), '')
  + '|' + ISNULL((SUB.JOB_CODE2), '')
  + '|' + ISNULL((SUB.PAY_CODE2), '')
  + '|' + ISNULL((SUB2.PAYPERIOD), '')
  + '|' + ISNULL(
      (CASE WHEN SUBSTRING(RIGHT(REPLICATE(0, 7) + SUB.EMPLOYEE, 7),1,1)='0'
      THEN SUBSTRING(RIGHT(REPLICATE(0, 7) + SUB.EMPLOYEE, 7),2,LEN(RIGHT(REPLICATE(0, 7) +
      SUB.EMPLOYEE, 7)))
      ELSE RIGHT(REPLICATE(0, 7) + SUB.EMPLOYEE, 7) END), '')
  + '|' +       ISNULL((SUB.FISCAL_YEAR), '')
  + '|' +       ISNULL((SUB.TIME_CLASS), '')
  + '|' +       ISNULL((SUB.UNIT_TYPE), '')
  + '|' +       ISNULL((SUB.UNIT_VALUE), '')
  + '|' +       ISNULL(HOME_ENTITY_CODE, '')
  + '|' +       ISNULL(HOME_DEPARTMENT, '')
  + '|' +     ISNULL(PAY_PER_END_DATE, ''))  AS PRL_HEADER
FROM
 (
  SELECT
     TOP 100 PERCENT DIST_COMPANY,
     LTRIM(RTRIM(DST_ACCT_UNIT)) AS DST_ACCT_UNIT2,
     LTRIM(RTRIM(JOB_CODE)) AS JOB_CODE2,
     LTRIM(RTRIM(PAY_CODE)) AS PAY_CODE2,
     PER_END_DATE,
     EMPLOYEE,
     (CONVERT(VARCHAR(4), GL_DATE, 112)) AS FISCAL_YEAR,
     'A' AS TIME_CLASS,
     'Hours' AS UNIT_TYPE,
     HOURS AS UNIT_VALUE,
     COMPANY AS HOME_ENTITY_CODE,
     LTRIM(RTRIM(HM_ACCT_UNIT)) AS HOME_DEPARTMENT,
     CONVERT(VARCHAR(8), PER_END_DATE,112) AS PAY_PER_END_DATE
  FROM
     TEST_TABLE
  WHERE
     (
        GL_DATE >= CAST('20200101' AS DATETIME)
        AND GL_DATE <= CAST('20201231' AS DATETIME)
     )
  UNION ALL
  SELECT TOP 1000
     DIST_COMPANY,
     LTRIM(RTRIM(DST_ACCT_UNIT)) AS DST_ACCT_UNIT2,
     LTRIM(RTRIM(JOB_CODE)) AS JOB_CODE2,
     LTRIM(RTRIM(PAY_CODE)) AS PAY_CODE2,
     PER_END_DATE,
     EMPLOYEE,
     (CONVERT(VARCHAR(4), GL_DATE, 112)) AS FISCAL_YEAR,
     'A' AS TIME_CLASS,
     'Amount' AS UNIT_TYPE,
     WAGE_AMOUNT AS UNIT_VALUE,
     COMPANY AS HOME_ENTITY_CODE,
     LTRIM(RTRIM(HM_ACCT_UNIT)) AS HOME_DEPARTMENT,
     CONVERT(VARCHAR(8), PER_END_DATE,112) AS PAY_PER_END_DATE
  FROM
     TEST_TABLE
  WHERE
     (
        GL_DATE >= CAST('20200101' AS DATETIME)
        AND GL_DATE <= CAST('20201231' AS DATETIME)
     )
  ORDER BY
     EMPLOYEE,
     PER_END_DATE
 )
 AS SUB
 INNER JOIN
  (
     SELECT
        ROW_NUMBER() OVER (ORDER BY PP_END_DATE) AS ROWNUM,
        SUB3.PP_END_DATE,
        (
           (CONVERT(VARCHAR(4), SUB3.PP_END_DATE, 112) + '-') + ROWNUM
        )
        AS PAYPERIOD
     FROM
        (
           SELECT DISTINCT
              TOP 100 PERCENT PER_END_DATE AS PP_END_DATE
           FROM
              TEST_TABLE
           WHERE
              (
                 PER_END_DATE >= CAST('20200101' AS DATETIME)
                 AND PER_END_DATE <= CAST('20201231' AS DATETIME)
              )
           ORDER BY
              PER_END_DATE
        )
        AS SUB3
  )
  AS SUB2
  ON SUB2.PP_END_DATE = SUB.PER_END_DATE

This query was written in Oracle by someone else and I'm converting it to MS SQL.  Also the app is large and legacy, written in Cobol.  Good thing is there is not a lot of data here therefore performance is not a big concern.


texts: We are in the planning phase to implement AlwaysOn Availability Groups. What is the best practice to setup the file share for data synchronization? Network path or local machine (creating a share on one of the SQL servers)? If we put it on a file server, Would there be a performance hit reading the data from the file server? If we put it on one of the SQL server, what will happen if we lost that server? Please help. Thank you.


texts: Use join to aggregate data from various normalized tables

texts:
I have a database as follows:

tbl_clients (Primary Key: client_id, Foreign Keys:place_id,district_id,zone_id,country_id)
tbl_places (Primary Key: place_id)
tbl_districts (Primary key: district_id)
tbl_zones (Primary Key: zone_id)
tbl_country (Primary key: country_id)
tbl_branches (primary key: branch_id, Foreign Key: client_id)

My client asked me to provide all the data in a csv file. The requirement is:

If a client has branches, the branches details also should show.(from tbl_branch)
If a client does not have branches,only its details should show.

I have achieved the second condition using the following:

select distinct
    a.name  as Client_Name,
    a.pobox_no  as Postal_Address,
    a.street  as Street_Address,
    d.place_name as Place,
    e.district_name as District,
    f.zone_name as Zone,
    g.country_name as Country,
    a.fax  as Fax,
    a.telephone  as Telephone,
    a.telephone2  as Telephone2,
    a.email  as Email,
    a.website  as website,
    a.published  as Published

from
    db_name.tbl_client a
join
    db_name.tbl_subcategory b on a.sub_cat_id = b.cat_id
join
    db_name.tbl_category c on a.category_id = c.id
join
    db_name.tbl_place d on a.place_id = d.place_id
join
    db_name.tbl_districts e on a.district_id = e.district_id
join
    db_name.tbl_zones f on a.zone_id = f.zone_id
join
    db_name.tbl_country g on a.country_id = g.country_id

where
    a.published = 1

How can I achieve the first condition?


texts:
I would like to know how should i store longitude and latitude values in a mysql database, maybe the questions should be: Decimal vs Point (Spatial Data)

I found this question where the question is almost the same, but what i want to know unlike that question is how will i use the data when using each of the options?
I know that when using decimal i'll simple retrieve the columns data and use them, but what when using Point?

Also i would like to know about the benefits of using any of them, and if there's a better way to do it i would like to know it. Also i'd like to know why google used float in this example?


texts: Estimate amount of downtime from RDS backup

texts:
I have an Multi-AZ RDS instance. Due to some maintenance I want to create a consistent snapshot of the database during the day, but this means shutting down all our services. I noticed from the AWS console that the time between a database backup start and finish is about 30 minutes.

What I was wondering is if this impacts performance and if so, how long and when? Is it like EBS where I can start everything up again when the snapshot moves from "creating" to "progess" or do I need to wait until it is entirely complete?

Because if possible I'd like to minimize the amount of downtime our services have, meaning that not having to wait for the entire backup would save us a lot of time.


texts: Is there a way to Oracle expdp not inherit char type nls semantics on DDL?

texts:
expdp, by default, generated internal DDL to create table observing current NLS_LENGTH_SEMANTICS of each column.

If a column as BYTE (the default and most used) NLS_LENGTH_SEMANTICS, the internal DDL would be like:

CREATE TABLE BDATA.Artikel(
    Key                   VARCHAR2(3 BYTE)  NOT NULL,
    Name                  VARCHAR2(60 BYTE) NOT NULL,
    Abkuerzung            VARCHAR2(5 BYTE)  NOT NULL
);

But I want an option to force expdp to not get/inherit the CHAR_USED value from DBA_TAB_COLUMNS, resulting into the following DDL:

CREATE TABLE BDATA.Artikel(
    Key                   VARCHAR2(3)  NOT NULL,
    Name                  VARCHAR2(60) NOT NULL,
    Abkuerzung            VARCHAR2(5)  NOT NULL
);

The reason is because the target database has a AL32UTF8 charset and NLS_LENGTH_SEMANTICS set as CHAR. So I want the tables be converted from BYTE to CHAR (accordingly to NLS_LENGTH_SEMANTICS parameter value).


texts: MySQL: Upgraded from 5.1 to 5.6 - no problem?

texts:
We're using Yum to maintain MySQL and our MySQL server only hosts a largish Drupal database.

We recently upgraded via Yum from 5.1 to 5.6. The process went like so:

Ran a mysqldump --all-databases --single-transaction
Un-installed MySQL 5.1 via yum
Removed our mysql data folder (/var/lib/mysql)
Installed the mysql 5.6 repo package for Yum and ran $ yum update, which updated the mysql client and libraries to 5.6.
Installed MySQL 5.6 server via Yum.
Imported our 5.1 dump file
Ran mysql_upgrade without any errors or warnings reported.
Checked databases, users and application worked ok, and it seems to be the case.

I've read up on the MySQL documentation on updating versions with Yum and it says not to use Yum and to upgrade to 5.5 first - the tricky part there is that there isn't an official 5.5 repo around anymore.

Different upgrade guides say different things, but the path taken above was an amalgamation of different guides based on circumstances. So my question is, can anyone see any issues with the upgrade path listed?


texts: Get the Replication/Lag time of Redshift

texts: I'm currently running RedShift with pushes of all data from our production Postgresql databases every 10 minutes or so. Periodically, the ETL process from Postgresql to Redshift gets delayed or backed up. Is there any way to monitor the lag time between RedShift and Postgresql?


texts:
The structure of my data is the following:

date: <timestamp>
filter_a: <integer> -> range [0, 1000]
filter_b: <integer> -> range [0, 1000]
filter_c: <integer> -> range [0, 86400]
filter_d: <integer> -> range [0, 6]
group: <string>
second_group: <integer>
variable_a: <float>
variable_b: <float>
variable_c: <float>
a couple more no very important

I need to perform the following queries:

First:

Filter data by date, filter_a, filter_b, filter_c and others

Second, with the filtered data:

count all records
get average of variable_a, variable_b and variable_c
get standard deviation of variable_a, variable_b and variable_c
get quartiles  of variable_a, variable_b and variable_c
group data by group or second_group and aggregate(Count, Avg, Std, ..)

The number of the system's users is about 10 or 15, but the number of items is huge, right now it is 70M but it will be 500M in a couple of weeks and it will be 1000M in about a year.

The number of queries is small, no more than 10 users concurrently, my problem is how to handle those queries with this huge amount of data.

What have I tried so far?

I started with mongodb, at the beginning it was fast but it became slow when calculating quartiles with 10M+. It improved when I added indexes but it didn't help very much when I had to query all data. I started using mongodb because data was very dynamic but luckily the data format "isn't going to change anymore".
As filter_a and filter_b could be seen like nodes, I tried neo4j. I liked it neo4j very much but my graph had A LOT of edges so that queries wasn't very fast.
Finally, since data format isn't going to change and it is only one collection/table so needs no joins in SQL, I checked postgresql. My tests has been faster with postgresql, but I'm scared it could not scale properly in the future.

What do I need?

Is postgresql a good choice for this case?
Is there another kind of database I could use? which one is the best for this case?
What else could I do to improve it?

Edit

About 1M of elements are inserted every day and "should not change" along the time.
Write speed is not important
The hard requirement is to read/aggregate fast

Thanks!


texts: What is the best way to include SQL Server Agent jobs in Source Control?

texts:
I found that if I script a SQL Server Agent job as 'create to' that the script starts with use msdb- so I assume that jobs are stored in the msdb database?

What is the best way to add SQL Server jobs to a Visual Studio database project? I added the msdb database, to the database solution, but that doesn't seem to have any reference to jobs.

I would like the jobs to be deployed/updated along with a database... it seems like this is not possible?


texts: Why is add timestamp column with default not null so slow in Oracle 11g?

texts:
In Oracle I need to add a new column with type timestamp with time zone to a large table (80 million rows).
I cannot have more than 30 - 60 seconds of downtime when I run it in production.

This statement completes instantly:

alter table
my_table
add my_timestamp_col timestamp(6) with time zone;

However I would like to default the column to current_timestamp at time zone 'UTC' and force it be non-null.

With other data types, Oracle 11g will do this quickly if you use "add column  default  not null"
For example both of these complete instantly:

alter table
my_table
add my_varchar_col varchar2(10) default 'hello' not null;

alter table
my_table
add my_date_col date default sysdate not null;

But with a timestamp it is slow (at least tens of minutes on my test DB). I assume this means it is updating every row.

So my questions are:

Why is it different with a timestamp than other data types?
Is there a fast way to accomplish the outcome where the column is
forced to be non-null and new rows default to the current UTC
timestamp?

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production


texts: MySQL copy all views from one database to another in stored procedure

texts:
I've got a stored procedure that copies all tables from one database to another but steps through each table individually (because of queries).

I need to add to the stored procedure to copy all views from the original database but I'm not totally sure how to loop through the results of this query:

SHOW FULL TABLES IN stations WHERE TABLE_TYPE LIKE 'VIEW';

Stored procedure:

USE station;
DROP PROCEDURE IF EXISTS updateStation;
DELIMITER //
CREATE PROCEDURE updateStation (IN stationName VARCHAR(255))
 BEGIN
   # stationList
   DROP TABLE IF EXISTS station.stationList;
   CREATE TABLE station.stationList LIKE stations.stationList;
   INSERT INTO station.stationList SELECT s.* FROM stations.stationList s WHERE s.hostName = stationName;
   # pinTypes
   DROP TABLE IF EXISTS pinTypes;
   CREATE TABLE station.pinTypes LIKE stations.pinTypes;
   INSERT INTO station.pinTypes SELECT p.* FROM stations.pinTypes p;
   # pinModes
   DROP TABLE IF EXISTS pinModes;
   CREATE TABLE station.pinModes LIKE stations.pinModes;
   INSERT INTO station.pinModes SELECT p.* FROM stations.pinModes p;
    # Copy All Views
    FOR SELECT TABLES IN stations WHERE TABLE_TYPE LIKE 'VIEW'
    DROP TABLE IF EXISTS ?;
    CREATE TABLE station.? LIKE stations.?;
 END //
DELIMITER ;

Perhaps something like this? :

DECLARE cur CURSOR FOR SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'stations' AND ENGINE IS NULL;
DECLARE done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
    FETCH cur INTO tableName;
        IF done THEN
          LEAVE read_loop;
        END IF;
    DROP TABLE IF EXISTS 'station'.tableName;
    CREATE TABLE 'station'.tableName LIKE 'stations'.tableName;
END LOOP;

CLOSE cur;


texts: IF condition in connect command in SQL*Plus

texts:
I want to fire SQL queries only when the user is connected to SQL*Plus. In my case even if the connection is not there, the rest of the commands are running in my SQL script.

if "connect &username/&password@&SID"
...

How to proceed with the queries if only the connect statement is successful?


texts:
I am beginning to set up CouchDB on an Ubuntu 14.04 Server (yes, it expires next year) and I'm having troubles setting up SSL connections. For the moment, I don't have any CA signed certs for it, so I've been following the steps on the CouchDB docs. To list my changes to the config file: I have essentially kept them as the default except I set the bind address to 0.0.0.0 to be accessible over the network, and I moved my database_dir and view_index_dir to another path. For the SSL part, I followed the documentation precisely step-by-step, and restarted the couch service. From localhost, I get the response I expect:

user@ubuntu:/etc/couchdb# curl -k https://127.0.0.1:6984/
{"couchdb":"Welcome","uuid":"43cf4aa1c38ef1feb9ec71ab93dddea3","version":"1.6.1","vendor":{"name":"Ubuntu","version":"14.04"}}

Now, when I try to access the server over the network (which right now is just me accessing a VM from the host, having tried a bridged and host-only adapter in VBox):

courtney@host:~$ curl -k https://192.168.10.38:6984/
curl: (35) OpenSSL SSL_connect: SSL_ERROR_SYSCALL in connection to 192.168.10.38:6984

At this point, I'm not sure what I'm missing. Is the documentation inaccurate? Or perhaps some packages I'm missing? I installed couch from the PPAs


texts: Is it possible to remove a database server from a cluster resource, but keep it running on the node

texts: I was wondering if anyone knew whether it was possible to delete the cluster resource, but keep all of the disks and sql instance running on the node instead? So if I was to delete the cluster resource, would the instance continue running on the node?


texts: What do these parameters mean at the beginning of my SQL Server query plan?

texts:
I'm looking at query plan in SQL Server Mgmt Studio and I see something like this:

(@P0 nvarchar(4000), @P1 nvarchar(4000)) Update...

What does the type mean beside the parameters? For this example lets assume that the column for those parameters are actually varchar(64). Is this how the parameters are being bound at runtime?

EDIT

This is a Java EE application using JDBC 3.0 or 4.0 providers. Basically we have a properties file full of SQL statements like this:

mySqlStatementFoo = UPDATE Schema.Table.Column set column = 1 WHERE objectID = ?

It looks like in the past if there was a binding mismatch we would cast the param like so:

Where objectID = CAST(? AS VARCHAR(36))

I suppose they chose to do it this way since we support multiple databases. I.E. DB2 and Oracle. I haven't seen the actual Java, but I suspect they are just selecting the statement from the file and sending it down the wire. I'm doing load testing and seeing that we are doing table scans and suspect that the optimizer isn't using the correct index because the parameters are being bound incorrectly.


texts: Cloud product - one database per customer?

texts:
I'm looking to start a build for a cloud software product - imagine a similar level of transactions and processing as Xero but with an initial demand of less than 100 clients.

For ease of troubleshooting, client-controlled backups and portability, is there any issues with each client (of typically 1 to 10 users) having their own database. I.e. new client signs up and we then clone a bank DB for them and connect it to their logon.

Does SQL use more resources for more databases? And are there any issues that will cause me problems, and if so, what is best practice - schemas etc.?

My human mind sees separate DBs as a cleaner option, but I wonder what MSSQL thinks?


texts: How can I differentiate between a Multivalued Attribute and an Entity?

texts:
While designing ER Diagrams, how can we differentiate between a Multivalued Attribute and an Entity?

How can we know that, what we are considering as an 'Entity', isn't actually an entity but a Multivalued attribute or vice-versa?


texts: How to force SQL Serve *not* to maintain a log file

texts:
I have a situation that I need some advice to overcome.

I have a SQL Server 2012 database that has been set to Simple recovery model. At times, my SQL Server transaction log file grows out of control and tries to consume all available disk space. It shows as empty in SQL Server, but continues to consume disk space (I understand why). I would like to know if there is a way to prevent SQL Server from maintaining the log file at all.

The application that lives in front of this database doesn't play well with the idea of recovering using a trans log backup. Our backup strategy is one of Full and Diff backups executed on a regular enough schedule that we are adequate covered. We don't need or want point in time recovery (hence simple recovery).

To test, I had the log file capped at 10mb, and 10% auto-growth turned on. My SQL Server load was working away at about 3.8mb trans file, when I initiated a full DB re-index (to try to approximate the load that our customers may put on the database). The log file went straight to 10mb+, and all transactions halted with a '9002' error.

I believe I understand the goal of the trans log in normal situations, but in my case, it is just taking up space and getting in the way. Is there a way to prevent SQL Server from using it?

Thank you in advance for your insight!

Catt11.


texts: Restoring a WordPress site from WAMP with only .ibd and .frm files, into a new WAMP with ... .myd and .myi files?

texts:
Background: I uninstalled a WAMPserver (version 2.5) on a PC, which left over a WordPress installation with only .frm and .ibd and .opt files. That's it. I need to restore the site.

My latest attempt to restore the site is based on a method I read at this link, where:

I install a fresh installation of WAMP (version 3 this time)
I install a WAMP add-on of MySQL 5.6.12, which is the version of MySQL that my site was running on before my WAMP failure.
Install WordPress as a fresh new install.
Copy all the .ibd, .opt, and .frm files left over from the old setup, and replace the fresh .ibd, .opt, and .frm files in the new setup.
Go into PHPAdmin, access the database and run "alter table" comments for each wordpress table (ex.: alter table wp_posts import tablespace;)

I copied the left over .ibd, .opt, and .frm files from the old setup, and then went over to the new database folder and was surprised to find that there were not .ibd files in there to replace but .myd and .myi files instead!

I find this confusing because I learned that I had to have the same version of MySQL running to attempt this.

My questions are this:

1) why did my new installation create dual .myd and .myi files for each database table instead of single .ibd files?

2) am I able to replace the files anyway, with the hopes of having the new setup read the old .ibd files and working them into the new .myi and .myd files

3) is there no hope of restoring this site now? This would appear to be my last hope before I'd have to resort to copying the paragraphs out of the .ibd files using TextWrangler to convert them into a text file in order to at least get my content back in some form.


texts: Checkpoints on secondary replica AlwaysOn AG

texts:
Setup

3 Node Alwayson cluster - 1 sync and 1 async secondary replica - SQL Server 2012

Situation

We are witnessing PageIOLatches when reading from the asynchronous secondary replica. This is mostly caused by the throughput of the SAN that has been throttled. The hosting partner told us that this limitation cannot immediately be alleviated due to hardware constraints.
The primary and synchrounous replica use other SANs with a higher throughput. Although this situation is far from ideal, this is a temporary situation that will be solved soon and not the subject of my question.

When investigating the IO waits we noticed that these occur concurrently with an increase in the number of checkpoint pages/sec.

I was under the impression that checkpoints don’t occur on secondary replica’s in an AG, just as discussed here.

To verify this behaviour, I’ve set up an extended event to monitor the checkpoint events on the asynchronous replica. Just as expected, no checkpoints were captured for this database, nor have I found any checkpoints from other databases that match the pattern.

Next, I’ve created the same extended event on the primary replica and started a perfmon to verify if we could witness the same behaviour. Here we were able to capture (automatic) checkpoints, they happen approx. once per minute. These checkpoints occur simultaneously with the checkpoint pages/sec increase on our secondary (and primary) replica. It seems that checkpoints are being generated on the primary and redone on the secondary replicas. This would mean that checkpoints do occur implicitly on secondary replica's in an AG.

Question

Is my assumption correct that in an AG checkpoints are being generated on the primary replica and redone on all secondary replicas?
And thus, if the database TARGET_RECOVERY_TIME isn't set, the recovery interval setting of the primary replica will dictate the checkpoints on all secondary replicas for these databases.


texts:
From what I can find the version store will only clean up versions that are older than the oldest active transaction. Question: Is the oldest transaction database specific or will SQL Server keep all versions, regardless of the database, if there is an older transaction still active, period?

Backstory - SQL Server 2005 SP4 Enterprise hosting around 40 databases. TempDB is currently 102 GB, version store is around 98 GB. One of the applications hosted on the database instance has an open transaction that is 40 days old based on sys.dm...database_transactions. Two separate large databases had extremely heavy use over the last month and we saw consistent TempDB growth coinciding with these operations. We expected some growth. We did not expect it to keep growing.
Question: Are the versions stored in TempDB's version store from these two separate databases still there because a third independent database has a connection that is 40 days old and shows an open transaction_state?

Perfmon counters: Version store is continually growing in the few hours I have tracked it this morning. Version Generation Rate AVG is around 30 kb/s, Version Cleanup rate is 0 kb/s.

Plenty of space left for TempDB, there are around 300 GB of total data files for all user databases, TempDB has grown on average 350 MB per day for each of its 8 data files since the last restart. This behavior is abnormal and investigation revealed the large version store

Answers to comment questions so as not to have a long running comment section:

Q: Why auto-growth on tempdb?
A: TempDB is set to initialize at a size we have found to be appropriate for most of the time. We allow auto-growth in order to handle abnormal database activity. We monitor auto-growth as well.

Q: How do you know the transaction is active and not just an active connection?
A: transaction_state says active in sys.dm_tran_active_snapshot_database_transactions and other stuff. Activity Monitor says each connection has 1 open transaction.

Q: Why is your app so stupid?
A: Its third party. One of many on this instance. I do not know if the behavior is abnormal, or easily fixed.

RESOLUTION

The open transaction(s) where preventing any version store cleanup, so Jon was right, version store cleanup is done independent of databases. Closing the offending transactions allowed version store cleanup to commence. Current theory behind why is from Jon Seigel

  The version store can only clear versions based on the oldest active transaction within the entire instance, to support the use of transaction-level snapshot isolation across multiple databases simultaneously.

If anyone knows for certain or can prove this please do

Referenced question: find-transactions-that-are-filling-up-the-version-store

Referenced documents:
TempDB 2005 WP
Teratrax tuning tempDB
Idera Demystify Tempdb


texts:
I would like to select items from a table where their frequency count is less than 5% of the total amount.

For example, if I had a table of names, I would like to select the names that appeared less than 5% of the total.


texts: Postgresql error: failed to re-find parent key in index

texts:
I have a Django app with a postgresql 9.3 DB (hosted on Ubuntu VM), and very recently the VM ran out of disk space (resulting in no space left on device errors showing up in my logs).

I cleared the space on the device and everything came back online.

But now, for a minority of my requests, I've started receiving a django.db.utils:DatabaseError alert, with the description failed to re-find parent key in index "links_grouptraffic_time" for split pages 24582/24583. links_grouptraffic is one of my data models, and time is an attribute within that data model.

Can someone explain the background of what this means, and a remedy for this problem? Thanks in advance!


texts: pgAdmin crashes or freezes when big MATERIALIZED VIEWs are refreshing

texts:
I'm using pgAdmin III v1.22.1 on Windows 7 to connect to a PostgreSQL server version 9.4.8 on Red Hat.

We have scripts to REFRESH MATERIALIZED VIEWS during the night.
When they are refreshing, I can't connect to pgAdmin III. It just freezes. It seems that it tries to query some locked objects.

When the materialized views finishes refreshing, I am then able to log in via pgAdmin.

When the REFRESH MATERIALIZED VIEW my_view is done, the above query is run and pgAdmin unfreezes.

Is there a way to prevent pgAdmin from freezing when MATERIALIZED VIEWS are refreshing?


texts: InnoDB: in what scenarios, if any, is it okay to have random insert WRT primary key?

texts:
For now let's ignore the benefits of having a surrogate key for business logic reasons. Let's only consider performance (speed and disk usage).

CREATE TABLE `person` (
  `ssn` bigint(20) unsigned NOT NULL DEFAULT '0',
  `fav_color` char(10) DEFAULT NULL,
  PRIMARY KEY (`ssn`)
)

Let's say the only read query I want to do is selecting fav_color for a contiguous range of ssn.  Let's assume ssn is globally unique.

Unfortunately the order in which I add people to this table is random WRT their ssn, and so the clustered index would become fragmented, right?

Well, to solve that, I could change to this:

CREATE TABLE `person` (
  `person_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ssn` bigint(20) unsigned DEFAULT NULL,
  `fav_color` char(10) DEFAULT NULL,
  PRIMARY KEY (`person_id`),
  UNIQUE KEY `ssn` (`ssn`,`fav_color`)
)

I made the ssn key a covering index because I want "USING INDEX" when I select fav_color on a range query of ssn.

So now the clustered index is nice and non-fragmented, but my secondary index will still be fragmented, right?  And that's all that will matter for reads since I'm USING INDEX.  So as a novice, I don't know why having a non-fragmented PK layout helps much here.

And remember, I only care about doing retrieval on ranges of ssn.

So can we analytically say whether one is likely preferable to the other for space/speed reasons?  I imagine the latter would use more space?

Or would it just require an empircal benchmark?

thanks


texts: How can you have two auto-incremental columns in one table?

texts:
I have a MySQL table that contains information on invoices of a company. However, this company has two branches, and each of them has a unique invoicing sequence; a "Serie A" and "Serie B", so to speak. However, this is one single company and I do not want to create two invoice tables. Rather, I somehow want to have two different auto-increments for one table. I know this is technically not possible, but I guess this is a problem others have tackled before, so I'd like to know if there is a well-known 'solution' for this issue?

What I am doing right now is not using the primary key as invoice number (which would be ideal), but rather using a secondary column with the invoice id, which is incremented manually (well, using a PHP script, but it's still not automatic), by checking the latest invoice for that particular series.

This is my current setup:

CREATE TABLE `invoices` (
  `id` mediumint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `invoicenumber` mediumint unsigned NOT NULL,
  `branch` enum('A','B') NOT NULL,
  `date` date NOT NULL,
  `client` varchar(100) NOT NULL
) COMMENT='' ENGINE='InnoDB';

To check the lateset invoice, I run:

SELECT MAX(invoicenumber+1) AS new_invoice_number FROM invoices WHERE branch = 'A'


texts: Handling newsletter mail subscribers data

texts:
A simple data structure for subscribers is email, name, phone, building.

Currently, Building is a VARCHAR field, however data contained in the field is actually mostly numeric.  How can I change the field type to INT without losing data?

Also, if a subscriber sends a request for newsletter, with name, phone, address in the request, and he need to click on a confirm link for the request, what will happen if before he clicks the link, the data structure is changed?  For example, if the phone field is deleted, when I insert the new subscribers data, there will be problem because the database structure no longer matches.  How can I fix this?


texts:
I've installed PostgreSQL 12 server onto an Ubuntu 20.04 server, but I want to access it using a GUI from my Kubuntu 20.04 laptop. I'd be interested to know any options for GUI administration.

I have an old Access database that I used mdbtools to export all tables to csv files. I'm wanting to import those table into PostgreSQL, but I'd really like something nicer than the command line.

One option I know about is pgadmin, however, it isn't clear to me where I should install that. Do I install it on the server or my laptop? I see that it is a web interface, I don't want to run a web server on my laptop just to have a GUI for Postgres located on a server. I'd like some direction from someone who uses a GUI to administer PostgreSQL remotely.

I've used Access, DB2, SQL Server, MySQL, MarinaDB, and Pervasive, but this is my first time messing with Postgres. Hopefully, I've saved the best for last. Please advise.


texts: Export .MWB to working .SQL file using command line

texts:
We recently installed a server dedicated to unit tests, which deploys
  updates automatically via Jenkins when commits are done, and sends
  mails when a regression is noticed
   > This requires our database to always be up-to-date

  Since the database-schema-reference is our MWB, we added some scripts
  during deploy, which export the .mwb to a .sql (using python) This
  worked fine... but still has some issues

Our main concern is that the functions attached to the schema are not exported at all, which makes the DB unusable.

We'd like to hack into the python code to make it export scripts... but didn't find enough informations about it.
Here is the only piece of documentation we found.  It's not very clear for us.  We didn't find any information about exporting scripts.
All we found is that a db_Script class exists.  We don't know where we can find its instances in our execution context, nor if they can be exported easily.  Did we miss something ?

For reference, here is the script we currently use for the mwb to sql conversion (mwb2sql.sh).
It calls the MySqlWorkbench from command line (we use a dummy x-server to flush graphical output.)
What we need to complete is the python part passed in our command-line call of workbench.

# generate sql from mwb
# usage: sh mwb2sql.sh {mwb file} {output file}
# prepare: set env MYSQL_WORKBENCH

if [ "$MYSQL_WORKBENCH" = "" ]; then
  export MYSQL_WORKBENCH="/usr/bin/mysql-workbench"
fi

export INPUT=$(cd $(dirname $1);pwd)/$(basename $1)
export OUTPUT=$(cd $(dirname $2);pwd)/$(basename $2)

"$MYSQL_WORKBENCH" \
  --open $INPUT \
  --run-python "
import os
import grt
from grt.modules import DbMySQLFE as fe
c = grt.root.wb.doc.physicalModels[0].catalog
fe.generateSQLCreateStatements(c, c.version, {})
fe.createScriptForCatalogObjects(os.getenv('OUTPUT'), c, {})" \
  --quit-when-done
set -e

note: posted on SO last week, but with 13 views in 4 days (and 1 up!), I doubt it was the right place.
I'll remove it from SO if I get answers here

@Rick James's comment :

MySQLDump was our previous option, but we'd like something which doesn't require an action from the developper anymore.
It doesn't only save the time taken to export / commit the .sql, but more importantly time spent due to mistakes, debugging problems which happen only because one forgot to update the .sql.

An other "alternative" could be to manually export the .mwb via the
GUI, since it features a checkbox to "also export scripts", but
again, it's not mistake-proof, and we know it will cause problems
We might also end-up with an export of the functions in a separate
(manually created) .sql, and import separately both structure and
scripts files, since our functions are rarely modified... but this isn't the safest / more efficient way either

We thought MySQL Workbench would be easier to use with command line.  It's quite disappointing to see that such tool is missing accessibility for features... which are accessible via the GUI !


texts: Why Sqlcode 100 does not come with exception

texts:
I have following query in a PL\SQL procedure on Oracle 10.2:

This is the code

LOOP
  BEGIN

    SELECT a.poid_id0 into v_acc_account_poidid0
    FROM account_t a
    WHERE a.poid_id0=i_acct_id0
    FOR UPDATE OF a.poid_id0 NOWAIT;

    EXIT WHEN sqlcode = 0;

  EXCEPTION

    WHEN resource_busy THEN

      BEGIN
        v_max_retry_times_counter := v_max_retry_times_counter + 1 ;
        IF (v_max_retry_times_counter>v_max_retry_limit) THEN
          RAISE_APPLICATION_ERROR (ERROR_SELECTING,'Resource Busy with Nowait Option.',TRUE);
          EXIT;
        END IF;
        DBMS_LOCK.sleep(2);
      END;

    WHEN OTHERS THEN

      BEGIN
        RAISE_APPLICATION_ERROR (ERROR_SELECTING,'ORACLE ERROR DESCRIPTION'||sqlerrm ,TRUE);
        EXIT;
      END;

  END;
END LOOP;

The statement returns sqlcode 100 and does not throw an exception.

From the Oracle documentation I understand that the error code is accompanied by an exception. What may be the reason behind this behaviour?

The documentation says the error code 100 is DATA NOT FOUND; we have data for the select query.

In this context does DATA NOT FOUND mean the select is failing or the lock is not available?

Any help much appreciated.


texts: Cross database reference select access with disabled login for DB user

texts:
We have two databases (DB1, DB2) with the following view structure.

DB1:

T1, T2, T3, ..... Tn (Tables)
V1 (View consuming {T1, T2, T3 ..... Tn })

Inside DB2 we have to create a view V2 which will pull the data from DB1.dbo.V1

USE DB2;
CREATE VIEW dbo.V2 AS SELECT * FROM DB1.dbo.V1; -- with some business logic

Now the problem statement is, user created for the DB2 should not have any login access to DB1 but should be able to login on DB2 to consume the V2.

I don't want to disable the owner chain setting. Till now we have tried the below solution. But it enables the user to see the selected view (V1) inside DB1.

USE DB1;

CREATE View dbo.V1 AS SELECT * FROM T1; -- And some other tables according to business logic

CREATE USER testUser FOR LOGIN testUser;

GRANT SELECT ON dbo.V1 To testUser;

USE DB2;

CREATE View dbo.V2 AS SELECT * FROM DB1.dbo.V1; -- with some business logic

Please consider testUser is already present for DB2, I have just mapped it with the DB1 database.

This solution provides me the expected result. But it also allows testUser to login on the DB1 and fetch the data directly from DB1.dbo.V1. Is there any way to deny login access to DB1 but at the same time allowing select access to V2 created inside the DB2?

What we are trying to do over here is to create an user who will have a complete access to DB2. User should be able to fetch the data using the internal views as explained above. But should not able to login to DB1. In Linux/Unix based systems admin can create an internal user which can be used for the access definitions but can not be used for the logging in into system. We are looking solution to implement something similar in SQL.

If we don't grant select access to V1, server throws permission error:

  The SELECT permission was denied on the object V1, database 'DB1', schema 'dbo'.


texts: Assign root user password after installation

texts:
I have installed MySQL 5.7.9 on RHEL 6.5. After installation, when I tried to enter using system root password it is not able to login. I know I have to assign the password for MySQL root user but how can I assign the password when MySQL is not even letting me enter?

When I run the command:

#mysql -u root

It just kicks me out with this error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

I know after MySQL installation it generates the temporary password in /var/log/mysqld.log. But after looking into this file I found nothing except these errors:

0 [ERROR] Incorrect definition of table mysql.db:
          expected column 'User' at position 2 to have type char(32),
          found type char(16).
0 [ERROR] mysql.user has no `Event_priv` column at position 28
0 [ERROR] Event Scheduler: An error occurred when initializing system tables.
          Disabling the Event Scheduler.
0 [Note] /usr/sbin/mysqld: ready for connections.
         Version: '5.7.11' socket: '/var/lib/mysql/mysql.sock' port: 3306
         MySQL Community Server (GPL)
2 [Note] Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO)
3 [Note] Access denied for user 'root'@'localhost' (using password: YES)
4 [Note] Access denied for user 'root'@'localhost' (using password: NO)
5 [Note] Access denied for user 'root'@'localhost' (using password: YES)


texts: Permission to select sys tables in stored procedure

texts:
I have implemented the stored procedure to check if a specific [schema].[procedure] exists.

CREATE PROCEDURE [BlobCheckExisting].[usp_DoesStoredProcedureExist]
@schemaName varchar(128),
@procedureName varchar(128)
AS
SELECT IIF (EXISTS (SELECT 1 FROM sys.procedures p INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id]
    WHERE s.[name] = @schemaName  AND p.[name] = @procedureName), 1, 0)
GO

When I tested with sa it selected 1 as I expected but when I tried with a minimum permission account which only granted EXECUTE to the procedure, it selected 0.

My assumption of stored procedure mechanism was role/user only requires EXECUTE to perform the procedure.
We don't need to concern the permission setting of each DB object like CRUD to table and so on.

But it seems sys tables are exceptions? Or did I miss anything?
Moreover, If I don't want to grant select sys.procedures/sys.schemas to the role. I just want my procedure to work as expected, what should I do?


texts: Help with wait type CHECK_TABLES_THREAD_BARRIER

texts:
I have a 2016 SQL Server freshly built - 13.0.5149.0 - OS 2016 (Microsoft Windows NT 6.3 (14393)). The environment is VMWare. No SQL Server HA configuration.

It has a problem where CheckDB "stops" with a wait type of CHECK_TABLES_THREAD_BARRIER. It has happened twice on 2 different databases. I don't see much listed on online resources for what to do with this wait. Microsoft lists "TBD" on information. This is a non production proof of concept environment for an upgrade.

Can anyone provide any insight or next steps?


texts: Federate engine to help overcome distributed transaction?

texts:
Currently we have been using pure innodb. The issue is that we have architecture where we have multiple outlet each have its own local db and a copy of it in the hq outlet. The issue is that we involve distributed transaction which we know is not supported in mysql so what we do we build 3 transaction one for local db and one for the remote db  and one for the hq db. So it works most of the time but there are times it failed too. So we are looking how could federated engine help in this issue?

For e.g this table in hq outlet is updated according to the branch outlet columns TotalStock and Total Amount. There many other tables involved too.

CREATE TABLE `tblproduct` (
  `productID` int(5) NOT NULL AUTO_INCREMENT,
  `productCategoryID` int(10) NOT NULL,
  `productSubCategoryID` int(10) NOT NULL,
  `productBrandID` int(10) NOT NULL,
  `productGroupID` int(10) NOT NULL,
  `productStatusID` int(10) NOT NULL,
  `productCode` varchar(100) NOT NULL,
  `productName` varchar(100) NOT NULL,
  `productPrice` double(10,2) NOT NULL DEFAULT '0.00',
  `productType` enum('Accessory','Coupon','Imei') NOT NULL,
  `productTotalStock` int(10) NOT NULL DEFAULT '0',
  `productTotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `productBooked` int(5) NOT NULL DEFAULT '0',
  `productStatus` enum('y','n') NOT NULL DEFAULT 'y',
  `rrpMarkUpType` enum('Fixed','Amount','Percentage') NOT NULL,
  `rrpMarkUpFixed` double(6,2) NOT NULL DEFAULT '0.00',
  `rrpMarkUpAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `rrpMarkUpPercentage` double(5,2) NOT NULL DEFAULT '0.00',
  `branch1TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch1TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `branch2TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch2TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `branch3TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch3TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `branch4TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch4TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `branch5TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch5TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `branch6TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch6TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `branch7TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch7TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `branch8TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch8TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `branch9TotalStock` int(10) DEFAULT '0',
  `branch9TotalAmount` double(10,2) DEFAULT '0.00',
  `branch11TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch11TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `branch12TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch12TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `branch13TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch13TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`productID`),
  UNIQUE KEY `productName` (`productName`),
  UNIQUE KEY `productCategoryCode` (`productCategoryID`,`productSubCategoryID`,`productBrandID`,`productGroupID`,`productStatusID`),
  UNIQUE KEY `NewIndex1` (`productCode`)
) ENGINE=InnoDB AUTO_INCREMENT=8210 DEFAULT CHARSET=latin1


texts:
I'm using Postgres 9.1 and want to get a result with some blanks where there is no data.  My query looks like the following:

SELECT institution_id FROM ... WHERE institution_id IN (1, 3, 4, 5, 7, 9)

The ... is not important to this question, it's just important that it returns a result with the institution_ids in the array (1, 3, 4, 5, 7, 9) and it includes those institutions with no data. Here is an example of the current output:

days    treatments    institution_id
266    6996    4
265    5310    1
267    3361    5
260    2809    3
264    5249    7

An example of the output I want is:

days    treatments    institution_id
266    6996    4
265    5310    1
267    3361    5
260    2809    3
264    5249    7
               9

I know I can achieve this by using the following query:

SELECT *
FROM (
       SELECT institution_id
       FROM ...
       WHERE institution_id IN (1, 3, 4, 5, 7, 9)
     )
RIGHT JOIN generate_series(1,9) ON generate_series = institution_id
WHERE generate_series IN (1, 3, 4, 5, 7, 9)

However, this is extra work because generate_series(1,9) creates institution_ids I'm not interested in, it requires that I know the max institution_id a priori, and it introduces an unnecessary WHERE clause.  Ideally I'd like a query like the following:

SELECT *
FROM (
      SELECT institution_id
      FROM ...
      WHERE institution_id IN (1, 3, 4, 5, 7, 9)
     )
RIGHT JOIN (1, 3, 4, 5, 7, 9) ON generate_series = institution_id

Where (1, 3, 4, 5, 7, 9) is just an array that Postgres will use for the JOIN command.  I've also already tried [1, 3, 4, 5, 7, 9] and {1, 3, 4, 5, 7, 9} both to no avail.

Any ideas?


texts: How does the spec store JSON on a table?

texts:
From this question, I quoted the docs,

  "MariaDB implements this as a LONGTEXT rather, as the JSON data type contradicts the SQL standard, and MariaDB's benchmarks indicate that performance is at least equivalent."*

That spurred a comment

  I had to google and it appears that MariaDB now supports CHECK constraints. Not sure what they mean when they say that JSON contradicts the SQL standard? – Lennart yesterday

I want to know what they meant too. So what does MariaDB mean when say "contradicts the standard."


texts: How to find a list of local MS SQL Servers?

texts:
I looked at How can I determine installed SQL Server instances and their versions? already but CMD returns nothing:

C:\Windows\system32>sqlcmd -L

C:\Windows\system32>

But when using Visual Studio (v2019), I go to View > SQL Server Object Explorer, I see two SQL Server instances with system databases inside. One is called (LocalDB)\MSSQLLocalDB and the other (LocalDB)\ProjectsV13.

Both are using SQL Server 2016 (v13.0.4001). Using that I was able to connect through SSMS 2018 as well.

My question is: why aren't these instances showing up in the command line? And can I delete one of the server instance? Why are there two?

I have tried:

I checked my services and I there is only SQL Server VSS write. everything else is irrelevant to SQL. I looked up in SQL Server configuration manager but SQL Server services is empty there as well. I checked in C:\Program Files (x86)\Microsoft SQL Server\90\Shared and sqlbrowser.exe was not there also.
I just tried setting up inbound and outbound rules on TCP 1433 and UDP 1434 to allow connections. Failed again with nothing returned from cmd.
I turned on firewall logs and checked the logs after running the command a few times but nothing is being recorded (success or failure). I opened powershell and did test-netconnection to my ip and port 1433 and 1434 but the ping failed. Still nothing written to the firewall log files.


texts: Count Most Recent Browser Used By All Users

texts:
I have a table with columns Type, Login, and Timestamp. It has been collecting information about the type of browsers users log into the system with for years. Obviously many of the users have since upgraded their browsers. I do not want to consider data for old browsers when users have upgraded. Here is the query I currently have to get a count of how many people use which browser:

select
  Type,
  count(Type) as Count
from
  (select
    distinct Login,
    Type
   from BROWSER) as q
group by Type

This appears to work fine, but I have a lot of old browsers that I know are no longer being used:

How would I only select the most recent browser used by each user based on timestamp (or Browser_ID could work also since they are written sequentially). I also intend to ignore data by users who have not logged in during the past year, but that is simple to add later (I only mention it so that no one brings up why I would want browser data that is old for inactive users - I do plan on addressing that as well but I know how to do that already).

If anyone is up for a challenge, there are some users who log in from a couple different computers - or perhaps from their phone and from their desk. It would be great if I could determine that they frequently log in from two different places and also collect that data, but this might be too complicated. If anyone figured out how to add this complicated step, I'll gladly award a 50 point bounty to the answer.


texts: How to select the line with the max(date)?

texts:
the data looks as following:

id group date
1  a     2013-01-01
1  b     2014-01-01
2  a     2012-01-01
2  b     2013-01-01

My database is mysql, for each id, I need to select the line with max(date), I wrote the SQL as following:

select id,group, max(date)
from table1
group by id;

But it does not work well.


texts: Temporarily disable checking of constraints

texts:
I'm bulk inserting rows into a database and I'm finding (through the query analyser) that the foreign key constraints are taking about 20% of the insertion time. I've tried creating a separate index on the tables containing just the key so that the check could hopefully just do an index seek rather than a clustered index seek but this didn't seem to work.

The next thing I was planning on trying was to disable to check constraints temporarily so I was wondering if there is a way to do this on a per transaction basis.

Failing that I will probably try the performance with the constraints removed as they are also enforced in our DataAccess layer so in theory are redundant.

This SO question contains a possible solution, but it's more permanent than temporary.


texts:
I have a query to find the wait types in which my system is suffering from.

WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
       100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
        N'CHKPT', N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',

        -- Maybe uncomment these four if you have mirroring issues
        N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',

        N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC', N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',

        -- Maybe uncomment these six if you have AG issues
        N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',

        N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',
        N'ONDEMAND_TASK_QUEUE',
        N'PREEMPTIVE_XE_GETTARGETSTATE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK',
        N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
        N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_RECOVERY',
        N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
    AND [waiting_tasks_count] > 0
    )
SELECT
    MAX ([W1].[wait_type]) AS [WaitType],
    CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
    CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
    CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
    MAX ([W1].[WaitCount]) AS [WaitCount],
    CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S],
    CAST ('https://www.sqlskills.com/help/waits/' + MAX ([W1].[wait_type]) as XML) AS [Help/Info URL]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold
GO

Query is taken from Paul Randal's blog Wait statistics, or please tell me where it hurts

Now is there a way I can correlate the wait types with my actual queries. After some research I found we can configure extended events to capture the query with wait types.

Is there any other alternative through any DMV's can we get wait types along with queries.


texts: Keep local variable after rollback in mysql

texts:
I wish to keep local variable from stored procedure even if rollback occurres.

In following procedure, i "register" numbers to users. The procedure takes user_id, value_from and value_to as arguments. When I call the procedure with user_id = 1, value_from = 10, value_to = 20, it registered numbers 10, 11, ..., 19, 20 to user 1. Every number can be registered only once and only to one user.

There are two possible conflicts: I try to register number, which is already registered to another user, or I try to register number, which is already mine.

I wish to determine, which of these options occurred and store that value.

Here is my procedure, which takes also OUT parameter, which describes the result of the operation

-- create table
CREATE TABLE test
(
    value INT NOT NULL UNIQUE,
    user_id INT NOT NULL
) ENGINE = INNODB;

-- fast procedure with transactions
DELIMITER $$
CREATE PROCEDURE sp_insert_values(IN uid INT, IN val_from INT, IN val_to INT, OUT retval VARCHAR(25) )
BEGIN
    SET retval = 'ERROR_UNKNOWN';
    START TRANSACTION;
    SET @val = val_from;
    REPEAT
        -- is there already record, registered to some user?
        SET @uid = (SELECT user_id FROM test WHERE value = @val);
        IF @uid IS NOT NULL THEN
            SET retval = 'ERROR_ANOTHER_USER';
            -- is it already registered to me?
            IF @uid = uid THEN
                SET retval = 'ERROR_ALREADY_HAS';
            END IF;
            ROLLBACK;
        END IF;
        INSERT INTO test (value, user_id) VALUES (@val, uid);
        SET @val = @val + 1;
    UNTIL @val > val_to END REPEAT;
    SET retval = 'OK';
    COMMIT;
END$$
DELIMITER ;

The problem is, that ROLLBACK not only throws away all new records in table test, it also resets the value of @retval.

Is there any way to keep the custom error message even in case of ROLLBACK

-- test - should return OK - work fine
SET @retval := 'UNKNOWN';
CALL sp_insert_values(1, 10, 20, @retval);
SELECT @retval;

-- test - should return 'ERROR_ANOTHER_USER' - throws error and resets @retval, should
SET @retval := 'UNKNOWN';
CALL sp_insert_values(1, 1, 30, @retval);
SELECT @retval;

-- test - should return 'ERROR_ALREADY_HAS' - throws error and resets @retval
SET @retval := 'UNKNOWN';
CALL sp_insert_values(2, 15, 30, @retval);
SELECT @retval;


texts: "ORA-12899: value too large for column "V_UUID" (actual: 36, maximum: 4000)" when used to generate a Virtual column

texts:
I have the following function:

CREATE FUNCTION UUID_AS_HEX(bytes IN RAW)
  RETURN CHAR DETERMINISTIC
  IS uuid CHAR(36);
  BEGIN
    RETURN REGEXP_REPLACE(bytes, '([0-9A-F]{8})?([0-9A-F]{4})?([0-9A-F]{4})?([0-9A-F]{4})?([0-9A-F]{12})', '\1-\2-\3-\4-\5');
  END;

and I want to create a virtual column:

CREATE TABLE example_table
(
  uuid               RAW(16)                                   NOT NULL PRIMARY KEY,
  v_uuid             CHAR(36) GENERATED ALWAYS AS (UUID_AS_HEX(uuid)) VIRTUAL
);

throws the following exception:

ORA-12899: value too large for column "V_UUID" (actual: 36, maximum: 4000)

I tried creating the function with RETURN CHAR(36) DETERMINISTIC but it complains about that syntax with

2:14:PLS-00103: Encountered the symbol "(" when expecting one of the following:
; is authid as cluster order using external varying character

How do I create this function where it match the CHAR(36) in the table?


texts: How to check if a subquery has exactly one distinct result and a specified value concisely?

texts:
I found myself writing the following:

select 'yes'
where exists(select * from foo where val=1)
and not exists(select * from foo where val<>1);

and wondering if there is a more concise way without sacrificing too much readability.

I found one way which I am posting as an answer but I'm not entirely happy with it and would be very interested in alternatives

In this case val is unique within foo - there are no duplicates


texts: Quite possibly a stupid and long authentication question

texts:
First off, I am not remotely a DBA.  But I'm basically responsible for the application and we don't have a DBA, so here I am.  There's two separate errors that may be related but occur at separate times.  We have an application (Solarwinds) which has 12 servers that connect back to the SQL Server (11.0.6248) using SQL Authentication.  99.9% of the time it works fine.  Then, randomly out of the blue the following issues occur (issues occur at separate times).

Issue 1

Login failed for user 'username'.  Reason:  Password did not match that for the login provided. [CLIENT:  ipaddress]
Error: 18456, Severity: 14, State: 5.

Username is the SQL account that all 12 servers are using, and have been using for ages.  Password hasn't changed.  Client is always the "main" application server (Primary poller in Solarwinds terms).  At that point the application does a crash and burn.  Restart servers and it's back up and everything is fine until it does it again.  This happens once or twice a month.

The credentials the application use to connect are obviously good as it works the other 99% of the time.  If you're not familiar with the application it's doing real time server and application monitoring so there's constantly data being written to it.  There's nothing else installed on the application server the error points to nor are there any other databases on the SQL server.  Both boxes are dedicated to this application.

Issue 2

Again, out of the blue, application has been running fine.

Login Failed for user 'username'.  Reason:  Access to server validation failed while revalidating the login on the connection.  [CLIENT: ipaddress]
Error: 18456, Severity: 14, State: 23.

So far all my Googlefu has found for that error references specifically SQL Server 2005 and a hotfix for the issue.  As mentioned above, we're running SQL Server 2012.  As with the other error, this occurs out of the blue at random times and again, restart services, and everything going back to functioning normally.

What am I missing here?  The vendor says it's not an application issue and I see nothing to dispute that.  It's safe to assume SQL Server isn't setup remotely close to anything resembling best practices but I don't know where to start with that.


texts: DATEADD and DATEPART with milliseconds yields strange results

texts:
I have a table containing the date in unix time and a separate milliseconds field. I now try to create a date from the two fields for later calculation (e.g. filtering on a time range). After adding the milliseconds to the date created via ...
 dateadd(S, [timestamp_s], '1970-01-01')

by adding another DATEADD ...
dateadd(MS, [timestamp_ms], dateadd(S, [timestamp_s], '1970-01-01')) eventdate

... and then output the date the milliseconds are sometimes one millisecond off. Out of curiousity I then tried to extract the milliseconds just to see what this gives and its again 1 millisecond off.

I think it has to do with internal floating point precision but I don't see any rule in the data. Sometimes each operation takes 1 MS off, sometimes the first will subtract 1 but the DATEPART will then add 1 again, etc.
As this might cause frustration with some of the users I would like to understand the behaviour and ideally find a solution to the problem. Thanks in advance.


texts: How the first IAM page is found by SQL Server

texts:
Today, just of curiosity, I was checking the logs when the first allocation for a new table in a new database is made.

I can see that GAM was used to find a free extent and then a page from this extent is allocated to be used as a data page. Rows 8-12.
And then it allocates another page which isn't from the extent used before and uses this page as IAM. Row 13-16.
My question is, from where SQL Server finds this page and make it as IAM. Why this page is not allocated from an extent which is accessed by GAM.


texts: Unable to reset the password of users in postgres

texts:
Evrytime i run, su - postgres it asks me for a password which I did not set. I simply installed the postgres. I have gone to the pg_hba.conf.

# Database administrative login by Unix domain socket
local   all             postgres                               trust

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               ident
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

I changed the postgres method from ident to trust and then ran sudo /etc/init.d/postgresql restart. Even then it continues to ask for password. i am not sure where am I going wrong?

What should I do?


texts:
Can somebody explain me what is lock pinger in MongoDB

  [LockPinger] cluster XXXXXXX:27019 pinged successfully at Wed Feb  5
  04:46:01 2014 by distributed lock pinger
  'XXXXXX:27019/ip-XX-XX-XX-XX:27017:1391512883:1804289383', sleeping
  for 30000ms


texts:
I want to find classical Greatest N per Group. I have two methods to solve this problem

Dense_rank Over() method
Min Over() method

Both work flawlessly. Now I want to find which one is better and why.

Sample data:

CREATE TABLE #test
    (
        id    INT,
        NAME  VARCHAR(50),
        dates DATETIME
    );

WITH cte
    AS (SELECT TOP (100000) n = ( (Row_number() OVER (ORDER BY a.number) - 1) / 3) + 1
        FROM   [master]..spt_values a
            CROSS JOIN [master]..spt_values b)
INSERT INTO #test
SELECT n,
        Getdate() + n
FROM   cte ;

Dense_rank Over() Method:

WITH cte
     AS (SELECT Dense_rank() OVER (partition BY NAME ORDER BY dates) AS rn,*
         FROM   #test)
SELECT id,
       NAME,
       dates
FROM   cte
WHERE  rn = 1;

Min Over() Method:

WITH cte
     AS (SELECT Min(dates) OVER (partition BY NAME) AS max_date,*
         FROM   #test)
SELECT id,
       NAME,
       dates
FROM   cte
WHERE  max_date = dates ;

To compare the performance I checked on execution plan which said both query cost is 50%. But Max Over method execution plan looks little complex. So any guidance here will be helpful. I don't have very good understanding in execution plans.

Execution Plan:(6 rows)

Execution plan:(100000 rows)

For 100000 rows the execution plan says

Dense_rank Over() Query cost : 46%
Min Over() Query cost        : 54%


texts: Trigger To Find Deleting Job Agent History

texts:
We are trying to debug why SQL Agent Job history is getting deleted every 8 hours. In reading the following article, it suggests many great ideas like:

Create a trigger to check what application is deleting the job history
Check to see if we have a Job that is perhaps purging the job history
Check if the sp_purge_jobhistory stored procedure in msdb database is being used manually to delete the SQL Server agent job history

In the article, the trigger they created returned the following as the "Application Name" value:

SQLAgent - Step History Logger
SQLAgent - Job Manager

How would I create a trigger like this?


texts:
I am trying to figure out how to store time series data for an ad platform I am working on.

Basically I want to know some strategies/solutions for storing billions of rows of data so that I can easily search it (about 6-8 indexes on the table) and get fast counts based on queries.

I tried mySQL with the tokuDB engine and this seems to be very fast but is extremely slow when I try to do a COUNT query when the rows reached about 5-8 million.

I was looking at some noSQL alternatives but since I want to be able to search this data this is probably not the best solution. I was using dynamoDB. I would have had to store the data is many places in order to account for all the searching on the data.

What I am storing is a row in the database for each click on an AD that occurs. This table will grow very fast, especially when this site gets large.

Another solution would be to separate this data per advertiser. This means each advertiser will have their own table where all their data goes into. This means it will be much smaller and the COUNT queries will be much faster. I can even split it up by advertiser and month.

My goal is to give an advertiser the ability to search and display in a paginated way all their clicks. They should be able to get data between a time period and filter by about 5-8 other indexes if they want to.


texts: Why might a table scan have a huge I/O Cost?

texts:
I know that table scans are bad, and that every table should have a primary key and clustered index, but I'm wondering what might cause the spectacularly bad performance that I'm seeing below in SQL Server 2008 R2.

For testing's sake, I am doing a SELECT * FROM myTable on a table/heap with 1960 rows.  The table has the following columns: (int, datetime, int, int, float, smallint, uniqueidentifier). There are no indexes.  This table has had a long history of inserts and deletes.

I get the following stats from the table scan in the query plan:

<RelOp
  AvgRowSize="57"
  EstimateCPU="0.002313"
  EstimateIO="74.4157"
  EstimateRebinds="0"
  EstimateRewinds="0"
  EstimateRows="1960"
  LogicalOp="Table Scan"
  NodeId="0"
  Parallel="false"
  PhysicalOp="Table Scan"
  EstimatedTotalSubtreeCost="74.418"
  TableCardinality="1960">

And from SET STATISTICS TIME ON:

Table 'myTable'. Scan count 1, logical reads 100458, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

What factors could lead to such a small number of small rows requiring such a massive number of reads (and have the query plan predict the same)?


texts: Index Exists, data is present, but statistics are empty. Why?

texts:
I’ve created a dump of all statistics on all tables in a Production database (based on the three sets returned by DBCC SHOW_STATISTICS). The database has been up and running for several months, and is actively used around the clock.

Reviewing it all, I find that several statistics are not actually set. That is, the statistics entry is there, but “LastUpdated” is null, and there are no steps in the histogram table. I am not able to find a consistent reason for this—some are for indexes, unique indexes, filtered, indexes, filtered unique indexes, and auto-generated stats.

Why is this? I can see a handful of remotely possibly reasons. Many are for where there is no data in the table (this makes sense), but there are many that do have rows (up to 10s of millions), and for any reason that might barely explain it ( “first-column duplicates” of other stats/indexes, unique indexes, filtered indexes, unlikely datatypes such as bit or large nvarchars), I can find something similar in another table/index where statistics are present.

I’m mostly convinced it’s not just one single thing. What are the possible reasons that statistics might not be present for tables under these conditions? How worried should I be about this?


texts: Is there Such Thing As The Opposite of Unique Constraint

texts:
I want to be constrain my database with what is essentially the opposite of a UNIQUE constraint. I want to only be able to add a new row if some combination of fields has been added before.

Note: I am working within postgres.

Take the following datasets for example. I want a "NON UNIQUE" constraint on the following:

1, a, 1

1, a, 1

1, b, 1

I should not have been able to add the row (1, b, 1) since it has not appeared before. There are also other fields I don't want to constrain so it's not like all the entries are identical.

This may be a separate question, but I would also like to add the constraint that all rows with the same value in one column (i.e. a in the above example) have the same value in another.

So I want this to be impossible

1, a, 1

1, a, 1

0, a, 1

But this is ok

1, a, 1

1, a, 1

0, b, 1

I have looked around pretty heavily and the opposite of this is possible
with unique constraints, but I can't find anything that works for this.
Check constraints will only look at the row in question, and won't take into account the entire table's entries like a unique constraint would.

Is this even possible? Or is this now out of the realms of Postgres functionality, and must be done in-application.


texts: Matching string with LIKE derived from sub-query

texts:
Here is my query Where I want to match a string with a subquery output. But it is returning wrong result. What I am doing wrong? The concat part is not working as I try to run

Concat('"', (SELECT sec_dep
                                  FROM   admin_users
                                  WHERE  uid = 11), '"')

this part only. it shows error.

SELECT *
FROM   issues
WHERE  ( assigned_to_dept LIKE Concat('"', (SELECT sec_dep
                              FROM   admin_users
                              WHERE  uid = 11), '"')
              OR assigned_to_dept LIKE Concat('"%,', (SELECT sec_dep
                                FROM   admin_users
                                WHERE  uid = 11), '"')
              OR assigned_to_dept LIKE Concat('"', (SELECT sec_dep
                              FROM   admin_users
                              WHERE  uid = 11), ',%"')
              OR assigned_to_dept LIKE Concat('"%,', (SELECT sec_dep
                                FROM   admin_users
                                WHERE  uid = 11), ',%"') )

thanx in advance.

UPDATE

admin_users Table

CREATE TABLE `admin_users` (
 `uid` int(11) NOT NULL AUTO_INCREMENT,
 `pri_dep` int(11) NOT NULL,
 `sec_dep` text NOT NULL,
 PRIMARY KEY (`uid`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

issue table

CREATE TABLE `issues` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `title` varchar(250) NOT NULL,
 `description` text,
 `assigned_by` int(11) NOT NULL,
 `assigned_to_users` text,
 `assigned_to_dept` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8


texts: How to dump only create table statements (or table definitions only) with pg_dump in Postgresql

texts:
I want only the table definitions, not wiews nor functions.

I have a schema named "cvp". I try:

    pg_dump --schema-only -n cvp -t 'cvp.*' --format plain

And I get:

pg_dump: No matching tables were found

I try also without -n cvp or changing -t to -t *.* -t cvp.

I'am using PostgreSQL 11.


texts: ORDER from 2 tables to achieve a specific order returned by an API

texts:
There's an external API that returns image similarities, based on an "image ID" and a "image version" (a same image can have multiple versions).
The similarity ORDER is given by the API.

The API returns something like this:

+---------+---------+
|   id    | version |
+---------+---------+
|  37967  |    2    |
|    236  |    1    |
|  37967  |    1    |
|   1413  |    2    |
+---------+---------+

Then I need to retrieve entries in a MySQL database (containing 2 tables), and keep the same ORDER as the one returned by the API.

That's where I'm having a problem. Here are the 2 tables:

"img" MySQL table:
+---------+-----------------------+
|   id    | lots of other columns |
+---------+-----------------------+
|    236  |         data          |
|   1413  |         data          |
|  37967  |         data          |
+---------+-----------------------+

"vers" MySQL table:
+---------+---------+
|   id    | version |
+---------+---------+
|    236  |    1    |
|   1413  |    1    |
|   1413  |    2    |
|  37967  |    1    |
|  37967  |    2    |
|  37967  |    3    |
+---------+---------+

the closest result I can get is by using ORDER BY FIELD, but it's still not the same ORDER as the one returned by the API.

My query:

SELECT i.id, v.version
FROM img i
LEFT JOIN vers v ON i.id=v.id
WHERE ((i.id=37967 AND v.version=2)
OR (i.id=236 AND v.version=1)
OR (i.id=37967 AND v.version=1)
OR (i.id=1413 AND v.version=2))
ORDER BY FIELD(i.id, 37967,236,37967,1413), FIELD(v.version,2,1,1,2)

Results:
+---------+---------+
|   id    | version |
+---------+---------+
|  37967  |    2    |
|  37967  |    1    |
|    236  |    1    |
|   1413  |    2    |
+---------+---------+

As you can see, the order is not exactly the one returned by the API :(

Any help would be appreciated, thank you all in advance.


texts: tsql to find out memory, cpu and IP utilization by a databases over a week - MS SQL Server 2005 and above

texts:
I want to identify resource (CPU, memory and IO) usage by a database on a SQL 2005 and above server, over a week or a month. Can anyone please help in this?

Thanks in advance


texts: Meaning of "Dependency" in Relation to Elementary DB Normalization

texts:
In trying to figure out why the following table definitions from a textbook exercise about nurses in hospital wards is not in 2NF:
Ward (WName, Location, WType)
Ward-Nurse (WName(fk), NurseID, NurseName, TeamCode, TeamSkill,
Shift)
what is the meaning of "depends on" when describing how some attributes don't "depend" on the whole key? Is it something like "you don't need to know the ward name in order to know what shift a nurse is working" for example?
What is the thought process by which it becomes obvious that for this DB to be in 2NF I need to change the definition to
Ward ( WName, Location, WType)
Ward-Nurse ( WName(fk), NurseID(fk), Shift)
Nurse ( NurseID, NurseName, TeamCode, TeamSkill)
Likewise for 3NF, what interpretation of the word "depends" and its application to the table definitions above allows me to know that the solution for 3NF is
Ward (WName, Location, WType)
Ward-Nurse ( WName(fk), NurseID(fk), Shift)
Nurse ( NurseID, NurseName, TeamCode(fk))
Team (TeamCode, TeamSkill)
All the above being from a textbook exercise where no explanation is given beyond the proposed table definitions.


texts: How do I group by Quarters in Google big query/dbt?

texts:
I have a table with 6 columns and I want to group them by quarters of the year, so far this is my code

SELECT

DATE_TRUNC(date(timestamp_column), QUARTER),
Column_2,
Column_3,
Column_4,
MAX(column_4),
MAX(column_4)

from table

group by 1,2,3,4 order by 1 desc

This returns the dates in order but they are not grouped by quarter. Does anyone have any idea on the solution to this


texts:
I am trying to shutdown the system, then I want to mount it. I have tried this:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
SQL> startup mount;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist

I have googled for solution but I can't found the exact reason that causes it to go wrong.


texts: Implicit conversion causing error part of the time

texts:
I have been scratching my head about this one for the last day or so - I just can't see why a procedure works in one environment, but then fails in another due to a conversion error (same data, same code).

The plan for (a heavily cut down version of) the working server is here: https://www.brentozar.com/pastetheplan/?id=B1jZWTfOf

(This doesn't seem to be working, so I've uploaded the plan XML to pastebin here: https://pastebin.com/47Q6nniw)

A bit of background for the relevant columns causing the problem:

The propertyInst table holds a column called ValueStr, which is an nvarchar data type. The GeneralLedgerCode table has a column id of data type int. Our developers are attempting to join between the two tables. The ValueStr column holds text data, XML data, int data, decimal data etc. As a matter of course we decided to add a function to ensure only integers are parsed (ISNUMERIC(ValueStr) = 1). What we didn't realise is that this would then also try to convert any decimal values - this is causing a conversion error in production of the nature:

Conversion failed when converting the nvarchar value '0.1' to data type int.

My question here is, given that the Table Scan of the propertyInst table will be picking up all numeric values including decimals, why is the implicit conversion in the scalar operator then not failing with the same conversion error? The output of the ISNUMERIC query returns some of these decimals. I can't see how the attached plan is working at all.

Does an implicit conversion operator never fail outright, and is the conversion error coming from the probe residual in the hash match operator? Then again, how could the value even make it to the operator when it would fail the implicit conversion?

As a small assistance, the plan image is here, with the Compute Scalar I'm asking about circled.

In addition, I can see that there are no actual rows logged against the scalar operator - does this mean the engine decided not to use that particular operator at runtime due to the conversion error?

Any help would be appreciated.


texts: Impossible to create an SQL Server function that calls an Oracle function?

texts:
I have been trying too many hours to create an SQL Server Scalar Function that calls an Oracle function.
Imagine you have a function in oracle named "Stock_Orcl" that returns the stock for a product passed in parameter and you want an SQL server function (named "Stock_sql") that calls the oracle function above "Stock_Orcl".
To make it harder I do not want to make a CLR function.
After too much searching and digging, I dare to say that I think this is impossible.I did create an SQL server procedure with success.
It seems impossible to turn it to a function for 2 main reasons:

SQL server Functions cannot call procedures that contain exec statement
SQL server Functions cannot contain an openquery dynamic statement

Has anybody any experience with that?
Thank you,
Andreas


texts:
I hope this is the right place to ask this question. I'm a user at StackOverflow, and when I perused the StackExchange websites, this was the best match I could find. So, if I've posted in the wrong place, please let me know and I'll move the post.

I'm working with a group of people and we're creating a start-up investing firm. I'm the "tech guy" in the group (I can build PCs and program), and so it's up to me to figure out what we need for our database server.

We're going to use SQL Server because it's what I know (so, please don't suggest MySQL, Oracle, etc...).
I'm familiar with purchasing components and assembling a PC, although I've never done it specifically for a high-performance database server before.
Assume we have a several thousand dollar budget (my co-investers are doctors).
Assume we have a constant stock market data feed (something like 100 stocks at a time) that needs to log the data to a database as it comes in and also use the current data to query for similar past data and perform comparisons.

What I'm mainly interested in is where to spend more money and where additional power is less useful.

For instance, when it comes to drive set up, I was thinking of something along the following lines:

For the OS and SQL Server application installation, a RAID 1 array of 2 60 GB SSDs.
For the SQL Server data volume, a RAID 5 array of 4 medium-sized, fast HDDs (I'm thinking 300GB Velociraptor drives; So far, 9 YEARS of data takes up only about 100GB).
For the SQL Server log file volume, a RAID 1 array of 2 small-to-medium-sized, adequate HDDs (non-Velociraptor here).
For the SQL Server temp database volume, a RAID 1 array of 2 60 GB SSDs.

The OS/SQL Server application volume will be a software RAID using the motherboard; the rest of the RAID arrays will be controlled by a dedicated RAID card.

From what I've read online, the above setup is likely to give me great performance and stability.

Now, when it comes to other components, what's necessary?

Is the latest i7 Quad Core useful for this, or is that overkill with processing power? Can I get away with an i5? An i3? A Core 2 Quad?
I'm assuming that a 64-bit OS with 64-bit SQL Server is better because it will allow me to go beyond 4GB RAM. If that's true, is 8GB enough or do I actually get performance benefits at 16GB? Does DDR3 RAM offer significant performance enhancements?
I know that the graphics on this machine are extremely unimportant, but would a dedicated graphics card off-load work from the CPU, increasing performance, or is that a waste of money?

Most of the work I've done with SQL Server has been for small companies. As a result (due to price), we've mostly done installations of SQL Server Workgroup. For everything I've ever done, that's been just fine and dandy, and I'd like to continue using that version. Is there any practical benefit to upgrading to Standard?

Now, I'm guessing that for our needs, a server like this will cost about $5,000-$6,000. Am I better off buying the components myself and building the system or buying a server from Dell/HP?

Also, (and I know this is a big wrench in things), if, during the initial, testing phase, we decided that we didn't want to spend $6,000, what cuts would you make to bring this down to a $2,000 system?


texts: Where can I find a compatible version of SSMS for SQL Server 2016 CTP?

texts:
Microsoft seem to have many versions of SQL Server 2016 CTP available at a time to download, at only 2.5GB a piece, but only one version of SSMS 2016 CTP, and if your db engine is the wrong version for SSMS, they don't suggest another version of SSMS, but another bloody version of the actual engine. This is quite ridiculous. I have a trial version of Toad that easily connects to any nearly DB engine version.

My SQL Server engine version is:

Microsoft SQL Server 2016 (CTP3.0) - 13.0.700.242 (X64)
Oct 26 2015 18:46:06
Copyright (c) Microsoft Corporation Express Edition (64-bit)
    on Windows 10 Pro 6.3 <X64> (Build 10586: ) (Hypervisor)

Does anyone know where I can get a version of SSMS 2016 that is compatible with this version?

The version of SSMS I have is the one downloaded from Rob Klimes's link in his comment below. It is version v13.0.800.111. When I try and run it, it shows me this:

The log file is large, and I don't know which parts are relevant enough to post here, but I don't see any glaring errors.


texts: Why is it that when I drop a field in table it says `#1050 - Table already exist`

texts:
I want to drop some fields of a table but it returns an error.
the query is..

ALTER TABLE `tbl_incoming_bub` DROP `approved_date`, DROP `approved_by`, DROP `incoming_date`, DROP `incoming_by`, DROP `obligate_date`, DROP `obligate_by`, DROP `journal_date`, DROP `journal_by`, DROP `ana_date`, DROP `ana_by`;

and the error is.. #1050 - Table 'ncddp/#sql-ib176' already exists

Here is the screenshots..


texts: READ_COMMITTED_SNAPSHOT support in SQL Server 2005 Express

texts:
I wonder if SSE 2005 supports the READ_COMMITTED_SNAPSHOT database option. What I found is this article: Features Supported by the Editions of SQL Server 2005. It states that SSE 2005 supports Snapshot Isolation Level (Row-level Versioning), but doesn't support Database Snapshot.

Does it mean that READ_COMMITTED_SNAPSHOT is not supported? Or it is about some other database snapshot feature?


texts: SQL SERVER 2014 - SELECT INTO, ORDER BY - automatic clustered index?

texts: When I create a table using SELECT INTO A, and it has ORDER BY x, does SQL server automatically make x the primary key of A (thus auto create a clustered index)?


texts:
Followed guide Auto-starting instances .

[root@oktest-stage-db-1 ~]# db2greg -getinstrec instancename=db2inst1 | grep -i startatboot
   StartAtBoot  = 1
[root@oktest-stage-db-1 ~]# /opt/IBM/db2/V10.5/instance/db2iauto -on db2inst1
[root@oktest-stage-db-1 ~]# reboot
...
[root@oktest-stage-db-1 ~]# pidof db2sysc
[root@oktest-stage-db-1 ~]#

(db2sysc is the main db2 process).

I expected db2iauto to create a script in /etc/init.d/ , but there are no new scripts there.


texts: Operand data type time is invalid for add or subtract operators

texts:
I'll to calculate how many hours a person has worked on SQL server. For that I'm storing the Arrival, Departure and Break in my database table as time(7). A bit for DepartureNextDay determinate that the Departure is on or after midnight or not.

Here is an example of my data:

| ID | Arrival  | Depatrure | Break    | DepartureNextDay |
| -- | -------- | --------- | -------- | ---------------- |
| 1  | 23:00:00 | 01:00:00  | 00:00:00 | 1                |
| 2  | 08:00:00 | 17:00:00  | 01:00:00 | 0                |

Now I'll calculate the worked hours using this formula:

Departure - Arrival - Break

When DepartureNextDay is 1 there must 24 hours being added to Departure to get a positive result. So the results must be this:

| ID | WorkedHours |
| -- | ----------- |
| 1  | 02:00:00    |
| 2  | 08:00:00    |

For this I'm using this code:

select (Departure + (case DepartureNextDay
                         when 1 then convert(time(7), '24:00:00') --> this is invalid too because it's bigger then 23:59:59
                         else convert(time(7), '00:00:00')
                      end
                    )) - Arrival - Break as WorkedHours
from WorkTable

But I've this error:

  Operand data type time is invalid for add/subtract (when case is removed) operator.

How could I do this?

P.S.: I'm storing the DepartureNextDay on a different column because:

time(7) must between 00:00:00 and 23:59:59 (most important reason)
It's more logical to store 1 o'clock A.M. as 01:00:00 and not as 1.01:00:00 or 25:00:00 and keep DepartureNextDay in a different column.


texts: How to get useful error information from mysql command?

texts:
I'm trying to script the creation of a database with tables. I'm encountering errors but the error messages are not helpful to me because I'm a novice.

I'm finding the mysql tool is providing mostly useless error messages. It is telling me there's an error but it is not telling me what the error is. mysql -v just echos the command, and does not detail the actual error.

My question is, how do I get useful error information from mysql command?

Here is an example of the script:

# Whitelist table
mysql -v -u"${MARIADB_USER}" -p"${MARIADB_PASS}" -e '
    USE '"${MY_DB}"';
    CREATE TABLE IF NOT EXISTS '"${MY_WHITE_TABLE}"'
    ("key" INT PRIMARY KEY NOT NULL AUTO_INCREMENT, "number" VARCHAR(20) CHARACTER SET utf8);
'

Here is an example of the error message:

--------------
CREATE TABLE IF NOT EXISTS whitelist
        ("key" INT PRIMARY KEY NOT NULL AUTO_INCREMENT, "number" VARCHAR(20) CHARACTER SET utf8)
--------------

ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the ma
nual that corresponds to your MariaDB server version for the right syntax to use
 near '"key" INT PRIMARY KEY NOT NULL AUTO_INCREMENT, "number" VARCHAR(20) CHARA
CTER SE' at line 2

I've tried leaving the column name unquoted, single quoting the column name, and double quoting the column name. At this point I believe the problem lies elsewhere, but I don't know where.


texts: How can I synchronise Always Encrypted data from On-Premises to Azure SQL Database (near real time)?

texts:
Let's say I have On Premises SQL Server 2016 SP1 and Azure SQL Database.

There is no way at this point to redirect whole solution directly to Azure SQL Database and I need to have a copy of the database in Azure (few tables in fact). It is required for some real-time analytics purpose. Data in Azure SQL Database can be decrypted (probably even should be).

Now, I know that there are solutions like:

Azure Data Factory (15 min delay)
Azure Sync (5 min delay)
Transactional Replication (near real time)
Self-developed application to read-decrypt-send data (near real time)

It looks like Replication fits our requirements. I tested it and I am able to establish this solution in my environment. Except that Replication doesn't support Always Encrypted feature and this is huge disadvantage.

Self-developed application is not good resolution either because a schema of tables can vary over time (replication resolve this by itself) and there will be probably other problems which pop up in the future. I tried it and it works but only with a hard-coded schema (number and names of columns).

Other solutions, I haven't tested them yet:

Strech Database (depends upon Azure Subscription, currently not available for me)
Third-party Software (which one? HVR?)
SSIS (looks like a good approach, but a schema can vary)

Is it possible to synchronise Always Encrypted data near real time using those tools or in any other way? Do you have any experience in that matter?


texts: How To Query Databases in Subject-Predicate Format?

texts:
I have csv data in this format:

id   fieldname fieldvalue
--   --------- ----------
1    fieldA    A1
1    fieldB    B1
2    fieldA    A2
2    fieldA    B2

If I upload this to MySQL, how can I convert the data easily to the conventional "relational format":

id   columnA   columnB
--   -------   -------
1    A1        B1
2    A2        B2

Otherwise how can I query data in this format? I’d like to be able to do things like SELECT * FROM table_name WHERE x=y OR a=b AND t=w. Are there database software that specifically deal with this sort of data?

Lastly, is there a systematic name for such data representation? eg. we have "relational" data, XML data. I remember reading on the .rdf format standard and recall that it does resemble that, but I am not sure if there's a more general name.


texts: Multiple Instances and Availability Groups

texts:
In my scenario, I have two servers in each of them two SQL Server instances:

Server 1: Instance 1 and Instance 2
Server 2: Instance 1 and Instance 2

I created availability groups between the instances like this:

Instance 1 from Server 1 with Instance 1 from Server 2: Availability Group 1
Instance 2 from Server 1 with Instance 2 from Server 2: Availability Group 2

And I have create a two listeners:

Listener 1, for Availability Group 1
Listener 2, for Availability Group 2

The problem is when I try to connect with the Listener 1, it connects me to the Availability Group 1, but when I try with Listener 2 it also connects me to Availability Group 1, not to Availability Group 2.

I don't understand what I'm doing wrong.


texts: Different Result When Insert Data Contain Quote on Localhost and Online MySQL Hosting

texts:
I installed xampp on localhost and insert data contain quote using this php code

$content    = htmlentities($_POST['content']);
$content_esc = mysql_real_escape_string($content);
$save = mysql_query("INSERT INTO tbl_post (title,content,date,publish) values('$title_esc','$content_esc','$date','$publish')");

The result on localhost website is

this is 'example data'

The row on database filled with this

this is &quot;example data&quot;

But using the same code to insert data the result from my hosting provider is different

this is \'example data\'

The row on database filled with this

this is \&quot;example data\&quot;

I test my website on localhost, insert post contain quote and it show on page correctly. Then i surprise when i upload on online hosting, insert post and the post page show '\' before the quote.

At this time i replace the '\' with nothing. How to insert the quote so it can show same result on local mysql database and online mysql database ? What is wrong, my php code or my mysql database ?