PSQL trying to connect to wrong database I have noticed recently a weird (at least to me) behaviour of psql when trying to run psql -l. The essential steps to reproduce it are:

Create a sample DB (I did it with docker and created a simple role with a password)
Export environment variables that are used by psql

PGPASSWORD=password
PGDATABASE=database
PGUSER=username

Edit the pg_hba.conf. And instead of having the default for local I replaced it with. Of course don't forget about reloading the configuration.

local   database          username                                  trust

Running psql works as expected but running psql -l results in

psql: FATAL:  no pg_hba.conf entry for host "[local]", user "username", database "database", SSL off

This seems to not take the environment variables into account when making a connection to the database.

This is expected behaviour.

The man page for psql says (emphasis mine):

  -l

  --list

  List all available databases, then exit. Other non-connection options are ignored. This is similar to the meta-command \list.

  When this option is used, psql will connect to the database postgres,
  unless a different database is named on the command line (option -d or
  non-option argument, possibly via a service entry, but not via an
  environment variable).


When to use stored procedure / user-defined function? I'm confused about usage of SP (stored procedures) and UDF (user defined functions). Typically, it is also possible to write code in programs outside of the database.
Is there any general advice to decide when to use them?

Postgres 11 or newer
SQL procedures ("stored procedures") that can also start and end transactions:

The manual for CREATE PROCEDURE.

2ndquadrant blog entry introducing the feature.

However, commands that are not allowed in a transaction block can not (yet) be included. Important examples CREATE DATABASE or CREATE INDEX CONCURRENTLY or VACUUM.
And procedures can only return a single result row, yet, when created with INOUT parameters.
Postgres 14 or later also allows OUT parameters.
Postgres 10 or older (original answer)
There are no "stored procedures", strictly speaking. Just functions, doing almost but not quite the same. Most importantly, functions always run inside a transaction, which matters for lock management, trapping errors, or commands that cannot be run in a transaction context (see above). Related:

In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?

Processing data in SQL can have major benefits over retrieving all raw data into your application and doing calculations there:

What are the pros and cons of performing calculations in sql vs. in your application

The same principle can apply to bigger operations that can't be done in a single statement, not even when using "chained" commands in a (data-modifying) CTE:

Are SELECT type queries the only type that can be nested?
RETURN QUERY-Record in PostgreSQL

Depending on the use case, a function can be superior in performance, and often more convenient - especially when multiple apps access the same database. You have to chose a server-side language, most commonly SQL or PL/pgSQL:

Difference between language sql and language plpgsql in PostgreSQL functions
Function Performance

In the presence of competing concurrent transactions (multi-user environments) be brief. Only put as much into the same function as needs to be atomic. If possible, avoid long transactions with many locks. Locks are only released at the end of a transaction. Long transactions may stall concurrent access, even lead to deadlocks (which are detected and resolved by Postgres automatically, by raising an error for one or more competing transaction, which are then rolled back) ...
If you avoid those anti-patterns, server-side functions can be a great tool. For some purposes you must use functions anyway, like trigger functions.


SQL80001 - incorrect syntax near ')' In the below code (that I've inherited), I'm getting the message "SQL80001 - incorrect syntax near ')'", the error being on the third line from the bottom with the + sign. I've looked at THIS SO answer to similar issues, but that wasn't the issue here.

select
b.batch_id,
b.submitted_dt,
b.batch_nm,
b.paused_cd,
b.job_cnt,
b.start_dt,
b.finish_dt,
m.module_nm,
m.module_id,
mc.hostname
from
    tbl_bat_chess b,
    tbl_job_bobs j,
    tbl_man_holes m,
    tbl_monkey_cokes mc
where
b.finish_dt is null
and  to_char(b.submitted_dt, 'DD-MON-YYYY') = to_char(sysdate, 'DD-MON-YYYY')
and  (to_number(sysdate - b.submitted_dt) * 1440)  >  5 -- 5 minutes
and b.batch_id = j.batch_id
and b.batch_nm like '%EXAMPLE%'
and j.module_id = m.module_id
and m.proc_group_id not in (10, 18)
and j.machine_id = mc.machine_id (+)
group by b.batch_id, b.submitted_dt, b.batch_nm, b.paused_cd, b.job_cnt, b.start_dt, b.finish_dt, m.module_nm, m.module_id, mc.hostname
order by submitted_dt desc

DBMS: PLSQL Developer 10.05.

Oracle Version: 11g

I've manually removed all non-breaking spaces and tried putting square brackets around the line mc.machine_id with no success. How should this be resolved?

SQL80001 is a SQL Server error message. SQL Server doesn't support the old Oracle (+) syntax for outer joins.

Re-write your SQL using LEFT/RIGHT OUTER JOIN .... ON .....


Postgres Streaming Replication lagging, using lots of CPU and little I/O We have 2 dedicated DB Servers. One is master and other is its streaming slave.
Postgres version 9.0.17.

We have similar hardware on both servers and using 15krpm SAS drive in Raid 10 for Database cluster. The only difference is that on server we have 128 GB ram and on slave we have 64 GB ram.

The issue which we are facing is that our streaming slave is lagging behind.

On the streaming slave we have

postgres=# select pg_last_xlog_receive_location();
 pg_last_xlog_receive_location
-------------------------------
 1F7D/DD3BA000

postgres=# select pg_last_xlog_replay_location();
 pg_last_xlog_replay_location
------------------------------
 1F75/5BE08980

This difference between wal received and replay is increasing constantly.

Please let me know that how this issue can be resolved.

Output of perf top -u postgres is

98.30%  postgres          (.) DropRelFileNodeBuffers
0.20%  postgres          (.) 0x000000000008b05b
0.14%  (kernel)          (k) copy_user_generic_string
0.13%  postgres          (.) hash_seq_search
0.07%  postgres          (.) hash_search_with_hash_value
0.05%  (kernel)          (k) _spin_lock
0.05%  (kernel)          (k) apic_timer_interrupt
0.03%  (kernel)          (k) do_timer
0.03%  postgres          (.) RememberFsyncRequest
0.03%  (kernel)          (k) __sb_start_write
0.02%  (jbd2)            (k) do_get_write_access
0.02%  (kernel)          (k) unroll_tree_refs
0.02%  (cciss)           (k) do_cciss_request
0.02%  (ext4)            (k) ext4_check_dir_entry
0.02%  (kernel)          (k) path_walk
0.02%  (kernel)          (k) scheduler_tick
0.02%  postgres          (.) LWLockAcquire
0.02%  (kernel)          (k) dyntick_save_progress_counter
0.02%  (kernel)          (k) _spin_lock_irqsave
0.02%  (kernel)          (k) audit_syscall_entry
0.02%  (ext4)            (k) ext4_journal_start_sb
0.02%  (kernel)          (k) radix_tree_range_tag_if_tagged
0.01%  (kernel)          (k) sys_getppid
0.01%  (kernel)          (k) native_read_tsc
0.01%  libc-2.12.so      (.) _IO_str_init_static_internal
0.01%  (kernel)          (k) __getblk
0.01%  (kernel)          (k) run_timer_softirq
0.01%  (kernel)          (k) system_call
0.01%  postgres          (.) __errno_location@plt
0.01%  libc-2.12.so      (.) __GI___libc_lseek64
0.01%  postgres          (.) XLogReadBufferExtended
0.01%  (kernel)          (k) memcpy
0.01%  (kernel)          (k) irq_work_run
0.01%  postgres          (.) fsm_set_avail
0.01%  (kernel)          (k) kmem_cache_free
0.01%  (kernel)          (k) do_unlinkat
0.01%  libc-2.12.so      (.) vfprintf
0.01%  postgres          (.) hash_any
0.01%  (kernel)          (k) sysret_check
0.01%  (kernel)          (k) mutex_lock
0.01%  (kernel)          (k) printk_tick
0.01%  postgres          (.) elog_start
0.01%  (kernel)          (k) __percpu_counter_add
0.01%  (kernel)          (k) file_update_time
0.01%  (kernel)          (k) enqueue_task_fair
0.01%  (kernel)          (k) blk_recount_segments
0.01%  (kernel)          (k) rcu_check_callbacks
0.01%  (kernel)          (k) put_pid
0.01%  postgres          (.) mdunlink
0.01%  postgres          (.) LWLockRelease
0.01%  (kernel)          (k) current_kernel_time
0.01%  (kernel)          (k) perf_event_task_tick
0.01%  (kernel)          (k) native_apic_mem_write

output of gdb for postgres startup process

(gdb) thread apply all backtrace Thread 1 (Thread 0x7fd06d28f7c0 (LWP 26949)):
#0  0x00000000005ec320 in DropRelFileNodeBuffers ()
#1  0x0000000000603115 in smgrdounlink ()
#2  0x000000000047d992 in ?? ()
#3  0x000000000048eca9 in StartupXLOG ()
#4  0x0000000000490c58 in StartupProcessMain ()
#5  0x00000000004a6faf in AuxiliaryProcessMain ()
#6  0x00000000005d31c3 in ?? ()
#7  0x00000000005d7372 in PostmasterMain ()
#8  0x000000000057e320 in main ()

Current (true at least in PostgreSQL 9.4 and older) PostgreSQL releases have single-threaded WAL recovery.

This means that replay of the write-ahead log occurs in only one recovery worker, and is thus able to benefit less from I/O concurrency than a normal running master. This can result in WAL replay lagging behind in cases where the replica and master have similar hardware, even when you'd expect the master (which is generally also under more load) to be the slower server.

(It'd be  great to improve this, but wanted it enough to do the work yet, and nobody seems keen to fund it at the moment).

That said, your case with very high CPU use doesn't seem to fit with issues of low I/O concurrency. In your situation I'd be attaching gdb and seeing what the replay proess was doing, or using perf top to examine what the system as a whole is up to, then dig deeper with perf once I had some clues. I'd also be looking closely at iotop, vmstat, iostat, the PostgreSQL logs, dmesg, etc.

After investigation

Profiles revealed that most time was being spent in DropRelFileNodeBuffers.

That does a linear scan through shared_buffers whenever a relfilenode is deleted - caused by truncate, drop table, cluster, drop index, etc. This must be done during WAL replay, as well as on the main node.

So this suggests that:

Your shared_buffers is probably very big; and
You're probably doing lots of operations that delete relfilenodes

Reducing shared_buffers on the replica may well help.


Best approach to delete and update a big amout of rows in MySQL I'm currently developing a REST API that connects to a MySQL database and does a bunch of queries.

In one of those queries I happen to have to delete a bunch of rows from a table and each of those rows have a unique identifier.

And basically I have two approaches to do so: (that I know).

Build a bunch of DELETE FROM table WHERE id=value; statements and execute them sequentially.
Build only a DELETE FROMstatement but with a massive OR chain.

I don't have a ton of experience handling databases and any suggestion or advice would be greatly appreciated.

EDIT:

I just noticed that I also have something similar happening with a UPDATE FROM statement where I set a lot of records to the same given value, so any advice on that too would be really nice.

Simple and easy:

DELETE FROM tbl
    WHERE id IN ( 11,22,... 999 );

But...  If you have thousands of ids to delete, let's discuss where they the ids came from.  If they are sitting in another table, or can be found via some form of SELECT, then a "Multi-table DELETE" is probably a better way to do it.

Or if you know that the rows are consecutive in some way, then there may be other options.  For example, to delete "old" rows:

DELETE FROM tbl
    WHERE datetime < NOW() - INTERVAL 7 DAY;

will delete rows older than a week.


What's the best way to convert dtsx packages to query a new server? We just moved our sql server to a hosting company, and now all my dtsx packaged don't work.

I know that in a specific query you can use

    use SecondDatabase

but is there a way to do that with the server, too?

Goal: Not have to rewrite my code and paste [eih-dr01.db.org].livendb.dbo. infront of every table.

Two options come to mind:

If your dtsx packages are stored in the file system, you could use POWERSHELL  to read in each file, search and replace the connection string and rewrite the file.  Here is an example:

(Make sure you make a copy of the folder BEFORE running the Powershell script, just in case):

$DtsxFiles = Get-ChildItem C:\Users\uswsh\Documents\*.dtsx -rec
foreach ($file in $DtsxFiles)
{
    (Get-Content $file.PSPath) |
    Foreach-Object { $_ -replace "localhost", "localhost1" } |
    Set-Content $file.PSPath
}

If your packages are not stored in the file system, you could dynamically generate CREATE SYNONYM commands that would point to the new location.

CREATE SYNONYM [dbo].[OriginalTable] FOR [RemoteServer].[RemoteDatabase].[RemoteSchema].[RemoteTable]


How to mark the default row to select in a many to many relationship To start off, I have the following tables:

products - a list of products
images - a list of images (which may or may not belong to a product)
product_images - a join table to connect products to images (this table just has id, product_id, and image_id)

I need a way to mark one image as the default image to use for a product, but I'm not quite sure how to set it up. I've thought of a few options but none of them feel correct.

Options I've thought of:

Add an image_id field to the products table for the default image. This seems bad cause the image on the product might not be an image that belongs to that product (I could take care of this on the application side, but I'm looking for more elegant solutions)
Add a column on the product_images table that marks that image as default, and add a unique constraint on the combined product_id field and default field (the product_images table would then look like: id, product_id, image_id, default). I've never used unique constraints before, and I'm not sure if they're the right way to go.

Hope you guys have some better advice!

A UNIQUE constraint on (product_id, default) won't work since you can have several images that are not default for each product. A similar approach is to add a display_order attribute:

CREATE TABLE product_images
( product_id ... NOT NULL
      references ...
, image_id ... NOT NULL
      references ...
, display_order SMALLINT NOT NULL
,     PRIMARY KEY (product_id, display_order) )

To get the default image for a product choose the one with the smallest display_order for that product.

Depending on your business requirements you may add an alternative key. Examples:

-- An image may not be reused between products
ALTER TABLE product_images ADD CONSTRAINT ...
    UNIQUE (image_id)

-- An image may not be used several times for each product
ALTER TABLE product_images ADD CONSTRAINT ...
    UNIQUE (image_id, product_id)

To get the default image for a product:

SELECT image_id
FROM product_images
WHERE product_id = ?
ORDER BY display_order
LIMIT 1;

EDIT: I keep forgetting this bug in MySQL ...

To assign a new default image for a product:

UPDATE product_images x
    SET display_order = (SELECT min from (SELECT MIN(display_order)-1 as min                                        FROM product_images
                                          WHERE product_id = ?) as y)
WHERE product_id = ?
  AND image_id = ?;

or possibly:

UPDATE product_images x
    SET display_order = (SELECT min
                         FROM (
                             SELECT display_order-1 as min
                             FROM product_images
                             WHERE product_id = ?
                             ORDER BY display_order LIMIT 1
                         ) as y
                        )
WHERE product_id = ?
  AND image_id = ?;


T-SQL - AOAG Health state Healthy or Not Healthy in a Single row I am trying to write a small piece of code which should give the status of the AOAG SQL Server 2014 Health or not healthy based on below two conditions .

Mine is 3 Node replica AOAG (including BCP Replica).

Conditions I am looking for :

If the synchronization_health_desc is "Healthy" for all three replicas , I should get the result as below :

If the synchronization_health_desc is "Not Healthy" for any of the three replicas , I should get the result as below :

So far I have tried below :

select dns_name as AG_Listener ,
(select top 1 group_name  from sys.dm_hadr_availability_replica_cluster_nodes  ) as AG_Gorup,
synchronization_health_desc as AG_Status
from sys.dm_hadr_availability_replica_states rs
INNER JOIN  sys.availability_group_listeners gl ON rs.group_id = rs.group_id

The output is below . But I want only one row as result which says overall result as Healthy or Not Healthy based on above two conditions .

You have two states - everything is healthy or something is not healthy. This applies per availability group. So if anything's health is 'NOT_HEALTHY' that's the group's status, otherwise the status is 'HEALTHY'.

There are various ways to find if anything is unhealthy. One is EXISTS. Another is aggregate functions, some of which also work on character types. MAX() & MIN() are the useful ones. In a dictionary sort 'NOT_HEALTHY' > 'HEALTHY' so MAX('HEALTHY', 'NOT_HEALTHY') will return 'NOT_HEALTHY'. There is only one MAX in any GROUP BY so this removes the need for TOP clauses.

The query will look like

select
  case max (synchronization_health_desc)
    when 'NOT_HEALTHY' then 'NOT_HEALTHY'
    else 'HEALTHY'
  end
from <those tables, joined>
group by
  group_name

Once the GROUP BY is introduced other returned columns must also be in aggregate functions or mentioned in the GROUP BY clause.


How to find out CPU utilization per Oracle Database on Linux? For capacity planning on existing RedHat servers hosting Oracle Databases, I was asked how to find out the CPU utilization per database.

Thought it would be as simple as

ps -eo pcpu,comm |grep -i oracle_dbname

But I'm not sure if this is the right approach.

All the oracle processes are kicked off by the oracle executable, and if you're running multiple databases on a server, all of the processes for each database will show up with just that executable.

Adding the args option to the ps command will add the database name both for the instance processes (such as pmon) and server processes (those satisfying user activity) to the ps output to allow grepping.

What I don't know is what you'll get is useful, considering ps is a snapshot of activity as of a given instant in time, and that seems like a very strange way to do capacity planning.


Insert constraint issue Using IBM Informix database.
If I specify order_no manually, the statement works but if I try to increment it within the query, it fails.

Table structure

patkey serial
notetype char(4)
order_no int

The constraint is patkey, notetype and order_no

I can manually insert running the following and increment the order_no

insert into patnotes (patkey, notetype, order_no) values (5000, 'GEN', 3)

I wanted to increment it within the insert statement but I get a duplicate violation and don't understand how.

Here is the insert statement that I'm trying to use to increment the order_no.

INSERT INTO patnotes (patkey, notetype, order_no)
SELECT FIRST 1
    p.personkey,
    'GEN' as notetype,
    CASE WHEN n.order_no is null then 1 else (n.order_no + 1) end as order_no
FROM
    person p
LEFT JOIN
    patnotes n
    ON p.personkey = n.patkey
    AND n.notetype = 'GEN'
WHERE
    p.patid = '5000'
ORDER BY
    n.order_no DESC

Checking the table the following entries exist

5000, GEN, 1
5000, GEN, 2

The next order should be 3.

Running the select portion of the above insert statement gives:

5000, GEN, 3

This is correct but fails the constraint. If I enter these manually in the first insert statement, there is no issue.

Now the next odd issue is if I clear out these entries so the first entry is order_no 1.

This will add entries for order_no 1 and 2 but then fails for 3+ but again if I specify this manually in just an insert statement, no issues.

I'm not seeing what I'm missing

Assuming you are using a recent 12.10 Informix version, the online documentation states:

  As indicated in the INSERT statement syntax diagram, not all clauses
  and options of the SELECT statement are available for you to use in a
  query within an INSERT statement. The following SELECT clauses and
  options are not supported in an INSERT statement:

  FIRST and LIMIT
  INTO TEMP, INTO RAW, and INTO STANDARD result table options
  UNION, UNION ALL, INTERSECT, MINUS, and EXCEPT set operators.

So FIRST is not supported in SELECT statements used in an INSERT context. But using FIRST in the query does not give any syntax error, so maybe something else is going on.

-- The setup
CREATE TABLE patnotes
(
     patkey    SERIAL
    , notetype CHAR(4)
    , order_no INTEGER
);

CREATE UNIQUE INDEX patnotes_udx1 ON patnotes(patkey, notetype, order_no);

INSERT INTO patnotes VALUES (5000, 'GEN', 1);
INSERT INTO patnotes VALUES (5000, 'GEN', 2);

CREATE TABLE person
(
      personkey INTEGER
    , patid     CHAR(4)
);

INSERT INTO person VALUES (4999, '4999');
INSERT INTO person VALUES (5000, '5000');
INSERT INTO person VALUES (5001, '5001');

-- The insert query
INSERT INTO patnotes (patkey, notetype, order_no)
SELECT FIRST 1
    p.personkey
    , 'GEN' AS notetype
    , CASE
        WHEN n.order_no IS NULL THEN 1
        ELSE (n.order_no + 1)
      END AS order_no
FROM
    person p
LEFT JOIN
    patnotes n
    ON p.personkey = n.patkey
    AND n.notetype = 'GEN'
WHERE
    p.patid = '5000'
ORDER BY
    n.order_no DESC;

When we run the above INSERT we get:

239: Could not insert new row - duplicate value in a UNIQUE INDEX column (Unique Index:patnotes_udx1).
100: ISAM error:  duplicate value for a record with unique key.

If we remove the UNIQUE constraint and run the INSERT multiple times we keep getting the same value for column order_no .

-- remove the UNIQUE
DROP INDEX patnotes_udx1;

-- Run the insert multiple times and we get
SELECT * FROM patnotes;
patkey notetype    order_no

  5000 GEN                1
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2

It keeps inserting the same calculated order_no . Seems like a bug. You should open a defect with Informix support.

As a workaround you can use MAX to calculate order_no:

INSERT INTO patnotes (patkey, notetype, order_no)
SELECT
    p.personkey
    , 'GEN' AS notetype
    , (MAX(NVL(n.order_no,0)) + 1) AS order_no
FROM
    person p
LEFT JOIN
    patnotes n
    ON p.personkey = n.patkey
    AND n.notetype = 'GEN'
WHERE
    p.patid = '5000'
GROUP BY
    p.personkey, notetype
;

Please do note that this approach will fail miserably if you have multiple sessions trying to insert new values concurrently. You should use SERIAL or create a SEQUENCE to generate the order_no values.


MySQL security group view permission query I have a table of records that are only viewable by users with the correct group permission. The records may be viewable by multiple groups. Users may also be in multiple groups.

record

+----+-------------------+
| id |    record_data    |
+----+-------------------+
|  1 | Some sample data  |
|  2 | More data         |
|  3 | Some more data    |
+----+-------------------+

groups

+----+-------------+
| id | description |
+----+-------------+
|  1 | Group A     |
|  2 | Group B     |
|  3 | Group C     |
|  4 | Group D     |
+----+-------------+

user

+----+------+
| id | name |
+----+------+
|  1 | Levi |
|  2 | Bill |
|  3 | Sam  |
|  4 | Jen  |
|  5 | Amy  |
+----+------+

I have created lookup tables to match the record to the groups that have permission to view them. I have also created a lookup table that matched the users with the groups they are members of.

record_group

+----+-----------+----------+
| id | record_id | group_id |
+----+-----------+----------+
|  1 |         1 |        2 |
|  2 |         1 |        4 |
|  3 |         2 |        1 |
|  4 |         3 |        2 |
|  5 |         3 |        3 |
|  6 |         3 |        4 |
+----+-----------+----------+

So in the table above, record one is viewable by users in group 2 and 4. Record 2 is viewable by users in group 1, and record 3 is viewable by users in group 2,3 and 4.

user_group

+----+---------+----------+
| id | user_id | group_id |
+----+---------+----------+
|  1 |       1 |        1 |
|  2 |       1 |        2 |
|  3 |       1 |        4 |
|  4 |       2 |        1 |
|  5 |       2 |        4 |
+----+---------+----------+

Above, User 1 is in group 1,2 and 4. User 2 is in group 1 and 4.

I am trying to create a query that will show all the records that a particular user has the correct group permissions to view. Example. I want to list all the records that user ID 2 can view and ignore the others.

The query for user 2 should return record 2,3 and 6

I need to do this in a single query as it will be plugged into an existing framework.

My understanding is that I can do this with the MySQL IN function but I cant seem to get it to work with two sets, example (Select id from member_groups) IN ()

So my Question, how do I build a MySQL query that selects all the records user one has permissions to view?

I'm sorry I cant provide more information or example query, I'm not sure how to start.

SQL Fiddle http://sqlfiddle.com/#!9/e40ee6/1

SELECT DISTINCT record.id AS id, details from record
LEFT JOIN record_group ON record.id = record_group.record_id
WHERE record_group.group_id IN (SELECT group_id from user_group WHERE user_id = 2)

http://sqlfiddle.com/#!9/e40ee6/12


How long will an already started online Backup with RMAN take? I need to query know how long an online backup which is already started with RMAN will still take.

Any recommendation for a query?

RMAN jobs show up in V$SESSION_LONGOPS with OPNAME like 'RMAN%', there you can query the value of TIME REMAINING. Beware, this is just an estimate.


Shutdown one node in RAC I want to shutdown one node in RAC. I did the following steps:

Shutdown database instance

export ORACLE_SID=mydb1
sqlplus / as sysdba
shutdown immediate;
shutdown ASM instance

. oraenv
+ASM1
sqlplus / as sysasm
shutdown immediate;

But shows the following error: ORA-15097: cannot SHUTDOWN ASM instance with connected client

Please, tell me what steps should I do to shutdown all services and instances on the server which is the part of the RAC?

DISCLAIMER : Not an Oracle DBA

Use srvctl, the grid control program that allows you to manipulate parts of the RAC Cluster

Usage: srvctl <command> <object> [<options>]
    commands: enable|disable|start|stop|relocate|status|add|remove|modify|getenv|setenv|unsetenv|config
    objects: database|instance|service|nodeapps|vip|asm|diskgroup|listener|srvpool|server|scan|scan_listener|oc4j|home|filesystem|gns
For detailed help on each command and object and its options use:
  srvctl <command> -h or
  srvctl <command> <object> -h

Examples

srvctl stop database -d mydb
srvctl stop instance -i mydb1

Please get an expert to perform these, especially since you want to do something with ASM. If I recall, ASM is a proprietary Oracle file system. Using srvctl would be better than trying to handle it yourself.

Here is more help on srvctl

$ srvctl stop database -h

Stops the database.

Usage: srvctl stop database -d <db_unique_name> [-o <stop_options>] [-f]
    -d <db_unique_name>      Unique name for the database
    -o <stop_options>        Options to shutdown command (e.g. normal, transactional, immediate, or abort)
    -f                       Force stop
    -h                       Print usage
$ srvctl stop instance -h

Stops the database instance.

Usage: srvctl stop instance -d <db_unique_name> {-n <node_name> | -i <inst_name_list>}  [-o <stop_options>] [-f]
    -d <db_unique_name>      Unique name for the database
    -n <node_name>           Node name
    -i "<inst,...>"          Comma separated instance names
    -o <stop_options>        Options to shutdown command (e.g. normal, transactional, immediate, or abort)
    -f                       Force stop
    -h                       Print usage
$ srvctl stop asm -h

Stops ASM instance.

Usage: srvctl stop asm [-n <node_name>] [-o <stop_options>] [-f]
    -n <node_name>           Node name
    -o <stop_options>        Options to shutdown command (e.g. normal, transactional, immediate, or abort)
    -f                       Force stop
    -h                       Print usage
$


How to store data having a fixed number of columns and a variable number of rows I have posted this question in the stack overflow site and a more experienced user than me suggested I post it here.

I am quite new at collecting data and am collecting a new dataset using R from online media articles together with each article's disqus comments and other related information. My "record" will have 12 columns of information (see image below). My confusion lies with the fact that these media articles might have anything from 0 to 500 comments (maybe more). I would like to save this data as efficiently as possible and for now the only idea to my inexperienced knowledge is to save each in its own .csv file although I'm not sure about this.

And that is where I'm stumped. My first thought was to divide each record in two: the first part would contain six columns and will always have one row and the second part will contain another six columns with a varying number of rows. A sort of ID will link the two together. This would possibly be the "dirty" solution.

What I would like to kindly ask the Community is about newer technologies (for examples: Mongodb, NoSQL. Or ust SQL?) to store this sort of data. Eventually, this data will not be online as it will be queried or processed using some or all of its elements of data.

The image below shows an example of part of a data record starting out with 1 row but rows increase according to the number of comments in an article. This particular record would have 23 rows in the last 6 columns if all data is entered.

It would greatly help me to have an idea on a way forward rather than having to test or research a technology only to find that it is not what I need. Any comments or suggestions are most welcome.

Based on your image, something like the following will be fine (using a RDBMS solution):

create table articles
(
  article_id     integer PRIMARY KEY,
  title          varchar(256) NOT NULL,
  url            varchar(256) NOT NULL,
  summary        varchar(256) NOT NULL,
  body           varchar(4000) NOT NULL
);

create table article_comments
(
  comment_id    integer PRIMARY KEY, -- primary key, unique, does not map to the "comment number" in your question
  article_id    integer NOT NULL, -- references articles.article_id
  screen_name   varchar(100),
  disqus_url    varchar(256),
  comment_dt    datetime, -- both the date & time from your spreadsheet
  comment_text  varchar(4000),
  comment_url   varchar(256),
  comment_post_id varchar(25)
);

Basically, there's a one-to-many relationship between articles and comments.

You don't say what RDBMS you'd want to use, so the data types will need changing accordingly (bigger VARCHARs/TEXT columns etc will be needed for long articles/comments etc). Constraint DDL is also largely RDBMS-dependent, so you'll have to look the syntax up for yourself once you've made a decision. I can recommend Postgres.


How can i enforce data integrity in this setup? I have these tables:

   |-------------tblSchool -------------|
   |               + ID                 |
   |               + Name               |
   |                                    |
   |                                    |
tblSchoolYear                        tblTerm
 | + SchoolID                        | + SchoolID
 | + Name                            | + Name
 |                                   |
 |                                   |
 |---------- tblSchoolTerm ----------|
               + SchoolYearID
               + TermID
               + StartDate
               + EndDate

As you can see, I have 2 tables tblSchoolYear and tblTerm that both have SchoolIDs. These 2 tables then makesup a SchoolTerm. My problem is I want the to prevent the combination of tblSchoolYear and tblTerm in tblSchoolTerm withe different SchoolIDs.

For example, if I have 2 schools with IDs 100 and 101, I don't want to have a SchoolYear and Term in SchoolTerm in which SchoolYear has a SchoolID of 100 and the Term a SchoolID of 101.

What I can think of is using a trigger to enforce this, but I wanted to ask if maybe there is a better design to solve this problem without resorting to triggers.

Using SQL Server 2012 by the way.

ADDITIONAL INFO:

For those asking why I can't just have 2 tables, tblSchool and tblSchoolTerm, I have other columns in tblSchoolYear and tblTerm that contains data that I don't wnat duplicated. Sorry, I'll add some sample columns more to make it clearer.

Have two columns in tblSchoolTerm one called TermSchoolID and the other YearSchoolID. Add a check constraint that enforces they are equal.

From the information given, however, it would seem to me that your model is incorrect. I would have thought a Term would relate to a Year, then School would have many-to-many with Term. You know your business rules best, though.


Looking for a book on optimizing tables and queries for handling time sequence data I once had an electronic copy of what I understood to be a classic work on optimizing tables and queries for handling timestamped data.  It was old-ish when I found it 6-8 years ago.  I was sure I also printed and bound a copy but that disappeared in the move when I left my last job.

It was DBMS-agnostic and may be obsolete if standard SQL features and/or various vendor extensions have filled the hole it addressed.  However, now that I want it, I can't find it.

As an example of a question it helped answer was: if you record values only on change so your table says it was 75 degrees at noon and went up to 76 at 2pm, what was the temperature at 1pm?

I am not looking for answer to that question but I've Googled and Googled trying to find a new electronic copy and haven't come close.  Does this sound familiar to anyone here?  I'd appreciate any pointers.

Adding "out of print" to my search terms led to https://www.postgresql.org/message-id/02D8929E-1F0D-11D9-9830-000A95A0A9C8%40mit.edu which references https://www2.cs.arizona.edu/people/rts/tdbbook.pdf which is what I was looking for.


Can't query one view from another? Okay, I'm moving a database from SQL Server (which I already miss) to MySql 5.6. I'm having a problem creating a list of valid dates. In SQL Server I just used CTEs to build up numbers, then months, then years, then days of the month, then cross join'd everything together to create a valid list. I'd rather do that then create a giant table listing all valid dates from the start to the end of time. Converting it to a set of views, however, doesn't seem to be working:

create view `Numbers` as
    select 0 N union all select 1 union all select 2 union all
    select 3 union all select 4 union all select 5 union all
    select 6 union all select 7 union all select 8 union all select 9;
create view `Months` as
    select n.N+1 MonthNum union all select 11 union all select 12
    from `Numbers` n;

Error from MySql: Error Code: 1109. Unknown table 'n' in field list.

You have to rewrite the second View as follows:

create view `Months` as
    select N+1 MonthNum from `Numbers`
    union all select 11
    union all select 12
;

Here is proof

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.10-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> create view `Numbers` as
    ->     select 0 N union all select 1 union all select 2 union all
    ->     select 3 union all select 4 union all select 5 union all
    ->     select 6 union all select 7 union all select 8 union all select 9;
Query OK, 0 rows affected (0.05 sec)

mysql> create view `Months` as
    ->     select N+1 MonthNum from `Numbers`
    ->     union all select 11
    ->     union all select 12
    -> ;
Query OK, 0 rows affected (0.03 sec)

mysql>

Behold the output of the Query

mysql> select * from Months;
+----------+
| MonthNum |
+----------+
|        1 |
|        2 |
|        3 |
|        4 |
|        5 |
|        6 |
|        7 |
|        8 |
|        9 |
|       10 |
|       11 |
|       12 |
+----------+
12 rows in set (0.00 sec)

mysql>

Give it a Try !!!


SQL Server - MERGE join condition with NULLable Columns I am trying to merge two of my tables (identical definition).

MERGE INTO xx.dbo.acc_s AS Target
 USING yy.dbo.acc_s AS Source
 ON (Target.acc_id= Source.acc_id AND Target.s_id= Source.s_id AND a_code= Source.a_code)
WHEN NOT matched BY Target THEN
INSERT (acc_id,s_id,a_code)
VALUES (Source.acc_id,Source.s_id,Source.a_code);

What I expected is that "If Target doesn't have the row, INSERT it" - Nothing Else.

I got a "Violation of PRIMARY KEY" error on one of the rows. If it's a matching row BY TARGET, it shouldn't have attempted to insert it in the first place.

What I did after that was:

MERGE INTO xx.dbo.acc_s AS Target
 USING yy.dbo.acc_s AS Source
 ON (Target.acc_id= Source.acc_id AND Target.s_id= Source.s_id AND ISNULL(a_code, '')= ISNULL(Source.a_code, '')
WHEN NOT matched BY Target THEN
INSERT (acc_id,s_id,a_code)
VALUES (Source.acc_id,Source.s_id,Source.a_code);

And it picked up correctly. From this, I am thinking that when I am merging, if I have two NULL columns joining - SQL Server cannot resolve this correctly. Have I encountered an expected behaviour here?

I know that I should probably rather use

WHEN matched THEN
UPDATE SET a_code = Source.a_code

But in that case I might have to change quite a lot of things as I am trying to find a general way to update other tables by creating a template query. However, I understand that it might not be practical.

Mark Sinkinson nailed it in the comments:

  This is standard behaviour. NULL does not equal NULL

What you can do is adjust your predicate from this:

WHERE Destination.ColumnA = Source.ColumnA
    AND ...  -- other column comparisons

to the following:

WHERE ((Destination.ColumnA = Source.ColumnA)
       OR (Destination.ColumnA IS NULL AND Source.ColumnA IS NULL))
   AND ...  -- other column comparisons

This will provide a workaround that effectively equates a NULL in one column to a NULL in another.

So to work that into your Merge, it would be as follows:

MERGE INTO xx.dbo.acc_s AS Target
 USING yy.dbo.acc_s AS Source
 ON (Target.acc_id= Source.acc_id
     AND Target.s_id= Source.s_id
     AND (a_code= Source.a_code
          OR (a_code IS NULL AND Source.a_code IS NULL)
         )
    )
WHEN NOT matched BY Target THEN
INSERT (acc_id,s_id,a_code)
VALUES (Source.acc_id,Source.s_id,Source.a_code);


Benefits of Barracuda and Compression I've been reading about MySQL's file formats Antelope and Barracuda a while ago, and I wonder if I could benefit with having Barracuda and Compression.

My server is currently using Antelope, as it is the default of MySQL.
I've had many times issues with memory due to the large database I have. My database is increasing every day.

It seems Compression is giving benefits to a few people, like:
http://www.mysqlperformanceblog.com/2008/04/23/real-life-use-case-for-barracuda-innodb-file-format/

I understand memory and disk space can be lower, but I'm not sure if I understand this (quoted from the article):
"~5% CPU load according to top (from 80-100% mostly waiting for I/O)
0.01 sec average lookup time by primary key (from 1-20 sec before the conversion)"

I thought these two things would NOT improve, because if the data is compressed, the server has to uncompress in order to get the original data again, so doesn't that make sense that CPU usage would increase?

Does that benefit you in read/write intensive applications? Would you recommend me to change to Barracuda and Compression?

Are you aware of any issues of Barracuda?
It seems the answer of the following question points a few issues, but since it's from 2011, I'd say they are fixed by now: https://serverfault.com/questions/258022/mysql-innodb-how-to-switch-to-barracuda-format

Regarding "Dynamic", the non-compressed Barracuda-only format, very little has changed from compact, mainly on how blobs (and any very dynamic fields) are stored. I have never had any issues with compact vs. dynamic, so I can safely recommend Barracuda's dynamic. Remember that Barracuda also supports old redundant and compact row formats.

The article you are mentioning is probably too old (5.1) and, as Peter Z., Percona's CEO, mentions on the comments it may be a bit misleading. That doesn't mean that compression can't be a huge gain depending on the workloads. However, I would recommend you to try it on versions >= 5.6, as both Facebook and Oracle have done lots of improvements about it.

As more recent reference materials, I would recommend you:

Getting InnoDB Compression Ready for Facebook Scale
InnoDB Compression Present and Future
Benchmarking InnoDB page compression performance

In particular, I like Facebook materials as they are third party (no need for an agenda) and they have one of the largest MySQL deployments in the world. As you can see they have had very successful setups combining SSD technology with compression.

Will it benefit you? That will depend on your workload, working set and setup (IOPS, memory). Depending if you are IO bound, CPU bound or memory bound, compression can affect negatively in some cases, by adding extra CPU, memory requirements (both compressed and uncompressed pages are stored on the InnoDB buffer pool) or generating too many compression failures, augmenting the latency. It also depends on the type of data: compression can help a lot with large text blobs, but it may be useless with already-compressed data.

In my experience, in practice, there are people for which compression was a the holy grail of performance and are very happy with it, but on other cases, we had to revert to uncompressed data as no gain was obtained. While a very heavy writing workload may seem like a bad environment for compression, if in your particular case you are not cpu-bound and memory-bound, but iops-bound it may be none-less helpful.

In general, it is very difficult to predict results, usually you should setup a test environment for benchmarking and then discover why you get better or worse results (and that way you can play with different block sizes, etc.). Barracuda is completely safe. Compression may or not be for you. And you can always experiment with other compression methods like client-side compression of blobs (for example, if you end up CPU-bound) or other 3rd party engines like RocksDB and TokuDB, in which compression is a big priority, as it is focused in performance for larger datasets than InnoDB can handle.

In a nutshell: Main reasons to use Barracuda are BLOB handling, innodb_large_prefix compatibility (large indexes) and compression. Dynamic, on MySQL 8.0 is now the default file format.


Why is user: system doing an hourly backup of my SQL databases? I've got a pretty standard maintenance plan set up for my SQL Server databases; yet in addition to that plan, all of my databases, user and system, are backed up every hour by user: System... where is that getting initiated from and how can I turn it off?  There is also an associated MSMQ backup of components: registry, sysq, https, etc during the same time frame.

Your System Admin probably has a program such as BackupExec which backs up everything organisation-wide.  Check with them.

Typically, you'll see the backup device listed as a unique identifier, if some other system is managing backups.

I occasionally use this query to check backup history details:

DECLARE @DBName SYSNAME;
SET @DBName = DB_NAME(); -- modify these as you desire.
SET @DBName = NULL; -- comment this line if you want to limit the history
--SET @DBName = '<some database name>';
;WITH src AS
(
SELECT DatabaseName = bs.database_name
    , BackupStartDate = bs.backup_start_date
    , CompressedBackupSizeMB = bs.compressed_backup_size / 1048576
    , ExpirationDate = bs.expiration_date
    , BackupSetName = bs.name
    , RecoveryModel = bs.recovery_model
    , ServerName = bs.server_name
    , BackupType = CASE bs.type
          WHEN 'D' THEN 'Database'
          WHEN 'L' THEN 'Log'
          ELSE '[unknown]' END
    , LogicalDeviceName = bmf.logical_device_name
    , PhysicalDeviceName = bmf.physical_device_name
    , rn = ROW_NUMBER() OVER (PARTITION BY bs.database_name
            ORDER BY bs.backup_start_date DESC)
FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf
        ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE (bs.database_name = @DBName
    OR @DBName IS NULL)
    AND bs.backup_finish_date >= DATEADD(DAY, -7, GETDATE())
)
SELECT src.DatabaseName
    , src.BackupStartDate
    , CompressedBackupSizeMB = CONVERT(INT, src.CompressedBackupSizeMB)
    , src.ExpirationDate
    , src.BackupSetName
    , src.RecoveryModel
    , src.ServerName
    , src.BackupType
    , src.LogicalDeviceName
    , src.PhysicalDeviceName
FROM src
WHERE src.rn = 1
     AND src.DatabaseName NOT IN (
        'master'
        , 'model'
        , 'msdb'
        , 'tempdb'
     );

That should return a single row for each database backed up over the last 7 days.  You can limit this to a single database by modifying the @DBName variable.


Poor query performance in MySQL I have a 10 million record table in a MySQL 5.5 InnoDB. It's running in a 16GB RAM server with good CPU and fast HD. When I run the following query

SELECT DISTINCT knowledge_id,
                tag_order,
                tag_weight,
                total_title_direct_words,
                total_title_parenthesis_words,
                tag_level
FROM   knowledge_tags
WHERE  ( tag_text = 'washington' )
       AND ( tag_level < 10 );

It will take around 9 seconds (first run) locally (localhost). The result count is 640. There is a Btree index for the fields 'tag_text, tag_level'. This query time isn't acceptable for what i want. What can I do?

Here is the EXPLAIN result:

1   SIMPLE  knowledge_tags  range   tags_by_word_text   tags_by_word_text   308 [null]  344 Using index condition; Using temporary

And the CREATE TABLE statement:

CREATE TABLE `knowledge_tags` (
  `tag_id` int(11) NOT NULL AUTO_INCREMENT,
  `knowledge_id` int(11) DEFAULT NULL,
  `dictionary_word_id` int(11) DEFAULT NULL,
  `tag_text` varchar(100) DEFAULT NULL,
  `tag_order` int(11) DEFAULT NULL,
  `tag_level` int(11) DEFAULT NULL,
  `knowledge_family_id` int(11) DEFAULT NULL,
  `tag_weight` double DEFAULT '1',
  `total_title_direct_words` int(11) DEFAULT NULL,
  `total_title_parenthesis_words` int(11) DEFAULT NULL,
  PRIMARY KEY (`tag_id`),
  KEY `tags_by_knowledge_id` (`knowledge_id`),
  KEY `tags_by_word_text` (`tag_text`,`tag_level`) USING BTREE,
  KEY `tags_by_family_dictionary` (`knowledge_family_id`,`dictionary_word_id`,`tag_level`) USING BTREE,
  KEY `tags_by_family_word_text` (`knowledge_family_id`,`tag_text`,`tag_level`) USING BTREE,
  KEY `tags_by_dictionary` (`dictionary_word_id`,`tag_level`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3316 DEFAULT CHARSET=utf8;

I suggest you add an index on all the columns used in the query, with this order (actually the order matters for the first 2 columns only, the rest 5 can be in any order within the index):

 (tag_text,
  tag_level,
  knowledge_id, tag_order, tag_weight,
  total_title_direct_words,
  total_title_parenthesis_words)

The query should then use this index for both the WHERE and the DISTINCT (or group by) part. You probably need to change the query slightly, removing the DISTINCT and using GROUP BY (this acts like a hint to the MySQL optimizer.)

SELECT   knowledge_id,                      -- remove DISTINCT
         tag_order,
         tag_weight,
         total_title_direct_words,
         total_title_parenthesis_words,
         tag_level
FROM   knowledge_tags
WHERE  ( tag_text = 'washington' )
  AND  ( tag_level < 10 )
GROUP BY   tag_text,                        -- use the same order
           tag_level,                       -- as in
           knowledge_id,                    -- the index,
           tag_order,                       -- all
           tag_weight,                      -- the
           total_title_direct_words,        -- way
           total_title_parenthesis_words ;  -- down

The EXPLAIN should then show:

  Using where; Using index for group-by

If the result are good enough for your expectations, you can then drop the tags_by_word_text index.


Algebra of joins, is there ever a need to repeat a table in a series of joins? First, I'm just asking about inner joins. And, I'm thinking self joins or reflexive joins through a join table are special cases which depend on how the data is structured (e.g. city - state - country, child - parent - grandparent).

But for the rest of joining tables there's no need to repeat a table in any given "chain"?

(What I mean by "chain":

               __companies -- locations AS L1 -- N Joined Tables -- locations AS L2
SELECT people |
               __schools -- locations AS L3

So, as best I can figure there is a necessary duplication of L1 and L3 since they're unconnected searches. But any conditions on a search of L2 could be placed on a search of L1.

Is this universally true? Is this generally true?

Dependent on context (chain) addressing to the same table can mean different sets of rows. So you have to point them by two different aliases to avoid ambiguity.

Let's imagine we want to build the list of customers and their suppliers both with cities where they reside:

SELECT w.name AS customer,
       s.city AS c_city,
       z.name AS supplier,
       r.city AS s_city
  FROM customers      AS w
  LEFT JOIN suppliers AS z ON z.id = w.supplier_id
  LEFT JOIN cities    AS s ON s.id = w.city_id
  LEFT JOIN cities    AS r ON r.id = z.city_id
ORDER BY w.name ASC;

Here same table is joined twice because each join performed on it's own condition and produce different resulting city names - one for customer and one for supplier. And there is no way to achieve the desired result without joining the same table twice.

P.S.

Let's imagine we have the list of cities visited by tourist:

-------------------------------------
| ID | DATE | CITY_ID | PREVIOUS_ID |
-------------------------------------

Here PREVIOUS_ID is the reference to the row in the same table and it mean " the city  tourist come from". And now we want to build the route containing last four visited cities to the some point:

SELECT *
   FROM visited      AS w
   JOIN cities       AS z ON z.id = w.city_id
   LEFT JOIN visited AS r ON r.id = w.previous_id
   LEFT JOIN cities  AS s ON s.id = r.city_id
   LEFT JOIN visited AS q ON q.id = r.previous_id
   LEFT JOIN cities  AS d ON d.id = q.city_id
   LEFT JOIN visited AS x ON x.id = q.previous_id
   LEFT JOIN cities  AS f ON f.id = x.city_id
 WHERE w.id = 12345; -- last position

As you can see we can easely join again and again the same tables.


How to handle database of multiples languages and countries Imagine that I have a database of books. I would like to store multiples informations for multiples languages and countries.
Imagine that I would like to support 10 countries and 10 languages.

I want to store at least the name and description for each language.
The price, the currency and availability, the release date for each country.
And other rows.

Here are my options to store this on database and have the possibility with one request to get the infos for all countries and languages at a time :

Solution 1
- have one column per language and country, but in the end I have 100 columns

Solution 2
- have a table "price", a table "currency", "availability" etc, which store the information for one country in one row.
The same for "name", "description" which store the information for one language in one row

solution 3
- have 2 tables, one will contain every data for one country (so one row contain the price, the currency, the availability etc for one country and book). And the other the same but for one language (and contain the name and description for one language per row).

solution 1 makes it easy to retrieve informations for all countries and languages at a time, but this makes a very large table (i have millions of rows) and I believe it is slower to query this table due to number of columns

solution 2, the problem is to retrieve infos for all countries and languages will need one join for each lang and country, so I end up having a lot of join and be stuck with the maximum join limit (61 ?)

solution 3, will lower the number of joins (but still one join per lang and one per country).

What is the best ? How do people do when they want to show informations like that ?

thanks

EDIT:
What do you think of this solution, which is kind of solution 2, but with only one join for each table, instead of one join per language and per country :
http://sqlfiddle.com/#!9/7ae955/1

Normally I use two tables, main table has default language, just in case you forget to add corresponding values into translations tables.

create table books (
    book_id int,
    book_name varchar(200),
    description varchar(500)
)

create table books_language (
    book_id int,
    language_id vachar(10),
    book_name varchar(200),
    description varchar(500)
)

This returns all records including default language.

select book_id,
       isnull(books_language.laguage_id, 'default')
       isnull(books_language.name, books.name) as name,
       isnull(books_language.description, books.description) as description
from books
     left join books_language
     on books.book_id = books_language.book_id


How to use a MySQL error handler in a stored procedure with prepare/execute I have a procedure defined like this:

drop procedure if exists drop_tables_like;
delimiter $$
create procedure drop_tables_like(myint int, pattern varchar(255), db      varchar(255) )
begin
DECLARE CONTINUE HANDLER FOR 1064
select now() - interval  myint day into @owa;
set group_concat_max_len = 65535;
select concat('drop table ', group_concat(table_name)) into @str_sql
from information_schema.tables
where
    table_schema=db and table_name like pattern and
    create_time <= @owa;

prepare stmt from @str_sql;
execute stmt;
drop prepare stmt;
end$$

The idea is to drop tables matching a certain pattern that have a create_time <= a specified date.

When executed, I get an error:

mysql> call drop_tables_like(7, 'monkey%', 'daweb2012_civicrm');
ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to     EXECUTE
mysql>

How can I declare a handler for an exception in a procedure that is using a prepared statement?

Here is a sample of updated procedure with 1064 and 1065 errors caught

refer to https://gist.github.com/onare/c8d3042ba4d18408aeee for a longer list of errors

drop procedure if exists drop_tables_like;
delimiter $$
create procedure drop_tables_like(myint int, pattern varchar(255), db      varchar(255) )
begin
DECLARE E INT DEFAULT 0;
DECLARE M TEXT DEFAULT NULL;

DECLARE CONTINUE HANDLER FOR 1064 SET E='1064', M="%s near '%s' at line %d";
DECLARE CONTINUE HANDLER FOR 1065 SET E='1065', M="Query was empty";

select now() - interval  myint day into @owa;
set group_concat_max_len = 65535;
select concat('drop table ', group_concat(table_name)) into @str_sql
from information_schema.tables
where
table_schema=db and table_name like pattern and
create_time <= @owa;

prepare stmt from @str_sql;
IF E> 0 THEN
        SELECT CONCAT("MySQL Error #", E,  ": ", M);;
        set E=0;
    else
        execute stmt;
        drop prepare stmt;
    end if;
end$$


Storing massive amounts of data from a sensor array I have been tasked to implement a solution (app and db) to store the data samples from a huge sensor array. The array currently consists of about 20,000 sensors, but that is soon going to grow, up to 100,000 sensors. Each sensor sends a data sample every 10 seconds and each sample is 28 bytes in size.

Doing the sums thus leads to:

8640 samples per sensor per day
242kB of data per sensor per day
864 million samples per day

Now I have been wondering what the best way would be to store/retrieve the data ? I "joined" this project after the software has already been specified, so it needs to be implemented on a Windows Platform using SQL Server.

The current solution in my head is to create a DB with two tables to store the data samples. The first serves as a sort of an index into the second that stores the collated samples in a binary field on a per day per sensor basis :

Table 1:

  RecordID - BigInt - Identity
  SensorID - BigInt - Primary Key
  Date - DateTime - Primary Key (yyyy-mm-dd)

Table 2:

  RecordID - BigInt - Primary Key (from an insert into Table 1)
  Data - Binary

Basically I will write the samples from all sensors into temporary files (1 per sensor). At the end of each day I will then create an entry in Table 1, use the generated RecordID and dump the file into the Data field in Table 2.

This way I end up with only 100,000 entries into the table per day, instead of 864 million entries. The data should be available on the LAN or High Speed WAN, so retrieval of the sensor data on a whole day basis would be acceptable.

Although all data has to be stored, most of it will probably never be read. So the amount of reads on the table(s) will not be hugely more than the writes.

I know that I could implement something using the file system by just storing the path to the data files, but I read that SQL Server outperforms NTFS while your binary fields are less thank 256kB. (A gray area exists between 256kB and 1MB, while NTFS far outperforms SQL Server for binary sizes > 1 MB).

I am also slightly wary of storing data from 100,000 sensors into their own files without causes problems in the file system by either having huge amounts of files in a folder, or by having a complex tree structure with a few files in each folder, while not even taking file fragmentation into account.

Can anyone offer me some practical advice/comments on the above?
Are there obvious pitfalls that I am going to fall into?
The sample data does compress quite nicely. A 242 kB file compresses to about 85kB. Can I however implement some type of compression on database level so that the sample data (column) is compressed automatically?
Is SQL Server an obviously wrong choice for this project?
Is my design of the two tables wise, or could I just as well combine it into a single table that will still be as "performant" as the two tables?

Yes, there is a pretty big pitfall you're going to run into fairly quickly, and that is with the size and maintenance of the tables.  You are somewhat on the right track by saying that you want to put your data into a temporary table daily, and then move it into your permanent table, but you'll soon run into trouble with this scheme.

For example, let's say you want to "roll off" the oldest month's worth of data after two years.  In your design, you would have to issue a DELETE statement against your big, big table.  This will likely be somewhat slow, depending on the number of indexes you have.  Also, it will cause index fragmentation, and the only way to fix that would be to rebuild or reorganize the indexes on this very large table which would also cause performance problems.    There are a whole host of other issues with a big single table type design as well.  For example, with a big, single table, you can't do FILEGROUP based backups, which means that if you want to have a full backup of your database, it's gonna be BIG, and it's gonna take a LONG time to complete.

What's the solution?  Table partitioning.  Read about this in depth, in as many places as you can.  Basically, partitioning allows you to split up your data onto "tables within tables" -- each partition shares the same schema, and is accessed through the table object, but can be indexed and maintained differently.  Partitions are basically tables, cut up by some useful key.  In your case it will likely be date.  They can be dropped just like (and just as fast as) tables, which means that if you partition your big data tables by date, you can simply drop old partitions instantly, with no adverse effect to the indexes on any of the other partitions.  You can put partitions on different filegroups, which means that older partitions can be rolled off, or rolled on to cheaper commodity storage if it's not commonly used.  Last but not least, in SQL 2012 you'll be able to create COLUMNSTORE type indexes on your older, read-only partitions, while having a different, more insert-oriented indexing scheme on the active partition where you're inserting all your sensor data.

Hope this helps.  You have a good amount of research to do regarding partitioning and partitioning schemes, but hopefully now you know the direction you need to be looking.

P.S.:
Oh, and I forgot your bulleted list of questions...
Answer 1, 2, and 5.  See above.  Answer 3: In SQL Server, you can compress on a partition by partition basis, so compress your older partitions aggressively using PAGE compression.  But  I believe your out-of-row large data types will not be compressed if you do this -- again, you may want to alleviate this problem by normalizing your sensor values.  Answer 4: Absolutely not, but if all you want to do is store static data by day and never search on it any other way, compressed flat files may be a much easier way to go.

P.P.S:
Oh, and another thing.  You don't need your two-table solution to make this all work.  Large binary sensor data should be of type VARBINARY(MAX) because its values can be stored "out of row" but still be a column in a single table (see the sp_tableoption documentation).  You may want to consider normalizing some of your sensor data out of the binary data you have in the table, though, because your database won't be good for much beyond retrieving chunks of sensor data by time if you don't.


Database design for tracked inventory that could get “transformed” I need to track some serialized items in the inventory that can be either dismantled into multiple items or combined together into a single item. I have thought of a few options:

Flow tracking - Use two tables for inv_group_out -> inv_group_in with a joining table. Entry into either table can be a single item or multiple items each referencing the original inventory_id. New inventory item(s) get created for the re-entry of the inventory.
Movement tracking - Mark all changes in a single modifications table. Can't really think about how to reliably carry this out though.
Flow tracking alternative - Use the same table for original purchase/sales using a separate column to indicate that these are just inventory movements

Would love to hear some opinions on which would be preferred.

I'm guessing that you want two things:  A full history of each item, and a "current" location of each item.  Let's make that two database tables.

When items are "combined", let's give the combination a separate serial number.

CREATE TABLE History (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    item_id ...,   -- serial number of item
    combined_id ...,  -- pseudo-serialnumber of combination (or 0 if item by self)
    dt DATE TIME,   -- when the change occurred
    PRIMARY KEY(id),
    INDEX(item_id, dt)  -- handy for listing history of an item
) ENGINE=InnoDB;

CREATE TABLE Current (
    item_id ...,   -- serial number of item
    combined_id ...,  -- what it is currently in
    dt DATE TIME,   -- when it was constructed
    PRIMARY KEY(item_id),
    INDEX(combined_id)   -- to identify current components
) ENGINE=InnoDB;

CREATE TABLE Combos (
    combined_id INT UNSIGNED AUTO_INCREMENT NOT NULL, -- for generating ids
    etc -- description or whatever
    PRIMARY KEY(combined_id)
) ENGINE=InnoDB;

When you "combine" a bunch of items:

INSERT into Combos to get a new combined_id.
Change Current as needed
INSERT multiple rows into History.


Can I shed permissions during a session? I am creating a reporting server which will run certain reports.

The reports are created by me and saved as SQL files.

The reporting application has read/write access, for good reasons.

Is there a way I can deprivilege a SQL session so that when executing the SQL it will use less privileges for execution than the application has available?

If you use Roles to control access to your tables, you can set a "read-only" role after opening the database connection:

set role read_only_role;

Where this role has select access on the target tables.

https://dev.mysql.com/doc/refman/8.0/en/set-role.html


Designing a simple library database I am currently developing a simple book library system in ms access 2007 and need some help with the database design. I want to register books, borrowers and loans, so I assume that my database could consist of these three entities.

Borrower
Loan
Book

The relationships between the tables are as so:

Borrower has a 1 to many relationship with Loan.

Loan has a 1 to many relationship with Book.

The problem with this approach is that you can not create a row in the Book table without assigning a foreign key value; thus referencing the foreign key loanId of Book table with the related primary key in the Loan table. It breaks the referential integrity and it's not possible to create a book without relating it to a loaner.

So I think I need a join table between Book and Loan. This table should consist of the 2 primary keys from Book and Loan (a composite key). So there is a 1 to many relationship between Book and BookLoan and a 1 to many between Loan and BookLoan.

Is my way of thinking correct ? I can't figure out why I need the join/junction table between loan and book. Is there a more simple solution to my problem?

Thanks in advance :)

EDIT: I have added a E/R-diagram which has been corrected in respect to the answer, I have received.

I believe you are assigning the Foreign key on Book to Loan. It should be just the opposite and be on Loan to Book. The Book doesn't need a comparative loan but every loan needs a comparative book.

Both of your foreign keys should exist on Loan.


Exadata: how to backup system before upgrade rpm packages? My PCI scanned found some vulnerabilities on my Exadata. I must upgrade glibc package.

Before that I want to backup the system not to ruin something. By which tool can I backup OS?

I used acronis, clonezilla but maybe I have old version , because they are not able to backup the system.

Please, give me any advice.

Answer is the following:
Create LVM Snapshot-Based Backup for your OS.
Refer below link in steps to perform the same.
https://docs.oracle.com/cd/E50790_01/doc/doc.121/e51951/db_server.htm#DBMMN22020
2 Maintaining Database Servers of Oracle Exadata Database Machine
Section:
Creating a Snapshot-Based Backup of Oracle Linux Database Server


Way to run SQLCMD without storing password in text format Is there a way to run sqlcmd from a batch file where I don't want to store the password in plain text format. I have done some research and found the system variables method(store password in system variables) but the requirement is not to store a password in text format anywhere in the system.

The user that is logging into the system should not be able to see the password for sql server and still run the batch file containing sqlcmd and restore command

Any help, pointers are appreciated.

If you are using SQL Logins, then basically, no.  You'll want to have sqlcmd use the person's domain login to connect to the SQL Instance (the -E parameter instead of -U and -P).  You can use a login trigger to ensure that they are only able to connect via sqlcmd if you want to prevent them from logging in via SQL Server management studio (it isn't a perfect fix, but it'll stop 99% of people).


sp_recompile does not recompile inline TVF. What alternatives are there other than using an ALTER statement? In the following, I would expect the first SELECT of the new column [Bit01] to be successful however it is not. Is there any way of avoiding the use of the ALTER statement to get it to work?

I have not been anything to find anything that confirms or denies my findings on the relevant MSDN page: https://msdn.microsoft.com/en-us/library/ms181647(v=sql.110).aspx

-- Shell table
CREATE TABLE [tblRecords]
(
    Id INT NOT NULL PRIMARY KEY CLUSTERED
);
GO

-- Inline TVF
CREATE FUNCTION [ufnAllRecords]()
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM [tblRecords]
);
GO

-- Add a column
ALTER TABLE [tblRecords]
ADD [Bit01] BIT NULL;
GO

-- Recompile the function
EXECUTE sp_recompile N'ufnAllRecords';
GO

-- Attempt to select the new column
SELECT [Bit01]
FROM [ufnAllRecords]();
GO

-- Column does not exist and so it errors...
-- However it works after ALTER'ing it
ALTER FUNCTION [ufnAllRecords]()
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM [tblRecords]
);
GO

-- Now works
SELECT [Bit01]
FROM [ufnAllRecords]();
GO

-- Cleanup
DROP FUNCTION [ufnAllRecords];
GO
DROP TABLE [tblRecords];
GO

Using SELECT * in production code is evil, for a number of reasons.

If you absolutely must do this, the procedure you're looking for is sp_refreshsqlmodule, which works much like the built in sp_refreshview, rather than sp_recompile.


How to select specific rows if a column exists or all rows if a column doesn't I'm writing a script that gets a count of rows for a few tables, however for some tables I want to only get a count of rows where a flag is set (in this case active=1).  Is there a way I can do this in one query?

Eg:

Table users has a column called active

Table clients does not have a column called active

I want to get a count of users where active=1 and just get a count of clients.

Before you say "just hard code it" this is a query that's going inside a python script that could be run on numerous different databases and I have no way of knowing what tables my script will be selecting and if they have a column called active, and I would prefer to have just one query to do it all instead of two separate ones and relying on mysql to throw an error so I know to use the other one.

My first thought would be to use the INFORMATION_SCHEMA first, so you get to know (in one query for all tables in the MySQL instance) which tables have an active column and then use that info to construct your queries. And this is probably the most sane approach.

There is one other, tricky way though that works no matter if the table has or not such a column:

SELECT
  ( SELECT COUNT(*)
    FROM TableName AS t
    WHERE active = 1
  ) AS cnt
FROM
  ( SELECT 1 AS active
  ) AS dummy ;

Tested at SQL-Fiddle How it works?

If the table has a column named active, the query is "translated" as if it had:

    WHERE t.active = 1

If the table doesn't have a column named active, the query is "translated" as if it had:

    WHERE dummy.active = 1         -- which is true


Slow connect time to postgresql on Windows 10 Connect time to postgresql on my Win 10 machine incredible slow.
It take 2.5-3.5 seconds for a connect.

$ time 'c:/Program Files/PostgreSQL/10/bin/psql.exe' -h 127.0.0.1 -p 5433 -U xxx -c 'select true'
 bool
------
 t
(1 row)

real    0m3.416s
user    0m0.015s
sys     0m0.000s

Same time when i try to use psycopg2 python driver. Same time for postgresql version 9 and 10.
On the other hand connect time to mysql is 0.002, issue only with postgresql.

Here relevant logs with max debug from postgresql

2018-03-19 21:24:43.654 +03 [10048] DEBUG:  00000: forked new backend, pid=21268 socket=5072
2018-03-19 21:24:43.654 +03 [10048] LOCATION:  BackendStartup, postmaster.c:4099
2018-03-19 21:24:45.248 +03 [21268] LOG:  00000: connection received: host=127.0.0.1 port=9897

It fork new backend and then only 2 seconds later logs connection received

UPD
Even if i manage to avoid connection delay of postgresql ( for example via pgbouncer, or if postgresql running in docker) request still take 1.3-2seconds, but from first sent package till last its only 0.022 second, all other time idk what happening but not a network communication between client and server. Same code if run within docker - 0.025 second. From windows - 1.3-2sec but network interaction only 0.022sec

There actually two problems that might be caused by same thing or different, no idea.

Postgresql not sending packet for 1.8 second for unknown reason
Even if first problem eliminated and network interaction down to 0.022 sec whole thing still take 1.3-2 sec instead of 2.5-3.5 (  using either psql or psycopg2)

If PostgreSQL listen on localhost only, ensure IPv4 and IPv6 both addresses are set in postgresql.conf.

listen_addresses = '127.0.0.1,::1'.


Copy contents of network drive to local folder I have an automated third party backup places files on a network drive.

I've mapped this drive to a local folder on the DB server using the following PowerShell script

$pass ="xxxx"|ConvertTo-SecureString -AsPlainText -Force
$cred = New-Object System.Management.Automation.PsCredential('MERIVALEHOSTED\SVC.APPSQL',$pass)

New-PSDrive -Name K -PSProvider "FileSystem" -Credential $cred -Root '\\10.0.30.113\sqlbackup\ACCCluster01$AccWarehouseAG';
Get-PsDrive;

This maps my network drive to the K drive.

I then want to copy the entire folder structure of this (it's the output of Ola Hallengrens backup - DIFF/FULL/LOGS) to a local drive, but not before deleting the current contents of this folder.

This is the first step of a automated DB restore. I've been able to automate the restoration process successfully using the DbaTools powershell module, but the file copy isn't working.

I've tried both:

Copy-Item -path K:\*.* -destination M:\VoxtronRestore\;

as well as this:

function Copy-Directories
{
    param (
        [parameter(Mandatory = $true)] [string] $source,
        [parameter(Mandatory = $true)] [string] $destination
    )

    try
    {
        Get-ChildItem -Path $source -Recurse -Force |
            Where-Object { $_.psIsContainer } |
            ForEach-Object { $_.FullName -replace [regex]::Escape($source), $destination } |
            ForEach-Object { $null = New-Item -ItemType Container -Path $_ }

        Get-ChildItem -Path $source -Recurse -Force |
            Where-Object { -not $_.psIsContainer } |
            Copy-Item -Force -Destination { $_.FullName -replace [regex]::Escape($source), $destination }
    }

    catch
    {
        Write-Host "$_"
    }
}

$source = "K:\"
$dest = "M:\VoxtronRestore"

Copy-Directories $source $dest

The first of which fails to copy anything, and the second gives me an error about overwriting the source with itself.

I am wondering if anyone has any better suggestions or modifications to the above which will enable this to complete successfully.

*.* will only return the files directly under K:\ (You probably have a folder with the database/instance name here). Use Copy-Item K:\ -Recurse M:\VoxtronRestore instead.


Why can't I use FORMAT in Fra? I have started working with RMAN and I have seen several comments that FORMAT should never be used when saving files to the FRA. Is there a reason for that?
For example, I use some powershell to build folders with a date and DBID in their name, before adding RMAN backups to them that night. Can I still do that in the FRA or should it be left without any FORMAT rules or sub folders? If my RMAN backup script is using CROSSCHECK, DELETE INPUT / OBSOLETE / EXPIRED files explicitly, what is the advantage of using the FRA?
Thanks in advance!

One of the advantages of the FRA is that it is self-managing. IF you use the FRA, then when there is space pressure on it, any files that can be deleted will be deleted - automatically.  With the FRA, you don't need your powershell scripts to try to manage your backup storage.  In fact, you don't want your powershell script to manage your storage.  Let Uncle Oracle and rman do it for you.  You say the directories and file names it will create create are not the same as your PS scripts?  Why does that matter, since Oracle is managing it all for you.
One caveat that a lot of people miss.  Let's say you declare your FRA to be at '/u01/oracle/FRA'.  Now, say you specifically specify a backup destination format of '/u01/oracle/FRA/mydb/20201008/'.  Doing so is not using FRA functionality, and those files will not be known to be in the FRA, even though they are there and consuming space thought to belong to the FRA.


Unable to alter partition table to set logged I have a partition table that I created UNLOGGED. I'm using pg_partman to manage the partition. I then proceeded to load data into it with a COPY command.

Afterwards, I ran an alter table set logged on the parent table but the table still shows as UNLOGGED along with all its children.

Is this broken?

You can execute these commands to see the issue:

CREATE UNLOGGED TABLE customers(cust_id bigint NOT NULL,cust_name varchar(32) NOT NULL,cust_address text,
cust_country text)PARTITION BY LIST(cust_country);

CREATE UNLOGGED TABLE customer_ind PARTITION OF customers FOR VALUES IN ('ind');

CREATE UNLOGGED TABLE customer_jap PARTITION OF customers FOR VALUES IN ('jap');

CREATE UNLOGGED table customers_def PARTITION OF customers DEFAULT;

INSERT INTO customers VALUES (2039,'Puja','Hyderabad','ind');

INSERT INTO customers VALUES (4499,'Tony','Arizona','USA');

\d+ customers
checkpoint;
alter table customers set logged ;
\d+ customers;

Throughout the display of the table details the UNLOGGED status doesn't change when running the ALTER TABLE command against the parent table. But even when all the child tables have been SET LOGGED, I'm still not able to change this on the parent table. Tools like pg_partman will look to the parent table to create the child tables with the "correct" properties.

I was recently affected by this bug. I was able to work around by detaching the partitions, re-defining the parent table to enable logging, and then re-attaching the partitions. Continuing with the OP example:

postgres=> SELECT COUNT(1) FROM customers;
 count
-------
     2
(1 row)

postgres=> SELECT relpersistence, relname FROM pg_class WHERE relname LIKE 'customer%';
 relpersistence |    relname
----------------+---------------
 u              | customer_ind
 u              | customer_jap
 u              | customers
 u              | customers_def
(4 rows)

Enable LOGGED:

ALTER TABLE customer_ind SET LOGGED;
ALTER TABLE customer_jap SET LOGGED;
ALTER TABLE customers_def SET LOGGED;
ALTER TABLE customers SET LOGGED;

Notice that the parent table remains UNLOGGED:

postgres=> SELECT relpersistence, relname FROM pg_class WHERE relname LIKE 'customer%';
 relpersistence |    relname
----------------+---------------
 p              | customer_ind
 p              | customer_jap
 u              | customers
 p              | customers_def
(4 rows)

Manually, detach/attach partitions:

ALTER TABLE customers DETACH PARTITION customer_ind;
ALTER TABLE customers DETACH PARTITION customer_jap;
ALTER TABLE customers DETACH PARTITION customers_def;

DROP TABLE customers;
CREATE TABLE customers(cust_id bigint NOT NULL,cust_name varchar(32) NOT NULL,cust_address text,
cust_country text) PARTITION BY LIST(cust_country);

ALTER TABLE customers ATTACH PARTITION customer_ind FOR VALUES IN ('ind');
ALTER TABLE customers ATTACH PARTITION customer_jap FOR VALUES IN ('jap');
ALTER TABLE customers ATTACH PARTITION customers_def DEFAULT;

Verify that change worked:

postgres=> SELECT COUNT(1) FROM customers;
 count
-------
     2
(1 row)

postgres=> SELECT relpersistence, relname FROM pg_class WHERE relname LIKE 'customer%';
 relpersistence |    relname
----------------+---------------
 p              | customer_ind
 p              | customer_jap
 p              | customers
 p              | customers_def
(4 rows)


SQL server 2005 backup statistic I'd like to share with you this simple snippet (answering my own question) and I'm also curious how do you control your SQL server backups.

One of the slickest ways I've seen to control backups is Ola Hallengren's free maintenance script solution:

http://ola.hallengren.com/

It's installed as a series of stored procs in Master that do all kinds of nifty utility tasks.  It'll automatically handle newly added databases, leverages third party backup compression tools, and does index rebuilds/defrags too.


Is is posible to sample a database Is it possible to sample any SQL database?

By sampling I means the database table should remain and retaining the foreign key constraints.
But the actual rows from the table should be reduced.If I have 1 million records in some of my tables then in the sample database I only want any 100 records.
I am doing some analysis and processing all records takes many days.

Sure you can, you can append the SAMPLE clause to your selects:

For example:

select * from table sample(20);

sample_clause

  The sample_clause lets you instruct the database to select from a
  random sample of data from the table, rather than from the entire
  table.

  BLOCK
  BLOCK instructs the database to attempt to perform random block
  sampling instead of random row sampling.

  Block sampling is possible only during full table scans or index fast
  full scans. If a more efficient execution path exists, then Oracle
  Database does not perform block sampling. If you want to guarantee
  block sampling for a particular table or index, then use the FULL or
  INDEX_FFS hint.

  sample_percent
  For sample_percent, specify the percentage of the
  total row or block count to be included in the sample. The value must
  be in the range .000001 to, but not including, 100. This percentage
  indicates the probability of each row, or each cluster of rows in the
  case of block sampling, being selected as part of the sample. It does
  not mean that the database will retrieve exactly sample_percent of the
  rows of table.

  ...


insert multiple depend on prev queries I need to insert multiple records into a table. The number of records depend on the result of another query. For example:I want to insert booking detail depend on number of room customer choose. If customer choose 2 room, inserting should run two times. How can I do that?

$fetch = mysql_query("SELECT r.room_id FROM room AS r WHERE
(r.room_name  LIKE '$roomtype') AND r.room_id NOT IN (SELECT b.room_id
FROM bookingdetail AS b WHERE b.datearr =  '$datearrive' OR b.datedep =
'$datedepart LIMIT '$roomno')")or die(mysql_error());

$sql="INSERT INTO bookingdetail (booking_id, room_id, datearr, datedep)
VALUES ('$bookingid', '$room_id', '$datearrive', '$datedepart')";

This problem can easily be solved by php...
Get the number of rooms selected from the first query and assign it to a variable and then loop the second query by the number of rooms choosen.


Postgres SQL - Compare Count of rows, returning where a < b or b doesn't exist I'm trying to select the 'hash' value from the tags table where either of the following conditions are true:

Count of the 'thumb\_%' rows is Greater than Count of 'dht\_%' rows
Count of the 'thumb\_%' is Greater than 0 but no 'dht\_%' rows exist

I'm using Postgres version PostgreSQL 9.6.6

I have a 'tags' table like so:

CREATE TABLE tags (
    tag_id SERIAL PRIMARY KEY,
    hash VARCHAR(16),
    val VARCHAR(128),
    UNIQUE (hash, val)
);

INSERT INTO tags VALUES
 (243882, 'b119ceb321058d7c', 'thumb_0_1.00'),
 (243883, 'b119ceb321058d7c', 'thumb_1_2.79'),
 (243884, 'b119ceb321058d7c', 'thumb_2_5.59'),
 (243885, 'b119ceb321058d7c', 'thumb_3_8.38'),
 (243886, 'b119ceb321058d7c', 'thumb_4_11.18'),
 (243887, 'b119ceb321058d7c', 'thumb_5_13.97'),
 (244237, '8dbb1085750696c5', 'dht_0_c0b19393ad2c3c84'),
 (244238, '8dbb1085750696c5', 'dht_1_c0b193b3ad2d3884'),
 (244239, '8dbb1085750696c5', 'dht_2_c0b193b3ad2c3c84'),
 (244240, '8dbb1085750696c5', 'thumb_0_207.37'),
 (244241, '8dbb1085750696c5', 'thumb_1_276.50'),
 (244242, '8dbb1085750696c5', 'thumb_2_345.62'),
 (244286, '8dbb1085750696c5', 'thumb_3_1.00');

The tags table has other values associated with a hash besides these, however this query isn't concerned with the extra values.

This is the query I've been testing so far:

SELECT * FROM
(
    SELECT tags.hash, COUNT(*) as dh FROM tags WHERE val ILIKE 'dht\_%' GROUP BY tags.hash
) AS b LEFT OUTER JOIN
(
    SELECT tags.hash, COUNT(*) as thm FROM tags WHERE val ILIKE 'thumb_%' GROUP BY tags.hash
) AS a
ON (a.hash = b.hash AND a.thm > b.dh AND a.thm > 0) OR (a.thm > 0 and b.hash IS NULL);

The desired results would be similar to the following:

       hash       | dh | thm
------------------+----+------
 b119ceb321058d7c |    | 6     <- no dht_ exist for this result
 8dbb1085750696c5 |  3 | 4     <- dht_ is less than thumb_ results

A little background if you want to read.

This is part of a media tagging and management system I've written for some photography we take for some of our businesses. We extract thumbnails from videos and 'tag' the timestamp of each thumbnail. I also have some additional auto-tagging features which attempts to gather specific metadata and insert into this tags table. We have about 19k unique files with about 330k tags. Hashing is done via XXhash, helping us find duplicate files. The dht_ tags are dhashes of a given thumbnail image, to allow us to find videos that are very similar. This is all wrapped up in a nice little node express website and working quite well, until I tried implementing the dhashing of these thumbnail images. My query is much more complicated than the above example, however that's the portion I'm struggling with. Sadly my knowledge of sql has slipped in recent years and as this is a side-project, I've been struggling to get this to work. I am however very happy about the rest of the setup, and for media tracking and such, it's doing it's job well enough already that its saving us a bunch of time hunting down a specific media file.

You can get COUNT of each tag by using next query;

SELECT hash,
       SUM(CASE WHEN val ILIKE 'dht\_%' THEN 1 ELSE 0 END) dht,
       SUM(CASE WHEN val ILIKE 'thumb_%' THEN 1 ELSE 0 END) thumb
FROM   tags
GROUP BY hash;

hash             | dht | thumb
:--------------- | --: | ----:
8dbb1085750696c5 |   3 |     4
b119ceb321058d7c |   0 |     6

Then apply filter conditions:

WITH CTG AS
(
    SELECT hash,
           SUM(CASE WHEN val ILIKE 'dht\_%' THEN 1 ELSE 0 END) dht,
           SUM(CASE WHEN val ILIKE 'thumb_%' THEN 1 ELSE 0 END) thumb
    FROM   tags
    GROUP BY hash
)
SELECT hash, dht, thumb
FROM   CTG
WHERE  (thumb > dht)
OR     (thumb >= 1 AND dht = 0);

hash             | dht | thumb
:--------------- | --: | ----:
8dbb1085750696c5 |   3 |     4
b119ceb321058d7c |   0 |     6

The condition can be further simplified to:

WHERE  (thumb > dht) ;

And it might be more efficient as a subquery (derived table) than a CTE, if the rows (that are produced by the CTE/derived table) are very many:

SELECT hash, dht, thumb
FROM
(
    SELECT hash,
           SUM(CASE WHEN val ILIKE 'dht\_%' THEN 1 ELSE 0 END) dht,
           SUM(CASE WHEN val ILIKE 'thumb_%' THEN 1 ELSE 0 END) thumb
    FROM   tags
    GROUP BY hash
)
    AS CTG
WHERE  (thumb > dht) ;

dbfiddle here


Re enable Windows Authetication in SQL Server My old employee has disabled Windows Authentication in our server. Now I'm not able to access the SQL Server even though I have Administrator access to the server. I need to reset the sa password.

I tried logging in using single user mode as Remus described but I get the following error:

  Login failed for user 'SERVER\Administrator'.
  Reason: The account is disabled. (Microsoft SQL Server, Error: 18470)

Follow the steps in:

Connect to SQL Server When System Administrators Are Locked Out.

Briefly:

Start server with the -m switch
Connect as  a local administrator
Enable the sa account (and reset its password if necessary)
Restart server normally


SQL Comments disappear with psql and `\e` Is there anyway at all, ever to change the behavior of comments in psql. Take the query below. Execute it.

CREATE TABLE foo
AS
  SELECT x AS id,
    -- x AS id2,
    x AS id3
  FROM generate_series(1,50) AS x;

Run that in psql. Then run \e. Now at least, for me what I see in my editor is the line absent. This is driving me crazy. Is there a way around this.. The comment is just absent from the buffer that gets passed to the editor. Often, it's commented and not deleted because I want to uncomment it at a later point.

Workaround with C-style comments

For whatever reason.. this seems to work when you use C-style comments instead of standard SQL comments,

CREATE TABLE foo
AS
  SELECT x AS id,
    /* x AS id2, */
    x AS id3
  FROM generate_series(1,50) AS x;

Now you can do \e and edit it.


how to setup mongodb replica on cloud server? I want to setup mongodb repicaset on my cloud server(AWS ec2) but I have only one IP address.
Currently I have make /etc/hosts two entries 127.0.0.1 dbnode1 and xx.xx.xx.xx dbnode2 but when I try to reconfig rs config its give error.

Error:- Quorum check failed because not enough voting nodes responded; required 2 but only the following 1 voting nodes responded: node1:27017; the following nodes did not respond affirmatively: node2:27019 failed with Error connecting to node2:27019 (127.0.0.2:27019) :: caused by :: Connection refused

Can anyone suggest how to I can setup replica or without setup replica how to use transaction query.

If you try to set up replica set of two (or more) nodes at one physical server... You must use IP-addresses and not host names. So 127.0.0.1:27017, 127.0.0.1:27019


Are updates the only disadvantage of denormalization? I am trying to understand what are the disadvantages of denormalization. Say that I have the following database (School_has_Student is a denormalized table):

I have read that the problems that you may face when using denormalization are related to the operations of INSERT, UPDATE, and DELETE.

Now I understand why we have the UPDATE problem, because if I updated one piece of data, then I would have to update the rest of the related pieces of data (for example: if I updated the student name "Paul" in the Student table, then I would have to also update the student name "Paul" that exist two times in the School_has_Student table), but I don't understand why INSERT and DELETE are also problems.

Note: I know that the increase of storage space is also a disadvantage, but I do not think it is a big disadvantage with current storage devices which have very large capacity.

There are different kinds of denormalization. Say we can have the main table readouts like that:

CREATE TABLE `readouts` (
  `sensorID` INT(10) UNSIGNED NOT NULL,
  `timestamp` TIMESTAMP NOT NULL,
  `value` FLOAT(9,5) NOT NULL,
  PRIMARY KEY (`sensorID`, `timestamp`)
);
+----------+-----------+-------+
| sensorID | timestamp | value |
+----------+-----------+-------+

Table stores the readouts from the lot of sensors. If we need the last readout for each sensor we have to run the query like that:

SELECT a.*
  FROM readouts AS a
  JOIN ( SELECT sensorID
              , MAX(timestamp) AS timestamp
          GROUP BY sensorID
       ) AS b  ON b.timestamp = a.timestamp
              AND b.sensorID  = a.sensorID
 ORDER BY sensorID ASC
;

That is quite heavy query even when the table readouts is properly indexed. We can denormalize the database by creating the table of the same structure but the UNIQUE constraint for sensorID:

CREATE TABLE `last_readouts` (
  `sensorID` INT(10) UNSIGNED NOT NULL,
  `timestamp` TIMESTAMP NOT NULL,
  `value` FLOAT(9,5) NOT NULL,
  PRIMARY KEY (`sensorID`)
);

Now we have to INSERT incoming readouts to the both tables. The second table should be inserted in the special way:

INSERT INTO last_readouts
     VALUES (sID, ts, val)
     ON DUPLICATE KEY UPDATE
        `timestamp` = ts
         value = val
;

This addtitional INSERT is the insert-related disadvantage of the denormalization. But the advantage is the minimal cost of the query

SELECT * FROM last_readouts;

The DELETE disadvantage is of the same sort - you need more operations of DELETE but you have some benefits instead.


Can I refactor this query to get it to run in parallel? I have a query that takes about 3 hours to run on our server--and it doesn't take advantage of parallel processing. (about 1.15 million records in dbo.Deidentified, 300 records in dbo.NamesMultiWord). The server has access to 8 cores.

  UPDATE dbo.Deidentified
     WITH (TABLOCK)
  SET IndexedXml = dbo.ReplaceMultiWord(IndexedXml),
      DE461 = dbo.ReplaceMultiWord(DE461),
      DE87 = dbo.ReplaceMultiWord(DE87),
      DE15 = dbo.ReplaceMultiWord(DE15)
  WHERE InProcess = 1;

and ReplaceMultiword is a procedure defined as:

SELECT @body = REPLACE(@body,Names,Replacement)
 FROM dbo.NamesMultiWord
 ORDER BY [WordLength] DESC
RETURN @body --NVARCHAR(MAX)

Is the call to ReplaceMultiword preventing forming a parallel plan? Is there a way to rewrite this to allow parallelism?

ReplaceMultiword runs in descending order because some of the replacements are short versions of others, and I want the longest match to succeed.

For example, there may be 'George Washington University' and another from 'Washington University'. If the 'Washington University' match were first, then 'George' would be left behind.

Technically I can use CLR, I'm just not familiar with how to do so.

The UDF is preventing parallelism. It also is causing that spool.

You could use CLR and a compiled regex to do your search and replace. It doesn't block parallelism as long as the required attributes are present and will likely be significantly faster than performing 300 TSQL REPLACE operations per function call.

Example code is below.

DECLARE @X XML =
(
    SELECT Names AS [@find],
           Replacement  AS [@replace]
    FROM  dbo.NamesMultiWord
    ORDER BY [WordLength] DESC
    FOR XML PATH('x'), ROOT('spec')
);

UPDATE dbo.Deidentified WITH (TABLOCK)
SET    IndexedXml = dbo.ReplaceMultiWord(IndexedXml, @X),
       DE461 = dbo.ReplaceMultiWord(DE461, @X),
       DE87 = dbo.ReplaceMultiWord(DE87, @X),
       DE15 = dbo.ReplaceMultiWord(DE15, @X)
WHERE  InProcess = 1;

This depends on the existence of a CLR UDF as below (the DataAccessKind.None should mean the spool disappears as well as that is there for Halloween protection and isn't needed as this doesn't access the target table).

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using System.Xml;

public partial class UserDefinedFunctions
{
    //TODO: Concurrency?
    private static readonly Dictionary<string, ReplaceSpecification> cachedSpecs =
                        new Dictionary<string, ReplaceSpecification>();

    [SqlFunction(IsDeterministic = true,
                 IsPrecise = true,
                 DataAccess = DataAccessKind.None,
                 SystemDataAccess = SystemDataAccessKind.None)]
    public static SqlString ReplaceMultiWord(SqlString inputString, SqlXml replacementSpec)
    {
        //TODO: Implement something to drop things from the cache and use a shorter key.
        string s = replacementSpec.Value;
        ReplaceSpecification rs;

        if (!cachedSpecs.TryGetValue(s, out rs))
        {
            var doc = new XmlDocument();
            doc.LoadXml(s);
            rs = new ReplaceSpecification(doc);
            cachedSpecs[s] = rs;
        }

        string result = rs.GetResult(inputString.ToString());
        return new SqlString(result);
    }

    internal class ReplaceSpecification
    {
        internal ReplaceSpecification(XmlDocument doc)
        {
            Replacements = new Dictionary<string, string>();

            XmlElement root = doc.DocumentElement;
            XmlNodeList nodes = root.SelectNodes("x");

            string pattern = null;
            foreach (XmlNode node in nodes)
            {
                if (pattern != null)
                    pattern = pattern + "|";

                string find = node.Attributes["find"].Value.ToLowerInvariant();
                string replace = node.Attributes["replace"].Value;
                 //TODO: Escape any special characters in the regex syntax
                pattern = pattern + find;
                Replacements[find] = replace;
            }

            if (pattern != null)
            {
                pattern = "(?:" + pattern + ")";
                Regex = new Regex(pattern, RegexOptions.IgnoreCase | RegexOptions.Compiled);
            }

        }
        private Regex Regex { get; set; }

        private Dictionary<string, string> Replacements { get; set; }

        internal string GetResult(string inputString)
        {
            if (Regex == null)
                return inputString;

            return Regex.Replace(inputString,
                                 (Match m) =>
                                 {
                                     string s;
                                     if (Replacements.TryGetValue(m.Value.ToLowerInvariant(), out s))
                                     {
                                         return s;
                                     }
                                     else
                                     {
                                         throw new Exception("Missing replacement definition for " + m.Value);
                                     }
                                 });
        }
    }
}


Can the default minimum date range for 'datetime' be changed in SQL Server 2008 R2? The minimum date range is set to somewhere in the 1750's. I need to store dates prior to 1750.  One solution that came to mind is to store the dates in a particular format in a 'varchar' field, and later on, retrieve them back to the UI as we want.

Is there a better solution to this - an elegant one?

A datetime2 goes back further - http://msdn.microsoft.com/en-us/library/bb677335.aspx - to 0001. If you want to go back further still, then a custom data type may be an option, although given the potential confusion and lack of precision even before 1753, it is doubtful how precise you could expect to be.