texts: Best approach to maintain a single set of DDLs and DMLs for multiple database type

texts:
In our application, we support multiple types of databases namely Oracle, IBM DB2 (both UDB and ZOS), postgresql and So on. Currently we are maintaining separate DDLs and DMLs for each of these different databases.

It is difficult to maintain these multiple set of DDLs/DMLs and moreover in future our application might support few more database type.

What will be the best approach if we want to maintain a single set of DDLs/DMLs?


texts:
Hello I'm trying to implement some sort of fuzzy string matching using pg_trgm on my table which contains a jsonb array,
here is a sample data:

      name
------------------
[{"firstname": "jhon", "lastname": "doe"},{"firstname": "jhonny", "lastname": "doe"}]
------------------
[{"firstname": "jane", "lastname": "doe"},{"firstname": "janne", "lastname": "doe"}]

and this is a sample of my current query:

SELECT * FROM persons WHERE exists(
    SELECT * FROM json_array_elements(persons.name::json)
        WHERE word_similarity('jhon', (COALESCE(value->>'firstname', '') || ' ' || (COALESCE(value->>'lastname', ''))) ) >= 0.7);

It does the job but it's slow(no index).
My question is what can I do to speed up the searching (index, alternative query) ?
TIA


texts: In SQL Server, what's the best way to split multiple delimited strings into rows without a function?

texts:
I know there are numerous examples of how to split strings in SQL Server. However, they are often for a single delimited string. What's the best way to split multiple delimited strings without using a function (SQL Server 2016)?
Here's what I'm starting with:
GroupId     Val1      Val2
----------- --------- ------
G1           1,2,3     a,b,c
G2           4,5,6     d,e,f
G3           1,2,3,4   w,x,y,z

Here's what I would like to end up with:
GroupId Val1    Val2
------- ------- -------
G1      1       a
G1      2       b
G1      3       c
G2      4       d
G2      5       e
G2      6       f
G3      1       w
G3      2       x
G3      3       y
G3      4       z

Based on everything I've read, I think I may need to use the XML method because it allows me to specify an order, but I haven't been able to find an example of this method where multiple columns are split.
Here's the T-SQL to create the source table:
DROP TABLE IF EXISTS #TestData;

CREATE TABLE #TestData(GroupId VARCHAR(10),
                       Val1 VARCHAR(100),
                       Val2 VARCHAR(100)
                       );
INSERT INTO #TestData
VALUES ('G1', '1,2,3', 'a,b,c'),
       ('G2', '4,5,6', 'd,e,f'),
       ('G3', '1,2,3,4', 'w,x,y,z');

SELECT *
FROM #TestData;

Thank you in advance!


texts: Does MySQL exectution plan depend on available indexes?

texts:
Does MySQL choose an execution plan for a given query taking into account what indexes are available, or does it first choose an execution plan and then uses indexes if they are available?

Motivation: I want to decide which indexes would be useful. I have a number of typical queries. So I can imagine two possible strategies:

If the plans do not depend on available indexes: see the execution plans of those queries and add indexes useful for these plans, or
If the plans depend on available indexes: add all possible indexes (a lot!), see the plans for the queries, and remove unused indexes.

My DB is not very large, so I can afford playing with indexes. I currently use InnoDB, but I can switch to MyISAM or another if needed.


texts: Do database servers need load balancing?

texts:
I am trying to build a 1TB database on MySQL which will need strong read performance.  I intend to replicate my database to prevent downtime.

Do my database servers need load balancing to improve performance, or would a memcached setup  be better?

What is the best failover solution for databases?


texts: Best way to increase no of partition in SQL Server Table

texts:
I am maintaining a database with a big big table, which portioned for each month. There is already tens of partition there but its till January 2013, So its time to create new partitions.

Can anyone please advise, what's the best way to create new number of partitions.

I am doing the following step (Yet not did)

From SSMS, get the properties of Database and move to Files tab.
Click Add button on the bottom right to add a new line.
Put a new file name (e.g. FN_DW_Archieve_2013_02)
Put a new Logical Name (e.g. LN_DW_Archieve_2013_02)
Create a new Filegroup by selecting  in the dropdown under filegroup cell.
(e.g. FG_DW_Archieve_2013_02)
Hit Ok

This will create a new file group in my database.

Now the biggest problem which make me worry is to modify the Partition Function and Partition Scheme. Which expert advise, can I drop and recreate them again, does it affect my existing data. Or can I alter them with the new partition in it.

Thanks in advance.


texts:
Currently, I use a query with DISTINCT ON to get a list of recent posts for each author.
But how can I get previous recent posts for each author? In other words, how to make DISTINCT ON return second row of each group, not first.
I need this to compare recent post with previous one.
CREATE TABLE posts (
  title varchar(30),
  author varchar(30),
  created_at date
);

INSERT INTO posts VALUES
('Johns first post', 'John', 'January 1, 2021'),
('Johns second post', 'John', 'January 2, 2021'),
('Johns third post', 'John', 'January 3, 2021'),
('Mikes first post', 'Mike', 'January 1, 2021'),
('Mikes second post', 'Mike', 'January 2, 2021'),
('Mikes third post', 'Mike', 'January 3, 2021');

This query selects the most recent post for each author. (Third post for each in my example.):
SELECT DISTINCT ON (author) * FROM posts ORDER BY author ASC, created_at DESC

db<>fiddle here
I also need the previous post for each author (Second post for each in my example.)
I don't want to use window functions because my table is large enough, and I suppose window functions could be slow.


texts: pgpool connect failed: No such file /tmp.s.PGSQL.0 replication mode = true

texts:
Setup: I have two instances of Postgres 9.0.3 and pg_pool 3.14 all running on the same Ibuntu (10.04) machine and am attempting to evaluate pgpool.

I can attach to pgpool using psql -p <pgpool port> but only when the pgpool.conf file has replication mode = false. If I set the replication mode = true then I see the log file generate the connection failure error on /tmp.s.PGSQL.0 when I attempt the psql connection to pgpool's port.

After more exploration I would note that the additional error messages in the log indicate a failover handler is called, starting degeneration and shutdown secondary host(0) ????

After this if I do not restart pgool I can successfully connect (with replication mode = true) but I do not see any load sharing of my selects between my servers. (I have the load_share variable also set to true.)

What I am attempting to do is have have my insert/update statements replicated across my pool of servers and my selects to be load shared. Seem to be following the suggested setup but I encounter the above error problem.

Comments?


texts: prevent clustered index insert operator on unqualified indexed view

texts:
Does anyone know a workaround for this?  Essentially, the stored procedure forces an insert operator against the indexed view, even though the rows don't qualify.  As a result, there is a cast error.  However, for ad hocs, sql correctly eliminates the view from consideration.

Consider the following schema:

create table testdata (
    testid int identity(1,1) primary key
  , kind varchar(50)
  , data nvarchar(4000))
go
create view integer_testdata with schemabinding
as
select cast(a.data as int) data, a.kind, a.testid
  from dbo.testdata a
 where a.kind = 'integer'
go
create unique clustered index cl_intdata on integer_testdata(data)
go
create procedure insert_testdata
(
    @kind varchar(50)
  , @data nvarchar(4000)
)
as
begin
  insert into testdata (kind, data) values (@kind, @data)
end
go

These all work:

insert into testdata (kind, data) values ('integer', '1234');
insert into testdata (kind, data) values ('integer', 12345);
insert into testdata (kind, data) values ('noninteger', 'noninteger');
exec insert_testdata @kind = 'integer', @data = '123456';
exec insert_testdata @kind = 'integer', @data = 1234567;

This fails:

exec insert_testdata @kind = 'noninteger', @data = 'noninteger';

A comparison of the "estimated execution plans":

insert into testdata (kind, data) values ('noninteger', 'noninteger'):

exec insert_testdata @kind = 'noninteger', @data = 'noninteger':


texts: How to select records with two columns matching values in one table in PostgreSQL?

texts:
I have a table with the below structure and data:

create table employee (id int, name varchar, father_name varchar);
insert into employee values(1, 'John', 'Alex'),(2, 'Simi', 'Expatri'),(3,
'John', 'Alex'),(4, 'Hezad', 'Ambrose'),(5, 'John', 'Alex'), (6, 'Simi',
'Expatri'), (7, 'Hezad', 'Ambrose'), (8, 'John', 'Reman'), (9, 'Komal',
'Ambrose');

Now I want to fetch those records whose two columns name and father_name match each other.
The desired result would be as following:

id    |    name    |    father_name
1     |    John    |    Alex
3     |    John    |    Alex
5     |    John    |    Alex
2     |    Simi    |    Expatri
6     |    Simi    |    Expatri
4     |    Hezad   |    Ambrose
7     |    Hezad   |    Ambrose

Any help is appreciated in advance.


texts:
I have an oracle table with field type BLOB, length 4000, data type "2004".
I know that this field simply contains some XML.  How can I read the value of the BLOB, using an SQL select statement?  Is there some conversion function to use?
The same table/field in Sql Server is simply text.


texts: How To return null value if no records present in select query

texts:
I am querying in DB for last 72 records in Db
in my Db it may happens all data will not be present
for that I want to add dummy row in which I will add rank and adding null value to remaining data.

Query:

select (@rownum := @rownum + 1) AS rank,
date, day(date), hour(date),Sum(Ifnull(impressions, 0)) AS
total_Impressions,
ROUND(Sum(Ifnull(clicks, 0)) * 100.0 / Sum(Ifnull(impressions, 0)),10)
AS CTR_percentage,
ROUND(Sum(Ifnull(dv_viewed, 0)) * 100.0 / Sum(Ifnull(dv_measured,
0)),1)    AS Viewability_percentage,
ROUND(Sum(Ifnull(dv_measured, 0)) * 100.0 / Sum(Ifnull(dv_impression,
0)),1) AS Measurability_percentage
FROM (SELECT @rownum := 0) t,rhino.delivery_snapshot_summary where
adgroup_id IN(SELECT id FROM ad_group where campaign_id = 2467 AND
bundle = 0)
and date <='2018-11-26 23:59:59' and date >'2018-11-22 23:59:59'  group
by day(date), hour(date) order by date DESC limit 72;

and I'm getting
following response :

I want total 72 rows with all null data in response.

Database : Mysql
using MySQL workbench 6.3.6
mysql  Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using  EditLine wrapper


texts: 2 phase locking with added increment mode

texts:
I have this assignment question where increment mode is added in addition to read and write lock modes in two phase locking.But I cant seem to figure out whether each lock mode has its own seperate type of unlock method (like read unlock(x),write_unlock(x)),or we unlock all the locks acquired on the item with a single unlock(x) at the end.Can anyone please explain me the asnwer to the following question along with an example if possible.Thanks

That was the question and the place where I got stuck is according to the lock compatibility matrix given in the problem while a transaction is performing an increment operation on an item x (acquired an increment lock)other transactions can simultaneously acquire increment lock on the same item x and perform increment operations just like shared mode(where if a transaction holds read lock on an item x other transactions too can acquire read lock on x ) where as increment lock is conflict to read and write operations .so suppose the following non serial schedule which follows 2pl

T1          |           T2

lock-s(x)

read(x)

lock-i(x)

x = x + v

                      lock-s(y)

                        read(y)

lock-x(x)

lock-s(y)

write(x)

unlock(x)

                    lock-i(y)(cant acquire this lock since read lock on y is not released in transaction t1 ,hence waits)
                      y = y + v

read(y)

lock-i(y)(cant acquire this lock since read lock on y is not released in transaction t2 ,hence t1 also waits)

y = y + v

lock-x(y)

write(y)

unlock(y)

likewise both transactions enter into deadlock.No matter what example I try to take I get a deadlock .Can anyone please give me a schedule where the transactions dont enter into deadlock and they follow 2 Phase locking protocol


texts: Determine the origin IP address of queries against a table?

texts:
I have a table that is being updated by a service being run at an unknown location.

I've checked all of the servers that normally communicate with my database server and none of them appear to have the service in question running.

I am trying to track down where this service is running, and I'm wondering if it is possible for me to see where queries that affect a particular table originate from?

Is this possible?


texts:
I have a procedure with two temporary tables

CREATE TABLE #temp
  ( databasename VARCHAR(100),
    objectname VARCHAR(100),
    index_id INT,
    indexname  VARCHAR(100),
    avg_fragmentation_percent FLOAT,
    indextype VARCHAR(100)
  )

CREATE TABLE #temp2
(
    databasename VARCHAR(100),
    objectname VARCHAR(100),
    index_id INT,
    indexname VARCHAR(100),
    avg_fragmentation_percent FLOAT,
    indextype VARCHAR(100)
)

INSERT INTO #temp (databasename,
objectname,index_id,
indexname,
avg_fragmentation_percent,
indextype)
EXEC master.sys.Sp_msforeachdb
' USE [?] SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id) as
 ObjectName,
a.index_id,
b.name as IndexName,
avg_fragmentation_in_percent,
index_type_desc
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE b.index_id <> 0 and avg_fragmentation_in_percent <>0'

INSERT INTO #temp2 (databasename, objectname,index_id,
indexname,avg_fragmentation_percent,indextype)
EXEC master.sys.Sp_msforeachdb
' USE [?] SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id) as
 ObjectName,
a.index_id,
b.name as IndexName,
avg_fragmentation_in_percent,
index_type_desc
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE b.index_id <> 0 and avg_fragmentation_in_percent <>0'

and want to send the result from the procedure to email. The problem is that this query works fine but when i execute this its not finding the temporary tables.

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'
Report
' +
    N'
' +
    N'
ID	Table	' +
    N'Index	Before fragmentation	After fragmentation	' +
    --N'Expected Revenue
' +
    CAST ( ( SELECT td = T1.databasename,'',
                    td = T1.objectname, '',
                    td = T1.index_id, '',
                    td = T1.indexname, '',
                    td = T1.indextype,'',
                    td = T1.avg_fragmentation_percent AS
                         avg_fragmentation_percent_pre_change, '',
                    td = T2.avg_fragmentation_percent AS
                         avg_fragmentation_percent_post_change
              FROM #temp as T1
              INNER JOIN #temp2 AS T2
              ON T1.databasename = T2.databasename
              AND T1.objectname = T2.objectname
              AND T1.index_id = T2.index_id
              AND T1.indexname = T2.indexname
              AND T1.indextype = T2.indextype
              WHERE T1.databasename not in  ('master',
                                             'model',
                                             'msdb',
                                             'tempdb',
                                             'reportServer',
                                             'ReportServerTempDB')
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'
' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='example@mail.com',
@profile_name='example@mail.com',
    @subject = 'Report',
    @body = @tableHTML,
    @body_format = 'HTML';


texts: Backup / Restore of DB on MSSQL 2019 to MSSQL 2014

texts:
I have a production database running on MSSQL 2019 on a hosted platform.
I am wanting to create a sandbox copy of the db with all of the data in a local MSSQL server running 2014.

Everything I've tried  (backup / restore , generating scripts, export data) has error-ed out.

Does anyone have an easy button I am overlooking?


texts:
I inherited a database that has a VARCHAR column that really should be a DATE. There's no checking on the form (I need to fix that) so dates constantly get entered as 2016-12-4 instead of 2016-12-04 and so forth.

I'm pretty sure there are historically a lot of bad dates, so I'm a little skeevy about just changing the data type to DATE, but I'm curious about what would be a good strategy for changing that?

Background: (I'm a front-end developer, not a DBA, so while I can get around in SQL I don't know what I don't know and I'm always afraid I'll make a mistake that corrupts the database).


texts:
When I checked one of my client's database, I found some mistakes which will make problems.
The problem is that the date is showing as 01-01-01 when I try to display it in the dd-MM-yyyy format the result is 01-01-0001. How can I get rid of this problem. Is it possible to update the year alone in these dates.

I can find the dates with this problem by using this query

  select from_date from date_table where length(extract(year from from_date))='2';

Can I update this using the logic that

Find the dates using the above query
Update the year by checking the condition that if year is between '00' and '13' prefix '20' with the year else prefix '19'

How can I do this? Can I do it using Oracle query or should I write code for doing so?

Database is Oracle 11g R2 working in RHEL5.


texts: psql: could not connect to server: No such file or directory, allowing communication over TCP/IP, ubuntu 18.04

texts:
I was installing postgres on Ubuntu 18.04

the command line I used for installation is:

sudo apt-get install postgresql postgresql-contrib

since I have entered this command line a couple of times, I got:

Reading package lists... Done
Building dependency tree
Reading state information... Done
postgresql is already the newest version (10+190ubuntu0.1).
postgresql-contrib is already the newest version (10+190ubuntu0.1).
0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.

as the result.

Things seem to be fine for now.

Then I tried to set the password for "postgres" by using:

sudo -u postgres psql postgres

This is what I got:

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

To tackle this problem I tried the following approaches:

To find out what processes are running as user postgres, I entered:

ps -u postgres -f

and here is what I got:

UID        PID  PPID  C STIME TTY          TIME CMD

I don't quite understand what they are and how important they are. It would be kinda nice if you could tell me. With that being said, I didn't get much clue from this info.

I assume I haven't start postgresql yet due to this error:

psql: could not connect to server: No such file or directory

so I tried:

sudo /etc/init.d/postgresql start

What I got:

[ ok ] Starting postgresql (via systemctl): postgresql.service.

Seems I got something there. So I ran:

psql

But the same error showed up:

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

At this moment, I am really confused if I have started my postgres? If I have, why is "psql" not working. If I haven't, what else does this error indicate?

Then I tried an alternative setup approach:

https://help.ubuntu.com/community/PostgreSQL

check "Alternative Server Setup" from the link. The link per se is pretty neat. However the following command lines didn't work for me:

sudo -u postgres createuser --superuser $USER

I got:

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

OK, let me connect to db postgres:

For your reference:
https://www.cyberciti.biz/faq/postgresql-remote-access-or-connection/

Use the command line below to allow remote IP address to access PostgreSQL:

su - postgres

I got:

su: Authentication failure

2.Allow communication over TCP/IP. I think this part is pretty important.

command line:

vi /var/lib/pgsql/data/pg_hba.conf

I inserted this line to the interface:
host all all 192.168.1.0 255.255.255.0 trust

I used ":wq" to save and quit, but this was what I got:

"/var/lib/pgsql/data/pg_hba.conf"
"/var/lib/pgsql/data/pg_hba.conf" E212: Can't open file for writing
Press ENTER or type command to continue

I had to close the terminal. I didn't know other ways to quit in this case.

It's the same thing here for:

$ vi /var/lib/pgsql/data/postgresql.conf

and

tcpip_socket = true

Just a bit more info:(cl:command line, fb:feedback)

cl:

ps aux | grep postgres

fb:

ubuntu    5599  0.0  0.0  21536  1004 pts/1    S+   23:15   0:00 grep --color=auto postgres

cl:

sudo apt install postgres

fb:

Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package postgres

cl:

sudo apt-get install postgres

fb:

Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package postgres

4.
When I use "sudo -i -u postgres" and type in "psql"
I got nothing different.

Yeah, that's pretty much it.

Will allowing communication over TCP/IP makes my "pqsl" work? If so, how do I save and quit?


texts: MariaDB instance in cloud hit 95% CPU utilisation for 30 mins

texts:
I've got MariaDB instance in the cloud which I put live about 2 months ago. I'm new to the DBA business and I got the impression the people at the company who I run it through are learning on the hoof as well.

All is going well except that I saw CPU utilisation spiked on Tuesday to 95% for now apparent reason.

I requested performance stats after a user complained of a long running query. It turns out they were doing regular hourly queries on unindexed fields and the table scans involved suddenly took a lot longer. I am now looking at indexing this column so they can run their queries faster.

I'd like to know whether their queries could be the cause of the CPU utilisation, or whether whatever caused the CPU utilisation had just grabbed all the cache and caused their table scanning query to suddenly take far longer.

What should I be doing to capture information that will show what is going on and why the CPU utilisation spiked, and if it's a concern?

Here's the 5 days chart of CPU utilisation:

and the 1 day chart

and the only other chart that correlated with it:

and

The scales on the chart are probably the key, right?

The other charts provided (database connections, write IOPS, disk queue depth) didn't show any correlation.

I'm also puzzled why the normal pattern has totally changed. The usage is around a million queries per day, and has been for 2 months now.


texts: how does CXPACKETS relates to CXCONSUMER wait types?

texts:
when running a delete query to a table  that has many foreign keys I get the following waits.

I noticed that I have both CXPACKETS and CXCONSUMER wait types at the same time during of the execution of my query.

the question is:

what is the relation between these 2 wait types (if any)?


texts: Possible to add KEEPFIXED PLAN option to reduce the recompile in SQL Server 2005 in Production Env?

texts:
I'm not sure if it is good to enable the KEEPFIXED PLAN to reduce the impact of recompilation in the Production Env?

I'd know if we may gain performance after that or on the other hand it will be even worse.

For my case, I have a batch of stored procedures running in SQL Server 2005 and some one of need to run in a fixed loop condition.

Thanks!

Vance


texts:
I have my machine and a database server on the same Windows domain. I also have a staging machine that is not on the domain.

From my machine, I am able to perform any and all operations using FILESTREAM without trouble. From the staging machine, my application is able to use TSQL as it pleases, but the moment it tries to open a SqlFileStream, I receive 'access is denied.`

The staging machine, the database server, and my machine all have a local user set up with an identical name and password so that the application can use integrated security to connect to SQL Server. Since FILESTREAM is the only thing not working for the non-domain machine, I am wondering if I am missing something in the FILESTREAM documentation or if the documentation fails to specify that only Active Directory will suffice for integrated security, or of course if there is something I can do to make my situation work.


texts: Displaying the list of all tables in all database

texts: How can I display a list containing all the tables of all the database in a SQL Server 2008 R2 server?


texts: PostgreSQL COPY TO doesn’t honor Linux group privileges

texts:
I have issues executing COPY to command, receiving below error msg:

ERROR:  could not open file "/home/pgsql/TMP/out.txt" for writing:
Permission denied

Postgres engine is being run as postgres user
Postgres user belongs to app group
TMP folder has below privileges set:

drwxrws---  2 pgsql app       23 Mar 28 19:47 TMP

I don’t understand why it doesn’t work.
I did quick check login as postgres user and creating file and this worked:
sudo -i -u postgres

If I change privileges to 777 all works as expected (I can do copy to command with no issues)
So as to me it looks postgres doesn’t somehow honor Linux groups rights
Can someone guide me how to resolve that (777 is not an option to me)


texts: How do I do a complex GROUP BY in MySQL?

texts:
I have a table that contains several keys into other tables (where each key is comprised of multiple columns).  I would like to be able to group rows together that have an equal key, but I don't want to group all of them together.  It's not a simple GROUP BY on the key but rather I want to be able to make groups of say 10.  So if a particular key showed up 50 times I would get 5 results when I do this grouping (5 groups of 10).  I also want this grouping to occur randomly within the key.

I didn't know of the direct way to do this, and the roundabout method I came up with isn't working like I think it should.  The roundabout solution I came up with was to create a new column for each key that would be an integer such that value i represents the ith occurrence of that key (but in random order).  I could then do integer division so that every n (say 10) rows within the key have the same value, and I could do a GROUP BY on that value.

Is there a more direct way to accomplish what I just described?  It's quite awkward, and I ran into problems in creating the new index column (as I described in this question).

EDIT: First of all note that this is for MySQL.  I'll add an example in case my goal is not clear.  The MySQL docs show a method to get almost there:

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

This creates a table which, although not what I want, gets close:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

I would essentially like to GROUP BY id, except I would want the records with mammal to have one "group" for IDs 1-10, another "group" for IDs 11-20, etc.  However, I would be doing this with an existing table, and I wouldn't necessarily want "dog" to show up with ID 1.  I would want that initial ordering to be random, but then deterministic from then out.


texts: How much storage space do indexes and keys take for an SQL Table?

texts:
I have a table which is predominantly filled with NULLS and takes up 10 GB which was shocking since probably 90 to 95% of the table is NULLS. I copied this table over to a new database on the same SQL 2012 instance using Select * Into.... but the copied table takes up about 70 MB.

Checking them against each other, they seem to have the same data, as expected so it's not like data is missing.

Is the disparity in size a result of not generating a script to copy indexes, triggers, keys, etc?

Is there a way to optimize the table to reduce space?

Thank you,


texts: CTE: Get all parents and all children in one statement

texts:
I have this working CTE example.
I can select all grand-parents and all children.
But how can I select all grand-parents and all children in one statement?
In this example I want Grandfather, Father, Son as output if I give "Father" as input.
I use PostgreSQL. But I think this question should be standard SQL.
Please correct me if I use PostgreSQL specific syntax.
DROP table if exists tree;

CREATE TABLE tree (
 id SERIAL PRIMARY KEY,
 name character varying(64) NOT NULL,
 parent_id integer REFERENCES tree NULL
);

insert into tree values (1, 'Grandfather', NULL);
insert into tree values (2, 'Father', 1);
insert into tree values (3, 'Son', 2);

-- -------------------------------------
-- Getting all children works

WITH RECURSIVE rec (id) as
(
  SELECT tree.id, tree.name from tree where name='Father'

  UNION ALL

  SELECT tree.id, tree.name from rec, tree where tree.parent_id = rec.id

  )
SELECT *
FROM rec;

-- Result:
--  id |  name
-- ----+--------
--   2 | Father
--   3 | Son

-- -------------------------------------
-- Getting all parents works

WITH RECURSIVE rec (id) as
(
  SELECT tree.id, tree.name, tree.parent_id from tree where name='Father'

  UNION ALL

  SELECT tree.id, tree.name, tree.parent_id from rec, tree where tree.id = rec.parent_id
  )
SELECT id, name
FROM rec;

-- Result
-- id |    name
-- ----+-------------
--  2 | Father
--  1 | Grandfather

Above is a simplified working example. The tree can be up to 100 level deep. There can be several level of ancestors above "Father" and several level of descendants below. I want all ancestors and all descendants.


texts: How to distinguish between system and user defined roles in a single database?

texts:
Is there a way to distinguish between a system role/user and a user defined role/user? As system role/user I understand a role/user which is installed/assigned implicitly at the time the database has been created. As user defined role/user I understand a role/user created "manually" afterwards.

The only way I have found is by looking at the result of

select * from sys.database_principals order by create_date, is_fixed_role desc

or by comparing the result with a freshly created database. Is there another way?


texts: Question regarding PSQLException: User was holding shared buffer pin for too long

texts:
I recently encountered a strange error that I don't quite understand what could cause it:

caused by org.postgresql.util.PSQLException: ERROR: canceling statement due to conflict with recovery Detail: User was holding shared buffer pin for too long.

I have a fixed timeout 10s set for each query, so any long run query should get terminated and the error message I expected to see User query get cancelled by user request. However, I see this sometimes, but the above as well which is really confusing. Furthermore, it only show up when I see high lag from our RDS replica, then resolve itself after 5, 10 minutes.

I tried to reproduce this issue locally but no luck. Any thought would be greatly appreciated!


texts:
I like compact WAL files. Actually I do:

archive_command = 'cp "%p"C:\\%f"'

What is the best way to do it?


texts: ADD and DROP triggers frequently on a database

texts:
I have a table from which I am deleting the data. Whenever a row is deleted, the row matching the data from other tables must also be deleted. So I am planning to achieve the same using triggers. The trigger will be only on DELETE condition.

The application from which I am achieving the same runs 4-5 times a day. So, at the start of the application, I am adding the triggers and on exit of application, I am dropping the triggers (because triggers slow down the database performance).

This too much frequent adding and dropping triggers is OK for Postgres or is there any better idea to achieve the same?

There are other applications as well which keep on running and they are using the database. Other applications do only INSERT and UPDATE operations. Only my application does the deletion part. It is a utility for daily cleanups.


texts: Select statement for selecting every week's log record

texts:
Here is a simplified version of the table in question:

+------------------------------------------------------+
| DatabaseName | LogRecordNumber | LogTimestamp        |
+------------------------------------------------------+
|DB1           | 47378910        | 2014-11-12 10:40:00 |
|DB1           | 48268418        | 2014-11-12 10:41:00 |
|DB2           | 223781480       | 2014-11-12 10:40:00 |
|DB2           | 228976522       | 2014-11-12 10:41:00 |
+------------------------------------------------------+

We have a table that contains the log record number for each database captured every minute. This data spans back a full year, or shorter if the database is newer than that.

We have to watch the log record number to ensure it will not go over the maximum number of bits that it can have. Currently I'm tracking this information with a simple query and adding it into a spreadsheet.

Sample Query:

SELECT Databasename, MAX(LogRecordNumber), LogTimestamp
FROM MyDB.MyTable
WHERE LogTimestamp BETWEEN '2014-11-12-04...' AND '2014-11-12-08...'
GROUP BY DatabaseName, LogTimestamp

I would like to instead develop a report that a technician could run to determine through stat regression when the log records would be out of bits.

So I now need a select statement that would retrieve the LogRecordNumber for each interval of time where we have a log record. Getting the log number once a week would be appropriate and allow me to make a reasonable regression guess at when we will be out of bits.

Again these data can be as far as a year back or as soon as a few months ago, so hard coding something like DATE(CURRENT_TIMESTAMP) - X WEEKS is not possible.

Is there any way to do what I'm suggesting with a query, or do I need to rethink this?

If it matters, the database in question is DB2.


texts:
I have a Java application that configured with a connection pool. I have ensured that the application terminates the connections properly.

However when I run this command on mysql command line -

show status like '%onn%';

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 2     |
| Connections              | 72    |
| Max_used_connections     | 10    |
| Ssl_client_connects      | 0     |
| Ssl_connect_renegotiates | 0     |
| Ssl_finished_connects    | 0     |
| Threads_connected        | 2     |
+--------------------------+-------+

Why does mysql keep the connections open when they have been terminated from the client?

The contents of my.cnf file are (comments and unimportant sections omitted) -

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld]
user        = mysql
pid-file            = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
lower_case_table_names = 1
skip-external-locking

key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack    = 192K
thread_cache_size   = 8

myisam-recover      = BACKUP

query_cache_limit   = 1M
query_cache_size    = 16M

expire_logs_days    = 10
max_binlog_size     = 100M


texts: Database system vs database management system

texts:
I am now taking database course in school but have worked on projects using MySQL before.

I am a bit confuse with database system and database management system. For database management system, MySQL, MS Access are some of the examples.

Can someone give me a clear explanation between the two with some examples? Most are saying they are the same.


texts: How can I replace the following query with ISNULL function in SQL?

texts:
I have the following query. For performance optimization reasons I have been asked to replace the logic with ISNULL function in SQL. How can I achieve this? please advice.

                   SELECT CASE
                               WHEN tb1.[tag-value] IS NOT NULL THEN tb1.[tag-value]
                               ELSE 'N/A'
                          END     AS [tag-value]
                   FROM   @AgentTableFilt ta


texts:
I am importing data from another sources, which has multiple columns but I am facing problem in date column like I have multiple format in this column like:

dd/mm/yyyy
mm/dd/yyyy
mmm-yyyy

Can you guide me how can accommodate these format during migrate data (using cursor to transfer data from temporary table to destination table).

How can i convert mmm-yyyy date format to dd/mm/yyyy format


texts:
The task

I need to store the some values in sequence in the database. The data looks just like a sequence of numbers. Here is an example of the data:

            Val_1   Val_2   Val_3   ...     Val_145
Record 1        6       8       4                 5
Record 2        2       5       6                 3
etc.

Now the thing is that there are a couple of actions that the user has to be able to do:

Insert columns (== e.g. the user needs to enter additional data in between Val_1 and Val_2 columns and inserts 5 columns there). The column headers are sequential though (val_2 always follows val_1, val_3 always follows val_2) thus only the values get shifted to the right - just like in Excel when you insert columns.
Delete column - same as with insert columns - column header always stay the same and values get deleted and values from the right get shifted to the left - again just like in excel.
View random chunks of values. E.g. view values from index 600 to index 1000

The three solutions

I came up with three solutions that can store those values, but every solution has some flaws. Here they are:

Store the index of value in the Value

Tables:

table Record
    id int
table Value
    record_id int
    index int
    value int

Pros: easy to find the needed values by using the index field (where index > 662 and index < 987).
Cons: inserting and deleting value are horrible. If there are 1000 values for a record then inserting one value at index 500 will require 1 insert and 500 updates (to shift values after index 500). Same problem with deleting values.

Store references to previous value and the next value

Tables:

table Record
    id int
table Value
    record_id int
    value int
    next_value_id int
    prev_value_id int

Pros: easy to insert and delete values - inserting a value requires 1 insert and 2 updates (updating refs on adjacent values). We can even remove the prev_value_id to make it even better.
Cons: finding values by index will be horrible. Finding range of values from 500 to 1000 will require us to go though all the values starting at value 1.

Store values as e.g. string in the record table

Tables:

table Record
    id int
    values_str text

Pros: this is just like parsing a text file. Most work is done in code. Feels like a simple and straightforward way to go.
Cons: does not feel right. This may look and feel good, but something about this design smells bad. I have a bad feeling about this.

The question

By know you probably already guessed the question - which way should I go? Is there a more sophisticated way of storing such data? Does any of the solutions I've thought of make any sense?


texts: Fastest way to migrate database to AlwaysOn cluster

texts:
I want to make the fastest migration plan to move a SQL Server database in simple recovery from a standalone server (SQL 2005) to a two node AlwaysOn cluster in full recovery.

Some databases are larger than 200gb so restore them in primary node, switch to full mode and add them to AlwaysOn by selecting full in initial data synchronization is not an option because that would make another backup/restore, taking a lot of time and resources (Time is critical!).

What I want is to restore the database in parallel in all the nodes at the same time (using norecovery I think? ) and then join them to the AlwaysOn group.
I have already read the documentation about AlwaysOn and it's not clear about this.

Would it be ok if I follow these steps:

In Standalone server: Set database to single user mode, switch
    database to full recovery
Backup database from standalone server and restore it in primary
    node with recovery and restore it in secondary node with no recovery
Add database to AvailabilityGroup by selecting "Join only" in
Initial Data Synchronization

Extra info:

The standalone  server is 2005 standard, so it's  not  possible to
join it as  a new node.
I want to make as less backup/restore as possible.
What I meant with set the database to single user is that there will be no changes in the database during the migration. It's important because I could make a full backup and then I wont need a differential backup.

Questions:

After restoring database in Primary node and Secondary node: if I want to join the database to an AG Do I also need to make log backup/restore even though the database had
   just change to full recovery and no one but me has connected to it?
Wich option should I choose in Select Initial Data Synchronization
   Page on the AG Wizard ? Join Only or Skip data synchronization?

Thanks!


texts: Turn Emails into Usernames With MySQL Query

texts:
I am adding a username field to my database, and I want to populate it with the username portion of the email addresses the users have supplied...

I dont have many users, so I dont expect any conflicts.

What would a query look like to take the username portion of an email address and save it to the username column?

Thank you


texts: MySQL InnoDB log files on different location

texts:
I am using MySQL version: 5.5.38

There are following parameters that can control InnoDB log files:

innodb_log_files_in_group - amount of log files in the group, by default 2. Will create 2 files ib_logfile0 and ib_logfile1

innodb_log_group_home_dir - location of InnoDB log files.

Is there a way to place each individual log file to it's own place?

The main idea is to increase performance and place each file to it's own HDD drive. As I have to much IO related to InnoDB log files. Too much IO to MySQL InnoDB log files


texts:
I created an insert trigger in a table that will fire when an insert/update/delete is made. The trigger is supposed to send information about the query that fired it, but every time it fires, the error below occurs.

OLE DB provider "SQLNCLI11" for linked server "TMP-DBA-DB" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure AuditlDealerDebitAuditTrailTEST, Line 22
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "TMP-DBA-DB" was unable to begin a distributed transaction.

Here's my trigger query:

    create trigger dbo.AuditlDealerDebitAuditTrailTEST
on [dbo].[lDealerDebitAuditTrailTEST]

after UPDATE, INSERT, DELETE
as

declare @TableName varchar(20), @User varchar(20), @PCNo varchar(20), @server nvarchar(30), @Database varchar(30),
        @table varchar(30),@activity varchar(20), @date datetime;

begin
         set  @TableName = 'lDealerDebitAuditTrailTEST'

if exists(SELECT * from inserted) and exists (SELECT * from deleted)
begin
    SET @User = SYSTEM_USER;
    set @PCNo = HOST_NAME();
    set @server = @@SERVERNAME;
    set @Database = DB_NAME();
    Set @table = @TableName;
    SET @activity = 'UPDATE';
    set @date = GETDATE();
      INSERT into [TMP-DBA-DB].[Josel_TestDB].[dbo].[audit](ExecutedBy,  PCNo, "Server", "Database", "Table", Activity, ExecutedOn)
         values (@User, @PCNo,  @server, @Database,  @table, @Activity, @date);
end

If exists (Select * from inserted) and not exists(Select * from deleted)
begin
    SET @Activity = 'INSERT';
    SET @User = SYSTEM_USER;
    set @PCNo = HOST_NAME();
    set @server = @@SERVERNAME;
    set @Database = DB_NAME();
    Set @table = @TableName;
    set @date = GETDATE();
     INSERT into [TMP-DBA-DB].[Josel_TestDB].dbo.audit(ExecutedBy,  PCNo, "Server", "Database", "Table", Activity, ExecutedOn)
         values (@User, @PCNo,  @server, @Database,  @table, @Activity, @date);
    end
If exists(select * from deleted) and not exists(Select * from inserted)
begin
    SET @activity = 'DELETE';
    SET @User = SYSTEM_USER;
    set @PCNo = HOST_NAME();
    set @server = @@SERVERNAME;
    set @Database = DB_NAME();
    Set @table = @TableName;
    set @date = GETDATE();
     INSERT into [TMP-DBA-DB].[Josel_TestDB].dbo.audit(ExecutedBy,  PCNo, "Server", "Database", "Table", Activity, ExecutedOn)
         values (@User, @PCNo,  @server, @Database,  @table, @Activity, @date);
    end

    end
GO


texts:
I'm having an issue where some queries take an abnormal execution time, meaning 3 to 5 seconds.

This part of the schema is 2 tables A and B, where A has many to one relation to table B.

Table A:

CREATE TABLE `A` (
  `id` varchar(255) NOT NULL,
  `name` varchar(256) DEFAULT NULL,
  `status` varchar(25) DEFAULT NULL,
  `bId` bigint(20) DEFAULT NULL,
  `refId` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FKF9A4C12670427173` (`bId`),
  KEY `FKF9A4C126F3F3A79E` (`refId`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

Table B:

CREATE TABLE `B` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `refNumber` varchar(20) NOT NULL,
  `altId` varchar(15) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `foo` (`foo`),
  KEY `altId_index` (`altId`),
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

When I make any query which contains a restriction on the A.refId the query is extremely slow, in 3 to 5 second range.

select * from A a where a.bId in (select id from B b where b.altId like 'foobar') and a.refId = 'reference';

Table B has only one row where the altId matches, and there are about 500k rows in table A where refId is 'reference'.
I know that the large row count is probably reason for the long execution time, but I don't see why, or how to fix it.

The MySQL explain for the execution plan shows the following:

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'a', 'ref', 'FKF9A4C12670427173,FKF9A4C126F3F3A79E', 'FKF9A4C126F3F3A79E', '258', 'const', '9', 'Parent of 2 pushed join@1; Using where with pushed condition'
'1', 'SIMPLE', 'b', 'eq_ref', 'PRIMARY,altId_index', 'PRIMARY', '8', 'a.bId', '1', 'Child of \'a\' in pushed join@1; Using where with pushed condition'

So if anyone can shed a bit of light what is causing this, or how to fix it, that would be much appreciated.


texts: Insert performance progressively deteriorating for indexless table

texts:
I have a miniature SQL Server database being filled in by a mathematical model. The database contains two tables created thus:-

CREATE TABLE [dbo].[small_table](
[id_col_1] [tinyint] NOT NULL,
[id_col_2] [tinyint] NOT NULL,
[id_col_3] [int] NOT NULL,
[id_col_4] [int] NOT NULL,
[data_col_1] [real] NULL,
[date_time_added] [datetime] NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[small_table] ADD  CONSTRAINT [DF_st_date_time_added]  DEFAULT (getdate()) FOR [date_time_added]

and

CREATE TABLE [dbo].[big_table](
[id_col_1] [tinyint] NOT NULL,
[id_col_2] [tinyint] NOT NULL,
[id_col_3] [int] NOT NULL,
[id_col_4] [int] NOT NULL,
[id_col_5] [int] NOT NULL,
[id_col_6] [tinyint] NOT NULL,
[id_col_7] [int] NOT NULL,
[data_col_1] [real] NULL,
[data_col_2] [real] NULL,
     ...snip...
[data_col_14] [int] NULL
) ON [PRIMARY]
GO

The model runs writing single rows to these tables whenever it thinks proper. But as the run progresses, writing into these tables takes progressively longer. Does anyone have any idea why this might be or what I should do to find out?

If I start a new run with a different value of id_col_2, the initial inserts proceed at a decent rate and the run then slows down again.

I'm using VB.Net for the application running on Windows XP.

Microsoft SQL Server Management Studio  10.50.2500.0
Microsoft Data Access Components (MDAC) 3.85.1132
Microsoft .NET Framework                2.0.50727.3643
Operating System                        5.1.2600

If I use MySQL database instead (replacing the connection and INSERT statements), the inserts proceed at a regular rate.

(In the real problem the tables have indexes but I removed them one at a time in order to find out if they were causing the problem; there are now none left and it is still occurring).

Edit

The original code used a programmatically-constructed insert statement and the
ADODB.Connection.Execute method to insert the row. Since then I've tried using SQLConnection and SQLCommand objects to insert the row. This has improved performance slightly, but the progressive deterioration is still occurring.

My test database has an original file size of 130MB and an autogrowth of 10%. The live system has an original filesize of 10592MB and an autogrowth of 1MB. The problem is evident on both systems.

Edit

Recoded these inserts to use LINQ; still no better.


texts:
I hope this isn't too broad of a question. I'm certain it'll be able to help me and any future dba noobs that stumble upon it.

I'm a systems admin that got put into the roll of DBA (because I helped the CEO with his outlook, so I can obviously manage our databases!). It's not that big or busy of a database server... a mysqldump is about 6GB in size and it's taken us 5 years to get it that big. Munin reports that we're averaging 40-60 queries a second at our peak hours.

My boss paid for me to take the Oracle University systems admin course, but having gone through it, it simply explains the different parts of the mysql server, what things do and how they do it. But it's not that in-depth and you definitely don't come out of that course a DBA.

So as the current DBA, what should I do to make sure everything is running smooth? Are there daily maintenance tasks I can perform? Are there certain metrics I should be checking? Or to put it another way, as DBAs, what do YOU do on a daily basis to keep everything in good shape?

If it'll help tailor the answer a little bit, here are some specifics. We have 171 tables, all but 3 are innodb, the others are myisam. We have Master/Slave replication set up between our primary datacenter and our disaster recovery site, using RBR. Version is 5.5.28.

What can I do?


texts:
I'm trying modeling things like this using oracle:

An account can have 1~5 members

So I'll have a account table and a member table with a FK of account. But is that possible to check if the account have less than 5 members?

I've tried constraint and trigger but oracle doesn't allow sub queries in constraint or condition of trigger like

CHECK( SELECT ...) or IF count(*) >= 5

which make things getting hard.


texts: postgres9.5: can't create extension on standby

texts:
I can't create extension on postgresql9.5 server (hot standby). During to attempting I received:

db=# create extension pgrowlocks;
ERROR:  cannot execute CREATE EXTENSION in a read-only transaction

Can anyone assist? Thnx.


texts: Should I normalize a relational database design if I know the data will never be updated?

texts:
I'm designing a PostgreSQL database that will be storing an archive of data that I am absolutely certain will never be updated, only read (these are archives will be pointing to real data at a point in the past, and since the past can't be changed, these rows won't either).

There will be a lot of data (up to terabytes), so I am trying to optimize the design as much as I can.

Should I worry about normalizing the data if I know I will never have to update it? Consider I could simplify the design substantially by keeping data that would be stored in two tables in a normalized version in one single table.

Thanks!


texts: Cluster communication - SQL Server Failover Cluster

texts:
I am running a lab enviornment on my laptop two node SQL 2008 R2  running on
windows 2008 R2 clustered Vms

Based on my understanding

a) Public Network or all Communication network

I see checkbox is enabled for cluster communication on this network also.

b) Private or HeartBeat or Cluster communication only network.

It appears to me that currently public network is used for cluster communication?

Is this correct? Does heartbeat network is used only for redundancy and cluster communication is primarily done on public network?


texts: Trigger for multiplying two columns from different tables with a functions in mysql

texts:
I have been endelessly trying to get a trigger to work, with many different variations and all ending in errors.

I have finally just decided to ask for help, since im still a beginner at this.

So say I have a many-to-many table that contains pk's of o.orderId and p.productId, and within this table I have two columns called amount and totalPrice

I have then created a function, that returns the price of products based on their productiD.

CREATE FUNCTION func_getProductPrice($pId varchar(45)) RETURNS int(11)
    DETERMINISTIC
BEGIN
DECLARE origPrice int(11);
SET origPrice = (select o.productPrice from (productId_has_orderId
inner join productId on productId = $pId);
RETURN origPrice;
END

And a before insert trigger that is meant to call on this function to get the price, multiply the returned value based on new entry and then update the price field of this new entry.

delimiter |
CREATE TRIGGER `vet`.`calcProdutctPrice` before INSERT ON `productId_has_orderId`
FOR EACH ROW
IF NEW.amount <> 1 THEN
BEGIN
DECLARE newPrice int(11);
set newPrice = ((SELECT SUM((new.amount) * (select func_getProductprice(new.p.productId))) AS total from productId_has_orderId));
    begin
        update productId_has_orderId
        set price = newPrice
        where orderId = new.orderId;
        end;
    END;
end if
|
delimiter ;

Which gives this error when inserting data

Cant't update table 'productId_has_orderId' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

An another variation of the trigger which essentially works the same, but the price is not a declared variable and is instead just updated to the new price

delimiter |
CREATE TRIGGER `vet`.`calcProdutctPrice` before INSERT ON `productId_has_orderId`
FOR EACH ROW
IF NEW.amount <> 1 THEN
BEGIN
        update productId_has_orderId
        set price = ((SELECT SUM((new.amount) * (select func_getProductprice(new.p.productId))) AS total from productId_has_orderId));
        where orderId = new.orderId;
    END;
end if
|
delimiter ;

Which then instead gives this error when inserting values

insert into productId_has_orderId (orderId, productId, amount) values (5100002, 3100002, 3)

Error Code: 1093. You can't specify target table 'productId_has_orderId' for update in FROM clause 0.000 sec

I am not sure what else I can do by now, I was thinking about creating a trigger calling a stored procedure, but I don't know if I can pass values from a trigger to a procedure.

Any help is appreciated, thank you


texts: how to use an index on a mysql query with a where clause and a order by

texts:
I have a table

CREATE TABLE activity (
 id INT(10) UNSIGNED AUTO_INCREMENT,
 subscriberid INT(10) UNSIGNED NOT NULL DEFAULT '0',
 action VARCHAR(250) NOT NULL DEFAULT '',
 ip INT(10) UNSIGNED NOT NULL DEFAULT '0',
 cdate DATETIME NULL DEFAULT NULL,
 PRIMARY KEY (id),
 KEY cdate (cdate),
 KEY subscriberid (subscriberid)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 DEFAULT COLLATE = utf8_general_ci;

how can i return all rows for a single subscriberid ordered by cdate descending, using the index on  cdate field.

if i do

explain select * from activity where subscriberid = 2 ORDER BY cdate DESC;

it seems not to use the index. If i add a group by clause it doesn't do a filesort as you can see in the link but it still scans all rows
http://take.ms/8z3Po


texts: SQL Server 2008 R2 – Track Database Growth (Production Database)

texts:
My requirement is to get the database size information collected daily, I did not find any option to get database growth over a period of time.

Someone please advise what the correct method for this could be.


texts: Is "Estimated Number of Rows" always per execution within SSMS execution plans?

texts:
When I look at execution plans, I often compare "Estimated Number of Rows" with  "Actual Number of Rows", looking for discrepancies.  For example, when I hover over the bottom arrow in the picture below,  the estimate is 1 and the actual is 200, so at a glance it appears to be a terrible estimate.

However, I've come to wonder if this is actually the estimate for just one execution.  When I hover over the seek to get more detail, I see that there are 200 executions, and it estimated 199.98.  So if my hunch is correct, the estimate is actually dead on.

Can anyone confirm for me if this is the case?  And if so, am I the only one to think this is incredibly misleading (especially when hovering over the arrows)?  If it helps, here is the script to set up and reproduce the above plan:

-- Setup: Create 2 tables with 100K rows each.

CREATE TABLE T1(Id int NOT NULL PRIMARY KEY, X INT NOT NULL)
CREATE TABLE T2(Id int NOT NULL PRIMARY KEY)
CREATE INDEX Ix ON T1 (X, Id)

;WITH
    Pass0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
    Pass1 AS (SELECT 1 AS C FROM Pass0 AS A, Pass0 AS B),--4 rows
    Pass2 AS (SELECT 1 AS C FROM Pass1 AS A ,Pass1 AS B),--16 rows
    Pass3 AS (SELECT 1 AS C FROM Pass2 AS A ,Pass2 AS B),--256 rows
    Pass4 AS (SELECT 1 AS C FROM Pass3 AS A ,Pass3 AS B),--65536 rows
    Pass5 AS (SELECT 1 AS C FROM Pass4 AS A ,Pass2 AS B),--1048576 rows
    Tally AS (SELECT  ROW_NUMBER() OVER (ORDER BY C) AS Number FROM   Pass5)
INSERT INTO T1
SELECT Number, Number
FROM  Tally
WHERE Number <= 100000

INSERT INTO T2
SELECT Id
FROM T1

--Now join them

SELECT *
FROM T1
JOIN T2 ON T2.Id = T1.Id
WHERE T1.X <= 200


texts:
Could somebody tell what is wrong with obvious query below:

db2 => select next value for schema_name.sequence_name;

As a result I'm getting:

SQL0104N  An unexpected token "END-OF-STATEMENT" was found following
"schema_name.sequence_name".  Expected tokens may include:  "<table_expr>".
SQLSTATE=42601


texts: Backup from dacpac file extracted using SSTD

texts:
I tried method given in this answer by Ramankant Dadhichi but deploying failed.
Backup a database from Azure SQL Managed Instance and restore to on premise SQL Server
 I have my database in managed instance. and i extracted dacpac using SSTD but now when i try to deploy extracted file using SSMS. I am getting following error:

Could not deploy package.
Error SQL0: The element [releaseengineer] cannot be deployed. This element contains state that cannot be recreated in the target database.
Error SQL0: The element [Reporter] cannot be deployed. This element contains state that cannot be recreated in the target database.
 (Microsoft.SqlServer.Dac)


texts:
How Postgres triggers mechanism scales ?

We have a large PostgreSQL installation and we are trying to implement an event based system using log tables and TRIGGER(s).

Basically we would like to create a TRIGGER for each table we want to be notified for an UPDATE/INSERT/DELETE operation. Once this trigger fires it will execute a function that will simply append a new row (encoding the event) to a log table that we will then poll from an external service.

Before going all in with Postgres TRIGGER(s) we would like to know how they scale: how many triggers can we create on a single Postgres installation? Does they affect query performance? Did anyone before tried this ?


texts: Need to run select statement across all databases

texts:
I am trying to run a query like the one below across multiple databases

select ParamValue from Table1 where paramname = 'SYSTEM_LICENSE'

Each DB is named something like the one below and some DB should be excluded.

  "Customer_123"

I looked at using sp_MSforeachdb but it isn't supported and it wasn't working well. I tried figuring out how to write a cursor but was unsuccessful. At this point just looking for some help from someone who can actually write what I am looking to do.


texts: Postgres: extracting multiple bit-columns from single 'flags' column after join with 2nd table

texts:
I have a query similar to this below. As you can see, there are multiple parts which are very similar.

SELECT
    id,
    aaaa,
    bbbb,
    tags
    ,(
        SELECT COUNT(*) > 0
        FROM A
        LEFT JOIN B
            ON A.id = B.parent_id
        WHERE A.buzz=C.buzz
            AND B.tags & 1 <> 0
    ) as "has_children_tag_1"
    ,(
        SELECT COUNT(*) > 0
        FROM A
        LEFT JOIN B
            ON A.id = B.parent_id
        WHERE A.buzz=C.buzz
            AND B.tags & 2 <> 0
    ) as "has_children_tag_2"
    ,(
        SELECT COUNT(*) > 0
        FROM A
        LEFT JOIN B
            ON A.id = B.parent_id
        WHERE A.buzz=C.buzz
            AND B.tags & 4 <> 0
    ) as "has_children_tag_3"
    ,(
        SELECT COUNT(*) > 0
        FROM A
        LEFT JOIN B
            ON A.id = B.parent_id
        WHERE A.buzz=C.buzz
            AND B.tags & 8 <> 0
    ) as "has_children_tag_4"
FROM A
LEFT JOIN Z
    ON A.id = Z.id

I know one thing, that I cannot split flag column into multiple bit columns, so I have to figure out how to query my existing tables in efficient way.

I am wondering is it possible (I don't know how to do that) to create some kind of temporary table for part:

    SELECT COUNT(*) > 0
    FROM A
    LEFT JOIN B
        ON A.id = B.parent_id
    WHERE A.buzz=C.buzz

to have a possibility to run on this tmp table WHERE B.tags & 1 <> 0, WHERE B.tags & 2 <> 0, etc.

Any help/suggestion will be appreciated.


texts:
I'm trying to make sense from "autocommit" concept.
Originally I've heart about it in the context of Java JDBC API Connection.setAutoCommit(boolean autoCommit)
Quick search revealed that the term is not specific to JDBC, PostgreSQL refer to "autocommit" too:
https://www.postgresql.org/docs/current/sql-start-transaction.html

In the standard, it is not necessary to issue START TRANSACTION to start a transaction block: any SQL command implicitly begins a block. PostgreSQL's behavior can be seen as implicitly issuing a COMMIT after each command that does not follow START TRANSACTION (or BEGIN), and it is therefore often called “autocommit”. Other relational database systems might offer an autocommit feature as a convenience.

If it is widespread what is isolation level of autocommit mode?
Conceptually as we have only one SQL statement before implicit COMMIT isolation levels almost make no sense for autocommit. Only READ UNCOMMITTED might influence the result, other anomalies are defined in term of a sequence of SQL statements.
Can I alter autocommit isolation level to / away from READ UNCOMMITTED?


texts:
How can I copy the content of a cell in the output pane where the latter is detached from the query window in  pgAdmin III?

When the output pane is not detached from the query window, I can use   control  +  C, but it doesn't seem to work when it is detached.

Here is a screenshot showing the output pane detached from the query window:

I use pgAdmin III 1.20.0 with Windows 7 SP1 x64 Ultimate (English).


texts: support of multiple databases on different hosts by pgpool2

texts:
Let's assume We have 2 databases:
Database1 is located on host1 and host2
Database2 is located on host3 and host4
If I'll use pgpool2 to connect to these 2 databases with single pgpool config for backend configuration:
 backend_hostname0 = 'host1'
 backend_port0 = 5432
 backend_weight0 = 1
 backend_hostname1 = 'host2'
 backend_port1 = 5433
 backend_weight1 = 1
 backend_hostname2 = 'host3'
 backend_port2 = 5434
 backend_weight2 = 1
 backend_hostname3 = 'host4'
 backend_port3 = 5435
 backend_weight3 = 1

With this kind of configuration, will pgpool2 throw an error in case of trying to connect to Database2 for example which is absent on host1 and host2 or it will determine easily necessary hosts which contain appropriate database with error and coordinate client in appropriate way to connect to it?
Why I am asking this, because I don't see that pgpool supports something like conf.d directory where I can put separately hosts with different databases in order to connect to them via clients. Instead I see support only of single config file which is pgpool.conf for backend definition.


texts: how to create a data type and make it available in all Databases?

texts:
If I create a stored procedure in the master database, and I want to execute it from any of my databases I just follow this link:

Making a Procedure Available in all Databases

that give me this code example:

Just by following the example above, I can call my procedure from any database.

what about if I create a table data type in master,
how can I use it in any of my databases?

use master

        IF NOT EXISTS (select * from sys.types where name = 'theReplicatedTables')
                CREATE TYPE theReplicatedTables AS TABLE
                (  OBJ_ID INT NOT NULL,
                  PRIMARY KEY CLUSTERED (OBJ_ID)
                );

use APIA_Repl_Sub
go
declare @the_tables [dbo].[theReplicatedTables]


texts: mySQL data table seems to stuck at 16777215 rows

texts:
I am creating a MySQL table and loading data from a local file.

CREATE TABLE `patent` (

  `seq_number` mediumint(9) unsigned NOT NULL auto_increment,
  `seq_id` varchar(24) default NULL,
  `date` date default NULL,
  `patent_number` varchar(16) default NULL,
  `pat_seq_number` smallint(6) unsigned default '0',
  `organism` text,
  `assignee` text,
  `seq_length` int(8) default '0',
  `seq` text,
  PRIMARY KEY  (`seq_number`),
  Index `seq_id` (`seq_id`),
  Index `patent_number` (`patent_number`),
  Index `pat_seq_number` (`pat_seq_number`),
  Index `seq_length` (`seq_length`)

) Engine = MyISAM MAX_ROWS=536870912;

load data local infile '/data/p_table.txt' into table  patent ignore 0 lines
(seq_id, date, patent_number, pat_seq_number, organism, assignee, seq, seq_length);

It seems that I am stuck at the number of rows:

mysql> select count(*) from patent;

+----------+
| count(*) |
+----------+
| 16777215 |
+----------+

1 row in set (0.00 sec)

Although my txt data table has about 20 million lines (records).

I tried Engine=InnoDB

I also tried Engine = MyISAM MAX_ROWS=200000000 (200 million)

mysql> show table status like 'patent'\G

*************************** 1. row ***************************

           Name: patent

         Engine: MyISAM

        Version: 10

     Row_format: Dynamic

           Rows: 16777215

 Avg_row_length: 244

    Data_length: 4098768552

Max_data_length: 281474976710655

   Index_length: 715227136

      Data_free: 0

 Auto_increment: 16777216

    Create_time: 2013-04-14 12:46:10

    Update_time: 2013-04-14 12:50:43

     Check_time: 2013-04-14 12:54:08

      Collation: latin1_swedish_ci

       Checksum: NULL

 Create_options: max_rows=536870912
        Comment:

1 row in set (0.00 sec)

The manuals and online advices all say I should be able to create large tables.

her is my uanme linux output.

Linux ... 2.6.32-279.el6.x86_64 #1 SMP Wed Jun 13 18:24:36 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux

Here is the verison for mySQL:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 318
Server version: 5.1.67 Source distribution

How can I create a larger table to increase the number of rows to larger than 2**24=1677215 rows?


texts: Compound Primary Key - Index both fields?

texts:
I have a many-to-many join table between users and roles.

CREATE TABLE AppUserRole
(
    UserId INT NOT NULL,
    RoleId INT NOT NULL,

    CONSTRAINT PK_AppUserRole PRIMARY KEY (UserId, RoleId),

    CONSTRAINT FK_AppUserRole_User FOREIGN KEY (UserId)
        REFERENCES AppUser(UserId),
    CONSTRAINT FK_AppUserRole_Role FOREIGN KEY (RoleId)
        REFERENCES AppRole(RoleId)
);

CREATE INDEX IX_AppUserRole_RoleId ON AppUserRole(RoleId)
CREATE INDEX IX_AppUserRole_UserId ON User(UserId)

Since the compound key is clustered in the order of (UserId, RoleId), is the second index on UserId really necessary?

When you query for all rows for a specific UserId it's going to use the clustered index. Only when you query for all users in a specific RoleId will the index IX_AppUserRole_RoleId be used.

Is the index IX_AppUserRole_UserId necessary?


texts: weekly moving average from current_date to the last 52 weeks

texts:
I'm a new head to the mysql programming. I have to write a procedure for calculating a weekly moving average grouped by week from 'monday-friday'.

Criteria is : current_date is the end date. start date should be either previous 52 weeks if available or if the number of weeks is less then the moving average should be calculated till the available date.


texts: Single-column user defined variable based on a comparison

texts:
I have a database with over 300k lines and each row is a separate timed event.  I have a column of system spec values for error, followed by a stochastic draw on an actual error for that event.  I can change the confidence level for the actual error by multiplying the actual error by a constant.

I have tried this query and while it returns a 1 in each error code, what I am looking for is a way to develop a user defined variable called “myerror” that is filled by each rows error * the constant.

sample query statements:

((select(actual_error * “constant”) <= lowest_threshold) as level_1_error,
((select(actual_error * “constant”)  between this and that) as level_2_error,
((select(actual_error * “constant”)  between higher_this and higher_that) as level_3_error,
…
…
…
((select(actual_error * “constant”) >= highest_threshold) as max_error,

What I’m trying for is something like:

Case when (select(actual_error * “constant”) <= lowest_threshold  set @user_error  =  “level_1_error”
          when (select(actual_error * “constant”)  between this and that) set @user_error  =  “level_2_error”

but, alas…..can’t seem to get it to work…..I’ve tried putting the set statement in a sub-query and just using a sub-query to select a different error level also…..

help bitte sehr.


texts: comparing two values in same column with another column using AND condition

texts:
Column A is MOBILE NO.
Column B is Agent Id  calc

MOBILE NO     Agent Id
7024226667    A1EXF8ZF  unique
7042004489    A1XKV3J1  unique
7042004940    A1JA23X1  unique
7042005480    A1UFIHOD  unique

Formula in excel they have used column calc is

IF(A2="","Unique",IF(AND(A2=A3,I2=I3),"Multiple","Unique"))

I'm trying in sql server to implement the logic.


texts: PostgreSQL: Find rows for which any index of any value contained is over a given size

texts:
I have a database originally built with an instance of PostgreSQL with a custom page size much larger than the default. I'm trying to migrate its contents to a standard, 8k-page database.

Because data indexed by a standard index in PostgreSQL must be no more than 1/3rd of a page size, some data contained in the old database cannot be represented in the new one without modifying index definitions. Making schema changes is not an immediately available option; dropping rows (from the tables I expect to be involved) is.

Finding overall size of an index in PostgreSQL is easy and well-documented. Finding individual rows for which associated index sizes are over a threshold is something I'm having some trouble.

How can I find rows (across all tables in a schema) containing indexed values for which the indexes of said values are too large to represent in a standard PostgreSQL build (which is to say, longer than 2712 bytes)?


texts: Is it possible to setup failover clustering between a 32 bit instance and a 64 bit instance?

texts:
I am attempting to setup a couple of virtual machines in my lab for testing failover clustering on SQL Server 2008 and Windows Server 2008.

One of the hosts I have is not capable of running a 64 bit virtual machine, however the other host is.  Sure enough, I installed the 64 bit machine (host A) first without realizing host B cannot run a 64 bit VM.

Can I run failover clustering with one node on x64 and another node on x86?  Or do I have to re-install the OS on host A to be 32-bit?


texts: Linked server from SQL server on AWS RDS to On premises Oracle database

texts:
We are planning to migrate an on-premises Microsoft SQL server database to AWS RDS SQL server. Everything is good except one roadblock, i.e  this SQL server database has a linked server to on premises Oracle database.
I read in AWS documentation that currently RDS SQL server only supports linked server to other RDS SQL server within same VPC. So how can I create a linked server to an on-premises Oracle database from RDS SQL server?
Can anyone please suggest best alternatives/solutions for this scenario.


texts: What data is duplicated when MySQL/MariaDB BLOB columns are copied?

texts:
Let table_1 be created as follows:

CREATE TABLE table_1 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    some_blob BLOB
);

Let table_2 be created as follows:

CREATE TABLE table_2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    some_blob BLOB
);

What I want to know is, after I run this table-copying query

INSERT INTO table_2 (id, some_blob) SELECT id, some_blob FROM table_1;

will the actual text within each some_blob field of the table_1 table be duplicated and stored on disk, or will the DB have only duplicated pointers to the disk locations containing the BLOB data?

One argument for why BLOB copying must involve the duplication of actual content reasons as follows:

  Duplication of BLOB content is necessary because changes to BLOB data in table_1 should not also take place in table_2. If only the disk pointers were duplicated then content changes in one table would be reflected in the other table, which violates the properties of a correct copy operation.

Now I present an alternative method that the DB could implement to satisfy this copy operation. This alternative shows the above argument is not necessarily true. The DB could only duplicate disk pointers during the execution of the given INSERT statement, then whenever an UPDATE occurs which seeks to modify the BLOB data in one of the tables, the DB would only then allocate more space on disk to store the new data which is part of the UPDATE query. A BLOB data segment then is only deleted when there no longer exists any disk pointers to it, and a particular BLOB data segment could potentially have many disk pointers pointing to it.

So which of these strategies does MySQL/MariaDB use when executing the given INSERT statement, or does it use a different strategy?

Why I am asking this question

Currently I am running a couple of UPDATE queries which are copying large amounts of BLOB data from one table to another in the same database (over 10 million rows of BLOB data). The queries have been running for a while. I am curious about whether the performance is so slow because some of the columns I am comparing are poorly indexed, because these queries are literally copying over the content instead of disk pointers, or perhaps because of both of these reasons.

I use an INSERT in the question's example because this simplifies the concept of database internals that I am trying to understand.


texts: How to point Azure Hybrid Connection to Availability Group Listener ? Or redirect to secondary replica?

texts:
We have 2 SQL Servers on-premise, combined into Availability Group

We want to install windows updates, which will most likely require a reboot of virtual machines, and we are going to use rolling update technique (update secondary replica; fail over to secondary so it becomes primary; update former primary)

Applications that are located on the on-premise web server, are going to be fine, since web-servers' connection strings are pointed to AG Listener

But we also have apps running in Azure,
which are connecting to on-premise DB (to primary DB replica directly) using hybrid connections.
Azure Apps are going to be down if primary replica is down

Question: is there any way that we can point Azure hybrid connections to AG listener ? Or at least redirect hybrid connection to secondary replica if primary is not available for 1-2 mins ?


texts:
I have a largish 27mil row table.  The table contains a large number of duplicate string values due to case variance.

All rows have the string column and also one additional numerical column, and I am trying to identify duplicates, and then ignore the row with the numerically highest secondary value within each duplicate set and export the remaining rows to a new table.

I have tried a number of approaches, but performance has been so poor as to make them nonviable even as a one time event.

What is the most performant approach for this kind of duplicate search with logic?

Platform is MySQL.


texts:
I have Column A and Column B in a single table.

I need to add 1,000,000 records to this table.

I have 2 options:

Keep 2 columns and add 1,000,000 records
Add more columns and flatten out the data. This will decrease the row count by half.

Am I correct in assuming the table size will be the same for both options?


texts: How do I manage environment specific connections for SSAS projects?

texts:
I am using SQL Server 2012 and I am responsible for implementing release automation for our SQL, SSIS, and SSAS projects.

SSIS projects have project parameters and you can link those to SSISDB environments so in each environment (Dev, QA, Staging, Production, etc...) you can have specific values for things like database servers, mail servers, etc...

In SSAS, I cannot find an eloquent solution for doing this for the defined external Data Sources.  After I build the solution I need a way of updating the ConnectionString "Data Source" and "Initial Catalog" values in the .configsettings and the .asdatabase files so they point to the environment specific values.

The only way I have found to do this is to have an intermediate step between the build and the deployment that will go in and replace the values using search / replace.  I don't feel like this is scalable and it seems like it is error prone.

Does anyone have suggestions on how this should be handled as a best practice?


texts: how to query multiple counts on related tables

texts:
For each Forum, there are many Posts, and for each Post, there are many Comments

I'm trying to query the total number of Posts and Comments for each Forum

So I'm looking to have an output as such:

Forum name | post_count | comment_count
honda cars     256          489
toyota cars    53           230

Here are the tables

Forum
::Posts
  ::Comments


texts: SSIS via Import Wizard SQL Server 2008 R2

texts:
I have a flat file i need to import into SQL. For a lot of boring reasons, I curently don't have access to use BIDS, only the import wizard in SSMS. The flat file looks sort of like:

A
2
B
G
-blank line here-
8
R
F
6

Basically, a few lines, separated by an empty line, Repeat a few thousand times. The text field here doesn't show it all that well, if you have questions about that, I can try to clarify.

What i need is for each of those groups to be its own row, merged together with a delimiter. So, the data in the table would look like:

A,2,B,G
8,R,F,6

Only one column in the table, with a delimited string of data in that column.

Can this be accomplished via the wizard? If not, can it be accomplished with BIDS and a real SSIS package? That may be an option, but it will probably be a while before I have access to that, and by that time, it may be moot.


texts: MySQL General error: 126 Incorrect key file for table '/tmp/#sql_27e6_0.MYI'; try to repair it'

texts:
I have a table in MySQL with ~2,000,000 rows.

When I try to select all records, it exhausts the system's memory and I get an error about memory exhaustion back.

When I try to use the MySQL LIMIT and select 1,000 records, the error in the title is returned:

General error: 126 Incorrect key file for table '/tmp/#sql_27e6_0.MYI'; try to repair it'

What's the best course of action to export all of the data in this table?  Why is it returning an error when only trying to pick 1,000 records?

My query:

select * from notes order by id asc limit 1000


texts: PostgreSQL 9.2 - 9.6 upgrade with zero downtime

texts:
I have to upgrade from PostgreSQL 9.2 to 9.6. Following are the challenges that I am facing:

Since we have streaming replication setup, and PostgreSQL doesn't support lower to upper version upgrade when in streaming replication mode, when I upgrade the master I have to rebuild the slaves and that takes 3 hours. I don't have that time to spare. At any given time we need one slave and one master available. Is there any other way to upgrade without having to rebuild the slaves, using streaming replication?
To build logical replication we thought of using slony but slony has some limitations in that it does not automatically replicate:

Changes to large objects (BLOBS)
Changes made by DDL commands
Changes to users and roles
...and our application has continuous create commands. So we cannot use slony.

Please suggest something to avoid slave rebuild and to do upgrade with minimal downtime and one master and one slave ready.


texts: How to get multiple row data into a row with multiple columns

texts:
I have a MySQL table like this:

User_Id  course_name     course_location   course_id
1        course name 1   location 1        1
1        course name 2   location 2        2
1        course name 3   location 1        3
2        course name 2   location 1        2
2        course name 4   location 4        4

How can I get data a result like this:

User_id  course 1       course2        course3        course4
1        yes-location1  yes-location2  yes-location1  NULL
2        NULL           yes-location1  NULL           yes-location4


texts:
I appreciate that the above should not be done ('Creating a New Database Across a Network with SQL Server 2008 R2') for all of the reasons outlined here but a client wants to know how to do this and cannot be told otherwise.

To setup and test this I have two laptops connected across a local area network (machine one is 'VAIOE', machine two is 'VAION'). I am attempting to write a new database using VIAOE running SQL Server 2008 R2 Developer Edition across the network to a shared drive on VIAON called 'CShare' (VAION's C:Drive). I have enabled all of the appropriate permissions such that I am able to navigate (using VAIOE) on to VAION '\VAION\CShare\Databases\' and create a new Access database (for example).

Now to attempt to create a database (from VAIOE onto the C:Drive of VAION) using SQL Server 2008 R2 in management studio, I open a new scipt and write (USING Master):

CREATE DATABASE [networkedR2] ON  PRIMARY
( NAME = N'networkedR2', FILENAME = N'\\VAION\CShare\aaData\aaSSDBs\networkedR2.mdf' ,
  SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'networkedR2_log', FILENAME = N'\\VAION\CShare\aaData\aaSSDBs\networkedR2_log.ldf' ,
  SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

and I get the following error

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "\\VAION\CShare\aaData\aaSSDBs\networkedR2.mdf" failed with the operating system error 5(failed to retrieve text for this error. Reason: 15105).
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

indicating I do not have the correct permissions. Can someone tell me ewhat I have done wrong here as I have hit a wall?

Edit:
I am aware that for earlier versions of SQL Server the DBCC TRACEON(1807, -1) flag was required. This should not be the case here.


texts: What's is the best way to get incremental changes from production to incrementally changed development database?

texts:
I came to a new dev team where all development is on the same (instance) of an SQL Server (2005) database once in 1-2 days restored from a current full backup of production database replacing a dev database (simple recovery model).

So, the developers are essentially changinging stored procedures and some testing data whiich they really need between all perturbations (changes made back and forth)  and then told that it is again time "X" when a testing database is to be replaced from production in a few minutes in order to backsave all their stored procedures and testing/development data needed for development.

The dev changes are being transferred to testing then to production database. The "updates" from production database to dev database are needed I don't know why but they happen frequently... once every 1-2 day and I can give here only some guesses, but it is status quo in the team.

This is really enerving and without some experience (at least for me ) leads to loosing a lot of developed/changed stored procedures and testing data plus always and for all .

What would  be the best approach to get incremental changes (by users, purely in data, without any schema or programming) from production to dev database without replacing mostly incremental data and incremental programming (mostly in stored procedures) changes?

Update:
Note that we do not hide testing data in production database (they are mostly administrative data like testing/dev users which are anyway not visibly to normal users)

Update2 (answering comments):
@Jon-Seigel,

I couldn't answer in comments

The dev changes to database are scripted and kept with solution in VSS but... But they are checked in only after passing testing on separate machine/asp.net site. And this is lagging a month. So, there is discrepancy between changes kept under version control and dev changes being overwritten by backup restore.

@neil-mcguigan,
the dev database are completely overwritten by full backup restore from production database in order to get recent changes in data. So, any dev changes are being lost and should entered again - the programming ones by scripts (if they were not taken in time and not lost) and data changes by manual entering


texts: postgres jsonb return specific paths of json object

texts:
Consider the following jsonb object:

{
  "track": {
    "segments": [
      {
        "location":   [ 47.763, 13.4034 ],
        "startTime": "2018-10-14 10:05:14",
        "HR": 73
      },
      {
        "location":   [ 47.706, 13.2635 ],
        "startTime": "2018-10-14 10:39:21",
        "HR": 135
      }
    ]
  }
}

I would like to query the object, to return only the following jsonb object, given the folloiwng paths:

track.segments.hr

{
  "track": {
    "segments": [
      {
        "HR": 73
      },
      {
        "HR": 135
      }
    ]
  }
}

How can i formulate the query, to retrieve this kind of data from the json, and still return the json object, with only the specified paths?


texts: Backup and restore database - occured system error 5

texts:
In my work I made backup of database and I receive file .bak. Now I would like restore this database in my machine. But I get the following error:

  Cannot open backup device 'C:\Program Files...\MSSQL10_50.SQLEXPRESS...\file.bak'.
  Operating
  system error 5(failed to retrieve text for this error. Reason: 15105).
  RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server,
  Error: 3201)

Of course I searched the Internet, for example:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/805661f1-cf23-4230-ae31-d9cc16505359/cant-restore-backup-from-2005-to-2008r2?forum=sqlexpress

But all users have permissions to all folders so I'm really confused. What is the problem?


texts: Innovative solution required - Remotely create stored procedure

texts:
I have a local stored procedure that I want to distribute out to many other SQL Servers without having to log onto each one manually.

All servers can access the same network share if I want to go down the .sql and SQLCMD route.

Alternatively all servers are linked to a "Central" server using a the same login credentials.

Essentially I want to avoid using lengthy OPEN QUERY's because the syntax becomes a management nightmare when working with lengthy stored procedures.

Thoughts welcome/appreciated


texts:
So we've come across an astounding issue here. At random moments, our DB is dropping the database and recreating the table with data from an earlier point in time.

Unfortunately general query logs were not on, however we have binary logs (statement format) that prove the scenario in question. We use a MySQL database, with most tables in InnoDB format, with a master to slave setup. The MySQL error logs do not provide any insight into the situation, minus a buildup of "Unsafe Statement" warnings in regards to the replication.

Any idea why could this happen?

Thanks,
Shafique


texts: Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor )

texts:
I have already seen the questioned in the stack overflow that might relate to my problem but let me emphasize that none of them could resolve my issue.

issue is: when I wanted to integrate OWASP WebGoat application with oracle XE 11g , I receive the above mentioned error.

connection URL in web.xml is:

jdbc:oracle:thin:@//localhost:1521/XE/${USER}

=================================================================
listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = Amir-PC)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

=============================================================
tnsnames.ora:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Amir-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

===================================================================
java version:

java version "1.7.0_55"
Java(TM) SE Runtime Environment (build 1.7.0_55-b13)
Java HotSpot(TM) Client VM (build 24.55-b03, mixed mode, sharing)

===================================================================
output of the lsnrctl services:

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 09-MAY-2014 16:34
:07

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: AMIR-PC, pid: 5740>
         (ADDRESS=(PROTOCOL=tcp)(HOST=Amir-PC)(PORT=2403))
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

=====================================================================
output of the tnsping xe :

TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 09-MAY-2
014 16:35:19

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Amir-PC)
(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
OK (0 msec)

could some one please help me resolve this issue? I have been struggling more than 3 days and not found any useful answer yet.


texts:
How can I use 'system pwd' command with MySql CONCAT() function? I tried

select concat('test', system pwd);

gives me

ERROR 1583 (42000): Incorrect parameters in the call to native function 'concat'

Is there any way? My database version 5.1


texts: How to create a server instance of just ComputerName instead of ComputerName\SQLEXPRESS

texts:
I'm currently hosting a database on the server MyComputerName using SQL Server Express 2014.

I want to move the database to another computer, so I installed SQL Server Express 2014 on it but that computer only has the instance ThatComputerName\SQLEXPRESS. When I try to connect to just ThatComputerName it throws the error

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

However, I can't enable remote connections on ThatComputerName because I can't even connect to that instance.

I don't remember what I did on the previous computer to allow me to connect both to MyComputerName and MyComputerName\SQLEXPRESS, how can I create that instance on the new computer? Do I have to reinstall?


texts:
I am using mysql 5.5. I create following temporary table:

CREATE TEMPORARY TABLE IF NOT EXISTS tempdb.%s (
        `count` int(10) NOT NULL,
        `m_class` enum('SMS','EMAIL') NOT NULL,
        `priority` enum('HIGH','DEFAULT') NOT NULL,
        `time_taken` bigint(10) NOT NULL,
         KEY `m_class` (`message_class`),
         KEY `priority` (`priority`),
         KEY `time_taken` (`time_taken`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

My temporary table (both temp_lat2 and temp_lat3) has around 1 million records. And then I fire following query:

SELECT SUM(t1.count) sr, t2.time_taken l , SUM(t1.count)/<some_integer_value> p
        FROM (SELECT * FROM tempdb.temp_lat2 where m_class = 'SMS' and priority = 'HIGH') t1
        JOIN (SELECT * FROM tempdb.temp_lat3 where m_class = 'SMS' and priority = 'HIGH') t2
        ON t1.time_taken < t2.time_taken
        GROUP BY l
        HAVING p > 0.99
        ORDER BY p
        LIMIT 1;

Now the above query is first for around 10 times via a script which just changes some_integer_value. After 4th or 5th round, the above query gets hung and that too for more than 2 to 3 hrs, and at the end I have to kill the query.

Can anybody suggest the reason behind this problem? Is something wrong with my query?


texts: Log Shipping - Secondary Database(s) - Which recovery model?

texts:
In Log shipping configuration - the primary database must be either in Full or Bulk-Logged recovery model. However, is there any requirement of recovery model of the secondary database?

Example: Assuming both primary and secondary databases are in full recovery model, if there is a failover and the secondary becomes the new primary, we could change the db recovery model.

Also refer to: https://www.microsoftpressstore.com/articles/article.aspx?p=2832586&seqNum=3 and search for "Log shipped databases have to use the full recovery model." - is this correct?


texts: Group by data by month with selected fields not to be included in group by class

texts:
Is there any way to group by the data by the only month but I need the no_inv and date_inv columns to be selected too.
Mysql query :
SELECT no_inv, date_inv, SUM(total_unpaid) AS unpaid, SUM(total_paid) AS paid
FROM invoice
GROUP BY MONTH(date_inv)
LIMIT 3