texts: How to raise_application_error beyond a when others clause

texts:
Background:

I've used a few Oracle articles to develop an error package, with some procedures. One of these is Log_And_Return which is called throughout the database. I've simplified it for brevity, and it looks like this:

PROCEDURE Log_And_Return (error_name)
IS
BEGIN
    // Some code to convert error_name to error_code and error_message
    LOG (error_code, error_message);
    Raise_To_Application (error_code, error_message);
END Log_And_Return;
// In reality, this procedure takes parameters which determine whether it will log and/or raise.

The Log procedure does as it says and the Raise_To_Application procedure simply raises an application error.

Problem:

I have a procedure, which performs a query, e.g. fetching a customer record. If this query fails, it's a big problem. So I could do this:

BEGIN
    SELECT *something*
    FROM *some table*
    WHERE *some field* = *some user input*

EXCEPTION
WHEN NO_DATA_FOUND THEN
    ERR.Log_And_Return('unknown_id');
WHEN OTHERS THEN
    ERR.Log_And_Return('unknown_error');
END;

So I'm displaying a specific error message if no data is found, or a generic one is another problem has occurred.

Now, let's say I amend the code:

BEGIN
    SELECT *something* INTO *some variable*
    FROM *some table*
    WHERE *some field* = *user id*
    Call_Another_Procedure(*user id*, *some variable*)

EXCEPTION
WHEN NO_DATA_FOUND THEN
    ERR.Log_And_Return('unknown_id');
WHEN OTHERS THEN
    ERR.Log_And_Return('unknown_error');
END;

Now, after the select query, I'm calling a new procedure. Inside it, I'm doing a few things, including an update statement, like so:

// bunch of logic

BEGIN
    UPDATE *another table*
    SET *some field* = *some value*
    WHERE *some field* = *variable passed into method*
EXCEPTION
WHEN NO_DATA_FOUND THEN
    Err.Log_And_Return('some_error')
END;

Question:

If the no_data_found error is thrown, Log_And_Return will log the problem and raise an application error... which will then be caught by the when others clause in the parent procedure - which will return the wrong message to the user.

What is the workaround to this?

One workaround I had considered, and I've no idea if this is recommended or not, would be to wrap every stored procedure with a BEGIN END EXCEPTION block, where every procedure had a when others block that just logged and re raised the most recent error (using SQLCODE). Then, in my application layer I could specify that if the error is between -20000 and -20999, show it along with its message, otherwise show a generic message (and the DBA can find out what happened in the database by looking at the log table, along with a full stacktrace).

Or do I simply get rid of ALL when others blocks and let the application layer deal with any of these unknown exceptions?


texts:
First Trigger

ALTER TRIGGER [dbo].[Price_Modified]
    ON [dbo].[stock_recieve]
    AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE (Stock_out)
BEGIN
    UPDATE s
    SET value = new.Item_Price * new.Stock_out
    FROM stock_recieve AS s
    JOIN inserted AS new ON new.[Bill No] = s.[Bill No]
    JOIN deleted AS old ON old.[Bill No] = s.[Bill No]
END;

Second Trigger.

 ALTER TRIGGER [dbo].[newval]
      ON [dbo].[stock_recieve]
        AFTER UPDATE
    AS
    SET NOCOUNT ON;
    IF UPDATE (Stock_out)
    BEGIN
        UPDATE s
        SET Uservalue = new.Item_Price * new.Stock_out
        FROM stock_recieve AS s
        JOIN inserted AS new ON new.[Bill No] = s.[Bill No]
        JOIN deleted AS old ON old.[Bill No] = s.[Bill No]
    END;

Result to be obtained on first stock_out update.

| Item_Price | Stock_in | Stock_out | Value | Uservalue |
|------------|----------|-----------|-------|-----------|
| 50         | 200      | 100       | 5000  | 5000      |
| 100        | 50       | 20        | 2000  | 2000      |
| 30         | 100      | 40        | 1200  | 1200      |

Result to be obtained on second stock_out update.

| Item_Price | Stock_in | Stock_out | Value | Uservalue |
|------------|----------|-----------|-------|-----------|
| 50         | 200      | 200       | 10000 | 5000      |
| 100        | 50       | 40        | 4000  | 2000      |
| 30         | 100      | 100       | 3000  | 1800      |

The first trigger does what its suppose to do, but I want the second trigger on Uservalue to not add but instead replace the result.
Hope this is clear @Ypercube


texts: Calc between 2 Cols in Same Table with Different Where Criteria?

texts:
I have a table with these cols:

T

id (PK)

dt (DateTime - each row is unique and 1min apart)

E (Decimal Value)

C (Decimal Value)

S (Integer Value of either 1 or 3)

I need this :
AVG(T1.C - T3.E)
WHERE MIN(T1.id) > T3.id
^^^ This is just an example of how I think it needs to be done, but am VERY open to a better way!)^^^
So the T1/T3 tables are simply derived from the main table (T) using:

(SELECT * FROM T WHERE S = 1) AS T1
(SELECT * FROM T WHERE S = 3) AS T3

DETAILS

S = 1 for the id/row that C is pulled from
S = 3 for the id/row that E is pulled from
id/row for E Needs to be > the row/id for C YEARWEEK(dt) for the
id/row that C is pulled from needs to = the YEARWEEK(dt) of the row    that E is pulled from.

I can't join them on id or dt as each row has a unique value.
The way I've been trying to accomplish this is to split the original table into either Temp Tables or subqueries but nothing will bring it all together.


texts: Unable to query SQL server metadata through linked server

texts:
I have a scenario where a user wants to do the following through a linked server:

SELECT *
FROM [DBTEST].[AdventureWorks].sys.objects
WHERE object_id = OBJECT_ID(N'dbo.DatabaseLog');

The linked server is configured to use Be made using this security context and the linked server login has read access on the [DBTEST].[AdventureWorks] database.

The above query does not return any rows when executed through the linked server. However, when I login directly to DBTEST using the linked server login and do the following, it does return a row:

SELECT *
FROM [AdventureWorks].sys.objects
WHERE object_id = OBJECT_ID(N'dbo.DatabaseLog')

I am confused why it is not working through the linked server and what I need to do in order to fix it.

One more strange thing is even if I grant sa rights to the linked server login it still does not return any rows when executed through the linked server.

Both servers are SQL Server 2008 Enterprise Edition.


texts: Does MySQL lock full table in case of update query on specific rows?

texts:
I just wanted to know if the MySQL 5.5 with InnoDB will lock the whole table instead of some specific rows for SELECT query while UPDATE query is running on that specific table?

I can see that the SELECT queries stop responding while I was running the below query. All of the associated services were down in that particular period.

UPDATE table SET column='sample' where column is null;

Around 3.6 million(Total: 4 Million) rows were going to updated with this query.

Index on the column which is part of where clause makes any effect on this?
Is there any possible way to minimise the execution time?  I am ok with the query changes as long as the output remains the same.


texts: Adding a new primary key ID column to an existing table

texts:
I want to add a new column to a table. The table is already populated with data.

I can do this by making a complete copy of a table, inlcuding the contents, and add a Primary Key ID column to it.

I can create a new table thus:

create table myTestTable
(
    ID uniqueidentifier NOT NULL,
    SomeId uniqueidentifier NOT NULL,
    SomeOtherId uniqueidentifier NOT NULL,
    constraint table_constraint PRIMARY KEY (ID)
)

-- modify the script below to copy the old table across to the new one,
-- generating a unique ID (GUID) into

insert into myTestTable
(ID,
ClaimPaymentId,
TemplateId)
select
    NEWID(),
    SomeId ,
    SomeOtherId
from [dbo].[TableToBeCopied]

I can then drop the copied table and rename the copy to the old table name.

But I have to do about sixty such tables and I want to write a script which will take a table's name as a parameter and automatically generate the column definitions. But I can't see how to do this. Any ideas?


texts: sql query to compare column name to column data

texts:
What I am looking for is how I could go about running a query to select

SELECT 'text' FROM 'some_table'
WHERE 'text' = 'some_column_name'

I have looked and searched, and may be searching with the wrong information, but I have been unable to find anything of the sort.

Any help would be greatly appreciated.


texts: Read only access to Slaves from Server A

texts:
Here's a little scenario:

Server A which is a Dev server needs READ access to the Mysql databases which are on slaves B and C. No WRITE access is allowed from server A to the slaves.

How do i achieve this ?


texts: How to use --excludeCollectionsWithPrefix in mongodump

texts:
I would like know how to use the option "--excludeCollectionsWithPrefix" of the mongodump ?

I have some collections name with numbers like this:

   registry.logging_event_15313531
   registry.logging_event_143432432
   registry.logging_event_18979865
   registry.logging_event_905998132

I would like remove of mongodump every collection with "registry.logging "


texts:
From SQL Aggregate Functions (Office 2007) there are only these GROUP BY aggregate functions in Access SQL:

Count(), Sum(), Avg(),
First(), Last(), Min(), Max(),
StDev(), StDevP(), Var(), VarP()

From PostgreSQL Aggregate Functions there are (among others) also these aggregate functions in PostgreSQL:

array_agg (expression)
  -- input values, including nulls, concatenated into an array

string_agg (expression, delimiter)
  -- input values concatenated into a string, separated by delimiter

How to get the equivalent of array or string GROUP BY aggregate functions in Access?
Is it possible to build Access SQL aggregate functions?
In case it's not clear, if I this data

ID col
-----
1  A
1  B
1  C
2  A
3  A
3  B

how can I get the following aggregation?

ID cols
----------
1  A, B, C
2  A
3  A, B

Do I have to resort to VBA? Any other ideas?

I'm using Access 2007 - 2010, but if things are different in a newer version, please let me know.


texts: postgresql replay_location in pg_stat_replication is empty

texts:
I'm running a cluster of PostgreSQL 9.5.2, and export the stream log with confluentinc/bottledwater-pg: Change data capture from PostgreSQL into Kafka. The replication works fine, but I'm wondering about the output of pg_stat_replication:

db# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid              | 371125
usesysid         | 16385
usename          | db
application_name | bottledwater
client_addr      | 127.0.0.1
client_hostname  |
client_port      | 22338
backend_start    | 2016-09-30 16:58:04.074937+08
backend_xmin     |
state            | streaming
sent_location    | 12DD/D325D478
write_location   | 12DD/D2D75EF0
flush_location   | 12DD/D2B8B5F8
replay_location  |
sync_priority    | 0
sync_state       | async

See near the bottom, the replay_location is null. If anyone knows what's wrong, let me know.


texts: how to get the difference between 2 tables in 2 different schema's in Oracle?

texts:
I am using Oracle 11G.
I have a table called Tickets in the schema called 'tickets1"
I have the same table structure in another schema called 'tickets2'
I want to find out

What data is the same in both tables?
What data is different?

CREATE TABLE tickets(
    ticket_number NUMBER NOT NULL,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    PRIMARY KEY(ticket_number)
);


texts: Duplicate table structure with different data

texts:
I am looking for a way to resolve a design issue with a Microsoft Access project that is being converted to use SQL Server 2014 as a backend.

We have several Access backend files, two of them have the same table structure, but different data. In the "Quotes" MDB backend file, we have tables containing sales quotes. In the "Jobs" MDB backend file, we have tables containing jobs (quotes that have been sold), but also the same sales quotes tables are duplicated. When a quote is sold, it's copied from one backend to the other. Which means the data exists in two places and is not kept in sync.

When I originally started upsizing tables from the MDB backends, i didn't think about this issue but now it's becoming a problem that needs to be resolved. We don't know how the structure should be fixed. It's my understanding that the combined set of all the tables in the various MDB backends for this software, should be in one database in SQL Server. The only reason for splitting them up as they are now is to overcome limitations of the Access MDB format.

To complicate matters further, the tables have exactly the same names. The Access front-end application switches between backends on-the-fly, when the user changes between quoting or working with sold orders.


texts: In Microsoft SQL Server 2008, syntax generates the error "The Parallel Data Warehouse (PDW) features are not enabled."

texts:
I have the following virtual column generated from an aggregate over a sorted partition,

MIN(picture_id) OVER ( PARTITION BY [360_set] ORDER BY picture_id ASC )

However, when I execute that, I get the following.

Msg 11305, Level 15, State 10, Line 12
The Parallel Data Warehouse (PDW) features are not enabled.

This is where it gets interesting though, without a sort order on the partition, it works:

MIN(picture_id) OVER ( PARTITION BY [360_set] )

And, further, ROW_NUMBER() a window function (not an aggregate function) works with an explicit order on the partition.

ROW_NUMBER() OVER ( PARTITION BY [360_set] ORDER BY picture_id ASC )

How come the desired statement doesn't work? Where is this documented? The version information was requested, this is what I in Help → About.

Microsoft SQL Server Management Studio          10.0.5512.0
Microsoft Analysis Services Client Tools        10.0.5500.0
Microsoft Data Access Components (MDAC)         6.1.7601.17514
Microsoft MSXML                                 3.0 6.0
Microsoft Internet Explorer                     9.10.9200.16635
Microsoft .NET Framework                        2.0.50727.5472
Operating System                                6.1.7601

The result from SELECT @@VERSION is Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (X64)   Aug 22 2012 19:25:47   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)


texts: Is my MySQL instance irretrievably dead?

texts:
I'm trying to install a JIRA instance on a MySQL 5.1.73 instance running on CentOS 6.5 for our developers.

The install gets to the point where I need to create the database, and then FLUSH PRIVILEGES;

This is what I get:

ERROR 1033 (HY000): Incorrect information in file: './mysql/tables_priv.frm'

So I hit google, and try what's suggested by those who know.

(root@root.server) [mysql] 18:19:47 (4) $ REPAIR TABLE tables_priv EXTENDED USE_FRM ;
+-------------------+--------+----------+---------------------------------------------    -------------+
| Table             | Op     | Msg_type | Msg_text                                                     |
+-------------------+--------+----------+---------------------------------------------    -------------+
| mysql.tables_priv | repair | Error    | Incorrect information in file:     './mysql/tables_priv.frm' |
| mysql.tables_priv | repair | error    | Corrupt                                                      |
+-------------------+--------+----------+---------------------------------------------    -------------+
2 rows in set (0.00 sec)

After trying some other stuff, I decide to cut my losses, and reinstall the database..

mysqldump --events --lock-all-tables --all-databases -u root -p*********** > alldatabases.23.07.2014.sql

Oops...

mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577)

All right, so I'm stuck, now.  The 'nuclear option' would be to remove the database files, uninstall the database and reinstall it, but we have Groupware (Horde) running on that server, and that would be .. not cool.

Any help gratefully received.


texts: SQL Server Integration Services (SSIS) Package Failing with no error

texts:
I have some SSIS packages that are executed as steps in a Server Agent Job. The Steps often fail with no error and I have had to add retries. Now the jobs complete but I get the warning exclamation mark telling me there has been a failure and its these steps that nearly always require a retry.

When looking at the failed step in the Job History I can see the package execution log and then it just randomly cuts off and the step beings a retry. There is no consistency with where it stops and I know there isn't a problem with the package as it eventually succeeds.

Does this sound like a network related issue? Is there anyway to find out what is causing these failures to happen?


texts:
New to RDS (used to having my own server in my own data center).

We are on MySQL 5.6 on RDS.

when a slave gets behind in replication lag, the master ceases to rotate out its binary logs.  It is, apparently, waiting to every slave to commit every transaction before rotating binary logs out.   I don't have a problem with this conceptually (as long as you have monitors on disk space on the master, its a good thing).

My question is academic -- what causes this to happen?
** I know with ASYNCHRONOUS replication, the master is unaware of the slaves' status

is this a function of synchronous replication? semi-synchronous replication?

I'd love some insight and perhaps a pointer to a white paper somewhere (I can not find one that addresses this specific issue of binary logs not being rotated out)


texts: I would like to give user a permission to perform EXPLAIN but not to execute the SELECT itself. Is this doable in PostgreSQL?


texts: SQL Except Statement and Add in One more column

texts:
How do I conduct a Except statement and Add in one more column?
Three tables with the exact same structure. (CustomerTransaction1,CustomerTransaction2, CustomerTransactionDiff)

I want the LoadDate from CustomerTransaction1. If not, I will have to rejoin on the primary key, and extract the load date.

I do not want to except/compare on the LoadDate, just add in the column.

Create table dbo.CustomerTransaction1
(
    CustomerTransactionId int primary key identity(1,1),
    CustomerName varchar(255),
    PurchasePrice decimal(10,2),
    Quantity int,
    Loaddate datetime
)

insert into CustomerTransactionDiff
( CustomerTransactionId, PurchasePrice,Quantity,Loaddate)
select
    CustomerTransactionId,
    CustomerName,
    PurchasePrice,
    Quantity
from dbo.CustomerTransaction
except
select
    CustomerTransactionId,
    CustomerName,
    PurchasePrice,
    Quantity
from dbo.CustomerTransaction2


texts: MySQL Use an In Clause With a Comma Delimited Column

texts:
I am trying to use a column that has a comma delimited list of values in an in clause.  I thought about doing a contains but it would not be correct when searching for single digit values.
Example:  1,5,12,14,16,15,18,19,23,24,25,26,27,28,29,31,32,33,34,35,36,37,38,40,41,43,44,45,47,48,49,51,53,54,55,56,57,59,61,62,63,64,65
This is the error that I am getting:

[MySql.Data]
ErrorCode: -2147467259, Number: 1064
ErrorMessage: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'cpei5.value' at line 12

Here is my query:
SELECT soi.*
FROM   sales_order_item soi
       INNER JOIN sales_order so
            ON  so.entity_id = soi.order_id
       INNER JOIN sales_order_address soa
            ON  soa.parent_id = so.entity_id
       INNER JOIN catalog_product_entity cpe
            ON  cpe.sku = soi.sku
       INNER JOIN catalog_product_entity_text cpei5
            ON  cpei5.row_id = cpe.entity_id
       INNER JOIN eav_attribute ea5
            ON  ea5.attribute_code LIKE 'state_not_allowed'
            AND ea5.attribute_id = cpei5.attribute_id
WHERE  soa.address_type = 'shipping'
       AND date_add(so.created_at, interval 365 DAY) >= CURRENT_DATE()
       AND date_add(so.created_at, interval 365 DAY) <= date_add(CURRENT_DATE(), interval 30 DAY)
       AND cpe.type_id = 'simple'
       AND soa.region_id IN cpei5.value


texts: MariaDB subquery cache scope and settings

texts:
I am migrating an application from MySQL 5.6 to MariaDB 10.  One major new feature of MariaDB is the subquery cache.  I am trying to determine the expected effect this cache will have on our application before enabling and testing it.
Under the "Implementation" section, the knowledge base article says this:

Every subquery cache creates a temporary table where the results and all parameters are stored. It has a unique index over all parameters. First the cache is created in a MEMORY table (if doing this is impossible the cache becomes disabled for that expression). When the table grows up to the minimum of tmp_table_size and max_heap_table_size, the hit rate will be checked:

if the hit rate is really small (<0.2) the cache will be disabled.
if the hit rate is moderate (<0.7) the table will be cleaned (all records deleted) to keep the table in memory
if the hit rate is high the table will be converted to a disk table (for 5.3.0 it can only be converted to a disk table).

The first thing I'm trying to determine is what exactly it means by, "Every subquery cache."  Since it states that the caches are created in temporary tables, it implies that they must be scoped to the current connection.  So my guess is that there is one subquery_cache table created per connection.  (I doubt every query has its own cache, because then the line about the cache "growing" wouldn't make sense.)  But that's just a guess; I can't find any more details.  Perhaps these temporary tables are indeed shared between connections somehow, unlike regular temp tables.  Or perhaps there are multiple caches per connection.
Can anyone confirm the number and scope of subquery caches created?
My next question is whether there are any options for tuning the cache.  Since we are moving from an optimized MySQL application, we don't currently have any queries that make extensive use of cachable subqueries (since such queries would be very slow under MySQL).  We do use plenty of subqueries though, just not in ways that are likely to cause a lot of cache hits.  Given that, I expect that enabling the cache will simply create an overhead in temporary table creation and lookup for each subquery, as well as memory used to store the cache tables (which apparently can't be tuned separately from the main tmp_table_size and max_heap_table_size variables).  Given that I expect to find that our application as it currently exists is more efficient without the cache enabled.  The ideal though would be if it supports something like the 'DEMAND' setting for the main query cache (query_cache), which allows you to specify exactly which queries should use the cache.
Since no such features are documented anywhere, I expect they don't exist, but it would be fantastic if anyone has knowledge regarding why not, or when we might expect them to.


texts: How to reuse an update trigger for multiple tables in postgresql?

texts:
In the database for a new project, I've started the convention of having each table have a last modified timestamp column.  To implement this, I wrote a trigger for each table:

CREATE TRIGGER touch_users
  BEFORE UPDATE
  ON users
  FOR EACH ROW
  WHEN (OLD.modification_time IS NOT DISTINCT FROM NEW.modification_time)
  EXECUTE PROCEDURE touch_modification_time();

CREATE TRIGGER touch_company
  BEFORE UPDATE
  ON company
  FOR EACH ROW
  WHEN (OLD.modification_time IS NOT DISTINCT FROM NEW.modification_time)
  EXECUTE PROCEDURE touch_modification_time();

-- etc for each table

This gets tedious fairly quickly; especially since they're exactly the same, except for the table name.

Is there a way I can reduce the boilerplate required for each table?  Or possibly even eliminate it altogether with a single trigger that acts on every table by default?


texts: I have a SQL job that sends an e-mail. Can I make it run a program instead?

texts:
I set up a job, and in Job Properties > Notifications I checked E-mail When the job fails.

I'm wondering if instead of this e-mail, I can make it run an .exe or similar with the details. The reason is I have a piece of custom monitoring software I wrote for other applications, and I want the events to go through that instead.

Is this possible somehow? Or should I write to the event log and just try to parse it? What is net send, could that do it?

Thanks


texts: Efficiciency of multiple small triggers versus one large trigger on SQL Server

texts:
I have been given the honourable task of adding - very carefully! - a few triggers to our ERP system's database, to get data - "in real time" - from the ERP system to other pieces of software running at our company. The triggers will not make any changes to the data, just pick a few fields from the inserted/updates rows, roll them up in a XML message and send them to a message queue. There will be no JOINs, either.
(Whether or not this is a good idea in to begin with is a question for another time.)

I can imagine writing several triggers, one for each scenario. Or I could could write two large triggers, one for INSERT, and for UPDATE. With enough force, I could probably even boil that down to just one huge trigger that runs both on INSERT and on UPDATE.

From a testing/debugging/maintenance point of view, several self-contained triggers have obvious advantages: Each one can be written, tested and enabled/disabled individually. Each trigger would check if whatever just happened to the respective table is relevant to that trigger, if so, it would do its thing, and if not, it would just quit.

However, I am worried that multiple triggers might cause performance issues. The tables I am supposed to attach these trigger(s) see frequent INSERT and UPDATE queries as part of normal operation[1]. (OTOH, the load on the database server is relatively mild so far.)

Are there any general guidelines on which approach is better?

Thank you very much for any information you might be able to share,
Benjamin

[1] Frequent in this case means "a couple of dozen transactions per minute on a busy day".


texts: Can you set the output configuration at Query time?

texts:
Is there a TSQL statement to configure the output to be Text-Column aligned?

I want to use SQL templates for my coworkers to run some templated UPDATE before/after statements. But when they run the query it needs to output to text and Column delimited

So contrived example say I have this query:

USE master
GO

SELECT *
FROM sys.dm_os_ring_buffers AS dorb
WHERE dorb.timestamp = N'<time, bigint, NULL>'

Is there a some SET statement to configure the output to be Text-Column aligned? This is only for these specific queries otherwise my coworker would want their default settings which is grid. I also can't rely on them to know to change it and that is important because unlike my contrived example they are print commands of sql update before/after statements.

Thus far I think my best bet would be to create a SSMS Add-in but I REALLY do not want to spend time doing that!


texts: UNION queries with different number of columns?

texts:
I have 2 queries on a table:
SELECT *, 1 as query_cr_0 FROM invoices LEFT OUTER JOIN customers ON customers.id = invoices.customer_id WHERE (customers.id IN (64, 66, 63, 62, 65))

SELECT *, 5 as query_cr_1 FROM invoices WHERE (invoices.state IN ('ready', 'out_for_delivery', 'delivered', 'closed')

I want to fetch all invoices that fulfill either of the queries.
My approach was to use UNION but LEFT OUTER JOIN in the first query prevents this:

ERROR:  each UNION query must have the same number of columns

My second approach was to use something like this:
select invoices.id from invoices
where id in (SELECT invoices.id, 1 as query_cr_0 FROM invoices
             LEFT OUTER JOIN customers ON customers.id = invoices.customer_id
             WHERE (customers.id IN (64, 66, 63, 62, 65)))
OR id in (SELECT invoices.id, 5 as query_cr_1 FROM invoices
          WHERE (invoices.state IN ('ready', 'out_for_delivery', 'delivered', 'closed')))

But this throws an error:

subquery has too many columns

My purpose is to fetch the invoices with query_cr_0 and query_cr_1 value. For ex: An invoice might be satisfying both the conditions so in that case both query_cr_0 and query_cr_1 should be present. If an invoice satisfies only one of the queries corresponding query_cr must be present.
How to solve this issue?


texts: Are partition numbers guaranteed to be in order of value?

texts:
Say I have a partitioned table, set up like so:

CREATE PARTITION FUNCTION PF_Month (DATE) AS RANGE RIGHT FOR VALUES (
  '2017-01-01',
  '2017-02-01',
  '2017-03-01',
  '2017-04-01',
  '2017-05-01',
  '2017-06-01',
);
GO

CREATE PARTITION SCHEME PS_Month AS PARTITION PF_Month ALL TO ([Primary]);
GO

CREATE TABLE Logs
(
     Id           INT NOT NULL,
     DateRecorded DATE NOT NULL,
     FixStatus    INT NOT NULL
);
GO

ALTER TABLE Logs
    ADD CONSTRAINT PK_Logs PRIMARY KEY (Id, DateRecorded)
    ON PS_Month(DateRecorded);
GO

CREATE NONCLUSTERED INDEX [IX_DateRecorded] ON Logs(DateRecorded)
    INCLUDE(FixStatus)
    ON PS_Month(DateRecorded);
GO

If I want to query the logs, in order of date, I have been told I can use the partition number in order to avoid a sort when the results from each partition are joined back together.

SELECT * FROM Logs WHERE ... ORDER BY $PARTITION.PF_Month(DateRecorded), DateRecorded

Are the partition numbers in order of when each partition was created, or are they in order of DateRecorded? E.g. if I were to add another split to the function while it is in use, would ordering by partition number still work?

ALTER PARTITION FUNCTION PF_Month() SPLIT RANGE ('2016-12-01')


texts: How to get mysql automatically running when I start tomcat apache on Windows?

texts:
When starting my Tomcat Apache Server, I've been having to start up mysql before than using the mysqld command. I was wondering if there was a work around this so that I could have mysql start up when I run the startup.bat command for tomcat.

Sorry, if this is an easy question. I have a very limited understanding of both databases and web servers so I wasn't able to find or understand any information I found on google.


texts: Goldengate warning on table with primary key

texts:
Installed Goldengate Version 18.1.0.0.0 for Oracle 11gR2. Table has primary key and yet goldengate complains that there is no key found,see below

    GGSCI (server01.localdomain as gguser@UPGR) 3> add trandata scott.books

    2019-01-23 22:44:42  WARNING OGG-06439  No unique key is defined for table BOOKS. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

    2019-01-23 22:44:42  INFO    OGG-15130  No key found for table SCOTT.BOOKS.  All viable columns will be logged.

    2019-01-23 22:44:42  INFO    OGG-15135  TRANDATA for instantiation CSN has been added on table SCOTT.BOOKS`

    GGSCI (server01.localdomain as gguser@UPGR) 10> info trandata scott.books

    Logging of supplemental redo log data is enabled for table SCOTT.BOOKS.
Columns supplementally logged for table SCOTT.BOOKS: "AUTHOR", "ID", "LANGUAGE_ID", "TITLE", "YEAR_PUBLISHED".

Prepared CSN for table SCOTT.BOOKS: 978016

This is DDL of scott.books from Oracle SQL Developer

CREATE TABLE "SCOTT"."BOOKS"
   ("ID" NUMBER(*,0),
    "TITLE" VARCHAR2(26 BYTE),
    "AUTHOR" VARCHAR2(23 BYTE),
    "YEAR_PUBLISHED" NUMBER(*,0),
    "LANGUAGE_ID" NUMBER(*,0),
     CONSTRAINT "PK_ID" PRIMARY KEY ("ID")
  )

Can someone point me in right direction.
Thanks

From dba_constraints

SQL> SELECT
  2      constraint_name,
  3      constraint_type,
  4      status,
  5      validated
  6  FROM
  7      dba_constraints
  8  WHERE
  9      owner = 'SCOTT'
 10      AND table_name = 'BOOKS';
CONSTRAINT_NAME                C STATUS   VALIDATED
------------------------------ - -------- -------------
PK_ID                          P ENABLED  VALIDATED


texts: Should Database Servers Be Defragmented?

texts: I've never been clear on this point - should Windows servers running SQL Server ever be defragmented with a tool such as Smart Defrag or Windows' Disk Defragmenter?


texts: How to add new column based on calculation of another column in postgresql?

texts:
I have the following table:

id   time_start           area
1   2019-09-01T10:12:32Z  london
2   2019-08-29T10:13:32Z  chicago
3   2019-07-31T10:14:32Z  paris
4   2019-09-28T10:17:32Z  madrid
5   2019-07-04T10:18:32Z  spain

I would like to add a new column called correct_date which is just the time_start column:

the new column should be added to the end and look like this:

date_start
2019-09-01
2019-08-29
2019-07-31
2019-09-28
2019-07-04

however I am filtering the data before hand based on a where statement and am a bit stuck on how to alter the table after the where condition has been satisified - or which order to do the process in? I know I will have to use timestamp::date to attain the date.

my current query is

select *
from
my_table
where area in ('london','chicago','paris')

how can I add an alter table statement to the above code so that it will add a new column based on the dates of the time_start column - would I do the alter statement at the beginning?

Couldn't find a clear answer online using postgresql!


texts: Many-to-One replication / Transactional Replication

texts:
I have several PostgreSQL DBs in different geographical locations (local sites).

Each local site DB have the same schema, but unique data. For example, Take a table with columns: Site_ID, Department_ID, Department_Name. Site_ID is unique for each site.
I want to collect all the data from the local site DBs into a centralised DB (PostgreSQL again) which acts as a data warehouse.
The corresponding example table on the centralised DB will have the same columns as above. All local site data will go into this table. Each site data designated by the Site_ID, of course.

Question: How to achieve this with PostgreSQL replication methods (streaming/multi-master UDR/BDR/etc.)
I see this can be done with SQLServer using Transactional Replication. What is the best way to achieve this functionality with PostgreSQL?

Restriction: The local sites can make only outgoing network connections (i.e. no inbound connections due to firewall restrictions)


texts: MySQL #1005 - Can't create table 'startet' (errno: 150)

texts:
Can anyone figure out why phpMyAdmin won't create the last table? It works fine if I drop the last foreign key, but I need it in there to get my assignment approved...

    CREATE TABLE nasjon (
    nasjonskode INT NOT NULL,
    nasjon VARCHAR(25) NOT NULL,
    leder VARCHAR(25) NOT NULL,
    deltagernr VARCHAR(25) NOT NULL,
    PRIMARY KEY (deltagernr)
    );

    CREATE TABLE passering (
    avstand INT NOT NULL,
    starttid INT NOT NULL,
    slutttid INT NOT NULL,
    passeringspunkt INT NOT NULL,
    høydeforskjell VARCHAR(25) NULL,
    bilde VARCHAR(25) NOT NULL,
    mellomtid VARCHAR(25) NOT NULL,
    PRIMARY KEY (slutttid, starttid)
    );

    CREATE TABLE vinner (
    startnr INT NOT NULL,
    alder VARCHAR (25) NOT NULL,
    kjønn VARCHAR (25) NOT NULL,
    øvelseid VARCHAR (25) NOT NULL,
    slutttid INT NOT NULL,
    PRIMARY KEY (startnr, slutttid),
    FOREIGN KEY (slutttid) REFERENCES passering (slutttid)
    );

 CREATE TABLE øvelser (
  øvelseid INT NOT NULL,
  øvelse VARCHAR(45) NULL,
  lengde VARCHAR(45) NULL,
  dato VARCHAR(45) NULL,
  starttidspunkt VARCHAR(45) NULL,
  PRIMARY KEY (øvelseid)
  );

CREATE TABLE registrering (
  deltagernr VARCHAR(25) NOT NULL,
  fornavn VARCHAR(45) NULL,
  etternavn VARCHAR(45) NULL,
  fødselsdato VARCHAR(45) NULL,
  kjønn VARCHAR(45) NULL,
  PRIMARY KEY (deltagernr),
  FOREIGN KEY (deltagernr) REFERENCES nasjon (deltagernr)
  );

CREATE TABLE påmelding (
  deltagernr VARCHAR(25) NOT NULL,
  startnr INT NOT NULL,
  øvelseid INT NOT NULL,
  PRIMARY KEY (startnr, øvelseid, deltagernr),
  FOREIGN KEY (startnr) REFERENCES vinner (startnr),
  FOREIGN KEY (øvelseid) REFERENCES øvelser (øvelseid),
  FOREIGN KEY (deltagernr) REFERENCES registrering (deltagernr)
  );

  CREATE TABLE startet (
  starttid INT NOT NULL,
  startnr INT NOT NULL,
  dato VARCHAR(45) NULL,
  status VARCHAR(45) NULL,
  PRIMARY KEY (startnr, starttid),
  FOREIGN KEY (startnr) REFERENCES vinner (startnr),
  FOREIGN KEY (starttid) REFERENCES passering (starttid)
   );

Been trying for hours to get this to work, but I get the same error message each time, and I'm not an administrator over the DB so can't get any further.


texts: What is the difference between start and enable in the Oracle RAC srvctl?

texts:
In the Oracle 12c documentation it was not clear what is the difference between the srvctl start and srvctl enable commands for services associated with a database.
In the view dba_service a database that has a service already started, has the column ENABLED with NO, what I noticed is that when a RAC service has already started and you try to enable it, it informs you that it is already enabled.
srvctl enable service -d mydb -s myservice
PRCC-1010: myservice was already enabled
PRCR-1002: Resource ora.mydb.myservice.svc is already enabled


texts: Matching table (with sub-tables) to another table(with sub-tables) efficiently

texts:
I have a problem in an ETL process. I need to integrate data into a data warehouse. The data is associated with a "variant". A variant is defined as having a specific set of parameters (identified by a property bag table with name and value, both varchar).

For example:

Variant 1
  Field A value 11
  Field B Value 22
Variant 2
  Field A value 12
  Field B value 23

A simplified table structure:

CREATE TABLE [simstg].[Parameter](
    [VariantNo] [smallint] NOT NULL,
    [Variable] [varchar](64) NOT NULL,
    [Value] [float] NOT NULL,
 CONSTRAINT [PK_Parameter] PRIMARY KEY CLUSTERED
(
    [VariantNo] ASC,
    [Variable] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

I need to import those variants (and the number can not be changed) from staging tables into a final resting table. THere we do have similar Variant Numbers, but the numbers DO NOT MATCH UP. So I need to map the variants. Variant 1 from the staging tables may be variant 22 in the final tables, or may not exist (in which case I create a new variant number).

I am stuck trying to identify the variants, efficiently. I know I am looking for a variant number in the target table that has the same number of parameters, with every parameter having the exact same value. You can assume the table s are identical - I can take it from there.

I know I can do that with a cursor - but going through every variant in the target table is prohibitive expensive. I know I can also generate a key string on the client side (or via a SP) and / or put a hashcode on that one and store them (which could happen SQL side). I could use dynamic SQL to basically generate a parameter string, then use HasHBytes on top of it to get a hash - then compare hashes to rule out most of the data.

But is there a more efficient way?


texts:
I have recently learned about 1NF, 2NF, and 3NF.  I understand the definitions and the differences.  I also had learned earlier about removing M:M relationships from a conceptual model by using bridging 1:M entities (but at that time I was not aware of normal forms).  The cool thing that I see now about normalization is that if you start with one big messy relation, the normalization steps automatically take care of the M:M for you, so you don't have to consciously think "ok I am bridging away my M:M."

However, I decided to ask myself hypothetically WHICH level of normalization specifically is responsible for the removal of M:M relationships.  I know it is not 1NF, because I could easily come up with examples with are both 1NF and M:M.  However, in all the simple examples I contrived, bringing them to 2NF made the M:M go away.  But, I am not sure this is definitive since I seem to be not very creative in coming up with exhaustive examples.  So I pose this question: is there a 2NF relation which exists and is still M:M, which needs to go through 3NF to have the M:M removed?  Or does 2NF consistently disallow M:M by itself?

Thanks!

UPDATE: Let me try to explain myself better.  Consider this simple example:

Table BOOK_AUTHOR

| ISBN       | Title     | AuthorID  | AuthorName|
|------------|-----------|-----------|-----------|
|  0001      |  Book1    | A01       | King      |
|  0001      |  Book1    | A02       | Tolkien   |
|  0002      |  Book2    | A01       | King      |
|  0003      |  Book3    | A02       | Tolkien   |
|------------|-----------|-----------|-----------|

This is in 1NF and the PK is {ISBN, AuthorID}.

To go to 2NF we remove the partial dependencies ISBN->Title and AuthorID->AuthorName and end up with:

BOOK_AUTHOR (ISBN, AuthorID)
BOOK (ISBN, Title)
AUTHOR (AuthorID, AuthorName)

Now we have two real "entity" tables, BOOK and AUTHOR, plus the artificial bridge entity BOOK_AUTHOR.  And we got there just by going to 2NF.

However, if I had started in a different manner and made an ER with BOOK and AUTHOR and a M:M relationship between them, I'd have had to create the artificial BOOK_AUTHOR table myself, which happened above automatically at 2NF.  My question: does it always happen at 2NF, or do you sometimes need to get to 3NF to create the bridge?

However, now that I spell my own question out more I think I see a huge error in it.  I was asking at which xNF does the bridge get created, but the fact of the matter is that my 1NF example above is nothing more than a massive bridge.  Going to 2NF doesn't "create the bridge"... it's more like it creates the land on either side of the bridge by pulling the real entities out of the bogus huge table.  So going to 2NF (and higher) seems to be less about bridging and more about removing data redundancies (which is of course how it was presented to me in the first place)!


texts: I was RDP to one of my servers (SQL Server 2012 (SP4) Std Ed) and saw that the Server Roles had a red symbol (not a red x), but when I connect to that server through a VPN using MSSQL that red symbol doesn't show.  I have been through MS knowledgebases and no explanation what so ever. Anybody knows what it means and how to remove this?


texts: Best Design for this "Sweepstakes" Project?

texts:
Hello there fellow developers,

Merry Christmas first aff all, and i hope you will have a happy new year soon. :)

I was having more or less a problem with an idea that came to my mind.
I am currently making a Project. We are going to call this project for this threads sake "Sweepstakes Project". So in this "Sweepstakes Project" we got the following database tables:

users | id (INT[11] Auto Increment) | username (VARCHAR[30]) | real_name (VARCHAR[100])
sweepstakes | id (INT[11] Auto Increment) | creator_id (INT[11]) | title (VARCHAR[100])
sweepstakes_items .... DESIGN NOT IMPORTANT ....
sweepstakes_entries .... DESIGN NOT IMPORTANT ....
user_stats | user_id (INT[11]) UNIQUE | sweepstakes_entered (MEDIUM_INT[11]) | sweepstakes_won(SMALL_INT[11]) | sweepstakes_created (SMALL_INT[11])

The table NO. 5 is imaginary. It doesn't yet exist, but is the part of my question.

So here is my question: Which of the following 2 systems would you prefer, or do you have a third and better one?

We want when a user reach some specific statistics to give him a badge [Badge table already exists and is fully functional, not needed to include it here].

System 1. Each time a user creates/wins/enters a sweepstake to count all his previous sweepstakes created/won/entered and if he reaches the amount required to give a badge, then award him the badge.

System 2. Each time a user creates/wins/enters a sweepstake to add (++) that created/won/entered sweepstake in the user_stats table. Then check if the user has reached the amount required to give him the badge, and then reward him the badge.

System 1 PROS: * 1 Less query than System 2, * Less tables

System 1 CONS: * Can't keep backups since it will could break the badge system(When the database is backed up, the sweepstakes count will be wrong since it counts only the rows that are in the current table, not the ones that are also backed-up).

System 2 PROS: * Can keep backups -> Can make the DB faster by removing all old unwanted sweepstakes stats.

System 2 CONS: * 1 Extra query, * 1 Extra table, * Will require some extra time for each status update.

What system would you prefer? Do you have an alternative more effiecient way?

Kind regards,
Vasilis Dimitriadis

(Please keep in mind that the database design may not be the best that exists and i would enjoyed if i recieved some feedback on how to make it better too)


texts: Syncing two databases in a redundant network architecture

texts:
I've been reading posts on here about database mirroring and it's an almost overwhelming amount of options and information, but as I'm still new to most of the SQL database scene, I was hoping to find a more definitive answer to my situation:

My company has two 2008 R2 SQL Server databases, which exist on the same network and contain the same information. They are both updated simultaneously as new system data comes in from the automated processes they record, so all the records in both databases match perfectly. The concern in this scenario is that when one database goes down, the other is meant to kick in as the backup, and then when the problematic database is back up, it will be missing the records that were created during down time.

The question:

What would be the best method for retroactively syncing data between the two after one of them has crashed and been brought back online?

As many of the other posts have asked more questions back about what the company will / will not allow per the DBA or other permissions that might exist, I'd like to just state that it is safe to work off the assumption that all other concerns or conflicts have been taken care of, and we purely need to just migrate data to keep each database up to date with the other after a crash. In terms of a master / slave relationship, I don't believe one currently exists, as neither database is getting its data from the other (so long as they are both running); rather, it's coming from a tag-based program that monitors the afore-mentioned automated system

EDIT:

Did some looking into using the Publication / Subscription method through the REPLICATION folder you can find in SSMS and this seems plausible. The options appear as if it allows for immediate and continuous data sharing - would this be a viable option?


texts: Lob Logical Read and lob read-ahead reads in NCCI

texts:
I have a test table with following structure.

CREATE TABLE [dbo].[DW_test](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[CourtCaseID] [int] NOT NULL,
[ActionID] [int] NOT NULL,
PRIMARY KEY CLUSTERED([ID] ASC)

Next I populated my table with about 470 million records with the following script.

insert into DW_test
--select count(*)
--from (
select top 1000000 abs(checksum(newid())) % 100000 + 1 a, abs(checksum(newid())) % 10 + 1 b
from sys.all_objects
cross join sys.all_objects a
cross join sys.all_objects b
cross join sys.all_objects c
cross join sys.all_objects d
cross join sys.all_objects e
cross join sys.all_objects f
cross join sys.all_objects g
--) t
GO

The script was executed about 470 times to generate 470 million records in the table
and created NCCI on that table.

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_test1] ON [dbo].[DW_test]
(
    [CourtCaseID],
    [ActionID]
)

Next I test simple query to count the records in the table.

DBCC DROPCLEANBUFFERS
GO
select COUNT_BIG(*)
from DW_test

If I turn SET STATISTICS IO ON I get the following results

With cold cache

  Table 'DW_test'. Scan count 25, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 469697, lob physical reads 1, lob read-ahead reads 1877324.

  Table 'DW_test'. Segment reads 453, segment skipped 0.

  Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

With warm cache

  Table 'DW_test'. Scan count 25, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 229248, lob physical reads 0, lob read-ahead reads 0.

  Table 'DW_test'. Segment reads 453, segment skipped 0.

  Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.`

I know that 1 logical read is a single data page read from buffer pool during query execution and 1 physical read is a single data page read from disk. RedGate tells us that read-ahead read is:

  This number tells us how many of the physical reads were satisfied by SQL Servers ‘Read-ahead’ mechanism.  This is directly tied to physical reads, so if there are no physical reads, you will have 0 for Read-Ahead reads.

In my case I am dealing with lob logical, physical and read ahead reads. I want to understand what this number means in my particular case.

How it is possible to have only 1 lob physical read with cold cache if the table has about 470 million records?

How it is possible the total number of lob pages decreased from about 2.3 million in cold cache to about 220k in warm cache?


texts: Ranking not correct in sql server 2012 full text search

texts:
I am having a table like below:

CREATE TABLE [dbo].[test_table](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NULL,
    [area] [varchar](50) NULL,
    [designation] [varchar](100) NULL,
 CONSTRAINT [PK_test_table] PRIMARY KEY CLUSTERED
(
    [id] ASC
)

id is the primary key in test_table and this table is having full text indexes on name, area and designation columns.

Now I am using a full text query like below:

SELECT test_table.*, ftt.RANK FROM test_table

INNER JOIN

FREETEXTTABLE(test_table, (name,area,designation), 'ranjit trail engineer') as ftt
ON
ftt.[KEY]=test_table.id

ORDER BY ftt.RANK DESC

I get the results like this:

id,    name,           area,          designation ,      rank

84829,"Ranjit Barasa","marine drive","software engineer",24

85323,"Jimmy David","hayes road",   "software engineer", 13

85223, "Arthur Philip","center trail","Quality Engineer",13

As you can see above, row 3 with name as 'Arthur Philip' is having same rank as row 2 with name 'Jimmy David' though row 3 has more matches.(row 3 matched with word 'engineer' and 'trail' but still has same rank as row 2)

Can anyone help on how to solve this issue?


texts: Change Oracle banner without restarting database

texts:
I am removing the banner for Oracle by commenting out this line in SQLNET.ORA:

SEC_USER_AUDIT_ACTION_BANNER=C:\banner.txt

Is there any way to make the change take affect without restarting the database?  I've tried lsnrctl reload, lsnrctl start and lsnrctl stop, restarting the windows TNS service, and removing the banner file.

I'm using Oracle 11.2.0.3 on Windows 7.  The real problem is on a Solaris (SPARC) machine, but I don't have any Solaris boxes to test.


texts: how to identify the usage of an specific sql login

texts:
I'm examining a Database Server which have several sql logins.there is a specific sql login with sysadmin server Role which i can't find where exactly is its usage. On sp_who2 result this login is used with BACKGROUND status which is running commands Like LOG WRITER ,RECOVERY WRITER,LAZY WRITER,XE TIMER,TRACE QUEUE TASK,TRACE QUEUE TASK &c.

Now I want to know where is the usage of this SQL Login, because I want to change its password.


texts:
Does anyone know if DBs in general or in this case a Centos 7.x box running mongoDB would have corruption or any issues for that matter, if Live migrated from one host to another ?

These are hosted on Win2016 Failover Cluster/Hyper-V


texts:
Background: I have designed a solution for Filemaker to input data into a Question Database.

After we are done with inputting data into Filemaker, we will be exporting the data as excel and transferring it into an online system probably running on ASP.NET or PHP

Part of it looks like this:

Knowledge Table:
Knowledge ID (Primary Key),
Description,
Knowledge Type

Method Table:
Method ID (Primary Key),
Step,
Step Knowledge ID

For example. The "Find Acceleration method" Knowledge can have 3 steps:

Find unknown velocity
Find unknown time
Apply acceleration formula

Each of these steps is also a record in the Knowledge Table, i.e., the steps are knowledges in Knowledge Table.

How do I model the double relationship between Knowledge Table and Method Knowledge Step Table?

Currently, I thought of 2 ways:

Knowledge Table::Knowledge ID -- Method Table::Method ID relationship AND Knowledge Table::Knowledge ID --< Method Table::Step Knowledge ID
Creating another instance of Knowledge Table and link the Knowledge ID separately to Method Table: Step Knowledge ID.

I'm really not too sure of what is the best way of doing it.


texts: Changing collation type database-wide in MySQL

texts:
How do I retroactively update the collation type database wide without dropping and recreating it?

I accidentally created a database without specifying UTF8 as the default collation type. I then went and populated it with several gigs of data.

I know the ALTER commands to do this for the database/tables/columns individually...but I have dozens of tables, each with dozens of columns. Is there a simple non-tedious way of converting all these to use UTF8 collation?


texts: postgresql server, can connect with SSL, permission denied

texts:
I installed and used a Postgresql server a while back and have been using it with Postgis.
I would like to use my letsencrypt certificates with the server so i followed this article:
https://medium.com/@pavelevstigneev/postgresql-ssl-with-letsencrypt-b53051eacc22
The problem is that I can't get it work.
If I follow the articles instructions and try to psql a database:

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

Same with outside connections.
In my postgresql.conf i have the follwing lines:
listen_addresses '*'
port = 5432
password_encryption = scram-sha-256
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_prefer_server_ciphers = on

On my pg_hba.conf i have:
hostssl [mydatabasename] [myusername] 0.0.0.0/0 scram-sha-256

I installed postgresql 12 on a ubuntu server.
I followed every step in the article.
I use the certbot/letsencrypt certificates for apache2 and vsftpd
Edit: 20201001
I found in the logfile of postgresql@12-main that this service can't load the ssl certificate due to a permission problem.
Questions I struggle with:

what is the best place to store the .key and .crt file? in the /etc/ssl/certs of in the /etc/postgresql/12/main?
who should be owner of those files and what persmissions should they have?
what is a sensible setup that works, but doesn't make my server vulnerable


texts: Fulltext match() against() returning empty set

texts:
I have a full text indexed table set up with the data Family in it.  There is no White Space and the spelling is correct.  How can I make this query match results as score and display them by score in DESC order? I keep getting empty set and don't know why.

SELECT *, MATCH(MEDIA_TITLE) AGAINST('Family') AS SCORE
FROM MEDIA_DATA_VIDS
WHERE MATCH(MEDIA_TITLE) AGAINST('Family')
ORDER BY SCORE DESC ;


texts: ADD PERIOD FOR SYSTEM_TIME on table failed

texts:
I have:

table with existing data
SQL Server 2016 SP1
SQL Server Management Studio 17.5

I am using the following statement to make a make my table a temporal one:

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
ADD [SysStartTime] DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysStart DEFAULT GETUTCDATE()
   ,[SysEndTime] DATETIME2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysEnd DEFAULT CONVERT(DATETIME2(0), '9999-12-31 23:59:59'),
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]);

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AnalysisCustomRollupsV2JoinGroupsChanges));

The issue:

On my local SQL instance I have many databases; it's very strange that the query runs successfully on some of them, and on some of them it gives me the following error:

  Msg 13542, Level 16, State 0, Line 51 ADD PERIOD FOR SYSTEM_TIME on
  table 'dbo.AnalysisCustomRollupsV2JoinGroups’ failed because there are
  open records with start of period set to a value in the future.

Sometimes, when I am debugging/executing the query, the initial query runs successfully.

I read, that this could be because I have existing data in the table. So, I have change the logic like this:

create buffer table and populated it with all records
delete the records from the original table
create the temporal table
move the records back and drop the buffer table

and again, on some databases it is OK, and on other is not. Trying to resolve the issue I have found the following:

  For the StartDate I’ve specified the current UTC date – this could be
  any date and time that’s not in the future, though note it should be a
  UTC one. If I’d tried to use GETDATE, as I’m currently on British
  Summer Time, I would get the following error: Msg 13542, Level 16,
  State 0, Line 51 ADD PERIOD FOR SYSTEM_TIME on table
  ‘TestAudit.dbo.SomeData’ failed because there are open records with
  start of period set to a value in the future.

What does the above means? I need to change the machine time? Or because my local machine is not on UTC time I am getting this error sometimes?


texts: SQL Server default collation vs database with different collation - potential problems?

texts:
I'm struggling with the design of new database that has to use a different collation (Y Collation) than the SQL Server Instance default collation (X Collation). I'm looking for potential problems.

Temp Tables by default will be created with X Collation so any joins on text data types will fail unless I create temp table with Y collations on columns.

What about SQL Server string functions: REPLACE, SUBSTRING, etc.? Will they always be returned in X collation? What about when I use REPLACE(...) COLLATE Y? Do you see any potential data loss or maybe any different problems?


texts: Database diagram for linked server tables

texts:
I run Microsoft SQL Server Express 64-bit version 14.0.2027.2 (X64) on Windows 10 Pro 10.

I created a linked server connecting tables from a Microsoft Access (mdb) database to Server Objects/Linked Servers on my SQL Server Express instance. All the linked tables seem to be working well.

I would like to create a Database Diagram for the linked tables. Since I am new to SQL Server, as far as I know ER diagram is possible to make only for the local database.

Is there any option how to make an ER diagram for linked server tables?

If not (when diagram is allowed only for native database/tables), is there any possibility to link tables directly into a native SQL Server database and then create diagram for tables?


texts: Oracle TOAD and Team Coding with TFS: Unable to get revisions from TFS

texts:
My company is currently exploring the option of using TOAD as a means to seamlessly allow developers to check-in/check-out code in TFS. This would be seeded by our packages, stored procedures, etc. and put into an Oracle Database project in TFS. We are running a trial version of TOAD 11.6 on a virtual machine. When logging in, I had to create a new workspace and map it to a local folder on that machine. When I attempted to check-out a file, however, I was unable to as it stated it was 'unable to get revision from VCS repository'.

My question is... why is this happening? Is this because I haven't actually pulled down a copy of the project from TFS onto this file structure? Is there a way to do this through TOAD or is it assumed that I would map to a local folder on the machine and pull down a copy of the project beforehand? Does this have to do with me logging in through a VM and connecting? Any ideas?


texts: archiving tables that are related to other tables with FK

texts:
I have a database i need to archive some of its tables leaving always data above 2 years.
some of the tables are related to other tables with FK (about 21 FK tables) , so clearly i cant delete the rows with the PK without deleting the fk related rows.
the process of the archiving was suppose to be inserting the rows to the archiving database and deleting them from the old DB using dynamic SQL. but not with the FK i was wondering what is the best way to handle the archiving of the related tables.
can i do it using dynamic SQL or do i have to compare all the PK IDS from the originals tables and compare them in the related tables move the data from there delete the FK rows and then do the delete for every table?

what is the best approach to deal with that situation?
please let me know if i need to clarify my question.

thank you


texts: Why aren't my Postgres WAL segments (up to the point of turning on archiving) being archived?

texts:
The pg_xlog directory for my master cluster looks like this:

-rw------- 1 postgres postgres  16M Jun 21 21:42 000000010000000000000001
-rw------- 1 postgres postgres  16M Jun 21 22:42 000000010000000000000002
-rw------- 1 postgres postgres  16M Jun 21 23:42 000000010000000000000003
-rw------- 1 postgres postgres  16M Jun 22 00:42 000000010000000000000004
-rw------- 1 postgres postgres  16M Jun 22 01:42 000000010000000000000005
-rw------- 1 postgres postgres  16M Jun 22 01:49 000000010000000000000006
-rw------- 1 postgres postgres  16M Jun 22 01:55 000000010000000000000007
-rw------- 1 postgres postgres  16M Jun 22 02:05 000000010000000000000008
-rw------- 1 postgres postgres  16M Jun 22 02:30 000000010000000000000009
-rw------- 1 postgres postgres  16M Jun 22 02:50 00000001000000000000000A

The pg_xlog/archive_status directory looks like this:

-rw------- 1 postgres postgres 0 Jun 22 01:49 000000010000000000000006.done
-rw------- 1 postgres postgres 0 Jun 22 01:55 000000010000000000000007.done
-rw------- 1 postgres postgres 0 Jun 22 02:05 000000010000000000000008.done
-rw------- 1 postgres postgres 0 Jun 22 02:30 000000010000000000000009.done

When I first turned on archiving 006 was the current segment, but why weren't 001-005 archived anyway? Last time I set up archiving on an existing cluster (which was yesterday - I've been experimenting), all log segments were archived when I turned archiving on, despite the pg_xlog directory containing more than 50 previous log segments.

Relevant settings:

wal_level = hot_standby
archive_mode = on
wal_keep_segments = 2048  # I know, right :)
archive_timeout = 3600
archive_command = 'rsync -aq --delay-updates --ignore-existing %p postgres@db-slave:/mnt/pgsql/'

Note: I did change all of these settings (including was_level, which was minimal previously) when I turned on archiving earlier. Could it be that my wal level wasn't sufficient to warrant archiving, or because there is a mismatch between the prior level and the current level?


texts: What do these statements mean in the MS β exam 70-461 "skills measured" list?

texts:
I have registered for the upcoming beta exam 70-461 entitled Querying Microsoft SQL Server 2012.

I've been reading through the exam overview and found a bunch of descriptions I'm not exactly sure what some of the items mean:

Create and alter DML triggers.

This objective may include but is not limited to: inserted and deleted tables; nested triggers; types of triggers; update functions; handle multiple rows in a session; performance implications of triggers

Query data by using SELECT statements.

This objective may include but is not limited to: use the ranking
function to select top(X) rows for multiple categories in a single
query; write and perform queries efficiently using the new code items
such as synonyms and joins (except, intersect); implement logic which
uses dynamic SQL and system metadata; write efficient, technically
complex SQL queries, including all types of joins versus the use of
derived tables; determine what code may or may not execute based on
the tables provided; given a table with constraints, determine which
statement set would load a table; use and understand different data
access technologies; CASE versus ISNULL versus COALESCE

The problem here I think is with the wording, which I think I'm not able to fully understand.

If someone could clarify these I'd be most grateful.

To put it as an actual question:

What is Microsoft talking about on the above highlighted statements?


texts: Running a TPC-C Benchmark Without sleep() i.e. key-in + think time

texts:
We are running a TPC-C benchmark against a PostgreSQL 9.2 server using JdbcRunner-1.2 implementation. During first few tests we were not getting a smooth (that is, without sudden spikes down to almost 0 from 300 that we got at times) TPS graph even in supposedly steady state. During these tests we had not specified the sleeptimes for transactions (there are different sleep times recommended for different transaction types). So, by default all agents (for example, 100) continuously ran a mix of five transaction types without any sleeps in between. Later, we found out that we could in fact specify the sleep times; and when we did we could see a smoother graph without much variations.

So, the question is, if not specifying the sleep could be the real reason behind the bad results in first few tests?


texts: Viable strategies for rebuilding indexes online in SQL Server 2008 R2 Standard edition?

texts:
We use SQL Server 2008 R2 Standard edition (we can't afford Enterprise edition on all our production database servers) and have to periodically maintain the critical indexes (rebuilding/reorganizing) in our production servers.  The problem is that since we are using Standard edition we don't have access to the rebuild index online option.  If we don't limit access to the database while these critical indexes are rebuilt, we get many timeout errors because the index is essentially disabled during rebuild. I've used scripts such as Ola Hallengren's Index Optimize script, however this doesn't seem to solve my problem.

What I'm thinking to do to work around this problem is instead of rebuilding an index when an index is fragmented, create a separate index copy (i.e. IX_some_name_temp), once that is done, drop the original fragmented index and finally rename the copy index back to the original name.  What I'm hoping is that while the new index is being built Sql Server can use the original (somewhat fragmented index), then after the new temp index is built it can start using that one, then we drop the fragmented index and rename and we're back to the original state for the next time we have to run our scheduled job.

My question is, does this approach make sense?  Will sql server be able to utilize my new temp index copy while I'm dropping the original fragmented index?
Any tips or possibly other strategies are appreciated.


texts: How to remove all emoji from SQL Server table

texts:
How can I remove all the emoji icons from a SQL Server table?

I want to remove all   icons and keep only Hebrew and English Spaces.. letters

In the end I want to keep only Basic Latin (0020—007F) and Hebrew (0590—05FF).


texts: Postgres: Insert distinct values from one table into another table with constrains in place

texts:
I'm trying to insert data from one PostgreSQL 9.3 table to another while removing duplicate entries.

I have two tables containing email addresses.

The main table contains emails and a tag for each email address. The combination (email, tag) must be unique, and for this there is a constraint Unique(email, tag) on it.

The second table get created on the fly from text files which contain only the email address. There are many duplicates there.

I need to import the data from the temp table into the main one without breaking the constraint above. For a certain file containing email addresses the tag is constant.

Table structures:

CREATE TABLE emails (
  email character varying(128),
  tag bigint,
  CONSTRAINT "unique-tag-email" UNIQUE (email, tag) )

and

CREATE TABLE emails_temp (email character varying(128)

Here is my query:

insert into emails(tag,email)
select
 655,t.email
from
 emails_temp as t
where
 not exists ( select email from emails where email = t.email )

Note: 655 is just a tag of a certain group of email addresses.

This is the error I get:

  ERROR: duplicate key value violates unique constraint
  "unique-tag-email" SQL state: 23505 Detail: Key (email,
  tag)=(user@hotmail.com, 655) already exists.

There are indeed, two email addresses user@hotmail.com in the file.

Useless to say, because of this error, there is nothing added in the main table (email).

What am I doing wrong?


texts:
Hi guys when I try to create user for my database I always encounter this kind of error.

This is the sequence of task I done.

I manage to create new login using windows authentication of SQL then I use that login to access SQL Server again then I create my database and set the owner of that database to the login that I was created. After creating the database, I try to create new user and assign the login I use but I was halted by this error.


texts:
I have configured a RMAN script which first takes the backup of the database and then deletes the obsolete backupsets according to the present retention policy.Now is there any possibility of the given scenario

Scenario: The backup process starts and failed abruptly dues to some reason creating incomplete backupset.Then the backup deletion part of script runs and deletes the latest full/working backup(not the one that failed but the one which the script created on the previous run)of the database provided that the retention policy is redundancy 1.Hence I lose the only available/working backup of my database.

Can it happen?


texts:
Since a use case for a mongodb replica set is a base for analyses and learning cases (i.e a read-only database), is it possible/advisable to, in a replica set mongodb network, have an instance running on my laptop that will sync when connected to the net and then act as an offline read only set when not?

To put a point on it,

will this replica set be stable when the laptop is put to sleep/opened/etc?
will the intermittent availability of this set jeopardize an otherwise robust replicated database (say, with one primary, two arbiters and always on secondaries)?

Other questions that touch upon this but don't answer it directly (AFAIK) are

MongoDB - Replica sets - Syncing and DC issue
Sync MongoDB Replica set with Remote Replica Set

In these cases setting the arbiter correctly seems to do the trick, so maybe my crazy idea would work?

Thanks in advance.


texts: Does MySQL run WHERE clause before ORDER BY?

texts:
I have this query:

SELECT my_code
FROM ranges
WHERE 123456789 BETWEEN first_number AND last_number
ORDER BY type_of_code ASC LIMIT 1 ;

My question is, will it run the ORDER BY after it has selected rows from the WHERE clause?

I have KEY on first_number and on last_number. My table is around 5 million rows. I do not wish to ORDER BY first.


texts: Problems Calculating and Grouping Results Accurately with timediff

texts:
Query Function
 time_to_sec (TIMEDIFF(ohrm_attendance_record.punch_out_user_time,ohrm_attendance_record.punch_in_user_time))/3600 AS time_to_sec_TIMEDIFF_ohrm

Results should total to 4.35 Right? but for some reason I cannot get a group total for the records when I use that query function and I would like to get the group total for those results.

The closest I can come to the right total is with this next query function:  It at least does total the records but goes not give me the desired result:

CAST(SUM(timediff(punch_out_user_time,punch_in_user_time))AS time) AS totalhours   This comes up with a total of 04:21:00

I surely need to get this stuff calculated so I can report payable time accurately.

Im Stumped. P.S Any Suggestions?
Here are some results from first query.
The Calc:
time_to_sec (TIMEDIFF(ohrm_attendance_record.punch_out_user_time,ohrm_attendance_record.punch_in_user_time))/3600 AS time_to_sec_TIMEDIFF_ohrm

The Results:

6   brk ADJ- Time showed 9:18  Adjusted to 8:53.
Updated by Steve - Please be sure to verify that you punched out successfully and keep a written timesheet as backup. 1st Occurance.
    continue inputting  2015-07-11 06:48:00 2015-07-11 08:53:00 6   2.0833
6   brk Continue inputting  2015-07-11 09:22:00 2015-07-11 10:23:00 6   1.0167
6   brk continue lahaina divers     2015-07-11 10:32:00 2015-07-11 10:46:00 6   0.2333
6   stop for now, still need to continue with lahaina divers drift divers   continue inputting lahaina divers   2015-07-11 11:00:00 2015-07-11 12:01:00 6   1.0167

Here are some more results:  The Calc
 CAST(SUM(timediff(punch_out_user_time,punch_in_user_time))AS time) AS totalhours

The Results
6   brk ADJ- Time showed 9:18  Adjusted to 8:53.
Updated by Steve - Please be sure to verify that you punched out successfully and keep a written timesheet as backup. 1st Occurance.
    continue inputting  2015-07-11 06:48:00 2015-07-11 08:53:00 6   04:21:00
Thats it.
Help!


texts: I have to do some updates on myisam tables which have around 20 M records. Our server has 24GB of ram and 2 quad core xeon with 24 MB of cache. I want to know how I can change the my.ini file so I get the best performance. I have windows 7 64 bit.


texts: Quality Control: Check file names in a folder against filenames in a table

texts:
I have a records system in an Oracle database. The records system is used to retrieve pdf, jpeg, video documents, etc.

The design is quite simple: there is a table called infrastructure_records that has a column with hyperlinks to files:

+------------+------------------------------------------+
| project_id |                file_path                 |
+------------+------------------------------------------+
| Project 1  | X:\Records_System\Folder A\Project 1.pdf |
| Project 2  | X:\Records_System\Folder B\Project 2.jpg |
| Project 3  | X:\Records_System\Folder C\Project 3.mpg |
+------------+------------------------------------------+

We use a front-end that creates a clickable hyperlink out of the file paths. Using the hyperlink, users can navigate to the files.

Question:

I want to check the files on the network drive against the list of files in the database table.

If I could get a list of files from the network into a database table, then I could easily query for errors.

However, I'm not sure what the best way would be to create the list of files in an Oracle table.

There are approximately 60,000 files on the network drive, nestled in a variety of subfolders
The files don't exist on the same server as the Oracle database (I'm not an I.T. guy, so I'm pretty clueless about this sort of thing). However, I do have the drive mapped in Windows Explorer on the computer that I would be running the query from.
This QC operation would be run about once per month.
Performance isn't a huge concern. The list of files doesn't need to be "live", although that would certainly be a plus.
I can think of quick-and-dirty ways to do this using VBA scripting in MS Office or the like. But, it has occurred to me that there might be a more professional or out-of-box way to do this (perhaps using built-in Oracle functionality).

How can a I elegantly insert a list of files on a network drive into an Oracle table?


texts: How to display Oracle Enterprise Manager 11g (OEM) alerts and reports in Nagios?

texts:
I am working on a project that requires an unobtrusive integration of OEM data into a Nagios front-end.  I know there are ways to use SNMP traps to do this, but I am not completely familiar with how SNMP traps work.  I came across the following:

http://snmptt.sourceforge.net/

http://sourceforge.net/projects/nagtrap/

If anyone has experience displaying OEM source alert and monitoring data in Nagios (or another tool) via SNMP or API, please provide some examples and resources.

Thanks!


texts:
CREATE TABLE IF NOT EXISTS `usuarios` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user1` varchar(255) DEFAULT NULL,
  `user2` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `usuarios` (`id`, `user1`, `user2`) VALUES
(1, 'pepe', NULL),
(2, 'pepe', 'juan'),
(3, 'juan', NULL),
(4, 'juan', NULL),
(5, 'juan', 'pepe'),
(6, NULL, 'pepe'),
(7, 'pepe', 'juan');

I need to make a query: I have done this:

SELECT
`user1`,
COUNT(`id`) AS Total
FROM usuarios
WHERE (`user1` is not null)
GROUP BY `user1`
UNION
SELECT
`user2`,
COUNT(`id`) AS Total
FROM usuarios
WHERE (`user2` is not null)
GROUP BY `user2`

But the result is:

user1    total
juan      3
pepe      3
juan      2
pepe      2

I need to remove duplicate names, add up the total and this is the result:

user1    total
juan      5
pepe      5


texts: How to parse query plans to get a count of nested loop operators with unordered prefetch?

texts:
My boss wants me to parse a set of query plans stored in a table and to determine how many nested loop operators with unordered prefetching are present in each plan. I only have around 100 query plans so performance isn't very important. I tried doing it myself but quickly got confused and couldn't make progress.

The structure of the table:

DROP TABLE IF EXISTS dbo.query_plans;

CREATE TABLE dbo.query_plans (
plan_name VARCHAR(100),
query_xml XML
);

I uploaded T-SQL to add three example query plans to the table on pastebin. This is the output that I'm looking for:

╔═════════════╦════════════════╗
║  plan_name  ║ OPERATOR_COUNT ║
╠═════════════╬════════════════╣
║ NO_PREFETCH ║              0 ║
║ 1_PREFETCH  ║              1 ║
║ 2_PREFETCH  ║              2 ║
╚═════════════╩════════════════╝

I can't answer any questions as to why I need to do this. Thanks!


texts: Simple/basic database design problem (with relationships)

texts:
It's been years since I've done anything with SQL databases (and I was never more than a novice). I think I've messed something up and I'm not sure how to fix it.

I have 3 tables:

services (as in cable TV, satellite TV, and streaming TV), with
columns for ID and Name
channels (as in CBS, ESPN, HBO, etc.) with
columns for ID and Name
service_channels with columns for ID,
service_id (foreign key), and channel_ld (foreign key)

Apparently I messed up when creating the relationships because when I go into phpMyAdmin to insert rows, when I click the dropdown for service_id, it lists all the rows in services AND all the rows in channels. The same is true for the channel_id dropdown. Can anyone suggest what I may have done wrong and how to go about fixing it?

EDIT: I'm not sure exactly what info to post about the database. Currently, there's just a little bit of test data in it, so I'll just publish a full export. (I hope that's not a violation or anything.)

-- phpMyAdmin SQL Dump
-- version 4.7.7
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Aug 29, 2018 at 04:11 PM
-- Server version: 10.1.31-MariaDB-cll-lve
-- PHP Version: 5.6.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `cxyz77uc_tvchannels`
--

-- --------------------------------------------------------

--
-- Table structure for table `channels`
--

CREATE TABLE `channels` (
  `ID` int(3) NOT NULL,
  `Name` varchar(24) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `channels`
--

INSERT INTO `channels` (`ID`, `Name`) VALUES
(1, 'ABC'),
(2, 'CBS'),
(3, 'Fox'),
(4, 'NBC'),
(5, 'PBS');

-- --------------------------------------------------------

--
-- Table structure for table `services`
--

CREATE TABLE `services` (
  `ID` int(2) NOT NULL,
  `Name` varchar(48) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `services`
--

INSERT INTO `services` (`ID`, `Name`) VALUES
(1, 'YouTube TV'),
(2, 'Sling Orange'),
(3, 'Sling Blue'),
(4, 'Hulu'),
(5, 'Fubo');

-- --------------------------------------------------------

--
-- Table structure for table `service_channels`
--

CREATE TABLE `service_channels` (
  `ID` int(5) NOT NULL,
  `service_id` int(2) NOT NULL,
  `channel_id` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `channels`
--
ALTER TABLE `channels`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `services`
--
ALTER TABLE `services`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `service_channels`
--
ALTER TABLE `service_channels`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `service_id` (`service_id`),
  ADD KEY `channel_id` (`channel_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `channels`
--
ALTER TABLE `channels`
  MODIFY `ID` int(3) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `services`
--
ALTER TABLE `services`
  MODIFY `ID` int(2) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `service_channels`
--
ALTER TABLE `service_channels`
  MODIFY `ID` int(5) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `service_channels`
--
ALTER TABLE `service_channels`
  ADD CONSTRAINT `service_channels_ibfk_1` FOREIGN KEY (`service_id`) REFERENCES `services` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `service_channels_ibfk_2` FOREIGN KEY (`channel_id`) REFERENCES `channels` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


texts: How to get value of last updated row from a table?

texts:
I want to insert the last updated row into a new table using a trigger?

How to fetch that row if any of the column (value) from that table has been updated or changed?

I am using this code:

CREATE TRIGGER database.tbl1_AFTER_UPDATE
    AFTER UPDATE ON tbl1
FOR EACH ROW
BEGIN
    INSERT INTO tbl2 (c1,c2,c3)
    SELECT c1,c2,c3
    from tbl1;
END

But this is inserting all the rows from tbl1 into tbl2. I want only the updated rows to be inserted into tbl2.

How can I do it?


texts:
I would like to add median to the following query, the median obviously will for each type of bedroom, unittypeid (already in the group by), I tried working with PERCENTILE_CONT but I couldnt figure out how to make it work.
CREATE TABLE #TempListings
(
    ListingId int,
    Price money,
    UnitTypeId int,
    BedroomsAvailable int
)
INSERT INTO #TempListings VALUES(1, 1000, 1, 1)
INSERT INTO #TempListings VALUES(2, 2000, 1, 1)
INSERT INTO #TempListings VALUES(3, 3000, 1, 1)

INSERT INTO #TempListings VALUES(4, 1000, 1, 2)
INSERT INTO #TempListings VALUES(5, 2000, 1, 2)
INSERT INTO #TempListings VALUES(6, 3000, 1, 2)

INSERT INTO #TempListings VALUES(7, 1000, 2, 1)
INSERT INTO #TempListings VALUES(8, 2000, 2, 1)
INSERT INTO #TempListings VALUES(9, 3000, 2, 1)

INSERT INTO #TempListings VALUES(10, 1000, 2, 2)
INSERT INTO #TempListings VALUES(11, 2000, 2, 2)
INSERT INTO #TempListings VALUES(12, 3000, 2, 2)

SELECT BedroomsAvailable,
    COUNT(listingid) AS Count,
    MIN(price) AS MinPrice,
    MAX(price) AS MaxPrice,
    AVG(price) AS AveragePrice,
    STDEV(price) as StandardDeviation,
    UnitTypeId
FROM #TempListings
GROUP BY BedroomsAvailable, UnitTypeId
ORDER BY UnitTypeId

DROP TABLE #TempListings

I am using sqlserver 2019 if it matters


texts: Lost data during PostgreSQL minor version upgrade

texts:
I had PostgreSQL 9.4.5 installed on MacOS which was done using Homebrew. I wanted to update it to the latest minor version of 9.4, so I did this (if I remember correctly):

brew uninstall postgresql
brew install postgresql@9.4

which installed PostgreSQL 9.4.17 fine, however it seems I lost all my data during the process. I saw initdb was run at one point, is it what caused the deletion?

Note: It was not a production database, so nothing was really lost however I was surprised with this outcome, so I would like to know the reason.


texts: Filtering a MySQL table on multiple columns, including one with spatial data

texts:
I have a MySQL table with linestrings:

CREATE TABLE edges (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  geometry linestring NOT NULL,
  type_key varchar(255) NOT NULL,
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at datetime DEFAULT NULL,
  PRIMARY KEY (id),
  KEY type_key (type_key, deleted_at),
  SPATIAL KEY geometry (geometry)
) ENGINE=InnoDB AUTO_INCREMENT=130966 DEFAULT CHARSET=utf8mb4;

I want to plot a subset of them on a map. To do so, I run the following query (the ? are variables):

SELECT *
FROM edges
WHERE deleted_at IS NULL
  AND type_key = ?
  AND MBRIntersects(geometry, LineString(Point(?, ?), Point(?, ?)))

The issue I'm facing, is that MySQL does not allow for multi-column indices that include spatial columns (so the geometry column). It therefore has to choose: either use the type_key index, or use the spatial geometry index.

Which one is more efficient depends on the type_key chosen and the minimum bounding rectangle of the linestring constructed in the query. And in quite some cases, it's not efficient at all to only be able to use one of them: with about 100k rows in this table, it might very well take MySQL more than 500ms to compute that one such query results in 0 rows.

I googled quite a bit on this topic, but could not find any good solutions. Interesting results were:

partitioning (see eg. http://mysql.rjweb.org/doc.php/latlng), but the number of partitions you should make is quite limited (< 100, I read)
not using a spatial column at all: the geometry can be represented by a bounding box with four points. I could add all four as separate columns and add them to the index. That will help on one of them (as they are all inequality constraints), but will still be quite inefficient (because of the other 3)
migrating to other database software, eg. Postgres. I read that Postgres is able to use multiple indices in the same query (https://devcenter.heroku.com/articles/postgresql-indexes), but it's not clear to me how efficient that is. I'm not clear on the alternative: whether a spatial column and other columns can be combined in a single index. I spent some time in setting up a test environment with the same data, but gave up after an hour or so, as it took me too much time.

My question therefore is: how are others solving this issue?


texts: column auto updated after 24 hours in mysql

texts:
i have a table T1 with column :

Name      State             time
mon         1          2014-01-09 11:23:00
tue         0          2014-01-07 14:40:00
wed         2          2014-01-08 09:23:00
thu         0          2014-01-09 12:23:00

Now i want to schedule an event or develop a trigger (if possible) , if any state is 0/2 and its time is more than 24 hours from its current time then it should be updated to 1.

For example in above table, state of Tue is 0 and time is more than 24 hours , so state should be updated to 1 and same applies to wed , but not for thu.

I went through many questions like :

https://stackoverflow.com/questions/17387802/how-to-auto-update-mysql-after-timestamp-field-expierd

https://stackoverflow.com/questions/12421756/how-to-update-table-after-a-certain-time-interval

but i haven't understand yet , how to solve my problem


texts: Oracle sqlldr convert date field with different NLS_DATE_FORMAT

texts:
I want to load data file which contains date fields in this format:

10-AUG-16

but, my oracle NLS_LANG is "Simplified Chinese_China.ZH16GBK", if I specify date format in control file like this:

dd-Month-YY

It will fail, as it expect data like this:

10-八月 -16 // 八月 means august in Chinese

how can I load this data?


texts: Does my table violate normalization rules?

texts:
I am developing an emulator for a game.  My item table looks like this:

-- ----------------------------
-- Table structure for `items`
-- ----------------------------
DROP TABLE IF EXISTS `items`;
CREATE TABLE `items` (
  `guid` int(11) NOT NULL,
  `template` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `position` int(11) NOT NULL,
  `stats` text NOT NULL,
  UNIQUE KEY `guid` (`guid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

so some rows look like this (row is wrapped for readability):

'110', '6797', '1', '6',
     'b6#1#0#0#0d0+1,3ca#2412#0#0#0d0+9234,3cb#1#0#0#0d0+1,3cc#1#0#0#0d0+1,
      3cd#a#0#0#0d0+10,3ce#1#0#0#0d0+1'

As you can see the long mess at the end is only one column - they are stats.  I think this field could be divided into individual stats in more columns.  Would that violate normalization rules? Should columns hold only indivisible attributes?


texts: SQL Server: efficient query with (double) linked servers

texts:
I am DBA of server A (SQL Server 2008R2), linked to server B (SQL Server 2012SP1), linked to server C (not sure which version).
I have no control on server C, and I cannot contact its DBA.  I am not even sure that server C is Microsoft SQL Server, it might be Oracle.

On server B, I can contact the DBA and ask for a view on server C, example:

Create view [dbo].[View_Stuff] as
select * from
openquery (SERVER_C, 'SELECT c1,c2,c3 FROM t1,t2 WHERE t1.x=t2.x')

I would like to do things like:

SELECT * FROM View_Stuff WHERE mykey=27

This view gets a lot of data from server C, because the openquery gets all the content of the table, which is not acceptable. On the other hand, I know that this would extract only one row, and would be very efficient on server C:

SELECT c1,c2,c3 FROM t1,t2 WHERE t1.x=t2.x AND mykey=27

I considered using indexed views, but they cannot work with linked servers.
Mirroring the data is possible, but I really want real time information.

What can I ask to define on server B to be able to get data efficiently and in real time from a query on server A?


texts: Are there any things to consider before granting VIEW SERVER STATE and VIEW DEFINITION to Developers?

texts:
I would like to grant below to permissions to Developers group, on Production SQL Server:
VIEW SERVER STATE
VIEW DEFINITION (server level)

This is done to make them able to query some of dynamic management views and functions, view performance data, as well as see code (definitions) of all stored procedures and functions
Are there any drawbacks or any reasons why this is not good in certain scenarios ?
Any possible unwanted side effects of granting above permissions ?
Second question - if I grant VIEW DEFINITION in master database, that makes it server-level and it doesn't have to be granted in any user database ? Same with SHOWPLAN ?


texts: Select records that fit criteria in another record

texts:
I have a procedure that I want to return only when a second procedure was selected.

Id  Name      Procedure
1  Jim Smith  Procedure1
2  Jim Smith  Procedure2

I would like the first record returned only if Jim Smith had both procedures.


texts: Object with many-to-many relationships with multiple tables

texts:
Here's an example for a use-case that matches what I'm trying to better understand. Say I have 3 objects I need to deal with that are similar in many ways, such as invoice, PO, and receipt. They can all have a number, and amount, etc. They can also have many similar relationships, such as line items, images attached, etc. I can think of a number of ways of modeling this.

Option 1
Make everything separate. Have tables for invoice, invoice_line, invoice_image, then po, po_line, po_image, etc. This means strong referential integrity with foreign keys and least possible number of joins, but a ton of duplication for each table.

Option 2
Have parent document table with common fields and a type field, then have invoice, po, and receipt table have a foreign key to document_id. I can then have a single document_image table. For the lines, there are again some differences but many similarities between all, so could have a document_line table with a foreign key to document_id and invoice_line, po_line, and receipt_line tables with foreign key to document_id. Here, we have less duplication, keep referential integrity with foreign keys, but start having many more joins to get all the info we need. If I have an invoice line item and wanted to get all the info, I'd need to join invoice_line to document_line, invoice, and document.

Option 3
Use separate invoice/po/receipt tables, but for image relationship (or any other) add multiple nullable foreign keys, so image would have invoice_id, po_id, and receipt_id nullable foreign keys. With this we can still enforce referential integrity but we have some fields that will often be useless polluting things, plus we now can't make a necessary field required because they all need to be nullable. We do cut down on duplication.

Option 4
Use separate invoice/po/receipt tables, but for image relationship (or any other) have a type field and fk_id field. This way I don't need to have multiple many-to-many tables so it cuts down on duplication, especially if you have lots of these many-to-many, since it would always be 3 tables each time. I like this option the least because you can't have  a foreign key, I pretty much not even considering it as a valid option.

I'm leaning towards option 1 or 2, I think options 3 or 4 seem like bad design and I'd likely only consider if somebody explained that there are major performance benefits. For option 1, even though there's duplication, you can get around it with code generation, so maybe not so big a deal. But would be interested in knowing if there's a major advantage to breaking it down like option 2.


texts: pt-table-sync - Resync non existing table

texts:
I created my little lab where I can try different setup/scenarios. I have four machines debootsraped/chroot and this is my current layout:

| Master A | < ---- > | Master B |
      |
| Slave C |            | Slave D |

Database is created with one table, replication is working.

Intention of this post it to find out if it is possible to do the following:

I set sql_log_bin=0 and create table called sync on masterA, (to simulate inconsistent state)
then I returned variable sql_log_bin to 1.

Normally when I try to use pt-table-sync this doesn't work (throwing error that table doesn't exists; which is noted on percona site), so I was thinking if I create identical table on slave and try to synchronize that it will work but it doesn't.

Is there a solution to sync it with pt-table-sync, to avoid backing up db on masterB and moving to masterA?


texts:
I have 2 tables, TA and TB:

TA
--------
id  name
1   a
2   b
3   c
4   d
5   e
6   f

TB
--------
id  p_id  name
1   1     a
2   1     b
3   1     c
4   2     a
5   2     b
6   2     d

I need : if p_id = 1 then :

id  name match
1   a    1
2   b    1
3   c    1
4   d    0
5   e    0
6   f    0

If p_id = 2 then :

id  name match
1   a    1
2   b    1
3   c    0
4   d    1
5   e    0
6   f    0


texts: Mysql replication no error and not replicating

texts:
I am getting no errors yet replication is not working. I wonder where I am going wrong?

My master status is

            File: mysql-bin.000002
        Position: 2006432
    Binlog_Do_DB: site3,site2
Binlog_Ignore_DB:

My Slave Status is

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 104.131.119.241
                  Master_User: drupal
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 2006432
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 2006577
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: site3,site2
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2006432
              Relay_Log_Space: 2006879
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)


texts: Can SSDT deploy SQL server service broker objects?

texts: Can SQL Server Data Tools (SSDT) deploy sql server broker objects? (So contacts, message types, services, queues and so on.)


texts: How to convert execution time to a specific format?

texts:
I am working on a report that allows people to print off data for physical signatures.  Part of the requirement is to format the exectuion date in the format DDMONYY (27Aug12).  I can not find a way to set the format of the date in the report.

How can I format the report exectuion time to the format DDMONYY?