texts: pgbouncer 1.7 with TLS/SSL client and server connections

texts:
With settings borrowed from a similar server running Postgres 9.4.1 & pgbouncer 1.6.1, I have multiple users connecting to the database through pgbouncer on port 6543. I also have a second server running PostgreSQL 9.4.5 where I have verified that all users are only able to connect directly to the databases (on port 5432) using TLS/SSL set to verify-full.

However, I need to create an environment combining these configurations: one where all users connect to the database via TLS/SSL with connection pooling through pgbouncer. That means I need to use the new TLS/SSL functionality in the recently released (as of Dec 18, 2015) pgbouncer 1.7, but aside from documentation of the new TLS parameters, I have not found any examples available demonstrating the new functionality nor had any success on my own establishing a valid connection through pgbouncer using TLS/SSL on my second server.

I have included pertinent excerpts from the postgresql.conf, pg_hba.conf, & pgbouncer.ini from my second server.

postgresql.conf:

ssl = on                                # (change requires restart)
ssl_cert_file = 'server.crt'            # (change requires restart)
ssl_key_file = 'server.key'             # (change requires restart)
ssl_ca_file = 'root.crt'                        # (change requires restart)

pg_hba.conf:

hostssl    all             all             10.10.5.0/24            cert

pgbouncer.ini:

;
; pgbouncer configuration
;
[databases]
mydatabase = host=localhost port=5432 dbname=mydatabase
;
[pgbouncer]
listen_port = 6543
listen_addr = *
admin_users = lalligood, postgres
logfile = /tmp/pgbouncer.log
pidfile = /tmp/pgbouncer.pid
ignore_startup_parameters = application_name
server_reset_query = DISCARD ALL;
pool_mode = session
max_client_conn = 1000
default_pool_size = 300
log_pooler_errors = 0
; Improve compatibility with Java/JDBC connections
ignore_startup_parameters = extra_float_digits
; USER AUTHENTICATION (old way commented out with new lines below)
;auth_type = md5
;auth_file = pgbouncer/users.txt
auth_type = hba
auth_hba_file = pg_hba.conf
; TLS SETTINGS (NEW STUFF!)
client_tls_sslmode = verify-full
client_tls_key_file = server.key
client_tls_cert_file = server.crt
client_tls_ca_file = root.crt
server_tls_sslmode = verify-full
server_tls_key_file = /tmp/pgb_user.key
server_tls_cert_file = /tmp/pgb_user.crt
server_tls_ca_file = root.crt

pgbouncer starts, however, when I attempt to connect as any user but let's say I want to be user 'lalligood', I get the following error:

ERROR: no such user: lalligood

pgbouncer.log contains the following line for each attempt:

2016-01-13 16:00:36.971 2144 LOG C-0xcad410:
(nodb)/(nouser)@10.10.5.194:54848 closing because: No such user:
lalligood (age=0)

I can provide more information if necessary. If anyone has any advice/suggestions on what I might be overlooking to get this working, I greatly appreciate the help!


texts:
This question will involve some C# code but I hope that is fine, in the end it is the SQL that is the issue. (To see short question look at end of post)

So I have a site, where you can add a site in to a category as well as a subcategory. Now I want to search the entire database using the same searchbox, redirecting to the same page.If the search finds a category I want the page to display a list of sites in that category. And if I search for a site I want to show a profile of that site.

So what does this mean? Well, it means I need a way to tell if the result (from the search) is coming from my site-table, my category-table or my subcategory-table.

So far, I have the following SQL:

"SELECT url, 'Web' As Type FROM Website WHERE (url=@search)
UNION ALL
SELECT category, 'Cat' As Type FROM Category WHERE (category=@search)";

Now this does search both tables, and return a type of "Web" if it comes from the Website-table or a type of "Cat" if it comes from the category table. However, the result always is under column "url", even if the result comes from the category-table.

C# that works:

using (SqlDataReader reader = cmd.ExecuteReader())
{
   if (reader.Read())
   {
       if (reader.GetString(reader.GetOrdinal("Type")) == "Web")
       {
           Label1.Text = reader.GetString(reader.GetOrdinal("url"));
       }
       else if (reader.GetString(reader.GetOrdinal("Type")) == "Cat")
       {
          Label1.Text = reader.GetString(reader.GetOrdinal("url"));
        }
    }

C# that does not work:

 using (SqlDataReader reader = cmd.ExecuteReader())
{
   if (reader.Read())
   {
       if (reader.GetString(reader.GetOrdinal("Type")) == "Web")
       {
           Label1.Text = reader.GetString(reader.GetOrdinal("url"));
       }
       else if (reader.GetString(reader.GetOrdinal("Type")) == "Cat")
       {
          Label1.Text = reader.GetString(reader.GetOrdinal("category"));
        }
    }

As above, you can see I only get the Type when I add "url" to the reader. Which I understand, in a UNION ALL it selects the first column as the name.

What I need is some way to do this so that I can select ALL (*) from my tables with one query and then be able to know what table it came from. Something like this (pseudocode):

if(search is from Website-table){
DisplayWebsitet();
}
else if(search is from Category-table){
DisplayCategory();
}
else{
DisplaySubCategory();
}

Is there a way to do this using SQL? Is there for example some if/else function I could use? There should be since at for example www.imdb.com you can search for both actor and movie. Or do I need to run 3 different queries? something I really want to avoid doing.

EDIT:
Here is something that kind of gives a result I want but only works inside SQL-studio. I cant use this in C# for some reason. Maybe if I made it a stored procedure and loaded it from C#?

Scenario - If someone searches for "www.facebook.com":

SELECT Category.*, 'Cat' As Type FROM Category WHERE category='www.facebook.com'

SELECT SubCategory.*, 'Sub' As Type FROM SubCategory WHERE subCategory='www.facebook.com'

SELECT Website.*, 'web' As Type FROM Website WHERE url='www.facebook.com'

This return 2 empty results and 1 result (from website table) with all the corresponding information to "www.facebook.com".
If I save this as a stored procedure, can I access the type from C#? Is it a good way to do this?


texts: How to use fsyncLock() safely in backing up a single mongod

texts:
I read from mongodb document:

important:
db.fsyncLock() may block reads, including those necessary to verify authentication
warning:
When calling db.fsyncLock(), ensure that the connection is kept open to allow a subsequent call to db.fsyncUnlock(). Closing the connection may make it difficult to release the lock.

In worst case, I use fsyncLock() to block database and suddenly lost connection to database (because of temporary network failure). I cannot reconnect because fsyncLock() block reader. My database will be blocked forever.

I am considering to shutdown mongodb to backup.

What is the most safest backup solution for single mongod ?


texts: Source dump interrupted by computer restart

texts:
I'm have a wamp server installed on my windows machine (apache 2.4.9, mySQL 5.6, php 5.5, PHPmyAdmin 4.1). I was recently performing a source dump operation to restore a tables in a database. Previously I restored several large tables successfully to my database, but in the middle of the most recent operation, my machine decided to restart. Thanks windows update!

When the machine rebooted, I find that I am unable to connect to the server. The wamp symbol is orange instead of green and I have no access to get into mysql, PHPMyAdmin, or anything else.

I've tried restarting the wamp server but I have no success. I have also attempted to restart the machine several times to no avail. Does anyone know of how I could remedy this (get a connection back to mySQL and/or my server) without destroying the other successfully restored tables?


texts: What should be correct DB schema when input data are kinda versioned?

texts:
I have been bit confused on how should I create a proper DB schema for my particular case - everithing I come up with is very suboptimal (duplicated columns and so). I think I'm missing right DB thinking or I can not see the problem from higher perspective.
Task we dealing with is in bio-informatics realm (but naming below is inaccurate from that point of view) - entities we work with are like this:

There is a protein - e.g. 1tqn
Every protein have 1 or more chains (about 1 - 20 per protein) - e.g. A
Every chain consist of 1 or more domains (about 1 - 5 per chain, below 500k in total) - e.g. 00, so full domain name is 1tqnA00

And also:

There is a family which is a group of domains (about 7k of them) - e.g. 1.10.630.10
And each family have 1 or more subfamiles and it groups one or more domains (about 32k of them) - e.g. 1.10.630.10.25

This is quite simple, but then there is a version: all of above changes in time and we compute some data based on weekly "snapshots" (i.e. these versions), so if 1tqnA00 is in 1.10.630.10.25 at one week, it can be in 1.10.10.10.55 next week and another week it can be deleted at all. Usually about 10% of families changes every week by moving very small part of the domains to another family.
Most usual queries would be:

What is the latest version for family XYZ?
What is the list of domains in family XYZ in version ABC?
What is the latest version for domain XYZ?
What is the family for domain XYZ in version ABC?

Some points I'm not certain about:

Should I split all these entities into separate tables and interconnect with foreign keys when I'll be only tracking names of these entities and not any additional metadata?
Should I be using numerical primary keys, or is string (name of the entity) OK given sizes above?

How should the schema reflect this "versioning" of input data? As of now, we are running on SQLite, but plan to migrate to PostgreSQL soon. Thank you very much for ideas.


texts: TEMPDB full causing login problems with server login trigger

texts:
We have a server trigger used to prevent some logins from accessing SQL Server via SSMS in our DEV environment.

CREATE TRIGGER [Deny_SQLLogin_SSMS_Trigger]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @login    SYSNAME = ORIGINAL_LOGIN()
    DECLARE @app      SYSNAME = APP_NAME()
    DECLARE @hostname SYSNAME = HOST_NAME()

    -- is this login in our prohibited list.
    IF @login IN ('some_user', 'some_user2', 'some_user3') AND @app LIKE N'%Management Studio%'
    BEGIN
        -- yes so send back an error
        --ROLLBACK;

        -- .. and record the attempt.
        INSERT msdb.dbo.AuditLog([Login],App,HostName,EventTime)
        VALUES (@login, @app, @hostname, SYSUTCDATETIME());

        THROW 51000, 'Connection not allowed.', 1;
    END
END

GO

ENABLE TRIGGER [Deny_SQLLogin_SSMS_Trigger] ON ALL SERVER
GO

This works just great when tempdb isn't full but when it is, all logins get rejected.

Message
Logon failed for login 'some_user' due to trigger execution.

Message
The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.

No one can login from anywhere and we are forced to restart the instance. This is not going to be possible for our production instances when we start using the login trigger there as well.

So how can we design the trigger so that logins don't get blocked in case tempdb fills up unexpectedly?


texts:
I am using Rational Application Developer to run querys on a database. We are unable to locate anyone who has a list of the tables on the schema and so far the queries we have found to get a list of tables are unsuccessful.

USE <DB>
GO
SELECT *
FROM sys.Tables
GO

Use breaks but I replaced it with CALL.

GO breaks as well and I cannot locate a function that does the same thing.


texts: Using GROUP BY and ORDER BY in my query works in MySQL but not in SQL Server

texts:
I have a query which runs correctly on MySQL server. In SQL Server it gives error:

  "Column 'Tovar.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" .

I don't know how to convert it in to SQL Server. Looking forward
for the help from guru's.

SELECT B.BBarcode,
       Name, C.Country,
       Manufacturer,W.id,
       Packtype,
       B.TovarId,
       SUM(WCount) as count,
       Price,
       Series,
       ExpDate
FROM Warehouse W, Tovar T, Barcodes B, Countries C
WHERE C.CId=T.Country
   AND B.WarehouseId=W.id
   AND WCount>0
   AND T.id=W.TovarId +'=@param'
GROUP BY W.ID,B.BBarcode
ORDER BY Name


texts: Differential Backup of A Table In Sql Server

texts: I will transfer the datas of a two different table to a database in reporting server Daily but i don't want to do it manually and also don't want to transfer all the datas to that databases everyday. Instead of doing it, i wonder that if i can take full backup and then day by day differential bakcup or not? Is there a way doing this logic in sql server?


texts:
Currently I've been assigned the task to research ways to speed up an existing project. It's going to be rebuild so we can start from scratch, the current implementation has been build by another company and is lacking in performance.

The data isn't all that big except for one table, which is going to grow quite rapidly.
I've been looking into sharding and partitioning tables and I can find enough info regading those topics.

However:

Another possible solution would be lowering resolution of data gather for old data.

So for example, the table would contain answer of employees, these employees anser 1-30 questions a day, answers have a numerical part over which averages are made.

I could store only the averages for data of a couple of years old, ie average per month, for data of a couple of years old.

However I can't find any info because I don't know what to google for, does anyone recognize this pattern, or know if this solution is already flawed for whatever reason.


texts: NLS_LANG AL32UTF8/UTF8 for Oracle database

texts:
I recently installed a new Oracle database on Ubuntu using this tutorial.

I got an error

oracle@ubuntu:/home/bruno$ echo $NLS_LANG
/u01/app/oracle/product/11.2.0/xe/bin/nls_lang.sh
oracle@ubuntu:/home/bruno$ /u01/app/oracle/product/11.2.0/xe/bin/nls_lang.sh
AMERICAN_AMERICA.AL32UTF8
oracle@ubuntu:/home/bruno$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 17 11:00:45 2016
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
ERROR:
ORA-12705: Cannot access NLS data files or invalid environment specified
Enter user-name:

After searching, I found a possible solution:

oracle@ubuntu:/home/bruno$ export NLS_LANG=AMERICAN_AMERICA.UTF8
oracle@ubuntu:/home/bruno$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 17 11:03:15 2016
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>

What is the most elegant way to correct this error?

Answer

export NLS_LANG = $($ORACLE_HOME/bin/nls_lang.sh)


texts: How to run EM of Oracle 11g, if i haven't emctl, netmgr, dbca, OracleDBConsole?

texts: I don't know what is wrong with my Oracle 11g, but it is missing a lot of things. After reinstallation it stays the same. My overall path is like C:\oraclexe\app\oracle and after it there are folders like admin, diag, fast_recovery_area, oradata, product. Also, I don't have dbca, netmgr, emctl and service called OracleDBConsole<SID>. How to fix it?


texts: How can I start Oracle as a lightweight process?

texts:
I have OracleDB running in a CI pipeline in a docker image.  With each test it is started and then shutdown.  It's purely there because Django tests expect a real database.
However it takes a couple of minutes each time to start and shutdown.  Is there a way to make it start really quickly and use less memory?


texts:
I'm working with subscription table currently and I'm creating a cron job that will take records based on some condition from the subscription table.

I want to select all subscription records that have either been renewed_at or created_at (these are column names) on certain date, seems easy so I do it like this, if the date is 2013-10-16 :

select * from subscription where
(subscription.renewed_at between '2013-10-16 00:00:00.000000'
and '2013-10-16 23:59:59.999999') OR
(subscription.created_at between 2013-10-16 00:00:00.000000'
and '2013-10-16 23:59:59.999999')

So here is what I want to be sure, and I'm not sure how to do it.

I want to prioritize renewed_at column over created_at column, in a sense that if somebody renewed their subscription at a certain date that I should select the record based on that date rather than on created_at date. In case however the renewed_at is not there I should select the record based on the created_at date.

And I should not select the record based on created_at if the renewed_at column is in there, and they're always different in sense than they're not ever the same date even if they're both present. created_at is always present.

Is is possible to do it? Or am I thinking this the wrong way?


texts: How can a Cassandra node see another node as down?

texts:
I'm running Cassandra on three nodes. Here's their nodetool status output:

ubuntu@ip-10-0-8-8:~$ nodetool status
Datacenter: us-east
===================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address    Load       Tokens  Owns    Host ID                               Rack
UN  10.0.9.8   2.07 MB    256     ?       c8d574b9-540c-410f-9326-789eb75d3d14  1c
UN  10.0.8.8   2.06 MB    256     ?       d9454056-a358-4428-ab5f-c03e8042167e  1d
UN  10.0.10.8  2.01 MB    256     ?       3617643d-b0a8-4b72-a9d4-feded4445292  1a

ubuntu@ip-10-0-9-8:~$ nodetool status
Datacenter: us-east
===================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address    Load       Tokens  Owns    Host ID                               Rack
UN  10.0.9.8   2.07 MB    256     ?       c8d574b9-540c-410f-9326-789eb75d3d14  1c
UN  10.0.8.8   2.06 MB    256     ?       d9454056-a358-4428-ab5f-c03e8042167e  1d
DN  10.0.10.8  2.09 MB    256     ?       3617643d-b0a8-4b72-a9d4-feded4445292  1a

ubuntu@ip-10-0-10-8:~$ nodetool status
Datacenter: us-east
===================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address    Load       Tokens  Owns    Host ID                               Rack
UN  10.0.9.8   2.07 MB    256     ?       c8d574b9-540c-410f-9326-789eb75d3d14  1c
UN  10.0.8.8   2.06 MB    256     ?       d9454056-a358-4428-ab5f-c03e8042167e  1d
UN  10.0.10.8  2.01 MB    256     ?       3617643d-b0a8-4b72-a9d4-feded4445292  1a

Everything looks fine except one thing (last line in second block):

DN  10.0.10.8  2.09 MB    256     ?       3617643d-b0a8-4b72-a9d4-feded4445292  1a

The D in the start of the line indicates the node being down. How can it be that 10.0.9.8 is seeing the node as down while the other nodes are seeing it just fine? Does this lead to inconsistencies?

Using Cassandra version 2.1.1 by the way.


texts: Triggers not being run on a partitioned table

texts:
I'm using PostgreSQL. I have a table that I have partitioned into three sub-tables - and as far as I can tell, this all works correctly. Rows are correctly inserted into the appropriate partition and the master table remains empty.

My problem is that one of the triggers on the master table is no longer run on insert or update. Other triggers are run correctly and this trigger worked correctly prior to partitioning.

I suspected this might be similar to constraints and indexes and the trigger needed to be applied to child tables, however this doesn't work either.

Other triggers work, and if they are run alphabetically as the documentation suggests, all should be run before the partition-finding trigger.

The trigger that is causing me grief:

CREATE FUNCTION set_expiry_timestamp() RETURNS trigger AS $BODY$
BEGIN
    -- Don't re-generate if the timeleft value has not changed
    IF (TG_OP = 'UPDATE') THEN
        IF OLD.expires IS NOT NULL AND OLD.timeleft = NEW.timeleft THEN
            RETURN NEW;
        END IF;
    END IF;

    IF (NEW.timeleft = 'SHORT') THEN
        NEW.expires := NEW.updated + interval '30 minutes';
    ELSIF (NEW.timeleft = 'MEDIUM') THEN
        NEW.expires := NEW.updated + interval '2 hours';
    ELSIF (NEW.timeleft = 'LONG') THEN
        NEW.expires := NEW.updated + interval '12 hours';
    ELSIF (NEW.timeleft = 'VERY_LONG') THEN
        NEW.expires := NEW.updated + interval '48 hours';
    ELSE
        RAISE EXCEPTION 'timeleft was unrecognised. Expected SHORT, MEDIUM, LONG or VERY_LONG.';
    END IF;
    RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER set_expiry AFTER INSERT OR UPDATE ON auction FOR EACH ROW EXECUTE PROCEDURE set_expiry_timestamp()

Query expected to run the trigger:

INSERT INTO auction (created, timeleft) VALUES (NOW(), 'SHORT');

updated is set in a BEFORE INSERT/UPDATE trigger, however it still doesn't fire when updated is supplied.

Any suggestions?


texts: Database design for products with bundles of products

texts:
I am building a database system for my retail business. I have set some tables which are:

Product
Purchase
Sales
Balance

All are connected one another and are able to show my inventory level.

The problem I am having is I also sell bundles of products - that have different prices than their individual prices.
Example: I sell an orange for $1, an apple for $1.2; I sell fruit package 1 (2 oranges and 2 apples) for $3.8, package 2 (4 oranges and 4 apples) for $7.

Is there a right way how to create relationship for these product bundles?

PS: I am using FileMaker Pro creating this.


texts: Getting File not found error doing point in time database restore in SQL Server 2008

texts:
So I'm testing out some of our DR stuff and tried to restore a point in time backup from one of our main databases to a new database on the same server.

I am admin on the box and I'm RDP'd into the box and am using SSMS to do the restore.

When I choose my database the "select the backup sets to restore" window populates with a ton of our 1hr incremental backups all the way back to about 30 days ago.

When I select a random time out of the set I get the error

  Cannot Open Backup Device {guid}. Operating System error 2, the system cannot find the file specified.

I'm restoring to a new .mdf and .ldf on a different local drive but I have access to everything.

Not sure what's going on or where to diagnose which file it can't find.

Thanks


texts: MySQL query with an OUTFILE to csv not working when called from a batch file

texts:
When I execute the following query, it works:

SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS' INTO OUTFILE 'D:/stats.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

But when I call this query from the Windows command prompt or from a batch file using the following command:

"%MBIN%\mysql.exe" -u"%MUSER%" -p"%MPASS%" -h "%MHOST%" -e "SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS' INTO OUTFILE 'D:/Stats.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';"

it does not run. Here's what happens:

C:\Documents and Settings\188931>"%MBIN%\mysql.exe" -u"%MUSER%" -p"%MPASS%" -h "
%MHOST%" -e "SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS' INTO OUTFILE 'foldrname/
orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';"
C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe  Ver 14.14 Distrib 5.5.16,
 for Win32 (x86)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Usage: C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe [OPTIONS] [database
]
  -?, --help          Display this help and exit.
  -I, --help          Synonym for -?
  --auto-rehash       Enable automatic rehashing. One doesn't need to use
                      'rehash' to get table and field completion, but startup
                      and reconnecting may take a longer time. Disable with
                      --disable-auto-rehash.
                      (Defaults to on; use --skip-auto-rehash to disable.)
  -A, --no-auto-rehash
                      No automatic rehashing. One has to use 'rehash' to get
                      table and field completion. This gives a quicker start of
                      mysql and disables rehashing on reconnect.
  --auto-vertical-output
                      Automatically switch to vertical output mode if the
                      result is wider than the terminal width.
  -B, --batch         Don't use history file. Disable interactive behavior.
                      (Enables --silent.)

Another thing I noted is, when I run the following:

"%MBIN%\mysql.exe" -u"%MUSER%" -p"%MPASS%" -h "%MHOST%" -e "SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS';"

from the command prompt or a batch file, it does execute. (Note - I removed the csv export).

So it seems a combination of executing a query from batch file (or Windows command prompt) which is having a OUTFILE to csv is not working.

Could any body kindly help on this?


texts: Is it possible to switch queries based on an input in mySQL?

texts:
Is there a way to switch queries based on values? Let's say I have the below query:

select pdate as 'Payment Date', ROUND(@sum_amount := @sum_amount + `amount`) AS 'Total Amount' from
(
select @sum_amount := 0, date(issue_date) pdate, sum(amount) amount from payments group by YEAR(date(issue_date))
) inner_tbl

I want to change query interval (yearly, monthly, daily) based on user input. Something like below:

SELECT CASE input WHEN 1 THEN **RUN YEARLY QUERY**
     WHEN 2 THEN **RUN MONTHLY QUERY** ELSE **RUN DAILY QUERY** END;


texts: How to run Sql Server stored procedure in Async mode?

texts:
Is there a way to run SQL Server 2019 stored procedures with parameters asynchronously? Calls to this SP will be made several times a day, i.e. this is not one time requirement.
Service Broker can be one option, but this is 15+ years old technology and Microsoft did not try to enhance its functionality.
Agent job can be an option, but I want to submit a job on demand "as and when required". Plus, the  stored proc has parameters as well.
I came from Oracle background and there are multiple ways to submit a background job to the Database engine and it will run the job in Async manner.


texts: Error trying to SET a field when two fields in different tables match

texts:
I am trying to set catid to 584 whenever the rows in the tables ykz2d_crmery_deals and ykz2d_maps_integration have the same company id using the following query:
UPDATE ykz2d_maps_integration
SET catid = '584'
WHERE ykz2d_crmery_deals.company_id = ykz2d_maps_integration.company_id

I keep getting the following error:

Failed to execute SQL : SQL UPDATE ykz2d_maps_integration SET catid = '584' WHERE ykz2d_crmery_deals.company_id = ykz2d_maps_integration.company_id failed : Unknown column 'ykz2d_crmery_deals.company_id' in 'where clause'
The column company_id does exist within ykz2d_crmery_deals.

What I can do to fix this?


texts:
I am developing an app that send messages (email and sms) to users. The db must also maintains the list of messages send to users. And also the status of messages send to user like send or failed.

I have build this scheme that 1 message have many users and 1 reciept is generated to  show the status against each message.

Here the confusion is that how would i maintain the status of a message for every user that message is particular user is send or failed.


texts: How does Postgres 9.2's streaming replication handle schema changes, and initial table setup?

texts: I want to set up a backup database on the physical server which sits right under my primary physical database server. I'm using Postgres 9.2, and I want to use synchronous, streaming replication (for atomicity), but I'm completely unaware of how things like, A) initial table setup is transferred from server 1 to server 2 (e.g., I run syncdb from my Django app, which creates a series of tables on server 1), and B) how continued schema changes are carried from server 1 to server 2 (e.g., I run a Django South migration, which sends ALTER TABLE queries, as well as adding/removing indexes, etc.). Are these things handled transparently with streaming replication, or is there something I have to do to affect this change across both servers?


texts: Vertica INSERT speed highly depends on server load

texts:
I faced a problem with very slow INSERTs in Vertica DB.
I made such test: create empty table with no projections and start inserting in it in batch, using

> INSERT INTO table
SELECT 111,222,333 UNION ALL
SELECT 444,555,666 UNION ALL
SELECT 777,888,999;

1000 rows in one query.

On idle server one query runs about 2sec and it's alright, but then I start generating some load making SELECTs (not very big load though, about 10 LA on 32 core server) and INSERTs start to performs 20x slower ( 40-60 sec each 1000 rows INSERT). It's too slow for my conditions. What can I do besides copying files to Vertica server and make COPY which seems to perform good, but too complicated?


texts:
here is query:

SELECT nw.news_id, nw.title, nw.description, nw.url, nw.date, nw.image,
       nw.status, src.title as src_title, src.keyword as src_keyword,
       src.url as src_url, src.icon as src_icon, cat.title as cat_title
FROM news as nw
INNER JOIN sources as src ON nw.source=src.source_id
INNER JOIN categories as cat ON nw.category=cat.category_id
WHERE nw.status=1
ORDER BY nw.news_id DESC LIMIT 0,140;

news table size is around 30MB. server RAM is 1GB.

my.cnf:

[mysqld]
max_connections = 120
key_buffer = 32M
myisam_sort_buffer_size = 32M
join_buffer_size = 8M
read_buffer_size = 8M
sort_buffer_size = 32M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 8K
query_cache_size = 32K
query_cache_type = 1
tmp_table_size = 16M

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
local-infile=0

it was myisam I converted to innodb for better performance but nothing changed so I get back to myisam.

query works correctly on localhost but when ran in server, get high load and eat all RAM until crash mysql. just one of above query get me highload! something like that mysql could not finish it. table has around 50000 records.

I tried to fix issue by changing my.cnf parammeters with no luck.


texts:
I have two tables with almost exact structure, but one table always seems to not be updated appropriately.  To show the issue, I have created two test tables that illustrate.

Declare @Test1 Table (card4 int, d1 date, amt float)
Declare @Test2 Table (card4 int, d1 date, amt float)

Insert Into @Test1 VALUES
('4356', '01/01/2015', '12.24'),
('4356', '01/01/2015', '44.12'),
('1111', '01/01/2015', '55.10'),
('2222', '01/01/2015', '23.12')
Insert Into @Test2 VALUES
('4356', '01/01/2015', '12.24'),
('4356', '01/02/2015', '11.11'),
 ('4356', '01/01/2015', '44.12')

Select * from @Test1
Select * from @Test2

From the sample data @Test1 is missing the amt 11.11 and I am not sure how to write a query that will pull all values from @Test1 and any values that are missing from @Test2

This is the data set that I want returned

  card4    d1          amt
      1111    1/1/2015    55.1
      2222    1/1/2015    23.12
      4356    1/1/2015    12.24
      4356    1/1/2015    44.12
      4356    1/2/2015    11.11

What would syntax be to achieve this?


texts: PostgreSQL query with max and min date plus associated id per row

texts:
I have the following table:

CREATE TABLE trans (
    id SERIAL PRIMARY KEY,
    trans_date date,
    trans_time time
);

I want to have the following view

CREATE OR REPLACE VIEW daily_trans AS
SELECT trans_date,
    max(trans_time) as first,
    min(trans_time) as last,
    calculate_status(min(trans_time), max(trans_time)) as status
GROUP BY trans_date

with columns that specify the ids of the max and min trans_time.

How do I do that?


texts: PostgreSQL / pgAdminIII: is it possible to send command line commands via the UI

texts:
Using PostgreSQL / pgAdminIII, is it possible to send command line commands to PostgreSQL via the UI? Put another way: how can I access the command line via the pgAdminIII?

As I am coming up to speed on pgAdminIII, occasionally it seems like it would be useful. Ex: I search for a solution to an issue, but all I find is a solution that involves the command line, not the pgAdminIII UI.

Edit:

Maybe I am not understanding something more fundamental here, or maybe I am not using the proper terminology, but for example in this link Jay Taylor's answer seems to use the command line commands I am referring to:
https://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql

In this link Bruno's answer uses them:
https://stackoverflow.com/questions/20775462/cant-grant-user-privileges-to-postgresql-database-for-a-rails-app

In this link there are several references to them:
https://jamie.curle.io/creating-a-read-only-user-in-postgres

Explicitly, from the last link above, how would I send something like this to PostgreSQL/the server from pgAdminIII (not that I actually want to):

 pg_dump -O -Fc  --format=t productiondb -U backup_user -h localhost


texts: Find insert/update/delete queries that reference a partitioned view

texts:
I have just given a SQL Server with performance problems. The database is very large and use distributed views with a very large primary clustered key. (24 bytes).

I would like to change this primary key to another one (identity (1,1)) but in this case view won't be updatable. So I would like to find any update/insert/delete commands using this view. We have many procedures and linked servers too, which is why I can't review all procedures.

Any ideas? Profiling, but how? I know about instead of triggers, but I don't want to use them.


texts: Why does the CBO not chose a lower cost skip scan without stats?

texts:
In the script below, the CBO chooses an INDEX FAST FULL SCAN over an INDEX SKIP SCAN (this is illustrated by the queries with the UNION ALL but you can demonstrate it is true with just explain plan for select * from foo where baz=1). This choice is made despite the skip scan being the lower cost choice.

Once the table is analyzed, the skip scan is preferred (despite the relative cost actually rising). It seems the CBO does not consider a skip scan at all with dynamic sampling, is this true? If not, why is the skip scan not chosen before the stats are gathered?

schema:

create table foo( bar integer,
                  baz integer,
                  qux char(99),
                  constraint pk_foo primary key (bar, baz) )
             organization index compress;
--table FOO created.
insert into foo(bar,baz) select mod(level,1000), level from dual connect by level<1000000;
--999,999 rows inserted.
commit;
--committed.

pre-analyze:

explain plan for
select * from foo where baz=1
union all
select /*+ full(foo) */ * from foo where baz=1
union all
select /*+ index_ss(foo) */ * from foo where baz=1;
--plan FOR succeeded.

select * from table(dbms_xplan.display);
/*
--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |   132 | 16764 |  8480  (53)| 00:01:34 |
|   1 |  UNION-ALL            |        |       |       |            |          |
|*  2 |   INDEX FAST FULL SCAN| PK_FOO |    44 |  5588 |  4034   (1)| 00:00:45 | <-- why full?
|*  3 |   INDEX FAST FULL SCAN| PK_FOO |    44 |  5588 |  4034   (1)| 00:00:45 |
|*  4 |   INDEX SKIP SCAN     | PK_FOO |    44 |  5588 |   411   (0)| 00:00:05 |
--------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
*/

analyze:

analyze table foo compute statistics;
--table FOO analyzed.

post-analyze:

explain plan for
select * from foo where baz=1
union all
select /*+ full(foo) */ * from foo where baz=1
union all
select /*+ index_ss(foo) */ * from foo where baz=1;
--plan FOR succeeded.

select * from table(dbms_xplan.display);
/*
--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     3 |    30 |  6038  (84)| 00:01:07 |
|   1 |  UNION-ALL            |        |       |       |            |          |
|*  2 |   INDEX SKIP SCAN     | PK_FOO |     1 |    10 |  1003   (1)| 00:00:12 | <-- makes sense
|*  3 |   INDEX FAST FULL SCAN| PK_FOO |     1 |    10 |  4033   (1)| 00:00:45 |
|*  4 |   INDEX SKIP SCAN     | PK_FOO |     1 |    10 |  1003   (1)| 00:00:12 |
--------------------------------------------------------------------------------
*/

something similar on SQLFiddle here and here


texts: innodb_file_per_table option enabled but ibdata1 file still grows up

texts:
I installed mariadb 10.2.10 with innodb_file_per_table option enabled.

Then I create table with Innodb engine and inserted 1 M row into.

Now I look and local (..data/mydbname/table_name.ibd)   file is grown, but  grown system ibdata1 file also. Why? I expected that because of innodb_file_per_table is enabled, only local .ibd should grown, but not ibdata1.

Why size of ibdata1 also grows up? What I misunderstood ?


texts:
I am using RDBMS MySQL 5.7.
I have two tables with the following structures:

Table A - primary table - products:

+----+-----------+-------------+------------+--------+
| id | title     | description | createdAt  | status |
+----+-----------+-------------+------------+--------+
|  1 | Product A | Desc A      | 2019-07-19 |      1 |
|  2 | Product B | Desc B      | 2019-07-04 |      1 |
+----+-----------+-------------+------------+--------+

Table B - product_categories -> one-to-many model

+----+-----------+------------+
| id | productId | categoryId |
+----+-----------+------------+
|  1 |         1 |          3 |
|  2 |         1 |          5 |
|  3 |         2 |          5 |
|  4 |         2 |          7 |
|  5 |         2 |          9 |
+----+-----------+------------+

productId and categoryId are foreign keys.

I want to select rows from products table which have categories 3 and 5 both. The query should return the only row with id 1.

I have such a query:

SELECT p.id
FROM products p
inner join product_categories pc on p.id = pc.productId
WHERE (pc.productId, pc.categoryId) IN ((p.id, 3), (p.id, 5))
GROUP BY productId
HAVING COUNT(productId) = 2

How you would simplify it? Without using row constructors.


texts: MySQL InnoDB locking on combined UPDATE-JOIN statements

texts:
on querying statements which contain an UPDATE combined with JOIN, we are getting the following errors:

Statements writing to a table with an auto-increment column after selecting
from another table are unsafe because the order in which rows are
retrieved determines what (if any) rows will be written.
This order cannot be predicted and may differ on master and the slave.

These statements finally turn into deadlocks and kill other statements trying to access the same table (update: it doesnt even seem the queries care about the timeout, because some deadlocks occure after 5-20 seconds).

innodb lock wait timeout = 150
binlog_format = STATEMENT

I can't believe that MySQL offers the possibility to UPDATE a table depending on another table (UPDATE x JOIN y.. SET x.val = 1 WHERE y.val = 1), but can't handle it on InnoDB-Cluster.

Any Ideas?


texts:
On our network we have a tunnel to a NAT gateway host.  From the gateway host we are able to access a private network.  On this network we will be running jobs that log to a database.  The database will be hosted on the same network.
A couple of notes on the remote database:

The database will only receive lightweight insert/update queries when a job starts/finishes.  At peak usage this could be 10 queries/minute, but it would probably average closer to 0-1 queries/minute.
The database probably won't be run from the gateway host, but it could share a file system (so log files may be accessible from the gateway host).

We would like to continuously sync data from the remote database to a local MySQL database (remote database is not configured yet but it will probably be MySQL).  The sync should be close to real time, but a 5-10 minute delay is acceptable.
How can we achieve this?  I don't know anything about DBA/networking but it seems like one way to do this would be to set up a cron job to pull log files from the gateway host, then perform an update to the local database based on the logs.  Am I on the right track?  Is there anything else that I need to consider/look in to?


texts: How do I resolve this error, "ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'"?

texts:
When I run the following command I get an error, however one of my scripts requires it.

SET time_zone = 'UTC';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'


texts:
I have a table has 3 non-clustered indexes and a clustered index. The 3 NCI are getting fragmented very fast, I rebuild the index last night, and this morning I checked the avg_fragmentation_in_percent was 97%

dbo.TestResultMCATRaw.IX_TestResultMCATRaw:

 [2 KEYS] AAMCId {int 4}, TestDate {datetime 8}
 Reads: 1,696 (1,696 seek) Writes:8,955
 0 singleton lookups; 1,700 scans/seeks; 0 deletes; 0 updates;
 30,306 rows; 1.3MB

dbo.TestResultMCATRaw.IX_TestResultMCATRaw_1 (11):

[3 KEYS] AAMCId {int 4}, TestType {varchar 10}, TestDate {datetime 8}
Reads: 2,943 (2,943 seek) Writes:8,955
0 singleton lookups; 2,945 scans/seeks; 0 deletes; 0 updates;
30,306 rows; 1.5MB

dbo.TestResultMCATRaw.PK_TestResult_MCAT_Raw (1):

[CX] [PK] [1 KEY] Id {int 4}
Reads: 22,757 (13,914 seek 20 scan 8,823 lookup) Writes:15,726
16,430 singleton lookups; 8 scans/seeks; 0 deletes; 8,212 updates;
30,306 rows; 8.1MB

dbo.TestResultMCATRaw.IX_TestResultMCATRaw_2 (60):

[3 KEYS] LastName {varchar 25}, FirstName {varchar 25}, DateofBirth {datetime 8}
Reads: 0 Writes:8,955
0 singleton lookups; 2 scans/seeks; 0 deletes; 0 updates;
30,306 rows; 2.1MB

My questions are

Why are the indexes getting fragmented so fast?
The fillfactor is 0, should I change it to 80?
Why the fragmentation for the PK index still 2% and the fragmentation for the other 3 indexes went from 0 to 97% however there is no update?

ADD based on JonathanFite comment:

in case the page count is more than 1000 and the index get fragmented quickly. How can I fix this ?


texts: Append duplicate records with count (mysql)

texts:
We need to update a set of titles that have some duplicate entries. Because of the size of the database I would prefer to use direct mysql method, but I am open to any solutions out there.

If we have records (url_title):

a-stoney
a-stoney
ab-dille
ab-dille
ab-dille
ac-gifta
ae-jones
ae-jones

I want to be able to run against the table and end up with ONLY the duplicate url_titles updated as follows:

a-stoney
a-stoney-1
ab-dille
ab-dille-1
ab-dille-2
ac-gifta
ae-jones
ae-jones-1

Or some similar output, where non-duplicate url-titles are not edited, and any duplicates are applied a similar convention

Table syntax:

CREATE TABLE `exp_channel_titles` (
  `entry_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` int(4) unsigned NOT NULL DEFAULT '1',
  `channel_id` int(4) unsigned NOT NULL,
  `author_id` int(10) unsigned NOT NULL DEFAULT '0',
  `forum_topic_id` int(10) unsigned DEFAULT NULL,
  `ip_address` varchar(45) NOT NULL DEFAULT '0',
  `title` varchar(100) DEFAULT NULL,
  `url_title` varchar(75) NOT NULL,
  `status` varchar(50) NOT NULL,
  `versioning_enabled` char(1) NOT NULL DEFAULT 'n',
  `view_count_one` int(10) unsigned NOT NULL DEFAULT '0',
  `view_count_two` int(10) unsigned NOT NULL DEFAULT '0',
  `view_count_three` int(10) unsigned NOT NULL DEFAULT '0',
  `view_count_four` int(10) unsigned NOT NULL DEFAULT '0',
  `allow_comments` varchar(1) NOT NULL DEFAULT 'y',
  `sticky` varchar(1) NOT NULL DEFAULT 'n',
  `entry_date` int(10) NOT NULL,
  `year` char(4) NOT NULL,
  `month` char(2) NOT NULL,
  `day` char(3) NOT NULL,
  `expiration_date` int(10) NOT NULL DEFAULT '0',
  `comment_expiration_date` int(10) NOT NULL DEFAULT '0',
  `edit_date` bigint(14) DEFAULT NULL,
  `recent_comment_date` int(10) DEFAULT NULL,
  `comment_total` int(4) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`entry_id`),
  KEY `channel_id` (`channel_id`),
  KEY `author_id` (`author_id`),
  KEY `url_title` (`url_title`),
  KEY `status` (`status`),
  KEY `entry_date` (`entry_date`),
  KEY `expiration_date` (`expiration_date`),
  KEY `site_id` (`site_id`)
) ENGINE=MyISAM AUTO_INCREMENT=181332 DEFAULT CHARSET=utf8;
    enter code here


texts:
I want to design an "Address" model for all types of entities like users, businesses, etc.
I have two types of main models: one is User and the other is Business. Each one has different address types like below.
User
1.Contact Address
2.Billing Address

Business
1.Contact Address
2.something

So I created an address model with an addresstype column like this
Address
id
addresstype
user
addressline1
addressline2

Relationships:

User – One to many –> Business
User – One to many –> Address (User Column)

Now using the above relations, addresstype and user columns will be in a relation, but Business address is not relatted with address.
How can I design this one in an efficient way?


texts:
I have two complex queries and I have to filter out the results from the second one, it looks something like this:

SELECT STH INTO #temp_table_1

FROM TABLE1,
TABLE2,
TABLE3

WHERE
TABLE1.ID = TABLE2.ID,
TABLE2.ID = TABLE3.ID,
TABLE3.STATUS = 'A';

SELECT STH INTO #temp_table_2

FROM TABLE1,
TABLE2,
TABLE3

WHERE
TABLE1.ID = TABLE2.ID,
TABLE2.ID = TABLE3.ID,
TABLE3.STATUS = 'B';

SELECT STH FROM #temp_table_1 t1
WHERE NOT EXIST (SELECT 1 FROM #temp_table_2 t2 WHERE t2.COL = t1.COL);

The problem is that I have to get rid of those temporary tables and write everyting in one query. But if I substitute subqueries for #temp_table_1 and #temp_table_2, it just takes too much time.

Is it possible to achieve almost the same runtime using only single query? Someone told me that I could use UNION, but didn't elaborate on that further. Is it true? This is Sybase IQ.


texts:
Employee_M

City        Emp_Num_ID
Cleveland   2164445437/FTTD/JAMES, TYLER/139561151
Tokyo       1261120379/FTTD/BOYD, ADAMS/14468140
Marakech    4049838896/FTTD/SMITH, TULY E/13956144
Houston     7980151429/FTTD/NEARY, HARTMAN/14215411

I'm trying to extract all digits after the third / with substring

select substr(Emp_Num_ID,/,10) as Emp_ID
from Employee_M

sometimes we can have between 4 to 10 charterers after the third / so I chose 10 for the length.

any idea how to fix that.


texts:
I'm having difficulty getting data from a query using the LAG method. The issue is that I only need one record from the entire set pulled down, but when I filter out that record in the WHERE clause it prevents the LAG method from using the entire dataset for its comparisons, and just returns NULL. For example, I have this set of records in my DB:

All I want is the previous status to "Finished". But when I run this query, it only returns NULL because there is only one record the LAG method is working with:

SELECT
    s.StatusName, LAG(s.StatusName) OVER (ORDER BY cs.CustomerId, cs.CustomerStatusId) as PreviousStatus
FROM
    #CustomerStatus as cs
    left join #Status as s on s.StatusId = cs.StatusId
WHERE
    cs.StatusId = 3

ORDER BY
    cs.CustomerStatusId

If I remove the WHERE clause, it pulls the dataset below correctly, but I only want the third record:

Is there a way for me to grab only that one record from the results?

If you would like to work with my sample data, here is the Rexter link:

Rexter - LAG


texts:
Is it possible to script the creation of a database diagram using T-SQL?

If not, what is the Database Diagram Designer doing when you use it to save / edit a diagram (is it using PowerShell or some other language to save and load these type of objects)?

I ask because I'm looking to automate the creation of database diagrams.
I have a tool one of my developers wrote (in C#) which automatically creates tables and their foreign key relationships based on the responses of some web service calls it makes to our vendors' data.


texts:
I have used attached query to check unused indexes in my EDB9.5 DB. It's showing attached indexes are the unused indexes. Is it safe to drop below unused indexes?
SELECT
    idstat.relname AS TABLE_NAME,
    indexrelname AS index_name,
    idstat.idx_scan AS index_scans_count,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    tabstat.idx_scan AS table_reads_index_count,
    tabstat.seq_scan AS table_reads_seq_count,
    tabstat.seq_scan + tabstat.idx_scan AS table_reads_count,
    n_tup_upd + n_tup_ins + n_tup_del AS table_writes_count,
    pg_size_pretty(pg_relation_size(idstat.relid)) AS table_size
FROM
    pg_stat_user_indexes AS idstat
JOIN
    pg_indexes
    ON
    indexrelname = indexname
    AND
    idstat.schemaname = pg_indexes.schemaname
JOIN
    pg_stat_user_tables AS tabstat
    ON
    idstat.relid = tabstat.relid
WHERE
    indexdef !~* 'unique'
    and idstat.relname='cwt_act'
ORDER BY
    idstat.idx_scan DESC,
    pg_relation_size(indexrelid) DESC;

Please suggest!


texts: large number of tables and postgres query performace

texts:
I have a scenario where there are 40000 tables in the database. but there are no huge unions. Very simple single table select queries are performing much slower than usual (table with just 600,000 rows and 10-15 columns).

Does the (large) number of tables in a database can affect query performance? If Yes, How?


texts:
we have an external production database (maintained by another company) and we are trying to build an internal database that replicates the production database for reporting purposes (both sql-server).

Here is the scenario: they have sent us a full backup (BAK) which I already have it restored properly. And they will send us the transaction logs (TRL) on a daily basis.

I know I can restore it with BAK plus all the TRL sequentially and overwrite the previous restore. But is there a way that I can restore with the most recent log I received 'incrementally'?

Thanks in advance. And correct me if I used inappropriate terminologies.

:)


texts: postgresql insert from select query, plus static values

texts:
Referring to How to insert values into a table from a select query in PostgreSQL?,

I would like to INSERT into a table rows from another, specified by a SELECT DISTINCT, plus some static values, something like:

INSERT INTO new_tbl (column1, column2, column3)
SELECT DISTINCT id FROM -- long where clause --,
  'a string', 0;

So that every row in the new table will get the same values for column2 and column3 Is this possible?


texts:
Suppose We have a replica set with 1 primary and 2 secondaries.

During write operation on Primary, will write commit happen at once upon finishing write operation on primary and before spreading changes to secondaries? Or it will happen once all changes will be apllied on secondaries?


texts: PostgreSQL pg_bulkload query eats all free disk space

texts:
I have a simple task: bulk insert csv file to the table.
The .ctl file is

TYPE = CSV                            # Input file type
ESCAPE = \                            # Escape character for Quoting
DELIMITER = ","                       # Delimiter
PARSE_BADFILE = "/home/USERNAME/bulkload.bad"
LOGFILE = "/home/USERNAME/bulkload.log"
WRITER = BUFFERED
CHECK_CONSTRAINTS = YES
PARSE_ERRORS = 0

I'm importing csv file of approximately 6MB size. The import proceeds for 2-3 minutes, eats all free space (14 GB on main partition) and ends up in this error:

2012-10-28 12:13:22 UTC ERROR:  could not extend file "base/383631/709460.1": wrote only 4096 of 8192 bytes at block 177462
2012-10-28 12:13:22 UTC HINT:  Check free disk space.
2012-10-28 12:13:22 UTC STATEMENT:  SELECT * FROM pg_bulkload($1)

The table I'm inserting into have 74732120 rows already.

UPDATE:
The CSV file is 4-6 MB size, 150-300k rows in average.

UPDATE 2: the table definition is

id               | integer                  | not null default   nextval('shops_offer_id_seq'::regclass)
is_trashed       | boolean                  | not null
price            | double precision         | not null
availability     | double precision         |
description      | text                     |
identificator_id | character varying(148)   | not null
shop_id          | integer                  | not null
date             | timestamp with time zone | not null
transaction_id   | integer                  | not null
Indexes:
"shops_offer_pkey" PRIMARY KEY, btree (id)
"shops_offer_identificator_id" btree (identificator_id)
"shops_offer_identificator_id_like" btree (identificator_id varchar_pattern_ops)
"shops_offer_is_trashed_idx" btree (is_trashed)
"shops_offer_shop_id" btree (shop_id)
"shops_offer_transaction_id" btree (transaction_id)
Foreign-key constraints:
"shops_offer_identificator_id_fkey" FOREIGN KEY (identificator_id) REFERENCES products_identificator(id) DEFERRABLE INITIALLY DEFERRED
"shops_offer_shop_id_fkey" FOREIGN KEY (shop_id) REFERENCES shops_shopcore(id) DEFERRABLE INITIALLY DEFERRED
"shops_offer_transaction_id_fkey" FOREIGN KEY (transaction_id) REFERENCES shops_importtransaction(id) DEFERRABLE INITIALLY DEFERRED

The row sample of CSV is

201298602,False,29.2,1.0,"","5000000000500000000037",965,"2012-11-05 11:10:23",1269570

Last time the file was 7.5MB.
We solved the issue for now by increasing disk space, but the problem persists.


texts: "All joins can be rewritten to sub queries", is this statement true?

texts:
I wonder if there any case that a join can not be converted to a sub-query.

BTW: I am not discussing advantages and disadvantage of using sub-query or using join.


texts: want to download 3GB database table from my development mysql server

texts:
I need to download database structure with data. Total size of the DB is more than 3GB. If i use phpmyadmin it is not working. I have also tried by increasing the execution time in php.ini. I need some tool to import and Export the data base. My database server is Mysql server.

My requirement is any tool that will help to download Large database and Upload to another server. It should hold the download when internet fails. IF internet is ready it should continue the download process.

The answer already existing haven't worked for me. My issue is I need to export and import data from one server to other. if Internet Connection fails. It should not start a fresh export. It should continue the incomplete process.


texts: Make SQL Server Agent retry failed script with Try/Catch

texts: I have a script that has a try catch in it so that if it fails I can roll back the transaction. It runs on a job scheduled through SQL Server Agent. But when it fails SQL Server Agent does not see that it failed, I'm assuming because I catch the error. I want it to still retry the job even if I catch the error. I put in the RAISEERROR command in my catch part of the script to try and throw the error up to SQL Server Agent but it did not work. Any solutions?


texts: Is storing redundant data part of the (CouchDB) self-contained data concept?

texts:
The CouchDB concept speaks about self-contained documents. In other words: ALL related data is stored in a single concept, instead of splitting them in multiple tables and reference each other using FKs.

  An invoice contains all the pertinent information about a single transaction the seller, the buyer, the date, and a list of the items or services sold.

But this means that we end with redundant data in our database. Let's consider the invoice example: I buy 10 products over a period of time, which results in 10 invoices. Now several information are redundant, e.g. my billing address, delivery address and so on. That's at least a waste of space.

Considering other use-cases, updating data can be very complex. If we have a blogging-cms like WordPress, every post-document contains metadata like author name/email and so on. If those information changes, we've to update a lot of documents. And we have to know the structure from all of them. E.g. the email could be used in posts, comments, log entrys and so on.

When you think that the CouchDB allow a different form for any document, updating such information looks like a horrible tasks. I used MySQL as relational database for years and it seems much more meaningful to normalize such information, put e.g. users in a user table/collection, so that we can refer to some kind of PK.

What is the purpose behind those concept in CouchDB?

So my question is: Did I understood the concept of CouchDB properly, that they drop normalisation completely? I see the (theoretically?) benefit of having a better performance when no joins are required. Is the concept that we get a database with high throughput, but have to live with a (possible massive) amount of redundant data and we have to deal with a complex update-process when related metadata changes?


texts: Can underscore be forced as a word splitter without a full-text parser plugin?

texts:
Is there any way in MySQL to force underscore to become a word splitter character (like space or dash) without having to use a fulltext parser plugin?

If not, does such a plugin already exist or do I have to learn to make my own?


texts:
i try to create parent table for exists table, by next algorithm:

-- CREATE TABLE
CREATE TABLE t1 (id serial PRIMARY KEY);
CREATE TABLE foreign_t1 (id serial, t1_id integer REFERENCES t1 (id));
-- insert data
INSERT INTO t1
SELECT * FROM generate_series(1,4);
INSERT INTO foreign_t1
SELECT generate_series, generate_series % 3 + 1
FROM generate_series(1,9);
-- CRETAE PARENT table
CREATE TABLE parent_t1 (LIKE t1 including all);
-- ALTER t1 DO it inherit of the parent
ALTER TABLE t1 INHERIT parent_t1;
-- TRY TO MOVE FK TO the parent
ALTER TABLE foreign_t1 DROP CONSTRAINT foreign_t1_t1_id_fkey;
ALTER TABLE foreign_t1 ADD CONSTRAINT
foreign_t1_t1_id_fkey FOREIGN KEY(t1_id)
REFERENCES parent_t1 (id);

But cath the error:

ERROR:  insert or update on table "foreign_t1" violates foreign key constraint "foreign_t1_t1_id_fkey"
DETAIL:  Key (t1_id)=(2) is not present in table "parent_t1".

And when i try to validate the data looks like all exists:

dev=# SELECT id FROM parent_t1 WHERE id = 2;
 id
----
  2

i can't understand what i missing?


texts: Count hours when a specific value is higher than a treshold

texts:
i have this JSON that is contained in "data" column, the table also have: insert time, pk and some fk as reported below:
PK  INSERT_TIME                     DATA   SENSOR
1   2020-01-04 15:39:44.19364+00    {}       7

The table is populated with an external application that, about every minute, pushes new data, this is the JSON that i have typically in the table:
{
   "dev":11,
   "kw":43.0,
   "lux":200.2,
   "temp":20.3,
}

My goal is to build a query that, having all rows in the table is able to count how many hours in total and hours per day the device was up and running and producing kW, so, when the kW value is greater than zero (those measurements comes from a solar inverter system)
This is the query that i've built, actually i'm able to select only the rows that have the kw value greater than 1 and group them by month, but i'm not able to figure out how to calculate the production hours:
SELECT mt.insert_time,
       date_part('month', mt.insert_time) as mon
FROM mytable mt
WHERE sensor = 7
  AND date_part('month', mt.insert_time) = 5
  AND (mt.data->'kw')::int > 1

What i'm expecting:
I need to get the exact amount of working hour of my solar panel production system, i'm assuming that the solar panel "works" only when the kW value is greater than zero.
I want to see, day by day, and month by month the working hours of my panel.
Here guys i'm reporting an example of my data rows
+-----------------------------------------------------------------------------------------------------+
|                                     pk,insert_time,data,sensor                                      |
+-----------------------------------------------------------------------------------------------------+
| 3003711,2020-10-03 09:55:54.271738+00,"{""dev"": 12, ""kw"": 0, ""temp"": 20.8, ""lux"": 2.0}",12   |
| 3003692,2020-10-03 09:54:54.289131+00,"{""dev"": 12, ""kw"": 0, ""temp"": 20.6, ""lux"": 2.0}",12   |
| 3003681,2020-10-03 09:53:54.287502+00,"{""dev"": 12, ""kw"": 9.0, ""temp"": 20.5, ""lux"": 2.0}",12 |
| 3003670,2020-10-03 09:52:54.284262+00,"{""dev"": 12, ""kw"": 9.0, ""temp"": 20.5, ""lux"": 2.0}",12 |
| 3003659,2020-10-03 09:51:56.382746+00,"{""dev"": 12, ""kw"": 12, ""temp"": 20.5, ""lux"": 2.0}",12  |
| 3003648,2020-10-03 09:50:54.279558+00,"{""dev"": 12, ""kw"": 9.0, ""temp"": 20.5, ""lux"": 2.0}",12 |
| 3003637,2020-10-03 09:49:56.377988+00,"{""dev"": 12, ""kw"": 9.0, ""temp"": 20.5, ""lux"": 2.0}",12 |
+-----------------------------------------------------------------------------------------------------+

The final result may produce a query that (starting from a specific day of month, or specific month, or specific year) will give me the number of minutes and hours that the system was powered up and producing kW.
Like, for example:

Selecting the date of 10/08/2020 i need to get the total production minutes and respective hours count
Selecting the month of july (7) i need to get the total production minutes and respective hours count
Selecting the year (2020) i need to get the total production minutes and respective hours count


texts: If a table with a surrogate key has a column known to have unique non null values (eg SSN), does it violate 3NF?

texts:
As I understand it, third normal form (3NF) basically means there should be exactly one key.

If a table with say an auto-increment id column also has a column known to be unique and not null, eg social security number, this other column could be used as the key.

Ignoring practical/business issues (eg ecurity/privacy risk when passing around SSN as a key/FK), from a strictly schema design aspect, would such a table not be in 3NF because there are effectively 2 keys?

Would the answer vary on whether there was a unique key on the other column? If so, why?


texts: Exist on table from linked Server giving Error 4104

texts:
I am comparing two tables on different Servers and the query I am running looks like this:

Select top 100 *
From [ServerA].[MyDB].[dbo].[Table1]
    Where Exists( Select * from [ServerB].[TheirDB].[dbo].[TableA1]
    Where [ServerA].[MyDB].[dbo].[Table1].[foo] = [ServerB].[TheirDB].[dbo].[TableA1].[bar])

This is not working and throwing error 4104 on [ServerB].[TheirDB].[dbo].[TableA1].[bar] even though bar is a valid column and

Select * from [ServerB].[TheirDB].[dbo].[TableA1]

Works fine


texts: Unused Indexes - Consider Primary Key Constraint Supporting Indexes?

texts: I have given a task to find out the unused indexes in my production environment. I'm using a DMV to find the unused indexes should i include the Primary key Clustered Indexes on it? If a Primary key Index isn't being used, shouldn't that be of some concern? Unless, perhaps, it is a small table that isn't accessed very often?


texts:
ERROR 1067 (42000) at line 25: Invalid default value for 'created_on'

I'm getting this error while restoring a table after taking the dump(remotely). I have used

 mysqldump -h (hostname) -u -p (dbname) (tablename) > dump.sql

and restored using

 mysql -u -p (dbname) < dump.sql.

Thank-you.


texts: Postgresql 9.1 PITR file system base backup - do I need to stop database?

texts:
I am preparing for a migration of a big database to another server and plan to use WAL archiving and Point-In-Time-Recovery to minimize downtime. After reading the documentation I learned I have to do full filesystem-level base backup since the point of WAL archiving started.

In http://www.postgresql.org/docs/9.1/static/backup-file.html, it is stated that:

  The database server must be shut down in order to get a usable backup.
  Half-way measures such as disallowing all connections will not work
  (in part because tar and similar tools do not take an atomic snapshot
  of the state of the file system, but also because of internal
  buffering within the server). Information about stopping the server
  can be found in Section 17.5. Needless to say, you also need to shut
  down the server before restoring the data.

Next, in the PITR section it is said the source server does not have to be shutdown if pg_start_backup() was run before.

http://www.postgresql.org/docs/9.1/static/continuous-archiving.html

  Perform the backup, using any convenient file-system-backup tool such
  as tar or cpio (not pg_dump or pg_dumpall). It is neither necessary
  nor desirable to stop normal operation of the database while you do
  this.

Also this caught my eye:

  Some file system backup tools emit warnings or errors if the files
  they are trying to copy change while the copy proceeds. When taking a
  base backup of an active database, this situation is normal and not an
  error

If the database binary files are changing while performing the backup and this is allowed, how is the file format consistency guaranteed and can I be sure I won't end up with corrupted files?

So my question is, do I or do I not need to stop the source server when performing the full file system backup if steps in PITR section of documentation are followed? And how is it possible the base backup is correct if the files are allowed to change during it? Don't I end up with partial overwrites or something like that? My guess would be that any such damage can be repaired by restoring WAL files? Is this correct?

I am probably just asking what was already said in the documentation, but I need to be sure and I am a bit jumpy about this migration process.

Thank you.


texts:
I need to know if the T-SQL below is correct to roll back if something bad happens.

I first create a temp table

IF OBJECT_ID(N'tempdb..#LocalSummary') IS NOT NULL
    DROP TABLE #LocalSummary

CREATE TABLE  #LocalSummary (

then I populate the table

INSERT INTO #LocalSummary
   SELECT *....

Then I start my try catch

BEGIN TRY
 BEGIN TRANSACTION

    DELETE FROM LocalSummaryRealTable

    INSERT INTO LocalSummaryRealTable
        SELECT S.*
        FROM #LocalSummary AS S

    COMMIT
END TRY
BEGIN CATCH
       IF @@TRANCOUNT > 0
              ROLLBACK TRANSACTION
    ...

I noticed when I did the insert into the LocalSummaryRealTable the columns didn't line up and I got an error. I expected things to roll back but in SSMS query window it gave me a message that the trans wasn't committed and asked me if I wanted to commit it. Why would this ask me when it should of been rolled back?


texts: Powershell function to drop table(s) from SQL Server database

texts:
### List of tables that require full sanitizing
$requiredTables = 'table1','table2','table3'

foreach ($table in $requiredTables){

function Delete-FromTable{
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString =  "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText =

"
delete from $table
"

$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandTimeout = 0
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$test=$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
}

Delete-FromTable

}


texts: Multiple AGs on same Windows Failover cluster the Listeners don't work properly

texts:
Here is my case. I have Server1 and Server2, which were installed 4 SQL instances as follows:

Server1

Server1\SQL1
Server1\SQL2

Server2

Server2\SQL1
Server2\SQL2

The Server1\SQL1  and Server2\SQL1 are replicas in SQLAG1 which is listening on the listener xxx.xxx.xxx.54

The Server1\SQL2 and Server2\SQL2 are replicas in SQLAG2 which is listening on the listener xxx.xxx.xxx.55.

But when I use SSMS to connect to xxx.xxx.xxx.54 or xxx.xxx.xxx.55 It always connects to the Server1\SQL1 which is the primary replica of SQLAG1.

Any thoughts? Do I need to install more AGs on the same servers? How can I separate them by listeners?


texts:
I have searched over the internet looking for a function like REGEXP_REPLACE in Oracle, regexp_replace in PostgresSQL but I haven't find one similar in MySQL just REGEXP and RLIKE, but these operators just check whether the string matches pattern containing a regular expression not what the match is.

I have read about UDF like mysql-udf-regexp, but it doesn't work for me since I want something that I can manipulate like stored functions for faster optimization tasks or whatever conditions I want.

My questions are:

Do I have to make 2 functions if I want to delete all the alpha characters when I want just letters or vice versa?
Does anyone know any better way to approach this?


texts: Error creating database in SQL Server Management Studio. (Could not load file or assembly 'Microsoft.SqlServer.Sqm)

texts:
I'm trying to create a new database in SSMS, but I get this error:

  Could not load file or assembly 'Microsoft.SqlServer.Sqm, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. (SqlMgmt)


texts: Re-page Oracle tables after removing CLOB fields

texts:
I have a table that has a large amount of CLOB data and a large number of records. Even with indexes, some queries are extremely slow. My assumption is that the reason for this is two fold:

There's a lot of data being selected.
The data for these tables is spread out over a large number of pages in the db.

We decided to move the CLOB data to another table for the DATA and modify our queries to no longer select the CLOB columns and don't join to the new DATA table but our queries are still extremely slow. Is there some additional step we need to take to tell Oracle to re-optimize the table that no longer has the CLOBs?


texts: How to use SUBSTRING using REGEXP in MySQL

texts:
I have the following situation. I have to substring regular expression from description using MySQL.
Description:

  Lorem D9801 ipsum dolor sit amet

Where D9801 is REGEXP. Every strong text description has different content but my regexp should looks like: REGEXP 'D[[:digit:]]{4}'

REGEXP always has "D" at the beginning and "xxxx" - 4 digits at the end: Dxxxx

I know the REGEXP returns only true/false value, but how can I make query to return only 'D9801' value?

I tried something like this:

SELECT SUBSTRING (description, LOCATE(REGEXP 'D[[:digit:]]{4}', description), 5)
FROM (
   SELECT "Lorem D9801 ipsum dolor sit amet" AS description
) temp

I know it's wrong, so I try with this:

SELECT
    id,
    SUM(description REGEXP 'D[[:digit:]]{4}') AS matches,
    CASE
        WHEN (SUM(description REGEXP 'D[[:digit:]]{4}') > 0) THEN
            SUBSTRING(description, LOCATE( /*POSITION_OF_REGEXP_IN_DESC*/ , description), 5)
        ELSE 'Brak schematu'
    END AS show_substr FROM ps_description GROUP BY id;

But how to find position of regexp?

I heard about UDF but I cannot use it, I use OVH hosting.


texts: Which index will be used in this scenario?

texts:
SQL Server 2014 Standard Edition

I need to find the number of flights that are to and from specific cities for certain months. E.g.

select count(*)
from flights
where flightTo_AirportCode = 'aaaa'
and flightFrom_Airportcode = 'bbbb'
and flightdate < '2016-04-01'
and flightdate > '2016-02-28' ;

The table schema is below.

I am trying to estimate if index modelA or index modelB (below) is preferable (it takes many hours to build the index, and disk space allows only one to exist at a time, so I am trying to look before I leap).

From my experience, either index will do. Am I right?

  create index [modelA] on flights (flightTo_AirportCode, flightFrom_AirportCode, flightDate)

  create index [modelB] on flights (flightDate, flightTo_AirportCode, flightFrom_AirportCode)

(Or, better, is there a binary index or advanced mechanism I can use to approach this?)

CREATE TABLE [dbo].[flights](
    [flightId] [uniqueidentifier] NOT NULL,
    [accountId] [uniqueidentifier] NULL,
    [flightDate] [datetime] NULL,
    [flightTo_AirportCode] [nvarchar](30) NULL,
    [flightFrom_AirportCode] [nvarchar](30) NULL,
    -- ... 45 more fields
    CONSTRAINT [PK_flight] PRIMARY KEY CLUSTERED
(
    [flightId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


texts:
I need a 2-3 TB storage server group for Oracle Database.  We want SAN Storage & Server for Oracle Replication, but not Microsoft Storage Server.

For example : HP DL380 G6 as a server and EMC CLariion CX4 120 as a SAN Storage etc...

We have an option, but want some others.  What do you recommend for a Server & for SAN Storage (Make & Model) to use for Oracle Replication?


texts: MySQL, can't find MYD files on OS X, MAMP

texts:
Try as I may I can't find the data files for my MAMP installation!

FRM files are here:

/Applications/MAMP/db/mysql/database_name/

but, there are no corresponding MYD or MYI files anywhere.
The tables definitely have content.

Is it possible that this version of MySQL creates MYD/MYI files that are not the same as the table name? I've been at this for hours.
Any help is appreciated.

OS X Mavericks.
MySQL 5.5.34

+--------------------+---------+
| engine             | tblsize |
+--------------------+---------+
| CSV                |       0 |
| InnoDB             |   16384 |
| MEMORY             |       0 |
| MyISAM             |    4096 |
| PERFORMANCE_SCHEMA |       0 |
| total              |       0 |
+--------------------+---------+
6 rows in set, 1 warning (0.38 sec)

+--------------------------------+
| @@global.innodb_file_per_table |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (0.00 sec)

+------------------------------+
| @@global.datadir             |
+------------------------------+
| /Applications/MAMP/db/mysql/ |
+------------------------------+
1 row in set (0.00 sec)


texts:
I'm working my way through the steps in this slide to configure Postgresql & PostGIS.

I configured my user role as directed on this slide. Note, the new role should not be a superuser.

That brings me to my current problem. How do I alter my role to be a superuser? In the image below, you can see I'm connected to template_postgis using my role (also my system account name as instructed on the above slide). When I try to create the extension postgis, I get a permission denied message.


texts: CREATE TABLE LIKE another table but with additional columns

texts:
Is there a way to create a table that has the same structure as another table, but with additional columns?
From this question, I can copy a table's structure with:

CREATE TABLE new_table_name ( like old_table_name including all)

And then I can add columns one at a time, with ALTER TABLE new_table_name ADD COLUMN... but I am looking for a way to do this more succinctly, like:

CREATE TABLE new_table_name ( like old_table_name including all), new_col1 new_col1_type, new_col2 new_col2_type,...


texts: troubleshooting mysqlimport very large text file

texts:
I've been attempting to import a very large (390 GiB) text file using the mysqlimport client.
I've been successful at importing a 14 GiB text file as well as numerous smaller files using this method. Server version is 5.7.28

The specific command used is:

mysqlimport -u root --fields-terminated-by='\t' --fields_escaped_by=\\ homo_sapiens_variation_98_38 -L *.txt

The my.cnf file I'm using:

[mysql]
local-infile = 1

[mysqld]
secure-file-priv = ""
local-infile = 1
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size = 8G
innodb_write_io_threads = 64
innodb_log_buffer_size = 256M
innodb_read_io_threads = 64
innodb_buffer_pool_size = 32G
innodb_doublewrite = 0
innodb_flush_method = O_DIRECT

[client]
loose-local-infile = 1

It always hangs (repeated 3 times) when it reaches an auto increment value of 2 324 322 623 (i've spaced for clarity).

The number of lines in the file is 2 324 037 635 (determined by sed -n '$=' transcript_variation.txt)

This suggests the file has been read but the process of writing the data did not take place.

-Update-

Table structure

CREATE TABLE `transcript_variation` (
  `transcript_variation_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `variation_feature_id` int(11) unsigned NOT NULL,
  `feature_stable_id` varchar(128) DEFAULT NULL,
  `allele_string` text,
  `somatic` tinyint(1) NOT NULL DEFAULT '0',
  `consequence_types` set('splice_acceptor_variant','splice_donor_variant','stop_lost','coding_sequence_variant','missense_variant','stop_gained','synonymous_variant','frameshift_variant','non_coding_transcript_variant','non_coding_transcript_exon_variant','mature_miRNA_variant','NMD_transcript_variant','5_prime_UTR_variant','3_prime_UTR_variant','incomplete_terminal_codon_variant','intron_variant','splice_region_variant','downstream_gene_variant','upstream_gene_variant','start_lost','stop_retained_variant','inframe_insertion','inframe_deletion','transcript_ablation','transcript_fusion','transcript_amplification','transcript_translocation','feature_elongation','feature_truncation','protein_altering_variant','start_retained_variant') DEFAULT NULL,
  `cds_start` int(11) unsigned DEFAULT NULL,
  `cds_end` int(11) unsigned DEFAULT NULL,
  `cdna_start` int(11) unsigned DEFAULT NULL,
  `cdna_end` int(11) unsigned DEFAULT NULL,
  `translation_start` int(11) unsigned DEFAULT NULL,
  `translation_end` int(11) unsigned DEFAULT NULL,
  `distance_to_transcript` int(11) unsigned DEFAULT NULL,
  `codon_allele_string` text,
  `pep_allele_string` text,
  `hgvs_genomic` text,
  `hgvs_transcript` text,
  `hgvs_protein` text,
  `polyphen_prediction` enum('unknown','benign','possibly damaging','probably damaging') DEFAULT NULL,
  `polyphen_score` float DEFAULT NULL,
  `sift_prediction` enum('tolerated','deleterious','tolerated - low confidence','deleterious - low confidence') DEFAULT NULL,
  `sift_score` float DEFAULT NULL,
  `display` int(1) DEFAULT '1',
  PRIMARY KEY (`transcript_variation_id`),
  KEY `variation_feature_idx` (`variation_feature_id`),
  KEY `consequence_type_idx` (`consequence_types`),
  KEY `somatic_feature_idx` (`feature_stable_id`,`somatic`)
) ENGINE=MyISAM AUTO_INCREMENT=2324322623 DEFAULT CHARSET=latin1

RAM 128Gb
State: "Repair with keycache"

Any suggestions on how I can troubleshoot and import this file?


texts:
In an application, I am using mysql's TEMPORARY TABLE. Since this application does not need permanent tables, I wonder if I can find an alternative RDMS designed for memory tables. I hope to find an in-memory database system, which is

Lightweight
Faster than mysql temporary table
Having an API for C (my programming language)

and no special features is needed, just creating table and performing SQL queries. FK or other advanced features are not needed.


texts: TIMESTAMP accuracy needs to be increased in GridDB

texts: Timestamp data type needs to be increased from (milliseconds) to (nanoseconds) to accommodate financial time-series applications including MIFID II regulatory requirements.


texts: Connectivity to SQL Server on Azure Error: 121 - The semaphore timeout period has expired

texts:
I having trouble connecting to a SQL Server database on Azure. We've already added the IP address to the firewall exclusion list and the username/password work when connecting from other machines. We tried with both username/password and MFA.
What else could be causing the problem?

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) (Microsoft SQL Server, Error: 121)


texts: SQL recursion and cte - dates timeseries

texts:
I need to build vector(s) of time series dates (say 10) for a subset of start dates.

The following statement returns 10 consecutive dates starting from '2010-01-01'.

;with cte as
(select 1 i union all
 select i+1 i from cte where i < 10)
select '2010-01-01' as start_date, dateadd(d, i-1, '2010-01-01') AS trial_date
INTO #tmp_trialdates
FROM cte

Does anyone have any suggestions as to how I could incorporate querying a table (or other dataset) of multiple start dates (not necessarily sequential),  generating time series dates for each start date, then populating the data in the one output table?


texts: Disadvantages to using ON DELETE CASCADE on every foreign key?

texts:
Our application currently has "soft delete" built-in to most tables with a BIT "deleted" column.
There is now a requirement for "hard delete" functionality.
Are there any disadvantages, gotchas, or things to bear in mind when adding ON UPDATE CASCADE ON DELETE CASCADE to every single foreign key in the database?


texts: What happens when we take a timediff between timestamp and datetime

texts:
Say I have following table in mysql (5.5+):

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL,
  `x_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `y_time` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

My x_time is a timestamp, and y_time is a datetime column

I want to understand that what happens when I do TIMESTAMPDIFF(SECOND, x_time, y_time)

Does x_time gets converted to datetime and then the difference is calculated? or it happens vice-versa? Mysql manual (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timestampdiff) says: One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary.

My assumption is that both the columns x_time and y_time has to been of one datatype to calculate TIMESTAMPDIFF, and if it is not already then mysql has to do it internally. Am I wrong here?

Also, if it has to convert one of the column to match the another column's datatype, then how the performance will matter for the following query? Will it do the datatype conversion for 5000 rows? And how time consuming process is this?

SELECT * from test WHERE `TIMESTAMPDIFF(SECOND, x_time, y_time)` LIMIT 100000, 5000


texts:
We're using MySQL with the INNODB storage engine.
The account table is accessed by two different transactions. We didn't expect a deadlock because only one transaction is holding a lock (2):

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-05-12 11:04:51 7fa10bf3b700
*** (1) TRANSACTION:
TRANSACTION 91255426, ACTIVE 0 sec starting index read
mysql tables in use 5, locked 5
LOCK WAIT 10 lock struct(s), heap size 2936, 6 row lock(s), undo log entries 3
MySQL thread id 434044, OS thread handle 0x7fa14b33b700, query id 224359496 10.0.31.146 test_user statistics
SELECT netbalance, grossbalance INTO p_netbalance, p_grossbalance FROM account WHERE user_id = NEW.user_id
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 12332 n bits 824 index `user_id` of table `test`.`account` trx id 91255426 lock_mode X locks rec but not gap waiting
Record lock, heap no 212 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 00000000005e0c01; asc      ^  ;;
 1: len 8; hex 00000000035de4e3; asc      ]  ;;

*** (2) TRANSACTION:
TRANSACTION 91255425, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 5, locked 5
13 lock struct(s), heap size 2936, 9 row lock(s), undo log entries 4
MySQL thread id 434043, OS thread handle 0x7fa10bf3b700, query id 224359489 10.0.31.146 test_user update
INSERT INTO account (added, user_id, currency_code, netbalance, grossbalance, status) VALUES
                (CURRENT_TIMESTAMP, NEW.user_id, NEW.currency_code, NEW.netbalance, NEW.grossbalance, "ACTIVE")
                    ON DUPLICATE KEY UPDATE netbalance = (netbalance + NEW.netamount), grossbalance = (grossbalance + NEW.grossamount + NEW.testfee)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 12332 n bits 824 index `user_id` of table `test`.`account` trx id 91255425 lock_mode X locks rec but not gap
Record lock, heap no 212 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 00000000005e0c01; asc      ^  ;;
 1: len 8; hex 00000000035de4e3; asc      ]  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 12332 n bits 824 index `user_id` of table `test`.`account` trx id 91255425 lock_mode X waiting
Record lock, heap no 212 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 00000000005e0c01; asc      ^  ;;
 1: len 8; hex 00000000035de4e3; asc      ]  ;;

*** WE ROLL BACK TRANSACTION (1)

These transactions are two instances of the same trigger:

/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER balance_update BEFORE INSERT ON regtransaction
FOR EACH ROW BEGIN
    DECLARE p_netbalance   FLOAT(21, 4) DEFAULT NULL;
    DECLARE p_grossbalance FLOAT(21, 4) DEFAULT NULL;

    IF (NEW.added AND ABS(NEW.added - NOW()) > 300.0) THEN
        SET p_check = 0;
    END IF;

    SELECT netbalance, grossbalance INTO p_netbalance, p_grossbalance FROM account WHERE user_id = NEW.user_id;

    IF (p_netbalance IS NULL OR p_grossbalance IS NULL) THEN
        CALL test.calculateBalanceForUser(NEW.user_id, NEW.cro, p_netbalance, p_grossbalance);
    END IF;

    SET NEW.netbalance   = p_netbalance   + NEW.netamount;
    SET NEW.grossbalance = p_grossbalance + NEW.grossamount + NEW.testfee;

    INSERT INTO account (added, user_id, currency_code, netbalance, grossbalance, status) VALUES
        (CURRENT_TIMESTAMP, NEW.user_id, NEW.currency_code, NEW.netbalance, NEW.grossbalance, "ACTIVE")
        ON DUPLICATE KEY UPDATE netbalance = (netbalance + NEW.netamount), grossbalance = (grossbalance + NEW.grossamount + NEW.testfee);
END */;;


texts: Oracle Number (10, 0) decimal in (C#) code

texts:
The following query is executed to add a new column to an existing table:

ALTER TABLE TABLE1 ADD COLUMN1 NUMBER(10, 0) DEFAULT -1 NOT NULL.

Using DBForge to check the database, result in NUMBER (10, 0) column

An OracleConnection (of Devart dotConnect) is used and the NumberMapping property is set:

connection.NumberMappings.Add(OracleNumberType.Integer, 6, 10, typeof(int));
connection.NumberMappings.Add(OracleNumberType.Integer, 11, 19, typeof(long));

As soon as the next query "SELECT * FROM TABLE1" is executed, the DataTable contains 'COLUMN1' column with DataType decimal! It should be an integer according to the NumberMappings.

If the 'DEFAULT -1 NOT NULL' part is removed in the query, the 'COLUMN1' DataType is Int32.

I have checked the schema of the (Data)Table as well and noticed the NumericPrecision is set to 0 (zero). It should be 10.

DataTable schema ;
using (OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
{
    schema = reader.GetSchemaTable();
}

The issue only occurs in certain tables (not all). Does Oracle provide a way to modify or fix the NumericPrecision?


texts: Query on hot standby replica server taking forever, while identical query on master is fast

texts:
I have PostgreSQL database and a table with the following schema:

profiles (
    id serial primary key,
    username varchar(255) unique,
    followers bigint,
    categories citext array not null default '{}'
);

and I have created indices as follows

CREATE INDEX IF NOT EXISTS username_index
    ON profiles(username);
CREATE INDEX IF NOT EXISTS followers_index
    ON profiles(followers);
CREATE INDEX IF NOT EXISTS categories_index
    ON profiles USING GIN(categories);

Creating a GIN index on a citext data type required additional operations which I defined as follows:

CREATE OPERATOR CLASS _citext_ops DEFAULT
  FOR TYPE _citext USING gin AS
  OPERATOR 1 &&(anyarray, anyarray),
  OPERATOR 2 @>(anyarray, anyarray),
  OPERATOR 3 <@(anyarray, anyarray),
  OPERATOR 4 =(anyarray, anyarray),
  FUNCTION 1 citext_cmp(citext, citext),
  FUNCTION 2 ginarrayextract(anyarray, internal, internal),
  FUNCTION 3 ginqueryarrayextract(anyarray, internal, smallint, internal, internal, internal, internal),
  FUNCTION 4 ginarrayconsistent(internal, smallint, anyarray, integer, internal, internal, internal, internal),
  STORAGE citext;

Now, I need to query profiles that have a specific category (one to be precise). This is the query I am using:

SELECT id, username, followers, categories FROM profiles
  WHERE $1 = ANY(categories) AND
        followers is not null
  ORDER BY (followers, id) DESC
  LIMIT 50;

I also ran it with EXPLAIN ANALYZE and this is the output:

       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..210.57 rows=50 width=70) (actual time=95099.206..96187.302 rows=50 loops=1)
   ->  Index Scan Backward using followers_index on profiles  (cost=0.43..4236073.48 rows=1007921 width=70) (actual time=95099.202..96187.206 rows=50 loops=1)
         Filter: ('food'::citext = ANY (categories))
         Rows Removed by Filter: 80711
 Planning time: 387.986 ms
 Execution time: 96187.590 ms
(6 rows)

As seen in this output the executor uses an index scan, as expected. The query ran on a hot standby replication server, I wonder if that is the cause of the slowdown.

EDIT: Actually it turns out that the problem is indeed the hot standby replica. The same query on master runs in 100ms, with the same query plan:

                                                                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..198.22 rows=50 width=70) (actual time=97.456..100.930 rows=50 loops=1)
   ->  Index Scan Backward using followers_index on profiles  (cost=0.43..6080117.69 rows=1537024 width=70) (actual time=97.454..100.920 rows=50 loops=1)
         Filter: ('food'::citext = ANY (categories))
         Rows Removed by Filter: 80672
 Planning time: 0.196 ms
 Execution time: 100.957 ms
(6 rows)


texts: How to structure SQL data model to maintain data integrity

texts:
Background

I'm building a Private Chef booking service where you can book a Chef to cook you a custom Menu.  I'm having trouble creating a SQL db schema that accurately represents the domain while maintaining data integrity.

Requirements

Customers create a Booking by selecting a Chef and a Menu and choose the MenuItems they want for each MenuCourse
A Chef defines a set of MenuItem that a customer can choose from to create their Booking
A Menu is a collection of MenuCourses. (ex. A Menu named "Tasting Menu" is a 6 course meal, where each MenuCourse costs between $10-20).
A Chef should be able to associate their MenuItems with multiple Menus and MenuCourses.
A customer Booking should contain the Chef the customer selected along with the Menu (and the MenuItems) that will be served. Booking price is determined by the Menu and MenuCourse selections (an appetizer costs less than an entree)

Problem

In my current data model, I have the following issues that I'm not sure how to fix:

it's possible to create a Booking with Chef "A", but then have a BookingMenuItem that references a MenuItem with Chef "B" (all the BookingMenuItem for a Booking should belong to the same Chef)
a Booking references a particular Menu (which I need for pricing, pricing is based on Menu and MenuCourse) however a BookingMenuItem for that Booking could reference a completely different Menu or MenuCourse

Is it possible to re-design my db schema to fix the integrity problems I'm having? Or do I just have to implement these checks at the application level. Thanks!


texts: Oracle Scheduled Job Stays in State RUNNING

texts:
I have an Oracle 11.2g on which I use Scheduled Jobs to get some regular work done. There are around 15 jobs running 24/7.

The time interval at which those jobs run varies between 1 second for some of the jobs, and 5 minutes for some other jobs. Each job starts a single stored procedure (different for each scheduled job). The job is created by

declare
begin
    DBMS_SCHEDULER.CREATE_JOB (job_name => 'TRANSPORT_ORDER_POLLING_JOB',
                               job_type => 'PLSQL_BLOCK',
                               job_action => 'begin PROC_POLL_TRANSPORT_ORDERS; end;',
                               start_date => SYSDATE,
                               repeat_interval => 'FREQ=SECONDLY;INTERVAL=1',
                               auto_drop => FALSE, -- Default ist TRUE
                               enabled => TRUE);    -- Default ist FALSE
    commit;
end;

The corresponding procedure would be defined as

create or replace procedure PROC_POLL_TRANSPORT_ORDERS is

    pTaskID     varchar2(30);
    pErrorCode  number;

begin

    pTaskID := 'PROC_POLL_TRANSPORT_ORDERS';

    -- ...do the real work here...

    exception
    when others then
        LOGGER.INSERT_LOG_MESSAGE (pTaskID, 'PROC_POLL_TRANSPORT_ORDERS failed; ()', LOGGER.LOG_LEVEL_ERROR);
        LOGGER.INSERT_ERROR (pTaskID);
        rollback;
end PROC_POLL_TRANSPORT_ORDERS;

The aim of the wrapper procedure is to catch all exceptions and log them. If one of the jobs takes longer than its execution time interval, the job seems to be executed at the next time interval possible. Although not generally desirable, this sitaution may occur from time to time, since each job just polls its own table in search for new work to do, while the work load of a single table quite depends on physical conditions of my system.

My problem is, that sometimes the scheduler leaves a job after execution in the status RUNNING, although it has been ended hours ago. This in turn prevents the scheduler from starting the job anew, leaving me in the situation where my routing might stop working correctly.

My Question about this situation is: am I using the correct technology for my aim? What am I doing wrong?

As a note: I can not use triggers for this because I need to use library functions which would also alter some attributes of the row which triggered the call itself. This would raise an exception by oracle, hence the scheduled jobs.


texts:
I have a database having a staging_table.
From this table, entries are added to other table named Profile.
Whenever staging_table is updated, it checks whether all its entries are present in Profile, if any is not present, add it to profile.
Till, here its fine.
Now I want that even if an entry exists in Profile, there might be a condition where some fields in Profile have been changed. So also want those entries to get updated.

Requirement: I cannot use any flags or any other table to check for its update.

Should I compare all fields? But that is very in-efficient.
Please tell me, what should be the most efficient method that I should follow to achieve this?

Thanks


texts: Can I determine if a stored procedure uses dynamic sql without parsing the definition?

texts:
Since there seems to be no way to strip the procedure definition from comments  without parsing cf. this, there seem to be no reliably T-SQL query to answer this question.

Or can I get this information from any system view?

cf. my question about stripping comments


texts: Can XML columns in Oracle/MS SQL Server replace document oriented databases to some extent?

texts:
Just a strategic question: I am currently working with frequently changing data schemas and it takes a lot to keep track of versions and specific content.

I do, however, have a classic OLTP application, which requires ACID, transactions, joins, single-point-responsiblility; plus, the DB load seems not to be extreme, so a document oriented ("no SQL") database seems not to be a good solution.

I currently have numerous objects/tables with XML documents in clob/varchar(max) columns, the database being completely ignorant of their content. Also, many don't contain "real" XML, but simply a collection of XML elements with content. In fact, I use the XML columns much like noSQL documents.

As I have read, both Oracle and SQL Server have a kind of XML columns which is not bound to a schema, effectively stored in clob/blob style, but the database can have indices on certain content, such as an XPath to a certain XML element which frequently occurs and contains important data. Much like document databases can index documents by content, which not even every document may contain. They even can handle simple XML element collections, which are not true XML documents, if I understood right.

Looks like I can use some features of document oriented DBs through XML, living without a fixed schema, but still making parts of the XML data structure visible to the DBMS and it's clients without parsing the XML on client side. For example, I can SELECT all OrderItems which have a 2 year warranty stored somewhere in the XML, without a badly performing and possibly wrong "where Terms LIKE '%<warranty>2</warranty>%'".

Any experiences with this?

BTW, the XML is mostly read-only, few costly updates, most variable data is still in classic relational columns.


texts: What is the actual behavior of compatibility level 80?

texts:
Could somebody provide me with a better insight about the compatibility mode feature? It is behaving different than I expected.

As far as I understand compatibility modes, it is about the availability and support of certain language structures between the various versions of SQL Server.

It does not affect the inner workings of the database engine version. It would try to prevent use of features and constructs that were not yet available in earlier versions.

I just created a new database with compat level 80 in SQL Server 2008 R2. Created a table with a single int column and populated it with a few rows.

Then executed a select statement with a row_number() function.

My thought was, since the row_number function was only introduced in 2005, this would throw an error in compat 80 mode.

But to my surprise this worked fine. Then, surely, the compat rules are only evaluated once you 'save something'. So I created a stored proc for my row_number statement.

The stored proc creation went fine and I can perfectly execute it and obtain results.

Could someone help me to better understand the working of compatibility mode? My understanding is obviously flawed.