texts: LEFT JOIN / WHERE clause with is null and false Estimated Number Of Rows

texts:
I have a table named Product and a table named ProductPropertiesCountry.

Product is the definition of a product (description, price, productId, etc)

ProductPropertiesCountry contains restrictions on the product by country, for example IsBlocked, and attributes such as Special Price.

Product
 - ProductId
 - Description
 - Price

ProductPropertiesCountry
 - ProductId (PK)
 - CountryId (PK)
 - SpecialPrice
 - IsBlocked

I'm doing a LEFT JOIN between Product and ProductPropertiesCountry with a WHERE clause like this:

SELECT P.ProductId
FROM Product P
    LEFT JOIN ProductPropertiesCountry PPC ON
        P.ProductId = PPC.Product AND PPC.CountryID = 1
WHERE (IsBlocked IS NULL OR IsBlocked = 0)

The problem is that the execution plan does not handle IsBlocked IS NULL efficiently and it gives me Estimated number of rows = 1 after that filter.  As result, the query is slower.

This is much faster:

;WITH CATALOG
AS
(
   SELECT P.ProductId, ISNULL(IsBlocked, 0) AS IsBlocked
    FROM Product P
        LEFT JOIN ProductPropertiesCountry PPC
            ON P.ProductId = PPC.Product AND PPC.CountryID = 1
)
SELECT ProductId, IsBlocked
FROM CATALOG
WHERE IsBlocked = 0;

Do you have any idea for the reason of this behavior?

Any suggestion to change it to get the right estimated number of rows in execution plan?

I'm using SQL Server 2008.


texts:
I'm seeking some help understanding my PostgreSQL 9.3 log file.

(Note: maybe this should be multiple questions, they are all involve the same general topic - which is understanding my log file).  In my postgresql.conf file I have the following log related settings:

log_min_duration_statement = 0
log_line_prefix = '%m session_id: %c; trans_id: %x '
log_statement = 'all'

If I have the following in my log:

2014-01-15 21:39:22.217 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: BEGIN
2014-01-15 21:39:22.217 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.132 ms
2014-01-15 21:39:22.218 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: SELECT t.oid, typarray
    FROM pg_type t JOIN pg_namespace ns
        ON typnamespace = ns.oid
    WHERE typname = 'hstore';

2014-01-15 21:39:22.219 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 1.651 ms
2014-01-15 21:39:22.220 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: ROLLBACK
2014-01-15 21:39:22.220 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.043 ms
2014-01-15 21:39:22.220 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: BEGIN
2014-01-15 21:39:22.220 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.028 ms
2014-01-15 21:39:22.220 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: select version()
2014-01-15 21:39:22.221 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.204 ms
2014-01-15 21:39:22.222 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: select current_schema()
2014-01-15 21:39:22.222 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.136 ms
2014-01-15 21:39:22.222 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: show transaction isolation level
2014-01-15 21:39:22.222 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.065 ms
2014-01-15 21:39:22.223 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-01-15 21:39:22.223 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.281 ms
2014-01-15 21:39:22.223 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-01-15 21:39:22.223 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.063 ms
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: SELECT 'x' AS some_label
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.053 ms
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: ROLLBACK
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.032 ms
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: BEGIN
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.023 ms
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: show standard_conforming_strings
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.035 ms
2014-01-15 21:39:22.225 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: SELECT api_log_entry.id AS api_log_entry_id
    FROM api_log_entry
2014-01-15 21:39:22.227 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 1.319 ms

Questions:

In this log snipped, does each log entry (everything with a duration) represent a call from the client app (and is a round trip)?

Given my log_line_prefix, why is trans_id showing up as zero?  It scare me to say but I thought Id see a new trans_id for each "BEGIN" that was logged.


texts: DB2: how to export data file and own by db2inst1 via ADMIN_CMD

texts:
There is some requirement that I need to run the export command in Java via JDBC. Hence below is the command I execute

CALL SYSPROC.ADMIN_CMD('EXPORT TO /tmp/header.csv OF DEL SELECT * FROM MYTABLE');

However, when I ssh login to the database server via db2inst1, I checked the /tmp/header.csv is own by db2fenc1 user, which not allow me to delete but read-only access as shown below:

[db2inst1@26ea60b7e4a1 /]$ ls -lrt /tmp/header.csv
-rw-r--r-- 1 db2fenc1 db2fadm1 24335 Nov 12 12:25 /tmp/header.csv

May I know how to execute ADMIN_CMD as db2inst1? Or any other workaround?


texts:
The query below works pefectly in SQL Server

update userrole
set userid = 6 where userid = 5
and id not in
(
  select b.id from userrole a inner join userrole b
  on a.roleid = b.roleid and a.region = b.region
  where a.userid = 6 and b.userid = 5
)

When I use the same query in MYSQL, I get the exception 1093 which says Error Code: 1093. You can't specify target table 'userrole' for update in FROM clause.

How to change the query to MySQL equivalent?
How to use JOIN query instead of sub-query in the above query

Thanks in advance.


texts: Grouping data by MONTH on DATETIME column in SQL Server

texts:
I have a table Employee which has the columns Id, Date of joining and Name

Date of joining is a DATETIME column.

I want to know how many users have joined in the month of October?


texts: Is it possible to implement minimally logged reads from a disk table

texts:
My code uses a table that links SQL Server SPIDs with CompanyIDs.  For example

Spid|CompanyID
1   | 123
2   | 412

Every time a request reaches the database, the request first checks this table to get the current user's company ID, and if there isn't one, it inserts it.

Naturally, this is a very high traffic table.  My current implementation is using a non-durable in memory table using SQL Server 2016 in memory OLTP.  However, we have discovered a bug with memory pressure (msft currently looking at it) and need to move away from this implementation.

This table only contains ~300 records at any given time, and rarely do users WRITE to this table.  But there are hundreds of reads a second, potentially.

We're going to have to move back to a disk table for now, and I'd like to not log anything to the transaction log for reads on this table.  Is there a way to NOT log anything to the transaction log OR minimally log to the transaction log?

I looked into global temp table and also using TABLOCK and Minimally Logging keywords, but these all seem to be concerned only with high traffic WRITES.

Is there a way to accomplish what I want here?


texts:
I'm stuck with table design and query FOR XML

Sync engine for Ads requires XML schema in this format

<attribute1>value</attribute1>
<attribute2>value</attribute2>…
<lookup_list>
<lookup_item code="installations">220</lookup_item>
<lookup_item code="heating">228</lookup_item>
<lookup_item code="permits">233</lookup_item>
<lookup_item code="parking">162</lookup_item>
<lookup_item code="garden">166</lookup_item>
<lookup_item code="electronics">167</lookup_item>
</lookup_list>
<attributeN>value</attributeN>…

That lookup list is separate table that looks like this:

Lookup_item_id  code            lookup_item_title
220         ad_flat.installations   Town gas
221         ad_flat.installations   City water
224         ad_flat.heating_type    gas
226         ad_flat.heating_type    wood

For normal attributes I created this FOR XML query with sub-query for images that are in other table,

    SELECT
    attribute1,
    attribute2,
    (SELECT
    images.image_name AS image
    FROM images
    WHERE images.realestateID = realestates.ID
    FOR XML PATH (''), TYPE
    ) AS 'image_list'
FROM (SELECT DISTINCT
*
FROM realestates) realestates
FOR XML PATH ('ad_item'), ELEMENTS, root ('ad_list')

but for this lookup_list I have no idea how to design table and relations for that and how to write a query FOR XML export, please if someone have an idea I will be very grateful.


texts:
I am having issues quering pg_stat_statements and puzzled what else I missed?
See below what I have done. I still have issues when I query pg_stat_statements! I got error saying the libraries is not loaded.

#show config_file;

------------------------------------------------------------
 /var/opt/rh/rh-postgresql96/lib/pgsql/data/postgresql.conf

I have edit the config file and add the following

shared_preload_libraries = 'pg_stat_statements'         # (change requires restart)
pg_stat_statements.max = 10000
pg_stat_statements.track = all

3.# \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description
--------------------+---------+------------+-----------------------------------------------------------
 pg_stat_statements | 1.4     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

#select count(*) from pg_stat_activity;

 count
-------
    43
(1 row)

#select * from pg_stat_statements;

ERROR:  pg_stat_statements must be loaded via shared_preload_libraries

Thanks,
Lucia


texts:
I'm migrating a database from DB2 to MySQL. There are big tables, so it takes a huge amount of time. The question here is:

If there's a table with about 9 millions of records with InnoDB storage engine.

What of the next approaches would take less time?

Create the table without indexes, foreign keys or any constraints. Load the data. Alter table to set the constraints
Create the table with constraints and load the data.

In theory the load of the data on an indexed table is slower than on a non indexed table. But creating an index and foreign keys on big tables takes a lot of time.


texts: Synchronising databases / database technologies

texts:
I need some advice before we engage in some costly development work

We have two SQL Server database servers on the same network. One runs an in house database and the other a proprietary database that we bought in.

We want a couple of tables between the two to be synchronised (customer details / contact details)

Now if I were doing this I would create a trigger on a table in database A that when updated calls a stored procedure that updates a table in database B. And vice versa to keep the records in synch with an appropriate flag that activates the trigger.

The creators of the proprietary database that we need to interface with have proposed creating a network service that picks up changes in our database and extracts the data back to their own. And the transfer can only be one way by their service because of 'record locking'. Without making any assumptions is this really the best way to do things?

Thanks

NB both databases are running on SQL SERVER 2005


texts: How do I create a SQL Agent Alert on a custom error message in SQL Server 2012?

texts:
I want operators to be notified when I throw a custom error message. My understanding is that I need to add a message to sys.messages, then I can either RAISERROR or THROW that error ID. Creating an alert on the message ID and setting it to send to operators should create those notifications.

I've created a SQL Agent alert on the message ID of 50005, and am using the following T-SQL to create the message:

EXEC sp_addmessage @msgnum = 50005,
          @severity = 16,
          @msgtext = N'%s';
GO

Then, if I execute this:

RAISERROR (50005, -- Message id.
       16, -- Severity,
       1, -- State,
       N'My custom message');
THROW 50005, 'My custom message', 1;
GO

I get the following output as expected from both RAISERROR and THROW:

  Msg 50005, Level 16, State 1, Line 68
  My custom message

When I view the history of the alert though, it shows that it has not been triggered, and the operator does not receive an email update.

My agent alert looks like this:

Scripting the alert generates the following:

USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N'Alert DBA on custom errors',
    @message_id=50005,
    @severity=0,
    @enabled=1,
    @delay_between_responses=0,
    @include_event_description_in=1,
    @database_name=N'',
    @notification_message=N'',
    @event_description_keyword=N'',
    @performance_condition=N'',
    @wmi_namespace=N'',
    @wmi_query=N'',
    @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_update_notification @alert_name=N'Alert DBA on custom errors', @operator_name=N'My Operator', @notification_method = 1
GO

For your convenience, if you try to recreate my issue this will help you drop from sys.messages:

sp_dropmessage @msgnum = 50005;
GO


texts: Remove last character in selected column?

texts:
Query:

select * from aa;

Output:

ID  FirstName   LastName    City
-----------------------------------
29  Abcrdrr     hai         chennai
67  adf         adsd        adsf
1   John        vinoth      CITY

I want to display the lastname column without last character. I am using following queries:

Declare @name as varchar(100);

select @name = city from aa
Select @name, left(@name, len(@name)-1) as AfterRemoveLastCharacter

But output will come first row only


texts: Is it possible to use target column value on insert in SQL Server merge 'not matched' block?

texts:
When working with a MERGE statement in SQL Server to use the updated state of a temp table to later modify records across multiple tables in a db schema, I am running in an issue when executing the following stored procedure:

-- Create procedure, set up, etc...

MERGE #TempTargetTable AS target
USING #TempSourceTable AS source
ON target.ExternalId = source.ExternalId
WHEN MATCHED THEN
    UPDATE SET target.Price = source.Price,
    Action = 'update'

WHEN NOT MATCHED BY target THEN
    INSERT (InternalId, ExternalId, Price, Action)
    -- at runtime, target.InternalId is a non bindable multi part identifier when referenced here
    VALUES (target.InternalId, source.ExternalId, source.Price, 'create')

WHEN NOT MATCHED BY source THEN
     UPDATE SET Action = 'delete'

The procedure throws the classic exception - multi part identifier 'target.InternalId' could not be bound. I am aware of what this error means, but as one who is not intimately familiar with the internals of a merge statement, why is the target not accessible here? Is it possible to use target in this way?


texts: How do I Add Integration Services to existing SQL Server 2012 Install that has no internet access

texts:
Did a bunch of hunting, but I have a SQL 2012 Developer instance that's on an isolated network (no internet access, except for VPN to it). At install time we didn't install integration services, and my developer needs to deploy/configure SSIS packages.

I tried a few things, and got stuck. Part of the issue is that we appear to need some flavor of VS, and I'd assumed that I already had it per below.

Would appreciate a step by step.. keeping in mind that whatever I put on the machine can't be dependent on getting files from the internet.


texts:
I can't find an answer for this problem.

If I have in my database data of datatypes like date and timestamp. Can the change of system date affect this saved data ? I mean, can these existing timestamp and date values be changed after the change of system date ?

Thank you very much :)


texts:
I have a SQL Server 2005 database using the full recovery model. If I execute the following commands:

ALTER DATABASE test SET RECOVERY SIMPLE;
ALTER DATABASE test SET RECOVERY FULL;

or

BACKUP LOG test WITH  truncate_only;

Then try to run a log backup:

BACKUP LOG test TO  DISK = 'backupfile.bak'

I receive the error message:

  BACKUP LOG cannot be performed because there is no current database backup.

Question:

How do I check that I will not be able to take a log backup, without running a backup log command?


texts:
I am developing a hotel reservation system have come up with two approaches to model the database (MySQL).

OPTION 1

rooms

id | name | description | total_rooms

rates

id | room_id | start_date | end_date | price

reservations

id | user_id | room_id | total_rooms | checkin_date | checkout_date | total_amount

In this method, the room price can be entered through a date range (start and end), but checking for room availability is a little more complex.

However, the number of rows in the rates table is very less.

OPTION 2

rooms

id | name | description

availability

id | room_id | date | total_rooms | booked_rooms | available_rooms | price

reservations

id | user_id | room_id | checkin_date | checkout_date | total_rooms | total_price

This method involves every individual date of each room into the availability table and the table might become very big.
But I think checking for room availability and generating reports will be much easier if taking this approach.

Would like to know which method is used generally by hotel reservation systems, or if there's another different method.


texts: Table Relationships for Property Bags Configuration

texts:
I am creating a database table setup for user preferences in my application based partially on this design and while I created the tables and foreign keys, I can't seem to figure out the relationships of these tables. The attached screenshot is the DB design and below is what I currently believe are the correct relationships

user to user_preference = one-to-many

user to preference = many-to-many

user_preference to user = many-to-one

user_preference to preference = many-to-one

user_preference to allowed_preference_value = many-to-one

preference to allowed_preference_value = one-to-many


texts: MySQL replication - replicate to multiple DBs on slave?

texts:
I've got a single table in a master DB that I would like to replicate to multiple databases on one slave server. Like this:

masterDB.tableA -> slaveDB1.tableA
masterDB.tableA -> slaveDB2.tableA

slaveDB1 and slaveDB2 are on the same slave server. Is this possible?

I can get slaveDB1's replication working no problem, but it's as it it's ignoring the command in my.cnf for slaveDB2:

replicate-rewrite-db="masterDB->slaveDB1"
replicate-rewrite-db="masterDB->slaveDB2"

replicate-wild-do-table=slaveDB1.tableA%
replicate-wild-do-table=slaveDB2.tableA%

Am I missing something, or can this just not be done with MySQL replication?


texts: Inputs on handling large volumes of data for specific tables in SQL Server standard

texts:
I have large OLTP ERP database with 10 million records in few tables. The database size is as large as 10GB.
I have few critical transaction tables like this:
1. Payments
2. Cheques etc.,
and each of these tables have more than 30 columns and at least 10 million records each.

I have identity column as the primary key in all these tables and each of the tables is having the transaction date (which is as well non cluster indexed). Recently I found out that my INSERT, UPDATE and DELETE's on these tables is taking considerable time. The users are interested in accessing last 3 years of data (most likely, except for reporting). I would like to optimize this and came up with these two approaches:
1. Archiving all records and moving them to a new database for those records, which are more than 3 years old.
2. Creating staged tables for each of the years and moving the data into them and doing CRUD operations on an indexed view (with union of all the staged tables).

Since I'm not using SQL Server Enterprise I cannot create partitioned tables.

I'm thinking that the second method is appropriate and suitable than the first one since the maintenance is easy. Kindly suggest me if I'm correct. Also give your kind inputs on any other ideas.

Thanks in advance.


texts: Is there a way to grant a user access to only all non system views of database?

texts: In SQL Server, I have a user in a particular database and I've been asked to grant them access to all of the non-system views of the database only. I believe this can be done by editing securables of type view and granting select on each one, but there are many, many views. Is there a more efficient way to accomplish this?


texts:
A Oracle 12c Database has a table with Chained rows:

select
   owner              c1,
   table_name         c2,
   pct_free           c3,
   pct_used           c4,
   avg_row_len        c5,
   num_rows           c6,
   chain_cnt          c7,
   chain_cnt/num_rows c8
from dba_tables
where
owner not in ('SYS','SYSTEM')
and
table_name not in
 (select table_name from dba_tab_columns
   where
 data_type in ('RAW','LONG RAW','CLOB','BLOB','NCLOB')
 )
and
chain_cnt > 0
order by chain_cnt desc
;
RESULT:
C1  C2  C3  C4  C5  C6  C7  C8
SCHEMA_XY   PERSON  10      662 98.930  85.872  1

I understand that this table has 85.872 % Chained rows. I tried:

alter table SCHEMA_XY.PERSON move;

alter table SCHEMA_XY.PERSON move pctfre 30;

SELECT 'alter index '||OWNER||'.'||INDEX_NAME||' rebuild online ;'FROM DBA_INDEXES where TABLE_NAME='PERSON';

Chained Rows remain high. The table has 250 columns mostly varchar2 and number. How can I fix it?


texts:
I know that when we create an index then statistics are automatically created. My question is: Is it the only case when statistics are created? I remember that once I found on the internet that we have column statistics along with index statistics. Could you please enlighten me regarding this topic? When I execute the below script I get the following result:

use movies
go
EXEC sp_helpstats
@objname = '[dbo].[actors]',
@results = 'ALL';

This object does not have any statistics or indexes.


texts: How to securely store the password for an Oracle DB link?

texts:
Having a system consisting of two linked Oracle DBs, we currently have the password for the dblink stored in a table, in order to be able to change it (session issues) and to re-created the dblink in some weird cases.

Having a password in a table, however, is not a particularly good move from the security perspective. So are there any alternatives to this? Does Oracle allow safely storing the password in a keystore or vault or something? Can you create a dblink without actually retrieving the password?


texts:
Is there any option to change the sybase sql default port ?

Here I want to change the port number because we can't start two SAP instances at the same time on the same server?


texts:
Suppose you have these fields to keep track of in records: (first_name, last_name, state_id, stateid_status, day, month, year, id_status, id, criminal_status, charge, plea, sentence, create_time, update_time, hashID). If you were designing the database/table(s), would you create a single table, or would you divide the fields over multiple tables?

Why?

P.S. stateid_status, criminal_status, and id_status refer to the existence of those elements (e.g., criminal history either does or doesn't exist, the entity either does or doesn't have a state ID, etc.)


texts: Very slow query, not sure if MySQL index is being used


texts:
Can someone explain this deadlock to me? It seems as if there are 2 inserts happening within milliseconds of each other with the same Primary Key. I'm not sure how this can cause a deadlock. At most a duplicate key error. Please let me know.

   LATEST DETECTED DEADLOCK
    ------------------------
    141210 19:12:08
    *** (1) TRANSACTION:
    TRANSACTION 5D33C809, ACTIVE 1 sec inserting
    mysql tables in use 1, locked 1
    LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
    MySQL thread id 10092312, OS thread handle 0x7fbc69898700, query id 16041618913 xxxxx.net 10.x.x.x prod_user update
    INSERT INTO `shipments` (`applicant_id`, `courier_id`, `created_at`, `shipment_type_id`, `status_time`, `tracking_number`, `updated_at`) VALUES (4621087, 3, '2014-12-10 19:12:07', 1, '2014-12-10 18:42:00', '5068214830', '2014-12-10 19:12:07')
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 2536 page no 18587 n bits 552 index `index_shipments_on_ci_ai_tn_sti` of table `prod`.`shipments` trx id 5D33C809 lock_mode X locks gap before rec insert intention waiting
    Record lock, heap no 191 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
     0: len 4; hex 80000003; asc     ;;
     1: len 4; hex 80468328; asc  F (;;
     2: len 10; hex 33353032373538363034; asc 3502758604;;
     3: len 4; hex 80000001; asc     ;;
     4: len 4; hex 8044fe80; asc  D  ;;

    *** (2) TRANSACTION:
    TRANSACTION 5D33C810, ACTIVE 1 sec inserting, thread declared inside InnoDB 1
    mysql tables in use 1, locked 1
    4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
    MySQL thread id 10098192, OS thread handle 0x7fafa1434700, query id 16041619033 xxxxx.net 10.x.x.x prod_user update
    INSERT INTO `shipments` (`applicant_id`, `courier_id`, `created_at`, `shipment_type_id`, `status_time`, `tracking_number`, `updated_at`) VALUES (4621087, 3, '2014-12-10 19:12:08', 1, '2014-12-10 18:42:00', '5068214830', '2014-12-10 19:12:08')
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 2536 page no 18587 n bits 552 index `index_shipments_on_ci_ai_tn_sti` of table `prod`.`shipments` trx id 5D33C810 lock mode S locks gap before rec
    Record lock, heap no 191 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
     0: len 4; hex 80000003; asc     ;;
     1: len 4; hex 80468328; asc  F (;;
     2: len 10; hex 33353032373538363034; asc 3502758604;;
     3: len 4; hex 80000001; asc     ;;
     4: len 4; hex 8044fe80; asc  D  ;;

    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 2536 page no 18587 n bits 552 index `index_shipments_on_ci_ai_tn_sti` of table `prod`.`shipments` trx id 5D33C810 lock_mode X locks gap before rec insert intention waiting
    Record lock, heap no 191 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
     0: len 4; hex 80000003; asc     ;;
     1: len 4; hex 80468328; asc  F (;;
     2: len 10; hex 33353032373538363034; asc 3502758604;;
     3: len 4; hex 80000001; asc     ;;
     4: len 4; hex 8044fe80; asc  D  ;;

    *** WE ROLL BACK TRANSACTION (2)


texts:
I am facing an issue in connecting to SQL Server 2012 with a .net application. SQL Server 2012 database and code are deployed on the same server.

Connection string:

<add key="umbracoDbDSN"
     value="server=test.test.com;database=testumbraco;user id=testumbraco;password=testumbraco" />

Localhost:

<add key="umbracoDbDSN"
     value="server=localhost;database=testumbraco;user id=testumbraco;password=testumbraco" />

I tried same credentials with SQL Server Management Studio 2012/2014 and these are working fine locally and from remote machine.

Error:

  The network path was not found

Create a second test virtual directory:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Please advise


texts: How to upgrade PostgreSQL from 9.5 to 9.6.1 without losing data?

texts:
When I tried to run psql, I got

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

and when I checked Server.log, I saw:

FATAL: database files are incompatible with server
DETAIL: The data directory was initialized by PostgreSQL version 9.5,
        which is not compatible with this version 9.6.1.

I tried to follow this tutorial (from 9.4 to 9.5) for upgrading 9.6.1, but keep getting this error message

cannot write to log file pg_upgrade_internal.log
Failure, exiting

after running

$ pg_upgrade -v \
> -d /usr/local/var/postgres \
> -D /usr/local/var/postgres9.6.1 \
> -b /usr/local/Cellar/postgresql/9.5/bin/ \
> -B /usr/local/Cellar/postgresql/9.6.1/bin/

Does anyone have any idea what I am missing/doing wrong? Thanks!

System:

MacOS 10.12.2
PostgreSQL installed with Homebrew


texts: What happens if a table with longer varchar column is replicated into the table with shorter column in Sybase ASE?

texts:
I have a table T1 (id int, xname varchar(10)) on two dataservers, D1 and D2 (Sybase ASE 16).

"id" is unique identity column.

There is a replication set up from dataserver D1 to D2 on that table T1
I do alter table statement on D1, increasing "xname" column from varchar(10) to varchar(20)

D2 does NOT update the table to new column width

I update a row in table T1, to have xname of length 15.

What would happen?

Replication fails?
D2 row gets updated but xname is truncated to 10 characters?
else?


texts: Need explanation SELECT query on many to many relationship

texts:
I have two tables related through another (many-to-many)

This is a schema excerpt:

CREATE TABLE user (
    user_id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    email    TEXT    NOT NULL UNIQUE
);

CREATE TABLE alias (
    alias_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    address  TEXT    NOT NULL UNIQUE
);

CREATE TABLE alias_member (
    alias_id INTEGER NOT NULL,
    user_id  INTEGER NOT NULL,
    PRIMARY KEY(alias_id, user_id),
    FOREIGN KEY(alias_id) REFERENCES alias(alias_id) ON DELETE CASCADE,
    FOREIGN KEY(user_id)  REFERENCES user(user_id)   ON DELETE CASCADE
);

I have this query that satisfies my needs:

SELECT email
FROM
    user,
    alias,
    alias_member
WHERE
    user.user_id = alias_member.user_id
    AND alias.alias_id = alias_member.alias_id
    AND alias.address = 'foo@domain.tld';

A friend suggested me another query that works too but I don't get it:

SELECT
    email
FROM
    user
INNER JOIN
    (alias INNER JOIN alias_member ON alias.alias_id = alias_member.alias_id) ON user.user_id = alias_member.user_id
WHERE address='foo@domain.tld';

Would anyone explain it for me?


texts: SQL Server scheduled backup to Azure Table bacpac

texts:
I moved my database from Azure SQL to Windows Server 2008 R2 + SQL Server 2012 Web edition virtual machine on Windows Azure.

The Azure SQL provided automated backup to Azure Table as bacpac file. It was good feature for me.

Similarly, I'd like to make scheduled backup as bacpac file to Windows Azure Storage with my VM. is there any tutorial, or how can I do?

I checked SQL Backup And FTP tool, but it is not enough for me (Azure Table push is not supported).


texts: Activity Monitor (SSMS) timeout error on Sql server instance

texts:
Environment:

Window server 2012R2: RAM   = 4GB

Sql Server 2008R2: Min server memory = 0MB Max server memory = 2147483647MB

Background of problem:

I am managing database of asp.net application and recently application started to crash (when user tried to sign-in to the application; user see timeout exception on the browser).

In order to rectify the issue I set timeout =1000 in connection string of webconfig file and that resolved the issue.

When application was crashing I tried to turn on the activity monitor on Sql server instance but I coudn't start activity monitor and got following error:

My finding so far:

I started my quest and troubleshooted and found that there is memory pressure but I need expert advice if there is any link of activity monitor not starting with memory pressure? I know error is from database as Sql server was busy at the time of crash. How I can further troubleshoot the problem?


texts: Implementation of JDBC DataSource in MySQL Connector/J 8

texts:
In the MySQL Connector/J 8.0 Developer Guide (JDBC driver for MySQL), I have not found any mention of an implementation for the DataSource interface in JDBC.
To quote the DataSource Javadoc:

An alternative to the DriverManager facility, a DataSource object is the preferred means of getting a connection.

So I would expect an implementation to be provided with Connector/J. For example, for this Postgres JDBC driver, I can find the implementation of DataSource in the class PGSimpleDataSource, as discussed on Stack Overflow.
➥ Is there no implementation of DataSource for Connector/J? Or did I miss it?
There is this page, 6.1 Driver/Datasource Class Name. But, despite the title, mentions only the driver class name, not the class name for a DataSource implementation.


texts: Postgresql - Error in function that returns table

texts:
I just defined this function to return all column names from a given table:

create or replace function GET_COLUMNS(in tbl_name character varying(30))
returns table(colunas character varying) as $$
begin
    SELECT column_name
    FROM information_schema.columns
    WHERE table_schema = 'Main'
    AND table_name   = tbl_name;
end;
$$ language 'plpgsql'

But When I call it using select * from get_columns('tabfuncionarios'); I just got the following error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function get_columns(character varying) line 3 at SQL statement

I'm using postgresql 9.4.5 version


texts: what is relation between sql server back file size and space required for restoring database

texts:
I am using sql server 2008. I have taken full backup of my database. Its backup file file (.bak) is around 750 MB. Sql Server is installed on c drive which contains 5 GB free space.
When i try to restore my database it get following error :

  Restore failed for Server 'FQA2008'.
  (Microsoft.SqlServer.SmoExtended)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

  ADDITIONAL INFORMATION:

  System.Data.SqlClient.SqlError: There is insufficient free space on
  disk volume 'C:\' to create the database. The database requires
  12304515072 additional free bytes, while only 5931470848 bytes are
  available. (Microsoft.SqlServer.Smo)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&LinkId=20476

Question: I dont understand that my bak file size is just 750 MB and how does it takes 12 GB of space for restoring database?


texts: SQL server Installation - SQL server agent is enabled by default?

texts: When SQL server is installed on a machine, is the SQL Server Agent enabled and running by default, or does it need starting manually ?


texts:
I have a problem understanding a particular case of BCNF.
Let's suppose that we have a relation R(B, C, D) with df:{C->D}.
Is my relation in BCNF and if yes why???
Is C considered as superkey?


texts: Extract a table from a MySQL database using mysql/databasename folder

texts:
I am not an SQL expert, not even a novice so please excuse me if this question is naive. I have a copy of an old Linode drive where my Joomla website was hosted. I think that the Joomla database is located in "/var/lib/mysql/sitename".

I need to extract a specific table from this database but the database is not online anymore and I can not boot up from that drive anymore. What is the procedure to extract the info that I need from that database? (I know the table name from the Joomla documentation).


texts: SQL Server AG - Is there any case for un-clustering prior to certain operations?

texts:
I have a software company asking me to remove a database from a synchronous availability group prior to them running their upgrade scripts.

They are convinced that this will increase the execution time. Are their any DDL or DML queries that would take significantly longer when clustered?

What I want to know is: If I do an operation that takes 5 minutes on a non clustered database, will the same take 10 minutes in a cluster, or the same amount + some minimal overhead? I'm guessing it's not going to double or even get that close, but I was hoping for some experienced insight.


texts: How to insert very big XML data file to PostgreSQL

texts:
I'm using PostgreSQL 9.5 and trying to import XML data file to database.
To load the XML I use this function from here

CREATE OR REPLACE FUNCTION bytea_import(p_path text, p_result out bytea) as
$$
DECLARE
  l_oid oid;
BEGIN
  SELECT lo_import(p_path) INTO l_oid;
  SELECT lo_get(l_oid) INTO p_result;
  PERFORM lo_unlink(l_oid);
END;
$$
LANGUAGE plpgsql;

I successfully load data from a small XML file, but when I try a 4GB XML file,
it returns error:

SQL Error [54000]: ERROR: large object read request is too large
Where: SQL statement "SELECT lo_get(l_oid)" PL/pgSQL function
bytea_import(text) line 6 at SQL statement.

It seems lo_get cannot read/extract very large data.
It's said that since version 9.4, PostgreSQL can handle up to 4TB, so how can I import very large XML data file to PostgreSQL 9.5?

Thanks in advance


texts: 10+ minute query time with DATEPART in WHERE clause

texts:
I have a list of records with datetimes over more than 1 year. My goal is to compare the count of today's records with the count of records from last year, where the week # and day of the week from last year's search match today's week # and day of the week.

For example: if today is 07-26-2016, then the week number is 31, and the day is Tuesday (3). Then I would be searching for week number 31, Tuesday (3) of 2015. This day is 07-28-2015.

My problem is that, if I use a WHERE clause with the date in form '07-28-2015', the query completes in ~30 seconds. But if I use a WHERE clause with DATEPARTs for year, week, and day of week, my query takes > 10 minutes.

Here are the two queries to compare:

first, with the full date in the WHERE clause. This takes 30 seconds

SELECT COUNT(*)
FROM my_table
WHERE CAST(my_datetime AS DATE) = '2015-07-28'

now, with the DATEPARTs. This takes 10+ minutes

SELECT COUNT(*)
FROM my_table
WHERE DATEPART(yy,CAST(my_datetime AS DATE)) = 2015
  AND DATEPART(wk,CAST(my_datetime AS DATE)) = 31
  AND DATEPART(dw,CAST(my_datetime AS DATE)) = 3

I have also tried the second query without casting my_datetime as DATE, and it is just as slow. They DO work, and I have compared the results from the two, but it is necessary that I use the second query so that I can replace the static values (2015, 31, 3) with

DATEPART(yy,GETDATE())-1 / DATEPART(yy,CAST(GETDATE() AS DATE))-1
DATEPART(wk,GETDATE()) / DATEPART(wk,CAST(GETDATE() AS DATE))
DATEPART(dw,GETDATE()) / DATEPART(dw,CAST(GETDATE() AS DATE))

That way, the query will always give me results for  last year's corresponding date.

How do I optimize this query to be faster? Or am I approaching this incorrectly?


texts: Avoiding MySQL Fabric to become a single point of failure

texts:
I managed to install and configure MySQL Fabric that manages multiple MySQL Server nodes. It works very well since I tested it by connecting to the Master node directly and played with the data (insert, delete). The slave nodes become identical to the master.

Now, the fabric server should manage everything. Hence, the application should connect to the Fabric and not to the master node. This way, when the Master fails, Fabric will nominate a new Slave.

Q1: How can I avoid Fabric server itself from becoming a single point of failure?

Q2: Anyone has experience implementing Fabric? Is what I am planning to do is the best practice?

Regards,


texts:
OS: Windows 10 Enterprise 64bit

SQL Server: Microsoft SQL Server 2019 (RC1)

SQL Server Version: Developer Edition

I'm unable to start the SQL Server service due to the following error, "SQL Server evaluation period has expired".  What's strange about this is I'm already using SQL Server Developer edition.  Can anyone make sense of this?

Below are screenshots of the error and proof of my version.

Version:

ERROR

SSCM:

Event Viewer:

Log File:


texts:
On a mysql table with about 1.7M rows, I tried to delete duplicates posts:

delete a FROM comment a
  INNER JOIN comment a2
     WHERE a.id < a2.id
     AND   a.body = a2.body;

The result was:

  Query OK, 35071 rows affected (5 hours 36 min 48.79 sec)

The table schema:

CREATE TABLE `comment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `author_id` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `title` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `body` longtext COLLATE utf8_unicode_ci NOT NULL,
  `post_id` int(11) NOT NULL,
  `published` tinyint(1) NOT NULL,
  `cid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_postid_idx` (`id`,`post_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1774682 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

This happened on my almost idle workstation with Intel(R) Core(TM) i7-4790 CPU @ 3.60GHz.
I'm wondering if there are some tricks to make this delete operation faster?


texts:
I followed Barman's document to setup a primary(backup) server and a passive server:

http://docs.pgbarman.org/release/2.10/

node1: backup server(10.0.1.1)
node2: passive server(10.0.1.2)

On node2, I set /etc/barman.d/passive-server.conf as:

[passive]
description =  "Barman passive server"
primary_ssh_command = ssh barman@backup

Then switch to barman user to test:

-bash-4.2$ barman sync-backup
usage: barman sync-backup [-h] server_name backup_id
barman sync-backup: error: too few arguments

Because I set PostgreSQL server info on the node1 as:

/etc/barman.d/app_db-server.conf

[app_db]
description = "app_db PostgreSQL server"
conninfo = host=app_db user=barman dbname=postgres
streaming_conninfo = host=app_db user=streaming_barman dbname=postgres
backup_method = postgres
streaming_archiver = on
slot_name = app_db

But didn't config /etc/barman.d/passive-server.conf on the node1.

I tried to run on node2 again:

-bash-4.2$ barman sync-backup app_db 20200311T121411
ERROR: server app_db is not passive

If a I rm barman cron on node1, I found this directory has been created and some data was there on node2:

$ sudo ls /var/lib/barman/
app_db streaming-app_db

However, in the app_db folder, only 2 folders found: streaming  wals. In the streaming folder, there is nothing.

Is the usage wrong?


texts: Should one always VACUUM ANALYZE before REINDEXing in PostgreSQL 8.4?

texts:
Early in the morning every day a pgAgent job refreshes the contents of table A from table B on my PostgreSQL 8.4 database. Table A contains around 140k records across 91 columns and has two indexes - one as part of the PRIMARY KEY and the other a GIST index on a POINT PostGIS geometry column.

To make the process go a little faster the job drops the index on the geometry column, before deleting the records in the table A and inserting the records from table B, then the index is recreated. This all being done the autovacuum daemon gets to work when it feels like it (after ten minutes or so from comparing the job stats and table stats for the job completion time and autovacuum run time).

Upon checking on the table this morning after all this had happened the table stats told me the table size was 272MB, the TOAST table size was 8192bytes, and the index size was 23MB. This seemed quite large so I issued a REINDEX command on the table and the index size came down to 9832kB.

My question(s) is this:

Why does the REINDEX apparently reduce the size of the indexes so much when the indexes (or at least the geometry column index) have been built anew from scratch? Should I make sure that the table has been vacuumed/analyzed before the indexes are built? Is not dropping the index on the primary key a factor in this? What am I missing?


texts: Control InnoDB buffer pool allocation in MySQL (5.7+)

texts:
I have some large tables which causes to fill the InnoDB buffer pool and removing other data.

Is there any way how can I control the InnoDB buffer allocation in one shared database?

Like:
a) Limit the buffer size for one table
b) Limit the buffer size for data (so indexes will remain cached)
c) Allocate a buffer exclusively for one particular table or even for one particular table index (or for all indexes in one table)


texts:
I'm trying to make a table to store the edges of a directed graph, but want to avoid duplicate edges in both directions.
I can of course make it one direction :

CREATE TABLE edge (parent integer, child integer, UNIQUE(parent, child));

But how can I make sure that I also don't allow a new (parent, child) that already exists as (child, parent) ?

Any ideas would be greatly appreciated !


texts: PGSQL: Which is faster, many constraints or a single trigger?

texts:
I have a table that currently has 12 check constraints. I'm making some updates and I'm wondering if this is the best approach. I see three options:

Keep using the 12 constraints. They're a bit difficult to understand, but definitely possible.
Craft one massive constraint that no feeble human mind could ever understand or maintain.
Use an insert or update trigger to validate contents. This is a dream to implement and maintain.

I'm aware that a trigger will not validate existing rows. For this use case, that is acceptable.

Obviously, I like #3, but I'd some like input regarding performance of inserts and updates before actually implementing anything.


texts: Storing created/retired dates for a large amount of different entities

texts:
In the db I'm working on now almost every entity has these 4 columns:

CreatedDate
CreatedBy
RetiredDate
RetiredBy

Usually this is used for logging purposes, and for some of the entities the usefulness of knowing when it was retired is arguable (but don't tell my boss). For some other things, (like a truck) it makes more sense because a "retired" vehicle may come back into service.

Anyway, I was wondering if it's a good idea to stick this information into one table since it's repeated all over the place. If it is, anyone have a good name for it..? Simply created_retired_dates?


texts: How do you monitor specific SQL statement of a specific user ?

texts:
We have a few standalone users here which cause high server loads for several 11.1.0.7 Oracle's.
I spoke with them a few times but it seems that their queries are causing high loads and probably bottlenecks

Is there any option in Oracle to monitor or see which SQLs they running ? I'm not talking about only the ones which cause high loads, although they're the most important ones I would like to see all of their queries.


texts: Delete duplicate rows that have same non-unique values in MySQL InnoDB

texts:
How do I drop rows that have the same values in the column title? I do have a unique hash for all the rows, but they seem to have failed me anyway. As an added question, If my row_id is all over the place, can I reset it to start from 1 and re-count up to whatever number of entries that I have?

Thanks.

EDIT: Here is my CREATE TABLE:

CREATE TABLE `news_archive` (
  `rowid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hash` char(32) NOT NULL,
  `source` varchar(45) DEFAULT NULL,
  `title` text NOT NULL,
  `rsslink` text NOT NULL,
  `timeadded` datetime DEFAULT NULL,
  `tags` varchar(255) DEFAULT NULL,
  `imglink` text,
  `author` text,
  `filed` varchar(45) DEFAULT NULL,
  `added` tinyint(1) unsigned DEFAULT NULL,
  `priority` tinyint(3) unsigned DEFAULT NULL,
  `timepublished` varchar(45) DEFAULT NULL,
  `content` mediumtext,
  `polarity` decimal(4,2) DEFAULT NULL,
  `subjectivity` decimal(4,2) DEFAULT NULL,
  `notes` mediumtext,
  PRIMARY KEY (`rowid`,`hash`),
  UNIQUE KEY `hash_UNIQUE` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=591601 DEFAULT CHARSET=utf8;


texts: How to restore a database if full database is corrupt?

texts:
I have the following strategy for my SQL Server Database:

Full backup on Sunday at 1 PM
Differential backups daily from Monday to Saturday at 1 PM
Transaction log backups daily every 3 hours

Now my last full backup got corrupt. In this case how can I restore my database?

Can I use Full backup that was taken before the corrupt full backup i.e two weeks before?


texts: SSIS script task - Execute Dataflow task based on variable value

texts:
I have been trying to create a package which executes a couple of dataflow tasks based on the available value in one of the variables. How can I achieve this?

The sample scenario: Variable param may have a string value "current" or "previous". If the user selects current then the data flow task A will get executed, else data flow task B will get executed.

I have two variables : 1) Param and 2) ParamValue. Using the script task If param=="current" then paramvalue would be 1, else if param=="previous" then paramvalue would be "Previous"

And in the precedence constraint added conditions to execute the data flow tasks. But its not working as expected.


texts: Memory threshold is attempted on SQL Server

texts:
Currently we have a monitoring for all the infrastructure (server, DB, network components, etc ...). The alert thresholds, usually saturation, are configured as follows:

90% : Warning
95% : Critical

Once the threshold is reached, an alert is generated and sent as an email or via a ticketing solution.
To be more concrete for our case, we have a server with the following config:

Server memory : 8 Gb
SQL max memory : 7 Gb

Once SQL reaches the max memory, if we also add OS processes, the threshold of 90% is reached, then an alert is generated.
Among the following solutions to avoid the alert, which is the most optimal:

Physical memory extension
Modification of the alert threshold from 90% to 95%
Empty the unused SQL chase (if possible)
Decreasing SQL max memory

Otherwise if you have other solutions do not hesitate to share it.


texts: What does "ERROR: failed to find parent tuple for heap-only tuple at (1192248,5) in table "fruits"" mean?

texts:
After being bitten by the bug mentioned in the release notes of 9.3.4, I have realized that a very small part of my data is corrupt, after doing a failover. The bug showed it self as duplicate rows, with the same primary key.
Now I can easily afford to delete some data from my database if that’s what it takes to fix my problem, so I just deleted those duplicates whole hug. After doing this, I decided to write a script that drops/recreates all constraints (Pkeys and Fkeys), in order to validate that no more of my data is corrupt. This worked well, until I bumped into the following error, while creating one of the foreign keys:

ERROR:  failed to find parent tuple for heap-only tuple at (1192248,5) in table "fruits"
CONTEXT:  SQL statement "ALTER TABLE "pm"."fruits" ADD CONSTRAINT "fruits_pkey" PRIMARY KEY (id)"

What is it, and how do I solve it?


texts:
I have a user that has his Active Directory logon set to allow logging into a specific SQL Server, and that works fine.  This user is also part of an AD Group that has access to a specific database on this server. However, this membership doesn't grant him access to that specific database. I have to give his AD logon access to the database separately.

Question: Is there a conflict when granting SQL Server access via a user's AD login, and also adding permissions to a specific database on the SQL Server by the user's membership in a specific AD group?

If there is anyway to set this up to work let me know.


texts:
is there any way to determine from server statistics* running MySQL, how much traffic(bits per second) WOULD replication consume between 2 servers, without setting-up the actual replication between those 2 servers ?

*Statistics = mysql commands over time, Disk IO per second, traffic, ram cpu...(e.g. CollectD rrd data)

Thank you. :)


texts: Fill a GTT with external data each session

texts:
I have to import an external csv file into a table in database each day. The file is encoded in 'UCS2-LE BOM'. I have found a solution using external tables and bulk insertion, but the problem is to re-use the GTT made as an external table to be filled by the new daily file, each day in a job.

Since the GTT already exists from previous session, I can't use CREATE GLOBAL TEMPORARY TABLE ... ORGANIZATION EXTERNAL .... I can however drop the table each time and re create it but I think there has to be a way I'm not aware of.

Additionally, It is much appreciated if the importing could be done entirely without the need for GTTs and loading a file straight into the destination table. I have done that before with custom tagged hierarchial XML files (Oracle is so powerful in XML files) but couldn't find many resources and references on csv files, since.

Thanks!

Edit:

GTT's can't be used as external tables

Error report -
SQL Error: ORA-30658: attempt was made to create a temporary table with EXTERNAL organization
30658.0000 -  "attempt was made to create a temporary table with EXTERNAL organization"
*Cause:    An attempt was made to create an External Organized Temporary table.
           This is not supported.
*Action:   Don't do that!

Edit2:

I found out that organization external doesn't create the table though it says on the log it does. The table is parsed each time from the blueprint defined in organization external on access.


texts: Format a value from other values in the table

texts:
I have identified a location for data integrity problems in a design for a database I'm planning on building. I have lots of two way connections between maps in a game. These connections cause from 2 to 8 duplicate strings per connection. Due to this I have decided to add a names table.

As an example we have two locations Beach and Town, to move between them you need to move into the "Beach to Town" or "Town to Beach" entity.
As I'm defining regions with a tree / graph like structure, there would be five regions in total. The four described earlier. And "Beach - Town Path" which is the parent to both "Beach to Town" and "Town to Beach".

In a table this would look like:

ID  NAME
1   Beach
2   Town
3   Beach to Town
4   Town to Beach
5   Beach - Town Path

I can solve this outside of SQL easily with Python. However I would prefer this logic to be inside the database.

import string

formatter = string.Formatter()

names = {
    1: "Beach",
    2: "Town",
    3: "{names[1]} to {names[2]}",
    4: "{names[2]} to {names[1]}",
    5: "{names[1]} - {names[2]} Path",
}

def get_name(id: int) -> str:
    name = names[id]
    while True:
        segments = list(formatter.parse(name))
        if len(segments) == 1 and segments[0][1] is None:
            break
        name = name.format(names=names)
    return name

print(get_name(1))  # Beach
print(get_name(2))  # Town
print(get_name(3))  # Beach to Town
print(get_name(4))  # Town to Beach
print(get_name(5))  # Beach - Town Path

How this works:

I have the table imported into Python. (I have used a literal for brevity)
We get the name from the table.

name = names[3]
# name = "{names[1]} to {names[2]}"

We check if the name has segments to be formatted.

segments = list(formatter.parse(name))
# segments = [('', 'names[1]', '', None), (' to ', 'names[2]', '', None)]
if len(segments) == 1 and segments[0][1] is None:
# if False:

We format the name with the linked names.

name = name.format(names=names)
# name = Beach to Town

We check if the name has segments to be formatted. Since it has we break out of the loop.

segments = list(formatter.parse(name))
# segments = [('Beach to Town', None, None, None)]
if len(segments) == 1 and segments[0][1] is None:
# if True:
    break

Return the desired name.

return name
# return "Beach to Town"

Back to SQL. This is an example table containing more examples:

ID NAME
1  foo
2  bar
3  {names[1]} - {names[2]}
4  {names[1]} -
5  {names[4]}{names[2]}

Whilst I can just use the exact code above and get the desired output.
Is it possible to use SQL with something like the following to get the desired output?

SELECT name FROM names WHERE id = 1 -- "foo"
SELECT name FROM names WHERE id = 2 -- "bar"
SELECT name FROM names WHERE id = 3 -- "foo - bar"
SELECT name FROM names WHERE id = 4 -- "foo - "
SELECT name FROM names WHERE id = 5 -- "foo - bar"

There are no limit on how the data is stored or retrieved, except that duplicate names are not allowed and the parsing must happen in SQL.


texts: The problem having different vms with the same sql server Licence key

texts:
I was hired in a new company 1 year ago. After doing all important stuff, i'm now hunting for problems and etc.
Using a powershell that can give me the licence key using to install the SQL Server, I could find that we have 5 different vms, using the same SQL Server 2012 with the same licence key.
probably the old people here just used the same EXE and the same KEY to install all sql servers in all vms created because they didn't know about it ( just clicking next next next next)
is there a way to fix this and is it possible to edit to elicence key to use a valid one ( a diferent one for each instance on each vm?


texts: Powershell SQL Server SMO - Failed to Enumerate Collection error

texts:
I have a weird problem with PowerShell and the Invoke-Sqlcmd when used in a ForEach block. In an nutshell, I've done this before many times and not really seen this.

I have a list of SQL Server instances in a database table, let's call it inst_list, and lets say it contains 3 instances: Inst1, Inst2 and Inst3. I'm populating an array called $instances, like this:

$instances = @(Invoke-Sqlcmd -serverinstance $myserver `
-database $mydatabase -query "select name from inst_list") |
select-object -expand name

Then in a ForEach block, I'm enumerating over the list and running Invoke-Sqlcmd, like this:

 foreach ($instance in $instances)
{
    Invoke-Sqlcmd -serverinstance $instance -database master -query "SELECT 1"
}

Simple enough right? However, I'm getting a Failed to Enumerate Collection error output, and alongside that another error which is Can't Connect to Server Inst1 Inst2 Inst3.

It's as if it's seeing my variable $instances as one large, long string than as a collection of instances to loop over.


texts: On PostgreSQL 9.5, does partitioning a table prevent autovacuum from automatically running ANALYZE?

texts:
I'm trying to grasp the following sentence from https://www.postgresql.org/docs/9.5/static/sql-analyze.html

  The autovacuum daemon, however, will only consider inserts or updates on the parent table itself
  when deciding whether to trigger an automatic analyze for that table. If that table is rarely inserted
  into or updated, the inheritance statistics will not be up to date unless you run ANALYZE manually.

Since partitioning is usually implemented through inheritance, initially, I thought this sentence implies that, being the master table empty in a partitioned table, autovacuum would never trigger an automatic ANALYZE on the whole partitioned table (including all children) leaving the planner with no statistics at all for the partitions.

On the other hand, "that table" seems to refer only to the parent table (master).  If this second reading is correct, autovacuum does trigger ANALYZE automatically on the children tables and, obviously, ignores the parent, empty table. There would be no need to run ANALYZE manually in this case.

Which of the two interpretations is the right one?


texts: Mysql Connector C++ statement values question

texts:
I just want to know, if a value, like an int for example, is actually converted into a string before being sent as part of the command.

pstmt = con->prepareStatement("INSERT INTO test(id) VALUES (?)");

Usually, the ? is replaced with:

pstmt->setInt(1,155);

What I'm curious about is: could I simply make the command:

pstmt = con->prepareStatement("INSERT INTO test(id) VALUES (155)");

And execute it? Would it be the same as setInt? (basically that would mean that the int value is converted to a string before being set as part of the command)


texts: Forbid mongodb not authenticated connect

texts:
If to run mongo with authorization (i.e mongod --auth) it still allows to connect using client mongo and get to mongo cli where one will get refused to execute any command.

But I wonder if it is possible to close such connection for the unauthenticated user so one does not see mongo cli at all?


texts: how to get complementary information from sp_readerrorlog

texts:
when auditing what are the failing logins in one of my server, using the sp_readerrorlog procedure I get the following result, when running the following query:

 EXEC sp_readerrorlog 0, 1, 'Login failed'

it shows me the IP address of the servers where from the login was attempted.

I know that ping -a resolve ip addresses to hostnames

any ideas, even vague ones, or indications, as how I could get to those hostnames using T-SQL?

lets, say I can get to the ip address and put it into a variable. how then could I go and do a magic and get to the hostname for that particular ip address?


texts: mySql Relationships / Index - Checking for duplicates

texts:
I have this setup on innoDB:

TblUser
user details

TblConnection
User Connection

TblConnection also has these indexes: User, Connection where User is the foreign key of TblUser.id and another index: Connection, User where Connection is also a foregin key to TblUser.id

Now I want to make sure that when adding a new row I have complete uniqueness between column combinations. By this I mean I want that 1,2 is treated the same as 2,1 and therefore an INSERT is not made.


texts: What platform for a large record based mathematical database?

texts:
Hi I am a civil engineer with some experience of programming but I am not familiar with the vast range of options available today. Hope you could give me any pointers the best way to proceed.

I want to make and query a database of ground level survey measurements in a grid format. There will be many measurements for each grid location at various times through the earthmoving job so there is a 4th dimension of time.

The observations will most likely be read in from a text file. In each record there will be an (2 x integer) grid position (row and column) a (floating point) ground level and various string information codes (maybe up to 30 characters in total).

The grids could be about 10000 rows x 10000 columns. Not every location on the grid would be have a record in each survey, but they would typically have up to a hundred records. A lot of the grid locations would have no records at all (the site will not be perfectly rectangular).

I want to search the records, extract data and do calculations eg calculate the lowest or highest ground level for each grid location.
I am fairly confident I would have the ability to program this fairly simply in a language like FORTRAN, BASIC or C using arrays. A lot of the array elements would be empty though and I am guessing that this is not the right way to do it though and big databases like this need special tools that I will have to learn how to use.

I am thinking of possible options for the platform -

Use a database program. I am not familiar with how powerful these can be but I imagine they would have a lot of overhead with GUI.
Use SQL? This I don't know much about but it seems to be the language for databases. I have always used imperative languages rather than declarative and as I understand from wikipedia that SQL is declarative I am a bit nervous of the change. I don't fully understand the process for using it. Is there a compiler that makes console programs? Is the database stored on disk? Sorry for such stupid questions.
Use an API like c-treeACE? I think this may be the way to go offering me the familiarity of a "do this, then do that" language (unfortunately this is the way I think as an engineer!). But I am hoping that the behind the scenes memory and processing management offered by the API would be superior to what I could achieve with huge arrays.
Or could i do it with an object orientated language and let the computer worry about the storage requirements. eg if i stored the records as objects with methods and properties that would help me get the results I need out of each record - would it be a huge bloated program compared to 3)

There are likely to be hundreds of millions of records and I want to be able to query and process them in minutes not hours (preferably seconds!) on a modern PC running windows.
To be more specific mine is an i7 processor with 6Gb ram and 120Gb SSD running Windows 7 64 bit.

Hope someone has time to share a couple of words of wisdom with a newbie.


texts: How do I store more than 15,000 Japanese characters in a column?

texts: I want to store more than 15,000 characters of Japanese text in a SQL Server 2008 database. We have a front end tool that will display this text.


texts: Troubleshooting a slow performing encrypted stored procedure

texts:
I have a stored procedure which is part of a vendor supplied system. The stored procedure is encrypted so we can't see the code.

The stored procedure has all of a sudden started to run slow.

The first thing I would look to do is look at the execution plan to try to work out where the problem is, however, there doesn't seem to be a way to show the plan (sp_Whoisactive is NULL in the query_plan field)

I might also try to update statistics but I have no way of seeing what tables the stored procedure uses.

The wait stats for the query do not show anything obvious

Is it realistically possible to do any sort of troubleshooting on this query without having an un-encrypted version?


texts:
I'm running a master-master replication with read-only second master. Now since my binary logs are not getting replicated in my passive master.

Should I need to enable --log-slave-updates? If I enable this, will I be able to take binary log backups from my passive master? If I enable it in passive master, I should not enable the same on active master right?

If I enable --log-slave-updates in my active master also, consider the case of a disaster where in my primary master has a issue, then I will make my passive master the active one and point my application to my new master. Now my new master will be generating binary logs which will be waiting to replicate to my actual master (which is down now). SO what happens when my actual master comes up? Binary of my secondary master gets copied to my primary without any issues?

While enabling --log-slave-updates, should I have attn on any other parameters as well.?

Thanks!


texts: Check if there are users who are using a common password

texts:
Table Structure:
userid password adminlevel

userid is BIGINT (UNIQUE)
password is VARCHAR
adminlevel is SMALLINT

How do I write a query to get a list of users with adminlevel greater than 0 whose password is being used on other accounts?

The query result must satisfy the following conditions:

adminlevel > 0
there are accounts other than itself where the same password is being used


texts:
I'm fairly new to databases, but I'm beating my against the wall trying to do something that (I believe) should be fairly trivial.

I'm using SQL Server Management Studio 2012 and am trying to delete all data in my database. This is not a production database, and I am the only one with access. I've got several relations between tables, so after reading some it seems like a DROP and CREATE should be the easiest route to get around the fact that I have foreign keys. So, using "Tasks -> Generate Scripts" and generating a "DROP and CREATE" script, I keep getting the following error (and the script endlessly executes):

  Msg 3702, Level 16, State 4, Line 2
  Cannot drop database "emcb_schema_v1_0_7" because it is currently in use.
  Msg 1801, Level 16, State 3, Line 2
  Database 'emcb_schema_v1_0_7' already exists. Choose a different database name.

I've made sure that I run the script from master, so what gives? In my frustration, I'm blaming Microsoft for making this such a difficult task, but it could easily be an operator error...


texts: MySQL Error 111 caused by remote connection

texts:
This database has been running for years, such that all connections have came from the localhost address.  Recently, I have been using xampp to develop new software, and have been connecting to the database from my own machine without any immediate issues.

However, after several hours of making connections from my own machine, the MySQL server stops working altogether regardless of if I am connecting from my own machine or from the server.  This only happens AFTER successfully making connections from my own machine for several hours, or sometimes days.  I get the following message when trying to login via PHPMyAdmin:

Lost connection to MySQL server at 'reading initial communication packet', system error: 111

And the following message when trying to visit previously functional pages:

Warning: mysqli::mysqli(): (HY000/2003): Can't connect to MySQL server on '...' (111) in '...'

And the following when connecting via my own machine:

allSQLSTATE[HY000] [2002] No connection could be made because the target machine actively refused it.

Unfortunately, the client is on GoDaddy Shared Hosting and I don't have access to the my.cnf.  This problem has worked itself out if I just wait several hours or days before, but I do need to avoid it in the future.

Thanks.  I am not very experienced with database admin work, so my apologies if this is a silly question.


texts: MySQL replication between VPS and shared host

texts:
I have a VPS where I have ssh access and shared host with CPanel. Both have MySQL.
I need to create a master/slave setup among the two server.
As per this tutorial: How To Set Up Database Replication In MySQL, I will need to modify the my.conf file.
Is it possible to achieve this with the following permissions on both the servers?

VPS
I have root access with ssh login where I can modify /etc/my.conf file.

Shared host
Cpanel with phpmyadmin.


texts: SQL Server: Has anyone used the large-page allocations tuning option?

texts:
Has anyone used the tuning option of using TF834 large page allocations. I was just reading an MS article on it and was wondering if anybody had used it and seen performance gains. Are there any things to watch out for, tips, pitfalls?

The server is a Windows 2008 64 bit, 128 GB RAM, 4 CPU 8 core hyperthreded (total 64 cores)  SQL2005 server. I am looking to tune the server to better use its specs than just using the default SQL installation that is currently done on it. Any extra tips will be welcomed.


texts:
CREATE OR REPLACE PROCEDURE ProcedureX()
    TYPE AVERAGE_COUNT_TBL IS TABLE OF INTEGER;
    AVERAGE_COUNT AVERAGE_COUNT_TBL;
BEGIN
    INSERT INTO AVERAGE_COUNT
    VALUES(10);
END ProcedureX;

It throws the error:
    PL/SQL: ORA-00942: table or view does not exist

What is the problem?


texts: Why are transactions running as distributed (DTCXact)?

texts:
I am actually trying to analyze and solve some regularly occurring deadlocks that all include at least one transaction named "DTCXact", this makes me ask.

I repetitively run the following query against production servers:

SELECT  DTAT.transaction_id ,
    DTAT.[name] ,
    DTAT.transaction_begin_time ,
    CASE DTAT.transaction_type
      WHEN 1 THEN 'Read/write'
      WHEN 2 THEN 'Read-only'
      WHEN 3 THEN 'System'
      WHEN 4 THEN 'Distributed'
    END AS transaction_type ,
    CASE DTAT.transaction_state
      WHEN 0 THEN 'Not fully initialized'
      WHEN 1 THEN 'Initialized, not started'
      WHEN 2 THEN 'Active'
      WHEN 3 THEN 'Ended' -- only applies to read-only transactions
      WHEN 4 THEN 'Commit initiated'-- distributed transactions only
      WHEN 5 THEN 'Prepared, awaiting resolution'
      WHEN 6 THEN 'Committed'
      WHEN 7 THEN 'Rolling back'
      WHEN 8 THEN 'Rolled back'
    END AS transaction_state ,
    CASE DTAT.dtc_state
      WHEN 1 THEN 'Active'
      WHEN 2 THEN 'Prepared'
      WHEN 3 THEN 'Committed'
      WHEN 4 THEN 'Aborted'
      WHEN 5 THEN 'Recovered'
    END AS dtc_state
FROM    sys.dm_tran_active_transactions DTAT
        INNER JOIN sys.dm_tran_session_transactions DTST
                         ON DTAT.transaction_id = DTST.transaction_id
WHERE   [DTST].[is_user_transaction] = 1
ORDER BY DTAT.transaction_begin_time

It always shows results similar to this sample:

  2414848764;DTCXact;2016-01-28
  10:24:41.983;Distributed;Committed;Committed
  2414896908;DTCXact;2016-01-28
  10:26:05.847;Distributed;Committed;Committed
  2414903917;DTCXact;2016-01-28
  10:26:29.017;Distributed;Committed;Committed
  2414918503;user_transaction;2016-01-28
  10:27:06.823;Read/write;Active;NULL 2414918551;DTCXact;2016-01-28
  10:27:06.973;Distributed;Committed;Committed

This result shows that most transactions run as distributed. I really don't know why at all. Some of the servers are clustered with two nodes. Others are not. Their results are similar.

Facts:

The MSDTC Service is running on all SQL-Servers. None of the SQL-Servers has any Linked Servers.
remote trans proc in sys.configurations is set to 0.
I found no explicit Begin DISTRIBUTED Transaction or SET REMOTE_PROC_TRANSACTIONS usage in code
no definition / usage of TransactionScope in Code
Found a class described as follows: //COM+ Transaction mgr using "Service without Components" model built according to System.Transactions.TransactionScope (NET 2). I don't know what this means.

The asp.net web application causing those transactions usually always reads and writes data from one and the same database on one and the same SQL-Server. There MAYBE some very few exceptions, especially running during nighttime copying some data around, but I would not expect to see the MAJORITY of transactions being distributed the whole day long.

I traced the actios of a SPID that I found was running distributed transactions:

Here is a sample result of tracing: I see dozends of RPC:Completed Events in a row followed by 2 DTCTransaction Events (1st: EventSubClass="Enlisting in a DTC transaction", 2nd EventSubclass= "Propagate Transaction").
All target the same DB. No TM:Promote Tran xxx Events happen.

I found out that those queries are running on the IIS (where the Web Application is hosted) in a .net Windows Service, that uses the same database as well and executes jobs. May this 2nd application cause DTC??

I searched around the web without finding similar questions or any answers. So I wonder what could be the reason for this? Or what knowledge do I miss to understand this?


texts: DB_NAME(database_id) return null when executed through linked server

texts:
I am trying to execute below query :

SELECT DB_NAME(Database_id) FROM [linked_servername].MASTER.SYS.DATABASE_MIRRORING
WHERE database_id > 4 and mirroring_role = 1

But I am getting Result as NULL means database name is null.


texts: What happens when the lock limit is reached?

texts:
My question is about the default isolation level: "read committed".
As I understand, during a read committed transaction, when we update a record, the system puts a lock on that record, so other transactions would wait.
Is this correct?

Other: and there's a limit in the config file for the maximum number of locks in a transaction: max_locks_per_transaction.

What happens when that limit is reached? I made a test and nothing happened. Does the system exchange the row-level locks for a full-table lock?


texts: SQL Server MAXDOP and TempDB Recommendations for VMs

texts:
When configuring SQL Server on a VM does the recommendation around tempDb and MAXDOP still stand true?

TempDB - As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors.

MAXDOP - Server with multiple NUMA nodes > Less than 8 logical processors > Keep MAXDOP at or below # of logical processors.

Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server

I have a VM with the following and 2016 SP1 installed so my intention is to config 2 TempDb files and set MAXDOP = 2?


texts: Disable nestloops into the Postgresql JDBC connection

texts:
I'd like to disable nested loops for a specific JDBC connection string (batch java application), using the PostgreSQL community driver

Is it possible to do so?

I tried this way, in vain:

jdbc:postgresql://xxx.yyy.zzz:5432/mydb?options=enable_nestloop=off


texts:
I use PostgreSQL 11 and have two columns. One is unix timestamp which is not aware of leap seconds and an utc timestamp which is aware of leap seconds.

Is there a way either to find the number of leap seconds between an unix timestamp (without leap Seconds) and an utc timestamp (with leap seconds)?

Alternatively is there a way to convert an utc timestamp (with leap seconds) into an unix timestamp (without leap seconds)?


texts: In MySQL User table and Login history log table with Left join gives an error for contains nonaggregated column

texts:
I am trying to get all user information data that is on the USER table and also only the user's last login information(which is on another table called LOGIN_HISTORY).
I tried with Left join but MySQL is giving me the error which shows "Expression #15 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'login_history.user_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"
Here is the USER table

Please help!
and this is my LOGIN_HISTORY table.

The output which I want is

I have tried this query, I don't know what I am missing but it is not giving me the desired output. I only want all the user information with his LAST login_datetime column. I tried the below query but it is showing all the user records with duplicate and all the login_datetime.
I tried:
select u.id, u.firstname, u.lastname, u.email, u.username, u.password, lh.login_datetime
from user u
left join login_history lh on u.id = lh.user_id
group by u.id
order by DATE(lh.login_history) desc;`

Please help!


texts: I have been given a homework task where I am to create an ER model of a database from a set of client requirements. Another part of this task is to create a diagram that shows the scope and boundary of the database system I create. I am having problems creating a scope/boundary diagram as I have been unable to find any examples in my textbooks or on-line. Is anyone able to suggest or provide an example of how this could be done?


texts: 02270. 00000 - "no matching unique or primary key for this column-list"

texts:
For some reason I keep coming up with the 02270 error. However I'm not sure what's wrong with it. All I know is that the problem lies with the room table and the conferencesession table.

CREATE TABLE BUILDING (
BUILDINGNO CHAR(2),
BUILDINGWING VARCHAR2(15),
BUILDINGLANE VARCHAR2(15),
CONSTRAINT BUILDING_PK PRIMARY KEY(BUILDINGNO));

CREATE TABLE SPEAKER (
SPEAKERID CHAR(2),
SPEAKERNAME VARCHAR2(20),
SPEAKERADDRESS VARCHAR2(50),
SPEAKERPHONE NUMBER(12),
CONSTRAINT SPEAKER_PK PRIMARY KEY (SPEAKERID));

CREATE TABLE ROOM (
BUILDINGNO CHAR(2),
ROOMNO CHAR(2),
ROOMCAPACITY NUMBER(3),
CONSTRAINT ROOM_PK PRIMARY KEY(ROOMNO,BUILDINGNO),
CONSTRAINT ROOM_FK1 FOREIGN KEY(BUILDINGNO)REFERENCES BUILDING(BUILDINGNO));

CREATE TABLE CONFERENCESESSION (
SESSIONID CHAR(4),
BUILDINGNO CHAR(2),
ROOMNO CHAR(2),
SPEAKERID CHAR(2),
SESSIONDATE DATE,
SESSIONPRICE NUMBER(4,2),
CONSTRAINT CONFERENCESESSION_PK PRIMARY KEY(SESSIONID),
CONSTRAINT CONFERENCESESSION_FK1 FOREIGN KEY(BUILDINGNO) REFERENCES
BUILDING(BUILDINGNO),
CONSTRAINT CONFERENCESESSION_FK2 FOREIGN KEY(ROOMNO) REFERENCES ROOM(ROOMNO),
CONSTRAINT CONFERENCESESSION_FK3 FOREIGN KEY(SPEAKERID) REFERENCES
SPEAKER(SPEAKERID));


texts: Out-of-order results in the READ UNCOMMITTED isolation level?

texts:
On the Wikipedia page for Isolation (database systems), when explaining dirty reads, it says:

The only thing that may be prevented in the READ UNCOMMITTED isolation level is updates appearing out of order in the results; that is, earlier updates will always appear in a result set before later updates.

I am confused about in which case updates would appear out of order in the results. I think it is impossible and not what READ UNCOMMITTED isolation level avoids?


texts: "Manage" option vanished on portal for Azure SQL database

texts:
I had a database which I deleted and it used to have a "Manage" option which would open the online sql management portal.

I created a new database, now I don't have the "Manage" option anymore.

I cannot connect when I try to go to the same URL with my new server name.

I'm also having issues connecting to this new database using Entity Framework. The error message:

  A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections. (provider: SQL
  Network Interfaces, error: 26 - Error Locating Server/Instance
  Specified)


texts: How do I find all uses of a data type in a MySQL database?

texts: I'm planning an upgrade to MySQL where DATETIME, TIMESTAMP, TIME and YEAR are potentially a problem. I'd like to query the database to find all uses of these datatypes. How can I accomplish this?


texts: Does large text datatypes reserves storage on disk?

texts:
I'm design an DB for an app, in a table there is a column MEDIUMTEXT which according to mysql doc can storage on disk upto 16MB.
So my question is if I'm storing a text of 1Mb in this column, will it still reserves 16MB storage/block on disk for this particular record.
And also while reading all columns of multiple rows at once i.e 100 rows, what impact will have on memory and CPU usage for reading such large size storage columns?


texts: How to use 'Bind Variable' with 'With clause' in a Dynamic query using PLSQL

texts:
I have three tables with these structures:
1) z_test_a(c_num   number , <other columns>)
2) z_test_b(c_num   number , Amount  number , <other columns>)
3) z_test_b(c_num   number , Amount  number , <other columns>)

Inspired by one the answers in this post: https://stackoverflow.com/questions/2514254/how-can-i-create-a-dynamic-where-clause , I've learned that one way to use Dynamic Query and still use Bind variables is to write your query with With clause. I tried to apply this approach with Intersect but I could not succeed and my procedure does not work.It ends up giving me this error:ORA-00904:"B"."AMOUNT1" invalid identifier for this input parameter:
1)i_table_names:z_test_b,z_test_c
2)i_table_names:1000
3)i_amnt_second:1000

Is this even possible to use this approach when there is intersect or union or the query must be as simple as it is in the post mentioned above? And is there a better way to write this procedure?Maybe it can be written without dynamic query .
My procedure is:
create or replace procedure bind_variable_intersect(i_table_names in varchar2,
                                                    i_amnt_first  in number,
                                                    i_amnt_second in number,
                                                    o_out         out sys_refcursor) is

v_base_query varchar2(2000) := ' with binds as
                                   (select :bind1 as amount1,
                                           :bind2 as amount2
                                    from dual)
                                 select t.c_num
                                 from z_test_a t , binds b where 1=1  ';

begin

-- Check input parameter " i_table_names "
if i_table_names like '%z_test_b%' then
   v_base_query := v_base_query ||
                ' intersect select c_num from z_test_b where amount = b.amount1  ';
end if;

if i_table_names like '%z_test_c%' then
   v_base_query := v_base_query ||
                ' intersect select c_num from z_test_c where amount = b.amount2  ';
end if;

-- Debug Code
dbms_output.put_line(v_base_query);

-- Execute
open o_out for v_base_query
   using i_amnt_first,i_amnt_second;

end;


texts:
I have a composite type where one subcolumn is an integer and the other is a numrange.

I'm using: PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 6.3.0-5ubuntu1) 6.3.0 20170124, 64-bit

And for the purposes of this testing SET enable_seqscan=false.

This uses the index:

EXPLAIN ANALYZE SELECT * FROM shot
WHERE lower(shot.matchsecond) <@ (0, numrange(5, 10))::matchsecond_type;

Bitmap Heap Scan on shot  (cost=471.17..790.19 rows=50 width=45) (actual time=2.601..29.555 rows=5 loops=1)
  Recheck Cond: (((matchsecond).match_id)::integer = (0)::integer)
  Filter: ((numrange(lower(((matchsecond).second)::numrange), lower(((matchsecond).second)::numrange), '[]'::text))::numrange <@ ('[5,10)'::numrange)::numrange)
  Rows Removed by Filter: 9996
  Heap Blocks: exact=94
  Buffers: shared hit=193
  ->  Bitmap Index Scan on ix_shot_matchsecond  (cost=0.00..471.16 rows=10001 width=0) (actual time=2.516..2.516 rows=10001 loops=1)
        Index Cond: (((matchsecond).match_id)::integer = (0)::integer)
        Buffers: shared hit=99
Planning time: 0.401 ms
Execution time: 29.623 ms

But this doesn't:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM shot
WHERE lower(shot.matchsecond) <@ ((shot.matchsecond).match_id, numrange(5, 10))::matchsecond_type;

Seq Scan on shot  (cost=10000000000.00..10000000319.02 rows=1 width=45) (actual time=0.091..20.003 rows=5 loops=1)
  Filter: ((((matchsecond).match_id)::integer = ((matchsecond).match_id)::integer) AND ((numrange(lower(((matchsecond).second)::numrange), lower(((matchsecond).second)::numrange), '[]'::text))::numrange <@ ('[5,10)'::numrange)::numrange))
  Rows Removed by Filter: 9996
  Buffers: shared hit=94
Planning time: 0.351 ms
Execution time: 20.075 ms

Note the 0 in the first compared to (shot.matchsecond).match_id in the second on the right hand side of the <@.  Interestingly, if the left hand side is simply shot.matchsecond instead of lower(shot.matchsecond), the query manages to use the index.  The index is also used when constructing the numrange with functions like numrange(lower((shot.matchsecond).second), lower((shot.matchsecond).second + 10)).

Here are the relevant definitions:

CREATE DOMAIN matchsecond_match AS integer NOT NULL;
CREATE DOMAIN matchsecond_second AS numrange NOT NULL CHECK(VALUE <> numrange(0,0));

CREATE TYPE matchsecond_type AS (
    match_id matchsecond_match,
    second matchsecond_second
);

CREATE OR REPLACE FUNCTION matchsecond_contains_range(matchsecond_type, matchsecond_type)
RETURNS BOOLEAN AS $$ SELECT $1.match_id = $2.match_id AND $1.second @> $2.second $$
LANGUAGE SQL;

CREATE OPERATOR @> (
    LEFTARG = matchsecond_type,
    RIGHTARG = matchsecond_type,
    PROCEDURE = matchsecond_contains_range,
    COMMUTATOR = <@,
    RESTRICT = eqsel,
    JOIN = eqjoinsel
 );

CREATE OR REPLACE FUNCTION matchsecond_contained_by_range(matchsecond_type, matchsecond_type)
RETURNS BOOLEAN AS $$ SELECT $1.match_id = $2.match_id AND $1.second <@ $2.second $$
LANGUAGE SQL;

CREATE OPERATOR <@ (
    LEFTARG = matchsecond_type,
    RIGHTARG = matchsecond_type,
    PROCEDURE = matchsecond_contained_by_range,
    COMMUTATOR = @>,
    RESTRICT = eqsel,
    JOIN = eqjoinsel
 );

CREATE OR REPLACE FUNCTION lower(matchsecond_type)
RETURNS matchsecond_type AS
$$ SELECT ($1.match_id, numrange(lower($1.second), lower($1.second), '[]'))::matchsecond_type $$
LANGUAGE SQL;

And a test table:

Reminder: Use CREATE EXTENSION btree_gist;

CREATE TABLE shot AS(
    SELECT i AS id, (0, numrange(i, i+1))::matchsecond_type AS matchsecond
    FROM generate_series(0,10000) AS i
);

ALTER TABLE shot ADD PRIMARY KEY (id);
CREATE INDEX ix_shot_matchsecond
    ON shot
    USING gist (((matchsecond).match_id), ((matchsecond).second));

I tried adding this index, but it didn't change anything

CREATE INDEX ix_shot_lower_matchsecond
    ON shot (lower(matchsecond));


texts: Getting error while running dbca command as oracle user on linux, please help me on this

texts:
could someone please help me on the below error to get ride of it:

[oracle@localhost ~]$ dbca
UnsatisfiedLinkError exception loading native library: njni11
java.lang.UnsatisfiedLinkError: /u01/app/oracle/product/11.2.0/db_home/lib/libnjni11.so: libaio.so.1: cannot open shared object file: No such file or directory
Exception in thread "main" java.lang.UnsatisfiedLinkError: get
        at oracle.net.common.NetGetEnv.get(Native Method)
        at oracle.net.config.Config.getNetDir(Unknown Source)
        at oracle.net.config.Config.initConfig(Unknown Source)
        at oracle.net.config.Config.<init>(Unknown Source)
        at oracle.sysman.assistants.util.NetworkUtils.<init>(NetworkUtils.java:286)
        at oracle.sysman.assistants.util.step.StepContext.<init>(StepContext.java:332)
        at oracle.sysman.assistants.dbca.backend.Host.<init>(Host.java:811)
        at oracle.sysman.assistants.dbca.ui.UIHost.<init>(UIHost.java:258)
        at oracle.sysman.assistants.dbca.ui.InteractiveHost.<init>(InteractiveHost.java:54)
        at oracle.sysman.assistants.dbca.Dbca.getHost(Dbca.java:164)
        at oracle.sysman.assistants.dbca.Dbca.execute(Dbca.java:112)
        at oracle.sysman.assistants.dbca.Dbca.main(Dbca.java:180)
[oracle@localhost ~]$


texts: MySQL Foreign Key Prevents Deleting Table — But Table is Empty

texts:
Why would dropping an empty table produce a foreign key error?

In MySQL, I have a table with the following definition

CREATE TABLE `enterprise_rma` (
  `entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'RMA Id',
  `status` varchar(32) DEFAULT NULL COMMENT 'Status',
  `is_active` smallint(5) unsigned NOT NULL DEFAULT '1' COMMENT 'Is Active',
  `increment_id` varchar(50) DEFAULT NULL COMMENT 'Increment Id',
  `date_requested` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'RMA Requested At',
  `order_id` int(10) unsigned NOT NULL COMMENT 'Order Id',
  `order_increment_id` varchar(50) DEFAULT NULL COMMENT 'Order Increment Id',
  `store_id` smallint(5) unsigned DEFAULT NULL COMMENT 'Store Id',
  `customer_id` int(10) unsigned DEFAULT NULL,
  `customer_custom_email` varchar(255) DEFAULT NULL COMMENT 'Customer Custom Email',
  PRIMARY KEY (`entity_id`),
  KEY `IDX_ENTERPRISE_RMA_STATUS` (`status`),
  KEY `IDX_ENTERPRISE_RMA_IS_ACTIVE` (`is_active`),
  KEY `IDX_ENTERPRISE_RMA_INCREMENT_ID` (`increment_id`),
  KEY `IDX_ENTERPRISE_RMA_DATE_REQUESTED` (`date_requested`),
  KEY `IDX_ENTERPRISE_RMA_ORDER_ID` (`order_id`),
  KEY `IDX_ENTERPRISE_RMA_ORDER_INCREMENT_ID` (`order_increment_id`),
  KEY `IDX_ENTERPRISE_RMA_STORE_ID` (`store_id`),
  KEY `IDX_ENTERPRISE_RMA_CUSTOMER_ID` (`customer_id`),
  CONSTRAINT `FK_ENTERPRISE_RMA_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID` FOREIGN KEY (`customer_id`) REFERENCES `customer_entity` (`entity_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `FK_ENTERPRISE_RMA_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='RMA LIst';

I'm trying to DROP this table, but whenever I do I receive the following error.

  Cannot delete or update a parent row: a foreign key constraint fails

What I don't understand is, the table is already empty.  If I SELECT * FROM enterprise_rma;, no rows are returned (0 rows affected)

and SHOW ENGINE INNODB STATUS; returns nothing useful.

I know I can work around this with the following

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE enterprise_rma;
SET FOREIGN_KEY_CHECKS=0;

But I don't understand how a foreign key constraint could fail if there's no data in the table.  Is there a subtlety of MySQL's foreign key mode I don't understand?  Can MySQL get damaged or put in an invalid state when this happens?  Are there methods for troubleshooting this myself?


texts: How to edit `postgresql.conf` with pgAdmin 3?

texts:
How does one edit the 'postgresql.conf' file with the pgAdmin app?

With pgAdmin 1.18.1 on Postgres 9.3.x, when I choose File > Open postgresql.conf I get a file picker dialog. And, yes, I'm connected in pgAdmin as the superuser 'postgres'.

I know I've done this in older versions of Postgres.


texts: Can I use `base/pgsql_tmp` directory for non-admin applications?

texts:
((
  edit after answer: this is a typical
  XY question/problem,
  there are no restriction on use absolute path,
  on PostgreSQL's FileSystem functions. As @LaurenzAlbe comment, "just try".
))

I need to export/import JSON files, CSV files, XML, GeoJSON, etc. Example:

CREATE EXTENSION adminpack;
SELECT pg_catalog.pg_file_write(
  'base/pgsql_tmp/test1_hello.txt',
  E'Hello\n\n\tWorld!',
  false
);

All are temporary (exists in a BEGIN/COMMIT), but no problem?

no risk on filename conflict? (e.g. my filename and a pgsql's managed temporary table filename)
no risk of directory (base) volume overflow? (e.g. when I save a big file there)
no risk on read-and-write sequences over same filename? They will be synchronous on BEGIN/COMMIT  sections?

NOTES AND CONTEXT

Sometimes we can say that "database servers aren't designed for general file I/O" (ref), but sometimes we need it (!),  to simplify and automatize ETL or interface procedures.

About item 1 and the practical use, to simplify export/import procedures (and not need to remember where is data_directory), is perhaps better to create a ${data_directory}/base/pgsql_tmp/MyArea as symbolic link to other area  (e. eg. /tmp/MyRealArea).


texts: Improving performance when ordering by a column of a joined table

texts:
I have a parent table that contains a foreign key to a lookup table (simplified example):
CREATE TABLE [dbo].[Parent] (
    [Id] [uniqueidentifier] NOT NULL,
    [LookupId] [uniqueidentifier] NULL
)

CREATE TABLE [dbo].[Lookup] (
    [Id] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](64) NOT NULL
)

In this case, the Parent table has over 10 million rows and the Lookup table has around 5,000. The real Parent implementation has several such foreign key references to other tables and each of those columns may contain NULLs.
Both example tables have unique clustered indexes for their Id columns, Parent has a non-clustered index for LookupId and Lookup has a non-clustered index for Name.
I'm running a paged query where I want to include the lookup value in the results:-
SELECT
    P.Id,
    L.Name
FROM Parent P
LEFT JOIN Lookup L ON P.LookupId = L.Id
ORDER BY P.Id
OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY

This runs quickly, as does ordering by P.LookupId.
If, however, I try to order by Name (or even L.Id), the query runs considerably slower:
SELECT
    P.Id,
    L.Name
FROM Parent P
LEFT JOIN Lookup L ON P.LookupId = L.Id
ORDER BY L.Name
OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY

The query plan for the second query is here: https://www.brentozar.com/pastetheplan/?id=Sk3SIOvMD
Other seemingly related questions seem to involve ordering by columns in the first table which could be resolved using an appropriate index.
I tried creating an indexed view for this query, however, SQL Server won't allow me to index the view because it contains a LEFT JOIN which I require because LookupId may be NULL and if I use an INNER JOIN those records would be excluded.
Is there a way to optimise this situation?
EDIT
Rob Farley's answer (thanks!) is great and works perfectly for the question as I originally asked it, in which I implied I was joining a single table.
As it is, I have multiple such tables and I was unable to reconcile all using INNER JOINs in order to use that solution.
For the moment I have worked around this by adding a "NULL" row to the lookup tables so I can use an INNER JOIN without losing any rows on the left.
In my case I use uniqueidentifier identities, so I create an indexed view like this:
CREATE VIEW [dbo].[ParentView]
WITH SCHEMABINDING
AS
SELECT
    P.Id,
    L.Name
FROM [dbo].Parent P
INNER JOIN [dbo].Lookup L ON ISNULL(P.LookupId, '00000000-0000-0000-0000-000000000000') = L.Id

I then add a row to the Lookup table with a value of 00000000-0000-0000-0000-000000000000 for Id so there is always a match on the right of the join.
I can then create indexes on that view as needed.
Also, as I'm not using Enterprise, I found I needed to use the NOEXPAND hint to ensure those indexes are used:
SELECT *
FROM [ParentView]
WITH (NOEXPAND)
ORDER BY Name
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY


texts: Oracle 19c GI Install on OL7 : ASM disks stamped via AsmLib not getting discovered via GridSetup.sh

texts:
I am trying to install GI 19c on Oracle Linux 7 (required for 19c), and have used AsmLib to try to achieve device persistence (owner,group) for ASM devices/disks. Prior to that I used udev rules for the same and was able to achieve the same. Both times I get the same issue while doing ASM disk discovery as explained below.

Problem is when I try to invoke the gridSetup.sh from node 1, all checks pass including node readiness, ssh equivalence, scan etc, but the installer fails(not able to find the disks) at discovering ASM disks which are already marked by oracleasm(AsmLib) as asm disks and are owned by grid:oinstall.

The installer logs from oui show the below.

WARNING:  [May 29, 2019 12:32:14 PM] SRVCTL not found as there is no Grid Infrastructure home on the box.
INFO:  [May 29, 2019 12:32:14 PM] Executing [/u01/app/19.0.0/grid/bin/kfod.bin, nohdr=true, verbose=true, op=dfltdstr]
INFO:  [May 29, 2019 12:32:14 PM] Starting Output Reader Threads for process /u01/app/19.0.0/grid/bin/kfod.bin
INFO:  [May 29, 2019 12:32:14 PM] The process /u01/app/19.0.0/grid/bin/kfod.bin exited with code 1
INFO:  [May 29, 2019 12:32:14 PM] Waiting for output processor threads to exit.
INFO:  [May 29, 2019 12:32:14 PM] Parsing Error 49802 initializing ADR
INFO:  [May 29, 2019 12:32:14 PM] Parsing ERROR!!! could not initialize the diag context

Already tried setting ORACLE_BASE env variable and added to path, so adrci is executable. Also verified owner and permissions, (oracle:oinstall)

Also checked on permissions for /u01/app/19.0.0/grid/bin/kfod.bin and they are executable by grid user.(grid:oinstall)

Also tried various different asm diskstrings like ORCL:VOL*, ORCL*, VOL*, /dev/oracleasm/disks/* etc, but none of them worked.

FYI: This is a fresh install of 19c GI onto a 2 node RAC cluster, (2 freshly created virtualbox machines with shared disks) with no prior GI or RDBMS installations on.

Any help/assistance is greatly appreciated.

Thanks