APEX Installation ORA-01435 while running @apxconf I'm trying to configure and use APEX on a Oracle Database 12c. When I try to run @apxconf I get the Error ORA-01435. I don't really now what is wrong since I followed the Documentation. https://docs.oracle.com/database/121/HTMIG/E17958-12.pdf#G5.1084317

SQL> @apxconf

PORT
8080

Enter values below for the XDB HTTP listener port and the password for the Appli
cation Express ADMIN user.
Default values are in brackets [ ].
Press Enter to accept the default value.

ERROR:
ORA-01435: Benutzer ist nicht vorhanden.

Verbindung zu Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit
Production beendet

Verbindung zu Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit
Production beendet => Connection closed

Working Directory is dbhome_1/apex.

I'm logged in with sys as sysdba.

Oracle support doc 1970925.1 suggests that this is because you're trying to use the wrong version of script with the installed APEX version (e.g. 5.0 APEX installation but 5.1 scripts).

Ensure you're using the right version of scripts with the right APEX version.

Another reason this could happen, is if APEX isn't even installed.  Note that with 12.2 onwards, APEX isn't installed in the database by default.


ORA-48108 & ORA-01034 I'm trying to set up a database on a Solaris 10 platform that had its database corrupted before. Due to the lack of backups, I was forced to reinstall Oracle server. Currently I'm facing an issue where answers I have found keep sending me in loops.

I opened sqlplus, and saw that it was connected to an idle instance, so I performed the STARTUP command. This led me to the following errors:

ORA-48108: invalid value given for the diagnostic_dest init.ora parameter
ORA-48140: the specified ADR Base directory does not exist
           [/export/home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/<ORACLE_BASE>]

So after some googling, I was told to try changing the diagnostic_dest via the following: ALTER SYSTEM SET diagnostic_dest='u01/app/oracle';, which was equivalent to setting the diagnostic_dest to my ORACLE_BASE.

However, the error below occurred:

ORA-01034: ORACLE not available

After some googling again, I found that the solution was to start the database (which I can't)! I have no idea how I should proceed from here. Any help will be much appreciated. If any other information is required, I will gladly update it in the OP.

I have tried restarting lsnrctl, but it doesn't seem to help.

I would change all the default parameters within the file locations, but from what I found, the init.ora file which was supposed to be used already has diagnostic_dest='<ORACLE_BASE>', the spfile.ora file also has diagnostic_dest='/u01/app/oracle'. I'm really lost as to how I should carry on.

You can not use ALTER SYSTEM when the instance is not started, but you can not start the instance because of an incorrect parameter value. Create a PFILE (if you do not already have one), edit that, then start the instance.

$ sqlplus / as sysdba
SQL> create pfile from spfile;
SQL> exit
$ vi $ORACLE_HOME/dbs/init${ORACLE_SID}.ora

Set the value of diagnostic_dest as required, then save the file.

$ sqlplus / as sysdba
SQL> create spfile from pfile;
SQL> startup

Note: the directory must exist.

Answer to comment:

  For UNIX or Linux, the platform-specific default location (directory)
  for the SPFILE and text initialization parameter file is:

  $ORACLE_HOME/dbs

  Oracle Database locates your initialization parameter file by
  examining filenames in the following order:

  spfile$ORACLE_SID.ora
  spfile.ora
  init$ORACLE_SID.ora

Or, for a test, you can startup the instance by manually specifying the pfile, for example:

SQL> startup pfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initORCL.ora


MariaDB 10 replication strange behaviour I have following MariaDB 10 replication setup.

Server A MariaDB 10 (master), Server B MariaDB 10 (master), Server C MariaDB 10 (slave/master), Server D MariaDB 10 (slave).

A and B are getting new data from different sources (Percona Server, AWS RDS) using Tungsten replicator and replicate this data to C. C replicates to D.

My steps:

mysqldump from A and B
import to C and configure multi-source replication
mysqldump from C
import to D configure replication

After a while A and B are getting new data, but this data is not replicating neither to C or D (from C).

If I go to A or B and create fake tables and insert fake data everything replicates up to D.

No errors in mysql error log, no errors in replication status.

What can be possible wrong?

Ensure you have --log-slave-updates and --log-bin enabled in the servers.

  Normally, a slave does not write to its own binary log any updates
  that are received from a master server. This option causes the slave
  to write the updates performed by its SQL thread to its own binary
  log. For this option to have any effect, the slave must also be
  started with the --log-bin option to enable binary logging. Prior to
  MySQL 5.5, the server would not start when using the
  --log-slave-updates option without also starting the server with the --log-bin option, and would fail with an error; in MySQL 5.6, only a warning is generated. (Bug #44663) --log-slave-updates is used when
  you want to chain replication servers. For example, you might want to
  set up replication servers using this arrangement:

  A -> B -> C

  Here, A serves as the master for the slave B, and B serves as the
  master for the slave C. For this to work, B must be both a master and
  a slave. You must start both A and B with --log-bin to enable binary
  logging, and B with the --log-slave-updates option so that updates
  received from A are logged by B to its binary log.

Source: http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_log-slave-updates


Missing the pg_ctl package in Postgres 9.3 installation I have installed postgresql 9.3 on ubuntu 14.04 using "apt-get install postgresql".

Everything was going well until I discovered that I do not have access to the "pg_ctl" commands. Installing the postgres-xc literally breaks postgresql server. The package links become broken. I cannot re-install and have had to revert my VM and keep working without pg_ctl.

My question is, to get access to this package, should I start from scratch or uninstall and re-install postgresql?
What should I have installed in the first place?
Alternatively, is there something wrong with my path?

(I am new to postgresql and have been tossed into it for work purposes)

In Debian/Ubuntu, the functionalities of pg_ctl  are provided by pg_ctlcluster. Quoted from its manpage:

NAME

       pg_ctlcluster - start/stop/restart/reload a PostgreSQL cluster

SYNOPSIS

       pg_ctlcluster [options] cluster-version cluster-name action -- [pg_ctl
       options]

       where action = start|stop|restart|reload|promote

DESCRIPTION

       This program controls the postmaster server for a particular cluster.
       It essentially wraps the pg_ctl(1) command. It determines the cluster
       version and data path and calls the right version of pg_ctl with
       appropriate configuration parameters and paths.

It's not technically true that pg_ctl is not installed with postgres packaged for Debian/Ubuntu. It's actually installed in /usr/lib/postgresql/X.Y/bin, where X.Y is the postgres version. This path is intentionally not in any user's default PATH since it should never needed to be invoked directly.

The reason for this setup is that the Debian/Ubuntu packaging supports several PostgreSQL instances running in parallel, either identical or different major versions.

To learn about that, it's best to read first https://help.ubuntu.com/community/PostgreSQL , before the real PostgreSQL manual or any other non-Ubuntu documentation.

postgres-xc is a multi-master fork of PostgreSQL that shouldn't be confused with PostgreSQL itself. The hint suggesting to install it to get pg_ctl is an unfortunate nonsense, probably a machine-generated advice based on the contents of postgres-xc.


PostgreSQL High Availability/Scalability using HAProxy and PGBouncer I have multiple PostgreSQL servers for a web application.
Typically one master and multiple slaves in hot standby mode (asynchronous streaming replication).

I use PGBouncer for connection pooling: one instance installed on each PG server (port 6432) connecting to database on localhost. I use transaction pool mode.

In order to load-balance my read-only connections on slaves, I use HAProxy (v1.5) with a conf more or less like this:

listen pgsql_pool 0.0.0.0:10001
        mode tcp
        option pgsql-check user ha
        balance roundrobin
        server master 10.0.0.1:6432 check backup
        server slave1 10.0.0.2:6432 check
        server slave2 10.0.0.3:6432 check
        server slave3 10.0.0.4:6432 check

So, my web application connects to haproxy (port 10001), that load-balance connections on multiple pgbouncer configured on each PG slave.

Here is a representation graph of my current architecture:

This works quite well like this, but I realize that some implements this quite differently: web application connects to a single PGBouncer instance that connects to HAproxy which load-balance over multiple PG servers:

What's the best approach? The first one (my current one) or the second one? Are there any advantages of one solution over the other?

Thanks

Your existing configuration of HAProxy -> PGBouncer -> PGServer approch is better. And that only works.
Here is the reason: HAProxy redirects connection to different servers. this results in MAC address change in the database connection. So if PGBouncer is above HAProxy, each time the connections in the pool gets invalidated because of MAC address change.


How to optimize stored procedure so it won't end with "Out of memory exception" I have created a stored procedure that check against 2 and then delete from the 3.
When I execute the stored procedure on Management Studio I get the exception as mentioned in this question: StackExchange Question
However what my question is how to optimize the SP so it will not take that long time to run?
My code is as following:

 CREATE PROCEDURE [dbo].[Clean] (
 @Deletion date
  ) AS
   BEGIN

BEGIN TRANSACTION Cleaning
DECLARE @id int
Declare @ErrorCode int =1

 DECLARE cursorE CURSOR local fast_forward FOR
        select distinct m.ID
        from Member m
        left join (
            select *, row_number() over (PARTITION BY rid order by ceid
desc) as rn
            from TypeA
        ) x on m.ID = x.ID and x.rn = 1
        where (
            (x.ceid is null and m.LastChangedDateTime < @Deletion)
            or x.Resignation < @Deletion
        )

    OPEN cursorE
    FETCH NEXT FROM cursorE INTO @erID

     WHILE ( @@FETCH_STATUS = 0 )
  DELETE FROM Errn WHERE erid = @id
  FETCH NEXT FROM cursorE INTO @rID
  COMMIT TRANSACTION Cleaning
  RETURN 1
  END
 CLOSE cursorE
DEALLOCATE cursorE
ERRORHANDLER:
-- Rollback the transaction if there were any errors
 ROLLBACK TRANSACTION Cleaning
 RETURN @ErrorCode

GO

You can do it in a single statement.

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY

BEGIN TRANSACTION;

DELETE FROM
    Errn
WHERE
    erid IN (
             select
                 distinct m.ID
             from
                 Member m
             left join
                 (select
                      *, row_number() over (PARTITION BY rid order by ceid desc) as rn
                  from TypeA) x
                 on m.ID = x.ID and x.rn = 1
             where
                 (
                     (x.ceid is null and m.LastChangedDateTime < @Deletion)
                     or
                     x.Resignation < @Deletion
                 );

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    RAISERROR('ERR MSG', 16 1);

END CATCH


Assignment of Thread IDs? I'm in the process of decommissioning an old mysql server.  Of course there's a few things left connecting to it periodically people had since forgotten about.  I turned on general query logging to get a better view of what was still going on there to track down the culprits and noticed something odd.

There was an entry like:

870 Connect     <user>@<server> on

It went on and did it's thing ending with a

870 Quit

The very next line was

1143 Connect     <user>@<server>

Why the jump in thread IDs?  Just grepping out all the "Connect" lines there are bursts of incrementing thread ids +1 then varying size gaps for the next connect id.

Is this expected behavior?

Failed Connection Attempts will still increment the Process ID next to be assigned

Here is an example:

I will restart mysql on my PC (MySQL 5.5.12) and connect for the first time

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

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Then I connect to mysql with an intentional bad password

I then connect to mysql with the right password

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

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

and BINGO a MySQL Connection ID of 3 instead of 2

There is a record of it in the Global Status variables

mysql> show global status like 'abort%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_clients  | 0     |
| Aborted_connects | 1     |
+------------------+-------+
2 rows in set (0.00 sec)

mysql>

You evidently have connections still coming in from somewhere who can no longer authenticate. Maybe you could try using netstat and watch port 3306. If your have removed certain user@host values from mysql.user, then there is no need for real concern in terms of the process ID established and used.

You may want to run this query now and then...

SELECT hostaddr,COUNT(1) connections FROM
(SELECT LEFT(host,LOCATE(':',host) - 1) hostaddr
FROM information_schema.processlist
WHERE LOCATE(':',host) > 0) A
GROUP BY hostaddr;

This will tell you how many connections are coming in per IP address or DNS Name


Are there any good reasons for keeping date and time in separate columns? I'm trying to understand our software vendor's decision to keep date and time in separate columns. For example, when the row was created or updated. Both time and date are DateTime columns. We are using SQL Server 2005.

The database holds our ERP system's data and I believe the largest tables contain about ~3 million rows. Most of the tables are roughly between 100 000 - 1 000 000 rows.

I would personally by default choose a single DateTime for a single timestamp. This would allow easier time difference calculations and the date and time parts could be easily extracted from the timestamp. It would also consume less space.

Is separating date and time bad practice or is there something very brilliant in this design I don't understand?

My guess is that it's a legacy support thing (i.e back in version 1 of their software, they had date and time in seperate fields and they've been forced to support it because it always worked that way).

That said, there's nothing brilliant about seperating date and time, so there's nothing you're missing.

(The other option is that your ERP system developers didn't know that a datetime field can store a date and a time, but far more likely is that they had a business requirement to make it work the same as prior versions.)


Process to reclaim free space from mongodb ,replication is there We are facing space pressure on production server so we are planning to  reclaim space from prod instance.replication is there ,can anyone help me  to  know complete process in sequence to reclaim free space.

I am thinking to use repair db commend at slave node to reclaim space but don't know how to make secondary node after repairing to primary server and start replication again.

Once and for all: The database repair command is potentially harmful and is advised against

  You should not use repairDatabase for data recovery unless you have no other option.

And

  This command obtains a global write lock and will block other operations until it has completed.

Hardly what you want in a production system. I already answered a similar, though not identical question. However, the same rules apply. Read the part on capacity planning closely.

And honestly: if you have to ask for the procedure, you really, really, really should not even think about executing it. Even when you get a detailed How-To, you will lack any knowledge of potential side effects. If it is a production system, get yourself a DBA. It is worth it.


mysql_install_db: How to set the root password mysql 5.6 seems to have a new --random-passwords option for mysql_install_db, which lets me discover the root password:
http://dev.mysql.com/doc/refman/5.6/en/mysql-install-db.html#option_mysql_install_db_random-passwords

But what can I do with mysql 5.5? The documentation suggests that the root password should be empty, but I cannot seem to connect with no password (or an empty password), so i cannot set a real root password:

For instance:

I initialize the database like so:

$ mysql_install_db --no-defaults --user=murrayc --datadir=/tmp/testmysql/data

I start the server like so:

$ mysqld_safe --no-defaults --user=murrayc --port=3308 --datadir='/tmp/testmysql/data' --socket='/tmp/testmysql/mysqld.sock' --pid-file='/tmp/testmysql/pid'

But when I try to set the root password like so (I believe that --user in this case refers to the mysql user, not the linux user):

$ mysqladmin --no-defaults --port=3308 --user=root --password='' password 'somenewpassword'

I get this error:

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

Upon installation, root@localhost does not have a password.

You should be able to connect without a password by just doing this:

$ mysql -uroot

Try the following:

$ mysqladmin --no-defaults --port=3308 --user=root password 'somenewpassword'

or you can do it the stubborn way:

$ service mysql restart --skip-grant-tables --skip-networking
$ mysql -e"UPDATE mysql.user SET password=password('somenewpassword') WHERE user='root'"
$ service mysql restart
$ mysql -uroot -p

UPDATE 2013-01-02 16:47 EDT

I am sorry, I overlooked the port number.

Here is the problem: you cannot use root@localhost against a non-3306 port unless you use the TCP/IP protocol. Please try this:

$ mysqladmin --no-defaults --port=3308 --user=root --protocol=tcp password 'somenewpassword'


Table redirect / filter / trigger on select Is there any way to redirect queries to different tables / views based on the package that's referencing the table?  I.e. packages A and B both have "select grade from schema1.grd_tbl", but I want package A to get the percent grade that's stored in the table, and package B to get a letter grade that's calculated from the percent.  I'd like to avoid modifying the (dozens of) packages that reference the table.  I'd rather 'spoof' the table somehow if we can, replacing the percent in the grade column with a letter when called from those packages. (The column's varchar2; percents are coded as characters.)

First thought was to create a synonym pointing to a view that massages the column based on the calling package, but the code fully qualifies the table name in most cases so that doesn't seem doable. I went looking for something equivalent to a trigger on select; the closest I've found is fine-grained audit, and it's not row-based.

Is there magic somewhere that might let me do this?  Any hints appreciated.

Perry.

You could do something like this:

create or replace package body pkg1
is
...
begin
  dbms_application_info.set_module('PKG1');
end;
/

create or replace package body pkg2
is
...
begin
  dbms_application_info.set_module('PKG2');
end;
/

You can then access the module information using SYS_CONTEXT():

create or replace view v1
as
select
  case
    when sys_context('USERENV','MODULE') = 'PKG1' then to_char(grade_pct)
    when sys_context('USERENV','MODULE') = 'PKG2' then calc_grade_letter(grade_pct)
    else ''
  end
...
from
  yourtable

Instead of the function calculating the letter from percentage you could use a view to select from, of course. Be aware that the performance of queries against that view will likely suffer because of all the PL/SQL calls.


SQL Server job - powershell Where does "log to table" save logs After selecting the "log to table" option and running the job, when I come back to this screen and select View, I can see the output of the powershell script in a text file.

The only job step is a call to a powershell script that takes a string as input and prints it after performing a toUpper

However, where are these logs being stored?

A simple press on the F1 key for the help would reveal, that the execution result is stored in the sysjobstepslogs table of the msdb database.


How to determine if a column already exists in a MySQL table before altering the table to add it? I am trying to determine if a column already exists in a MySQL table before altering the table to add it. I need an all in one query I can run since I am executing this through an old PHP mysql library that only executes a single statement in one call. This the query I was attempting to use.

SELECT COUNT(*) as col_count
FROM information_schema.COLUMNS
WHERE
    TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'mytable'
AND COLUMN_NAME = 'status';

Unfortunately the DATABASE() function returns information_schema instead of current connection DB at least in PHPMyAdmin. It may be different through the PHP mysql driver. Therefore, it returns zero for the column count.

SELECT DATABASE(), COUNT(*) as col_count
FROM information_schema.COLUMNS
WHERE
    TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'mytable'
AND COLUMN_NAME = 'status';

This returns: "Information_schema", "0" despite the fact that the original connection was to a different database.

I have come up with a workaround in PHP but would still like to know if this is possible using only SQL.

You were on the right track.  Add one thing before your code:

USE YourDb;  -- You need to be in _your_ db to make `DATABASE()` work right

SELECT COUNT(*) as col_count
    FROM information_schema.COLUMNS
    WHERE  TABLE_SCHEMA = DATABASE()
      AND  TABLE_NAME = 'mytable'
      AND  COLUMN_NAME = 'status';


Looping through string, adding all numbers e.g. '123' = 1+2+3, demo with working loop included This works to output the string 123456 as:

1
2
3
4
5
6

Code:

declare @string varchar(20)
declare @index int
declare @len int
declare @char char(1)

set @string = '123456'
set @index = 1
set @len= LEN(@string)

WHILE @index<= @len
BEGIN
set @char = SUBSTRING(@string, @index, 1)
print @char

SET @index= @index+ 1
END

But when I try to use this code to add those values up when looping through them (1+2+3+4+5+6 = 21), then I do no get a result from SQL, please help

declare @string varchar(20)
declare @index int
declare @len int
declare @char char(1)

set @string = '123456'
set @index = 1
set @len= LEN(@string)

declare @Total int

WHILE @index<= @len
BEGIN
set @char = SUBSTRING(@string, @index, 1)
set @Total = @Total + cast(@char as int)

SET @index= @index+ 1
END

print @Total

You need to set @Total to a default value, otherwise it is NULL and NULL + anything always returns NULL.

Changing this line returns 21 for me:

declare @Total int

to

declare @Total int = 0

That being said, this is not a great thing to try to do on a large number of rows.  If you need to do this on scalar values like you are using there, you may want to consider using a scalar udf for it.  This will NOT scale well though so if you use it against a table be prepared to wait a while.


Returning XML Value for Extended Event I would like to get the filepath for my extended event event_file and put it inside a variable but I'm stuck.

Here's what I have:

DECLARE @ExpensiveXML XML

SET @ExpensiveXML = (SELECT CAST(xet.target_data AS xml) AS 'XML'
                    FROM sys.dm_xe_session_targets AS xet
                    JOIN sys.dm_xe_sessions AS xe
                    ON (xe.address = xet.event_session_address)
                    WHERE xe.name = '<my extended event>');

SELECT @ExpensiveXML.query('/EventFileTarget/File')

and it returns:

<File name="MyFilePath.xel" />

I would like to only return MyFilePath.xel without the < File name=.../ > around it.

Use value() instead.

The value() function returns a scalar value and takes two parameters. The first is the xpath expression and the second is the data type you want.

@ExpensiveXML.value('(/EventFileTarget/File/@name)[1]', 'nvarchar(260)');


Does oracle client 12c requires Java on windows if I install Oracle 12c client on window server, does it get Java installed?

can we install oracle driver/client without Java?

Oracle Client - the "thick" one - has own JRE embedded, it is used to run GUI management tools. If you do not need it, download and install Oracle (thin) Instant client


bcp command Incorrect syntax near '�'. Character is actually: "ä" I have  mssql-server and mssql-tools installed on Ubuntu (Linux). When I try to export data with the bcp command using the following command-line:

bcp DBname.dbo.Täble_Name out Täble_Name -c -k  -S127.0.0.1 -Usa -PpassWord -r ~

I get this error:

  SQLState = 37000, NativeError = 102
  Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near '�'.

The � is ä.

If I surround the Täble_Name with square-brackets:

 bcp DBname.dbo.[Täble_Name] out Täble_Name -c -k  -S127.0.0.1 -Usa -PpassWord -r ~

I get this error on the object name:

  SQLState = S0002, NativeError = 208
  Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid object name 'DBname.dbo.Täble_Name'.

I went further and added single-quotes '' along with the -q option (which enables Quoted Identifiers):

bcp 'DBname.dbo.[Täble_Name]' out Täble_Name -c -k  -S127.0.0.1 -Usa -PpassWord -r ~ -q

The error becomes :

  SQLState = S0002, NativeError = 208
  Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid object name 'DBname.dbo.T�ble_Name'.

NB: the command works perfectly with table names without this special character ä .

I believe that this is an encoding issue between the shell and bcp / SQL Server. SQL Server expects UTF-16 Little Endian, but Linux isn't using that. The default for my Linux VM is UTF-8 via en_GB.UTF-8.

<TL;DR> Use the "queryout" bcp command and specify "SELECT * FROM ..." instead of using the "out" command and simply supplying a table name.

What follows is my testing...

I got the list of available locales / encodings using:

$ locale -a

returned:

C
C.UTF-8
en_AG
en_AG.utf8
en_AU.utf8
en_BW.utf8
en_CA.utf8
en_DK.utf8
en_GB.utf8
en_HK.utf8
en_IE.utf8
en_IN
en_IN.utf8
en_NG
en_NG.utf8
en_NZ.utf8
en_PH.utf8
en_SG.utf8
en_US.utf8
en_ZA.utf8
en_ZM
en_ZM.utf8
en_ZW.utf8
POSIX

I tried several of those options by setting:

$ export LC_CTYPE=C.UTF-8

and then trying again using:

$ export LC_ALL=C.UTF-8

Nothing seemed to make a difference. And each time I tried with various combinations of square brackets without -q and then with -q, and then no square brackets both with and without -q.

I even tried injecting the bytes that would equate to the UTF-16 LE character of ä via $'\xe4\x00' and even $'\xe4'$'\x00', but no improvements.

HOWEVER,

what did work was changing the bcp command from out to instead be queryout, and then changing the table name to be part of a SELECT statement (I removed the -r ~ switch only here to make the command line not scroll horizontally, but it was in my testing). I created the table in [tempdb] and ran the following:

bcp "SELECT * FROM tempdb.dbo.[Täble_Name]" queryout tab -c -k -S127.0.0.1 -Usa -Ppass

No problems there. But interestingly enough, I changed the accented ä to a non-accented a:

bcp "SELECT * FROM tempdb.dbo.[Table_Name]" queryout tab -c -k  -S127.0.0.1 -Usa -Ppass

and received the following error:

  SQLState = S1000, NativeError = 0
  Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to resolve column level collations

That is an error from bcp and must be referring to the meta-data of tempdb since the only column in my test table uses the INT datatype.

Now, my instance-level Collation is accent-sensitive, so I wasn't really expecting the non-accented a to work (though I did expect an "invalid object" error). So, in order to test accent-insensitivity, I created a new Database with a Collation of Latin1_General_100_CI_AI_KS_WS_CS, create that same table in the new DB, and added a few rows. I then ran the following two tests:

bcp "SELECT * FROM ImportTest.dbo.[Täble_Name]" queryout tab -c -k  -S127.0.0.1 -Usa -Ppass

bcp "SELECT * FROM ImportTest.dbo.[Table_Name]" queryout tab -c -k  -S127.0.0.1 -Usa -Ppass

and both worked!

Going back to the initial bcp command of just specifying the table name instead of a query, I was able to get ImportTest.dbo.[Table_Name] and ImportTest.dbo.Table_Name to work. However, I was still not able to get any combination of ImportTest.dbo.[Täble_Name] to work; all variations got the same errors as before.


Causing ORA errors in a program using JDBC. How can I find what's causing them? So I'm building a java app to interface with the company's database. Whenever it causes an ORA error, it just prints something like the following to system output:

ORA-00911: invalid character

Is there some way I can see what's causing it? Maybe some SQL statement I can use to see the last 10 errors caused on a table or something? I have SQLDeveloper if that might be useful.

Either set tracing on client(jdbc) side. Or you can create system trigger ON SYSTEMERROR see this, but this requires DBA privs.


Spatial data modelling tools Is there anything like MySQL workbench for PostGIS? Something that supports SQL output and geometry types would be nice.

CLARIFICATION:

I am trying to implement the Land Administration Domain Model and all I got are the specs ( UML diagrams). I need a tool that can create the models and output code that would create the tables. It needs to support geometry types as the LADM deals with parcels.

You could try GRASS or QGIS., open-source GIS applications.  You might be able to get what you want from one of them.

EDIT: I see you're after a data modelling tool with support for spatial data types.  I can't see any specific reference with basic google-fu.  You may be stuck with building your database model using CREATE TABLE statements and then reverse engineering them into a modelling tool for documentation.

If you have Visio Professional, the database modelling feature of that has a passably good database reverse engineering feature that will work OK with anything that supports an ODBC driver.

Another option might be to get an extensible tool such as Sparx Enterprise Architect and frig the metamodel.  However, this might be more work than building your database by hand.


MySQL is using swap memory while I have plenty of free RAM I have a 6 GB buffer pool size for my database; however, mysqld process is moving to swap while there is plenty of RAM available. I think pool size and pages along with pool of reads and pool of read requests should be tuned to avoid such situation.

Below is my current configuration:

[fuad@damra mysql]$ free -m
         total       used       free     shared    buffers     cached
Mem:          7872       7138        734          0        304        678
-/+ buffers/cache:       6155       1717
Swap:        32767        456      32311

mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool%';
+---------------------------------------+-------------+
| Variable_name                         | Value       |
+---------------------------------------+-------------+
| Innodb_buffer_pool_dump_status        | not started |
| Innodb_buffer_pool_load_status        | not started |
| Innodb_buffer_pool_pages_data         | 316153      |
| Innodb_buffer_pool_bytes_data         | 5179850752  |
| Innodb_buffer_pool_pages_dirty        | 20          |
| Innodb_buffer_pool_bytes_dirty        | 327680      |
| Innodb_buffer_pool_pages_flushed      | 51645307    |
| Innodb_buffer_pool_pages_free         | 75279       |
| Innodb_buffer_pool_pages_misc         | 1776        |
| Innodb_buffer_pool_pages_total        | 393208      |
| Innodb_buffer_pool_read_ahead_rnd     | 0           |
| Innodb_buffer_pool_read_ahead         | 0           |
| Innodb_buffer_pool_read_ahead_evicted | 0           |
| Innodb_buffer_pool_read_requests      | 26268346860 |
| Innodb_buffer_pool_reads              | 496         |
| Innodb_buffer_pool_wait_free          | 0           |
| Innodb_buffer_pool_write_requests     | 207971337   |
+---------------------------------------+-------------+

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF            |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | OFF            |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 6442450944     |
+-------------------------------------+----------------+

Do I need to tune these values to achieve better results and to not let MySQL switch to swap disk unless RAM is fully utilized?

This is a dedicated database server holding two MySQL schemas. Both schemas are serving the same application (one schema is to store statistics).

I can't change swappiness as this is not an option for the VMware administrator. As for RAM, I have 734+678 (cached memory can be re-used).

I have Java installed on this server to serve the JDBC driver between my application and MySQL.

Red Hat Enterprise Linux Server release 6.7 (Santiago).

Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total shows about 15% free. Given the low number of dirty pages and the very low Innodb_buffer_pool_reads vs Innodb_buffer_pool_reads_requests you probably could trim your buffer_pool size.

Innodb_buffer_pool_pages_free == 0 is perfectly acceptable if you aren't reading from disk frequently (low Innodb_buffer_pool_reads).


SQL Azure not using available memory We are using SQL Azure Premium P1 which comes with 8GB ram and 150 IOPS. For a couple of hours, we have been seeing timeouts on SQL queries (even fairly simple queries take time).

In logs for SQL Azure we have seen that our physical read IOPS has increased from a steady 5 IOPS to around 130 in a matter of minutes but our actual production in the system relying on SQL Azure has not increased.

The minute it jumped to 130 IOPS the log shows that the database memory usage decreased from a very steady 4.8GB to around 1GB

I think it makes sense that If memory usage drops, physical reads increase.

I have looked at queries being executed on the server but see nothing out of the ordinary except for the slower response.

My question is whether there any reasons why SQL Azure (or SQL server for that matter) would drop things from memory and favor disk IO, even if there is plenty memory available.

Turns out that even if you pay for reserved resurces and are guaranteed 8GB of RAM, you aren't really guaranteed it. And it's not really reserved for you. In our case it turned out that another Windows Azure customer was using too much resources and our database got 1GB instead of 8.


Relation between tables This question may be too easy to ask, but I can't understand how to do it.
I am new in SQL.
I have two tables.

Table I
book_id (AI & Primary Key)
book_name
book_year

Table II

writer_id (AI & Primary Key)
writer_name
writer_bio

I want to create a relation between book_id and writer_id. When I add a new book, I don't want to store the writer's name for the every single time, I want to connect it to a specific writer_id. That's how relational databases work, right?
A writer could be written more than one book, but a book also can be written by more than one writers. How can I make the relation?
I also appreciate any kind of link to understand how this works.

It seems to me that in a case like this you need a linking table that will allow for that many to many relationship.  Something like

Create Table tblBookWriter
(tblBookWriter_ID int
, Book_id --(FK to Book table-Book_Id)
, Writer_id --(FK to Writer table-Writer_Id)
);

With this you can add have a book with as many writers as it needs and a writer can have authored as many books as is needed.  Something like that should get it for you anyway.

If you do a web search for "database many to many linking table" you will see all kinds of results.  The first is a wiki link for a Junction Table


Update columns values using select from external database and table in MariaDB trigger I have two databases pdone and veevan. pdone is a kind of master and veevan keep on continue sync against an external sources. I need to perform a internal sync between veevan and pdone so I am using triggers for achieve this. Now at pdone db I have a table targets and at veevan I have a table address_vod__c. Every time a new record is inserted on address_vod__c or every time a record is updated I need to update the row at pdone.targets so this is how the process should run:

INSERT a new record on veevan.address_vod__c, then SELECT the current inserted row (the values) find the match on pdone.targets and UPDATE columns
UPDATE a record on veevan.address_vod__c, then SELECT the current updated row (the values) find the match on pdone.targets and UPDATE columns

This is what I have so far:

USE `veevan`;

DELIMITER $$

DROP TRIGGER IF EXISTS veevan.account_ai$$
USE `veevan`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `veevan`.`address_vod__c_ai` AFTER INSERT ON `address_vod__c` FOR EACH ROW
BEGIN
    DECLARE vCount INT(6) DEFAULT 0;
    SET vCount=(SELECT COUNT(*) FROM pdone.targets WHERE targets.veeva_account_id=NEW.account_vod__c);
    IF (vCount>0) THEN
        SET @TargetLastMod=(SELECT targets.lastSyncAt FROM pdone.targets WHERE targets.veeva_account_id=NEW.account_vod__c LIMIT 0,1);

        UPDATE pdone.targets
        SET
        `address1` = avc.address1,
        `address2` = avc.address2,
        `city` = avc.city,
        `state` = avc.state,
        `zip` = avc.zip,
        `phone` = avc.phone,
        `fax` = avc.fax,
        `lastSyncAt` = NOW(),
        `updatedAt` = NOW()
        FROM (
            SELECT
            address_vod__c.name AS `address1`,
            address_vod__c.address_line_2_vod__c AS `address2`,
            address_vod__c.city_vod__c AS `city`,
            address_vod__c.state_vod__c AS `state`,
            address_vod__c.zip_vod__c AS `zip`,
            address_vod__c.phone_vod__c AS `phone`,
            address_vod__c.fax_vod__c AS `fax`
            FROM
                address_vod__c avc
            WHERE
                address_vod__c.account_vod__c = NEW.account_vod__c
                    AND external_id_vod__c IS NOT NULL
                    AND address_vod__c.primary_vod__c = 1
                    AND account.recordtypeid COLLATE utf8_general_ci IN (SELECT id FROM veevan.recordtype WHERE name = 'Professional_vod')
        )
        WHERE
            targets.veeva_account_id=NEW.account_vod__c;
    END IF;
END$$
DELIMITER ;

But I don't know if the UPDATE using that SELECT will works and that is my doubt, how do I achieve an UPDATE from SELECT? Any time I try to run that SQL on I got this error:

  Error Code: 1064. You have an error in your SQL syntax; check the
  manual that corresponds to your MariaDB server version for the right
  syntax to use near 'FROM     SELECT              address_vod__c.name
  AS address1,             addr' at line 19

Can any give me some feedback and|or help on this?

veeva_account_id is the PK on pdone.targets and account_vod__c is the FK (kind of since that DB has not real relationships) on veevan.address_vod__c.

Your UPDATE statement is wrong. I've edited your TRIGGER and the modifications I did and errors I saw:

Errors:

You tried to drop a different TRIGGER that the one you're going to create. DROP TRIGGER IF EXISTS veevan.account_ai$$.
Why do you need the variable @TargetLastMod?. You didn't use it in the whole trigger.

Modifications:

I edited the DROP TRIGGER with veevan.address_vod__c_ai instead veevan.account_ai.
I used a JOIN with the same fields of your SELECT, with (address_vod__c.account_vod__c = NEW.account_vod__c AND  targets.veeva_account_id=NEW.account_vod__c) in the UPDATE.

TRIGGER:

USE `veevan`;
DELIMITER $$
DROP TRIGGER IF EXISTS veevan.address_vod__c_ai$$
USE `veeva_new`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `veevan`.`address_vod__c_ai` AFTER INSERT ON `address_vod__c` FOR EACH ROW
BEGIN
    DECLARE vCount INT(6) DEFAULT 0;
    SET vCount=(SELECT COUNT(*) FROM pdone.targets WHERE targets.veeva_account_id=NEW.account_vod__c);
    IF (vCount>0) THEN
        SET @TargetLastMod=(SELECT targets.lastSyncAt FROM pdone.targets WHERE targets.veeva_account_id=NEW.account_vod__c LIMIT 0,1);

        UPDATE pdone.targets
        JOIN veevan.address_vod__c AS avc ON (address_vod__c.account_vod__c = NEW.account_vod__c AND targets.veeva_account_id=NEW.account_vod__c)
        SET
        `address1` = avc.address1,
        `address2` = avc.address2,
        `city` = avc.city,
        `state` = avc.state,
        `zip` = avc.zip,
        `phone` = avc.phone,
        `fax` = avc.fax,
        `lastSyncAt` = NOW(),
        `updatedAt` = NOW()
        WHERE address_vod__c.account_vod__c = NEW.account_vod__c
            AND external_id_vod__c IS NOT NULL
            AND address_vod__c.primary_vod__c = 1
            AND account.recordtypeid COLLATE utf8_general_ci IN (SELECT id FROM veevan.recordtype WHERE name = 'Professional_vod');

    END IF;
END$$
DELIMITER ;

Try it!


SQL Server 2008R2 - How to prevent a role from creating or altering views within a database? I have a situation on SQL Server 2008 R2 where I am trying to prevent users of an AD group from being able to create new views, or alter any existing views, within a particular database. This AD group is mapped to a role in the database, so I'm working on tweaking the permissions that role has.

From the documentation and Management Studio it's straightforward to deny the CREATE VIEW permission at the database level. I am also familiar with denying the alter command at the schema level, but that would prevent the altering of tables as well as views. I'm hoping to just deny the ALTER VIEW permission at the database or schema level.

Is this possible?

CREATE VIEW encapsulates creation and alteration.  Here's a sample script using a login named test mapped to the current database:

grant create table to test
grant create view to test

execute as user='test';

--succeed
create table t ( Id int );
create view v as select * from t;

revert;

deny create view to test;

execute as user='test';

--fail:  permissions error
alter view v as select * from t;

revert;

In the first step we grant create table & view to test.  Executing as test, we're able to create a table and a view.  Then, we revert back to the real account and deny create view for the test user.  When we try to alter the existing view, we get a permissions error:  Cannot alter the view 'v', because it does not exist or you do not have permission.


Created database does not appear in database list I created a new PostgreSQL 9.5 database as follows:

[root@myhost pgsql]# sudo -u postgres initdb -D /var/lib/pgsql/test
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_CA.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /var/lib/pgsql/test ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/pgsql/test -l logfile start

If I then ask psql to list the databases, but my 'test' database is missing:

[root@myhost pgsql]# sudo -u postgres psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 |
 template0 | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

Can someone explain what's wrong?  Do I have to register my new test database with PostgreSQL somehow?

I also tried to backup the new database but that too fails:

sudo -u postgres psql -d /var/lib/pgsql/test -c "SELECT pg_start_backup('test');"
psql: FATAL:  database "/var/lib/pgsql/test" does not exist

You have two commands confused:

initdb is used once to initialize a DB environment directory structure. That does not create a new database.
createdb is used for each DB you want to create.

When you ran your initdb you were just initializing the folder /var/lib/pgsql/test to hold ALL future databases, but you did not actually create ANY database.

  initdb creates a new PostgreSQL database cluster. A database cluster is a collection of databases that are managed by a single server instance.

Reference: initdb (PostgreSQL Documentation)

You did not specify if this is a test machine or planned for prod, but you probably should use the service command to run the initdb and after that sudo to the postgres user to run the createdb. Often in *nix systems the installer setups up PGDATA variable for you so you don't have to specify the directory. There are some installers that even run the initdb for you; you did not specify the OS, so no idea of how your OS / installer handles setup/installation.

So, you may do something like:

sudo service postgresql initdb
sudo su - postgres
createdb test
psql -c "\l test"


Creating index on large MySQL table taking forever My table has 981 million rows and is about 30GB large. It is six columns: ID, Ticker, Date, Time, Price, Volume. The data was in text files and using LOAD DATA INFILE was actually quite painless and only took a couple hours. However, each select statement takes 5 minutes now because of the number of rows.

I want to create an index on Ticker, Date, Time. I used the following command:

create index tickerdatetime on kibot (Ticker,Date,Time);

It is now been over eight hours and it is still working. My machine is ok (PC, 3.40GHz, quad core, 12GB RAM). Two questions:

Is there a better (ie faster) way of doing this?
Is there any way I can know what percentage is done? Am I 10% done or 80% done?

Yes, there is a safe way (not faster though) to alter schemas if you use tool. This does not require any downtime.  The one we use everyday on our production is to use

pt-online-schema-change
this tool will show you the progress of the SQL.

I hope this helps


What is an "ACCESS METHOD"? In the context of the CREATE ACCESS METHOD DDL in PostgreSQL,

CREATE ACCESS METHOD name
    TYPE access_method_type
    HANDLER handler_function

What is a a high level description of what an "access method" is and what they are used for?

Using CREATE ACCESS METHOD in postgres allows you to define a function to handle (or 'access') a type of index.

It exists to allow you to define new ways for postgres to understand a mapping from the data key values to the actual TID that identifies the related value.

Without an understanding of how relational databases work, it's probably not something you'll ever directly deal with.


Is it possible to specify Active Directory username and password in connection string? My organization uses Active Directory to control access to our databases. Building web applications and as part of our CI process I would like to specify the credentials in the connection string.

Is it possible to pass AD credentials in a connection string or do I need a standard SQL account for these items?

You can have your app impersonate a windows user and do the connection that way. Example for ASP.net app.


Generate unique random string to be used as value for a field inside newly created rows in PostgreSQL I have this statement which generate a random string:

SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '')
FROM generate_series(1, 20);

My question, how to implement this inside a function which would assign the value of the above statement automatically to a field named url_prefix when new records are created (on INSERT's) ?

You can create a function and use it as the default for the column:

create function f() returns text language sql as $$
  SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '')
  FROM generate_series(1, 20)
$$;

create table foo(id serial primary key, url_prefix text not null default f());

insert into foo default values;

select * from foo;

id | url_prefix
-: | :-------------------
 1 | iByDaJ2h4JHi3dI3uBoy

db<>fiddle here


What is the best relationship model for high performance? I want to model a relationship between Customer and Transaction.

However, because Customer is only described by a unique string and no extra info, I have two possibilities to implement it in the database:

Customer(Id, NameString) --> Transaction(CustomerId, OtherDetails...)

or

Transaction(CustomerNameAsString, OtherDetails...)

I have to manage a few hundreds transactions per sec, and store a several million transaction in the table.

Each new transaction INSERT is done after 1 or 2 SELECT queries on the table, like SELECT count transactions (customer, lastMonth).

Is there a big performance penalty on one model or another?

In other words, what is the faster way?  Should I use a join and an integer index, or a string index with no join?

I am using Oracle.

You'll probably want to keep your transaction table as small as possible, so use an integer column as a foreign key to your customer table.


Column Name in separate table SQL Server I am working with an accounting package backend.

In SQL Server 2012 there is a custom fields table with Values (see sample below) and another table with custom header names (see sample below).

I have created a query that joins the 2 tables multiple times and transposes data into columns and uses aliases to name the column.  It was a tedious task with over 50 custom fields.

There have been some field changes and Executives are talking about adding even more fields.

I am looking for a better solution to dynamically accommodate field name changes or adds.  Any suggestions will be appreciated.

Values Table:

Company,JobNumber,FieldNumber,Information
'01',12345,'01','Value1'
'01',12345,'02','Value2'
...
'01',12345,'50','Value50'
'02',12345,'01','Value2_1'

Header Name Table:

Company,FieldNumber,Description
'01','01','ColumnName1'
'01','02','ColumnName2'
'01','03','ColumnName3'
...
'01','50','ColumnName50'
'02','01','ColumnName2_1'

The query below will dynamically Name ColumnName1 and show values in Column for Company 1.

Declare @CompanyCode nvarchar(2)
SET @CompanyCode = '01'
Declare @CustomInfo1 varchar(20)
Set @CustomerInfo1 = (Select TOP 1 H.Description
                      from dbo.HeaderTable AS H
                      where H.CompanyCode = @CompanyCode and H.FieldNumber = '01')

DECLARE @sql nvarchar(2000)

Set @sql = 'SELECT V.JobNumber, V.Information AS [' + @CustomInfo1 + ']
FROM         dbo.ValuesTable AS V
Where V.CompanyCode = ' + '''' + @CompanyCode + '''' + 'AND V.Fieldnumber =   ''01'''

EXEC sp_executesql @sql

Results:

Company,JobNumber,ColumnName1
'01',12345,'Value1'

Desired Results:

Company,JobNumber,ColumnName1,ColumnName2,,...,ColumnName50
'01',12345,'Value1','Value2',...,'Value50'
'01',12346,'Value1a','Value2a',...,'Value50a'
'02',12345,'Value2_1','Value2_2',...,'Value2_50'
'03',12346,'Value3_1','Value3_2',...,'Value3_50'

The part I am struggling with is adding the next 49 columns. The ColumnNames could be potentially different for the other companies. I could copy this code 50 times with 50 @CustomerInfo 1-50 and Change FieldNumber from 01,02,03...50, then repeat again for company 2-4 with a Select query1, query2, query3, etc.  I cannot use a Union to connect each Company since Column Names may be different.

Maybe some kind of loop?

Select Loop(Query Above for Company1), From Table
Union
Select Loop(Query Company2), From Table
Union
Select Loop(Query Company3), From Table

Ultimately I would like use a Stored Procedure or SSIS to create a table, then update, insert, delete to maintain data.

I consider myself between beginner and intermediate in SQL so please go easy on me.

After @Duffy Answer my final query is

DECLARE @SQL NVARCHAR(MAX)
DECLARE @Columns NVARCHAR(MAX)
DECLARE @Company NVARCHAR(5) = '01'

SET @Columns = STUFF( (SELECT ',['+H.description+']' AS [data()]
                    FROM dbo.Header H
                    WHERE H.Company = @Company
                    ORDER BY H.FieldNumber
                    FOR XML PATH('')),1,1,'')

SET @SQL = '
SELECT Company,JobNumber,'+@Columns+'
FROM
(
    SELECT h.Company,RV.jobNumber,RV.information,h.description
    FROM dbo.Header h
            INNER JOIN dbo.RowValues RV
                ON RV.FieldNumber = h.FieldNumber
                   AND RV.Company = h.Company
    WHERE H.Company = ' + '''' + @Company + '''' + '
) as Data
PIVOT
(
    MAX(information) FOR [description] IN ('+@Columns+')
) as p
ORDER BY JobNumber ASC'

EXEC sp_executesql @sql;

The only change I needed to make was adding the single quotes in the Dynamic SQL statement.  Otherwise, SQL was treating the Variable '01' as an int type even though nvarchar was Declared.  Thanks again @Duffy

What you're looking for is Dynamic SQL Pivots. A PIVOT will turn row values into columns based on an aggregate, however you need to define the column names as part of the PIVOT, luckily we can do that on the fly with dynamic SQL. The following should generate the result set you want for a particular company (replace rowValues with your table name):

DECLARE @SQL NVARCHAR(MAX)
DECLARE @Columns NVARCHAR(MAX)
DECLARE @Company NVARCHAR(5) = '01'

SET @Columns = STUFF( (SELECT ',['+H.description+']' AS [data()]
                        FROM dbo.Headers H
                        WHERE H.company = @Company
                        ORDER BY H.fieldNumber
                        FOR XML PATH('')),1,1,'')

SET @SQL = '
SELECT company,jobNumber,'+@Columns+'
FROM
(
    SELECT h.company,RV.jobNumber,RV.information,h.description
    FROM Headers h
            INNER JOIN dbo.RowValues RV
                ON RV.fieldNumber = h.fieldNumber
                   AND RV.company = h.company
    WHERE h.company = '+@Company+'
) as Data
PIVOT
(
    MAX(information) FOR [description] IN ('+@Columns+')
) as p
ORDER BY jobNumber ASC'

EXEC sp_executesql @sql;

Unfortunately due to the possibility that different companies will have different columns there is no reliable way to combine all the the possible sets the above query could generate. Depending on how you want to use this query the easy option is to loop through all the companies and call an SP that contains the above, allowing you to output each company as a separate SELECT. Or you can do something with your SSIS package to output each one into a file.

If you want to insert the data into a table matching the calculated schema you can do a SELECT INSERT and a bit more dynamic SQL to get the data where it needs to go.


Secondary and Arbiter stuck in Startup I'm using MongoDB version 3.0.0. I'm trying to setup mongodb replication on our machine. Replication was initially setup but due to some changes on the VM the entire thing crashed. When I tried to set it up again, the secondary and Arbiter became stuck in StartUp Mode.

In the mongoDB conf file I have set

replSet=ReplicaSet1

I added 2 machines using the command

rs.add("10.235.96.12:27017")
rs.add("10.235.96.12:27017")

But after this on the primary when I do a rs.status() secondary and arbiter are still shown in StartUp

ReplicaSet1:PRIMARY> rs.status()
{
    "set" : "ReplicaSet1",
    "date" : ISODate("2015-07-31T04:45:57.260Z"),
    "myState" : 1,
    "members" : [
            {
                    "_id" : 0,
                    "name" : "BOSPROD9:27017",
                    "health" : 1,
                    "state" : 1,
                    "stateStr" : "PRIMARY",
                    "uptime" : 63104,
                    "optime" : Timestamp(1438257913, 1),
                    "optimeDate" : ISODate("2015-07-30T12:05:13Z"),
                    "electionTime" : Timestamp(1438254975, 2),
                    "electionDate" : ISODate("2015-07-30T11:16:15Z"),
                    "configVersion" : 7,
                    "self" : true
            },
            {
                    "_id" : 1,
                    "name" : "10.235.96.12:27017",
                    "health" : 1,
                    "state" : 0,
                    "stateStr" : "STARTUP",
                    "uptime" : 62663,
                    "optime" : Timestamp(0, 0),
                    "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
                    "lastHeartbeat" : ISODate("2015-07-31T04:45:56.520Z"),
                    "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
                    "pingMs" : 0,
                    "configVersion" : -2
            },
            {
                    "_id" : 2,
                    "name" : "10.235.96.13:27017",
                    "health" : 1,
                    "state" : 0,
                    "stateStr" : "STARTUP",
                    "uptime" : 60043,
                    "lastHeartbeat" : ISODate("2015-07-31T04:45:55.786Z"),
                    "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
                    "pingMs" : 0,
                    "configVersion" : -2
            }
    ],
    "ok" : 1
}

When I try to do a rs.conf on the Secondary or Arbiter I get the message

2015-07-31T05:23:07.927+0000 E QUERY    Error: Could not retrieve replica set config: {
    "info" : "run rs.initiate(...) if not yet done for the set",
    "ok" : 0,
    "errmsg" : "no replset config has been received",
    "code" : 94
}

I did try multiple times to clean all the local.0, local.1 files in all the machines and also removed all the journal files from the journal folder as well. Still I'm getting the same issue. Can someone tell me what is it I'm doing wrong here?

I have set replset on all the members. Tried starting in standalone mode and it works.

From "BOSPROD9", try to connect with the mongoshell to the other serves:

$ mongo --host 10.235.96.12 --port 27017

$ mongo --host 10.235.96.13 --port 27017

(Telnet is not the same.) If this doesn't work, it might be firewall or BindIP.

Check bind_ip (should be 0.0.0.0, change in mongodb.conf is it's 127.0.0.1):

$ netstat -nap | grep :27017 | grep LISTEN
tcp        0      0 0.0.0.0:27018           0.0.0.0:*               LISTEN      -

Try to look at the log-files on 10.235.96.12 and 10.235.96.13, why they are stuck. Did they receive the configuration?

Try to reconfigure this way:

mongo> var cfg = {_id:"ReplicaSet1",members:[{_id:0, host:"BOSPROD9:27017"},{_id:1, host:"10.235.96.12:27017"},{_id:2, host:"10.235.96.13:27017",arbiterOnly:true}]};
mongo> rs.reconfig(cfg);

Solution:

Use hostnames instead of ip's: give all servers a hostname, update the hosts-files and use hostnames in the rs-configuration. It seems that mongodb advices not to use ip addresses, but hostnames.


Assembly 'EDCLR' references assembly 'system.servicemodel, version=4.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089 error I'm try to create clr function in c# with this code:

[SqlFunction]
        public static string Decrypt(string Input)
        {
            var test = "";
            var url = "http://www.url/feed/";
            using (XmlReader reader = XmlReader.Create(url))
            {
                SyndicationFeed feed = SyndicationFeed.Load(reader);
                //MessageBox.Show("Title:" + feed.Title.Text);
                //MessageBox.Show("Uri:" + feed.Links[0].Uri);
                foreach (SyndicationItem item in feed.Items)
                {
                    test=item.Title.Text;
                }
            }
            return test;
        }

when try add that to sql server assembly with this script:

create assembly behzad
from 'd:\EDCLR.dll'

i get this error:

  Assembly 'EDCLR' references assembly 'system.servicemodel,
  version=4.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.',
  which is not present in the current database. SQL Server attempted to
  locate and automatically load the referenced assembly from the same
  location where referring assembly came from, but that operation has
  failed (reason: 2(The system cannot find the file specified.)). Please
  load the referenced assembly into the current database and retry your
  request.

how can i solve that?thanks.

There are two problems here:

ServiceModel is not a supported .NET Framework Library for SQLCLR. Meaning, it is not included in SQL Server's CLR host. In such cases, one needs to manually load, via CREATE ASSEMBLY, the Assembly. BUT,
Starting in SQL Server 2012, you cannot reference / load ServiceModel as that version is the first to be linked to CLR v 4.0 in which ServiceModel became a mixed mode Assembly. Only pure MSIL Assemblies are allowed.

You will need to find a different way to accomplish this. One way is to use HttpWebRequest and HttpWebResponse.

This issue has been documented on Stack Overflow here:

(Unfixable) Assembly exists on SQL Server 2014 but it claims it doesn't have it
Register CLR function (WCF based) in SQL Server 2012
SQL CLR - Migration from 2008 R2 to 2012.

More info here:

SQL Server: "CREATE ASSEMBLY for assembly 'Test' failed because assembly 'Test' is malformed or not a pure .NET assembly."
SQL Server custom CLR fails with error "Could not load file or assembly or one of its dependencies. The system cannot find the file specified."


How do I report progress of a long running transaction in MySQL? I have this transaction in a stored procedure:

...
START TRANSACTION;

WHILE @Codes > 0 DO

    EXECUTE qry_rand_stmt;
    EXECUTE qry_insert_stmt;

    SET @Codes = @Codes - ROW_COUNT();

END WHILE;

COMMIT;
...

This could be inserting anywhere up to 1M unique rows taking up to 10 minutes which is fine and dandy but I need a way to report back its progress.

I would normally add an if statement that would run a query to update a job table of its progress every 1000 inserts but because this is in a transaction the update wouldn't appear if queried externally until the entire transaction is finished which defeats the purpose of having a status as you would only see when the job started and finished.

I have even thought of committing every 1000 inserts so I could report progress that way but then I lose the ability to roll back all inserts if something goes wrong later, or if the job is canceled before it is finished.

So, is there a way to run an update query inside a transaction that doesn't become part of the transaction and is written to the table/row with immediate effect?

If not, do you have any workarounds?

There are some caveats that apply to mixing transactional and non-transactional tables in the same query, but in this case, you're actually doing it because the side effects are desirable... so I see no problem with the following suggestion:

The MEMORY storage engine seems like a good fit for this application.  It's non-transactional, so your "status" inserts/updates would be visible immediately to other threads.

Data stored in a MEMORY table persists until the table is truncated or the rows are deleted, or until the server is restarted.  In a replication environment, a restarted master will also direct the slaves to empty their copy of a memory table when the master restarts, since the master's table will start empty.

Also, if you're doing this in a replication environment, a binlog_format of MIXED or ROW would likely be best, since those formats allow more deterministic replication, and determinism becomes more tricky when replicating a query with transactional and non-transactional tables.


How to show rows with 0 without any records in database I have a lot of record in my database and for data analysis, I need to show count of records for each day including days when I do not have any record, so result is 0.

For example I have these records (SQL Fiddle):

CREATE TABLE Tabs
    (`timestamp` varchar(10), `id` int)
;

INSERT INTO Tabs
    (`timestamp`, `id`)
VALUES
    ('2015-01-01', 1),
    ('2015-01-01', 2),
    ('2015-01-02', 3),
    ('2015-01-02', 4),
    ('2015-01-02', 5),
    ('2015-01-04', 6),
    ('2015-01-05', 7),
    ('2015-01-05', 8)
;

When I use this query:

SELECT `timestamp`, COUNT(*)
FROM Tabs
GROUP BY `timestamp`

I get ths result:

timestamp   COUNT(*)
2015-01-01  2
2015-01-02  3
2015-01-04  1
2015-01-05  2

But I need 2015-01-03 too with 0, so correct output for me is:

timestamp   COUNT(*)
2015-01-01  2
2015-01-02  3
2015-01-02  0
2015-01-04  1
2015-01-05  2

How can I realize it please?

I suppose you know the date_start and date_end and you won't work on more than 1000 days although it can be extended.

This example works with these values:

SET @date_start := CAST('20150101' as date);
SET @date_end := CAST('20150108' as date);

Below queries return each day between @date_start (2015-01-01) and @date_end(2015-01-08). This is similar to a calendar. The full list of days can then be LEFT JOIN to the list of available days in the table.

Using SELECT ... UNION ALL ...

SELECT @date := date_add(@date, INTERVAL 1 DAY) as date
FROM (
    SELECT 0 as 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
  )d10, (
    SELECT 0 as 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
  )d100, (
    SELECT 0 as 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
  ) d1000
  , (SELECT @date := date_add(@date_start, INTERVAL -1 DAY)) v
WHERE @date < @date_end
;

Output:

date
2015-01-01
2015-01-02
2015-01-03
2015-01-04
2015-01-05
2015-01-06
2015-01-07
2015-01-08

Each inner SELECT ... UNION ALL ... returns 10 rows from 0 to 9
This SELECT is used and CROSS JOIN 3 times and returns 1000 rows (10*10*10) from day 0 to day 999
Because 1 is added to @date, it means that it can return up to 1000 consecutives days
You can work on 10.000 days if you add one more SELECT ... UNION ALL ... and so on or on 100 if the third join is removed
You can then LEFT JOIN this query with your table

Using a view

It is probably easier to create a view with numbers from 0 to 9 and CROSS JOIN it 3 times or create 1 single view from 0 to 999:

CREATE VIEW viewDec As
  SELECT 0 as 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
;

SELECT @date := date_add(@date, INTERVAL 1 DAY) as date
FROM viewDec d10, viewDec d100, viewDec d1000
    , (SELECT @date := date_add(@date_start, INTERVAL -1 DAY)) v
WHERE @date < @date_end
;

You can work on 10.000 days by adding an extra viewDec d10000 ...

Using an existing table

It is also possible to replace the SELECT or VIEW by an existing table with enough rows (1000 in this example):

SELECT @date := date_add(@date, INTERVAL 1 DAY) as date
FROM (SELECT 1 as n FROM table_with_over_1000_rows LIMIT 1000) t
    , (SELECT @date := date_add(@date_start, INTERVAL -1 DAY)) v
WHERE @date < @date_end
;

Full query (using viewDec)

SELECT `date`, count(t.`id`)
FROM (
  SELECT @date := date_add(@date, INTERVAL 1 DAY) as date
  FROM viewDec d10, viewDec d100, viewDec d1000
    , (SELECT @date := date_add(@date_start, INTERVAL -1 DAY)) v
  WHERE @date < @date_end
) d
LEFT JOIN (
    SELECT `id`, `timestamp`
    FROM Tabs
    /* WHERE sensor = 10 */
) t
    ON d.`date` = t.`timestamp`
GROUP BY d.`date`
;

In this SQL Fiddle timestamp has been replaced by a type date column.

Output:

date        count(t.`id`)
2015-01-01  2
2015-01-02  3
2015-01-03  0
2015-01-04  1
2015-01-05  2
2015-01-06  0
2015-01-07  0
2015-01-08  0


Slow queries related to subqueries using aggregation Here's a simplified version of the queries I am running:

select
    ...
from
    editions
    join edition_events on edition_events.edition_id = editions.id
    join events on events.id = edition_events.event_id
    join (
        select
            event_id,
            array_agg(date_start),
            array_agg(date_end)
        from
            event_dates
        group by
            event_id
        ) as dates on dates.event_id = events.id
WHERE
    editions.id = ?;

These queries ran great with my test data (~1500 events).  Now that I'm starting to use a copy of the real data (~400,000 events, ~500,000 event_dates), my application is timing out before I get results back from the database.  I have added indexes on all relevant columns, which only helped a little bit.  I've narrowed down the problem to the subqueries that use aggregation.

Now the real version of these subqueries does things like time zone manipulation or a count instead of array_agg, so using a trigger to update a counter column is not practical in many instances.  The dates associated with events must be individual rows rather than stored as an array since they have a foreign key relationship with other tables.

What would be my best bet for improving performance here?  I understand that the subquery is being evaluated first, but I don't know which dates I actually need to look at until I've done the joins.  I have looked at lateral joins, but I am uncertain if they would help here (and most information I can find is along the lines of "look, new feature!").  Since I am still using 9.2, I don't want to ask to upgrade to 9.3+ if it won't actually solve my problem.

Your approach ("aggregate first, join later") generally performs best when you need to retrieve the whole table or large parts of it, or if you can restrict to relevant rows cheaply in the subquery - or while the aggregated table is small. See:

Query with LEFT JOIN not returning rows for count of 0

For only a few rows in the result, this gets disproportionately expensive for big tables, since the whole table (event_dates in your case) has to be processed. In these cases, it's better to revert the paradigm to "join first, aggregate later". The equivalent query would then be:
SELECT e.id
     , array_agg(ed.date_start) AS starts
     , array_agg(ed.date_end)   AS ends
     , ...
FROM   editions       e
JOIN   edition_events ee ON ee.edition_id = e.id
JOIN   events         ev ON ev.id = ee.event_id
JOIN   event_dates    ed ON ed.event_id = e.id
WHERE  e.id = ?
GROUP  BY e.id;

This should help with any version of Postgres.
Also using table aliases to simplify the syntax.
I don't see how LATERAL (Postgres 9.3+) could help this particular query. You would use it to reference a column from the left side of the join tree that you couldn't access on the same query level otherwise, often to produce multiple rows to join to. Neither is the case here. What you are thinking of can be implemented with plain old correlated subqueries, but those typically perform worse.
Don't take my word for it. Run a quick test with EXPLAIN ANALYZE for your case (with your real life data), then you know for sure.


dm_db_missing_index_details - does the order of equality_columns matter I've been investigating some databases using the dm_db_missing_index_group_stats DMV, and have a question about the equality_columns field in the dm_db_missing_index_details view.

MSDN says that this holds "a comma-separated list of columns that contribute to equality predicates of the form: table.column = constant value"

I have a table which is represented in three of the highest cost missing indexes, with the following equality columns:

client_product_id
client_id, client_product_id
client_id, client_product_id, status

Does the order of the columns in the second and third index groups matter? e.g. if I created an index on client_product_id, client_id, status, it seems that it should satisfy all three missing index cases, on the grounds that a query like client_id = 123 AND client_product_id = 456 should be satisfied equally regardless of whether client_product_id or client_id comes first in the index definition.

(obviously if it was an inequality predicate, this would not be the case)

In this case the single index you propose could be used to satisfy all three equality searches.  If any of the columns allows null values, it may matter which order the columns are in.

The optimizer should match the index regardless of the order in the query.  What is important that the equality conditions match the leading columns in the index.  The proposed index would not be good for an equality condition on client_id or stats but not including client_product_id.

In some cases the index may be used if the first field is referenced, but one of the subsequent values is skipped.  Depending on data distribution an optimizer may use a range scan on the proposed index to search for equality on client_product_id and status but not client_id.


Access 2013: Merging the same table-field with different data I have six tables with identical fields. the group was tasked to input the data with the same fields and I need to merge all into one dataset--- table and form.

create a view likes this:

    select * from table1
    union
    select * from table2
    union
    ...
    select * from table6


Execute SQL query after execute TRUNCATE in a table I have a table called fos_user_user where I have a default record "always". Since I'm testing all the time and sometimes I need to clean that table I'm asking if there is any way to run the following query any time I run the TRUNCATE command on the table:

INSERT INTO `fos_user_user` (`id`, `username`, `username_canonical`, `email`, `email_canonical`, `enabled`, `salt`, `password`, `last_login`, `locked`, `expired`, `expires_at`, `confirmation_token`, `password_requested_at`, `roles`, `credentials_expired`, `credentials_expire_at`, `created_at`, `updated_at`, `date_of_birth`, `firstname`, `lastname`, `website`, `biography`, `gender`, `locale`, `timezone`, `phone`, `facebook_uid`, `facebook_name`, `facebook_data`, `twitter_uid`, `twitter_name`, `twitter_data`, `gplus_uid`, `gplus_name`, `gplus_data`, `token`, `two_step_code`) VALUES
(1, 'admin', 'admin', 'admin@local.com', 'admin@local.com', 1, 'qd3bxv0cg34s0ow0owsso848k8g4g8s', 'w0PbwCKqCJmEzr7VewCJIq0M8t7+35wS4Tfbzj2LaRKNg9wNv8ic/1TY+y5BTJXoftMENobutsizDluVKM/R/Q==', '2014-02-25 02:36:56', 0, 0, NULL, NULL, NULL, 'a:1:{i:0;s:10:"ROLE_ADMIN";}', 0, NULL, '2014-02-13 02:36:09', '2014-02-25 02:36:56', NULL, NULL, NULL, NULL, NULL, 'u', NULL, NULL, NULL, NULL, NULL, 'null', NULL, NULL, 'null', NULL, NULL, 'null', NULL, NULL);

Any advice?

How about creating a stored procedure that includes your truncate command and the insert statement.

Since that insert statement only makes sense specifically after calling truncate on your fos_user_user table, you could just as well run 'exec .ufn_Truncate_fos_user' or something like that.


Surrogate key vs Natural key I have a table called devices. Most of the devices that will get stored in this table can be uniquely identified by their serial number and part number. But there are some device types that do not have serial number and part number assigned to them. Instead they can be uniquely identified by another field (internal id).

Should I create a surrogate key for this table or should I create a composite primary key (serial number, part number, internal id) and insert default values to the serial number and part number columns when they are not supplied? The device types that do not have part number and serial number now, will have the numbers assigned to them in the future releases (may be 5 years from now). Should I create a surrogate key or a composite key in this scenario? Or using the three unique attributes, should I create a hash in the program and use that as a surrogate key for the tables?

Use the surrogate key as the primary key for the moment. When natural keys become available, make them non-nullable unique constraints.

By the YAGNI principle, you should only code for "real-life" current requirements - a primary key that may (or may not) arrive in 5 years is not worth considering now!

Surrogate keys are very widely used in practice, much to the horror of purists: the rest of us just use them! See this Stack Overflow answer (as well as the rest of the thread).


Clustered Index vs Primary Key Performance I'm trying to tune a stored procedure which has a temp table used for matching (only retrieval, no updates or inserts). We're considering applying a clustered key to a temp table. We're also considering applying a primary key (clustered).

Aside from the unique/non-null constraint of a primary key, are there any differences performance wise between clustered indexes and primary keys in regards to looking up values in the temp table?

Thanks.

A clustered index will dictate the order of the data in the pages of your table. When your temp table is initially populated, the inserted data will be sorted or, if you're doing multiple inserts to populate, it's possible you will end up with some page fragmentation as the pages must split to accommodate additional data being written.

Assuming your temp table is populated in a single insert, and you have no page fragmentation, the next issue that may or may not affect your performance is the presence of a 4 byte "uniqueifier." Because the clustered key is used as a pointer to each row in the table, it must be unique for that purpose. So if you're using a non-unique clustered index, every nonclustered index (and your clustered index) is going to be 4 bytes wider than you've defined which will affect page density and IO required to perform the query. This will likely only be of very marginal impact as the uniquifier will only be added to duplicates.

The other factor that you need to consider is, how are you joining to this temp table. SQL Server supports 3 physical join operators; Merge, Loop, and Hash. Merge can yield the best performance in many situations.

  Unlike the nested loops join where the total cost may be proportional
  to the product of the number of rows in the input tables, with a merge
  join each table is read at most once and the total cost is
  proportional to the sum of the number of rows in the inputs.  Thus,
  merge join is often a better choice for larger inputs.

It is unlikely SQL will chose a merge operator unless both datasets being joined are sorted by the join criteria. There are other factors as well, of course, but if you can optimize your table for specific, common usage patters, all the better.

The second thing to consider is any other indexes you are going to place on that table since each nonclustered index will contain the keys specified (in the order specified) and the pointer to the clustered index to facilitate the lookup and retrieval of any fields not covered by the index. Again, a narrow clustered key will yield better density in your non-clustered indexes and thus better i/o performance for index scans and seeks. If your nonclustered indexes require many lookups to the clustered index either consider including those columns in your index (sacrificing page density for reduced lookups) or consider clustering on that criteria (if this is your primary usage pattern)

The biggest factor that would steer me towards using a primary key over a unique (or non-unique clustered index) would be the ability to create the index inline in the create table statement. Historically, I have found that the optimizer is less apt to use indexes that I created inside my stored procedure after the create table statement. It's easier to create a primary key inline with the create table statement. I'm not sure if it's possible to do this with a non-constraining index but you can create a unique clustered index inline.


Is there a need to index the short string code for a faster query? We have a large number of tables for setup forms.
In each form, there is a need to fill in the Code and the Description.

The Code consists of a varchar(10) datatype.

Now, the thing is, every time a user creates new data or updates the data, there is a need to check whether the Code conflicts with the others or not.

In LINQ, this code checking is inevitable:

string ExistingCode = "ITEM01";
int ExistingKey = 1;

var recordWithConflictedCode = (from a in db.MyTable
                               where a.Code == ExistingCode &&
                               a.Key != ExistingKey).ToList();

if(recordWithConflictedCode.Count >= 1)
    return BadRequest("Duplicated Code found");

Now, since this involves a string comparison on every process, I think it makes sense to index the column Code to make the query faster.

Is this a common practice for you guys?

Use the DBMS for its intended purpose; that is create a constraint on the table so that it fits the business rules.

Designing your table so it looks like:

USE tempdb;
CREATE TABLE dbo.MyTable
(
    [Key] int NOT NULL
    , Code varchar(10) NOT NULL
         CONSTRAINT uq_MyTable_Code
         UNIQUE
);

This will ensure every row inserted into MyTable has a unique Code column.  Now you can add a try ... catch statement around  your inserts in .Net code to inform the user when a duplicate is detected.

The SQL Server engine will automatically create a unique index on the table to support the constraint.  You could just manually create a unique index on the table to satisfy the same requirements; however adding it as a unique constraint in the way detailed above self-documents the business rule.


Show procedure code in MySQL I've tried to show the SQL code of a stored procedure called add_pixel:

mysql> SHOW PROCEDURE CODE add_pixel;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

I've made sure that the procedure exists:

mysql> CALL add_pixel();
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE pixels.add_pixel; expected 3, got 0

And SHOW CREATE PROCEDURE works:

mysql> SHOW CREATE PROCEDURE add_pixel;
+-----------+----------+------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+-----------+----------+------------------+----------------------+----------------------+--------------------+
| add_pixel |          | NULL             | utf8                 | utf8_general_ci      | utf8_unicode_ci    |
+-----------+----------+------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

and that my MySQL version supports `SHOW PROCEDURE (SHOW PROCEDURE CODE was added in MySQL 5.0.17).

How do I view the source SQL code of the procedure?

EDIT

Amazingly enough, INFORMATION_SCHEMA.ROUTINES  does not have a param-list column.

You can use mysql.proc. For example, looking at a procedure of mine:

SELECT * FROM mysql.proc WHERE name='run' \G
*************************** 1. row ***************************
                  db: common_schema
                name: run
                type: PROCEDURE
       specific_name: run
            language: SQL
     sql_data_access: MODIFIES_SQL_DATA
    is_deterministic: YES
       security_type: INVOKER
          param_list:
  in query_script text

             returns:
                body: main_body: begin
  if (LEFT(query_script, 1) in ('/', '\\')) and (LEFT(query_script, 2) != '/*') then
    begin
          declare query_script_file_name text;

      set query_script_file_name := query_script;
      set query_script := LOAD_FILE(query_script_file_name);
      if query_script is null then
        call throw(CONCAT('Cannot load script file: ', query_script_file_name));
      end if;
    end;
  end if;
  call _interpret(query_script, TRUE);
end
             definer: root@localhost
             created: 2012-08-17 17:24:46
            modified: 2012-08-17 17:24:46
            sql_mode:
             comment: Run given QueryScript text
character_set_client: utf8
collation_connection: utf8_general_ci
        db_collation: latin1_swedish_ci
           body_utf8: main_body: begin
  if (LEFT(query_script, 1) in ('/', '\')) and (LEFT(query_script, 2) != '/*') then
    begin
          declare query_script_file_name text;

      set query_script_file_name := query_script;
      set query_script := LOAD_FILE(query_script_file_name);
      if query_script is null then
        call throw(CONCAT('Cannot load script file: ', query_script_file_name));
      end if;
    end;
  end if;
  call _interpret(query_script, TRUE);
end

Look for the param_list column (I believe that was your main concern).
Also look for db, name, type.

=================

Alternatively, can use INFORMATION_SCHEMA.ROUTINES. For example, looking at a procedure of mine:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='run' \G
*************************** 1. row ***************************
       SPECIFIC_NAME: run
     ROUTINE_CATALOG: NULL
      ROUTINE_SCHEMA: common_schema
        ROUTINE_NAME: run
        ROUTINE_TYPE: PROCEDURE
      DTD_IDENTIFIER: NULL
        ROUTINE_BODY: SQL
  ROUTINE_DEFINITION: main_body: begin
  if (LEFT(query_script, 1) in ('/', '\')) then
    begin
          declare query_script_file_name text;

      set query_script_file_name := query_script;
      set query_script := LOAD_FILE(query_script_file_name);
      if query_script is null then
        call throw(CONCAT('Cannot load script file: ', query_script_file_name));
      end if;
    end;
  end if;
  call _interpret(query_script, TRUE);
end
       EXTERNAL_NAME: NULL
   EXTERNAL_LANGUAGE: NULL
     PARAMETER_STYLE: SQL
    IS_DETERMINISTIC: YES
     SQL_DATA_ACCESS: MODIFIES SQL DATA
            SQL_PATH: NULL
       SECURITY_TYPE: INVOKER
             CREATED: 2012-08-17 17:24:46
        LAST_ALTERED: 2012-08-17 17:24:46
            SQL_MODE:
     ROUTINE_COMMENT: Run given QueryScript text
             DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
  DATABASE_COLLATION: latin1_swedish_ci

In particular, look at the columns ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION. These provide most of the interesting data.


remove a paritition table I need to remove a partition table for an application upgrade. It is SQL Server 2008 R2.

The partition column is on column col1 data type smallint, null. Col1 has length 10 and precision 5. As you can see the partition column is not the primary key.

I have three partitions. Left boundary <=boundary 2, 5 and the last one is empty. All of the partitions are on primary file group. There are no other filegroups.

@Kin yes he wants just to remove the partition so it becomes non- partitioned table.
 as Aaron said I would   select * into new_table from old_paritioned and rename the new table and drop the partitioned table.I am sure they are better ways of doing that though.
If it is large table(billion of rows) copying all rows takes time and resources.


adrci not purge trace files and alert logs I try to purge some files in oracle. I use adrci. I want to purge trace files in listener file. But it's not work. I don't know why.

[grid@orcl1 app]$ adrci

adrci> set homepath diag/tnslsnr/orcl1/listener
adrci> purge -age 360 -type alert
adrci> purge -age 60 -type trace
adrci> purge -age 1 -type trace
adrci> exit

Tracefile still full with 4.9 gb.

[root@orcl1 trace]# ls -alh
total 4.9G
drwxr-xr-x  2 grid oinstall 4.0K Nov  2 12:43 .
drwxr-xr-x 14 grid oinstall 4.0K May  7 14:48 ..
-rw-r-----  1 grid oinstall  43M Aug 28  2014 listener_backup_20140828.log.bz2
-rw-r-----  1 grid oinstall 4.8G Nov  2 12:43 listener.log
[root@orcl1 trace]# pwd
/u01/app/grid/diag/tnslsnr/orcl1/listener/trace

ADRCI deletes the XML files, but not the plain text logs, which is a nuisance.

An easy but dirty solution would be overwriting the file with empty content, e.g:

$ cat /dev/null > listener.log

Deleting the file while the listener is running and logging is enabled may not lead to the expected result because of the file handling implementation on Linux/UNIX platforms.

Another solution is to temporarily disable logging, delete (or move or compress) the listener.log, then enabled logging again:

$ lsnrctl set log_status off
$ rm listener.log
$ lsnrctl set log_status on

Obviously, if you want, you can just stop the listener, take care of the listener log, then start the listener again.


Get count of intersected rows on the same table I have the following table:

ID  user_id          skill_id
1   8                1
2   8                3
3   2                3
4   5                3
5   2                1
6   5                2
7   5                4
8   5                5
9   8                5

I need to get the count of users who have both skills 1 and 3 (or more), a real life example would be to get the count of users who know php,mysql,and javascript, but not those that only know php and javascript.

When I do SELECT COUNT(USER_ID) WHERE skill_id IN(1,3) I get a count of 3, which means it's giving me the users with any of them, I need the users who have all?

As a side note I think I could do this intersecting different queries but that would mean a separated query for each tag and there can be endless combinations of skills, something tells me there is a simpler way, speed is really important here.

Thanks.

To obtain those users use

SELECT user_id
FROM `table`
WHERE skill_id IN(1,3)
GROUP BY user_id
HAVING 2 = COUNT(*)

If there is no unique constraint for (user_id, skill_id), use COUNT (DISTINCT skill_id) instead of COUNT(*).

To obtain only the count of those users you may use the above query as a subquery:

SELECT COUNT(*)
FROM ( SELECT user_id
       FROM `table`
       WHERE skill_id IN(1,3)
       GROUP BY user_id
       HAVING 2 = COUNT(*)
     ) AS `table`


MongoDB geospatial query with sort - performance issues I have query (which is very slow ~2,5s):

db.markers.find({ latlng: { '$within': { '$box': [ [ -16, -140 ], [ 75, 140 ] ] } } }).sort({_id: -1}).limit(1000)

When I run explain for this query I get

{
   "cursor" : "GeoBrowse-box",
   "isMultiKey" : false,
   "n" : 1000,
   "nscannedObjects" : 242331,
   "nscanned" : 242331,
   "nscannedObjectsAllPlans" : 242331,
   "nscannedAllPlans" : 242331,
   "scanAndOrder" : true,
   "indexOnly" : false,
   "nYields" : 1383,
    "nChunkSkips" : 0,
    "millis" : 2351,
    "indexBounds" : {
        "latlng" : [ ]
    },
    "lookedAt" : NumberLong(262221),
    "matchesPerfd" : NumberLong(242331),
    "objectsLoaded" : NumberLong(242331),
    "pointsLoaded" : NumberLong(0),
    "pointsSavedForYield" : NumberLong(0),
    "pointsChangedOnYield" : NumberLong(0),
    "pointsRemovedOnYield" : NumberLong(0),
    "server" : "xx:27017"
}

When I remove sort({_id: -1}) explain gives me (fast query 5 millis):

{
    "cursor" : "GeoBrowse-box",
    "isMultiKey" : false,
    "n" : 1000,
    "nscannedObjects" : 1000,
    "nscanned" : 1000,
    "nscannedObjectsAllPlans" : 1000,
    "nscannedAllPlans" : 1000,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 5,
    "indexBounds" : {
        "latlng" : [ ]
    },
    "lookedAt" : NumberLong(1000),
    "matchesPerfd" : NumberLong(1000),
    "objectsLoaded" : NumberLong(1000),
    "pointsLoaded" : NumberLong(0),
    "pointsSavedForYield" : NumberLong(0),
    "pointsChangedOnYield" : NumberLong(0),
    "pointsRemovedOnYield" : NumberLong(0),
        "server" : "xx:27017"
}

I have  2d index on latlng, desc index on _id and compound indexes.

db.markers.ensureIndex({latlng: '2d', _id:-1})
db.markers.ensureIndex({ latlng: '2d' })
db.markers.ensureIndex({ _id: -1 })

What I want to achieve is to get markers from a particular area sorted from newest.

Any ideas or suggestions how to do that in much less time than 2.5 seconds?

A query in MongoDB can only use one index at a time, so it's a case of one or the other - it can't use the 2d index first, then do a sort on the _id index.  In order to use indexes for both the selection and the sort, you would need a compound index like this:

db.markers.ensureIndex( { latlng : "2d" , _id : 1 } );

Try that, or similar and see how it impacts the results bearing in mind that once you define it, you can remove the original 2d index to save space and that this new index will have to be loaded into memory to be efficient.

Update: as mentioned in the summary, the above did not improve things, and the resulting query results in a scanAndOrder result.  This also happens with range based queries, as explained in this excellent blog post:

http://blog.mongolab.com/2012/06/cardinal-ins/

As explained in that post, the usual resolution for range based query performance is to switch the order of the indexes.  However this is currently not possible with geo indexes.  There is a Jira issue already open for this here for voting and tracking purposes:

https://jira.mongodb.org/browse/SERVER-4247


Should I have a column for every data type or 1 column that stores everything I have a database table that currently looks like this:

PageField
-Id (Int, PK)
-FieldType (String) Can be Text, Decimal, Integer or Bit
-Value (This stores the value regardless of the FieldType, so it is not strongly typed)

Should I remove the Value column and replace it with 4 separate columns TextValue, DecimalValue, IntegerValue, and BitValue?

The data type would be set to what is relevant. This would mean in every row 3 of these 4 columns would be NULL.

This question (in various guises) crops up regularly. This type of "solution" is known as EAV (Entity-Attribute-Value) and is not a good idea. Take a look here or here for tips (or links to tips) on the problems it can cause and how to properly leverage the data types that your RDBMS offers.

Not using the correct data type is a sure fire way to confuse the
query optimiser and slow your system down. Joe Celko is a world renowned expert in all matters SQL and his opinions merit attention.

The fact that he uses the acronym "MUCK" (*) to describe this system should tell you all you need to know :-)

(*) Massively Unified Code Key


Loop through comma separated values I have a Master DB.
I want to make duplicate DB of the Master DB but not all tables just specified.

I have those table names in comma separated string.
tbl1, tbl2, tbl7, tbl10 etc.

I'm trying to make a stored procedure passing it new DB name That creates the DB and replicate the given table to it. So far i have 2 thing, but i don't know how to loop through it. I'm new to DB so please guide how to achieve this.

(Note) I haven't found if an array can be used to loop through, the table names could also be stored in array variable, because the table names are hard coded.

The following is the sudo code i desire.

CREATE DATABASE %param_db%;

@tables = 'tbl1, tbl2, tbl7, tbl10';
loop through @tables as table_name
    CREATE TABLE %param_db.table_name% LIKE Master.%table_name%;
End loop

Here are the steps to layout in the Stored Procedure

Create the Target database
Determine how many elements are in the list of tables
Loop through each element of the list of tables using the ELT() function
Take each element and form SQL Statement to create the new table in Target DB

Here is that Stored Procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`TableCookieCutter` $$
CREATE PROCEDURE `test`.`TableCookieCutter`
(
    SourceDB VARCHAR(64),
    TargetDB VARCHAR(64),
    TableList VARCHAR(1024)
)
BEGIN

    # Create the Target DB
    SET @sql = CONCAT('CREATE DATABASE IF NOT EXISTS ',TargetDB);
    PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

    # Strip Table List of Blanks
    # Enclose each table in quotes
    SET @table_list = CONCAT('''',REPLACE(REPLACE(TableList ,' ',''),',',QUOTE(',')),'''');

    # Count the number of tables in the list
    SET @table_stub = REPLACE(@table_list,',','');
    SET @array_count = LENGTH(@table_list) - LENGTH(@table_stub) + 1;

    # Loop through list of tables, creating each table
    SET @x = 0;
    WHILE @x < @array_count DO
        SET @x = @x + 1;
        SET @sql = CONCAT('SELECT ELT(',@x,',',@table_list,') INTO @tb');
        PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
        SET @sql = CONCAT('CREATE TABLE ',TargetDB,'.',@tb,' LIKE ',SourceDB,'.',@tb);
        PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
    END WHILE;

END $$

DELIMITER ;