Sunday, June 9, 2013

[how to] MSSQL - Can a partitioned table span on multiple files across different file groups?

[how to] MSSQL - Can a partitioned table span on multiple files across different file groups?

MSSQL - Can a partitioned table span on multiple files across different file groups?

Posted: 09 Jun 2013 07:39 PM PDT

In SQL 2008 and above, if a table is partitioned, can it span across multiple files across multiple filegroups?

Thanks

Remove Mac OSX Lion pre-installation of PostgreSQL

Posted: 09 Jun 2013 12:44 PM PDT

I'm running Lion 10.7.5, and I'm trying to install Postgresql 9.2.4 for use with Django. I have hit countless roadblocks and errors, so I would like to start with a totally clean slate.

I did a brew uninstall postgresql and removed any lingering folders from my attempted 9.2.4 installation. Now, I would like to completely remove the Mac installation psql -V 9.0.10.

What is the best method for doing this? or, is this definitely not recommended?

Note: I do not have a postgresql uninstaller app, so I'm interested in which files I need to manually delete (unless there's a better way of course.)

DBCC CHECKDB command creating database file with same name but with space character in extension

Posted: 09 Jun 2013 08:32 PM PDT

I am running simple DBCC CHECKDB ('DBNAME') command on my machine every thing works perfect!

But when i execute same command on the live server the command executes without any error, but it creates one extra file int the same location as my mdf file. For instance, if my mdf file name is "mydatabase.mdf" then this command is creating one more file with name "mydatabase.mdf ". Additionally, I am not able to delete this file.

Whats wrong here?

BEFORE INSERT trigger in MySQL

Posted: 09 Jun 2013 06:29 AM PDT

I am new to MySQL and I'm having problems with a BEFORE INSERT trigger. I get an "unexpected END" error.

I have a table named verlof_aanvragen with a column datum (and 6 more columns).

What I want to accomplish is only rows with a date between now()+8 and now()+365 or CURDATE() + INIERVAL 8 DAY AND CURDATE() + INTERVAL 365 DAY) can be inserted. An insert with a date outside that interval should fail.

I am using MySQL version 5.5.24.

This is the code:

CREATE TRIGGER chk_dates    BEFORE INSERT ON verlof_aanvragen    FOR EACH ROW    BEGIN      IF (NEW.datum < CURDATE() + INTERVAL 8 DAY OR NEW.datum > CURDATE() + INTERVAL 365 DAY)   THEN        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Any Message'    END IF    END

Having a PostgreSQL database exposed to the internet

Posted: 09 Jun 2013 04:59 AM PDT

I've seen on DBA and other sites that exposing a database to the Internet is a particularly bad idea, but I noticed that PostgreSQL has SSL-certificate-only authentication. Does this make it any safer to expose a database to the internet (cert-only auth from non-localhost connections) or would it still be better to establish a VPN, or tunnel through SSH etc?

If the latter is safer, what exactly makes it safer? I feel like I am overlooking something glaringly obvious in this regard.

I've set up a handful of databases before but never actually needed to set one up that requires some sort of remote administration (“remote” in this case meaning over the Internet or WAN rather than a LAN).

How to rebuild entire MySQL data after server re install?

Posted: 09 Jun 2013 05:21 AM PDT

I have a MySQL server running with multiple databases in a 32 bit environment. I need to rebuild my system to a 64 bit environment.

Is it possible to replicate the entire MySQL databases, users, privileges, settings, etc. after the rebuild?

How can I model this problem in a graph database?

Posted: 09 Jun 2013 01:09 AM PDT

I have a project that I'm working on where I extract data from PDFs and map/visualize the relationships between the extracted pieces.

Here's an example of my problem:

file: 11425646.pdf    author: bob    company: abc co    date: 1/1/2011    mentioned_users: [alice,sue,mike,sally]    images: [1958.jpg,535.jpg,35735.jpg]    file: 15421484.pdf    author: betty    company: ionga    date: 2/15/2011    mentioned_users: [john,alex,george]    images: [819.jpg,9841.jpg,78.jpg]    file: 11975748.pdf    author: micah    company: zoobi    date: 9/26/2011    mentioned_users: [alice,chris,joe]    images: [526.jpg,5835.jpg,355.jpg]

How can I model this in a graph database like Neo4j?

I would like to be able to be given one piece of data (like a person's name) and find all related (images, co-mentions, authors, etc.) at up to 10 depth. Here's what I'm thinking for the structure, but I'm not sure if it's a good approach: (this isn't any kind of actual syntax)

[file: 11425646.pdf date:1/1/2011] -written_by-> bob  [file: 11425646.pdf date:1/1/2011] -from_company-> abc co  [file: 11425646.pdf date:1/1/2011] -mentions-> alice  [file: 11425646.pdf date:1/1/2011] -mentions-> sue  [file: 11425646.pdf date:1/1/2011] -mentions-> mike  [file: 11425646.pdf date:1/1/2011] -mentions-> sally  [file: 11425646.pdf date:1/1/2011] -has_image-> 1958.jpg  [file: 11425646.pdf date:1/1/2011] -has_image-> 535.jpg  [file: 11425646.pdf date:1/1/2011] -has_image-> 35735.jpg

Is this the right way to structure this data in a graph database?

What's the practice to generate long user id like those on Facebook?

Posted: 08 Jun 2013 10:54 PM PDT

Now I am about to launch a site and I have trouble coming up with a way to create long user id(e.g. 4000002).

Do I set id to auto-increment(Starts at 0) and then add something(e.g. 4000000) to this id and put this new number in a new column in my table?

Does any body have a better practice to achieve this?

Thanks in advance!

User roles and permissions

Posted: 09 Jun 2013 05:59 AM PDT

Look at the attached graphic.

I'm trying to keep this as simple as possible.

Basically I want to have multiple admins(or superusers) who own regular users who own sites.

  • Admins can give regular users permission or role to a website.
  • Admins can see and edit all sites of the users they own.
  • Users can only see & edit their own sites. (based on the role).
  • One site can be managed by multiple users.

I don't know if I should have permissions table with multiple rows for each role so one user can have many rows for each role or maybe use an array in one row only... Idk. I'm stuck there.

What's the best way to do this simply?

P.S: I know the graphic has User2 under site1 and site2 but I don't need it that complex. Same users for same site but different roles is sufficient. No need for 1 regular user to edit 2 sites.

enter image description here

In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table

Posted: 09 Jun 2013 01:25 PM PDT

On Windows, I upgraded from 5.1 to 5.5 no problem.

  1. Copied my 5.1 data folder into my 5.5 instance
  2. Started mysqld skipping grants
  3. Ran mysql_upgrade

All good, but going from 5.5 to 5.6:

  1. Copied 5.5 data folder to 5.6 instance
  2. Started mysqld skipping grants
  3. Ran mysql_upgrade

but I get:

C:\Users\QAdmin>mysql_upgrade  Looking for 'mysql.exe' as: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe  Looking for 'mysqlcheck.exe' as: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlcheck.exe  Running 'mysqlcheck' with connection arguments: "--port=3306"  Running 'mysqlcheck' with connection arguments: "--port=3306"  mysql.user_info                                    OK  Running 'mysql_fix_privilege_tables'...  Running 'mysqlcheck' with connection arguments: "--port=3306"  C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlcheck.exe: Got error: 1130: Host 'localhost' is not allowed to connect to this MySQL server when trying to connect  FATAL ERROR: Upgrade failed

If I look at the mysql.user table it is completely empty.

  • Has anyone seen this or know what is going on?
  • During the "upgrade" the user table gets erased and when it tries to connect it can't?

Thanks.

SSRS 2008 R2 setup issue

Posted: 09 Jun 2013 04:25 PM PDT

I have installed SSRS 2008 R2 on my desktop and server. When I hit the reports link on my desktop

http://mypc/Reports_mypc/Pages/Folder.aspx

all I get to see is this home page of the desktop ssrs instance

I cant create a new folder or data source or anything of the sort

On the server where I am attempting to set up SSRS 2008 R2, all I get is a white screen that shows the virtual folder name in large fonts, followed by the version of the reporting services server on the next line. This is not leaving me any clues as to what needs to be fixed. On both pcs I am using the credentials of the local admin. Any clues on what needs to be fixed?

SQL Server Import Job Removed Data After Job Failed

Posted: 09 Jun 2013 07:06 PM PDT

I have a SQL Server Job set up to import data from a table to a table on another server, using the Import/Export data wizard.

In one instance, the job failed (with, I believe a SQL connection issue), and with that, the data from both tables were deleted. The tables on both the source and destination server were removed.

Does this make sense? If anything, why would the data be removed from the source?

The source is SQL Server 2012 and the destination is SQL Server 2008.

Get all the database in db2 through web

Posted: 09 Jun 2013 10:25 AM PDT

I would like to get all the databases available on a db2 instance from a C# application. The CLP command to be used is LIST DB DIRECTORY.

How can I fire this command from C#? Is there another select statement I can use to get all the databases on one db2 instance?

Database design for an E-commerce website

Posted: 09 Jun 2013 05:49 PM PDT

I am new to database design. I am designing a database for an E-commerce website, there are a lot of products to be updated, but while designing for product specification table I do not understand whether I need specify all the specifications in a table or do I need to use different tables for different products?

For example, consider the products Mobile and book, each of these having unique specifications (such as color,size, cost, model for mobile and title, ISBN, author, cost, year_of_publication, etc. for book), if it is only less number of products then it is possible for me to design, but when there is a thousands of products, it takes a lot of time.

Can anyone tell me how to design my database to manage this situation?

How to sync MySQL database between two servers

Posted: 09 Jun 2013 05:25 AM PDT

I need to sync a MySQL database between two servers.

If a new record will be added in server A, then server B fetch new record.

If a new record will be added in server B, then server A fetch new record.

I would like to query a range of criteria on multiple columns in MySQL

Posted: 09 Jun 2013 08:25 AM PDT

I have 3 columns in a mysql table. I'm using Innodb engine. I want to be able to search for some values on those columns and also anything close to those values. For example :

We have 3 columns as "Fast" "Smart" "Sharp" and they are of type boolean/int I have 1000 records in the table, as an example, I list 5 here.

Fast  |  smart  |  Sharp  ------------------------  0         1          1  1         1          1  0         0          1  1         1          1  1         0          0

and let's say we wanna search for people who are smart and sharp as 0 1 1 but if there is no record with the value of 0 1 1 we want to get the closest to it which is 1 1 1 or 1 0 1 or etc. So now when I search for it, I get the exact value, if the value doesn't exist, I won't get anything back from DB. So how can I achieve this and if I get a bunch of result, how can I sort them from the closets to my query to the feartest ?

Backing up a 400-500 GB database on MySQL/Amazon RDS?

Posted: 09 Jun 2013 08:25 PM PDT

We have an Amazon RDS small instance for a MySQL datawarehousing database. Database size will be around 400-500 GB very shortly. What is the best way to backup our database?

Can it be done efficiently with automatic backups and DB snapshots available with RDS?

Or should we consider some other options? What would those be? Using EC2, hosting in some other cloud, or using any other database?

What is the actual difference between innodb_autoinc_lock_mode 0,1,2

Posted: 08 Jun 2013 09:25 PM PDT

I would like to understand the differences between innodb_autoinc_lock_mode options 0,1 and 2 when parallel load data infiles are given.

I see in "0" option, it locks the entire table and does the First transaction requested for N number of records say TX1. So when next transaction say TX2 is raised in meantime when first transaction is still getting uploaded using "load data", then it has to wait in the queue for the first one TX1 to complete. And then it sets the max(AI_column)+1 value from the table and does upload for the next set of load data. In this case it doesn't jump the Auto Increment numbers.

Also I see in "1" option, it locks the entire table and does the First transaction requested for N number of records say TX1. So when next transaction say TX2 is raised in meantime when first transaction is still getting uploaded using "load data", then it has to wait in the queue for the first one TX1 to complete. And then it sets the max(AI_column)+1 value from the table and does upload for the next set of load data. And then it sets the max(AI_column)+some_creepy_jump.

But I see in "2" option, it doesn't lock the entire table. Instead it keeps inserts for each process simultaneously and inserting records for which ever request falls in randomly and ends all threads with average time around (1.21 sec for 7 parellel threads using load data having 1000000 record each). In this case it has multiple transactions in mixed order. And then it sets the max(AI_column)+some_creepy_jump atlast.

I'm using mysql 5.1.61 .

  • My question is what is the use of option 1?
  • And why does it stay as default later versions of Mysql 5.1.22 ?
  • Have anyone comeaccross any disasters with option 2?

As my project demands multiple processes using load data for a table. And I have tested the above options and finalized to go with option 2. Kindly correct me if my experiments are wrong.

Why would mysql "show global status" query be taking 15 minutes?

Posted: 09 Jun 2013 06:25 PM PDT

I'm reviewing the slow log, and on one of my slaves the average time for SHOW GLOBAL STATUS is 914s.

Any idea how to determine the cause of this?

Modify MySQL foreign key related column without dropping foreign key relation

Posted: 09 Jun 2013 06:25 AM PDT

I need to modify a foreign key related column. When I tried to alter it I got "Error on rename of 'x' to 'y' (errno: 150)" error. So I googled and found foreign key relation is the 'villain'. I dropped the foreign key relation, altered the column and created foreign key relation.

Is there any way to alter foreign key related columns without dropping the relation?

Is there a way to export Oracle's UNDO?

Posted: 09 Jun 2013 07:25 PM PDT

I tried exp utility to dump all database. Looks like this exports only the last version of data skipping undo log. Using flashback queries I see:

01466. 00000 -  "unable to read data - table definition has changed"  *Cause:    Query parsed after tbl (or index) change, and executed             w/old snapshot

What I'm trying to do is to capture db changes, make backup for later use with the ability to flashback to timestamp.

With rman backup I have similar situation:

ORA-01555: snapshot too old: rollback segment number 3 with name "_SYSSMU3_2472002983$"  too small   01555. 00000 -  "snapshot too old: rollback segment number %s with name \"%s\" too small"  *Cause:    rollback records needed by a reader for consistent read are overwritten by other writers`.

Update: I managed to do what I needed only by increasing undo retention and direct copying of data files and control file modification on cloned instance.

What is the difference between int(8) and int(5) in mysql?

Posted: 09 Jun 2013 11:25 AM PDT

I found out, that if you have a field defined as INT(8) without ZEROFILL it will behave exactly as INT(5)

in both cases the maximum value is

−2,147,483,648 to 2,147,483,647, from −(2^31) to 2^31 − 1

or do i miss something?

I found this Question: http://dba.stackexchange.com/a/370/12923

The (5) represents the display width of the field. From the manual, it states:

The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.

The display width, from what I can tell, can be used to left-pad numbers that are less than the defined width. So 00322, instead of 322. TBH, I've never used it.

But it doesn't affect the storage size of the column. An int will take up more space than a smallint.

so there seems to be no difference then.

Mysql innoDB write operations are extremely slow

Posted: 09 Jun 2013 01:47 AM PDT

I'm having serious performance problems with MySQL and the InnoDB engine. Even the simplest table makes writing operations (creating the table, inserting, updating and deleting) horribly slow, as you can see in the following snippet.

mysql> CREATE TABLE `test` (`id` int(11) not null auto_increment,     -> PRIMARY KEY(`id`)) ENGINE=InnoDB;  Query OK, 0 rows affected (4.61 sec)    mysql> insert into test values ();  Query OK, 1 row affected (1.92 sec)    mysql> insert into test values ();  Query OK, 1 row affected (0.88 sec)    mysql> insert into test values ();  Query OK, 1 row affected (1.10 sec)    mysql> insert into test values ();  Query OK, 1 row affected (6.27 sec)    mysql> select * from test;  +----+  | id |  +----+  |  1 |  |  2 |  |  3 |  |  4 |  +----+  4 rows in set (0.00 sec)    mysql> delete from test where id = 2;  Query OK, 1 row affected (0.28 sec)    mysql> delete from test where id = 3;  Query OK, 1 row affected (6.37 sec)

I have been looking at htop and the long waiting times are not because of abnormal CPU load. It's almost zero, and memory usage is also normal. If I create the same table using the MyISAM engine, then it works normally. My my.cnf file contains this (if I remember right I haven't changed anything from the default Debian configuration):

[client]  port        = 3306  socket      = /var/run/mysqld/mysqld.sock  [mysqld_safe]  socket      = /var/run/mysqld/mysqld.sock  nice        = 0    [mysqld]  user        = mysql  pid-file    = /var/run/mysqld/mysqld.pid  socket      = /var/run/mysqld/mysqld.sock  port        = 3306  basedir     = /usr  datadir     = /var/lib/mysql  tmpdir      = /tmp  language    = /usr/share/mysql/english  skip-external-locking  bind-address        = 127.0.0.1  key_buffer      = 40M  max_allowed_packet  = 16M  thread_stack        = 128K  thread_cache_size       = 8  myisam-recover         = BACKUP  max_connections        = 100  table_cache            = 64  thread_concurrency     = 10  query_cache_limit   = 1M  query_cache_size        = 40M  log_slow_queries    = /var/log/mysql/mysql-slow.log  long_query_time = 2  log-queries-not-using-indexes  expire_logs_days    = 10  max_binlog_size         = 100M    [mysqldump]  quick  quote-names  max_allowed_packet  = 16M    [isamchk]  key_buffer      = 16M  !includedir /etc/mysql/conf.d/

I have also tried to restart the server, but it doesn't solve anything.

The slow queries log doesn't give any extra information.

MySQL table relations, inheritance or not?

Posted: 09 Jun 2013 05:25 PM PDT

Im building a micro CMS. Using Mysql as RDMS, and Doctrine ORM for mapping.

I would like to have two types of pages. Static Page, and Blog Page. Static page would have page_url, and page_content stored in database. Blog page would have page_url, but no page_content. Blog would have Posts, Categories...

Lets say I have route like this:

/{pageurl}

This is page, with page url that can be home, or news, or blog... That page can be either Static page, and then I would joust print page_content. But it can also be Blog Page, and then I would print latest posts as content.

How should I relate these Static Page and Blog Page tables? Is this inheritance, since both are pages, with their URL, but they have different content? Should I use inheritance, so that both Static and Blog page extends Page that would have page_url? Or should I made another table page_types and there store information about available page types?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog