Is there any advantage to putting some columns in another table, or should I just put them in the same table? I have a SQLite database with about 50 tables in it. The largest table contains location data and it has 6,000,000 rows.
An example of some of the columns in this table are:
location_contrivedkey --(unique identifier for each location; 1, 2, 3, 4, 5, etc.)
location_coordinates
location_status --(is there a building in this location)
location_rating --(1-10 based on certain factors)

I now realize I need about 30 more specific rating type values for each location. For example:
location_rating_crime
location_rating_weather
location_rating_tourism
location_rating_income
location_rating_jobs

Each location will have a value in each of these columns. None will be blank.
Questions
Is there any reason to put them into another table and join on the location_contrivedkey, or should I just add them all to the current table?
I read somewhere about queries being slower the more columns you have in a table? Is that true?
Also, is there a 2,000 column limit?
Should I be concerned about this approach?

If I were you, I would include the new fields within the location table - while at the same time making considerable use of Declarative Referential Integrity.

I would do something like this (I haven't included all of your new fields - you can check out the fiddle here):

Create and populate two tables, crime and weather:

CREATE TABLE crime (cr_rating VARCHAR (25) PRIMARY KEY);
INSERT INTO crime VALUES ('Low'), ('Medium'), ('High');

and

CREATE TABLE weather (weather_type VARCHAR (50) PRIMARY KEY);
INSERT INTO weather VALUES ('Scorching'), ('Sunny'), ('Mild'), ('Overcast'), ('Drizzle'), ('Rainy'), ('Stormy'), ('Hurricane');

Then create and populate a job table - see discussion below:

CREATE TABLE job (job_id INTEGER PRIMARY KEY, job_type VARCHAR (30) NOT NULL);
INSERT INTO job (job_type) VALUES ('Unknown'), ('Unemployed'), ('Blue collar'), ('White collar'), ('Manager'), ('Self-employed');

No need to use AUTOINCREMENT for reasons outlined here. Developers tend to compulsively use AUTOINCREMENT - possibly a hangover from MySQL standards and possibly some idea that it must somehow be "better" than the simpler code above.

Then create and populate the main location table:

CREATE TABLE the_location
(
  loc_key INTEGER PRIMARY KEY,
  loc_coords VARCHAR (50) NOT NULL, -- not sure how you store these in your system
  loc_status INTEGER CHECK (loc_status IN (0,1)),
  loc_rating INTEGER CHECK (loc_rating BETWEEN 1 AND 10),
  loc_crime VARCHAR (25) NOT NULL,
  -- could use a CHECK CONSTRAINT, but use a lookup table instead
  -- note also that the types should match exactly
  loc_weather VARCHAR (50) NOT NULL,
  -- location_rating_tourism, -- same lookup table idea here (Good, Medium, Poor, Death-zone)
  -- location_rating_income, -- list of social classes table
  -- location_rating_jobs
  CONSTRAINT ct_loc_crime FOREIGN KEY (loc_crime) REFERENCES crime (cr_rating),
  CONSTRAINT ct_loc_weather FOREIGN KEY (loc_weather) REFERENCES weather (weather_type)
);

INSERT INTO the_location
(loc_coords, loc_status, loc_rating, loc_crime, loc_weather)
VALUES
('coord_1', 0, 7, 'Medium', 'Rainy'),
('coord_2', 1, 7, 'High', 'Mild');

I've left some of the other fields out, but if there is one and only one of these things associated with your location, then you can simply have them as a single field in the location table as discussed above.

However, consider the job table - corresponding to the location_rating_jobs record in your location table. Suppose there is a certain percentage of Unknown job_types, a certain percentage of Unemployed &c... per location?

In this case, you can use an Associative Entity (otherwise known as a joining or linking) table to cope with such a relationship. I would do something like the following:

CREATE TABLE location_job
(
  lj_loc INTEGER NOT NULL,
  lj_job      INTEGER NOT NULL,
  lj_percent  INTEGER DEFAULT 0 NOT NULL,
  PRIMARY KEY (lj_loc, lj_job),
  CONSTRAINT ct_lj_loc FOREIGN KEY (lj_loc) REFERENCES the_location (loc_key),
  CONSTRAINT ct_lj_job FOREIGN KEY (lj_job) REFERENCES job(job_id)
);

and then populate it respecting the FOREIGN KEY constraints.

INSERT INTO location_job
VALUES
(1, 1, 10), (1, 2, 10), (1, 3, 10), (1, 4, 10), (1, 5, 10), (1, 6, 50),
(2, 1, 30), (2, 2, 30), (2, 3, 10), (2, 4, 10), (2, 5, 10), (2, 6, 10);

And then, in order to find out the percentage of job_type by area, I would run the following query:

SELECT
  tl.loc_key, tl.loc_coords,
  lj.lj_loc, lj.lj_job, lj.lj_percent,
  j.job_id, j.job_type
FROM the_location tl
JOIN location_job lj ON tl.loc_key = lj.lj_loc
JOIN job j ON lj.lj_job = j.job_id
ORDER BY tl.loc_key, j.job_id;

Result:

loc_key loc_coords  lj_loc  lj_job  lj_percent  job_id  job_type
1   coord_1 1   1   10  1   Unknown
1   coord_1 1   2   10  2   Unemployed
1   coord_1 1   3   10  3   Blue collar
..
.. results snipped for brevity - see fiddle
..

Obviously, in a real query, the duplication of values would be eliminated and you would only have one of each necessary one - I've kept them all here so that you can effectively see the process of joining across tables.

The only area that falls outside the area of standard (current) SQL is the fact that the percentages per area cannot be > 100 (*) (Firebird can have queries in CHECK constraints - but it's about the only server that I know of which can). This is known as ASSERTIONs or "Declarative multi-row constraints" which have not yet been implemented by the major vendors - you'd have to use TRIGGERs for this - or maybe C code in SQLite.

(*) or perhaps your model would allow for those who were both Self-employed and Managers?

If this doesn't answer your question, please let me know. p.s. welcome to the forum! :-)


SQL Developer Data Modeller and Data Dictionary question I'm not a DBA but I am a (mostly!) competent SQL report writer. Can I trouble you DBAs for some pro-knowledge, please?

I can connect SQL Developer to Oracle XE's data dictionary without any problem, i.e. view the information of the data dictionary as an ERD within SQL Developer that shows the tables belonging to HR and the the relationships between these tables expressed as links between the PKs and FKs of the individual tables.

However, when I import the data dictionary from my workplace's 11g Oracle DB into SQL Developer, it brings back all the tables of the schema I'm pointing SQL Developer at but doesn't show the relationships between these tables - i.e. no links between the PKs/FKs of the tables belonging to the schema.

Is this feature something that has to be written into the ERD manually, or should table relationship information be available as part of the tables that make up the data dictionary?

The data dictionary will only show what it can.

Users can design their schema with or without referential integrity defined in the actual database.

When you import a data dictionary, using SQL Developer, to a new or existing relational data model, the foreign key constraints will be shown by default - if they are there to begin with.

Now, specific to the tool, it CAN attemp to infer any relationships. It does this by looking for common column names sprinkled around your tables, like an 'XYZ_ID' column, that happens to be a PRIMARY KEY in a driving table.

I have step-by-step instructions for this here, on my blog.


How to restrict table access to a application I would like to know how I would go about restricting access to a table so that only a certain application can access the data within.

Where I work, we have a database application that rather stupidly stores user logon details (including passwords) in plaintext. There is a vulnerability in the way the software works that allows any user to run a SELECT query on the users table, and return all this information. This has been reported to the application developers, and should hopefully be fixed in a future version.

For now however, would I be able to change security settings so that only this application can access that table, and users of the application would be denied access? Users should still be allowed to run UPDATE queries to change their passwords, but SELECT is a big no.

For the security credential/context which the application uses to authenticate to the DB, set an EXPLICIT DENY to it on the table to not allow it to access it at all (e.g. SELECT, UDPATE, DELETE, etc.).

You should really have the application credential locked down in the DB which it uses anyway to ONLY allow it to ONLY have the EXPLICIT level of access it needs to each DB object specifically in the name of security.

So why the user table is a concern now, wonder what other tables, etc. it has access to due to poor security design.


Is there an analog of GETDATE() that returns DATETIME2 According to MSDN, Getdate(), GetUtcDate(), and CURRENT_TIMESTAMP all return DATETIME. I ran a short test, which confirms that:

CREATE TABLE #t(T DATETIME2(7));
GO

DECLARE @i INT ;
SET @i=1;

WHILE @i<10000 BEGIN ;
INSERT #t VALUES(CURRENT_TIMESTAMP) ;
SET @i=@i+1;
END ;

SELECT DISTINCT t
FROM #t
ORDER BY t ;

---

2013-01-28 13:23:19.4930000
2013-01-28 13:23:19.4970000
2013-01-28 13:23:19.5000000
2013-01-28 13:23:19.5030000
2013-01-28 13:23:19.5070000
2013-01-28 13:23:19.5100000
2013-01-28 13:23:19.5130000

(snip)

Is there a similar function that returns DATETIME2(7)?


How can I identify all large tables that have no index? Is there a way I can identify all objects with over 10K rows and no index?

I would use this query to find that out.  The rowcount for Non-Clustered is the sum of ALL non-clustered indexes on the table, but the heap/clustered count is accurate.

;WITH CTE_RowCount AS
    (
    SELECT S.[name] AS SchemaName
        , O.[name] AS ObjectName
        , I.type_desc AS IndexType
        , SUM(P.[rows]) AS [RowCount]
    FROM sys.partitions AS P
        INNER JOIN sys.objects AS O ON O.object_id = P.object_id
        INNER JOIN sys.schemas AS S ON S.schema_id = O.schema_id
        INNER JOIN sys.indexes AS I ON I.object_id = P.object_id AND I.index_id = P.index_id
    GROUP BY S.[name], O.[name], I.[type_desc]
    )
SELECT *
FROM CTE_RowCount AS R
    PIVOT (SUM(R.[RowCount]) FOR IndexType IN ([HEAP], [CLUSTERED], [NONCLUSTERED])) AS Pvt
WHERE COALESCE(Pvt.[HEAP], Pvt.[CLUSTERED]) >= 10000
    --AND Pvt.[CLUSTERED] IS NULL -- Will only show if table is a HEAP
    --AND Pvt.[NONCLUSTERED] IS NULL --will only show if there are no non-clustered indexes.


How do you calculate the CPU Affinity Bitmap? I am trying to calculate the bitmap for my 32-core server. I want my instance to use cores 30 and 31.

I know that CPU 0 gets assigned a value of 1, CPU 1 gets assigned a value of 2, CPU 3 gets assigned a value of 4, etc.

I think this means that CPU 30 would have a value of 1073741824 and CPU 31 would get a value of 2147483648, but 1073741824 + 2147483648 = 3221225472, which is higher than the max value of an int and not acceptable to sp_configure

After 16 CPUs, does it flip to start using negative numbers or something?

I can't use ALTER SERVER CONFIGURATION to do this, because I am on SQL Server 2008.

I can't use the GUI, because all my processes need to be reusable on Server Core instances.

I also want to understand the theory and what I am missing from it!!!

Your binary representation of the mask should be:

1100 0000  0000 0000  0000 0000  0000 0000

Converting that to an unsigned 32-bit integer would equate to your original calculation:  3221225472.

But the affinity mask tasked a signed 32-bit value.  In that case, the binary to signed 32-bit value would be:  -1073741824.

Same bits, just different interpretation/conversion (signed vs. unsigned).

  I can't use the GUI, because all my processes need to be reusable on Server Core instances.

As an aside, you can and should use SSMS remotely, so the fact that you could have target database servers on a server core OS shouldn't prevent you from remotely using a GUI (like SSMS).  On that same note, I always prefer T-SQL at the very least for reusability and for documentation purposes, if nothing more than just knowing exactly what is happening when you do something with no GUI tricks/abstractions/assumptions.


Very slow Insert and update on 20 core 250 Gb RAM dedicated MYSQL 5.5 server on Ubuntu Most of the data is in tbl_user_ads. Its a huge table.

Many concurrent processes (over 1000) try to select, insert or update this table.

The CPU utilization is just around 2%.

As I increase the number of processes which access this table the performance drops further.

I am getting lots of deadlocks.

Thanks for your time and help.

innodb status

my.cnf customizations:

innodb_buffer_pool_size=220G
innodb_buffer_pool_instances = 4
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size=2048M
innodb_log_file_size = 1024M
innodb_flush_method = O_DIRECT
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_locks_unsafe_for_binlog = 1
innodb_lock_wait_timeout = 100

EDIT: after reading threw it, I noticed that your limiting it to 16 threads?
"innodb_read_io_threads = 16"
your server has 20 cores of cpu, and therefor you will only use a very small amount of CPU, try adding more threads and report back if it makes any difference,other then that i can't think of any thing else!

it may be due to server load, the fact that you have 20 cores may be useless, if the program can't multithread, whats your specs and general overall server load? other then that, I can't really think of any thing else that may be causing this.


Set column to only accept negative values Is there anyway to change a column data type so that it will accept only negative values?

It's SQL Server 2014 Azure.

CREATE TABLE t ( n INTEGER NOT NULL CHECK (n < 0) );

works in most RDBMS I know.

Edit: A comment by @IMSop prompts me to specify why I wrote “most RDMS I know:” it is well known (and very unfortunate) that CHECK constraints aren’t honored by MySQL. In MySQL, you have to use triggers instead. Another option is to switch to MariaDB.


How to apply TagName for all backup files I perform oracle database backup using the following rman command:

RUN {
    ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT 'C:\Windows\TEMP\%u';
    ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT 'C:\Windows\TEMP\%u';
    BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG TAG MyTAG;
}

But unfortunately MyTag is applied only for archive logs BS.
The query list backup; return 6 rows:

BS of archive logs;
BS of data files;
BS of data files;
BS of data spfile;
BS of data controlfile;
BS of archive logs;

The query list backup summary tag 'MyTag'; return only 2 rows

BS of archive logs;
BS of archive logs;

How to apply TagName for all backup files ?

Specify the tag for the database backup as well:

BACKUP AS COMPRESSED BACKUPSET DATABASE TAG MyTAG PLUS ARCHIVELOG TAG MyTAG;

Ok, I tested this, this does not add the specified tag to the controlfile backup. The below creates an additional controlfile backup and adds the specified tag to that as well:

BACKUP AS COMPRESSED BACKUPSET DATABASE TAG MyTAG INCLUDE CURRENT CONTROLFILE PLUS ARCHIVELOG TAG MyTAG;


Configuration options for relational algebra evaluator "ra.jar" I am participating in the Stanford Database Class, and there are some problem sets to be solved in "mathy" relational algebra syntax.

The course instructors demand from students to use this open-source, free jar file "ra.jar"  to submit solutions via web forms.

RA: an interactive relational algebra interpreter
Version 2.1b by Jun Yang (junyang@cs.duke.edu)
>

I wonder if there is a better evaluator available? This one is pretty good but

on the command-line-interpreter prompt, the cursor position is hard
to see
the tool translates relational alegbra experssions to SQL and submits
them to SQLite. It does not show (return to the user) the
intermediate steps.  There should be a -log or verbose option to
return the SQL generated. (The ra tool has a -v option to return the submitted ra query).

I want the CREATE VIEW.... and SELECT ... SQL statements that the jar file generates and sends to SQLite. Alternatively, can I configure SQLite to echo/log all sql commands submitted to it? Is there an option for the .properties file?

That RA tool does not have an option to output the SQL queries.

However, the mapping from relational algebra to SQL is pretty much trivial (except for division), can be easily found with a web search, and is something you should have learned in your course.

If you want to find out how RA handles this, look into the file RAXNode.java.
The various classes derived from RAXNode each implement one relational algebra operation; the method toPrintString shows the RA operation, while genViewDef generates the corresponding SQL command.
For example:

public static class PROJECT extends RAXNode {
    public String toPrintString() {
        return "\\project_{" + _columns + "}";
    }
    public String genViewDef(DB db) {
        return "SELECT DISTINCT " + _columns + " FROM " + getChild(0).getViewName();
    }
}
...
public static class INTERSECT extends RAXNode {
    public String toPrintString() {
        return "\\intersect";
    }
    public String genViewDef(DB db) {
        if (db.getDriverName().equals("com.mysql.jdbc.Driver")) {
            // MySQL doesn't support INTERSECT, so we need a workaround.
            ...
        } else {
            return "SELECT * FROM " + getChild(0).getViewName() +
                " INTERSECT SELECT * FROM " + getChild(1).getViewName();
        }
    }
}


Why would one need multiple instances of same Oracle database? I came across following statement while reading this short article -

  An instance is the software (and memory) that Oracle uses to
  manipulate the data in the database. In order for the instance to be
  able to manipulate that data, the instance must open the database. A
  database can be opened (or mounted) by more than one instance,
  however, an instance can open at most one database.

I am wondering, why would one create multiple instances of same database?

For availability (one instance goes down, another instance can continue to work), handling load between nodes (adding more nodes on as needed basis) and possibility to dynamically allocate resources based on needs. That is achieved by using Oracle RAC (Real Application Cluster) where instances are running on separate machines.
In Oracle for version up to and including 11.2, one database can be used by one instance (basic setup) or more instances (Oracle RAC).
 It's expected to see paradigm shift for instance vs database with soon-to-be-released Oracle 12c (having more databases inside one instance).


Grouping data by year based on two timestamp columns My data table has the following columns:

id INTEGER, name TEXT, created TIMESTAMP, deleted TIMESTAMP

I want to product a report of the count of each name (which can appear several times in the table) that is active each year.
(also, the deleted timestamp may be null, if it's currently still active).

So far I’ve managed to do this by manually entering the years in a long list of union statements (see below). I’m sure there’s a better way! And I have many more similar queries to perform. I tried to create a PL/pgSQL function, but couldn’t figure out how to get the year as a variable and also how to get the correct output. I would be very happy with a statement, or PL/pgSQL function to achieve this.

((select '2016' yr, name, count(*) from data
where (((deleted - '2016-01-01'::timestamp) > '0 secs') or (deleted is null))
and (created - '2016-01-01'::timestamp) <= '0 secs'
group by name
order by count desc)
union all
((select '2015' yr, name, count(*) from data
where (((deleted - '2015-01-01'::timestamp) > '0 secs') or (deleted is null))
and (created - '2015-01-01'::timestamp) <= '0 secs'
group by name
order by count desc)
etc..

I got the years using:

select distinct date_part('year',created) from data order by date_part('year',created);

And then manually entered them in the very long union statement. (In my case 2007-2016!)

With generate_series() in a LATERAL join (Postgres 9.3+) and date_trunc(), this can be short & simple:

SELECT EXTRACT(YEAR FROM yr)::text AS year, name, count(*) AS ct
FROM   data
     , generate_series(date_trunc('year', created)  -- LATERAL join
                     , COALESCE(deleted, localtimestamp)
                     , interval '1 year') yr
GROUP  BY yr, name
ORDER  BY yr, ct DESC;

That's all. Returns results for all years between the earliest created and the current year.
The trick is to generate one row for each year the base row overlaps with, before aggregating.

Alternative:

SELECT EXTRACT(YEAR FROM yr) AS year, d.name, count(*) AS count
FROM   generate_series ((SELECT date_trunc('year', min(created)) FROM data)
                       , localtimestamp, interval '1 year') yr
JOIN   data d ON d.created < yr::timestamp + interval '1 year'
            AND (d.deleted > yr::timestamp OR d.deleted IS NULL)
GROUP  BY yr, d.name
ORDER  BY count(*) DESC;

This generates the whole range of years before joining. May be more convenient to compute numbers for a manual selection of years.

Related:

Find overlapping date ranges in PostgreSQL
What is the difference between LATERAL and a subquery in PostgreSQL?

If you need to optimize performance for a small set of years out of a much bigger table, a GiST index on a tsrange type would be an option:

Optimizing queries on a range of timestamps (two columns)


What does a round relationship icon mean in MySQL Workbench Visual Data Model I have created a model in the Visual Data Model of MySQL Workbench, and on one single relationship, this icon shows up:

What does it mean? The Relation is 1:1, and this icon shows only on this particular relationship, none other. I already checked and the relationship is exactly the same as all the other.

The icon shows that that side of the relationship is not mandatory, meaning the field can be NULL.
You can change that if you click on the relationship line, go to Foreign Key tab and select the Mandatory check box.


way to prevent queries from waiting for table level lock We've encountered a problem after moving the database of our customer to an extra server. This should have had positive effects on the site's performance, but there is a problem with table locking in MyISAM. (I've heard of using InnoDB instead of MyISAM, but we cannot change the engine in the near future).
We could spot it to an update-query which is performed when a moderator activates a comment on the articlesite. This is the process:

update-query is processed  SET status = 1 WHERE id = 5 (index is set)
the cached files of the page are deleted

At this point the whole page becomes slow. The database itself is busy for minutes. I fetched the processlist a few times and saw about 60 entries of different select-queries, which were all on the state waiting for table level lock.
1. I don't unterstand why this update on the table article_comments can affect select-statements for table article to wait for table level lock. In processlist almost all waiting queries were from this table. I've read about the fact that updates/inserts are preferred to selects and that this can cause such problems, but the articles-table itself isn't updated when comments become activated, so the selects shouldn't wait. Did I missunterstand that?

2. Is there something besides changing to InnoDB to prevent this behaviour or at least to get a better balance? I'm very irritated about the fact that this problem did not appear before moving the database to the new server. I guess there is some misconfiguration but I don't know how to identify.

The MyISAM Storage Engine is furiously notorious for performing full table locks for any DML (INSERTs, UPDATEs, DELETEs). InnoDB would definitely solve that issue in the long term.

I wrote about pros and cons of using MyISAM vs InnoDB

InnoDB vs MyISAM with many indexes (Jul 05, 2012)
Which is faster, InnoDB or MyISAM? (May 03, 2012)
Any gotchas at all with converting from MyISAM to InnoDB? (Jan 09, 2012)
Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks? (Oct 07, 2011)
Benefits of table level locking (Sep 14, 2011)
What are the main differences between InnoDB and MyISAM? (Apr 14, 2011)

With regard to your current question, here is a possible scenario:

article and article_comments are both MyISAM tables
article_comments has one or more indexes with status as a column
Index page updates for article_comments are cached in the MyISAM Key Buffer (sized by key_buffer_size), causing old index pages out of the MyISAM Key Buffer
You have SELECT queries that perform JOINs between article and article_comments

In my suggested scenario, SELECTs against the article table can be held up from allowing writes because of having to wait for article_comments to be free from any DML (in this case, an UPDATE)


Why does adding an index on a column containing a char version of a UUID decrease performance? I have a table with 3.5 million rows containing UUIDs in their ascii representation (so 36 chars) and an integer amount.
I need to SUM all amounts grouping by UUID, so the query is very simple:
SELECT uuid, SUM(amount) FROM table GROUP BY uuid;

results:

with an index on uuid: Time: 68.074s
without an index: Time: 8.188s

execution plan with index:
| id | select_type | table  | type  | possible_keys    | key      | key_len | ref    | rows    | Extra  |
+----+-------------+-----------------------------+-------+------------------+------------------+---------+
| 1  | SIMPLE      | amount | index | uuid_idx         | uuid_idx | 110     | <null> | 3424833 | <null> |
+----+-------------+-----------------------------+-------+------------------+------------------+---------+

execution plan without index:
| id | select_type | table  | type | possible_keys | key    | key_len | ref    | rows    | Extra                           |
+----+-------------+-----------------------------+------+---------------+--------+---------+--------+---------+-------------
| 1  | SIMPLE      | amount | ALL  | <null>        | <null> | <null>  | <null> | 3424833 | Using temporary; Using filesort |
+----+-------------+-----------------------------+------+---------------+--------+---------+--------+---------+-------------

Am I missing something or does an index on that column really dramatically decrease performance on that kind of query?

The EXPLAINs show that, with the index, it used the index.  That lead to jumping around:  Pick the next alphabetical UUID, reach into the table in random location for the rows to find the amounts to SUM up.
Without the index, it simply plowed through straight through the table, collecting data, then sorting in order to finish the task.
3.5M random lookups apparently takes longer than sorthing 3.5M rows.
Converging the UUID to BINARY(16) would shrink it, thereby helping a little.
Having INDEX(uuid, amount) would significantly speed up this query.  But still, the random nature of uuids is a performance drag.
Some related discussion:  http://mysql.rjweb.org/doc.php/uuid


Where can I get the installation software for Oracle 8i DB? I have some backup files for an old Oracle 8i data that was created years ago. I need to be able to view this data and export it to a csv file.

The backup directory includes .dbf, .ctl , .arc and a few other file formats.

My initial thought is to obtain an installation of this DB so I can try to restore it. Is there anyway I can obtain it? (Any OS would be fine)

Any suggestions on how to do this would be great.

If you have 9i or 10g, you can try starting the database with either of those provided you do a startup upgrade. You should then do a manual upgrade. Of course you need to make sure that the endian is the same, and ideally the same type. ie. Linux/Windows/Unix.


Oracle: force shutdown as root without knowing logins I am root on a GNU/Linux machine with Oracle 10.2.0.1.0 XE installed. I need to shut down Oracle, but I do not have admin logins to Oracle.

How do I as root force Oracle to shutdown?

I have a normal user login. When using that I get:

ORA-01089: immediate shutdown in progress - no operations are permitted

This does not change even if I run:

sudo /etc/init.d/oracle-xe restart

I have the feeling I need to create an administrative user from the Unix prompt, login in as this user, and run some magic command (probably one of Oracle Shutdown Method).

My guess is you probably need to fiddle with these files:

$ ls $ORACLE_HOME/dbs
hc_XE.dat  init.ora  lkXE  orapwXE  spfileXE.ora

But exactly what needs to be done?

A shutdown is already in progress. You can either

Wait
Login using su - oracle and then sqlplus / as sysdba and issue the command shutdown abort if that's what you really want.


computed columns vs views I'm trying to think about the best way to do this. If i have a table like this in sql server:
StudentPointsTable
StudentID
TestDate
Points

I need to have Averages and standard deviation calculated as well for a student. I know i can create a view, or create a new table with these computed columns, or add the computed columns to this table.

Not sure if there are any other options but in terms of performance, what would be the best way to do this?

Creating view will not give you any performance gain but may help you with abstraction of underlying object and management of security from users.
Using Index view can definitely help you by doing the calculations before hand but you need to be careful as explained in detail here.

SQL Server must always ensure that the data in the index and base tables is synchronized, so we need to be careful when adding indexes to views. Every time an underlying column has a new row added or deleted, or is updated, SQL Server must maintain every clustered and non-clustered index, whether on the base table or the referenced indexed view. This will lead to additional writes, which can decrease performance.
I would not create computed column in the same table as you will have redundant data (average for a student in every row for that student) in your table. Using extra storage, less number of pages in memory etc.
You should also consider indexing strategy based on how you query this table. Meaning is it grouping by one student, student + date, one student + range of date?


sqlcmd gives no sign of failure yet doesn't work I am running sqlcmd from a linux bash script. My target server is an Azure SQL.  I execute an sql file with a lot of sql statements (around 2500). These are Insert/update/delete statements. I used the following command

sqlcmd -i $SQL_SCRIPT_FILE -m-1 -p

Initially there were individual sql statements in the file. When executing that, a lot of statements were being missed out (I found that by checking the database, as some of the insert statements does not reflect in db).

Then I put all the statements in try catch and transaction. I am writing the exceptions in an ErrorLog table.
Now when I execute the file, nothing happen. I do not see any error in ErrorLog table, the sqlcmd output also not display anything specific. The output is:

(0 rows affected)
Network packet size (bytes): 4096
1 xact[s]:
Clock Time (ms.): total        23  avg   23.0 (43.5 xacts per sec.)

It sounds like your transaction is rolling back (including any rows written to your ErrorLog table).  You haven't posted any TSQL source, so I'm not sure if you have the TRY/CATCH within a transaction or the transaction is within the TRY/CATCH.

I'd suggest following the guidance in this post - (TSQL Try / Catch within Transaction or vice versa?).

  Only open a transaction once you are inside the TRY block and just
  before the actual statement, and commit it straightaway. Do not wait
  for your control to go to the end of the batch to commit your
  transactions.

  If something goes wrong while you are in the TRY block and you have
  opened a transaction, the control will jump to the CATCH block. Simply
  rollback your transaction there and do other error handling as
  required, which might include writing rows to your ERRORLOG table.

Here's the code sample from the post above.

BEGIN TRY

  BEGIN TRANSACTION SCHEDULEDELETE
    DELETE   -- delete commands full SQL cut out
    DELETE   -- delete commands full SQL cut out
    DELETE   -- delete commands full SQL cut out
 COMMIT TRANSACTION SCHEDULEDELETE
    PRINT 'X rows deleted. Operation Successful Tara.' --calculation cut out.
END TRY

BEGIN CATCH
  IF (@@TRANCOUNT > 0)
   BEGIN
      ROLLBACK TRANSACTION SCHEDULEDELETE
      PRINT 'Error detected, all changes reversed'
   END
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage
END CATCH


SQL SSIS Closing Files already in use Sometimes when running SSIS packages on flat files, I receive error: "The process cannot access the file because it is being used by another process".

Another person already has file opened.

Is there a way for SSIS to manually close the file, so it can be processed?
I assume SSIS needs admin rights in the folder its processing.
I don't see "Close file" option in FileSystem task? Should I maybe use Powershell?

Please let me know of best options, I prefer SSIS first.

Thanks,

When an application opens a file, it specifies what type of access is allowed by concurrent processes (none, read, read/write, etc.). SSIS cannot change that. The file must either be closed or the other process terminated.

Although you could programmatically identify the terminate local processes owning a file handle, that can be challenging with remote processes and/or UNC paths. Personally, I wouldn't go there. A better solution is to have users copy files to a staging location once they are ready and have the ETL process them up from there.


Large queries are slower in SSMS I have a large table, about 750M rows.

I have an application using Entity Framework to load and parse data, a query that returns 2 million rows takes 4 seconds.

The same exact query takes 8 seconds using SQL Server Management Studio.

I noticed this happens with other queries (that all returns large data sets).
Both of the connection are using TCP/IP.

When including client statistics, I see this line (no idea what it means):

  Client processing time  2539        2539.0000

Why would a SSMS query be slower ?

Client processing time is the amount of time spent in milliseconds between the first received response packet and the last received response packet by the client.

When you run the query in SSMS it render the data row by row to the GUI. Your application doesn't do that.

Setting the SSMS option to Discard results after execution will eliminate the time spent rendering the results to the grid, which should give you a comparable execution time:


Can counting rows be made any faster? I need to calculate the total number of rows based on a set of criteria:

SELECT COUNT(*) FROM Users WHERE hasPhoto=1 AND userStatus='a';

The above query takes 0.10 sec to run on a MyISAM table with 500,000 rows despite having a composite index on columns userStatus and hasPhoto.

Is there any better way to improve counting performance short of writing the total count in a separate table?

I don't think you are going to get anything better than the composite index. However, the key distribution for the composite index could hamper query performance depending on the (hasPhoto,userStatus) combination you choose. Here is how:

Run this Query

SELECT COUNT(*) rowCount,hasPhoto,userStatus FROM Users GROUP BY hasPhoto,userStatus;

This will show you how sparse and how dense each composite index combination is.

Sparse combinations (low rowCount) should use an index scan.

Dense combinations (high rowCount) should use a full table if the Query Optimizer believes it must read too much of the composite index.

You can try this out by running

EXPLAIN SELECT COUNT(*) FROM Users WHERE hasPhoto=1 AND userStatus='a';

This will reveal whether the EXPLAIN plan favors an index scan (Extra column Using where; Using index), or a full index (or table) scan (select_type of ALL or SIMPLE).


Unique key constraint in mysql I have a table in mysql which has a many columns,one of them being time.I want HOUR(time) as unique key for the table,that is there should only be one record for every hour.How can i do it?

You could use a simple integer column as your hourly key constraint. The unique hour for each possible time can be determined by

select floor(now()/10000)

which will return something like

2015090511

for the current date 5.9.2015 at  11.25h


How to insert into view through GUI in pgAdmin? I have some updateable views for inserting the data. The reason is not just "security layer", inserting into some 10 tables directly would be a pain without the view with INSTEAD OF trigger. In complete production, there will be some client app doing the inserts, but it is not ready now. A customer doesn't want to wait for the app, but also doesn't want to learn SQL just to insert few rows. Inserting the data through pgAdmin would be ideal. Unfortunately, pgAdmin shows a "new row" while inspecting data only for tables, not for views.

So is it possible to enable inserting into updateable views in pgAdmin? If not, is it possible in any other PostgreSQL GUI?

It is not just for this customer - we plan to offer the database without the client app as an opensource "demo", and we would like this demo to have some value for SQL-inept potencial customers too.

Sorry, no luck with pgAdmin. Per documentation:

  Note that views can’t be edited and are read only by design; updatable
  views (using rules) are not supported at this time.

There is an item in the project's TODO list:

  Updateable View wizard

Seems like nobody tackled that, yet. (Contributors welcome.)
There have been requests to introduce this feature in the past. You could email pgadmin-support@postgresql.org to document popular demand ...

For now, it seems like Navicat supports it.


Migrating MyISAM tables from 5.5 => 5.6 I was wondering if anyone had any pointers on migrating MyIASM tables from mysql 5.5 to 5.6, specifically using Percona builds, via an rsync?

Is this safe?

I know we need to do a full mysqldump/reload for a migration for InnoDB tables but I didn't see any notes about significant changes to the MyISAM format between the two.

It would make things a little nicer for a rather large MyISAM db that would take several days to complete with a mysqldump.

MyISAM didn't change between 5.5 and 5.6. It's not being developed, and it's virtually stagnant.

Use FLUSH TABLES WITH READ LOCK and then it's safe to copy the MyISAM tables. This is mentioned in https://dev.mysql.com/doc/refman/5.6/en/backup-methods.html

Other solutions:

mysqldump --tab outputs tab-delimited data files instead of SQL, so it's much faster to import with mysqlimport.
mysqlhotcopy does a physical copy of MyISAM tables (it doesn't work for InnoDB tables). But be careful because mysqlhotcopy is on the chopping block for deprecation.
mydumper, which dumps to SQL, but it supports compression and multi-threaded execution for both dump and restore. Unfortunately it does not support an option like --tab.


db2 convert regular table to partitioned using online table move I am converting my regular table to partitioned table using timestamp column. However, the below code fails with sql error 2103 reason code 6.
Please note that there are no referential(or foreign key constraints) on this table so wondering where I am going wrong. There is primary key and few indexes defined on TRANSACTION table. Kindly help !

I am not using any options as well for simplicity though I want to use ' KEEP, COPY_USE_LOAD,COPY_WITH_INDEXES' as option.

  CALL SYSPROC.ADMIN_MOVE_TABLE(
  'ADMIN',
  'TRANSACTION',
  '',
  '',
  '',
  '',
  '',
  '(TRANSACTIONDATE) (STARTING FROM (1/1/2010) ENDING AT (1/1/2017) EVERY 1 MONTH)',
  '',
  '',
  'MOVE'
  )

The only problem seems to be your missing quotes for the dates. As they are enclosed in the string the have to be escaped (with a second single quote) like in my example:

CALL SYSPROC.ADMIN_MOVE_TABLE(
    'ADMIN',
    'TRANSACTION',
    '', '', '', '', '',
    '(TRANSACTIONDATE) (STARTING FROM (''1/1/2010'') ENDING AT (''1/1/2017'') EVERY 1 MONTH)',
    '', '', 'MOVE' )


Both replication masters stop by themselves I did a master/master replication with MySQL on a Gentoo OVH Release 2, all work fine in my test phase. I put it in production, the replication works fine for 1 or 2 days, but this morning, I don't know why, my slave stops running!

So now the log position is bad. I can't just restart slave and my replication doesn't work.

I want a master/master replication to make a backup server with an IP failover, so only one server is writing/reading in the database at same time.

When I go to MySQL and click on Show slave status, I see an error like:

  Error 'Duplicate entry '411465' for key 1' on query. Default database .... etc.

Did this error stop my replication? If yes, why do I get this error? The second server does nothing on the database, so normally, there is no problem with the autoincrement, right?

The provided error Error 'Duplicate entry '411465' for key 1' on query means that the slave read and attempted to execute a binary log event to insert a row that already existed, ie. the same value 411465 for your primary key.

The most likely cause of this is the the insert was executed on the slave. To diagnose the query, you would use mysqlbinlog and use the binary log coordinates from SHOW SLAVE STATUS. This will give you the server-id that the query originated from, which will match either your main master or 'passive' master.

Once you determine the query, you can identify the row on the server that is throwing the slave error to determine next steps. You can choose to :

skip the entry using SET GLOBAL sql_slave_skip_counter=1 to proceed to the next binary log statement
Delete the specific row on the server and start slave to have the statement run from replication.

However, you need to take steps to understand how the mismatch occurred, or you're going to run into this again. This will require some more detective work from your end using mysqlbinlog.

If, as you say, only one master is writeable at a time, you should ensure the following:

passive master is read_only=1 and your failover solution is able to modify read_only.
the user that your application runs as (or any other non-trusted user) does not have the SUPER privilege. Any user with SUPER privilege can execute statements even on read_only=1 servers.
setup pt-table-checksum to ensure the data is in sync on both servers.


Copying a MySQL 5.6 server settings for a new environment? I have a MySQL 5.6 Server running on a Windows 8 VM on a steel case server.  It's been having issues (The Windows 8, which is then causing the MySQL to have issues).  Therefore I was tasked with putting the MySQL 5.6 Server on a Windows 10 VM for stability reasons.  The amount of ram the server is allowed and other settings have been tweaked over time.  Is there an way I can export these settings, so when I install MySQL on my Windows 10 VM, I can just tell it to use the same?

The first recommendation is to not use Windows 10 on your installation if this is for a production server. I highly recommend you Linux but if that's not possible at least Windows Server.
Asking your question, YES.
Just install MySQL (same version as on old machine) and follow these steps

Stop MySQL on both machines (New and old)
Locate and delete datadir folder on the new machine and copy the one from the old machine
Do the same with my.ini file (Delete the new one and replace it with old one).

In theory, you should be able to start MySQL on the new machine and access the data.
Check data and if everything goes fine, you can delete old machine service/data.


How to select a row having SubjectCode X if not then select SubjectCode as Y and so on as mentioned in code In this query I want to select rows from Question table where Subject Code is X if not then Go for Y and so on as mentioned in the case statement.

select     dbo.GetStudentNumber('M', mcl.StudentID) As Student_Number,
           SubjectCode,*
FROM       dbo.Question mcl
inner join Answer mclo
on         mclo.AnswerID = mcl.AnswereID
where      StudentID = 6588
and        mclo.SubjectCode = (case when mclo.SubjectCode ='X' then 'X'
                               else
                                 case when mclo.SubjectCode ='Y' then 'Y'
                                 else
                                   case when mclo.SubjectCode ='Z' then 'Z'
                                   else
                                     case when mclo.SubjectCode ='P' then 'P'
                                     end
                                   end
                                 end
                               end)

as fas as I understand you would like to control the order of execution if x is not present search for y and so on. Therefore a simple IN clause or the case expressions probably won't work if you've got entries for x as well as for y and z....by result returning multiple rows.

The best way I can think of would be to introduce a priority factor within a cte and use this to return only the relevant row.

Here is an example. For simplicity I use a table with the following structure

  CREATE TABLE test
  ( StudentID int,
    SubjectCode char(1)
   );

The table has the following entries:

As you described we'd like to return X first, then possibly Y (if no X present) and at last Z and only one row per student if the applicable subject code.

XYZ is easy because it is alphabetical but let's introduce some additional complexity guessing that there could be also an A which we would like to return if there is no X.

Here is a SQL Fiddle with my example

I would use the following query to achieve this:

    WITH
   cteSortLogic AS
  (
      SELECT *
      FROM
       (
            VALUES ('X', 1), ('A', 2), ('Y', 3), ('Z', 4)
        ) AS tab(code,prio)
  )
 SELECT StudentId, SubjectCode
 FROM
 (
 SELECT
   StudentId,
   SubjectCode,
   ROW_NUMBER() OVER (PARTITION BY StudentId ORDER BY prio) AS rn
 FROM test t
 INNER JOIN cteSortLogic  sl ON t.SubjectCode = sl.code
 WHERE SubjectCode IN ('X', 'Y', 'Z')
 ) res
 WHERE res.rn = 1
;

The CTE gives me a simple table with the codes and their priority of output:

Then I join it to my table and add a rownumber based on the priority:
So here's the output of my subselect (or derived query) named "res":

I have still got all the rows but assigned a rownumber based on my priority. Then the last thing I have to do is to filter only the most relevant rows which are the one having row number 1 .

Presto...here's the result of the whole query:

So given your example query your code would look like this:

    WITH
   cteSortLogic AS
  (
      SELECT *
      FROM
       (
            VALUES ('X', 1), ('Y', 2), ('Z', 3)
        ) AS tab(code,prio)
  )
SELECT *
FROM
(
select     dbo.GetStudentNumber('M', mcl.StudentID) As Student_Number,
           SubjectCode,   ROW_NUMBER() OVER (PARTITION BY StudentId ORDER BY prio) AS rn, *
FROM       dbo.Question mcl
inner join Answer mclo on         mclo.AnswerID = mcl.AnswereID
inner join cteSortLogic sl on mclo.SubjectCode = sl.code
where      StudentID = 6588
AND         SubjectCode IN ('X', 'Y', 'Z')
)
WHERE
    rn = 1


Why does DBCC TRACEON (1204, 1222) not work? I have executed DBCC TRACEON (1204, 1222) on several servers.

Some of them simply do not turn the trace flags on. The command brings the same expected informational message for each but some simply do not react.

DBCC TRACESTATUS(1204, 1222) delivers  0, 0, 0 in this case for both flags.

Why do they ignore it?

Running just DBCC TRACEON (1204, 1222) is per session level.

You should run DBCC TRACEON (1204, -1) globally.

See BOL- DBCC TRACEON for more details.


Breaking database replication I am trying to break replication between two SQL 2000 databases.  Can this be done while the databases are live? or do they need to be taken offline?

Delete publications from the Local Publications folder in Microsoft SQL Server Management Studio.

To delete a publication

1.Connect to the Publisher in Management Studio, and then expand the server node.

2.Expand the Replication folder, and then expand the Local Publications folder.

3.Right-click the publication you want to delete, and then click Delete.


How do I get access logs from SQL Server database I am in the process of carrying out system reviews for some systems powered by SQL Server. IS there a way of getting the access logs to these systems databases spanning a period of 6 months starting from January 2020 to June 2020?
Is there a specific query that I can run to extract for me access logs on a given system FROM a given date TO a certain date?

At least, you can create table with fields for username, date and time and information about transaction. Secondly, you should create trigger functions for your tables, function should write logs into you log table, this allow you to access your logs quite simply.


Group By days interval (aging type) I would lIke to have Mysql group by days interval for example group by every 20 days from current day, like 1 - 20, 21 - 40, 41 - 60 and son on up to lets 120 days. The user can choose the days interval and up to how many days

select floor(days / 20) as daygroup, count(*)
from mytable
group by daygroup


How can I make the audit system create new audit files if they already exist? I am trying to make my audit files easier to read, I have audit trail configured as xml,extended. The problem is that if my server gets busy and reuses the pids - the logs are being appended to the same file.

I understand that there is a way to create a new file with suffixes if the audit file already exists. How can I do that? I use Oracle 10.0.4.0

There doesn't seem to be any reference to being able to directly manipulate the audit file names in 10g.  The closest thing I found is Bug 6023472 which lists the following changes introduced in 10.2.0.5/11.1.07/11.2.0.1:

OS/XML Audit filenames will have a filename format of the form:
  <InstanceName>_<ProcessName>_<ProcessID>_<SerialNo>.<Extension>
The default value for Max Audit FileSize is 1000KB and Max Audit File Age is 1 Day. This means that once one of these max limits are
reached, the audit records will be written in a new audit file with same
"<InstanceName>_<ProcessName>_<ProcessID>_" part, but with a new "<SerialNo>".

I'm not sure how Oracle assigns PIDs, but you could try increasing your PROCESSES parameter to see if that keeps it from re-using the values as quickly.


Postgres 11 - Fetching results after a transactiom With the recent update of Postgres 11. It now supports procedures. Transactions are finally supported in stored procedures in Postgres. However, I have been trying to perform a transaction and retrieving a refcursor as the result.

CREATE OR REPLACE PROCEDURE testproc(pid text,pname text,INOUT cresults refcursor)
 LANGUAGE plpgsql
AS $procedure$

BEGIN
 cresults:= 'cur';
 begin
    update "testtable" set id=pid , name=pname where id=pid;
  commit;
 end;
    OPEN cresults for select * from oureadata.CMS_CATEGORY limit 1;
end;
$procedure$

I understand that fetching of refcursor have to be in a transaction. This is how i execute the procedure.

BEGIN;
Call oureadata.testproc('1','2','');
fetch all in cur;
commit;

When i try to fetch the cursor, it throws an exception "ERROR: Invalid transaction termination"

But if i remove the commit from the procedure. I can actually execute the procedure and fetch the result of the refcursor. (The above is just an example, as i have other more complex transaction which will return refcursor after)

So my question is, can a procedure return a INOUT refcursor result after a transaction is done?

From what I understand, in order to use the cursor after your procedure ends, you need to be on the same transaction you was when calling the procedure.
Your code use two different transactions: the first one is started with BEGIN just before calling the procedure, and it ends at the COMMIT. The second transaction starts automatically after the COMMIT in your procedure and continue until the COMMIT after your FETCH.


SSRS email Subscription Not Working but local smtp is working SQL SERVER EDITION- 2008 ENTERPRISE EVALUATION EDITION
Operating System- Window Server 2008 R2

I've Created Local Smtp to use gmail id for Subscription of SSRS Report by Email as we don't have an Exchange Server.

Used this link to Create local SMTP for gmail

Below is the Screen Shot of rsreportserver.Config File

ERROR : on Report Manager

Below is report log on after time report was executed.

library!WindowsService_16!2b30!05/20/2015-13:19:05:: i INFO: Schedule 3374e590-8374-4360-b5da-d487eacb0417 executed at 05/20/2015 13:19:05.
schedule!WindowsService_16!2b30!05/20/2015-13:19:05:: Creating Time based subscription notification for subscription: ea7fc6ce-00df-4f6a-9050-e713986307ca
library!WindowsService_16!2b30!05/20/2015-13:19:05:: i INFO: Schedule 3374e590-8374-4360-b5da-d487eacb0417 execution completed at 05/20/2015 13:19:05.
library!WindowsService_16!2b30!05/20/2015-13:19:06:: i INFO: RenderForNewSession('/Subscription')
library!WindowsService_16!2b30!05/20/2015-13:19:06:: i INFO: Initializing EnableIntegratedSecurity to 'True'  as specified in Server system properties.
library!WindowsService_16!2b30!05/20/2015-13:19:06:: i INFO: Initializing SqlStreamingBufferSize to default value of '64640' Bytes because it was not specified in Server system properties.
library!ReportServer_0-24!1f08!05/20/2015-13:19:08:: Call to GetPermissionsAction(/Subscription).
library!ReportServer_0-24!2484!05/20/2015-13:19:08:: Call to GetSystemPropertiesAction().
library!ReportServer_0-24!1f08!05/20/2015-13:19:08:: Call to GetPropertiesAction(/Subscription, PathBased).
library!ReportServer_0-24!1f08!05/20/2015-13:19:08:: Call to GetSystemPermissionsAction().
library!ReportServer_0-24!2484!05/20/2015-13:19:08:: Call to ListSubscriptionsAction(/Subscription, null).
library!ReportServer_0-24!1f08!05/20/2015-13:19:08:: Call to GetPropertiesAction(/Subscription, PathBased).
library!ReportServer_0-24!2484!05/20/2015-13:19:08:: Call to GetSystemPropertiesAction().
library!WindowsService_16!2b30!05/20/2015-13:19:10:: i INFO: Initializing SnapshotCompression to 'SQL'  as specified in Server system properties.
library!WindowsService_16!2b30!05/20/2015-13:19:11:: i INFO: Initializing ChunkSegmentSize to default value of '32320' Bytes because it was not specified in Server system properties.
library!WindowsService_16!2b30!05/20/2015-13:19:11:: i INFO: Initializing ResponseBufferSizeKb to default value of '64' KB because it was not specified in Server system properties.
library!WindowsService_16!2b30!05/20/2015-13:19:11:: Using folder D:\Program Files\Microsoft SQL Server\MSRS10.SQLSVR2008_NEW\Reporting Services\RSTempFiles for temporary files.
library!WindowsService_16!2b30!05/20/2015-13:19:13:: i INFO: Initializing SessionTimeout to '600' second(s) as specified in Server system properties.
library!WindowsService_16!2b30!05/20/2015-13:19:13:: i INFO: Initializing EnableExecutionLogging to 'True'  as specified in Server system properties.
library!WindowsService_16!37e0!05/20/2015-13:19:43:: i INFO: Call to CleanBatch()
library!WindowsService_16!37e0!05/20/2015-13:19:43:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams, 0 segments, 0 segment mappings.
library!WindowsService_16!37e0!05/20/2015-13:19:43:: i INFO: Call to CleanBatch() ends

Please Suggest if there's any requirement or step i'm left with.

PS: Local SMTP is working, i.e. i am able to receive email if i place an email.text file in C:\inetpub\mailroot\pickup

It was seriously LOL.

I gave NetworkService user full control to mailroot folder residing in  C:\inetpub\ and Vola email was sent from localsmtp.

I bet if someone has faced this problem and has find any solution like same.

Reason OF Solution - login context/User that is rendering report on ReportServer and creating an email template to throw it in pickup directory residing in C:\inetpub\mailroot  should be "Network Service"

Question/Doubt Left
Now the confusion left is can we change this user as my service account user i.e. local Administrator was having  full control on  mailroot folder but it was not working till i opted above mentioned solution.


Powershell Restore Azure SQL Database to local with Restore-Database I have written a powershell script that uses Start-AzureSqlDatabaseExport & Get-AzureStorageBlob to export my Azure DB to a bacpac and download it locally, the next step is to restore the bacpac to my local SQL DB using Restore-Database, but below fails with "the media family on device is incorrectly formatted".

Restore-Database -dbName "MyDB" -backupFile "C:\MyAzureBackup.bacpac"

Is there a way to get this working with Restore-Database, or is there an alternative command that will do this?

I have not actually tried this (but it peaks my interest to later).

You will not be able to use Restore-Database because it only reads native backup formats from SQL Server, not Azure SQL.

The BACPAC file created from your backup of an Azure SQL database is in a data-tier format. Which in this format contains the schema and data for the database. In order to restore this to a local instance in PowerShell you will have to utilize the DAC objects to do the restore. Which if you are loading the SQLPS module the DAC assembly is loaded for you.

Basic examples that I find online seem to follow this format:

Add-Type -path "C:\Program Files (x86)\Microsoft SQL Server\<version>\DAC\bin\Microsoft.SqlServer.Dac.dll"
$restoredDatabaseName = 'NewDatabase'
$bacpacFile = "Your BACPAC file you downloaded"
$conn = "Data Source=Server;Initial Catalog=master;Connection Timeout=0;Integrated Security=True;"

$importBac = New-Object Microsoft.SqlServer.Dac.DacServices $conn
$loadBac = [Microsoft.SqlServer.Dac.BacPackage]::Load($bacpacFile)
$importBac.ImportBacpac($loadBac, $restoredDatabaseName)

#Clean up
$loadBac.Dispose()

Once I get something setup I will test the above myself, just to make sure.

Update

The dbatools PowerShell module now includes commands around managing DacPac files. This can be utilized to perform export and publish operations.

Publish-DbaDacpac. I have not had chance to test this against an Azure SQL instance.


SQL Server 2005 database corruption. Advise I have encounter some database corruptions in the past but I knew which object has hit the road. In this situation I run a DBCC CHECKDB against a problematic  SQL Server 2005 database and I get the following errors

    Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:2388) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:8807) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
Msg 8929, Level 16, State 1, Line 1
Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 281474980642816 (type In-row data): Errors found in off-row data with ID 1454243840 owned by data record identified by RID = (1:728:2)
Msg 8928, Level 16, State 1, Line 1
Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data): Page (1:2388) could not be processed.  See other errors for details.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). The off-row data node at page (1:2388), slot 0, text ID 1454243840 is referenced by page (1:728), slot 2, but was not seen in the scan.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'sys.sysobjvalues' (object ID 60).
Msg 8928, Level 16, State 1, Line 1
Object ID 393768460, index ID 1, partition ID 72057594063421440, alloc unit ID 72057594069647360 (type In-row data): Page (1:8807) could not be processed.  See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 393768460, index ID 1, partition ID 72057594063421440, alloc unit ID 72057594069647360 (type In-row data). Page (1:8807) was not seen in the scan although its parent (1:2862) and previous (1:8806) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 393768460, index ID 1, partition ID 72057594063421440, alloc unit ID 72057594069647360 (type In-row data). Page (1:10128) is missing a reference from previous page (1:8807). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'reservation_packages' (object ID 393768460).
Msg 8928, Level 16, State 1, Line 1
Object ID 1513772450, index ID 0, partition ID 72057594064994304, alloc unit ID 72057594071285760 (type In-row data): Page (1:5211) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1513772450, index ID 0, partition ID 72057594064994304, alloc unit ID 72057594071285760 (type In-row data), page (1:5211). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 63047689 and -4.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'reservation_services_log' (object ID 1513772450).
CHECKDB found 0 allocation errors and 10 consistency errors in database 'ChJ'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (ChJ).

Object ID 0?? index ID -1 ?? partition ID 0?? I cannot make out anything out of it

The database is in Simple Recovery mode (unfortunately) and I have a month old backup.

I must have it up an running because it contains vital data.

I would appreciate any help you could give me.

Thanx in advance!

EDIT1:Unfortunately Red Gate's Data Compare cannot register the database while it is scanning the tables... :(

As you have an old backup with the correct schema, the problem database is online and you've successfully queried several tables, I'd be inclined to try get a dump of the raw data as fast as possible.

Shut down the applications accessing the database.
BCP export the data, table by table to files. As disk corruption could be the source of your woes, export the data to a portable drive or to a fileshare on another machine.
Restore your old backup to a known good server.
Depending on the complexity of the database schema either truncate the data from your restored backup or script out the schema and create a new empty database.
BCP the data back in.

The data may contain inaccuracies, it may be missing rows or contain spurious values. But with any luck you'll end up with more than was in the month old backup.


Best to isolate the database in multiple instances? I read on a forum about the use of database isolated environments are neccessary for any large project that handles "big" data. For instance I will be using the following table for ALL blogs:

projectname_blog_blogposts

We all know that the table will be very big. Probably hard to maintain, maybe awful situation if the table is corrupt or something like that. I don't know, I don't have any experience to confirm such a statement.

So instead of doing that. Is this approach better:

projectname_blog_1_blogposts
projectname_blog_2_blogposts
projectname_blog_3_blogposts

And then split them up like this:

DATABASE01

projectname_blog_1_blogposts

DATABASE02

projectname_blog_2_blogposts

DATABASE03

projectname_blog_3_blogposts

Sorry if the question is fuzzy. But I do hope you guys understand me.

Where can I read more about this so I can make a good choice?

the problem you are facing can be likened to the problem of multitenancy as you are talking about multiple applications (multiple blog) and one or more tables, schemas, databases, instances. A good starting point can be the following article from MSDN: Multi-Tenant Data Architecture. The article is focused on Microsoft SQL Server but it can be easily generalized.

Regards
Giovanni


pg_upgrade 9.2 to 9.3 error I'm trying to upgrade a PostgreSQL 9.2 database cluster to a 9.3 cluster on a new server.

I've copied the old 9.2 cluster (using rsync) to /srv/data/postgres on the new server and want to convert it to a new 9.3 cluster at /srv/home/postgres/data using:

/usr/pgsql-9.3/bin/pg_upgrade -d /srv/data/postgres/ -D /srv/home/postgres/data/ -b /usr/pgsql-9.2/bin/ -B /usr/pgsql-9.3/bin/ -u postgres --check

But I get an error:

SELECT c.oid, n.nspname, c.relname, c.relfilenode, c.reltablespace, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM info_rels i JOIN pg_catalog.pg_class c ON i.reloid = c.oid   JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid   LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid ORDER BY 1;
ERROR:  could not create temporary file "base/pgsql_tmp/pgsql_tmp13803.0": No such file or directory

If I create an empty 9.2 cluster on the new server and run the same check, I get no errors.

Permissions seem to be the same for both the old, copied 9.2 cluster and the new initialized 9.2 test cluster (test 9.2 cluster on top; copied 9.2 cluster on bottom):

It's not clear to me what else I should try or what the problem is. Thanks!

A message is clean - it cannot create a temp file in "base/pgsql_tmp" directory. From some reason, this directory is missing.


How to know which user update/insert or delete in MYSQL database? Using the triggers I could make the establishment of a historic recordings in MYSQL tables where i detect all changes in my database.
My problem is that I want to know which user made the change in my database?

Depends on what version you are.

You can install the audit_plugin:

INSTALL PLUGIN server_audit SONAME 'server_audit';

You can choose two outputs, file or syslog. The output format will be:

20140901 15:19:44,localhost.localdomain,root,localhost,4,133,WRITE,employees,salaries,
20140901 15:19:44,localhost.localdomain,root,localhost,4,133,QUERY,employees,'DELETE FROM salaries LIMIT 100',0

WARNING NOTICE

If you enable the audit_plugin (it's right with all logging processes like SLOW LOW, GENERAL LOG...) be careful to not saturate your disks in term of I/O. Depends of your workload, prefer write these types of logs on dedicated disks to minimize the impact on your MySQL instances. You can also throw them to syslog but you should have the architecture to handle them (ELK for instance).

Max.


Database mirroring on Rackspace or Xeround I´m trying to resolve following problem.

I have 2 application servers on Rackspace that are connected to a load balancer. Both servers must have the same applications and share the same data. Basically on each server is a Java EE application that will be writing some data into database. This data will be accessible from outside. Each server must have the same data (so he has to take them from the other servers database and viceversa).

This fact means that I need to have 2 database servers (so that one database server is not so busy if we have big traffic). To have the same data on both database servers, I was thinking of mirroring one to another (Master - Master connection). I found a Xeround database cloud, and I was thinking of creating two database instances there and accesing them from Rackspace. (Server A -> Database server A; Server B -> Database server B). And Database server A <-> Database server B would have Master - Master connection (so both servers will have the same data periodically updated).

Is this a possible and effective solution ? Or is there a better solution?

I work for Xeround. With Xeround cloud database you do not need to mirror your database, you create a single database instance and direct your application servers to work with this instance. Hope this helps!

Avigail


Allocation Unit ID calculation is incorrect? I am reading the article http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-how-are-allocation-unit-ids-calculated/ on how to calculate the allocation unit ID based on m_indexId and m_objID. Then I try that on one of my database, by executing the command below:

    DBCC TRACEON(3604);
    DBCC PAGE (UFDATA_008_2013, 1, 73057, 3);
    GO

And the output result is:

    PAGE HEADER:

    Page @0x10008000

    m_pageId = (1:73057)                 m_headerVersion = 1                  m_type = 1
    m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x8000
    m_objId (AllocUnitId.idObj) = 1913890731                                  m_indexId (AllocUnitId.idInd) = 0
    Metadata: AllocUnitId = 406903719657472                                   Metadata: PartitionId = 406903719657472
    Metadata: IndexId = 1                Metadata: ObjectId = 1913890731      m_prevPage = (1:92774)
    m_nextPage = (0:0)                   pminlen = 11                         m_slotCnt = 61
    m_freeCnt = 3241                     m_freeData = 4829                    m_reservedCnt = 0
    m_lsn = (4028:6215:30)               m_xactReserved = 0                   m_xdesId = (0:0)
    m_ghostRecCnt = 0                    m_tornBits = 0

Then I use the formula in the above article to calculate the allocation Unit ID as (0 << 48) + (1913890731 << 16) = 125428742946816, which does not equal to the output Metadata: AllocUnitId(406903719657472). Why?

I can confirm the page is allocated, since I use the following command to list all the pages allocated to table ‘AADomDataColSet’:

       DBCC TRACEON(3604);
       Dbcc ind(UFDATA_008_2013, ‘AADomDataColSet’, -1);

And the result is below:

You can find the page (1, 73057) is allocated to this object.

Thanks

This seems identical to a case discussed in the comments to Paul's post. He indicated the cause is that the page is no longer allocated (notice metadata IndexId = 1, but m_indexid = 0).
Decoding the allocation unit ID using Paul's script:
DECLARE @alloc BIGINT = 406903719657472;
DECLARE @index BIGINT;

SELECT @index =
    CONVERT (BIGINT,
        CONVERT (FLOAT, @alloc)
            * (1 / POWER (2.0, 48)) -- right shift, reciprocal of left shift
    );
SELECT
    CONVERT (BIGINT,
        CONVERT (FLOAT, @alloc - (@index * CONVERT (BIGINT, POWER (2.0, 48))))
            * (1 / POWER (2.0, 16)) -- right shift, reciprocal of left shift
    ) AS [m_objId],
    @index AS [m_indexId];

Result:

This matches the m_objid in your DBCC PAGE output.
Updated question
The calculation is correct if an index ID of 1 is used, again using the formula in Paul's post:
SELECT allocation_id =
    1 * CONVERT (BIGINT, POWER (2.0, 48)) | -- index id
    1913890731 * CONVERT (BIGINT, POWER (2.0, 16)); -- object id

So your actual question seems not to be about the allocation unit calculation at all; rather you are asking why DBCC PAGE shows  m_indexId (AllocUnitId.idInd) = 0 when the page is allocated and associated with index 1 - as shown in the DBCC IND output.
That, I don't know offhand. What I do know is that the calculation works both ways for me, testing on SQL Server 2012 build 6518. Perhaps there is something about your SQL Server version or table that means AllocUnitId.idInd is not being reported correctly. Without specific, reproducible details, you might have to ask Paul himself (though he's currently on holiday).
It's probably some edge case. For example, this post says that there may be differences in m_indexId if the database was upgraded from SQL Server 2000. Try rebuilding the table, that oftens sorts these sorts of things out.


Encrypted Connection on Sql Server I want to make SQL server connection encrypted. I know that a certificate is needed, but I haven't been able to.

So far, I:

created certificate in IIS.
added certificate in MMC "Certificates" snap-in.
in SQL Server Configuration manager for my server:

registered certificate
forced secure login
stopped & started "MSSQLSERVER" service

Starting SQL Server service always throws an error. Is there something wrong with the above steps? What are the correct steps?

I am using SQL Server 2012 and Windows Server 2012 R2.

To create a certificate for SSL encryption for SQL Server you need a certificate for Server authentciation. That is [EnhancedKeyUsageExtension]
OID = 1.3.6.1.5.5.7.3.1

If you want to manually create a certificate for this using Windows Certificate services you need to do it like this.

(http://blogs.technet.com/b/pki/archive/2009/08/05/how-to-create-a-web-server-ssl-certificate-manually.aspx)

Create a file named cert.inf

[NewRequest]
Subject = "CN=*FQDN*"
HashAlgorithm =  SHA256
KeyLength = 2048
Exportable = TRUE
KeySpec = 1
KeyUsage = 0x20
MachineKeySet = TRUE
ProviderName = "Microsoft RSA SChannel Cryptographic Provider"
ProviderType = 12
[EnhancedKeyUsageExtension]
OID = 1.3.6.1.5.5.7.3.1
[RequestAttributes]
CertificateTemplate= WebServer

And then generate the certificate using CERTREG.EXE

Certreq –new cert.inf cert.req
Certreq –submit cert.reg

To do the same using OpenSSL

# generate key
openssl genrsa -des3 -out server.key 2048

# remove pass
openssl rsa -in server.key -out server.key

# generate sign request, be sure to include the correct FQDN
# (host name followed by primary dns suffix)
openssl req -new -key server.key -out server.csr

# generate self signed certificate
openssl x509 -req -in server.csr -signkey server.key -out server.crt

# include both the certificate and the private key in a PKCS12 keystore
# (leave the export key empty)
openssl pkcs12 -export -out server.p12 -inkey server.key -in server.crt

In both cases you end up with a CRT that you can import to the machine store of the SQL Server and then use the SQL server configuration manager to encrypt the connections. You will have to trust the root certificate in the latter case though and in the former case, if you are not running on a domain you will have to install the root certificate for the windows CSA on both the machines


Make SQL query "latency proof" I have developed an application which connects to a database over a WAN.

As long as I'm using the application on my local network, everything works fine.
When I connect over the WAN, the database request takes several seconds. The user experiences long load times when using the application from his location. The loading times are so long, that it isn't workable.

Most of the time, the query selects all the data from a table. Sometimes it only selects one line.

I think this is due to latency on the WAN. Does anyone know how I can make my queries "latency-proof"?

You can improve the WAN link or improve the query to make it more efficient.
Other than that you are limited by the laws of physics as the data has to travel back and forth through the link there is no getting around that.


Update Database Fields Based On Date I need a trigger that updates a table row field by checking date that is stored in their own field without any interaction of user. i.e I am working on a project where I have to book rooms for user for a particular date and time and at that case I want to show room status as "booked" but after expiry of that date and time our database automatically update room status "booked" to "available". means I want that database keep track of their record and update it automatically. How I can achieve this?

As Martin Smith said in the comments, there is no Date/Time based trigger, therefore you have two options.

Batch Update - Use a batch process to update the data.  This requires determining how often the data needs to change and then creating a batch process that runs at least that often to update the data.  If the information changes once a day this might be acceptable, but as the update need gets more frequent this solution becomes less usable.
Dynamic Evaluation - As Martin Smith suggested, determine availability when the information is requested.  This can be in the select statement itself or you can use a computed column on the table.  In either case the data will be as up to date as it can be.


How restore a specific database from backup using mongorestore command I created a backup of all my databases using mongodump command. Now I want to restore a specific database using mongorestore command.

How is this possible, I use this command: --db option then mongodb doesn't restore a specific database.

To restore a single database you need to provide the path to the dump directory as part of the mongorestore command line.

For example:

# Backup the training database
mongodump --db training

# Restore the training database to a new database called training2
mongorestore --db training2 dump/training

The --db option for mongodump specifies the source database to dump.

The --db option for mongorestore specifies the target database to restore into.


How to store mp3 files in MySQL database using phpMyAdmin? I am new to databases, but I want a database for my site to store mp3 files in it, so later I play them on my site by using jplayer.

They say there are two ways to store data (in this case mp3 files) in the database:

By storing actual file itself in the database.
By only storing particular file URL, address, location or metadata, etc.

My question is what is the right way and how can I do that in phpMyAdmin?

In my personal opinion (however I am new to databases as well), I would look to store the relative URL,  which would be the VARCHAR from here you could store the relative path to the file (from where the script is being called from)  and load the file that way.

However it may be worth writing the SQL for your tables as well.  Just in case you need to alter or tweak your table structure in the beginning it makes it simpler to alter.  so it would look something like this:

create database music;

use music;

create table tbl_album(
    album_id int(5) not null auto_increment,
    album_name varchar(50)  not null,
    album_artist varchar(50) not null,
    album_genre varchar(50),
    ablum_year varchar(4),
    primary key(album_id)
);

create table tbl_tracks(
    track_id int(5) not null auto_increment,
    track_number int(2),
    track_name varchar(50) not null,
    album_id int(5),
    track_url varchar(100) not null,
    primary key(track_id),
    foreign key (album_id) references tbl_album(album_id)
);

PS: If you do choose to write the SQL for your tables I would recommend MySQL workbench which is free and a nice tool to use (http://mysqlworkbench.org/)


Optimization for branched WHERE I have a query that executes against a table that can grow to millions of rows.  The query comes out of a QA tool that we use that is outside the standard functionality of the DB (as far as what is indexed and how and why).  The query is:
SELECT id FROM thisTable t
WHERE col = 'val'
AND ((not exists (SELECT 1 FROM thisTable WHERE refid = t.id) and refbool = 0) or refbool = 1)
ORDER BY newid()

Basically, let's say the table has id, refid, refbool, and col columns.  So you could have data as follows:
  id  |  refid  |  refbool  |  col
------------------------------------
   1  |   NULL  |    0      |  val
   2  |   NULL  |    0      |  val
   3  |   NULL  |    0      |  val
   4  |    2    |    1      |  val
   5  |   NULL  |    0      |  val
   6  |    1    |    1      |  val

The query should never pick the rows for id in (1, 2) because they are referred to by other rows.  It should only grab rows where refbool = 1, OR refbool = 0 AND that row's id is not any other row's refid.  This statement is horribly non-performant, but I'm not sure what a better query would look like for this.  Assume that no indexes, views, stored procedures, or other underlying machinations can be added - it must be a query.
The overall query is significantly larger, JOINS to two additional tables and gathers quite a bit of data.  However, I've narrowed it does to this particular bit as commenting out this line takes the query execution time from 16s to <1s.
I'm also reordering the rows by newid() as I need to randomly select a sample item.  Removing the ORDER BY also make the query significantly faster even leaving the third row in.  It appears that the two operations combined causes the slowness.  I've tried designing a CTE, but have failed to increase performance doing so.
I've looked at the execution plan. There are indices that would be added that would improve this query. However, performance of internal QA tools do not take precedence over performance in client production environments, and making changes to the structure in a QA environment for the utility in relation to indices, etc. invalidates it's usefulness as a QA environment as it will likely perform differently than a production environment.
I could certainly write a query that would perform worse than my current query by changing the logic of the query itself. I'm sure we all could. I'm asking to apply that sort of reasoning to improve the performance of the query instead.

An execution plan was not included, but the typical issue with queries like this (sorting aside) is the optimizer choosing a nested loops anti semi join without a good supporting index. It can also be a rogue top (1), or a poorly-performing transformation to a semi-join with nested start-up filters and an anti-semi join.
Regardless, there are two usual workarounds:

Rewrite the OR manually as a UNION (or, if guaranteed disjoint, as a UNION ALL).
Rewrite the NOT EXISTS as a left join filtering the preserved side for NULL.

The following incorporates both:
DECLARE @thisTable table
(
    id integer PRIMARY KEY,
    refid integer NULL,
    refbool bit NOT NULL,
    col varchar(10) NOT NULL
);

INSERT @thisTable
    (id, refid, refbool, col)
VALUES
    (1, NULL, 0, 'val'),
    (2, NULL, 0, 'val'),
    (3, NULL, 0, 'val'),
    (4,  2  , 1, 'val'),
    (5, NULL, 0, 'val'),
    (6,  1  , 1, 'val');

SELECT
    U.id
FROM
(
    -- T.refbool = 1
    SELECT T.id
    FROM @thisTable AS T
    WHERE
        T.col = 'val'
        AND T.refbool = 1

    -- Or (disjoint)
    UNION ALL

    -- T.refbool = 0 and not exists
    SELECT T.id
    FROM @thisTable AS T
    LEFT JOIN @thisTable AS T2
        ON T2.refid = T.id
    WHERE
        T.col = 'val'
        AND T.refbool = 0
        AND T2.id IS NULL
) AS U
ORDER BY
    CHECKSUM(NEWID());

db<>fiddle online demo
For more alternatives on random ordering see the existing Q & A:

What is the best way to get a random ordering?.

Don't just try the top answer.


How to link a MySQL Table with a binary key into Access? In MS Access I've created a linked table via ODBC to a MySQL table with a binary(16) primary key column (UUID). MS Access only shows the first row, all other rows have #deleted entries. I guess the reason for this is the binary field. I've checked the ODBC driver properties without success. Do you have a solution to this problem?

Details: I'm using Access 2010 64 bit and "MySQL ODBC 5.2a Driver". Opening the linked table looks like this:

The MySQL table has following Definition (reduced):

+---------------+---------------+------+-----+------------------+-------+
| Field         | Type          | Null | Key | Default          | Extra |
+---------------+---------------+------+-----+------------------+-------+
| LNEr          | binary(16)    | NO   | PRI |                  |       |
| Datum         | datetime      | YES  |     | NULL             |       |
| StatNr        | int(11)       | YES  |     | NULL             |       |

MS Access generates following Definition:

I solved this by creating a updateable VIEW to the table. The view displays every column but the primary key LNEr is converted to a string with the HEX() function:

CREATE VIEW v_ereignisse AS SELECT HEX(LNEr) AS LNEr, Datum, StatNr, ...

In MS Access instead of creating a link to the table I created a link to the view. Because the view has no primary key, the "table link manager" asks for selecting one, this (of course) is the string version of LNEr.


Postgresql pgAdmin, count(*), max(pk_field) giving inconsistent results I am running postgresql 9.3.4 on a Centos 6.5 server as a back end for a web app which I am currently load testing.

Presently, if I visit the state table in pgAdmin, right click on "count", I get a value of 7535871

If I run select count(*) from state in the sql window, I get the value of 35871

The state_id column is the primary key and incremented for each row, running select state_id from state order by state_id DESC limit 1 gives the value 7537276

If I run select max(state_id) from state, I get the result 537276

Can anyone explain where all these discrepancies lie, or have I just got a very corrupt database? The load testing was hammering the database, so I wouldn't be massively surprised if there was corruption, however it is a worrying prospect so I need to first ascertain if there is another explanation.

Many thanks

EDIT

A Friend has suggested that on insert ignores/replace into type queries, the PK might update despite no new rows. You would assume the pgAdmin would account for this, and the function that adds rows to this table performs a simple insert into, so I'm not convinced. However, I would be interested in seeing if there are any gaps in the primary key field. Suggestions as to best do this are welcome, although I realise that is effectively a different question.

Thanks to Hannah Vernon for pointing out the obvious truncation going on, which I'm quite annoyed with myself for not noticing.
Having re run all the queries above in psql, they return the full, non-truncated numbers. Re-running them again in the pgAdmin sql window, I get truncated values. What is most strange is that there is different precision in the truncation and it is reliably so. Who am I to guess at the logic, I'm sure it's a bug and I will look to see if there is a bug tracker for the software.
Many thanks to all for your comments.
EDIT
Here is a comment about column truncation in the FAQ, however it doesn't go into any detail. I would assume this is for long text columns rather than integers over 5 digits, and I can't see where the option it refers to are, but I thought it prudent to include it anyway:

Query tool columns truncated
Some columns are truncated when running a query in the query tool.
You can increase the query option "max. chars per column" for this.
Please note that there's another limit for this imposed by the
underlying windows control, which apparently doesn't allow more than
511 characters.  In pgAdmin V1.1 and up, we provide the function
"execute to file", which has no column restrictions. [AP]

http://www.pgadmin.org/pgadmin3/faq/#ColTrunc


How would I change this relational model to fit a nosql database? Because of things beyond my control, I have to switch from a relational database to MongoDB for the advertising platform I'm helping to build. I've got a relational schema sketched out, but I'm unsure where to begin switching to nosql. I'd like to know what tables it makes sense to embed, and where it makes sense to denormalize.

Here's my current schema (as you can see, it's pretty normalized):

Users: id, name, email, paypal_email, password, balance, created_at, updated_at

Clicks: id, bid_id, server_id, origin_ip, valid, created_at

Ads: id, user_id, title, content, link, created_at, updated_at

Bids: id, ad_id, continent, current, auto, max, created_at, updated_at

Servers: id, user_id, ip, created_at, updated_at

People: id, username, ip, server_id, created_at, updated_at

Transactions: id, amount, transactable_id, transactable_type, created_at

We'll be making pretty heavy use of these tables. I expect to get many click creations, people updates, and transaction creations per second. We obviously want to be able to provide statistics like clicks per ad, impressions, etc. This information will be updated real-time on our site.

Finally, at the end of each day or two, we'll be performing large queries against clicks in relation to a couple of other tables.

Is this realistic to do with something like MongoDB?

"Is this realistic to do with something like MongoDB?"

If "realistic" means can be engineered to produce acceptable performance then the answer depends on whether you will do with appropriate benchmarking before putting it into production.  You can replace "MongoDB" in your question with anything from Access to Oracle and the answer remains basically the same.

The MongoDB documentation has some good information on considerations for data modeling and performance.  I would start there and lean towards a "collection" per each of your original source tables.

The important step is to benchmark, benchmark and benchmark.  I would approach this from the standpoint of proving that MongoDB can't handle the workload. There are three possible outcomes:

You prove that it won't work. Maybe it's due to technical limitations or it would require excessive HW to meet your SLA. If they proceed you can't be held responsible ("I told you so").
You fail prove that it won't work and after it's in production it doesn't work then at least you tried.  Everyone is surprised and there is a big scramble for an alternate solution.
You fail to prove that it won't work and it works great. You will sleep good at night feeling confident in the solution forced down your throat.

Start by benchmarking the "critical paths". If you can't get MongoDB to handle the incoming click traffic then it's "case closed". Start with an aggressive goal, maybe 300% of what business users project traffic will be. Figure out how many documents/sec you can load with your initial schema.  Is it reasonable?  Change the schema and benchmark again. Is it better or worse?  How does it scale with additional HW?  Try scaling UP and OUT.  Try multiple clients performing inserts. Next look at the updates.  Can you meet your performance goals?

Lastly look at reporting.  Reporting performance is typically an easy problem that is solved by additional hardware.  If the reports are ran every day or so then you should have some leeway in expected performance.

Conclusion

There is nothing special about MongoDB compared to the evaluation of any other database technology.  Start with technical requirements and benchmark, benchmark, benchmark.


Oracle 11G Startup failed after altering the number of system processes When I alter the number of system processes to 100000 and restart the database, I get the error below:

ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semids_per_proc failed with status: 0
ORA-27301: OS failure message: Error 0
ORA-27302: failure occurred at: sskgpwcr2
ORA-27303: additional information: semids = 524, maxprocs = 100000

How can I return it to the normal processes?

First you can try:

STARTUP NOMOUNT
ALTER SYSTEM SET processes=400;
SHUTDOWN IMMEDIATE
STARTUP

If Oracle cannot start even in NOMOUNT mode then you can fetch configuration information from spfile:

strings $ORACLE_HOME/dbs/spfile$SID.ora > /tmp/pfile

Then edit processes in /tmp/pfile cleanup some mess made by strings start Oracle with pfile and recreate spfile:

STARTUP pfile=/tmp/pfile
CREATE spfile FROM pfile='/tmp/pfile';


SQL Server 2000 Backup fails with Status = 33 I am trying to take a Full Backup of a database size about 75GB.

It is SQL Server 2000 - 8.00.2055 x86  version. I have plenty of disk space where the backup is being created. So disk space isn't an issue.

The backup process starts fine but half way through it errors out just saying with status = 33.

I have been looking for any information online but couldn't find anything....  Any suggestions any solutions any pointers in the direct direction is much appreciated.......

Further investigation and "Googling" solved the issue, The issue was with drive, It was compressed and SQL Server did not like that.

Solution

Removed the compression from Drive and this time backup completed without any errors and much faster.


mysqldump: flush-privileges option I'd like to get some clarification about the --flush-privileges option for mysqldump.

Here's the description of the option from the MySQL docs:

--flush-privileges
Send a FLUSH PRIVILEGES statement to the server after dumping the mysql database.
This option should be used any time the dump contains the mysql database and any other
database that depends on the data in the mysql database for proper restoration.

When it says that it sends the flush statement after dumping the database, I read that to mean that the data, schema, etc. is dumped into the backup file and then the flush statement is sent to the database that was just dumped (after it was dumped).

I was wondering what dumping the data, etc. did that required the privileges to be flushed, so I started searching for an explanation to be sure when and why to use it.

As I read various answers, it occurred to me that it would make sense if the flush statement was being included in the dump file. Then after the contents of the file was loaded into a database, the flush privileges statement was run to update the settings after the new info was imported.

So, how does it work?
A) Flush the source database after dumping the data to a file? If so, why is this necessary?
B) Flush the destination database after importing the contents of the dump file?
C) Something other than the possibilities I've described?

The documentation is misleading.  I read it exactly the same way you do, which isn't what the utility does.

Adding --flush-privileges causes mysqldump to include the following in the backup file, after dumping the mysql schema...

--
-- Flush Grant Tables
--

/*! FLUSH PRIVILEGES */;

...which of course causes the server where the dump is being restored to re-read the potentially-changed grant tables.

And, that's all it does.

Confirmation of this can be found in the source code for mysqldump "10.13" (the version bundled with MySQL Server 5.5.30):

if (flush_privileges && using_mysql_db == 0)
{
  fprintf(md_result_file,"\n--\n-- Flush Grant Tables \n--\n");
  fprintf(md_result_file,"\n/*! FLUSH PRIVILEGES */;\n");
}

The using_mysql_db == 0 comparison was confusing at first, but it turns out to be the return value from a string comparison function where "0" means "identical."


Deadlocks in SQL Server - how to avoid? As per the code mentioned below, I am trying to understand how 'Deadlock' can be avoided with the help of a table and a single row. Could someone help me understand how this technic can help avoid deadlock situations.?

Disclaimer:
I found this from an answer to a different but relevant question in this forum but I could not add a comment on that thread.. Hence thought to raise it as a question by itself..

The idea is to - Instead of invoking sp_getapplock, we can use the following table to avoid deadlocks

CREATE TABLE dbo.DeadlockTestMutex(
ID INT NOT NULL,
CONSTRAINT PK_DeadlockTestMutex PRIMARY KEY(ID),
Toggle INT NOT NULL);
GO

INSERT INTO dbo.DeadlockTestMutex(ID, Toggle)
VALUES(1,0);

Once the above table is ready and populated with the data as shown above, use this statement inside your stored procedures

UPDATE dbo.DeadlockTestMutex SET Toggle = 1 - Toggle WHERE ID = 1;

Such that, the stored procedures are as follows:

ALTER PROCEDURE dbo.UpdateCounter1 @Key1 INT
AS
SET NOCOUNT ON ;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ;

----------
UPDATE dbo.DeadlockTestMutex SET Toggle = 1 - Toggle WHERE ID = 1;
----------

UPDATE dbo.DeadlockTest SET Counter1=Counter1+1 WHERE Key1=@Key1;
SET @Key1=@Key1-10000;
UPDATE dbo.DeadlockTest SET Counter1=Counter1+1 WHERE Key1=@Key1;
COMMIT;
GO

ALTER PROCEDURE dbo.UpdateCounter2 @Key2 INT
AS
SET NOCOUNT ON ;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ;

----------
UPDATE dbo.DeadlockTestMutex SET Toggle = 1 - Toggle WHERE ID = 1;
----------

SET @Key2=@Key2-10000;
UPDATE dbo.DeadlockTest SET Counter2=Counter2+1 WHERE Key2=@Key2;
SET @Key2=@Key2+10000;
UPDATE dbo.DeadlockTest SET Counter2=Counter2+1 WHERE Key2=@Key2;
COMMIT;
GO

  So, how does adding this one liner can avoid deadlocks?

Here is the code for the table used by the stored procedures..

CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY);
GO

INSERT INTO dbo.Numbers
    ( n )
        VALUES  ( 1 );
GO
DECLARE @i INT;
    SET @i=0;
WHILE @i<21
    BEGIN
    INSERT INTO dbo.Numbers
        ( n )
        SELECT n + POWER(2, @i)
        FROM dbo.Numbers;
    SET @i = @i + 1;
    END;
GO

SELECT n AS ID, n AS Key1, n AS Key2, 0 AS Counter1, 0 AS Counter2
INTO dbo.DeadlockTest FROM dbo.Numbers
GO

ALTER TABLE dbo.DeadlockTest ADD CONSTRAINT PK_DeadlockTest PRIMARY KEY(ID);
GO

CREATE INDEX DeadlockTestKey1 ON dbo.DeadlockTest(Key1);
GO

CREATE INDEX DeadlockTestKey2 ON dbo.DeadlockTest(Key2);
GO

Due to the fact that both stored procedures issue

UPDATE dbo.DeadlockTestMutex SET Toggle = 1 - Toggle WHERE ID = 1;

before attempting to execute any other code, the first stored procedure to successfully run that update will get an exclusive lock on dbo.DeadlockTestMutex and the other process will wait until that lock is released.  This allows each stored procedure to run to full completion without competition from the other stored procedure.


Write-lock a whole table during transaction I need to perform a delicate operation to my table in which I will solely insert, delete and select upon all of my rows and no God may interfere with the table during this operation: the table will be in an inconsistent state and any concurrent MySQL session shall not be allowed to modify it until commit-ed.

The use of SELECT ... FOR UPDATE | LOCK IN SHARE MODE is not suitable because, while it may potentially be used to lock all the rows in the table, it won't prevent the insertion of further rows by a concurrent session. Basically, I need to LOCK TABLES my_table WRITE within the body of a transaction.

The table contains about 20,000 rows and a master-slave, mixed-format replication is in place over a slow connection so, for any workaround, I'd prefer to avoid using temporary tables which may faint the slave, and the amount of data dumped into the binlog should ideally be minimized.

The engine is InnoDB on MySQL 5.6, for both master and slave.

The Slave should be irrelevant, unless you are using (misusing) some form of parallel execution on the Slave.

Try other transaction_isolation_modes.

GET_LOCK()/RELEASE_LOCK() may provide a sufficient "lock"; but all writers to that table(s) would need to use it.

Do not turn on auto-reconnect in your connection.  An network glitch will mess with both the transaction and GET_LOCK.