Reduce a huge number of possibilites to something manageable? I have this problem, which seems like it should have an easy solution, but I can't figure it out.

Say we have pizza_toppings table where a chef enters available sets of toppings. There are four toppings: pepperoni, olives, pepper, and basil. The chef makes four different pizzas with the above ingredients:

pizza_toppings
--------------
pizza 1 has only pepperoni
pizza 2 has pepperoni and olives
pizza 3 has olives or basil
pizza 4 has ( pepperoni and pepper) or only basil

How would I record all these 'and', 'or', 'and + or', 'and + and', '(and + or) + and ...', etc. relationships between table entries?

It seems like a problem from some difficult textbook that should have a clever solution, which I can't figure out. My program doesn't actually deal with pizza and there will be countless "toppings" created by users and infinite possible combinations, which will also be combined by users. So I can't really make a table with pre-defined combinations. Is there a solution to such a problem?

Hmmm... interesting question.

As you say, you can have hundreds even thousands of different combinations of toppings, but you don't want a menu the length of 'War and Peace'.

I would divide the toppings into categories (cat_1... cat_5) and have your pizza price based on the top 3 categories on your pizza.

1€ for cat_1 (salami, anchovies),
75¢ for cat_2 (turkey, chicken),
50¢ for cat_3 (pineapple, porcini),
25¢ for cat_4 (capers, cherry tomato)
0¢ for cat_5 (x 2 - standard tomato, mushroom)

So, you have a base price of, say, 5€ per pizza, then add two cat_1 and a cat_3 topping for a total price of €7.75.

Allow two "standard" toppings - say tomato and cheese as cat_5 and you have a relatively simple pricing scheme that even the stupidest staff member or client can understand.

You could also provide "specials" from time to time - i.e. "Normally, €7.50 - try it tonight for €6.50..." or suchlike!

You appear to be suggesting that pizzas are not your prime focus in life? Maybe if you explained your requirements in further detail, we might be able to be of more assistance. Having said that, I think the scheme above or similar would go a long way to helping resolve this or similar issues.

On a database level, I would have something like the following (fiddle):

CREATE TABLE order_
(
  order_id serial NOT NULL,
  order_time timestamp NOT NULL,
  order_taken_by INTEGER NOT NULL, -- references employee table
  order_served_by INTEGER NOT NULL, -- same
  order_value INTEGER NOT NULL -- in cent
);

CREATE TABLE order_item
(
  order_item_id SERIAL NOT NULL,  -- may be pizza_id
  order_item_price INTEGER NOT NULL
);

CREATE TABLE pizza
(
  pizza_id serial NOT NULL,
  cat_1_items VARCHAR (50), -- references table of cat_1 toppings
  cat_2_items VARCHAR (50) -- cat_2 toppings
  -- ..
  -- ..
  -- ..
);

You might have a stored procedure to calculate your pizza price? You might also find this interesting.


Why isn't the primary (clustered) key being used in this query? I have a SQL Server 2008 R2 table whose schema structure looks as follows:

CREATE TABLE [dbo].[CDSIM_BE]
(
    [ID] [bigint] NOT NULL,
    [EquipmentID] [varchar](50) NOT NULL,
    [SerialNumber] [varchar](50) NULL,
    [PyrID] [varchar](50) NULL,
    [MeasMode] [varchar](50) NULL,
    [ReadTime] [datetime] NOT NULL,
    [SubID] [varchar](15) NULL,
    [ProbePosition] [float] NULL,
    [DataPoint] [int] NULL,

    CONSTRAINT [PK_CDSIM_BE]
    PRIMARY KEY CLUSTERED ([ID] ASC, [EquipmentID] ASC, [ReadTime] ASC)
         WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON) ON [MonthlyArchiveScheme9]([ReadTime])
) ON [MonthlyArchiveScheme9]([ReadTime])

CREATE NONCLUSTERED INDEX [idx_CDSIM_BE__SubID_ProbePosition]
ON [dbo].[CDSIM_BE] ([SubID] ASC, [ProbePosition] ASC)
INCLUDE ([EquipmentID], [ReadTime], [BECorr])
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
          SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
          ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [MonthlyArchiveScheme9]([ReadTime])

CREATE NONCLUSTERED INDEX [IX_CDSIM_BE_ProbePosition]
ON [dbo].[CDSIM_BE] ([ProbePosition] ASC)
INCLUDE ([SerialNumber], [SubID])
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
          SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
          ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [MonthlyArchiveScheme9]([ReadTime])

CREATE NONCLUSTERED INDEX [IX_CSDIM_Readtime]
ON [dbo].[CDSIM_BE]([ReadTime] ASC)
INCLUDE ([EquipmentID])
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
          SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
          ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [MonthlyArchiveScheme9]([ReadTime])

And I'm executing this simple query:

Select Max(Id)
From dbo.CDSIM_BE

There are ~2.5B rows in the table.

The query plan shows an index scan being done on the IX_CdSIM_BE_ProbePosition index. I'm wondering why SQL Server simply wouldn't use the clustered (and primary) index and immediately go to the last row in the table and retrieve the Id value, since that must be the maximum.

The clustered index is partitioned on ReadTime so it couldn't use the PK as you describe. It would need to find the Max(Id) for each partition and then find the max of those. It is possible to rewrite the query to get such a plan however.

Using an example based on the article here a possible rewrite might be

SELECT MAX(ID) AS ID
FROM   sys.partitions AS P
       CROSS APPLY (SELECT MAX(ID) AS ID
                    FROM   [dbo].[CDSIM_BE]
                    WHERE  $PARTITION.MonthlyArchiveFunction9(ReadTime)
                                                    = P.partition_number) AS A
WHERE  P.object_id = OBJECT_ID('dbo.CDSIM_BE')
       AND P.index_id <= 1;

To process each partition in turn.

Note the plan still has a scan (with a seek predicate to select the partition) but this is not a full scan of the partition.

The scan is in index order with direction "BACKWARD". The TOP iterator can stop requesting rows from the scan after the first one is received.


Query goes in Copying to tmp table stauts in MySQL and never returns I am getting some strange behaviors for my MySQL query status. Here is my query code:

SELECT a.id as art_id, b.album_artist_id,
s.song_title,s.song_seo,a.artist_seo,a.artist_name,
b.album_title, b.album_picture, s.id,s.picture
FROM tbl_artist_album b, tbl_artists a, tbl_songs_artist_album saa, tbl_songs s
WHERE 1=1
  AND s.id = saa.song_id AND a.id = saa.artist_id
  AND b.id = saa.album_id AND saa.artist_id != 67
  AND ( MATCH (s.song_title) AGAINST ('taylor swift*' IN BOOLEAN MODE))
  AND (CONCAT(s.song_title) LIKE '%taylor swift%')
  AND s.song_status = 1
group by s.id
ORDER BY s.song_title = 'taylor swift' desc
LIMIT 50

When I execute above query in Phpmyadmin panel it works fine and returns 0 results with in few seconds.
But When the query is executed from browser by search form on a page , the query goes directly in the "Copy to tmp table" status and never returns anything.

| 79 | | | | |  632 | Copying to tmp table | select a.id as art_id,b.album_artist_id,s.song_title,s.song_seo,a.artist_seo,a.artist_name,b.album_title, b.album_picture, s.id,s.picture from tbl_artist_album b, tbl_artists a, tbl_songs_artist_album saa, tbl_songs s where 1=1 AND s.id = saa.song_id AND a.id = saa.artist_id AND b.id = saa.album_id AND saa.artist_id != 67 AND s.song_title like 'taylor swift%'  AND s.song_status = 1 group by s.id order by s.song_title = 'taylor swift' desc limit 50 |

I have also attached the screenshot of query execution for reference in Phpmyadmin and it work in few seconds but I am confused why it is showing "copy to tmp table" when executed from browser.

(I can't explain why one call was 'fast' and the other was 'slow'.)

The "copying to tmp table" provides virtually no useful info.  It may be saying that it is busy shoveling around more than 50 (re: LIMIT) rows collected from the 4 tables.  My fix, below, mitigates that.

Try the following rewrite as an attempt to speed things up.

First -- Find the ids for "Taylor Swift" songs.  (But would that be in the title??)
Second -- Get the rest of the data and do the JOINs.

The rationale -- Get the complex, slow, parts of the query over with before rummaging around in multiple tables.

SELECT ...
    FROM ( SELECT id FROM tbl_songs
        WHERE ( MATCH (song_title) AGAINST ('taylor swift*' IN BOOLEAN MODE))
          AND (CONCAT(song_title) LIKE '%taylor swift%')
          AND song_status = 1
        ORDER BY s.song_title = 'taylor swift' desc
        LIMIT 50 ) AS s1
    JOIN tbl_songs AS s2  ON s1.id = s2.id
    JOIN ... the-other-tables
        ON ...
    WHERE  ...  the-filtering
    ORDER BY s.song_title = 'taylor swift' desc
    LIMIT 50

Yes, the ORDER BY and LIMIT are needed twice.

The hope is that MATCH is performed first, leading to much fewer rows to apply the LIKE to.  Please provide EXPLAIN SELECT ....

The 1=1 is a common technique for building the WHERE clause.  It is harmless, but it would be better to gather an array of AND clauses and put them together at the end, not building a string as you go.

Check innodb_buffer_pool_size -- is it about 70% of available RAM?


Reverse-proxy for SQL Server Context: For our testing environment, we were migrating from on-premise SQL Server 2017 to Docker Linux SQL 2017 container. There is a large number of TestScripts in windows that try to connect to SQLServer in windows via trusted authentication. This Windows Authentication mode doesn't work with Docker Linux SqlServer as only SqlAuth is supported.
Question:
Is there a reverse-proxy that I can use to accept TrustedConnection from TestScripts requiring TrustedAuthentication and redirect the requests to Docker Linux SqlDb that accepts only SqlAuth?

It is ok to pre-define SqlAuth for Docker Linux SqlDb in reverse-proxy.
It is ok to skip or bypass or fake-validate any form of authentication from clients. Our test scenarios are mostly positive, and we don't need to validate Sql login failures.
It is ok to run reverse-proxy in windows platform, for windows auth capability.

This was to avoid the work of re-writing/updating auth on large number of TestScripts (which are legacy). This is test code, and ok to ignore all the security implications.

I found TDSProxy useful for this purpose. This proxy handles and redirects on TDS protocol used for SQL auth.
Code change to enable my scenario: https://github.com/tech-software/TDSProxy/pull/3


Create Language plperl - Error: could not load library plperl.dll When I create language plperl , I get error: ERROR:  could not load library "C:/Program Files/PostgreSQL/9.1/lib/plperl.dll": The specified module could not be found.

But in my computer,  "plperl.dll" file is exist in "C:/Program Files/PostgreSQL/9.1/lib/..." folder   ( I can not post illustrative image, this forum require >= 10 reputations)

And if I select * pg_pltemplate, I get:

-[ RECORD 4 ]-+-------------------------
tmplname      | plperl
tmpltrusted   | t
tmpldbacreate | t
tmplhandler   | plperl_call_handler
tmplinline    | plperl_inline_handler
tmplvalidator | plperl_validator
tmpllibrary   | $libdir/plperl

Because I missed 3 files dll (MSVCR90.DLL, PERL514.DLL, IESHIMS.DLL) in Windows I could not install the language pl/Perl.

Here my steps to solve:

Using  "Dependency Walker" tool to check dll files missing (if any). After installing, you open plperl.dll file and find errors.
Install dll files missing.
Install ActivePerl (to install Perl on window, It will automatically set Perl bin folder to windows environment variable)
Restart postgresql service
Test:

    createlang -U postgres plperl postgre  (it's ok)
    CREATE or replace LANGUAGE plperl      (it's ok)

Download (I just can post 2 links)

Dependency Walker tool: http://www.dependencywalker.com/

Reference: http://www.mkyong.com/database/install-perl-in-postgresql-the-specified-module-could-not-be-found/


Teradata: Get quarter for specific date When selecting the maximum date of a column how do I obtain which quarter that date falls into without using a CASE statement?

Option 1: You can create a reference table (Month, Quarter) and you can extract month from your date column and join with it. (Joining this table won't affect performance as PI will be month)

Mth| Quarter
1  | 1
2  | 1
3  | 1
4  | 2
5  | 2
6  | 2
7  | 3
8  | 3
9  | 3
10 | 4
11 | 4
12 | 4

Option 2: Use a logic as (2+Month)/3 as in

SELECT FLOOR((2+EXTRACT(MONTH FROM DATE_CLM))/3)


postgres: WAL files not being deleted during PITR I have got an issue with setting up PITR on my postgres server:
WAL archiving and restoring works quite well, as well as restoring to a specific point in time. The issue however is, that if i do a long replay all the WAL files are extracted from the archive and start piling up in the pg_xlog directory without ever being deleted, this causes the playback to stop with an error once the disk is full. If i then delete all the logs and restart the server the replay continues until the disk is full again. Repeat this procedure a few times and the recovery is completed sucessfully.
(the actual working time of the server is just a few minutes)

Since this is already a pain after only a few weeks of history i cannot take a system like this online as the recovery process would take forever...

I'd appreciate any help you can provide.

Ps.: wal_level is set to minimal and archiving is off during recovery

You probably have checkpoint_timeout set very high on the production system, and archive switches are being driven by checkpoint_segments, probably in conjunction with a low setting of archive_timeout.

But, checkpoint_segments has no effect during recovery.  Restartpoints during recovery are driven exclusively by checkpoint_timeout.  Since recovery can replay files far faster than it took production to create them, that means you have a huge accumulation of old xlog files waiting for a restartpoint to allow them to be cleared out.

One solution is to lower checkpoint_timeout by quite a bit for the system undergoing recovery, and then set it back once recovery has finished.  This will cause restartpoints to happen more often, allowing the log files to be removed sooner.

Another option is to simply have a script delete the files from pg_xlog during recovery once a newer file has been restored and renamed.  If the system crashes during recovery, it may then have to go refetch some of those deleted files from the archive.

The system was designed to tolerate the case where files are removed from the archive once they are fetched and so cannot be re-fetched.  That is why it stores all the fetched files in pg_xlog until a restartpoint; so it has all the data it needs to restart the recovery if the recovery crashes.  As long as you don't remove files from the archive, you can remove them from pg_xlog.

This situation will thankfully be improved in 9.5.


PostgreSQL created a PostgreSQL user on mac. Is this necessary? I just installed PostgreSQL on a Mac (Mountain Lion) today.

The installer created a mac user PostgreSQL on my machine. After installation, the user still exists.

My question is that why postgreSQL needs a new user? Can I remove this user? MySQL server does not require such a user being created.

Can someone help?

Usually, the Postgres server process is owned by the accompanying system user (not by root or the installing user), so it only has a limited set of rights, making possible attacks somewhat less dangerous.

Additionally, the Postgres system user is normally used to carry the default privileges to initialize db clusters and access newly created databases without password. But you can change all that, of course.

What MySQL does is hardly relevant to these considerations. It is in no way role model in security related affairs.


Minimum value within a set (or window) within a three column table I only have SQL Server 2008R2, therefore I do not have all the window functions available since SQL Server 2012.

I have a table that looks like this:

I would like to retrieve a result set like this, where each category has the ID associated with the lowest order number, and also the ID associated with the highest order number:

I am looking for the TSQL select statement that would do this.

You could do this without the new (2012+) window function FIRST_VALUE(), using the ROW_NUMBER(). But you could also do it using  OUTER APPLY:

SELECT
    d.CategorieID,
    IDMinOrder = a.ID,
    IDMaxOrder = b.ID
FROM
    ( SELECT DISTINCT CategorieID
      FROM tableX
    ) AS d
  OUTER APPLY
    ( SELECT TOP (1) a.ID
      FROM tableX AS a
      WHERE a.CategorieID = d.CategorieID
      ORDER BY a."Order" ASC
    ) AS a
  OUTER APPLY
    ( SELECT TOP (1) b.ID
      FROM tableX AS b
      WHERE b.CategorieID = d.CategorieID
      ORDER BY b."Order" Desc
    ) AS b ;


Materialized views master table New to this topic - in my current environment we are not doing any replication but we use materialized views frequently in order to achieve performance gains on complex views that are in turn used by web applications (obviously these are only used when the data involved is not completely time-sensitive, we do nightly refreshes of the materialized views).  Note that we are doing Complete, On Demand refreshes on a specified schedule.

The issue I am running into is it seems that Oracle has used some internal process to determine the 'master' table and when DML operations occur on the master, the MV becomes invalid.  To be honest we don't really care whether the master table has any inserts, updates, deletes as we just want to completely refresh the MV every night - unfortunately when the MV goes invalid it prevents refreshes.

So my question is can I manually set the master table to something like SYS.DUAL so that the MV never goes invalid or is there another workaround?

Failing this I suppose instead of using MVs I would just schedule a nightly CTAS.

Edit:
Added the Oracle note # in comments below - synopsis is that when the MV is in the same database as the master table, DML on master table will cause the MV to go invalid ("Needs Recompile" specifically).  Certainly I can schedule a nightly recompile but this doesn't seem elegant.

To downvoter - this is my 2nd question here, could you express what makes the question so flawed so I don't keep losing points in future posts?  :)

From the Oracle documentation

  When DML changes are made to master table data, Oracle Database
  stores rows describing those changes in the materialized view log and
  then uses the materialized view log to refresh materialized views
  based on the master table.1

So you should be able to see what changes are invalidating the MV.

Next you should examine the alert log to see if anything has been logged.

I wonder if you have a complex chain of materialized views?  Does one depend on another? Is only one becoming invalid and others are not?  Try this query to see what depends on what

dbms_snapshot.get_mv_dependencies(
list    IN  VARCHAR2,
deplist OUT VARCHAR2);

I would not recommend your suggested workaround until you understand what changes are invalidating the view.

Edit: Metalink note 264036.1 says:

"This is expected behavior. When there is a DML on the master table, all the MVs based on this table are marked as INVALID. Though the status is INVALID, you will be able to query the mview.
However, the query on MV will not return latest update done in master table unless MV is refreshed."

From the way you are using the Materialized Views I am not sure why you cannot issue a refresh of each view that is marked invalid.


Is the MySQL replica's data consistent if the Master was switched to binlog_format = MIXED but the Slave's relay logs were rotated sometime later? The accepted answer to this question is very helpful but it's not clear to me if one should be concerned about the replication that occurred from the time the Master's binlog_format was changed to MIXED to the time the Slave's relay logs were rotated.

There are no errors or warnings in the Slave's error log.

Both Master and Slave are MySQL 5.1

Relay log rotation have nothing to do with it.

What's critical is that the slaves, if set up with log_slave_updates (i.e. in themselves logging to binary logs), are pre-configured with binlog_format = MIXED or binlog_format = ROW.

To elaborate: there is no problem if the master uses binlog_format = STATEMENT and the slaves use ROW. There is a problem if the master uses ROW and the slaves use STATEMENT. You just can't restore the exact original statement from ROW format.

Likewise, if you master is MIXED, then slaves must be wither MIXED or ROW.

Back to your question, assuming the slaves are already configured with MIXED, there is no problem in just SET GLOBAL binlog_format = MIXED on the master. There is no problem in the binary log containing some entries of this format and some entries of the other. There is no problem with the relay logs containing entries of both forms.

I disagree with the accepted answer you linked to.


Unable to find how a table is being populated Using SSMS2014 w/ SQL Server 2008 R2.
I have a table that I am trying to find out how and from where data inside it is coming from. (I didn't create the table and am unable to contact the person who did)
I've tried the following:

RMB on the table -> View Dependencies.
Result: This comes back empty.
I've tried running the following code:

select object_name(object_id) as DatabaseName, last_user_update, *
from sys.dm_db_index_usage_stats
where database_id = DB_ID('PackardIntranet')
and object_id=object_id('tbl_Johnstone_Packard_SalesReport')

Result: This just shows NULLs and 0's except when I queried the table previously
I've tried using RedGate's SQL Search using the table name or a column name as a parameter.
Result: The only result is the table itself

Am I missing something or can it be that this table was just created and populated once by the person who created it? Or can this be part of a script that is used to create and populate this table? Is there any way I can attempt to find out more about this table?

Index statistics should tell you when the table was last updated, so maybe it really has been just populated once. Of course you could for example add a trigger to the table to track of someone inserts / updates it, but don't forget to remove it later.


SQL Server giving intermittant connection failures I have a SQL Server 2000 running on Windows 2003 Server that is giving me intermittent connection failures.

The user connections are getting closed on waiting but mature connections.  Meaning when users are connected to an application that is running on this server, the programs start up fine.  The problem usually shows up after the connection has been sitting for a bit.  I'm wondering if there is some odd setting that is killing older, not active connections?

What could be causing this?  What can I check to further troubleshoot this.

EDIT:

Is it possible that my problems are caused by running SQL Server 2000 personal edition, where the old server that died and was replaced was on Standard?  I have no way to verify the old version, but is this possible?

EDIT: Well I upgraded the edition to SQL 2000 Standard, and there is nothing different in the behavior of the server.  So that wasn't it.

“I'm wondering if there is some odd setting that is killing older, not active connections?” – short answer is no.

This is probably network issue, or SQL Server being under big performance impact or DNS or something similar.

Is your application on the same machine as SQL Server ? Do you have any other issues? Can you run SQL Server from Enterprise manager w/o issues?


INT or CHAR for a Type Field What is the best design for a table, a Type field that is of int or char(1)?  In other words, given this schema:

create table Car
(
    Name varchar(100) not null,
    Description varchar(100) not null,
    VehType .... not null
)

Is it more efficient (performance wise) for VehType to be an int or a char(1)?  Say you have five types of cars, should you use the incrementing values 0 -> 4, or characters for the types (say; 'v', 's', 'c', 't', 'm')?

If it is any more than that, I'd use a separate Type table and have a foreign key relationship, but I don't see the need for that.

I notice that the sys.objects catalog view uses a character for the type field.  Is there a reason for that?  Am I just grabbing at thin air here, and is it whatever I'm more comfortable with?

You'd typically use tinyint which is 1 byte too

char(1) will be slightly slower because comparing uses collation
confusion: what is S: SUV or Saloon or Sedan or Sports?
using a letter limits you as you add more types. See last point.
every system I've seen has more then one client eg reporting. The logic of changing V, S into "Van", "SUV" etc will need repeated. Using a lookup table means it is a simple JOIN
extendibility: add one more type ("F" for "Flying car") you can one row to a lookup table or change a lot of code and constraints. And your client code too because this has to know what V, S, F etc are
maintenance: logic is in 3 places: database constraint, database code and client code. With a lookup and foreign key,it can be in one place

On the plus side of using a single letter... er, don't see any

Note: there is a related MySQL question about Enums. The recommendation is to use a lookup table there too.


Migrating from Postgres 8.4 to 9.4 in different data centers I'm trying to migrate a Postgres 8.4 database in a Brazilian machine to Amazon RDS Postgres 9.4 in North Virginia.

The database uses encoding LATIN1, tablespace pg_default, lc_collate and lc_ctype C.

When the web application is connected to the 8.4 database, the accented words are shown normally.

  União Estável

When I connect it to the 9.4 restored database (created using the same encoding, tablespace, collate and ctype) the accented words are shown weird.

  UniÃ£o EstÃ¡vel

The web application HTML pages have charset defined to iso-8559-1.

Commands used for pg_dump and restore

pg_dump --file=example --format=custom --host=exampleIP --username=exampleusername --lock-wait-timeout=5000 exampledatabase

pg_restore --dbname=exampledatabase --format=custom --host="awsRdsDns" --username=exampleusername --verbose exampleFile

I also tried dump with --encoding=LATIN1 but nothing changed

When I select those data through PgAdmin3 in both databases, the accented words are shown normally.

What am I doing wrong?

The HTML charset is set to iso-8859-1. Altering HTML charset didn't change it. The RDS client_encoding is UTF8 because the majority of databases uses UTF8, there's only 6 other databases that uses LATIN1. Is there a way I can change it only for those 6 databases, without affecting the others?

Conclusion

I found out that my problem was my web application CLIENT_ENCODING was UTF8, so my solution was to set it to LATIN1, but because my question was directed to "other problem" I decided to acept that answer.

You can set an encoding that is different from the cluster-wide one the following way:

CREATE DATABASE important WITH ENCODING 'LATIN1' TEMPLATE template0;

but the documentation further says:

  Notice that the above commands specify copying the template0 database. When copying any other database, the encoding and locale settings cannot be changed from those of the source database, because that might result in corrupt data.

All this can be done when creating the database, there is no ALTER DATABASE syntax for changing it later.

Setting client_encoding on the server might be tempting, but that you cannot do for a single DB only, it's all or none.  A slightly ugly idea could be to set this parameter to 'UTF8' upon connection, before any data is accessed.


Who installed SQL Server Is there any way to find out who installed  SQL server on a Windows 2003 server?

If the correct auditing is turned on, and the security event log hasn't rolled over it'll be in there.  Failing that it may not be.

You could look at NTFS file permissions and ownership, and what accounts have sysadmin rights and possibly figure it out.


Where can I find exact rules of a collation? Is there any documentation anywhere for the exact sorting/equality rules defined by a collation in Windows (SQL Server), for instance "Finnish_Swedish_CI_AS"?

I know about CI/CS, AI/AS etc, but I'm looking for a table or set of rules that actually states that e.g. 'a' is sorted before 'b' and so on. Maybe something similar to this page: http://collation-charts.org/mssql/mssql.040B.1252.Finnish_Swedish_CI_AS.html The problem with that page is that I'm unclear on some aspects of how to read it. Also it doesn't indicate that æ is regarded as equal to 'ae', and I'm wondering if there are more such cases or other rules not shown.

Also, I'm trying to find the exact difference between Finnish_Swedish_CI_AS and Finnish_Swedish_100_CI_AS

Unfortunately no, no such documentation exists. The most likely reason for this is that it would be much more difficult to document than most people are aware of, due to the actual complexity of the rules. While there is a default weight to each character (even if that weight is 0), how that weight is used is determined by several layers of rules. Some of those rules are dependent on the Collation (e.g. case, accent, Kana, and width sensitivity) and some are simply hard-coded (e.g. sorting lower-case before upper-case in most, but not all, cases). But those types of rules are just some of the types of rules that are processed.

Yes, there are simple mappings of æ = ae, but there are more complex mappings involving combining characters (characters that are not displayed on their own, but are super-imposed over base characters). For example:

SELECT NCHAR(252), NCHAR(0x0308), N'u' + NCHAR(0x0308)
-- ü    ̈   ü

IF (NCHAR(252) = N'u' + NCHAR(0x0308)) SELECT 'Same!' ELSE SELECT 'Nope.';
-- Same!

IF (NCHAR(252) = N'u') SELECT 'Same!' ELSE SELECT 'Nope.';
-- Nope.

IF (NCHAR(252) = N'u' COLLATE Latin1_General_100_CI_AI) SELECT 'Same!' ELSE SELECT 'Nope.';
-- Same!

Of course, some "characters" are comprised of a base character and multiple combining characters.

Also, some weights depend on context (i.e. where the character is placed in relation to other characters). For example, a hyphen (i.e. == minus sign == - ) by itself sorts before letters. A simple test shows this behavior:

SELECT * FROM (VALUES ('a'), ('c'), ('-')) t(c)
ORDER BY t.c COLLATE SQL_Latin1_General_CP1_CI_AS ASC;

SELECT * FROM (VALUES ('a'), ('c'), ('-')) t(c)
ORDER BY t.c COLLATE Latin1_General_CI_AS ASC;

Both Collations have the same ordering here:

-
a
c

Now let's place the hyphen between some letters. First we will test with a SQL Server Collation (which uses the older, simpler "string sort"):

SELECT * FROM (VALUES ('a'), ('c'), ('-'), ('aaa'), ('aca'), ('a-b'), ('a-d')) t(c)
ORDER BY t.c COLLATE SQL_Latin1_General_CP1_CI_AS ASC;

That returns:

-
a
a-b
a-d
aaa
aca
c

Here there is no difference in that the - still comes before the letters, which is why both a- rows came before both the aa and ac rows. Now use a Windows Collation:

SELECT * FROM (VALUES ('a'), ('c'), ('-'), ('aaa'), ('aca'), ('a-b'), ('a-d')) t(c)
ORDER BY t.c COLLATE Latin1_General_CI_AS ASC;

That returns:

-
a
aaa
a-b
aca
a-d
c

What you are seeing here is that the hyphen, due to being between letters, is ignored (and yes, it is ignored only when sorting; when doing a comparison, it is not ignored, which can be seeing by running: SELECT 1 WHERE 'a-b' = 'ab' COLLATE Latin1_General_CI_AS;). This is why a-b sorts between aaa and aca as it is seen as being ab, and a-d comes after aca as it is seen as being ad.

Two other complicating factors are:

What versions of the Unicode Character Database (UCD) and Common Locale Data Repository (CLDR) are being used. It is not as straight-forward as one (or many) would hope, and the older versions of the Unicode standard either do not seem to have all of the base info, or at least they certainly don't make it easy to find
How precise is Microsoft's implementation of the Unicode standard? After all, it is just a standard and can be implemented differently by different vendors.

All that being said, you can still probably get pretty close to what you are looking for by examining some of the base data files from Unicode.org. But first, we will need to know which locale-specific customization file to look at. We can find this by getting the LCID of the Collation in question:

SELECT COLLATIONPROPERTY('Finnish_Swedish_CI_AS', 'LCID'),
       CONVERT(VARBINARY(3), COLLATIONPROPERTY('Finnish_Swedish_CI_AS', 'LCID'));
-- 1035       0x00040B

Now we can look for 040B on the following page: LCID Structure.

We see that 0x040B refers to fi-FI, which means that we will be looking for a file having either fi-FI or just fi in its name.

Explanations (some "light" reading ;-):

Unicode Collation Algorithm (UCA)
Unicode Locale Data Markup Language (LMDL): Part 2: General (Transforms)
Unicode Locale Data Markup Language (LMDL): Part 5: Collation

Data:

Transformations (æ → ae, etc.)
Remapped Unicode Element Table for use with CLDR
Root collation order
Finnish customizations (based on LCID of 1035 / 0x040B meaning "fi")


How to pass url value to index? How to read the value from URL and compare with index that i have created in MYSQL table. I have a form where provide list of selection using drop down list and i pass the selected value to the next page URL. Now i want to compare this value with my index. how to do this? please help me. i have something like this at the first form.

    <select id="ccrseid" name="selectvalue" onchange="showMe(this.value);">
  <option value="">Select</option>
  <option value="1">Computer Science</option>
  <option value="2">Engineering</option>
  <option value="3">Business</option>
  <option value="4">Education and Training</option>
  </select>
  <input type="submit" value="Submit">

then im trying to get the selected value and check with index that i have created in another form

$id=(int)$_GET['selectvalue'];;

$ret1="SELECT * FROM $db_tb_name WHERE MATCH(Id_index=$id) AGAINST('%$str%' IN BOOLEAN MODE)";
$result1 = MySQL_query($ret1);

but when i run i get this error

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 '==2) AGAINST('%%' IN BOOLEAN MODE)' at line 1

how to solve this?

I think you need to revise the design of your db tables. The way you have described where using fixed categories as fields of a table is not so wise, what if you needed to add more categories?

So assuming your problem is to search for keywords of those items belonging to a selected category, here is the way to do it.

We can use two tables in the Database.

Table 1. Categories

==============================
|  ID  | Category            |
------------------------------
|  1   | Computer Science    |
|  2   | Engineering         |

Table 2. Items

=============================================
 ID  |  Category_ID  | Keywords             |
=============================================
  1  |      1        | Keyword A            |
---------------------------------------------
  2  |      2        | Keyword B            |
---------------------------------------------
  3  |      1        | Keyword C            |
---------------------------------------------

Now you can do try the following query in the page that gets the selected id as url parameter.

// you can use $id = $_GET['selectvalue'];
// but do remember to escape the parameter value before passing to the query to avoid any security issues
// therefore use the following

$id = mysql_real_escape_string($_GET['selectvalue']);
$query = "SELECT * FROM ITEMS WHERE category_id = $id";

//Do note that use of mysql_query() is discouraged. Instead, the MySQLi or PDO_MySQL extension should be used.
//However to give you a simple answer

$result_query = mysql_query($query);
$result_data = mysql_fetch_assoc($result_query);

If this is not what you are trying to do and if you really want to search for a particular field with keyword values, than here is another option

$selectColumn = $_GET['selectvalue'];
$keywords = "2,3,4";

$query = "SELECT * FROM items WHERE $selectColumn IN ($keywords)";
$result_query = mysql_query($query);

UPDATED

If you want to do a fulltext search as what you are trying to do according to the details in your question, I want you to check the following.

Make sure the index of type is FULLTEXT.
Full-text indexes can be used only with MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.

Inside the MATCH() you need to put the field names comma separated and AGAINST() can only have a string and optional modifier indicating what type of search you want to do. SO in your case, it should be something like this:

SELECT * FROM TableNameHere WHERE MATCH (fieldA, fieldB) AGAINST ('"$str"' IN BOOLEAN MODE)

try doing a test by using a full query with actual values instead of the $variables in the query.

For more information, have a look at these two links

http://dev.mysql.com/doc/refman/5.5/en//fulltext-search.html

http://dev.mysql.com/doc/refman/5.5/en//fulltext-boolean.html


enforcing data integrity for sequential relations These are my tables:

create table trips (
  trip_id serial primary key,
  trip_nm text
);

create table trip_segments (
  segment_id serial primary key,
  departure_ts timestamp with time zone, -- departure time
  arrival_ts   timestamp with time zone, -- arrival time
  trip_id integer references trips (trip_id)
);

An insert would look like:

insert into trips (trip_nm) values ('my trip');

-- first segment
insert into trip_segments (departure_ts,arrival_ts,trip_id) values
                          ('2013-01-30 12:00', '2013-01-30 20:00', trip_id);

-- second segment
insert into trip_segments (departure_ts,arrival_ts,trip_id) values
                          ('2013-01-30 21:00', '2013-01-30 22:00', trip_id);

Given the above data: I want to ensure that a departure time in the second segment is after the arrival time of the first segment.

Which means the following insert should fail for the second segment:

insert into trip_segments (departure_ts,arrival_ts,trip_id) values
                          ('2013-01-30 19:55', '2013-01-31 22:00', trip_id);

There could be many segments for one trip. There are many trips.

So, how do I do that?

My first thought was to use a check constraint function for the trip_segments table, that looks like:

create function previous_ts(trip_id integer)
  returns timestamp with time zone as $$

  /* do some hackery here using the lag() function */

  return previous_arrival_ts;

$$ language 'sql';

Okay, so I haven't implemented that, but that would be the idea. However, I read (on stackoverflow, lost the answer) that using
a check constraint function is, and I'm para-phrasing, a really bad idea.

Ok, so there must be a better way, maybe using a trigger, I don't know. What's the best way to solve this problem?

I'm using postgresql 9.5

You can make use of an EXCLUSION constraint and tsrange datatype instead of two timestamps.

create table trip_segments (
  segment_id serial primary key,
  travel_ts tstzrange, -- departure time to arrival time
  trip_id integer references trips (trip_id),
  EXCLUDE USING gist (trip_id WITH =, travel_ts WITH &&)
);

-- first segment
insert into trip_segments (travel_ts,trip_id) values
                          ('[2013-01-30 12:00,2013-01-30 20:00)', 1);

-- second segment overlaps and fails
insert into trip_segments (travel_ts,trip_id) values
                          ('[2013-01-30 19:55,2013-01-30 22:00]', 1);

You get:

  ERROR:  conflicting key value violates exclusion constraint "trip_segments_travel_ts_excl"
  DETAIL:  Key (travel_ts)=(["2013-01-30 19:55:00+11","2013-01-30 22:00:00+11"]) conflicts with existing key (travel_ts)=(["2013-01-30 12:00:00+11","2013-01-30 20:00:00+11")).

The exclusion is set-up exclusively for time ranges specific to each trip_id.

insert into trips (trip_nm) values ('their trip'); -- add other trip

-- segment overlaps with trip_id=1, but the exclusion is setup specific to trips
insert into trip_segments (travel_ts,trip_id) values
                          ('[2013-01-30 19:55,2013-01-30 22:00)', 2);

Note that timestamp ranges cannot be specified backwards (ending timestamp must be equal or higher than the starting one), and their boundaries are either inclusive ([]) or exclusive (()) boundaries, being the default [), i.e. an inclusive starting and exclusive ending time.


How to open a file in MySQL event scheduler? I am currently creating my first event in MySQL:

CREATE EVENT check_for_uploads
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
DO
   // I would like to open a .php file here

However I cannot find information on if its possible to open a file from there. I think I have seen it in some tutorial.

Thank you very much!

You cannot or should not manipulate files within a store procedure (which is basically the same as an event, but with a schedule) due to scope and security reasons.

MySQL events and stored procedures are supposed to only modify or read data using SQL, and not go outside of the database server responsibilities. If MySQL server could read or execute arbitrary files, that would be considered a vulnerability and would be patched (with the exception of LOAD DATA or very few other very specific commands). While certain plugins allow accessing or executing arbitrary data, that is considered an exception, and not the normal way of using MySQL.

Normally PHP applications access MySQL as a resource, not the other way round. You may want to setup a UNIX cron or Window task scheduler at your OS to do what you are trying to do (executing PHP code) instead, rather than using the database.


AUTORECOVERY alternative for SQL Server Is it possible to mimic Oracle SET AUTORECOVERY ON in SQL Server 2008 for applying transaction logs?

RESTORE LOG [DB_NAME] FROM  DISK requires backup name;  in case I have couple hundreds logs I must ensure logs are restored in proper order . Surely, I can sort files by name, but I wonder maybe there is out of the box solution.

Thank you.

Is it possible to mimic Oracle SET AUTORECOVERY ON in SQLServer 2008 for applying transaction logs?

No. SQL Server does not have this option.

What you can do ?

Take often Full/differential backups (as suggested by @Aaron Bertrand). This highlights the importance of having a good backup (and restore) plan which is inturn dictated by your company's RTO and RPO.
Script out and often test your restores. A backup is ONLY good if you are able to restore it. You can use sp_RestoreGene - T-sql version from Paul Brewer. It automates your restores.
Implement Logshipping to a warm standby server. Logshipping is an automated process of backing up your primary database (Full backup followed by T-Logs set as per frequency (minimum is 1 mins)), copying the log files to a shared location on secondary and applying them on the secondary database. This will be a close option to SET AUTORECOVERY ON that you have in Oracle.

  My goal is to deliver logs to standby, but apply them once a day.

This is a perfect case of implementing Logshipping. You can even delay applying transaction logs and have the secondary database in standby mode which can be used for reporting queries.

Alternatively, if you are on sql server 2012 Enterprise and up, then you can look into implementing AlwaysON Availablity groups.


How to delete records without a primary key in a stardard way (i.e. not using ctid) Case of use

We have a log table without a PK with two columns (date, event);

You inserted a row. Then you want to undo the inserted record.

Is there a way to delete that row (without deleting other rows with the same data), that doesn't use postgres only capabilities?

I want a solution that works in another database (it doesn't need to be totally standard, it can be only in just one database: SqLite, Oracle, MySql or SQLServer).

Example:

create table the_log(
  date date,
  event_id integer
);

insert into the_log(date, event_id) values ('2019-09-21',1),('2019-09-21',1);

select * from the_log;

My atempts:

delete from the_log where row_number() over ()=1;

delete from the_log limit 1;

with the_log_2 as (select *, row_number() over () as version from prueba_sin_clave)
delete from the_log_2 where version=1;

I supouse that the answer is No. I want to know if I am wrong or in what documentation I read that I am right.

CAUTION : This can make you lose data, so consider using transaction block.

I think this is the more generic solution:

If you consider that the line you want to delete is:

select * from the_log limit 1

then if you have one column that has a unique value...

delete from the_log
where unique_the_log_column in
(select unique_log_column from the_log limit 1);

If you do not have a unique column, but you have two columns that combined generates a unique value:

delete from the_log
where (col1, col2) in (select col1, col2 from the_log limit 1);

Where any_the_log_column is a column with a unique value in this table.

This will probably delete the first line of the log table in any dbms - if data in column is not repeated, but remember that tables sometimes is not selected ordered in some databases so you must have a id in this table...

Consider creating a id column and increasing it by the numbers of the lines in the table, based on date or something if you don't want to get hurt in the future...


How to check there is enough disk space before overwriting a DB with restore command I need to automate the restore of a database each night from a backup but I want to first check if there is enough disk space. In the below Microsoft link it says the verifyonly command is able to do this "Checking for sufficient space on destination devices" but I can't find any examples online of how to use verifyonly with a destination to test. How can I do this?

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-verifyonly-transact-sql?view=sql-server-2017

but I can't find any examples online of how to use verifyonly with a
  destination to test. How can I do this?

You can use this syntax:

restore verifyonly
from disk = 'V:\SQL_backup\myBak.bak'
with move 'myDb'     to 'C:\myDb.mdf', -- put here your destination
     move 'myDb_log' to 'C:\myDb_log.ldf' -- put here your destination

In case of insufficient space you'll get this error:

  Attempting to restore this backup may encounter storage space
  problems. Subsequent messages will provide details. There is
  insufficient free space on disk volume 'C:\' to create the database.
  The database requires 500254375936 additional free bytes, while only
  57038258176 bytes are available.

This is not an optimal way to check for disk space because this will not only check for a space available and you will wait the whole database backup check to finish.

You can use your own code like this, for example, here I use xp_fixeddrives to check free space:

declare @t table (drive varchar(10), Mb_free bigint);
declare @Mb_free bigint;

insert @t exec xp_fixeddrives;

select @Mb_free = Mb_free
from @t
where drive = 'C';

if @Mb_free < ...


.NET error when trying to create snapshot for transactional Replication I encountered a bizarre prob today while creating a transactional publication on a SQL 2012 server running on Windows Server 2008R2.

I had just had some success creating and initializing some Merge publications (including generating snapshots) on this same server.

With my transactional publication though, around the time the snapshot agent goes to lock tables, it gives me this:

  A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_scriptdropinsreconciliationproc_sqlclr":

  System.TypeInitializationException: The type initializer for >'System.Data.SqlClient.SqlConnection' threw an exception. ---

Here are some things I tried:

Google: surprisingly few results. A reference to KB2840628, which is
not on the system.
Search here: nothing found
Manually create subscription thru the GUI (had
been using a script) - fail
Same as above but publish from a
different database - fail

In these tests I am using ridiculously
   powerful accounts to try to rule out permissions issues.

It's very strange. Any helpful hints would be appreciated.

The error:

  System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnection'

is typically (in my experience!) indicative of a mismatch between the version of .Net on the machine initiating the connection, and the target machine.

Ensure both machines have the same version of the .Net distributable.


How to pick a chunk from the text column in postgresql I have a table ABC with the following 2 columns
id(PK)        Name

1             Verapamil 160mg tablets (X Y Z Pharmaceuticals Ltd) 56 tablet 4 x 14 tablets
2             Zocor 10mg tablets (You & Me Ltd) 28 tablet 2 x 14 tablets
3             Atenolol 100mg tablets (NoName Pharmaceuticals Ltd) 20 tablet 2 x 10 tablets

I would like to select 56 tablet from id=1, 28 tablet from id=2 and 20 tablet from id=3. Is there anyway in Postgres to select just the chunk from the text?

create table abc (id int primary key, name text);
insert into abc values
(1, 'Verapamil 160mg tablets (X Y Z Pharmaceuticals Ltd) **56 tablet** 4 x 14 tablets'),
(2, 'Zocor 10mg tablets (You & Me Ltd) **28 tablet** 2 x 14 tablets'),
(3, 'Atenolol 100mg tablets (NoName Pharmaceuticals Ltd) **20 tablet** 2 x 10 tablets');

If there is a pattern, for example: (1 or more digits) plus the word tablet, you can use a regular expression like this:
select
    substring(name, '([0-9]+ tablet)')
from
    abc;

| substring |
| :-------- |
| 56 tablet |
| 28 tablet |
| 20 tablet |

db<>fiddle here


as variant:

SET @ID = 1;
SELECT
    ID,
    count(*) as count_matched
FROM TableB
WHERE
    ID != @ID
    AND
    CAT_ID IN (SELECT CAT_ID FROM TableB WHERE ID = @ID )
GROUP BY ID
ORDER BY count(*) DESC
LIMIT 2;

add after comment:

MySQL do not support LIMIT in subqueries, so You can:

Handle returned values in application and use it for prepared statements as parameters
Use temporary tables

SET @ID = 1;
DROP TEMPORARY TABLE IF EXISTS tt;
CREATE TEMPORARY TABLE tt as
SELECT
    ID,
    count(*) as count_matched
FROM TableB
WHERE
    ID != @ID
    AND
    CAT_ID IN (SELECT CAT_ID FROM TableB WHERE ID = @ID )
GROUP BY ID
ORDER BY count(*) DESC
LIMIT 2;
SELECT
    TableA.* FROM TableA WHERE     ID IN (SELECT ID FROM tt);

temporary table is session, so You could use it with same name independent for each session.

But if handle returned values in application - prevent table locks, because CREATE TABLE AS SELECT (and INSERT SELECT) - require lock table, with external code - it just 2 independent selects.


Integration: Keep two systems in sync I have a GIS system with 40 tables, ranging from 1,000 to 60,000 rows per table. The tables are the system of record for assets in a municipality.

The GIS assets in the tables get integrated to a Workorder Management System (WMS) on a weekly basis. The integration is based on web services that serve up the GIS tables to the WMS.

Constraint #1:

The integration to the Workorder Management System is multi-purpose.

There is a single asset table in the WMS that gets updated, via cron tasks, with any edits that have been made to the GIS assets (new assets, changed assets, and decommissioned assets). Only assets that have been edited are updated in the WMS.
The integration is also used to dynamically serve up the assets to a web map in the WMS (all of the GIS assets are used in the map--not just the assets that have been edited). The map in the WMS connects directly to the GIS web services--it does not use the records in the asset table or the cron tasks.

Constraint #2:

The WMS cron tasks are notoriously slow. Given my organization's infrastructure, my vendor says that the WMS cron tasks will only be able to sync 150 records per minute.

Testing is ongoing, but we have been told to only sync the records that actually need to be synced (edits) due to the significant performance concerns. In other words, we can't just integrate or copy all records, all the time.
To give you an idea, this is what the cron task process looks like:
REST GIS web service >> JSON object >> Parse the JSON into individual records >> Generate XML for each record >> Process the XML records with Java classes >> Insert the records into the database.

Constraint #3:

GIS data is notoriously messy.

In constraint #2, I mentioned that the records get processed with Java classes. The Java classes check for errors (parent/child, field rules, etc.) and flag any records that fail.

These records do not get integrated into the WMS.
It is up to the GIS teams to correct the errors in GIS tables, then we'll try again in the next integration instance (next week) to sync the GIS records to the WMS.

Question:

Given the constraints above, I think I need to figure out a way to integrate all the GIS assets to the WMS (constraint 1.2), but also flag the records that need to be synced due to edits (constraint 1.1).

For edited assets that failed to sync--I need to retry them in future syncs until they are successful.
And I need to avoid syncing records unnecessarily--due to the performance concerns.

How can I do this?

Are you running flashback database? If you were you could run a process that would start just after the top of every hour. The process would look at each table as it was at the beginning of every hour and do a Merge command between the source and destination databases for a one way sync. You would then be able to replicate all of the tables every hour. If for some reason, this process takes more than an hour, then you would replicated every other hour using the top of every even or odd hours as the starting point. You would be replicating based on consistent data across all tables everytime.

Goldengate would probably work better if you were licensed for it. It would keep the data more current more of the time. Governments should get a higher discount than corporations. It's worth getting a price for Goldengate even if you find out it costs too much.


See direct (instead of indirect) info about rented book in my "actors_directors" talbe dropdown list - Access365 I'm building a database of "actors_directors" who are renting "books" from my library - a kind of exercise to learn database basics :-)

This is how my "actors_directors" table looks like. They are the customers who are renting the books from my library. Index field is a primary key in this table.

There are books listed in different table called "books" and similarly "Index" field is a primary key in this table.

Finally I have all the instances of the book being rented in my third table "checkouts". Please ignore the last three columns there. Similarly "Index" field is a primary key in this table.
"ForeignKeyCustomer" column refers to Index column in "actors_directors" table and its one-to-many relationship.
"ForeignKeyBook" column refers to Index column in "books" table and its one-to-many relationship.

These are relationship I've set for my tables.

Now after I unfold the pluses in the "actors_directors" table like it's shown in the next picture, there are not exactly the info I wanted to see.
What I see is the number of the book, some specific actor rented, while I'd like to see the title of this book.

Instead of info about "ForeignKeyBook" - the book's number, I'd like to see it's Title in this place. But the info about the title is in different table. Can U help me with that?

I don't want to see the book's number, because it tells me nothing.

I want to see the book's title, that is in different table, but also the "CheckOutDate" and "RentPeriod" and "RentFee" from this "checkouts" table, that's related/linked in my current situation (this screenshot).

Thanks in advance my friends.

You can make foreignKeyBook field as a compobox with coulumn count = 2,  column widths =0"1", and row source is a query of books.index and books.title in that order

Or you could easily make this in the beginning while designing the checkout table by setting the data type of field ForeignKeyBook to lookup withered.. and following the lookup wizard window


SQL Server Services empty in SQL Server configuration As I upgraded from Windows 8.1 to Windows 10, I noticed that the SQL Server Services in SQL Server Configuration Manager is empty. SQL Server still works perfectly well at the moment. SQL Server 2014 is installed on a local machine.

I have read blogs - https://www.mssqltips.com/sqlservertip/2492/why-is-sql-server-configuration-manager-missing-services/ and
http://www.sqlservercentral.com/Forums/Topic1469391-1292-1.aspx and still could not resolve the issue.

I backed up databases, uninstalled and re-installed and yet the node is still empty. What should I do?

Updated with screen shot

SQL Server Configuration Manager Screen

I did the upgrade on my laptop with SQL Server 2008 R2 and SQL Server 2012 already installed. The issue I had was that SSCM actually disappeared from the Start Menu, if after repair it would not put them back. I ended up finding them under the Windows root directory, but have since done a complete reinstall of Windows 10 on my laptop, and reinstalled SQL Server.

I would expect you are looking at a similar situation that is going to require a reload of Windows 10. You might try just doing the refresh, where it does not completely remove everything...but I doubt that is going to work. I did a factory reset where I went back to Windows 8.1, then forced an upgrade to Windows 10. Which I might add, doing that seemed to have improved Windows 10 at least on my laptop (Lenovo Y 40-80) compared to running it after the upgrade.

One last chance you might attempt is to install the SSMS preview (standalone of SSMS) from here.


I try to use select union with order by and now i can select it but it seem like it not order by date with what i expect why? SELECT *
FROM (
    SELECT sale.NAME AS "Name"
        ,sale.responsible AS "Responsible:data:200"
        ,sale.action_type AS "Action Type:data:200"
        ,sale.id_customer AS "Id_Customer"
        ,sale.workflow_state AS "Workflow:data:200"
        ,sale.close_date AS "Close Date:data:200"
        ,sale.modified_by AS "Done By:data:200"
    FROM `tabsales_action` sale
    WHERE sale.workflow_state = 'Done'
    ORDER BY sale.close_date DESC
    ) `tabsales_action`

UNION

SELECT *
FROM (
    SELECT mfi.NAME AS "Name"
        ,mfi.responsible AS "Responsible:data:200"
        ,mfi.action_type AS "Action Type:data:200"
        ,mfi.id_customer AS "Id_Customer"
        ,mfi.workflow_state AS "Workflow:data:200"
        ,mfi.close_date AS "Close Date:data:200"
        ,mfi.modified_by AS "Done By:data:200"
    FROM `tabmfi_loan_action` mfi
    WHERE mfi.workflow_state = 'Done'
    ORDER BY mfi.close_date DESC
    ) `tabmfi_loan_action`

UNION

SELECT *
FROM (
    SELECT credit.NAME AS "Name"
        ,credit.responsible AS "Responsible:data:200"
        ,credit.action_type AS "Action Type:data:200"
        ,credit.id_customer AS "Id_Customer"
        ,credit.workflow_state AS "Workflow:data:200"
        ,credit.close_date AS "Close Date:data:200"
        ,credit.modified_by AS "Done By:data:200"
    FROM `tabin_house_credit_action` credit
    WHERE credit.workflow_state = 'Done'
    ORDER BY credit.close_date DESC
    ) `tabin_house_credit_action`

UNION

SELECT *
FROM (
    SELECT service.NAME AS "Name"
        ,service.responsible AS "Responsible:data:200"
        ,service.action_type AS "Action Type:data:200"
        ,service.id_customer AS "Id_Customer"
        ,service.workflow_state AS "Workflow:data:200"
        ,service.close_date AS "Close Date:data:200"
        ,service.modified_by AS "Done By:data:200"
    FROM `tabservices_planning` service
    WHERE service.workflow_state = 'Done'
    ORDER BY service.close_date DESC
    ) `tabservices_planning`

UNION

SELECT *
FROM (
    SELECT account.NAME AS "Name"
        ,account.responsible AS "Responsible:data:200"
        ,account.action_type AS "Action Type:data:200"
        ,account.id_customer AS "Id_Customer"
        ,account.workflow_state AS "Workflow:data:200"
        ,account.close_date AS "Close Date:data:200"
        ,account.modified_by AS "Done By:data:200"
    FROM `tabaccounting_action` account
    WHERE account.workflow_state = 'Done'
    ORDER BY account.close_date DESC
    ) `tabaccounting_action`

Looks like you are sorting by close_date column in all the tables in DESC order then UNION. Instead of this approach SELECT UNION all the tables and, in the final result you can ORDER BY the Close Date:data:200 as DESC.

The working query will be:

SELECT T.*
FROM (
    SELECT sale.NAME AS "Name"
        ,sale.responsible AS "Responsible:data:200"
        ,sale.action_type AS "Action Type:data:200"
        ,sale.id_customer AS "Id_Customer"
        ,sale.workflow_state AS "Workflow:data:200"
        ,sale.close_date AS "Close Date:data:200"
        ,sale.modified_by AS "Done By:data:200"
    FROM `tabsales_action` sale
    WHERE sale.workflow_state = 'Done'

    UNION

    SELECT mfi.NAME AS "Name"
        ,mfi.responsible AS "Responsible:data:200"
        ,mfi.action_type AS "Action Type:data:200"
        ,mfi.id_customer AS "Id_Customer"
        ,mfi.workflow_state AS "Workflow:data:200"
        ,mfi.close_date AS "Close Date:data:200"
        ,mfi.modified_by AS "Done By:data:200"
    FROM `tabmfi_loan_action` mfi
    WHERE mfi.workflow_state = 'Done'

    UNION

    SELECT credit.NAME AS "Name"
        ,credit.responsible AS "Responsible:data:200"
        ,credit.action_type AS "Action Type:data:200"
        ,credit.id_customer AS "Id_Customer"
        ,credit.workflow_state AS "Workflow:data:200"
        ,credit.close_date AS "Close Date:data:200"
        ,credit.modified_by AS "Done By:data:200"
    FROM `tabin_house_credit_action` credit
    WHERE credit.workflow_state = 'Done'

    UNION

    SELECT service.NAME AS "Name"
        ,service.responsible AS "Responsible:data:200"
        ,service.action_type AS "Action Type:data:200"
        ,service.id_customer AS "Id_Customer"
        ,service.workflow_state AS "Workflow:data:200"
        ,service.close_date AS "Close Date:data:200"
        ,service.modified_by AS "Done By:data:200"
    FROM `tabservices_planning` service
    WHERE service.workflow_state = 'Done'

    UNION

    SELECT account.NAME AS "Name"
        ,account.responsible AS "Responsible:data:200"
        ,account.action_type AS "Action Type:data:200"
        ,account.id_customer AS "Id_Customer"
        ,account.workflow_state AS "Workflow:data:200"
        ,account.close_date AS "Close Date:data:200"
        ,account.modified_by AS "Done By:data:200"
    FROM `tabaccounting_action` account
    WHERE account.workflow_state = 'Done'
) AS T
ORDER BY T.`Close Date:data:200` DESC


SSIS Data Flow Error Output runs all the time I've a SSIS Data Flow Task. Within which i've souce flat file and OLEDB destination. I've an error output sent to redirect row on OLEDB Destination.
The problem is that the redirect row always run even though there's no error. Is this a bug with SSIS package? If someone could help, will be appreciated.
e.g.

I am looking for the article that describes this behaviour better than I'll do here and will update if I can find it. I thought it was by Kirk Haselden or John Welch but no so luck.

If the batch size is set to 0, default, either the entire data flow will make it in or none of it. Generally, this is what you want as it gives the best performance and consistency.

If you have an exception in in the commit size (full or otherwise) and have set redirect error rows, the error row(s) and all the valid ones will all be mingled in the exception flow.

If your desire is to get all to "good" rows in and spit the "bad" ones to a staging area, the pattern described in the article is to try and commit successively smaller batches into the destination table. Eventually, you'll partition the inserts down to a valid sized batch or find the offending row(s) which are then stored to a staging area for review/cleanup.

The implementation is to have serial error redirect OLE DB destinations with decreasing rows per batch commit sizes, starting at say 10,000 and having the penultimate one with a size of 1. The last OLE DB destination would be to the staging/error table.

All of this assumes that you're using "fast load" version for the OLE provider. If you're "table or view" or "table or view from variable" you're already doing singleton inserts (and probably killing your performance) so the redirect stuff isn't needed to find a bad row.


MySQL InnoDB log files on different location I am using MySQL version: 5.5.38

There are following parameters that can control InnoDB log files:

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

innodb_log_group_home_dir - location of InnoDB log files.

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

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

Setting the two files on two different disks may not help you, because speaking generally, they are only written serially:

  The redo log files are used in a circular fashion. This means that the redo logs are written from the beginning to end of first redo log file, then it is continued to be written into the next log file, and so on till it reaches the last redo log file. Once the last redo log file has been written, then redo logs are again written from the first redo log file.

(from the InnoDB blog).

Setting them both on a separate disk from the tablespaces is a good idea, as if your problem is lots of writes, as having exclusive IO will be an advantage on magnetic disks. They are also good candidates for SDDs because (again, if you have problems with transaction log IO) they are relatively small and usually written synchronously to disk, unlike the indexes and data.

Another way to load-balance IO is to use a RAID level with improved throughput. RAID 1 will provide you better reads and writes thanks to its striping, although the recommended setup is RAID 10 for redundancy.

One disadvantage of being on separate disks is that in most cases, you will lose the option of performing consistent MySQL datadir snapshots.


Disabling sql services while performing maintenance on SQL Server 2008 R2 cluster Our storage team is performing updates on an MPIO driver for our SQL Server 2008 R2 cluster.  They suggested disabling the sql server service on the node they are doing the update on - will disabling the sql server service interfere with the configuration of the cluster or have no impact?

Disabling the service will not impact the cluster configuration. I would disable the node in possible owners as well to make sure that the server will not try to failover.


How to automate the detection of the column type of a csv file for a table creation script? I am new to pgAdmin and Azure database. I have a huge csv file with around 220 columns and I want to create a table out of it in pgAdmin4 to push it to Azure.

However I don't know how to detect automatically the types of columns.

Here is an exemple :

IDI GKID    S01Q01  S02Q01_Gender ...
100093  enq030059569748fc89091fdd91cc337cac44eca90  Yes, I agree    Female ...

I'm doing the script to create the table. However, given the number of columns I would like to automate it to get the script that would allow me to add the csv to the database in pgAdmin4 for Microsoft Azure.

Then, after transposing the csv header, I get:

    IDI
    GKID
    S01Q01
    S02Q01_Gender
    ...

I'm doing the script to create the table. However, given the number of columns I would like, in the best case, to automate the detection of the type of column to be able to write it in the right part, in the worst case to put a generic type of the type TEXT.

So far, I've tried

output = ""
file_name = "columns.txt"
string_to_add = " TINYTEXT,"

with open(file_name, 'r') as f:
    file_lines = [''.join([x.strip(), string_to_add, '\n']) for x in f.readlines()]

with open(file_name, 'w') as f:
    f.writelines(file_lines)

It gives me back:

IDI TINYTEXT,
GKID TINYTEXT,
S01Q01 TINYTEXT,
S02Q01_Gender TINYTEXT,
...

And, then, I can do:

CREATE TABLE my_table (
IDI TINYTEXT,
GKID TINYTEXT,
S01Q01 TINYTEXT,
S02Q01_Gender TINYTEXT,
...

But I'm not sure that this enough to make a table able to receive my csv file.

there are command line tools that do field type inferencing.

One is xsv https://github.com/BurntSushi/xsv/

Running this command on this (https://gist.githubusercontent.com/aborruso/3b1af402f0d2ed49465f218d19be81d9/raw/c0e95b320924e9e49902633d16e7ab253046ca16/input.csv)

xsv stats input.csv --everything | xsv table

you have

field       type     sum                 min       max         min_length  max_length  mean                stddev              median              mode  cardinality
id          Integer  5050                1         100         1           3           50.5                28.86607004772212   50.5                N/A   100
first_name  Unicode                      Annabal   Willabella  3           11                                                                      N/A   98
last_name   Unicode                      Albinson  Zaniolini   3           13                                                                      N/A   100
f           Float    2063.2419999999984  0.2656    51.1245     4           7           20.632419999999996  12.603955889545158  17.930799999999998  N/A   100

Using csvkit (https://csvkit.readthedocs.io/en/latest/index.html) and running

csvstat --csv input.csv

you will have

column_id column_name type   nulls unique min   max    sum       mean   median stdev  len freq
1         id          Number False 100    1     100    5,050     50.5   50.5   29.011 -   1, 2, 3, 4, 5
2         first_name  Text   False 98     -     -      -         -      -      -      11  Caren, Weylin, Heall, Flori, Lydia
3         last_name   Text   False 100    -     -      -         -      -      -      13  Saxby, Joderli, Kleinzweig, Coyle, Kleinplac
4         f           Number False 100    0.266 51.124 2,063.242 20.632 17.931 12.667 -   5.356, 12.596, 32.1245, 5.32, 0.2656

In csvkit you have also

csvsql -i postgresql input.csv

that gives you

CREATE TABLE input (
        id DECIMAL NOT NULL,
        first_name VARCHAR NOT NULL,
        last_name VARCHAR NOT NULL,
        f DECIMAL NOT NULL
);


Backend Table/Row Locking Structure using CTE Queries This question is an extension of a previous question that I asked.  Table Locking When using Unions

With the same query we are using that is causing blocking the various queries used in these UNIONS are CTE queries.

For example:

CREATE TABLE #TempTable (
    [Columns]
)

WITH CTE1 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE2 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE3 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE4 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE5 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE6 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE7 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
)

INSERT INTO #TempTable
SELECT * FROM CTE1
UNION
SELECT * FROM CTE2
UNION
SELECT * FROM CTE3
UNION
SELECT * FROM CTE4
UNION
SELECT * FROM CTE5
UNION
SELECT * FROM CTE6 WHERE [Where-Condition]
UNION
SELECT * FROM CTE7

Assuming this particular scenario (and we do not have any explicit transactions declared) does the physical table locking structure change, because we are using CTE queries?

When the SELECT for CTE1 is finished as part of the UNION, are the physical tables still locked or are the locks still released?

Based on the answer from my previous question, I was under the impression that in the context of a UNION when the SELECT for one of the various SELECT statements completes the tables are released.  Is that incorrect?

We have not modified the isolation level for this transaction.  As I understand it, that means we are using READ_COMMITTED. The question is still applicable with just 2 SELECT statements. I am trying to get an understanding on how this functionality works.  The particular query I am working with uses 7, which is the only reason I specified 7.

No, the locks will not change because of CTEs. What you have shown are non-recursive CTEs. These are just another way of introducing a sub-query into the SQL statement. The query
with some_CTE as
(
  select < whatever > from < something >
)
select * from some_CTE;

is semantically identical to
select * from
(
  select < whatever > from < something >
) as some_alias;

As the query optimizer is free to rearrange your statement into any logical equivalent it is likely these two statements will end up with identical execution plans.  (For very complex queries, where the CTE or the main query has many joins, or the CTE is referenced many times, the execution plans may be different, but this is an artefact of the optimization, not a fundamental property of how CTEs work.)
This paragraph is tangential to your main point about locking, but worth knowing. You say "When the SELECT for CTE1 is finished.." It doesn't necessarily work like that. The execution may proceed in any manner that satisfies the meaning of our query but does not have to follow the sequence in which it was written. See here and here for examples of how execution order may differ to written order.
As to your main question "I was under the impression that .. when the SELECT .. completes the tables are released. Is that incorrect?" I'll refer you to this Microsoft page:

The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:
If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared lock type determines when it will be released. Row locks are released before the next row is processed. Page locks are released when the next page is read, and table locks are released when the statement finishes.

Here is another explanation which may explain it better. Both sources make clear that the granularity of locking (row, page or table) affect when the lock is released. There are many things that affect this, including system configuration and lock escalation.
I'll note also that your example uses UNION. This removes duplicates from the results. You may get different lock behaviour using UNION ALL, but that leaves duplicates and could return more rows, depending on your data.


Why use master to create a database? I have a short question, why do I use use master; to create a database?
Here is the example from the Microsoft documentation

USE master ;
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
    FILENAME = 'C:\Program Files\...\saledat.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
    FILENAME = 'C:\Program Files\...\salelog.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB ) ;

It is absolutely not a requirement in this very specific case, but it is a requirement in many other scenarios. If you're creating a database called Sales, and you arelady have a database called Sales, you'll need to change your database context before you:

Restore with replace; or,
Drop the current database and then:

Create from scratch; or,
Create for attach.

There are plenty of other scenarios outside of database creation that also require either (a) not being in the context of the current database, or (b) being in the context of master specifically (or at least not a specific database), and many of these things you may be doing during or around creating databases:

Setting a database to a different state, like single_user
Preventing errors when a script has a USE command but that user database may be offline or otherwise inaccessible
Granting server-level permissions like CREATE DATABASE
Granting server-level role membership
Marking a module as a system object (sp_MS_marksystemobject) or as a startup procedure
Certain types of certificate, server audit, and Availability Group operations

Probably a slew of other things. USE master; isn't always necessary, but sometimes it is, and it doesn't hurt to always execute server-level commands from that database.


Special Character in password I'm Trying to connect to a remote DB using Sqlplus, but  the password contains a special character (!).
The SID is working fine, but Sqlplus is complaining about it when I use the ! in the password

/usr/lib/oracle/18.3/client64/bin/sqlplus -s bloth/hods!@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=remoteServer)(Port=1521))(CONNECT_DATA=(SID=SUDFR)))

  ORA-12505: TNS:listener does not currently know of SID given in connect

on the other hand, when I remove the special character I get

  ORA-01017: invalid username/password; logon denied

any idea how to fix that?

Works with single quotes.

[oracle@o71 ~]$ sqlplus u1/u1!@(description=(address=(protocol=tcp)(port=1521)(host=o71.balazs.vm))(connect_data=(service_name=min18_o71)))
-bash: !@: event not found
[oracle@o71 ~]$ sqlplus 'u1/u1!@(description=(address=(protocol=tcp)(port=1521)(host=o71.balazs.vm))(connect_data=(service_name=min18_o71)))'

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Mar 2 09:07:54 2019
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sat Mar 02 2019 09:07:15 +01:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0

SQL>


Error using pg_restore I am using the following guide to migrate a database from my Windows 10 machine to my Managed Database on Digital Ocean.
https://www.digitalocean.com/docs/databases/postgresql/how-to/import-databases/
Unfortunately, I am having some issues restoring my database from local to the remote server.  I am using the following command to perform my local dump:
pg_dump -U username -Fc dbname > filelocation.pgsql

This prompts for my password. Upon entering the password, pg_dump executes, and I am greeted with the following warning multiple times:
pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used

Following this, I attempt the pg_restore using the following command:
pg_restore -d 'my_connection_URI' filelocation.pgsql

However, I am greeted by the error:
pg_restore: [archiver] input file does not appear to be a valid archive

I believe that by specifying -Fc it should be a valid archive, so I can only presume that it has something to with the warnings I received, although I can't be sure.
Does anyone have any ideas of how to fix this?

Ok, looks like I had to get rid of the pipe.  Using the following pg_dump command worked for me:
pg_dump -U username --format=c --file=filelocation.sqlc dbname


Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates We have an unusual database structure (I leave the reasoning for another question), that in its current form requires a regular truncation and recreation of a lot of rows (about 1 million).

We're working to re-factor it. However, as a short term solution, are there any query hints that could help in this situation?

Is there a way of making it so if reads are occurring on the table being truncated/repopulated that it ignores this fact i.e. it'll just read the data before the truncation began?

We're trying to avoid locks (we think).

I realise this isn't a long term solution, but looking into possible solutions.

Any solution proposed, or any solution you could envision for the mater, would suffer from the same issue: coalesce point around the schema modification lock.

Whether you do a truncate, or an alter table ...switch, or sp_rename, alter schema whateverver, it doesn't matter. They're all the same solution in disguise. And all will have a point in time when the table that is being truncated/ switched/ renamed/ transfered must be locked with SCH-M. This is, in theory, an 'instantaneous metadata only operation', but in practice the SCH-M cannot be granted until all the other existing locks on the table are released, which means any query that is already running on the table will have to finish first. Fine, the operation will wait until the existing queries 'drain'. The issue is that no other query can get in until the pending SCH-M is granted and released. This is a well know lock starvation issue mitigation. So all of the sudden all your new queries will freeze until the very last old query finishes, then the truncate/ switch/ transfer occurs, and the new queries finally resume. This is not noticeable if all queries last 1 second, but if the queries are reports/ analysis that last on average minutes the effect is very visible, specially so if you have a long tail.

The good news is that you can stop worrying about how to do what you're trying to do: it is impossible. The bad news is that you have to fix the application now.


How is the password file accessed when it's on an ASM diskgroup and required to connect to ASM? When a RAC is turned on, it will try to accessing the OCR registry which is located on ASM.
ASM also holds the asm password file.
However to open ASM, it needs to access the password file.

If the password file is missing or corrupted etc... when turning on the CRS stack, this happens:

2016-01-12 12:00:51.317680*:kgfo.c@1115: kgfoSaveError: ignoring existing error:
ORA-01017: invalid username/password; logon denied
ORA-15077: could not locate ASM instance serving a required diskgroup

Oracle's solution to this is the following:

You can make a backup of the Oracle ASM password file, and if the Oracle ASM
password file is lost or the disk group becomes inaccessible, then you can
restore the backup password file.

Even if ASM is working, how can it access the password file which is inside ASM if it needs a password to access ASM?

ASM metadata describes the location of the password file on the ASM disk. With that information, even you can read it directly from the disk, with the help of kfed (which is installed with the Oracle binaries) and OS specific tools for reading the disk directly, for example dd, without using ASM. Same goes for the SPFILE (since 11g).
More about this topic:
ASM spfile in a disk group
The ASM password directory
And you do not need a password file to start the ASM instance, that is needed only for remote connections.


How to mark a Sybase ASE DB as offline for startup I need to load a database dump into a target database, on Sybase ASE 16. In order to do that, the target database must be offline. Sybase ASE doesn't have a command to put a database offline, so my only possibility is to stop the DB server and restart it, taking care that the target database is not brought online during startup. This is my question: which command/option do I need to set so that a specific database is not brought online at server startup? I couldn't find it in the official documentation :-(

Thanks to Learning_DBAdmin for correcting me: the database does not have to be offline, the load process was failing due to remaining connections of other users on this target database. After killing the offending connections and putting the database in single user mode, it was possible to load a dump into it.


SQL Server Full-Text Search: combining proximity term and thesaurus Scenario

I am developing a full-text search over a products table.

The full-text indexed field of th table contains a brief description of the product, the name of the category and the product code.

I succeed in querying the table using the CONTAINS predicate with NEAR terms:

/* @mySearchTerm is a generic proximity term such as '"hdd*"~"sata*""' */
SELECT myFields
FROM SEARCHABLE_PRODUCTS
WHERE CONTAINS(myIndexedField, @mySearchTerm)

Then I try to enhance the search, leveraging on the thesaurus feature in order to enable synonyms search. E.G. 'hard disk', 'hard disk drive', 'hdd'. So I have also configured
the thesaurus xml file, and I succeed in querying the table using the FORMSOF term.

    <expansion>
        hdd
        hard disk
        hard disc
        harddisk
    </expansion>

Issue

I do not succeed in combining the NEAR and the FORMSOF terms in the CONTAINS predicate:

/* @mySearchTerm is a generic proximity term such as '"hdd*"~"sata*""' */
SELECT myFields
FROM SEARCHABLE_PRODUCTS
WHERE CONTAINS(myIndexedField, ' FORMSOF (THESAURUS, @mySearchTerm) ')

A syntax error is returned. As to the MSDN reference, it seems that it is not possible. Is there any solution or workaround?

I do not believe that is a supported feature of SQL Server full text searching.  That would require a wildcard resolution to words and then a thesaurus lookup of each matching word to gather the thesaurus terms.

This basically maps to a pretty complex query: some one of a group of prefixed words is very near to some one of another group of prefixed words which all then go through a thesaurus lookup to provide even more words.  Based on previous experience, that is just not supported.

(I see online that you have asked this elsewhere in the last few months, but without any answers, so I hope that this helps.)

I believe that you can create something useful for your query, but it probably requires externalizing the thesaurus entries by doing something like the following:

DECLARE @FirstGroup TABLE (AGroup NVARCHAR(50));
INSERT INTO @FirstGroup VALUES ('hdd');
INSERT INTO @FirstGroup VALUES ('hard disk');
INSERT INTO @FirstGroup VALUES ('hard disc');
INSERT INTO @FirstGroup VALUES ('harddisk');

DECLARE @SecondGroup TABLE (BGroup NVARCHAR(50));
INSERT INTO @SecondGroup VALUES ('sata');
INSERT INTO @SecondGroup VALUES ('msata');
INSERT INTO @SecondGroup VALUES ('esatap');

DECLARE @SearchString NVARCHAR(2000);
SET @SearchString = ''

SELECT @SearchString = @SearchString + '("'+AGroup+'"~"'+BGroup+'") OR '
FROM @FirstGroup CROSS JOIN @SecondGroup

SELECT @SearchString = LEFT(@SearchString, LEN(@SearchString) - 3);

SELECT * FROM dbo.TBL_NAME
 WHERE contains(field_name,@SearchString);

So if you load the tables with the candidate thesaurus entries and make the OR connections in the code, it should work just fine.

You did not mention which version of SQL Server you are running, but if you are using SQL Server 2012, you might want to use the newer and more powerful syntax.  However, this article also has hints about the legacy NEAR context.
http://technet.microsoft.com/en-us/library/ms142568(v=sql.110).aspx


Aligned index column order Situation: There is one SQL Server (2014) database that has multiple partitioned tables, that all share the same partitioning strategy. Tables have a partition per unique [PartitionKey]. Every [Id] is unique and the column [PartitionKey] is reused about 20 million times per table, but each unqique value isn't spread over different partitions. [PartitionKey] is a formatted date(yyyyMM) stored as an int.

Table Definition

CREATE TABLE [dbo].[Table1](
    [Id] [uniqueidentifier] NOT NULL,
    [PartitionKey] [int] NOT NULL,
    [CreationDate] [datetime] null,
CONSTRAINT [PK_NC_Table1] PRIMARY KEY NONCLUSTERED
(
    [Id] ASC
))

Considering the following queries:

Query 1: Search for unique result (most important)

SELECT t1.id, t2.PartitionKey
FROM Table1 t1
WHERE t1.id = @id and t1.PartitionKey = @partitionKey

Query 2: Search for a range of data within a partition

SELECT t1.id, t2.PartitionKey
FROM Table1 t1
WHERE t1.PartitionKey = @partitionKey
AND t1.CreationDate > @creationDate

Would the order of a partitioned Clustered index matter? If so, how does it affect performance and what order would be best to suit my case?

Aligned Index: [Id] first

CREATE CLUSTERED INDEX [CI_Id_PartitionKey] ON [dbo].[Table1]
(
    [Id],
    [PartitionKey]
) ON [PartitionKeyScheme]([PartitionKey])

Aligned Index: [PartitionKey] first

CREATE CLUSTERED INDEX [CI_Id_PartitionKey] ON [dbo].[Table1]
(
    [PartitionKey],
    [Id]
) ON [PartitionKeyScheme]([PartitionKey])

You need to think about your partitions as if they are separate tables.

A clustered index on a partitioned table effectively has the partition number as its first key. For you, with a partition per unique PartitionKey, the clustered index will essentially have that column in the front of it. Or as good as...

I'd expect having Id first would feel better, in case you don't know the month and need to search all partitions. It'll seek on each partition rather than scan them.


A SQL Server database backup/restore issue A backup/restore issue has taken place on one of our client’s server.

We use Microsoft SQL Server 2014 installed on Microsoft Windows Server 2012.

Problem results: - Critical data loss, inability to restore the database.

Problem details: - We used to perform scheduled ‘encrypted’ backups from the client’s database. The database encryption is implemented using MASTER KEY ENCRYPTION BY PASSWORD.

Using the script:

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '#W0rrtyldOrtf$$ByryackupP@trSs0rd#';
GO
CREATE CERTIFICATE DBBackupEncryptCert
   WITH SUBJECT = 'DBBackupEncryptCert';
GO

The problem occurred after a server hardware failure led to a corruption of the server HDD that contains our databases and its backups encryption certificates. Now, we have an encrypted backup of the database, but its certificate was on the damaged HDD and was lost.

We also have a one-week earlier non-encrypted backup of the database. But the one-week lost data is very critical to us and to the client.

Our trials

We tried to recover the damaged HDD physical files, but this option was not possible
We have tried to find a way to re-generate the backup certificate using an older backup of the master database by creating an encryption certificate out of it with the same password, but this attempt did not work
We thought that if we could somehow re-create the certificate using the old backup, this would help us decrypt the database backup file.

So, kindly I ask you for help. If there is a way we can restore this database using the current encrypted backup without the certificate key, this would be appreciated.

And please, do not hesitate to ask for any further information or resources to let you know more about the issue for resolution.

Thank you

If you have a copy of the master database which holds the DBBackupEncryptCert then you'll want to restore that and grab the copy of the certificate, then import it into your current instance for use.

  If there is a way we can restore this database using the current encrypted backup without the certificate key ...

That would defeat the whole purpose of encrypting the backup - so, unfortunately, the answer is that there is no way. There is no back door or gated access to get the data, it is encrypted by the certificate of which no one has a copy but you. That's what makes it safe and secure (well, partially).

  We also have a one-week earlier non-encrypted backup of the database.

Without the certificate, this is what you're going to be able to recover.


Multi-source replication from older masters I have several different production databases running MariaBD 5.5. I'd like to setup a new machine, with MariaDB 10.0 in order replicate all of those databases to a single MariaDB instance using multi-source replication.

I know that multi-source was only introduced in MariaDB 10.0, but does it require that both master and slave are running the same version, or will it be sufficient for only the slave to be on MariaDB 10?

Updating all of the production databases to v10.0 isn't really an option at this stage unfortunately.

From what I can find on MariaDB's website, multi-source replication from MariaDB 5.5 to MariaDB 10.0 looks like it should not be an issue.

https://mariadb.com/kb/en/mariadb/upgrading-from-mariadb-55-to-mariadb-100/

"There are no changes in table or index formats between MariaDB 5.5 and MariaDB 10.0, so on most servers the upgrade should be painless."

They even mention considering Multi source replication on this same page (above).

This link provides incompatibilities with MariaDB 5.5 that you should watch out for: https://mariadb.com/kb/en/mariadb/multi-source-replication/

Incompatibilities with MariaDB/MySQL 5.5

max_relay_log_size is now (almost) a normal variable and not automatically changed if max_binlog_size is changed. To keep things compatible with old config files, we set it to max_binlog_size at startup if its value is 0.
You can now access replication variables that depend on the active connection with either GLOBAL or SESSION.
We only write information about relay log positions for recovery if innodb-recovery-update-relay-log is set.
Slave_retried_transactions now shows the total count of retried transactions over all slaves.
The status variable Com_slave_start is replaced with Com_start_slave.
The status variable Com_slave_stop is replaced with Com_stop_slave.
FLUSH RELAY LOGS are not replicated anymore. This is not safe as connection names may be different on the slave.


How to get output from trigger on linked server Fair warning, I know this isn't the best way to do this, but I'm trying to extend some existing code without needing to do a rewrite.

Anyway, I've got two SQL Server 2012 installs, one local in my office and another at a remote site.  The server at the remote site has a linked server setup for my local system.

An application at the remote site inserts a record to a table on the linked server which has an INSTEAD OF trigger.  That trigger has a select statement at the end which returns a single integer value.  If I do the insert on the local box in SSMS I get the integer as output.  If I do the insert on the remote box all I get is 1 row(s) affected.

Is there anyway I can get the trigger's output passed back to the linked server or am I going to need to rewrite as a stored procedure?

TEXT BELOW HERE IS BASED ON CLARIFICATION REQUESTED IN COMMENTS

All connections are made in the security context of a single user on the remote side. The output appears as expected if I use EXECUTE AS USER on the local server to do the insert rather than do it across the link, so it isn't a permission issue with the account.

Server options are:

Collation Compatible=False
Data Access=True
RPC=True
RPC Out=True
Use Remote Collation=True
Collation Name=(blank)
Connection Timeout=0
Query Timeout=0
Distributor=False
Publisher=False
Subscriber=False
Lazy Schema Validation=False
Enable Promotion of Distributed Transaction=True

Have you thought about modifying your trigger to put that integer into a table?  Include some identifying information, say the primary key from the table you are inserting into.  Then just query the table after the insert.


Tune postgres 9.1 to speedup deletes I need to tune my postgres db to speed up delete queries. I cant't optimize my db structure yet, so I'm looking for options available on server level for postgres 9.1.

I've seen in older posts the parameter max_fsm_pages, however I can't find the parameter in my postgressql.conf, is this parameter deprecated?

Are there other parameters I can tune?

max_fsm_pages was eliminated in version 8.4.  Free space tracking has gotten a lot smarter and more automatic.

Performance of deletes is going to depend on a lot of things.

If there are any foreign keys which reference this table, you should check whether there is an index on the referencing column(s).  Without that, each DELETE will cause a scan of the referencing table, to check that you're not causing any rows to be orphaned.
Like other database modifications, it is generally more efficient to group actions into larger transactions where reasonable, since there is overhead to each database transaction.
If you do have many transactions, you might want to consider setting synchronous_commit to off on the connection you're using for that work.
If you are deleting a significant fraction of the table, and you can do this during some maintenance window, you might consider dropping indexes not needed to find the rows to be deleted, and create them again after the mass delete.


Optimizing the table to hold a million records I want to use a table with a column that should have a million records and it has to retrieve the results very fast.

Im going to use it in a query like below

  Database : MySQL

select tags from tags_master where tags like 'A%';

Im trying to use it in typeahead. i tried with sample data, but for a million entries it is taking so much time.

I already tried appying indexes on that field. is there any other trick to optimize it further for typeahead purposes ?

Thanks in advance.

The possible solution is to limit the resulting set to 10-20 rows as well as sorting not only alphabetical but also by frequency. There are vocabularies exists that contains words along with the probability to be met in the text. May be you have to build your own based on the query statistics. That is called ranking and is used by the search engines. In the simpliest case you have the table like that:

+------+------+
! word ! prob !
+------+------+

and the query is :

SELECT word
  FROM table
 WHERE word LIKE a%
 ORDER BY prob DESC
        , word ASC
 LIMIT 10
;