texts: How to get the rowid in a TRIGGER for another table in SQLite

texts:
Given

the below schema

CREATE TABLE Item (
    _id         INTEGER      NOT NULL,
    name        NVARCHAR     NOT NULL,
    parent      INTEGER          NULL
        CONSTRAINT fk_Recursive
            REFERENCES Item(_id),
    PRIMARY KEY(_id AUTOINCREMENT)
);
CREATE TABLE Container (
    _id         INTEGER      NOT NULL,
    name        NVARCHAR     NOT NULL,
    root        INTEGER      NOT NULL
        CONSTRAINT fk_Container_root
            REFERENCES Item(_id),
    PRIMARY KEY(_id AUTOINCREMENT)
);

Used in client

e.g. Java equivalent prepared statements with ?s and params of these two statements:

INSERT INTO Item VALUES (1, 'ROOT', NULL);
INSERT INTO Container VALUES (1, 'My Box', 1);

Question

Is it possible to write a trigger (or anything else) without client code (only SQL) which achieves the above results with only

INSERT INTO Container(name) VALUES('My Box');

Note: Of course the client code will retrieve the new rowid for the inserted Container, but I don't care about the new (root) Item, that will be used much later by reading from Container.

The trigger would be along the lines of:

CREATE TRIGGER Rooter
INSTEAD OF INSERT ON Container WHEN (root IS NULL) BEGIN
    SET newRoot = INSERT INTO Item(name, parent) VALUES ('ROOT', NULL);
    INSERT INTO Container VALUES (new._id, new.name, newRoot);
END;

Changing Container.root to NULL and creating an AFTER INSERT TRIGGER shouldn't be an option. But then I guess the same problem of getting the Item's rowid would still stand.


texts: How can I categorize entites by their age in an entity relational diagram?

texts:
I am designing an entity relational diagram and I need to categorize entities in groups by their age.
What I have is an entity of Person and some personal attributes about it. But I want to have information about people based on their age. For example I want to have have groups for people of age 1-10, 11-20, 21-30 and so on.
Is it possible to design something like this in the entity relation diagram, and if yes how, or is it a bad technique and I should implement it in later stages of the development?


texts: what are all possible filters to add to a server-side trace?

texts:
On this question you can see how to set up a filter on your trace.

this code below filters a trace file to capture only events that happen on database 'AdventureWorks'

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @bigintfilter = 1000000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

exec sp_trace_setfilter @TraceID, 35, 0, 6, N'AdventureWorks'

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

from the example above:

1) what is 13?

2) I know that 35 must be database Name.

How can I find what could be the other options (hostname, program, ObjectName, etc?) where are them stored? which system table or view?

Here you can see what filters you have on your trace.

I found this link helpful:
Need sp_trace_setfilter xref list of @columnid parameters


texts: Add Active Directory User For Azure-SQL-DB

texts:
I have an active directory user LDomain\LUser and I want that user to be able to connect to Azure-Sql-DB.  The syntax MS uses is throwing an error.

T-SQL:

CREATE USER [LDomain\LUser] FROM EXTERNAL PROVIDER

Error:

  Principal 'LDomain\LUser' could not be found or this principal type is
  not supported.

I'm only looking for the script to add an AD user - no interface.  I know that the AD user exists in Azure and have confirmed, but the Azure-Sql-DB isn't recognizing it, or this T-SQL is invalid - though this is from their documentation.


texts:
I want to find out if all SQL databases registered in the database XXX on the YYY instance are available on the respective linked server and if they are online or offline.
I have the below Code, however when i run it, it Returns NULL values. What am I doing wrong?

DECLARE @STR_DBServer NVARCHAR(128)
DECLARE @STR_DBName NVARCHAR(128)
DECLARE @state_desc NVARCHAR(10)
DECLARE @table table (Server nvarchar(128), Datenbank nvarchar(128), Status nvarchar(10))

DECLARE CUR1 CURSOR FOR
        SELECT
        STR_DBServer , STR_DBName
        FROM   xxx
        where int_systemID = 1
        order by    STR_DBServer

OPEN CUR1
FETCH NEXT FROM CUR1 INTO @STR_DBServer, @STR_DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    begin try

        exec sys.sp_testlinkedserver @STR_DBServer
        If Exists ( select s.name,
        m.state_desc from master.sys.databases m, master.sys.servers s
        where is_linked= 1)
        insert into @table (Status)
        values
        (@state_desc)

    end try

    begin catch

          print 'Keine verbindung möglich mit '  + '' + @STR_DBName + ' auf ' + @STR_DBServer

    end catch

FETCH NEXT FROM CUR1 INTO @STR_DBServer, @STR_DBName
END
CLOSE CUR1
DEALLOCATE CUR1
select * from @table


texts:
I have a polymorphic users table and an address table the id of which is a foreign key that references the userid.

I am virtually a complete noob to databases.  If I define a user, does this mean that a whole new set of rows will be automatically created in the address table?
The users table is polymorphic as it is only necessary to record the address for one type of user, so it would seem to be wasteful if the above is true.  If it is true, is there a way of making it as efficient as possible?

Thanks


texts:
I have a table (actually a query, but I think it's the same thing) with two
range columns. I need to perform a 'subtraction' on the two ranges. Precisely,
I need Intersection(a, Complement(b)). Since this is not representable with a single
range value, there could be 0, 1, or 2 rows in the output for each input row.
Here's an example:

CREATE TABLE test (some_data VARCHAR, a INT4RANGE, b INT4RANGE);
INSERT INTO test VALUES ('a', '[0, 5)', '[1, 2)');
INSERT INTO test VALUES ('b', '[0, 5)', '[1, 5)');
INSERT INTO test VALUES ('c', '[0, 5)', '[0, 5)');
INSERT INTO test VALUES ('d', '[1, 3)', '[4, 5)');

SELECT * FROM test;
 some_data |   a   |   b
-----------+-------+-------
 a         | [0,5) | [1,2)
 b         | [0,5) | [1,5)
 c         | [0,5) | [0,5)
 d         | [1,3) | [4,5)

And this is the result I want:

SELECT somequery... FROM test;

 some_data |  a-b  |
-----------+-------+
 a         | [0,1) |
 a         | [2,5) |
 b         | [0,1) |
 d         | [1,3) |


texts:
I am testing out MySQL Delayed Replication. But it's not working.

I have a Master DB (5.5) and a Slave DB (5.6) both set up as a normal master/slave setup with statement based replication.

On the Slave I then do:

STOP SLAVE;

CHANGE MASTER TO MASTER_DELAY = 60;

START SLAVE;

In SHOW SLAVE STATUS I can now see it is running 60 seconds behind.

On the Master I do:

DROP TABLE db1.test1;

On the Slave I then do:

STOP SLAVE;
#Confirm the table is still there#
SHOW SLAVE STATUS; (to get the relay_log_file and pos)
SHOW relaylog EVENTS IN 'relay_log_file_name' FROM relay_log_pos;

I then find the entry where the table was dropped:

Log_name           | Pos  | Event_type | Server_id | End_Log_pos | Info
slave_relay.000005 | 1470 | Query      | 5         | 104469      | BEGIN
slave_relay.000005 | 1551 | Query      | 5         | 104601      | use `db1`; UPDATE . . .
slave_relay.000005 | 1683 | xid        | 5         | 104628      | COMMIT /* xid=746 */
slave_relay.000005 | 1710 | Query      | 5         | 104741      | use `db1`; DROP TABLE `test1` /* generated by server */
slave_relay.000005 | 1823 | Rotate     | 7         | 1877        | slave_relay.000006;pos=4

In this case, it was the row with End_log_pos = 104741.

I assume I need to stop ahead of this position, so I do:

START SLAVE UNTIL relay_log_file='slave_relay.000005', relay_log_pos=104740;

I have also tried stopping at (the equivalent of) pos 104627, 104628, 104629 (e.g around the previous COMMIT).

But each time, replication continues until the start of the next relay_log, when it stops.

Am I missing something?


texts: Ora2pg - migration Oracle db into postgreSQL without instalation of Oracle

texts:
I have sql file with structure:create table,insert into table, contrains, etc.... This script was generated in oracle, and my job is tranform this script into compatible script for postgreSQL.

Does anyone know if can I use ora2pg without installing oracle? For example only with files .sql or oracle dmp? Or I must allways  first import sql file into Oracle db and then connect to oracle db in ora2pg config file

Thank you


texts: Snowflake: Creating a table with a column name which is ANSI reserved fails, but adding a column(alter table) same ANSI reserved column name succeeds

texts:
CREATE TABLE test_table (LOCALTIMESTAMP varchar(10))

This query fails with .invalid column definition name 'LOCALTIMESTAMP' (ANSI reserved)

ALTER TABLE test_table ADD COLUMN LOCALTIMESTAMP varchar(10)

This query succeeds.

Any explanation for this behaviour?


texts: DB collation is used for comparison instead of column collation

texts:
I am moving data from one SQL Server to another. lets call them source and destination SQL server.

The source SQL Server has collation SQL_Latin1_General_CP1_CI_AS and the destination has collation Hebrew_CI_AS.

The destination's SQL Server's tables and their columns have collations SQL_Latin1_General_CP1_CI_AS - which is good.

I asked source SQL Server to generate script for moving some tables. When I run that script on the destination SQL Server I get an error:

  Cannot resolve the collation conflict between
  "SQL_Latin1_General_CP1_CI_AS" and "Hebrew_CI_AS" in the equal to
  operation.

Why is it that the collation Hebrew_CI_AS is used (SQL Server Collation) and not the collation of the column?

Thanks


texts: Is it possible to put mdf and ldf to NAS (Synology)

texts: I'm planning to purchase NAS (synology) for personal use. I'm working also as developer, it is possible to put my test database (SQL server data and log) to NAS? This just for personal and for development purpose. Because I have two pc setup on 1st floor and 2nd floor of our house, I just don't want to copy and paste the database every now and then when I go to my pc to another. Or do you have any suggestion?


texts: Moving MySQL Cold Backup to Another DB Server

texts:
I am new to MySQL and want to know the info whether taking a cold backup from source and copying it onto new server with same version require any additional configurations settings to be changed.

As I have gone through few of the tutorials, none of the config changes are required.

Steps I am following

Shutting down source server
copying the backup (/var/lib/mysql).
importing backup to new server (/var/lib/mysql).
Start the new server.

Thanks in advance


texts:
I have a Stored procedure that simply looks for Imported files through a set of other processes. The main SP runs every 5 mins and looks for new files in a directory and imports them into a staging table if there is a newer file.
At this point, if it does import a newly added file, there is another accompanying SP that ETL's the records from staging into another production DB. The ETL (EXEC dbo.ETL) is happening inside the monitoring SP that runs every 5 minutes.
My main question is, can I have the ETL stored Procedure run independently of the monitoring SP?
If an ETL SP takes longer than 5 mins, when the monitoring SP is called again, will it use another connection if the last call 5 mins ago is still running the ETL procedure?
IOW, I want to run a SP (SP #1) every 5 mins that calls other SPs (that may take more or less than 5 mins) without interrupting SP #1


texts: CREATE VIEW joining two tables, only displaying rows from the first table with the most recent date in the second table

texts:
I have multiple tables. My main table has a composite key consisting of a phone number and an order number.

I want to create a VIEW where only the subscriptions with the most recent order date are displayed. I've searched around, but I can't find out how to do this.

My current setup (greatly simplified) is as follows:

+-----------------+       +-----------------+
|  SUBSCRIPTIONS  |       |  ORDERS         |
+-----------------+       +-----------------+
| * phone_number  |\      | * order_number  |
| * order_number  |->---+-|   date          |
|   name          |/      |   merchant_id   |
|   other_stuff   |       |                 |
+-----------------+       +-----------------+

I am trying to create the following table:

+------------------------+
|  latest_subscriptions  |
+------------------------+
| * phone_number         |
| order_number           |
| date                   |
| merchant_id            |
| name                   |
| other_stuff            |
+------------------------+

Edit: I've now managed to create a view using the following query:

CREATE ALGORITHM=MERGE DEFINER=`removed`@`localhost` SQL SECURITY DEFINER VIEW `subscriptions_with_order_data` AS select `subscriptions`.`subscription_id` /*I use this as for linking another table I haven't mentioned here, namely "transactions", where each transaction gets assigned this ID instead of the composite key so it is possible to change the phone number and order number without having to modify the data in the transactions table*/ AS `subscription_id`,`subscriptions`.`phone_number` AS `phone_number`,`subscriptions`.`order_number` AS `order_number`,`orders`.`date` AS `subscription_date`,`orders`.`merchant_id` AS `merchant_id`,`subscriptions`.`name` AS `name` /*Lots of other column names ommitted*/ FROM (`tblprefix_subscriptions` `subscriptions` left join `tblprefix_orders` `orders` on((`orders`.`ordernumber` = `subscriptions`.`ordernumber`)));

However, phone numbers still aren't unique.


texts: How does SQL Server know predicates are correlated?

texts:
While diagnosing SQL Server 2008 R2 queries with poor cardinality estimation (despite simple indexing, up-to-date statistics, etc.) and hence poor query plans, I found a perhaps-related KB article:
FIX: Poor performance when you run a query that contains correlated AND predicates in SQL Server 2008 or in SQL Server 2008 R2 or in SQL Server 2012

I can guess what the KB article means by "correlated", e.g. predicate #2 and predicate #1 largely target the same rows.

But I don't know how SQL Server knows about these correlations. Does a table need a multi-column index containing columns from both predicates? Does SQL use statistics to check whether values from one column are correlated to another? Or is some other method used?

I'm asking this for two reasons:

to determine which of my tables and queries might be improved using this hotfix
to know what I should do in indexing, statistics, etc. to affect #1


texts: Bad performance on SQL server (express) because of indexed view

texts:
We have a SQL server express 2017 with an indexed view (clustered index) to speed up database reading. When we insert/alter data which will change the view, the query runs "for ever" (even longer then creating the whole index) but there is no usage of CPU on the server. If we remove the index, the query finish in less then 1 second.

I have also compared the time to create the index on the express version and on an enterprise version of sql server 2017. The index creation uses 1/3 of the time on the enterprise version, even its running on a 5 year old laptop. The CPU on the laptop will be used 100%. When I create the index on the express edition (on a server with more cores, more memory and faster disks) there is no use of cpu, memory or disk activity.

So the question is, is this because the express edition will limit the use of CPU and disk (as it does with memory, which is max ~1,5 gb)?


texts: Trigger to automatically calculate area when adding polygon in PostGIS

texts:
I need help with writing a trigger to automatically update and insert into a column the area of a polygon being created using PostgreSQL/PostGIS and QGIS.

This is what I have but it's not updating when I add new features.

CREATE OR REPLACE FUNCTION calc_area()
RETURNS trigger AS
$BODY$
BEGIN
NEW.area_ha := ROUND((st_area(NEW.geom::geography)/10000)::numeric,2);
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER area_calculate AFTER INSERT OR UPDATE ON public."Guyana_Coastal_Zone"
    FOR EACH ROW EXECUTE PROCEDURE calc_area();


texts: What problems are solved by splitting street addresses into individual columns?

texts:
We have a team who designs the tables and relations for software developers. In our organization, they are pretty strict about enforcing 3NF normalization - which to be honest, I agree with given the size of our organization and how the needs or our clients change over time. There is only one area I'm not clear about the reasons behind their design decision: addresses.

While this mostly focuses on addresses in the United States, I think this could apply to any country that does this. Each piece of an address gets its own column in the addresses table. For instance, take this gnarly U.S. address:

Attn: Jane Doe
485 1/2 N Smith St SW, APT 300B
Chicago, IL 11111-2222

It would get split up in the database like this:

Street number: 485
Street fraction: 1/2
Street pre-directional: N (North)
Street name: Smith
Street type: ST (Street)
Street post-directional: SW (Southwest)
City: Chicago
State: IL (Illinois)
Zip code: 11111
Zip4 Code: 2222
Country (assumed to be U.S.A.)
Attention: Jane Doe
P.O. Box: NULL
Dwelling type: APT (Apartment)
Dwelling number: 300B

And there would be a few other columns related to rural routes and contract routes. Furthermore, our specific application will likely have a few international addresses in it. The data modelers said they would add columns specific for international addresses, which would be the normal line 1, line 2 fields.

At first I thought this was WAY overboard. Researching online repeatedly refers to using address line 1, 2, 3 and possibly 4, then splitting out city, region and postal code. We do have one use case for our new application where this granularity is beneficial. We have to validate that the user is not creating a duplicate business, and checking the address is one of the validations. We can get it to work with address line 1 and 2, but it would be more difficult.

As for our specific application, we need to store multiple kinds of addresses for businesses and people (physical, mailing, shipping, etc). We might need to generate printable form letters, but that requirement hasn't been discussed so far.

Some other things applications in our organization need to support:

Auditing (with full history tables)
Printing mailing labels
Generating printed forms
Reporting (for national and regional governments)

While our application might not be doing everything that every other application is doing, splitting addresses into multiple components is an enterprise standard where I work. Regardless of whether our application would benefit from it, we are forced to do this.

Semi related StackOverflow question: Where is a good Address Parser which was closed, but illustrates how difficult parsing addresses can be.

In order for me to better understand their design decision, and to sell our client on the idea...

What problems are solved by splitting the street address into individual columns?

Bonus points for anyone who has implemented a system like this, because they ran into problems.


texts: SQLServer Inconsistency between changing values for columns vs variables

texts:
Two similar situations outlined below. The first being table columns where we run a series of calculations across columns a, b and c. We do the same thing using declared variables and the result is different to the answer for columns.

It appears that SQL Server persists the value of the table columns for the duration of the command whereas the values of the variables will change during the command.

In a case where you mix variables and table columns the results are still different - but consistent to the descriptions above.

In other RDBMS's that we use there is no difference between these two. One poignant example of where these behave the same is Sybase.

So why is there a difference? Is there something that can alter this behaviour and make it consistent?

/* Column version */

create table #CalcTest (
  a int not null,
  b int not null,
  c int not null
)

insert into #CalcTest ( a, b, c )
values ( 4, 14, 24 )

update #CalcTest
set  b = a + c,
     a = 5,
     c = b - a

select a, b, c from #CalcTest

--output 5 28 10
--Note further - if you break up the update command into 3 commands c = 23

/* Variable version */

declare @a int, @b int, @c int

select @a = 4, @b = 14, @c = 24

select    @b = @a + @c,
          @a = 5,
          @c = @b - @a

select @a, @b, @c

--output 5 28 23
--Note c is 23 where it was previously 10!


texts: Cloned production SQL Server now Windows Authentication doesn't work

texts:
I needed to create a test environment for one of our applications that uses SQL Server as its data store. Our sysadmin cloned the existing production server to create the test server.

I can connect to SQL Server on the test server using Windows Authentication, but not the production server (I can connect to production using a SQL login). I could connect to the production server using Windows Authentication before it was cloned.

Here is the error message:

  Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)

What can I do to connect using Windows Authentication again?

SQL Server 2008 R2 version: 10.50.2550.0

Some more info...

All of our servers are virtual (using VMware)
This same situation has happened with another production SQL Server. Again, we made a clone of production for a test environment. As with this case, I'm able to connect to that SQL Server using Windows Auth.

Sean brings up a good point about invalid SPNs, but what I don't understand is why SQL Auth. would work if it was cloned improperly?


texts: Service Broker process active for too long in SQL Sever

texts:
Let me acknowledge, I don't know much about database efficiency neither Service Broker as a whole, where I work the dba that used tp handle these kind of things left some weeks ago. The company uses service broker to handle a bunch of requests, constantly. My concern is that there's a query (which is used in a service broker) that pulls data from a queue active for more than six days.

The query is something like:

    RECEIVE TOP (1)
        @vQueueData = CAST([message_body] AS NVARCHAR)
        ,@MessageType = [message_type_name]
    FROM
        broker.WhateverReceiveQueue

When I run sp_whoisactive I get something like this:

Is it normal or would this be affecting database's performance somehow? I have not received any news regarding performing issues, though.

If anyone can help to clear my doubts thanks in advance.


texts: SQL Server Database Mirroring is going to die. How can I guarantee high availability now?

texts:
The Database Mirroring feature of SQL Server is going to die:

  This feature will be removed in a future version of Microsoft SQL
  Server. Avoid using this feature in new development work, and plan to
  modify applications that currently use this feature. Use AlwaysOn
  Availability Groups instead.

Anyone knows when? What are my options for an High Availability (and quick recovery) database?


texts:
We recently tried setting innodb_undo_tablespaces=2 for our mysql 5.7 server. But during the restart the server returned with:

[ERROR] InnoDB: Expected to open 2 undo tablespaces but was able to find only 0 undo tablespaces. Set the innodb_undo_tablespaces parameter to the correct value and retry. Suggested value is 0

It turns out this is already documented :
"The number of innodb_undo_tablespaces must be set prior to initializing InnoDB. Attempting to restart InnoDB with a greater number of undo tablespaces than you specified when you first created the database will result in a failed start and an error stating that InnoDB did not find the expected number of undo tablespaces."

What does "first created the database" mean? Does the documentation mean a fresh installation of mysql where there are no databases within the server? Is there a way to bypass this requirement?


texts: Optimizing real-time Database table in Microsoft SQL server 2008

texts:
We are using a web interface to monitor our jobs state and its started and completed timestamps. We have >200,000 job instances running on each day for different job run-users. We maintain the state of all jobs executed in last 6months in a database table. Database server used in Microsoft sql server 2008. With the growing number of jobs or records in table, time taken for updating the records in a table is also going slower than before.

We wanted to redesign our table and schema to update jobs state in real time scenario without causing any delay to job execution. We are thinking of splitting this single table to multiple tables for each user so that updating one user job state should not cause any delay in updating other user job state. To add a note here: the number of users is less than 25 but the number of jobs for each user is more than 50,000. Also, there is no dependency between each user jobs.

Will this table splitting be a better or an optimized way to get the table update or search faster?
Or Will row locking feature in microsoft sql sever solve this case?
Do we have any best optimized way to handle this case?
Will the index be the reason for slowness?

Table:
Job id [primary key] | Job name | started time | End time | status

We have 2 indexes: one based on job start and end time. Second index is only based on job name.

Thanks.


texts: How does SQL Server determine how much memory to allocate to Data cache

texts:
SQL Server Standard Edition has a limit to how much memory it can use for the data cache. With that in mind, how does Enterprise Edition decide how much memory it can use for the data cache? Lets assume we have 400GB of memory- so much of that will be allocated to the data cache, plan cache and working memory.

How does SQL Server decide how much of this 400GB goes to each respective area?


texts: dropping a table does not delete the directories on the disk

texts:
I am just testing the drop table command in Cassandra 3.11. The sequence is as follows:

Load data worth 10gb
drop the table
on the node,

nodetool flush
nodetool clearsnapshot
nodetool cleanup

Still I see those SSTables directory under the data folder (using default auto_snapshot = true).

select count(*) .. shows ZERO, which is correct.

Why the folders are not getting deleted?


texts: Google search console data - find the newly appeared keyword for the month

texts:
I have a table named sconsole for google searchconsole api query data with the fields specified in the code, I need to find the new keywords (r.query field) appeared each month. I tried using this but it returns zero results. At the moment all the data is saved to the same table. Is there any easy way to do this?

Previously I tried with a very simple query

SELECT DISTINCT s.*
FROM sconsole s
JOIN sconsole t ON (s.site_id = t.site_id AND month(str_to_date(s.month,'%b')) >  month(str_to_date(t.month,'%b')))
where (s.site_id = t.site_id)

It returned an error. the requirement is that the site_id is same and the month is the next month. This way each month should be iterated and returned with the new keywords.


texts: When are InnoDB table index statistics updated?

texts:
In a vanilla build of MySQL 5.5.20, when are InnoDB table index statistics updated?  What events trigger such updates?  I've seen comments suggesting that the following might trigger it:

table is opened for first time
query is run against table
ANALYZE TABLE
size of table changes by some threshold


texts: Postgresql - sequences bigger than bigint

texts:
PostgreSQL sequences (CREATE SEQUENCE) are limited to bigint (which is usually big enough). But in my case I need a bigger sequence. The table will not have this much records, it will be cleaned over time, but the numbers must never be reused. As I am planning this to be used for at least 20 years, there is the need to go bigger than bigint.

I am using NUMERIC(35).

How can I achieve this with less locking as possible?

Is creating two sequences and summing them a good idea?


texts: Querying dates with different timezones at db and application level

texts:
There is a created_at column of datetime type in the order table. In my application I'm storing dates to the db as datetime in UTC. When I bring them back from the db I'm presenting/formating them to the user as 'America/Tijuana'. Hence user input is expected in that timezone.

I'm very new to SQL programming, in fact I'm using the framework's integrated query building methods. Anyway, for "debugging" is what I am manually trying in phpmyadmin

SELECT * FROM `order` WHERE date(`created_at`) LIKE '%somedate%'

It works, but is comparing against UTC datetimes in the db. I want it to compare to 'America/Tijuana'.

I'm trying

SELECT * FROM `order`
  WHERE date(convert_tz(`created_at`, 'UTC', 'America/tijuana')) LIKE '%30%'

which runs, but return 0 values. Am I doing it correctly?


texts: Find last (max) value according to TimeStamp using update method

texts:
I have task to find  values of integer according to last (max) of timestamp (datetime) value.
Because it is so complicated query I am consindering this way to find last value something like this

Pseudo code:

update my_table
    set value=last_value
from my_table
    inner join (
                 select *
                    from (
                    select top(100) percent
                        from
                            ( select ts,last_value,pk_v from tb1
                                    union all
                              select ts,last_value,pk_v from tb2
                              ..
                              ) as temp
                              order by ts
                              ) as temp_order
                            ) as temp on my_table.pk_v=temp.pk_v

The idea is to make sub-query which is ordered by timestamp and then to update table.
In this case, sometime is going to be more values in subquery for one pk_val (primary_key value).

For me now looks like that it is impossible and I am considering to do updates in CURSOR. But before I go on, I would love to hear you option this.

Cleaner question would be: How updates working when in one transaction (select) SQL need to update same row two times ?

*EDIT:1 *
Here is sample with data

create table #Table_To_update
(pk int not null,last_value int)

insert into #Table_To_update
select 1,null
union all
select 2,null
union all
select 3,null

create table #table_as_sub_query
(fk int, value int ,ts datetime)

insert into #table_as_sub_query
select 1,5,'2012-01-01'
union all
select 1,6,'2012-03-01'
union all
select 1,2,'2012-04-01'
union all
select 2,7,'2012-02-01'
union all
select 2,8,'2012-02-05'
union all
select 2,6,'2012-04-01'
union all
select 3,0,'2012-01-01'
union all
select 3,9,'2012-05-05'
union all
select 3,12,'2012-01-01'

/*--This Way I want to update new table with last values --*/
update #Table_To_update
set last_value=table2.value
from #Table_To_update table1
inner join (select
                top(100) percent
                *
            from #table_as_sub_query
                order by ts desc
                ) as table2 on table1.pk=table2.fk


texts: How to pass a newly INSERTed row to a function?

texts:
I have following tables:

 table1:       table2:
| uid | did | | uid | did |
|-----+-----| |-----+-----|
| 1   | 2   | | '1' | 2   |
| 4   | 2   | | '4' | 2   |
| 2   | 3   | | '2' | 3   |
| 1   | 8   | | '1' | 8   |
...

I need to create a trigger that would copy every inserted row from table1 to table2, which is on a different schema.

So I made the following function:

CREATE OR REPLACE FUNCTION schema1."clone to table2"(uid numeric, did numeric)
 RETURNS void
 LANGUAGE sql
AS $function$
BEGIN;
        INSERT INTO schema2."table2" ("uid", "did")
        VALUES(uid::text, did);
END;
$function$
;
;

I have no clue how to pass this function properly to a trigger. After a bit of searching I found this:

CREATE TRIGGER on_insert
AFTER INSERT
ON schema1.table1
FOR EACH ROW
EXECUTE PROCEDURE schema1."clone to table2"(new."uid", new."did")

However it throws a "syntax error near ".", Position: 127". So I suppose I got it completely wrong.

I know views are perfect solution for my problem but I unfortunately can not use them in my scenario. I just need these two tables to be always identical, with a difference that table2 needs to have uid as text type.


texts: Postgresql: Pattern match against array elements?

texts:
Is there any way to do a pattern match against the elements of an array in postgresql (9.4 if the version makes a difference)? I have an aggregate function that, among other things, returns an array of elements, like:

SELECT
   lognum
   ,array_agg(flightnum) as flightnums
FROM logs
GROUP BY lognum;

Where the flightnum field is a varchar containing either a text string or a three-or-four digit number. Now say I want to select all logs that have a flight number starting with an '8' (so '800' or '8000' series flights). My first thought was to do something like this:

SELECT
   *
FROM (
      SELECT
         lognum
         ,array_agg(flightnum) as flightnums
      FROM logs
      GROUP BY
         lognum
     ) s1
WHERE
   '8%' like ANY(flightnums);

But while this doesn't give an error, it also doesn't return any results. I'm guessing that this is because the wild card is on the left of the operator. Of course, turning it around to be:

WHERE ANY(flightnum) like '8%'

gives me a syntax error. So is there any way I can run this query such that I get any rows containing a flight number that starts with an 8 (or whatever)?

Please note that this is a simplified example demonstrating just the section I am having difficulty with.


texts: trigger in MySQL to disallow multiple row deletions at a time on a table

texts:
How do we create a trigger in MySql to disallow deletion of more than 2 rows from (say) employee table by a single query?

delimiter \\
create trigger not_more_than_2_rows
before delete on  employee
begin
declare count int default 0;
if (count>1) then
    signal sqlstate '12345'
    set message_text = 'cannot delete more than 2 rows at a time'
end if;
set count = count+1
end \\
delimiter ;

but this does not seem to work, the count variable is getting initialized for each row.
I could see that one way to overcome this is to initialize variable count once per trigger execution instead of once for each row....

Any other solutions?


texts: updating a hstore with multiple new columns

texts:
I am trying to add 2 new hstore key/value pairs in my postgreSQL database.  Is it possible to do this in one statement or do I need to break it up?  Here is what I have so far and it is not working.

with foo as (
   select
          id,
          new_value_1,
          new_value_2
   from table 3
)
update public.table1
    set hstore = hstore('new_key_1',foo.new_value_1),
        hstore = hstore('new_key_2',foo.new_value_2)
    where id = foo.id

I am getting a error where I cannot call the same row in a update twice in a row.  How would can i insert into the hstore two different values I am selecting in the foo statement above as values with a new key into a hstore of columns that already exist.  I just need to tag the rows so even if the values change the original values are stored for future issue resolution.


texts: Multi-field unique constraint where we include one or another field based on nullity

texts:
Suppose I have four columns

| x | y | z | w |

I would like the following constraint:

If w is NULL, then x, y, z must be unique.
If w is not NULL, then x, y, w must be unique.

Is this possible?


texts: Does altering a table and adding multiple columns rebuild the table multiple times

texts:
I am using MySql 5.6, and have a requirement to add multiple columns to an existing huge table (more than a million rows).

I could use:
Alter table T add column C1 varchar(255);
 Alter table T add column C2 varchar(255);
 Alter table T add column C3 varchar(255);
 Alter table T modify column C varchar(255); -- making it nullable

Or

Alter table T
   add column C1 varchar(255),
   add column C2 varchar(255),
   add column C3 varchar(255),
   modify column C varchar(255); -- making it nullable

As per this page in the MySql docs, these operations will require a table rebuild.

Questions:

Will there be any difference between the above two approaches, in terms of how expensive the operation will be?
What are the best practices when performing such operations?
What does a table rebuild, really mean?
Is there a way to avoid a table rebuild, or make the operation faster: (ALGORITHM=INPLACE) ?


texts:
I am trying to create a non clustered index on a table which is around 500GB.  The table already has a clustered index on it. Since we do not want other transaction to be blocked we tried using ONLINE=ON, however at the beginning of the index build it blocked other OLTP transactions.

When I checked what locks were being held I noticed exclusive table lock on the table and other transactions are getting blocked by this.

Screen shot:

I look at Guidelines for Online Index Operations for exclusions and its not applicable for this table.

Any idea why this is happening?


texts: How can one optimize a query involving a sort and a LIKE?

texts:
Here is the query:

EXPLAIN ANALYZE SELECT
    *
FROM
    "Users" AS "User"
WHERE
    "User"."name" LIKE 'garr%'
AND "User"."id" NOT IN (2449214)
AND "User"."hellbanned" IS NULL
AND "User"."hellbanPostsAfterDate" IS NULL
ORDER BY
    "followerCount" DESC NULLS LAST
LIMIT 3;

Which yields:

Limit  (cost=0.43..3662.92 rows=3 width=1711) (actual time=181.935..2158.898 rows=3 loops=1)
  ->  Index Scan using users_search_followercount_index on "Users" "User"  (cost=0.43..280791.25 rows=230 width=1711) (actual time=181.933..2158.891 rows=3 loops=1)
        Filter: (((name)::text ~~ 'garr%'::text) AND (id <> 2449214))
        Rows Removed by Filter: 29434
Total runtime: 2158.951 ms

The index used is the most optimal I could build:

CREATE INDEX users_search_followercount_index ON "Users" ("followerCount" DESC NULLS LAST) WHERE "hellbanned" IS NULL AND "hellbanPostsAfterDate" IS NULL;

I also tried these:

-- CREATE UNIQUE INDEX users_name_unique_index ON "Users" ("name");
-- CREATE INDEX users_followerCount_index ON "Users" ("followerCount" DESC NULLS LAST);
-- CREATE UNIQUE INDEX users_search_name_followercount_unique_index ON "Users" ("name", "followerCount" DESC NULLS LAST) WHERE "hellbanned" IS NULL AND "hellbanPostsAfterDate" IS NULL; -- N/A
-- CREATE UNIQUE INDEX users_search_followercount_name_unique_index ON "Users" ("followerCount" DESC NULLS LAST, "name") WHERE "hellbanned" IS NULL AND "hellbanPostsAfterDate" IS NULL; -- 5422
-- CREATE INDEX users_name_unique_index ON "Users" ("name") WHERE "hellbanned" IS NULL AND "hellbanPostsAfterDate" IS NULL; -- 5422

But none speed it up.


texts: If there is mandatory participation between 2 entities in an ERD, does this mean neither of them are strong entities?

texts: Say I have entity A with primary key "key_A" and entity B with primary key "key_B”. I also have that key_A is a foreign key in entity B. My business rules state that every instance of A is related to at least 1 instance of B, and every instance of B is related to exactly 1 instance of A. In a situation like this, how would you classify A and B as weak/strong entities?


texts: Why doesn't this invalid subquery raise a syntax error?

texts:
Is there any explanation so as to why SSMS did not raise any compilation error for the below delete query with a subquery that is invalid on its own?

Below is the sequence of steps to reproduce the behavior:

CREATE TABLE [dbo].[delete_test]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nchar](10) NOT NULL
) ON [PRIMARY];

Seed data:

insert dbo.delete_test(name) values(N'a'),(N'b'),(N'c'),(N'd'),(N'e');

Create a backup table:

select id, name
  into dbo.delete_test_backup
  from dbo.delete_test where id > 2;

Below statement should ideally not execute and, even if it executes, should only delete rows with id values that have been backed up :

delete dbo.delete_test where id in (
    select id delete_test_backup
);

The message shows that 5 rows were deleted, but I expected only 3 rows to be deleted:

(5 row(s) affected)

Attempt to execute the sub-query as a standalone fails :

select id delete_test_backup ;

  Invalid column name 'id'

Below one executes as expected :

select id from dbo.delete_test_backup ;

  Output :

id
--
3
4
5


texts: In Change Data Capture (CDC), how to configure retention with success?

texts:
We have CDC enabled on SQL Server 2008 with retention re-configured to a number other than the default 3 days (or something similar).

When we first configured it (with default options), it worked fine. But since the day we changed the retention period from default to something like 25 days, it stopped working correctly. It would run for many hours (15 to 20 hours). So, after a few days we disabled the job (not good, but we had other priorities to deal with than the data retention).

Now after 7 months, the disk is close to getting full. So we need to re-configure it and start purging data. A few days ago, just to test how it works, I ran a test run. I checked the timestamp of the oldest LSN and added a month to it and changed the retention period to something like 8 months. So when the clean up job runs, it should come back with success immediately, as there is no data older than 7 months. But it does not. Its still runs for many hours.

Now I am starting to wonder if the retention period (column) in msdb.dbo.cdc_jobs table works or not. Aaron Bertrand mentioned in his blog that he did not have perfect success with this setting. Maybe this setting is not used by the Clean up job.

Before I run some intensive tests on a Development machine, I wanted to check with you guys.


texts: How to allow in-progress transactions to complete when shutting down PostgreSQL?

texts:
I'm working on automating as much as possible the mechanics of resizing an EBS volume on which we store all databases.  We're going to host a large number of large-but-infrequently-used databases on a single EC2 instance, where the secondary EBS volume hosts PGDATA (aka all the data files), whose configuration we've embedded in this script here.

One of the issues we've run into is that when extending the filesystem using fsadm under a running PostgreSQL, it determines that the target is busy and won't umount the volume.  This is expected.

I would like to shut down the database as gracefully as possible - while I'm fine to kill of open idle connections, I'd rather not kill it in the middle of any in-progress transactions.  I'm hoping to find some kind of mechanism that allows me to block new connections, block any further transactions from existing connections, and allow existing transactions to complete.

As far as I can tell, the most native way to shut down the database is using pg_ctl stop -m, where the -m takes one of three options: smart, fast and immediate.

From my experiments, it appears that

smart will fail if there are any open idle connections (which is too generous for my needs, as my developers may leave long-lived connections open in either their Django app or their PGAdmin consoles).
fast will kill all connections whether active or idle, which seems to set me up to risk corrupting an in-progress transaction

Does anyone have any suggestions for more gracefully shutting down PostgreSQL than killing off all active connections?  Are there SQL commands I can run before firing off pg_ctl stop?


texts: difficulties working with powershell Invoke-SQLCmd

texts:
I've setup a simple sqlcmd script.  Contents below.

  use $(dbname);
  select * from $(tablename)

I'm then running the following Invoke SQLCMD

$SQLCMDVarArr = "dbname='MyDatabase'", "Tablename='MyTable'"

INVOKE-SQLCMD -ServerInstance MyServer\MyInstance -inputFile '\\MyShare\Database\Sprints\csrtemp2\csrtest.sql' -variable $SQLCMDVarArr

And I'm getting the error:

  INVOKE-SQLCMD : Incorrect syntax near 'MyDatabase'.

I pulled the syntax straight from the invoke-sqlcmd get-help examples.  Can anyone tell me what is wrong with this?

Thanks,

as per request this is running on windows Server 2008 R2


texts:
im a bit confused about the event scheduler. First off, it is running and working, but the timezone handling confuses me a bit, as it seems it's not what is documented on events metadata

So to start off, they say, mysql.event table holds event data as well. This table does not exist on our system.
It also says in info schema, starts and last_executed are displayed as event timezone, but that doesnt seem to be the case on our system as well.

starts = reported as 'system' time zone, currntly utc -0400, hence i had to make the start time -4 hours to have it run in utc at start of next month (2019-07-31 20:00:00).

I know i can set the session timezone to utc.
While that works, as soon as you edit the event, the old gets deleted and a new create event is triggered by phpmyadmin ( possibly bug? ).
I also know i can run the commands in sql tab with alter event, but i am not the oly one accessing the system, and anyone could basicly use the event interface to edit the event = back to system timezone

timezone reported after adding is system (utc -4)
on an test run, last executed was in utc ( no problem really, but its not what docs say )

Now docs say mysql.event displays the start used to add the event in utc ( again our system not has this table ) and stores the timezone used to prevent wrong timings when changing timezones

What happens when we possibly change the system timezone at a later point? since info schema reports as system and not -0400 what would happen to our event which right now has a starts time of 31.07.2019 20:00:00?

Say we change to utc, would the starts column update to reflect this change? Would the timezone reflect the change?

I'm afraid our event would then possibly run at 8pm in utc instead of -0400, since timezone in info schema might still be 'system' then.

Anyone has some insight on this?
Don't really want to mess with the global timezone to test this out, since system is in production.

Appreciate any help/insights on this,
Seb


texts: Under what conditions could restore of a huge database get delayed, in SQL server 2016, hosted on VM?

texts:
I have system where my database gets restored every night, and off late the restore is taking a lot longer from past few days.

Here is server config:

ES 2637 XEON @ 3.5 Ghz
Windows virtual server 2012 R2 hosted in a Hyper V environment
RAM: 128 GB
8 CPUs
Ethernet: 500 Kps Throughput
Virtual Memory

Max Size:132,096 MB
Available: 39,395 MB
In Use:   92,701 MB

Version:

  Microsoft SQL Server 2016 (SP2-CU1) (KB4135048) - 13.0.5149.0 (X64)   May 19 2018 09:41:57   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)

Diff : RESTORE DATABASE successfully processed 3897077 pages in 825.165 seconds (36.896 MB/sec).
Full: RESTORE DATABASE successfully processed 51085865 pages in 3174.184 seconds (125.735 MB/sec).

Additional info:

Restores were completing in 3 hours; now, they are taking anywhere from 5 - 9 hours, about 2-3 times as long.
The size of the backup files hasn't changed.
We are currently backing up to a single file.
To the best of my knowledge, there have been no changes to the system that would explain this.

Any ideas how to improve the restore speed ?


texts:
We have a sizeable SQL Server 2016 database (running on Windows 2016 Datacenter). A table was growing exponentially due to a bad SQL process.  The bad SQL process has been resolved, but the specific table is still large, and the database instance grabbed a lot of space on the server.  That is, the database instance's physical size is very large.

We need to reduce the physical size and "shrink database" is not recommended by many because of the fragmentation it causes.  To process this while keeping everything on the same server, is the best thing to:

Deal with specific table

a) Create a script that will build a new table and copy records from original table.  But not copy excessive records created by bad SQL process.
b) Then delete the original table and rename the new table.
Deal with general database

c) Create a script to clone the database schema and use this to create a new database.
d) Then script the data in chunks from the current database to the new database (because data is extensive).
e) Drop the current database and rename the new one.

However, if space is an issue on the server to achieve this, could I:

f) Script the entire database WITH SCHEMA and DATA
g) Drop the current database
h) Use script to rebuild the entire database


texts: Database Modelling of Type, Subtype Relation

texts:
I'm trying to model the type and subtype relationship described here.

The best approach I could come up with would look like this:

CREATE TABLE Card_Types (
       card_type varchar(12) PRIMARY KEY
);

CREATE TABLE Card_Subtypes (
       card_subtype varchar(15) PRIMARY KEY
       card_type varchar(12) REFERENCES Card_Types
       PRIMARY KEY (card_subtype, card_type)
);

CREATE TABLE Card_Characteristics (
       card_name varchar(141) PRIMARY KEY,
       -- more to come?
);

CREATE TABLE Card_Characteristics_Types (
       card_name varchar(141) REFERENCES Card_Characteristics,
       card_type varchar(12) REFERENCES Card_Types,
       PRIMARY KEY (card_name, card_type)
);

CREATE TABLE Card_Characteristics_Subtypes (
       card_name varchar(141) REFERENCES Card_Characteristics,
       card_subtype varchar(15) REFERENCES Card_Subtypes,
       PRIMARY KEY (card_name, card_subtype)
);

Sample data for Card_Subtypes:

| card_subtype | card_type |
|--------------+-----------|
| Human        | Creature  |
| Rogue        | Creature  |
| Werewolf     | Creature  |
| Thopter      | Creature  |

Sample data for Card_Characteristics_Types:

| card_name                     | card_type |
|-------------------------------+-----------|
| Kruin Outlaw                  | Creature  |
| Ornithopter                   | Artifact  |
| Ornithopter                   | Creature  |
| Akroma, Angel of Wrath Avatar | Vanguard  |

Sample data for Card_Characteristics_Subtypes:

| card_name    | card_subtype |
|--------------+--------------|
| Kruin Outlaw | Human        |
| Kruin Outlaw | Rogue        |
| Kruin Outlaw | Werewolf     |
| Ornithopter  | Thopter      |

What I don't like about this model is that sometimes you're encoding the type information in two places. If a card has the subtype Human, then it is clearly a creature, so there is no need to store that information in the Card_Characteristics_Types table.

So I guess I could only write to the types table if the card didn't also have a subtype of that type. But that's a constraint that the user would have to uphold.

I know I could accomplish some of this w/ triggers, but I'm trying to stay as close as possible to the relational model. Is there a better way to model this situation?


texts: Query in SQL SERVER2014 for a report (I need the last ROW of a table)

texts:
I'm using SQL Server 2014 and I have a problem with a query.

[MY TABLES]1

I want to have in my report, ALL the items of the order with ID_Order = 9 that have been delivered. And for the items that have been delivered at two times (Item Code = Art3 for example), I just want to have the last row, that means the last delivery of this Item, with NO repetition.

I already tried these two queries without success:

Attempt #1: DISTINCT

SELECT DISTINCT
    Order.ItemCode, Delivery. Qty, Delivery.ID_Delivery,
    Order.ID_Order
FROM
    Delivery
INNER JOIN
    Order ON Order.ID_Order = Delivery.ID_Order
WHERE
    Order.ID_Order = '9'

Attempt #2: subquery

SELECT *
FROM
    (SELECT
         Order.ItemCode, Delivery.Qty,
     FROM
         Delivery
     INNER JOIN
         Order ON Order.ID_Order = Delivery.ID_Order
     WHERE
         Order.ID_Order = '9')
GROUP BY
    a.ItemCode, a.Qty


texts: Behavior of data in indexes based on fill factor

texts:
Let's say you have a database where the default fill factor is 20.  Whenever data is inserted, does it only create pages filled up to 20%?

From my understanding, when the data is inserted there will be roughly 20% of data in the pages.  When the data is updated, however, it will expand to more than 20% of the index, upto filling it and generating a page split, right?


texts:
I want to create a trigger, which does not delete a customer if his name is Peter. How can I do that?

CREATE TRIGGER trgCUSTOMER ON customer
FOR DELETE
AS
BEGIN
    IF (SELECT name FROM deleted) not like 'Peter'
    BEGIN
        delete from customer where name = (select name from deleted);
    END
END;


texts:
In powershell I use foreach to run a function through a list of targets.

I was wondering that if there is any thing similar to foreach in sql, so I can run the same query through multiple DBs I chose and save the results to one csv file?


texts:
I am trying to merge 4 source db's (one for each region) into a global db. We use ID ranges as primary key on the tables in each region and so the merge should happen nicely. I have a fairly high volume of updates/inserts occurring in these regional db's, about 1MB a second.

However -

The blackhole solution does not quite work for more than two master DB's. I have 4 source DB's and chaining them serially like that would slow down the updates/inserts too much and overall make the system too fragile.
I don't think the stop slave/switch master/start slave loop would work either because that would slow things down too much. All source db's update at the same time.
I can't use MariaDB since it's still in Alpha and this is a production environment.

The source DB's have all the same name and exact same table structure. Although when there are upgrades, we will have situations where the table structures are out of sync for a few days during gradual rollout.

Here is what I tried -

One Slave for each region source master
Each Slave has federated tables that point to tables in the Global DB
Triggers on the replicated table in the slave pass on the change to the Global DB via updates/inserts/deletes on the federated table

Problem here - the update/deletes on the Federated tables in the slave, seem to trigger "select *" within the global db for some reason and are s-l-o-w. Yes, I have the proper indexes all around.

Help!! Any other ideas?


texts: How 'Select * into' works into background?

texts:
Today I came through the weird issue for which I need your help. Basically, I am copying a table(temp_a) into table (temp_b) using below query.

select * into temp_b
from temp_a
where cast(date_from as date)>='2010-01-01'
and cast(date_from as date)<'2018-01-01'

Temp_a table Structure and sample data:
id int primary key,
name  varchar not null,
date_from datetime,
update_time getdate()

Temp_A
ID           name         date_from         update_time
-------------------------------------------------------
1            A            2010-01-01        2010-01-01
2            B            2011-02-02        2011-02-02
3            C            2012-02-02        2012-02-02
4            D            2013-09-09        2013-09-09
5            E            2014-08-06        2014-08-06

But above query results duplicate records in temp_b table.

Temp_B
ID           name         date_from         update_time
-------------------------------------------------------
1            A            2010-01-01        2010-01-01
1            A            2010-01-01        2010-01-01
2            B            2011-02-02        2011-02-02
3            C            2012-02-02        2012-02-02
3            C            2012-02-02        2012-02-02
4            D            2013-09-09        2013-09-09
5            E            2014-08-06        2014-08-06

Can someone please elaborate what would be the reason for duplicate records in destination table when there is no duplicate in the source table.

Or

How "Select * into" works in the background?


texts: How to create 3 columns from 2 columns with SQL query?

texts:
I have the following table, Table1:

Id, Speed, Power
1, 1000, 100
1, 2000, 150
1, 3000, 200
2, 1000, 125
2, 2000, 175
2, 3000, 225

I would like to know SQL syntax to return the following:

Speed, Power600V, Power750V
1000, 100, 125
2000, 150, 175
3000, 200, 225

I tried this:

SELECT Speed, Power as Power600V From Table1 WHERE Id=1
UNION
SELECT Speed, Power as Power750V From Table1 WHERE Id=2

It isn't returning the data the right way though.


texts: Creating logins mapping for a linked server without creating logins for AD users

texts:
We use windows auth, but we don't create logins for particular AD users. We create logins for AD groups and the users have access by being their members.

I noticed that when I try to create a mapping for some AD users (not the groups) using a command like below, besides creating the mapping the DOMAIN\ADlogin login is created.

EXEC master.dbo.sp_addlinkedsrvlogin
  @rmtsrvname = N'RemoteName',
  @locallogin = N'DOMAIN\ADlogin',
  @useself = N'False',
  @rmtuser = N'rmtuser',
  @rmtpassword = N'password'

If I drop the login the mapping will be dropped as well.

DROP LOGIN [DOMAIN\ADlogin]

Is there any way to avoid creating logins but get the mapping for AD users? It sounds for me like it's impossible but maybe somebody has good experience in solving this problem.

  Microsoft SQL Server 2017 (RTM-CU9) (KB4341265) - 14.0.3030.27 (X64)
    Jun 29 2018 18:02:47    Copyright (C) 2017 Microsoft Corporation
    Standard Edition (64-bit) on Windows Server 2016 Standard 10.0
  (Build 14393: )


texts: PostgreSQL 9.6 log shipping replication stuck on startup

texts:
Overview

We have been trying to restore replication between a Master and Replication server for a while, but the individual who had set up has left and we've been working at it for a while. In brief, the issue seems to be that the Replication server starts up and within a few minutes reports postgres: startup process    recovering [FILENAME] and never advances beyond that same FILENAME.

Configuration

Master

pg_hba.conf

local   replication             replicator                      trust
host    replication             replicator    10.1.20.181/32    trust

postgresql.conf

(only active settings related to replication)

wal_level = hot_standby         # minimal, archive, hot_standby, or logical
fsync = on                      # turns forced synchronization on or off
wal_sync_method = fdatasync     # the default is the first option
archive_mode = on               # allows archiving to be done
archive_command = '/bin/true'   # Temporary until replication is working
archive_timeout = 300           # force a logfile segment switch after this
max_wal_senders = 5             # max number of walsender processes
wal_keep_segments = 600         # in logfile segments, 16MB each; 0 disables
max_replication_slots = 3       # max number of replication slots

Replication Server

postgresql.conf

hot_standby = on

recovery.conf

standby_mode = 'on' # enables stand-by (readonly) mode

# Connect to the master postgres server using the replicator user we created.
primary_conninfo = 'host=10.1.20.180 port=5432 user=replicator'
primary_slot_name = 'oh_slot'

# Specifies a trigger file whose presence should cause streaming replication to
# end (i.e., failover).
trigger_file = '/tmp/pg_failover_trigger'

Replication Set up Process

On the Master, we clear out all old WAL files by using pg_controldata to find the current WAL file and delete anything older

On the Replication Server:

Stop the database
Unmount pg_xlog directory
Delete contents of data directory
Run pg_basebackup -Xs -U replicator -h #{SOURCE_DB} -D /var/lib/postgresql/9.6/main -v
Copy recovery.conf into new data directory
Move the pg_xlog directory, create a new one and mount it, then copy the archive files over to the new directory and fix permissions
Start up the database

At this point, I've seen the startup process process a number of WAL files, but it always (and rather quickly -- like within a few minutes) stops and continually reports that it is recovering the same file indefinitely. Checking tables between the two databases confirms that the replication database hasn't added new records that are in the master database.

I get the feeling that I am missing something fundamental and would greatly appreciate questions and guidance.

Updates

Luan Huynh: I had the false impression that there were no errors upon startup, but the log has the following 2017-04-16 06:38:32 EDT [13029-1] FATAL:  could not start WAL streaming: ERROR:  replication slot "oh_slot" does not exist; so clearly I did not create the replication slot on the Master as I thought I had.

KookieMonster: I had found advice to set the archive_command to true as a debugging technique. $FILENAME is the last of the files in the pg_xlog directory as it had been copied during the pg_basebackup. The Master indeed has additional WAL files that were created after $FILENAME, but the error in the log seems to clearly indicate why the system has not been been moving over the WAL files created after the pg_basebackup.

I'll verify the replication slot on the master and update.


texts: How to Resolve Corruption Detected by BACKUP WITH CHECKSUM, But NOT DBCC CHECKDB?

texts:
Recently, I was asked to refresh a couple of databases on a test server from production.  Always wanting to follow best practices, I performed the backup using the WITH CHECKSUM option, and eventually got the following error:

Msg 3043, Level 16, State 1, Line 32
BACKUP 'DBName' detected an error on page (1:428321) in file 'F:\path\DBName.mdf'.
Msg 3013, Level 16, State 1, Line 32
BACKUP DATABASE is terminating abnormally.

Surprised at the error, I ran DBCC CHECKDB to see what's going on, but unfortunately (or perhaps fortunately), it comes back clean.

All of the information I have found so far encourages the use of DBCC CHECKDB's findings as a guide on where to go next, so I'm not sure how to fix this?

Some quick info:

SQL Server is 2008 R2.
I found confirmation of the error in the msdb.dbo.suspect_pages table.  It contains event types 1 and 3.
The DBCC command I used was DBCC CHECKDB (DBName) WITH NO_INFOMSGS, ALL_ERRORMSGS;
I am not the official DBA, but since I have some experience, I was permitted to perform the backup and restore.
I did read the similar post What types of corruption can DBCC CheckDB miss?.  However, while I believe I understand why (the point of that post), I don't know how to resolve it.
I was able to successfully perform the backup by omitting the WITH CHECKSUM option.

Thanks in advance for any guidance!

UPDATE

The comment provided by "usr" below got me on the right track.  Just what exactly does page (1:428321) contain?  I did some more research and found the DBCC PAGE command.  However, I noticed something odd.  When I executed the following...

DBCC TRACEON (3604);
DBCC PAGE ('DBName', 1, 428321, 3);

I got the following back (note the page number on the first line)...

PAGE: (1:2021305)

BUFFER:

BUF @0x0000000CFEFCC580

bpage = 0x0000000CFE764000           bhash = 0x0000000000000000           bpageno = (1:428321)
bdbid = 18                           breferences = 0                      bcputicks = 0
bsampleCount = 0                     bUse1 = 20204                        bstat = 0xc00809
blog = 0xbcca2159                    bnext = 0x0000000000000000

PAGE HEADER:

Page @0x0000000CFE764000

m_pageId = (1:2021305)               m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 5313   m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594386120704                                 Metadata: PartitionId = 0
Metadata: IndexId = -1               Metadata: ObjectId = 0               m_prevPage = (1:2021304)
m_nextPage = (1:2021306)             pminlen = 194                        m_slotCnt = 10
m_freeCnt = 228                      m_freeData = 7944                    m_reservedCnt = 0
m_lsn = (121329:238172:13)           m_xactReserved = 0                   m_xdesId = (0:0)
m_ghostRecCnt = 0                    m_tornBits = 297819981

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED
PFS (1:420576) = 0x40 ALLOCATED   0_PCT_FULL                              DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Page 2021305???  Didn't I enter 428321?  If I simply run the command with the page before it (428320), I get that page.  So I guess I'm getting closer, but still don't know what that page contains.


texts: How to see last actions executed (and time) of a specific database in MongoDB?

texts:
There are multiple databases but some of them look stale. I need to understand if the database is stale or not.

How can I get the last actions executed of a specific database in MongoDB? And can I get the date and time information of the last actions?


texts:
Hi I'm trying to execute an SQL File in a Exception Statement when I'm executing an Spool file.

I have something like this:

column dt new_value _dt
select to_char(sysdate,'yyyymmdd_hh24mi') dt from dual;
set line 10000;
set pagesize 50000;
set serveroutput on;

spool .\backup\backup.sql

select dbms_metadata.get_ddl(object_type, object_name)
from user_objects
where object_type in ('FUNCTION')
and object_name = 'TEST_TABLE';

Spool Off

spool .\!Run_&_dt..txt

BEGIN
    raise_application_error( -20001, 'This is a custom error' );
EXCEPTION
    WHEN OTHERS THEN
        Prompt ./backup/backup.sql
        @ ./backup/backup.sql
END;

Spool Off

And I get the next error:

>> BEGIN
     raise_application_error( -20001, 'This is a custom error' );
EXCEPTION
    WHEN OTHERS THEN
        Prompt ./backup/backup.sql
        @ ./backup/backup.sql
END;

Spool Off
Error at line 18
ORA-06550: line 6, column 11:
PLS-00103: Encountered the symbol "/" when expecting one of the following:

    <an identifier> <a double-quoted delimited-identifier>
    current delete exists prior
ORA-06550: line 6, column 49:
PLS-00103: Encountered the symbol "SQL" when expecting one of the following:

    <an identifier> <a double-quoted delimited-identifier>
    current delete exists prior

I don't have any idea, thanks in advance.

Best regards.


texts: What's the best way to structure logging application data in MySQL

texts:
I may be overthinking this but:

I have an API; I want to log calls to the API for stat/debugging purposes. Ideally the speed of reads should be quick but is not essential (as will primarily be read by me). However, the speed of writes should be good as I don't want to slow the API down.

We'll be starting off from scratch, but the API (and logging) will last years. The older the data gets, the less useful it is. At some point we might want to drop the old data entirely (say, dropping data older than three years).

Running MySql 5.5 at the moment.

What's the best way to structure the table? Partition it by year and just make up a load of future partitions?

PARTITION BY RANGE( YEAR(date) ) (
    PARTITION p0 VALUES LESS THAN (2016),
    PARTITION p1 VALUES LESS THAN (2017),
    PARTITION p2 VALUES LESS THAN (2018),
    PARTITION p3 VALUES LESS THAN (2019),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

Or is there a way to create automatic rolling partitions?

Or do I just index on year?


texts: Migration of oracle database from rhel5 to rhel6

texts:
I currently have oracle 11G running on RHEL5 on one server. (This Server has reached END of Life and no hardware support for it)

This server is connected with a Storage and ORACLE_HOME is on storage.

So Now I've a brand new machine with RHEL6 & I want to migrate that full oracle database to this machine.

So what is best feasible way.

I am thinking of RMAN backup and restore it on this new machine.  <-- This works

But If I connect that storage to new RHEL server what is best way (may be using control files or changes in Pfile or SPFILE can help) (then its just mapping of LUN task and connect to default oracle DB instance)

I don't want full procedure, just few ideas from SO users who faced this scenario and can share their best solution.


texts: How to upgrade SQL Server 2008 to 2012 with table partition?

texts: My project is upgrading from SQL Server 2008 to 2012, but old version consisting table partition. is this feature will work same as in 2012 also ?


texts: Single Identity column and composite key, which to make primary?

texts:
I've got a table that's a working copy subset of a base table. The base table has a composite key with a clustered index.  However I need a single column unique identifier for my framework to work with the table. Both keys are completely unique, and I'll need to make a clustered, unique index on the original, composite key.

Should I keep the composite key as a clustered primary key, or should I use the single column identifier as a non-clustered primary key? Though my framework requires a single identifying column I don't actually have to specify it as the primary key in the DB layer, so I'm not sure which is preferable.

I'm using SQL Server 2005 and the single column identifier will be an IDENTITY.


texts: How to best handle MAX within a query/view

texts:
I have two tables named "Visit" and "VisitMovement" and one Visit can contain many VisitMovements.

Within my query I need to join to the latest record for the Visit in VisitMovement so I can get back the latest VisitMovement details.

To do this I have a view like this that I join to:

SELECT
    VisitID,
    max(VisitMovementID) as VisitMovementID
FROM
    dbo.VisitMovement
GROUP BY
    VisitID

and finish up with:

    SELECT
        ......
        FROM
            Visit V
        INNER JOIN VisitMovement VM ON
            V.VisitID = VM.VisitID
        INNER JOIN vwLatestVisitMovement LVM on
            VM.VisitMovementID = LVM.VisitMovementID

I can't Index this View due to the max operator, and I can see over-time this becoming a performance issue with the Index Scan operation.

I wondered if anyone had any good ideas on how to approach this? I was thinking of having a trigger and maintaining the latest VisitMovementID in the Visit table but this doesn't sit well with me.


texts: grant select on all databases except one mysql

texts:
I have user called test_user created under MySQL 5.5.17 under MS Windows 2008R2, I want to grant this user select privileges on all databases except MySQL database, note I have around 200 database inside this instance.

EDIT:

EDIT2:


texts:
Regarding SQL Server virtualization, been trying to find info if there is a positive performance impact on separating Data devices from Log devices into different Paravirtual SCSI (PVSCSI) Adapters, similar to what is done here.

There has been a scenario on a client where an additional PVSCSI was added and log devices were separated to the new PVSCSI, showing considerable performance gains. Yet, the doubt remains if it was due to this separation or simply due to the fact that an additional PVSCSI was now present.

As it is known, Log disks typically are written in a sequential manner, while Data disks follow a more random pattern in their r/w, and there are performance benefits on placing these two different kinds of files on separate disks.

But what about the controllers? Is there a benefit also on keeping these different patterns in separate PVSCSI controllers?

Anyone has any insight on this?

Thanks in advance


texts: Replication with MySQL 5.6 as the master and 5.7 as the slave

texts:
When trying to do replication with 5.6 as the master and 5.7 as the slave, after source dump.sql I entered this;

mysql> CHANGE MASTER TO MASTER_HOST="<MASTER_HOSTNAME>", MASTER_USER="slave_user", MASTER_PASSWORD="password", MASTER_AUTO_POSITION = 1;

obviously I changed  to an actual hostname, I got the following error;

ERROR 1805 (HY000): Column count of mysql.slave_relay_log_info is wrong. Expected 9, found 8. The table is probably corrupted

Is this a problem with using 5.6 as the master and 5.7 as the slave?
I have tried the dump several times with the same error. Although is says it's supported here http://dev.mysql.com/doc/refman/5.7/en/replication-compatibility.html


texts:
What is the fastest query in PostgreSQL, which I can use as a validationQuery binding a JNDI resource?
I thought that SELECT 1 is the simplest, but in this document it is said that in PostgreSQL we should use select version(). This is not obvious to me.
I've tried to compare EXPLAIN ANALYZE SELECT 1 and EXPLAIN ANALYZE SELECT version() and still can't see why second one is (or should be) faster.


texts: Can a role be granted roles+privileges in oracle all at the same time?

texts:
I know that you can grant a role:

Other roles
System privileges
Object privileges

But, can you grant to the same role all at once? I mean, for example you grant a role:

2 roles
1 system privilege
1 object privilege

Is this possible?

If so, what is the priority of the grants? Are roles the top priority, or the privileges?


texts:
My question might be dumb...

But anyone please tell me

Does automatic fail over happens in mirroring if the connection between witness server and principle server is lost for some reason.?

Thanks in advance..


texts: What is the meaning of Order preserving Conflict Serializable schedules?

texts:
I was going through the concept of order preservation in Conflict Serializable class and I came across Order preserving conflict serializable (OCSR in short). The following is the definition of OCSR that I found:

  History h is order preserving conflict serializable if h is conflict equivalent to a serial history hs where t,t'∈h:if t occurs completely before t' in h then the same holds in hs.

Following is one example of a schedule that is in OCSR:

  w3(y) c3 w1(x)r2(x) c2 w1(y) c1

But I could not get why this schedule is in OCSR. Because as per my understanding this is the conflict graph t3 --- > t1 ---- > t2

which is showing a serial schedule where t1 comes before t2. But in the original interleaved schedule t2 completely comes before t1. Then how is the given example said to be in OCSR?
Can anyone please help me understand this better?


texts:
I have a situation where a client is using software that writes to a SQL table. The program has been running fine with no issues prior to today.

Today, they began receiving errors inserting NULL into the ID field. Upon going in and looking at the tables, the column "ID" which is an identity field, was set to 'NO' for "is identity".

My question is, how could this change on its own? As far as the client and I am aware, nothing has been changed recently that would cause this.

Could this happen for another reason without manually going in and changing it?


texts: Oracle Enterprise Manager 11g Database Control - Agent Unreachable

texts:
Single instance DB. I can login to Oracle Enterprise Manger (OEM). However,  on the Home Page it says:

Status - Agent Unreachable

Most tasks such as checking RMAN status, adding datafiles, etc. can still be done through the OEM. I do not know how Agent Unreachable impacts the OEM in this case (DB Control)
I checked status of the agent from $ORACLE_HOME/bin/emctl status agent, it showed:

The Agent is not Running

So, I want to start the agent, but running the command $ORACLE_HOME/bin/emctl start agent is not working. The command is not available.
In Oracle docs, it is mentioned that the command emctl start agent should be executed from AGENT_HOME/bin. However, I do not know the AGENT_HOME path.
How can I start the OEM Agent or figure out what the underlying issue is?


texts:
I am running a very long query described below.
It fetches the next needed action for each account on an automation system.

I.e.

SELECT Account.id,
(IFNULL(**Should send message query**,
    IFNULL(**Should check inbox**, NULL))) as nextTask FROM Account

In reality the string of IFNULL's is around 10, each is quite a complicated subquery.

I want to know if MySQL will compute the values for following IFNULL expression if the first is satisfied. That is, if an account should send a message, it shouldn't bother computing the subquery for Should check inbox

Is this how MySQL works?

What's the difference between this and CASE WHEN's

E.g.

CASE WHEN **Should send message** THEN **Should send message**
    WHEN **Should check inbox** THEN **Should check inbox**
END

I just want to get the CPU usage down for this query.


texts:
I am trying to find a way to figure out when was couple of my sql databases were taken offline.

I checked the the logs but could not find any such info and moreover there is no default trace enabled.

I just have a info that earlier there was a DBA who took that offline, but no emails or a written communication as such..

Can we find an info on this?

please suggest, thanks!


texts:
I am trying to import a .sql file using MySQL Workbench and I get this error:

ERROR 1046 (3D000) at line 28: No database selected

I have first created an empty database called with the same name as the .sql file but it doesn't work.
I have also tried to do it with the mysql command client using:

mysqldump -u root database > file.sql

But it says a have an error in my SQL syntax. Furthermore, I don't know the path where I have to set the file.sql.


texts: Using column size much larger than necessary

texts:
I'm creating an SQL Server database with someone else.  One of the tables is small (6 rows) with data that will probably remain constant.  There is a remote possibility that a new row will be added. The table looks something like this:

CREATE TABLE someTable (
    id int primary key identity(1,1) not null,
    name varchar(128) not null unique
    );
INSERT INTO someTable values ('alice', 'bob something', 'charles can dance', 'dugan was here');

I'm looking at the char length of that name column, and I think that its values are probably never going to be larger than, say, 32 characters, maybe not even larger than 24. Is there any advantage to my changing this column to, for example, varchar(32)?

Also, is there any advantage to keeping default column sizes to multiples of 4, 8, 32, etc?


texts:
Suppose I have two tables I want to keep in sync:

create table a(x number, s varchar2(10));
create table b(x number, s varchar2(10));

It's easy to keep B synchronized with A, using triggers such as:

create or replace trigger a_t1
after insert on a
for each row
begin
  insert into b(x,s) values(:new.x,:new.s);
end;

Of course, if I add the inverse trigger:

create or replace trigger b_t1
after insert on b
for each row
begin
  insert into a(x,s) values(:new.x,:new.s);
end;

I will get a mutating table error if I insert into either table (and which of course is the only thing that keeps the triggers from executing in an infinite cycle).

My question:  how can I arrange for these two tables to sync themselves, such that modifications to A are reflected in B, and vice versa, without modifying the applications that are modifying A and B?

This is for a very short-term solution, so no hack is too cheap.

(disclaimer)
I realize the horribleness of what I'm asking!  This is a short-term integration bridge and will go away as soon as integration is complete.
(end disclaimer)


texts:
Has anyone seen this issue. I am using SSMS 2008 (x64 Version   10.0.5768.0) on Win 2008 R2 machine. Management studio crashed and then when I launch it, it doesn't show Object Explorer even when I hit F8 or select it from View. The only time it works is when I select full screen.

Thanks!


texts: Is transaction log lost after backup on SQL Server?

texts:
I have database that has regular nightly backup (full recovery, full backup). I noticed some irregularities in data and wanted to check out transaction log to see what happened. I restored earlier backup on my developer machine and noticed transaction log is empty. So ...

Transaction log is not written along with full backup ?

Do I lose transaction log on a database when I do full backup ( is log truncated on full backup ) ?

If I want to have data backup and transaction logs backup what should I do ?

Current backup script:

BACKUP DATABASE MyDB TO
DISK = @MyFileName
WITH NOFORMAT, INIT,  NAME = N'MyDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,
STATS = 10


texts:
Production SQL Server 2008 performance is far below expectations. Simple single-table queries sometimes are working for 5 seconds in the mid-day compared to 250ms after hours. We cannot find which resource is the bottleneck when load increases.

Usual advices about performance tuning don't help. CPU is not overloaded (about 30%), page life expectancy is about 1.5 hours; IO queues are almost always empty. There is a suspicious record leading sys.dm_os_wait_stats view by wait_time_ms: LATCH_EX. And the largest latch (4 times larger than second largest) is NESTING_TRANSACTION_FULL. Which is described as "internal use only".

Does NESTING_TRANSACTION_FULL statistic mean anything? And could it give us a lead to diagnose problems with its configuration, hardware, application etc?


texts: How do I bypass the typing of `GO` in `sqlcmd`?

texts:
sqlcmd has this most annoying this of having to type GO after every command, how can I get around this?

SELECT 1 AS "foo"
2> GO
foo
-----------
          1
(1 rows affected)

Can I make this like psql...

test=# SELECT 1;
 ?column?
----------
        1
(1 row)


texts: SQL Server 2016- send automatic email after store proceedure executes

texts: Is it possible to have a stored procedure send an email after it executes successfully. I have an alert account set up but dont know how to create a trigger after the procedure completes. very new here


texts: What all files are required for restoration process of SQL Server in Full + Diff + Transactional backup policy

texts:
I am reading about different types of backup policy in sql server. I am not clear with backup policy for Full + Differential + Transactional backiup

My main attention is towards what all files would be required at the time of restoration. As per the diagram given on SQL Authority, it seems like I need all the files i.e.

last full backup file 1 + log file 1.1 + log file 1.2 + differential 1.1 + log file 1.4 + log file 1.5 + Differential 1.2 + ...all log files..

or did i interpret it wrong and I would need
last full backup file + differential 2 + log file 2.1 + log file 2.2....

EDIT:

What is contained in differential backup, is it just mdf + ndf backup or ldf + mdf + ndf backup since last full backup was taken?


texts: SQL Server 2012 Transactional Replication Latency Warning Not Working

texts: I have transactional replication setup on SQL Server 2012.  I have the default alerts setup and they're working with the exception of the alert for latency.  The alert for latency only fires when I have Replication Monitor open.  Is this by design?


texts: MySQL table with identical name multiple times in same database

texts:
I stumbled into a pretty nasty bug into our PROD environment
I was running some alter scripts and it appears it ended up duplicating the table for each column altered.
I've tested with MySQL Workbench, HeidSQL and DBBeaver they all display same duplicated results.
Bear in mind below examples it's just one table, but I have this issue with 4 different tables in the same Database

Any table that appears duplicated now it's also duplicated in the DB backup script.
If I click and drop one, all entries are gone.
When I run the create script, the table is multiplied for each column.
Per question below this the CREATE script, this script is multipled same in the dump script.
If I drop the table, all entries are gone. When run this script, they're all back
DROP TABLE IF EXISTS `employee`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employeeNumber` varchar(45) DEFAULT NULL,
  `firstName` varchar(150) NOT NULL,
  `lastName` varchar(150) DEFAULT NULL,
  `email` varchar(150) NOT NULL,
  `phoneNumber` varchar(20) DEFAULT NULL,
  `address` varchar(250) DEFAULT NULL,
  `userId` int(11) NOT NULL,
  `merchantId` int(11) NOT NULL,
  `departmentId` int(11) NOT NULL,
  `startDate` date NOT NULL,
  `endDate` date DEFAULT NULL,
  `contract` tinyint(4) DEFAULT '0',
  `countryId` int(11) NOT NULL,
  `createdDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifiedDate` datetime DEFAULT NULL,
  `isActive` tinyint(4) NOT NULL DEFAULT '0',
  `contractIssueDate` datetime DEFAULT NULL,
  `visaType` varchar(45) DEFAULT NULL,
  `visaStartDate` datetime DEFAULT NULL,
  `visaEndDate` datetime DEFAULT NULL,
  `knownAs` varchar(45) DEFAULT NULL,
  `dateOfBirth` datetime DEFAULT NULL,
  `ppsNumber` varchar(15) DEFAULT NULL,
  `passOrIdNumber` varchar(45) DEFAULT NULL,
  `nokName` varchar(100) DEFAULT NULL,
  `nokAddress` varchar(200) DEFAULT NULL,
  `nokRelationship` varchar(100) DEFAULT NULL,
  `nokPhoneNumber` varchar(20) DEFAULT NULL,
  `lastModifiedUserId` int(11) DEFAULT NULL,
  `version` int(11) DEFAULT NULL,
  `honorificTitleId` int(11) NOT NULL DEFAULT '0',
  `genderId` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `fk_country_idx` (`countryId`),
  KEY `fk_department_idx` (`departmentId`),
  KEY `fk_gender_idx` (`genderId`),
  KEY `fk_honorificTitle_idx` (`honorificTitleId`),
  KEY `fk_merchant_idx` (`merchantId`),
  CONSTRAINT `fk_country` FOREIGN KEY (`countryId`) REFERENCES `country` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_dep` FOREIGN KEY (`departmentId`) REFERENCES `department` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_gender` FOREIGN KEY (`genderId`) REFERENCES `gender` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_honorificTitle` FOREIGN KEY (`honorificTitleId`) REFERENCES `honorificTitle` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_merchant2` FOREIGN KEY (`merchantId`) REFERENCES `merchant` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

Any help would be greatly appreciated on how to prevent or resolve it.
Thank you


texts: Using SQL Server Snapshot Isolation is it possible to determine a row being read is not the latest?

texts:
I understand that starting a transaction under the SNAPSHOT isolation level means that any subsequent SELECT statement will provide data as it was when the transaction was started.
The following statement, for example, will return data from the table without blocking writers as well as ignoring subsequent changes:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRANSATION

SELECT  col1, col2, [something here to tell me the row is out of date]
FROM    dbo.SomeTable st
WHERE   (st.SomeForeignKey = @SomeValue)

COMMIT TRANSATION

Is there a performant way of knowing whether a record retrieved from this SELECT statement has been modified since the transaction was started (without running the entire thing again)?


texts: Copying a table with its structure, indexes, and associated triggers?

texts:
I have studied this post on Stack Overflow: Fastest way to copy a table in MySQL.

But no answer is copying everything like the table's structure, data, indexes as well as associated triggers.

Is it possible in MySQL? If yes, how can we do this?
Is it also possible to copy triggers from one table to another?


texts: remove Analysis service from a SQL 2014 cluster

texts: Is it possible to uninstall Analysis service from a SQL 2014 cluster. I’ve seen that it cannot be done in 2008/12. I’m hoping that this maybe fixed now


texts: How to get next/previous partition count using window function?

texts:
I'm trying to get the current_count, next_count, and previous_count for date.month partitions with window functions:

WITH tmp1 AS (
SELECT location.city, date.month, date.year
     , COUNT(*) OVER W AS current_count, COUNT(*) OVER W2 AS
count_next
FROM fact, crime, date, location
WHERE fact.location_key = location.location_key and crime.crime_key =
fact.crime_key and fact.date_key = date.date_key

WINDOW W AS (
        PARTITION BY location.city, date.year, date.month
        ORDER BY date.month
    ),

    W2 AS (
        PARTITION BY location.city, date.year, date.month
        ORDER BY date.month
        RANGE BETWEEN CURRENT ROW AND FOLLOWING
    )
)

SELECT *
FROM tmp1
GROUP BY city, year, month,current_count, count_next

However, the code above works for current_count only, the count_next shows the same result as the current_count.

How to show the count for next month and previous month? With window functions?


texts:
I have 3 tables.

BRANDS

CREATE TABLE BRANDS (
    id_brand BIGINT(4) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(25) NOT NULL,
    UNIQUE (name)
)

PRODUCTS

CREATE TABLE PRODUCTS (
    id_product BIGINT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(6) NOT NULL UNIQUE,
    name VARCHAR(30) NOT NULL,
    price FLOAT(6,2) UNSIGNED NOT NULL,
    is_discounted TINYINT(1) NOT NULL,
    discount_percentage INT(2) NOT NULL,
    discounted_price FLOAT(6,2) NOT NULL,
    description TEXT NOT NULL,
    stock INT(4) UNSIGNED NOT NULL,
    photo_1 VARCHAR(50),
    product_type_id BIGINT(4) UNSIGNED NOT NULL,
    brand_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (product_type_id) REFERENCES product_types (id_product_type),
    FOREIGN KEY (brand_id) REFERENCES brands (id_brand),
    is_visible TINYINT(1) NOT NULL
)

PRODUCT SALES

CREATE TABLE PRODUCT_SALES (
    id_product_sale BIGINT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    QUANTITY INT(4) UNSIGNED NOT NULL,
    sale_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    total_price FLOAT(12,2) UNSIGNED NOT NULL,

    user_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (user_id) REFERENCES USERS(id_user),

    product_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (product_id) REFERENCES PRODUCTS(id_product),

    payment_method_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (payment_method_id) REFERENCES PAYMENT_METHODS(id_payment_method),

    sale_status_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (sale_status_id) REFERENCES SALE_STATUS(id_sale_status)

)

I need a query to display the ammount of produces sold per brand.
I should get the following

Intels - 27
Western - 5
GeForce - 10

But i get

Intels - 42

This is how i tried to get the result i need.

SELECT b.name, sum(ps.quantity)
FROM Product_Sales ps
JOIN Products p
ON ps.product_id = p.id_product
JOIN Brands b
ON b.id_brand = p.brand_id

What's wrong with my query? Thanks in advance.


texts: Iterating through a record set and selecting certain records

texts:
I am tasked with automating our item return process at work. Our policy for returns is to take the average price of the most recently purchased items desired to be returned.

We are effectively calculating a weighted average unit price of the most recently purchased items, excluding items from the average price once we have hit the desired return quantity.

I currently have a table in Microsoft SQL Server that contains the purchase history of all items ($, units, and purchase date) that have been asked to be returned, and the desired quantity to be returned.

I'll add a specific example; 18 units of Item number 138799528 are to be returned (NOTE that the table is already ordered by ItemNumber, CreatedDateTime descending). In order to determine the return price, we need the query to start with the most recently purchased item (line 137) and select records until the sum of the POQuantity is equal to or greater than the desired return quantity. In this case, the proper item selection would be rows 137,138 and 139, while excluding the rest of the purchases of Item 138799528. The query continues until it has iterated through all items in the record set.

I am attempting to iterate over this table and select the appropriate records into a another table with which to calculate the average. I have attempted using a cursor but my DBA has insisted to avoid cursors at all costs. Below is the cursor logic:

    DECLARE ReturnCursor CURSOR

        FOR SELECT
                T.ItemNumber
                ,-(T.ReturnQuantity)
                ,T.POQuantity
                ,T.PurchasePrice

            FROM #tableforcalc T
            ORDER BY T.ItemNumber, T.CreatedDateTime DESC           --Added order by so following logic works when looping through records

    OPEN ReturnCursor

        SET @Counter=0
        SET @Item2=''           --Needed to set Item2 = ''

        FETCH NEXT FROM ReturnCursor
            INTO @Item, @ReturnQty, @POQty, @PurchPrice

    WHILE @@FETCH_STATUS=0
    IF @Item2!=@Item
            BEGIN

                SET @Counter+=@POQty ;
                SET @Item2=@Item;

                SELECT

                 @Item AS ID
                ,@POQty AS qty
                ,@PurchPrice AS price
                ,@ReturnQty AS RQ

                --INTO #Temp1               --  Put selected variables into a temp table

            END

        ELSE                --this else condition is essentially  IF @Item2=@Item

            BEGIN

                IF (@Counter<@ReturnQty AND @Counter!=0)

                    BEGIN

                        SET @Counter+= @POQty;
                        SELECT
                            @Item AS ID
                           ,@POQty AS qty
                           ,@PurchPrice AS price
                           ,@ReturnQty AS RQ

                        --INTO #Temp2
                    END

                ELSE

                    BEGIN

                        SET @Counter=0;

                    END

I was wondering if there is a way to solve this problem using set operations in SQL? I am open to all suggestions.


texts: Database convert character_set_server latin1 to utf8

texts:
I am writing about a problem on the character encoding that i have on my site with Magento. In practice, the special characters do not take utf8 and gives me the classic non-coded characters: Ã.

These are the codes in my database:

character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8

My question is ... the problem of encoding is caused by character_set_server latin1 string? How do I convert my database character_set_server latin1 to utf8? It can be a very simple Query?

Thanks so much


texts: Syntax check for creating logins in different SQL versions

texts:
We are trying to write a script to do the following task:

Create a new login and grant datareader to the master database on all SQL Server 2012 and 2014.

The script we have did what we want but for some reason, SSMS is complaining about the syntax saying the user already exists but it doesn't exist.

    use master
    go
    IF EXISTS
    (select * from sys.syslogins
    where loginname =  'login_name')
    (select 1 from sys.syslogins)
    else

    if
    (select  left(cast(SERVERPROPERTY('ProductVersion') as varchar),2)) = '12'
    CREATE LOGIN [login_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    CREATE USER [login_name] FOR LOGIN [login_name]
    ALTER ROLE [db_datareader] ADD MEMBER [login_name]

    if
    (select  left(cast(SERVERPROPERTY('ProductVersion') as varchar),2)) = '11'
    CREATE LOGIN [login_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    CREATE USER [login_name] FOR LOGIN [login_name]
    ALTER ROLE [db_datareader] ADD MEMBER [login_name]


texts:
I have code create table

CREATE TABLE OFPHONE
(
  PHONEID     INTEGER                           NOT NULL,
  HOTLINE     VARCHAR2(20 CHAR),
  EXT         VARCHAR2(20 CHAR),
  PHONE       VARCHAR2(30 CHAR),
  PASSWORD    VARCHAR2(30 BYTE),
  NAME        VARCHAR2(30 BYTE),
  CREATEDATE  TIMESTAMP(6)                      DEFAULT CURRENT_TIMESTAMP
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;

COMMENT ON COLUMN OFPHONE.PASSWORD IS 'Password';

CREATE UNIQUE INDEX OFPHONE_PK ON OFPHONE
(PHONEID)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
CREATE OR REPLACE TRIGGER TRIGGER_OFPHONE_AFTER_INSERT2
AFTER INSERT
ON OFPHONE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
    tmp varchar2(20);

begin

    insert into ofphone_tmp (PHONEID,HOTLINE,EXT,PHONE,PASSWORD,NAME,CREATEDATE)
    values (:new.PHONEID,:new.HOTLINE,:new.EXT,:new.PHONE,:new.PASSWORD,:new.NAME, sysdate);

    insert into OFUSER(USERNAME,PLAINPASSWORD,NAME,EMAIL,CREATIONDATE,MODIFICATIONDATE)
    values(:NEW.PHONE,:NEW.PASSWORD,:NEW.NAME,trim(' ' from :NEW.NAME)||'@localhost',sysdate,sysdate);

    --commit;

   for x in
   (select  phone,name,hotline from ofphone_tmp where hotline = :NEW.hotline and phone <> :new.phone) loop

        insert into ofRoster values (SEQ_OFROSTER.nextval,:new.PHONE,x.phone||'@localhost',3,-1,-1,X.name);

        insert into ofRoster values (SEQ_OFROSTER.nextval,x.phone,:new.PHONE||'@localhost',3,-1,-1,:new.name);

   end loop;

end;
/

I tried debug code snippet

But I checked the details of the value of the block
for cur in (has data)
and select SEQ_OFPHONE (has data)
are valid.

Please tell me this error case.

begin

    for cur in(select
                c.COMPANY_MOBILE,
                e.EMPLOYEE_NAME,
                E.MOBILE_PHONE,
                E.EMPLOYEE_NO,
                E.EMPLOYEE_PWD,
                E.Email
             from TAB_XN_EMPLOYEE e
            inner join TAB_XN_COMPANY c
            on e.COMPANY_ID = c.COMPANY_ID
            and c.COMPANY_MOBILE  = '0912400604'
            ) loop
            insert into ofphone( PHONEID,HOTLINE,EXT,PHONE,PASSWORD,NAME,CREATEDATE)
            values(SEQ_OFPHONE.nextval,cur.COMPANY_MOBILE,cur.EMPLOYEE_NO,
            cur.MOBILE_PHONE,cur.EMPLOYEE_PWD,cur.EMPLOYEE_NAME,sysdate);

    end loop;

end;

Error

  ORA-00001: unique constraint (MEG.OFUSER_PK) violated ORA-06512: at
  "MEG.TRIGGER_OFPHONE_AFTER_INSERT2", line 20 ORA-04088: error during
  execution of trigger 'MEG.TRIGGER_OFPHONE_AFTER_INSERT2' ORA-06512: at
  line 15